王秀文的博客

MySQL 索引

发布
分类
MySQL
篇幅
2129 字 / 5 分钟
阅读
369

索引




需要创建索引的情况

1、 字段值唯一
2、 Where条件
3、 Group by 和order by
4、 Update、delete的where条件
5、 Distinct字段
6、 多表join操作时，表尽量不超过3个，对where条件创建索引，对连接的字段创建索引且类型一致
7、 多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。

不需要创建索引的情况

1、 WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引
2、 如果表记录太少，比如少于 1000 个，那么是不需要创建索引的
3、 字段中如果有大量重复数据，也不用创建索引，比如性别字段
4、 频繁更新的字段不一定要创建索引

索引失效的情况

1、 如果索引进行了表达式计算，则会失效
2、 如果对索引使用函数，也会造成失效
3、 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效
4、 当我们使用 LIKE 进行模糊查询的时候，后面不能是 %
5、 索引列尽量设置为 NOT NULL 约束
6、 我们在使用联合索引的时候要注意最左原则

MySQL 选择存储引擎

MySQL 中 InnoDB、MyISAM 存储引擎都会默认使用的是 B+树 来进行存储，无法使用 Hash索引。
如果是Memory/Heap，或者是NDB存储引擎，可以进行选择 Hash 还是 B+树。

自适应 Hash 索引

因为B+树可以使用到范围查找，同时是按照顺序的方式对数据进行存储，因此很容易对数据进行排序操作，在联合索引中也可以利用部分索引键进行查询。
这些情况下，我们都没法使用Hash索引，因为Hash索引仅能满足（=）（<>）和IN查询，不能使用范围查询。
InnoDB 本身不支持 Hash，但是提供自适应 Hash索引（Adaptive Hash index），不需要用户来操作，而是存储引擎自动完成的。
此外，Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
而对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。

自适应Hash也是InnoDB三大关键特性之一，另外两个分别是插入缓冲（Insert Buffer）和二次写(Double Write)。

什么情况下才会使用自适应Hash索引呢？

如果某个数据经常会访问到，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。需要说明的是：

  1. 自适应哈希索引只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，可以让自适应Hash放到缓冲池中，也就是InnoDB buffer pool，进一步提升查找效率。

  2. InnoDB 中的 自适应 Hash 相当于是"索引的索引"，采用 Hash索引 存储的是 B+树 索引中的页面的地址。这也就是为什么可以称自适应Hash为索引的索引。
    采用自适应Hash索引目的是可以根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以提高数据的检索效率。

  3. 是否开启了自适应Hash，可以通过innodb_adaptive_hash_index变量来查看

show variables like '%adaptive_hash_index';

联合索引

联合索引的最左前缀匹配原则针对的是创建的联合索引中的顺序，如果创建了联合索引（x,y,z），那么这个索引的使用顺序就很重要了。如果在条件语句中只有y和z，那么就用不上联合索引。
此外，SQL条件语句中的字段顺序并不重要，因为在逻辑查询优化阶段会自动进行查询重写。
最后你需要记住，如果我们遇到了范围条件查询，比如（<）（<=）（>）（>=）和between等，那么范围列后的列就无法使用到索引了。

通过索引进行检索的原理

B+树是如何进行记录检索的？

如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

普通索引和唯一索引在查询效率上有什么不同？

我们创建索引的时候可以是普通索引，也可以是唯一索引，那么这两个索引在查询效率上有什么不同呢？

唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB存储引擎的页大小为16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次“判断下一条记录”的操作，对于CPU来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

查找 “不经常使用的” 索引

performance_schema.table_io_waits_summary_by_index_usage 数据表，它表明了每个索引进行统计的I/O等待事件。其中COUNT_STAR代表了事件的次数。

过滤掉一些系统表，查看下数据表中有哪些索引不经常使用，具体的SQL语句：

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,COUNT_STAR FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA != 'mysql' AND OBJECT_SCHEMA != 'performance_schema'
MySQL