-
MySQL索引详细
一、简介
索引用于快速找出在某个列中有一特定值的行。
不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
如果有了索引,那么会将该Phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,
二、优缺点
2.1优点
-
所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段设置索引
-
大大加快数据的查询速度
2.2缺点
创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加
索引也需要占空间,数据表中的数据也会有最大上限的
如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值
当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
三、分类
操作:
查看索引:show index from
删除索引:drop index <索引名> on <表名>
3.1 单列索引
1.主键索引(自动创建)
create table user(id varchar(20) primary key,name varchar(20))
2.普通索引
MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
-建表时创建(此时索引名和列名相同)
create table user(id varchar(20) primary key, name varchar(20),key(name))
-建表后创建
create index name_index on user(name)
3.唯一索引
索引列中的值必须是唯一的,但是允许为空值,
-建表示创建
create table user(id varchar(20) primary key, name varchar(20),unique(name))
-建表后创建
create unique index name_index on user(name)
3.2 组合索引(复合索引)
在表中的多个字段组合上创建的索引
只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
-建表示创建
create table user (id varchar(20) primary key, name varchar(20), age int,key(name,key))
-建表后创建
create index name_age_index on user(name,age)
MySQL 最左前缀原则
mysql 建立多列索引(联合索引)有最左前缀的原则,即最左优先,如:
如果有一个 2 列的索引 (col1, col2),则已经对 (col1)、(col1, col2) 上建立了索引;
如果有一个 3 列索引 (col1, col2, col3),则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引;
并且mysql 具有查询优化器:
MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效
四、数据结构
在mysql 使用InnoDB存储引擎时,首先会将插入的数据按照主键进行排序从而形成一个单向链表,然后为了提高查找效率,mysql就将单向链表升级成了B+树。
一般高度为3的B+数可存储的记录为10亿左右,因此对于我们2-3层树就足够了。
4.1 B+树特点
B+树是B树的一种变形,比B树具有更广泛的应用
- 为所有叶子结点增加一个链指针;
- 所有关键字都在叶子结点出现;
- 非叶子结点作为叶子结点的索引;B+树总是到叶子结点才命中;
4.2为什么使用B+数
五、聚簇/非聚簇索引
5.1 聚簇索引
定义
将数据与索引放到了一起,索引结构的叶子节点保存了行数据。
简介
1.如果一个主键被定义了,那么这个主键就是作为聚集索引
-
如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
-
如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。
-
自增主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。聚集索引的排序,必然会带来大范围的数据的物理移动,这里面带来的磁盘IO性能损耗是非常大的。
注意
主键索引一定是聚簇索引(在mysql数据库innodb引擎里面,主键的确就是聚集索引。)
5.2 非聚簇索引(辅助索引)
将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置。
辅助索引访问数据总是需要二次查找
叶子节点存储的是主键值:(不存主键地址原因)如果数据记录发生了页裂变导致数据地址变了,那辅助索引也要更新,对于这种情况来说存储主键更好
5.3注意
建议使用int 自增作为主键
原因:
聚簇索引的数据在索引中存放顺序与物理存放顺序是一样的,那么,只要索引是相邻的,对应的数据在磁盘上也是相邻的。
如果不是自增id,那么在添加记录时就会不断地调整数据的地址、数据的物理地址、分页,而不是直接添加到索引树的末尾。
如果是自增的,就简单了,直接一页一页逐步添加,索引结构相对紧凑,并且磁盘碎片也少,效率高。
六、无法使用索引
情况一:查询语句中使用like关键字
如果使用like关键字,并且匹配字符串时的第一个字符为“%”,那么就无法查询索引树,因为不知道要查询什么。但如果%在后面,就可以 使用。
情况二:查询语句中使用多列索引
不满足最左前缀原则时
情况三:查询语句中使用or关键字
只有当or两边的字段都创建了索引,才可以使用索引,否则无法使用。
__EOF__