Mysql索引总结

索引

帮助加速检索数据库数据的一种数据结构。

Mysql索引分类

按数据结构：B+tree索引、Hash索引、Full-text索引

按物理存储：聚簇索引（聚集索引）、非聚簇索引（非聚集索引、二级索引、辅助索引）

按字段特性：主键索引、普通索引、前缀索引

按字段个数：单列索引、联合索引（复合索引、组合索引）

下面根据数据结构介绍一下常用的两种索引分类

 B+树

一种多路平衡树，所以节点天然有序（左节点小于父节点小于右节点），范围查询不需要全表扫描

B+树索引

聚集索引与非聚集索引异同

相同点：内部都是B+树，高度平衡、叶子节点存放所有数据

不同点：

①聚集索引的叶子节点存放的是一整行的信息

②聚集索引一个表中必须且仅有一个；非聚集索引一个表可以存在多个

③聚集索引存储记录是物理上连续存在的；非聚集索引是逻辑上的连续，物理存储不连续

④聚集索引查询速度快，插入速度越慢，范围查询块；非聚集索引相反

⑤聚集索引的非叶子节点存储的是表的主键；非聚集索引的非叶子节点存储的是自己设置的索引字段对应的值（联合索引则为几个字段对应的值）

⑥聚集索引的叶子节点，存储着当前表中每条记录的所有信息；非聚集索引的叶子节点上只存储当前记录对应的主键ID（即聚集索引的非叶子节点存储的值）

⑦非聚集索引会存在回表问题

Hash索引

底层是一个Hash表。因为是一个k-v的数据结构，所以多个数据在存储层上完全没有任何顺序关系。所以在区间查询时无法直接通过索引查询，就需要全表扫描，即Hash索引适合等值查询场景而不适合范围查询场景。

Hash索引缺点

①Hash索引适合等值查询，无法进行范围查询

②Hash索引没法利用索引完成排序

③Hash索引不支持多列联合索引的最左原则

④有大量重复键值的情况下，Hash索引效率会很低，因为存在Hash碰撞问题

*非聚集索引的回表问题

当索引为非聚集索引的时候可能存在回表问题

 索引覆盖

查询语句的执行只用从索引中就能取得，如果一个索引包含或覆盖了满足查询语句中字段与条件的数据就交覆盖索引。

索引覆盖可以避免回表减小IO次数。

如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。

不索引覆盖情况

①select中字段含不在索引中的字段

②where中对索引进行like操作

索引下推

索引下推是5.6引入的对索引的优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';。目的是减少回表次数，io操作，所以发生在非聚集索引中。

people表中(zipcode，lastname，firstname)构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

在引用子查询条件不能索引下推；引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

当使用explain解析查询sql时，Extra一列值为Using Index condition，则证明使用了索引下推。 

组合索引举例