Mysql 索引特点

承接上文Mysql Server原理简介

聚簇索引、二级索引、联合索引分别具备什么样的特点？

聚簇索引

数据跟索引放在一起的叫聚簇索引；

数据和索引分开存储的叫非聚簇索引；

innodb存储引擎，数据和文件都放在ibd文件中，实际的数据是跟索引绑定在一起的，如果表中有主键，那么跟主键绑定，如果没有主键那么跟唯一键绑定，如果没有唯一键，和6个字节的rowid（6字节的随机字符串）进行绑定。

6字节的rowid是隐藏的，看不到的，实际的mysql数据行中包含了非常多的隐藏字段。

MyISAM是非聚簇索引，因为数据文件和索引文件是分开存放的。

innodb中有聚簇索引也有非聚簇索引。

一个表可以有N个索引，每一个索引都是一颗B+树，每个B+树都是独立的，一个表中会存在多颗B+tree。

表中的数据存储几份?

叶子节点存储的数据行存了一份。

如果存一份的话，就会存在一个问题，因为一个表里面会包含N多个B+ tree：

数据只存储一份，其他的非聚簇索引的叶子节点中存储的是聚簇索引的key值，

所以innobdb中也包含了非聚簇索引。

这是一个表数据，id是主键索引，name是普通索引，

id主键索引，叶子节点存储的是数据，所以id是聚簇索引；

name是普通索引，叶子节点存储的是id值，所以name B+ tree是一个非聚簇索引、二级索引或叫辅助索引。

索引是一个具体的物理结构；

如果表中唯一键有多个，没有主键，那么此时聚簇索引按照唯一键的前后顺序创建。

多个列建的索引叫联合索引或复合索引

一般情况下，设置索引列的时候，只会选择一个列作为索引字段，但是在某些特殊情况下，需要将多个列共同组成一个索引字段，称之为联合索引。

MyISAM索引都是辅助索引，没有聚簇索引，都是用来辅助查询的。

在索引优化的时候需要注意什么问题?

• 索引字段要尽可能小的占用存储空间

• 在满足业务系统的需求内尽可能自增

• 索引字段尽可能不要为null，因为很多情况下null并不等于空

• 选择索引的时候，索引的基数尽可能大，到底给哪些列建立索引，DV/count >= 80%适合创建索引，distinct value唯一值除以count，不重复的值要尽可能多

• 不要给所有字段添加索引，并不是索引越多越好

什么情况下会导致索引失效？

• 索引字段尽量不要频繁修改

• like查询的时候左边不要加%

• 索引字段不要添加任何表达式操作

• 索引字段在使用的时候不要出现类型的隐式转换

• 索引上不要出现函数计算

• 组合索引在进行使用的时候要遵循最左匹配原则

• in或or在很多情况下会导致索引失效，但是要根据实际的情况来进行判断

• 在使用索引的时候，如果中间的某个索引列使用了范围查询，会导致后续的索引失效

回表

select * from table where name='zhangsan'，

id是主键，name是普通索引， 先根据name值去name B+树找到对应的叶子节点，取出id值，再根据id值去id B+树中查找全部的结果，这个过程称为回表，回表的效率比较低，尽可能不要使用，避免回表的产生，因为需要回到原来的表里查询对应的数据记录。

索引覆盖

先根据name值去name B+树查找结果，能够直接获得id和name，不需要去id的B+树查找数据了，这个过程叫索引覆盖即索引的叶子节点中包含了要查询的全部数据，推荐使用索引覆盖。

最左匹配原则

id是主键，name、age是组合索引，在查找的时候必须从左往右匹配。第一和第三个sql符合该原则；

如果把age和name的顺序换下，不会影响最终的查询结果，因为这时候优化器会优化，调整对应的顺序，所以也会匹配该原则；在比较数据的时候，先比较第一个，再比较第二个，因为只有第一个相同了，才有比较第二个的可能。

索引下推

select * from table where name=? and age=?，没有索引下推前，先根据name的值从存储引擎中拿到符合条件的数据，然后在server中对age进行数据过滤，有了索引下推之后，直接根据name和age从存储引擎中筛选对应的数据，返回给server，不需要做索引过滤；原来在server层需要对age做过滤，现在下推到了存储引擎层过滤数据。

mysql 5.7之后默认开启索引下推，两个字段一起筛选，筛选的数据量少了，io量也有少了。