Mysql 索引总结

一、索引分类

• 聚集索引（Clustered Index）
  聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。

• 主键索引
  索引列中的值必须是唯一的，不允许有空值。

• 普通索引
  MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

• 唯一索引
  索引列中的值必须是唯一的，但是允许为空值。

• 全文索引
  只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

• 空间索引
  MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

• 前缀索引
  在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

• 辅助索引
  辅助索引，也叫非聚集索引。和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

二、为什么使用B+树作为索引结构？

B-树

B+树

B+树相对于B-树来说，叶子结点存放数据，这么做的好处是为了提高范围查找的效率，除此之外，一个磁盘块的大小是固定的，如果像B-树那样，在非叶子结点放数据，磁盘放不了多少指向，树会很深，I/O效率就会低。

使用Hash作为索引结构怎么样？

可以直接对‘鸡蛋’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据， 那么如果现在执行下面的sql语句：

select * from sanguo where name>‘鸡蛋’

则无能为力，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

所以哈希表是适合于查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。

使用二叉树作为索引结构怎么样？

索引也不只是在内存里面存储的，还是要落盘持久化的，可以看到图中才这么一点数据，如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。

为了节约成本很多公司的磁盘还是采用的机械硬盘，这样一次千万级别的查询差不多就要10秒了，这谁顶得住啊？

三、聚集索引与非聚集索引

• 聚簇索引：将数据存储的顺序与索引顺序相同，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，本质上非聚簇索引存储的是聚簇索引的值，比如主键ID

假设一张表有主键和age列，都建了索引，那么在Innodb引擎中，就对应了两颗B+树。主键对应的那颗树存了记录的全部数据，而age列索引的叶子结点中只存了age->主键的关系。

​ 每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引）。

• 如果表上定义有主键，该主键索引就是聚簇索引。
• 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

​ 表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。

聚簇索引( 主键索引）和非聚簇索引（非主键索引）有什么区别？

• 非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。
  

四、什么是回表？

上面我介绍了两个索引对应了两颗B+树，对应非聚簇索引的那颗树，其实叶子结点没有存所有的记录，存的是到ID的映射。

回表是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：

select * from table where name = ‘hello’

执行的流程是先查询到name索引上的“hello”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。

回到主键索引树搜索的过程，就是回表。

归根到底是因为，普通索引无法直接定位行记录。

覆盖索引如何避免回表？

用覆盖索引，实现覆盖索引的方法是：将被查询的字段，建立到联合索引里去。

读取索引，而无需读表，极大减少数据访问量。

联合索引事示意如下：

五、非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

这个好理解，如果全部命中了索引，你就拿到了所有你想要的字段数据，还回什么表？

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

六、什么是最左匹配原则？

• MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。

• 如有联合索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

• 如有联合索引 (a,b,c,d)，对于下面，这两条都不会命中索引，因为不是从最左开始匹配的，

select * from table_name where  b = 1
select * from table_name where  b = 1 and c = 2

• 对于下面，只有a列使用索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的。

select * from table_name where a = 1 and c = 2

• 对于下面列为字符串的情况，只有前缀匹配会用到索引，即只有在%在右边的情况下才会生效，中缀匹配和后缀匹配只能进行全表扫描。

select * from table_name where a like 'ab%';// 前缀匹配
select * from table_name where  a like '%ab' // 中缀匹配
select * from table_name where  a like '%ab%' // 后缀匹配

七、索引失效的情况

1. 有or必全有索引;
2. 联合索引未用左列字段，这对应着索引的最左匹配原则;
3. like以%开头，即不是前缀匹配;
4. 需要类型转换;
5. where中索引列有运算或者使用了函数;
6. 如果mysql觉得全表扫描更快时（数据少）;

八、为什么建议使用主键自增的索引？

• 自增是有序的对吧，那么你插入ID的时候不用查询了，直接插入就行了
• 不然你为了插这条数据，又需要保证B+树的规则性，你肯定是有花销的

九、索引下推

最左前缀可以用于在索引中定位记录，那么，那些不符合最左前缀的部分，会怎么样呢？

以用户表的**联合索引（name, age）**为例，假设现在有一个需求，找出所有姓 “张” 并且 20 岁的男性：

select * from tuser where name like '张%' and age = 20 and sex = male

《高性能 MySQL》 书中提到：对于联合索引，如果查询中有某个列的范围查询，则其右边所有列都无法使用索引进行快速定位。

这是因为联合索引底层实际上是从左到右优先级排序的，即只有name相同时，age才有效

所以对于这条语句来说，其实并不能完全踩中 (name, age) 这个联合索引，他只能踩到 name。

具体来说，这个语句在搜索（name，age）的联合索引树的时候，并不会去看 age 的值，只是按顺序把 “name 第一个字是张” 的记录一条条取出来，然后开始回表，到主键索引上找出数据行，再一个一个判断其他条件是否满足。

• MySQL 5.6 之前，当进行索引查询时，首先根据索引来查找记录，然后再根据 where 条件来过滤记录

• MySQL 5.6 开始，数据库在取出索引的同时，会根据 where 条件直接过滤掉不满足条件的记录，减少回表次数，比如上面的例子（name，age），那么其实可以根据age再过滤一遍。这就是 索引下推 (Index Condition Pushdown,ICP) ，一种根据索引进行查询的优化方式。

十、关于联合索引的一些注意点

• 联合索引建立的时候，会默认在索引树上带上主键索引

参考

https://www.zhihu.com/question/52536048/answer/2264727222