Mysql索引

1、索引类别

在Mysql中，基于InnoDB存储引擎存储的表采用的索引类型为B-Tree索引，其他的索引类型还有Hash等。

B-tree索引

聚簇索引 - 每个表只能有一个聚簇索引，聚簇索引的作用是将数据行存储在一个B+树索引结构中，该结构的叶子节点存储了完整的数据
二级索引（非聚簇索引） - 包含单列索引、聚合索引等，数据与索引不在一起存储，使用不同的B-tree索引结构来保存指向数据行的指针，以支持特定的查询需求
除Primary Key外，都可规类为二级索引，二级索引是一种间接快速访问数据的方式。形式包含多列索引，唯一单列或多列索引等 二级索引的性能，严重依赖设计良好的Primary Key

2、聚簇索引的选择

第一顺位为primary key（即主键），若存在主键，则一个表的聚簇索引必为主键
第二顺位为unique index（即唯一索引），当表未指定表的主键时，而存在索引值全部为非空值的唯一索引时，此时聚簇索引选择唯一索引
若既无主键，也不存在unique index时，此时会默认在行ROW ID的合成列上生成一个名为GEN_CLUST_INDEX的隐藏聚簇索引

ROW ID 是6 byte字段，由InnoDB分配，用于行排序。插入新行而单调增加，在物理上插入按ROW ID顺序排列

3、二级索引

与聚簇索引的区别

聚簇索引叶子节点存储整行数据，而二级索引的每个叶子节点仅包含二级索引的字段以及指向聚簇索引的引用
较为依赖主键（聚簇索引）的选择，如果主键选择不合理，那么在通过主键的引用进行回表时，速度受主键的制约。与此同时，如果主键过长，二级索引所占空间也会很大，增加了存储占用

回表的概念：

在MySQL中，每个索引都对应一颗B+树，回表的主要含义就是获取到的数据不全，回聚簇索引的叶子结点上取到完整数据。

当我们使用了到了主键（聚簇索引）时，并不会发生回表的情况，因为在聚簇索引的叶子节点上，已经包含了所有要查询的数据。

而在我们使用二级索引时，如果查询的字段中不全为使用到的二级索引，那么在我们查询到叶子节点时，因为叶子节点上只有当前的二级索引值和指向聚簇索引的引用，没有完整的数据，会通过聚簇索引的引用到聚簇索引对应的B+树上进行查询，再返回对应的结果，增加了IO操作。

举一个例子：

假设现在有一个职员信息表employee_info，主键是id，联合索引为age、address两个字段。表中还包含phone、sex等字段。

select * from  employee_info where id = '1'

这个时候不会发生回表现象，因为使用了主键（聚簇索引）id，在其对应的B+树的叶子结点上已经包含了所有数据，按照条件进行筛选后返回结果就好。

select age,address from  employee_info where age = '30'

这个时候同样不会发生回表现象，原因是对应的age、address字段在联合索引上已经存在，不需要通过去聚簇索引的叶子结点取数据。

select age,address,phone from  employee_info where age = '30'

这个时候同样就会发生回表现象，原因是对应的phone字段在联合索引上不存在，在联合索引对应的B+树的叶子节点上，只有age、address和id的引用，需要通过id去聚簇索引的叶子结点取到phone字段数据，返回查询所需的字段。

4、索引的创建以及查看

要在MySQL中创建索引，可以使用CREATE INDEX语句。例如，假设有一个名为employee_info的表，并且想要在address列上创建索引，可以使用以下语句：

CREATE INDEX last_name_index ON employee_info(address);

查看索引

SHOW INDEXES FROM employee_info;

当然这种情况下，如果设置了主键，或者唯一索引，那么是可以看到索引的属性的。如果是无主键，无唯一索引，此时聚簇索引仍然会生成，在行ROW ID的合成列上生成一个名为GEN_CLUST_INDEX的隐藏聚簇索引，这个聚簇索引是看不到的。

当然如果你跟我一样，就是想看看它生成了没有，在哪里能看到它呢。

以我的数据库为例，输入

select version()

查看版本号，我的MySQL数据库版本是5.6.32，为什么要看版本号呢，因为在不同版本的数据库中InnoDB对于的数据表结构和名称发生了一些改变，你可以在数据连接里的information_schema数据库中看到对应的InnoDB的数据表，而在我的数据库版本下，所有的索引的都储存在表INNODB_SYS_INDEXS，你可以使用查询语句或者数据库工具打开这个表看一下，不过想要找到你想查看的表对应的所有索引信息，你仍需知道表id，因为在INNODB_SYS_INDEX中使用table_id做为数据库中的表的标识信息，简单做个连接再做个筛选就可以得到我们想要看到的表的索引索引的信息。

select * 
from INNODB_SYS_INDEXES I1
LEFT JOIN INNODB_SYS_TABLES I2 on I1.TABLE_ID = I2.TABLE_ID
where I2.NAME = 'cb/user'

我这里使用了我的cb数据库中的user表，对应替换数据库及表名即可，展示对应表的所有索引。

5、索引的使用规范

怎么查看语句执行有没有走索引嘞

explain + 你的语句 

例：
explain select sex from employee_info where years = '4'

返回的就是这条语句的各项指标

5.1 最左匹配

先说个与上述关联性最强的。

如果我们使用了联合索引，仍然举上面的例子，假设现在有一个职员信息表employee_info，主键是id，联合索引为years(上班年限)、age、address三个字段，表中还包含phone、sex等字段。

1、 select sex from employee_info where years = '4'

2、 select sex from employee_info where address = 'xxxxx'

3、 select sex from employee_info where age = '20'

4、 select sex from employee_info where years = '4' and address = 'xxxx'

5、 select sex from employee_info where years = '4' and age > '28' and address = 'xxx'

6、 select sex from employee_info where years > '4'

最左匹配原则顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。范围查询(>、<、between)就会停止匹配，截止到当前已匹配到的索引值。

所以上述的6个查询，1、4、5会走索引，2、3不走索引，6视情况而定。

对于1来说，用到了years索引。

对于4来说，也只用到了years索引，因为中间的age间断了，将无法继续匹配赛address索引。具体原因为索引的底层采用的是B+树进行存储，是有序的，但是不同的索引的有序是建立在前一个索引之上的。例如字段名为a,b,c ，对应数据行为（1,9,8、3,4,7、2,3,7、3,4,8)，假设其三个字段是一个联合索引，那么在叶子节点处，他们的排布顺序为 1,9,8 -> 2,3,7 -> 3,4,7 -> 3,4,8 。是基于第一个索引值有序的，但是脱离第一个索引，第二个索引值的排序为 9 -> 3 -> 4 -> 4，是散列的，无序的，第三个索引也是基于前两个的值所确定顺序的，故间断后将无法继续匹配索引。

对于5来说，用到了years和age两个索引，因为使用范围查询后，将不再向后匹配索引。

对于6来说，比较有意思，虽然有使用范围查询后，会使用当前索引，但是在实际应用中，使用这种写法大概率会导致全表扫描，主要原因大概是在第一个索引出就使用范围查询，将会导致大量的回表的操作，导致IO花费过高，数据库的计算发现其花费的成本大于全表扫描，就可能采取全表扫描的措施。其规律是越小的表越会走索引，越大的表，我试过使用40w条的表进行实验，除了过滤掉100%数据外的条件下，全部走得全表扫描。

补充一点，看具体生成环境来说：

在低版本的MySQL中，普遍遵循最左匹配机制，但是MYSQL8.0更新了索引跳跃扫描的机制，支持了2、3两种类型可以使用索引。

这一点看起来与4中说到的B+树的结构相悖，但是实际上底层并没有变，只是在使用时对前面未使用的索引进行遍历后，直到我们使用到的索引处，例如对于4中所举例子，假设条件是 where b = 3 ,首先走索引a，比如它的第一个值为1，那么在这个值的基础上，找b = 3的节点，然后重复这个步骤，直到遍历完a的所有值之后,返回对应结果。本质上是数据库帮你对a进行了扩充，使得你的 b 的条件建立在 a 之上。