MySQL索引

索引的定义及作用

索引是存储引擎用来快速找到记录的一种数据结构

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高好几个数量级

索引的优点：

1. 索引大大减少了服务器需要扫描的数据量

2. 索引可以帮助服务器避免排序和临时表

3. 索引可以将随机I/O变为顺序I/O

三星系统

用来衡量什么是好的索引

InnoDB索引的数据结构

索引的常见模型

InnoDB中索引的底层结构为B+树，是B树的一个变种

为什么使用B+树而不是B树

1. B+树的磁盘读写代价更低：由于B+树的中间节点只存放键，不存放值，因此一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围

2. B+树的查询效率更加稳定：B+树的所有查询都要查找到叶子节点

3. B+树的支持高效的范围查询：叶子节点直接由双向链表连接

B-Tree索引的生效场景

• 全值匹配：和索引中所有列的值做等值匹配，比如有一个多列索引（A，B，C），则A = ? and B = ? and C = ?
  这样的查询就是全值匹配

• 最左前缀索引列匹配：和索引中的最左前缀索引列做等值匹配，比如有一个多列索引（A，B，C），则你同时拥有了（A）和（A，B）这两个索引

• 单列前缀匹配：可以匹配某一列的值的开头部分，比如你有一个姓名name的索引，你可以使用这个索引来快速查找姓“李”的数据

• 范围值匹配：索引列也支持range查询，但是做range查询的索引列之后的索引列将失效

• 精确匹配某些列并范围匹配另一列：对于多列索引，可以对前缀索引列做精确匹配，再对后面的一列做range匹配，比如有一个多列索引（A，B，C），则 A = ? and B = ? and C >= ? and C <= ?
  这样的查询条件可以很好的利用（A，B，C）索引

• 只访问索引（覆盖索引）：查询的条件和返回在字段都只需要访问索引，则无需做回表查询

高性能的索引策略

正确创建和使用索引是高性能查询的基础，我们需要评估选择不同索引的性能差异，从而才能高效的使用索引

独立的列

独立的列是指：索引列不能是表达式的一部分，也不能是函数的参数，否则MySQL无法使用已有的索引

例如下面的查询：

select * from user where {索引列} + 8 = 18;
复制

我们应该使用简化的where条件，并将索引列单独放在比较符号的一侧

选择合适的前缀索引长度，提高索引选择性

索引的选择性是指不重复的索引值（基数，cardinality）和数据总条数（total）的比值，值范围为1/total~1之间，索引的选择性越高则查询效率最高，因为选择性高的索引在查询时可以过滤掉更多的行，比如主键或者唯一键索引的选择性就是1

一般计算完整列的选择性，并找出前缀索引选择性接近完整列的选择性的长度

-- 完整列的选择性
select count(distinct {目标列})  count(*) from {数据表};
-- 前缀索引选择性
select count(distinct left({目标列}, {前缀长度}))  count(*) from {数据表};
复制

只考虑平均选择性的话是不够的，在数据倾斜的特殊情况下，比如对某些前缀的请求远大于其他前缀，那么该前缀索引的作用就几乎没有了

多列索引

很多人对多列索引的理解是不够深入的，一个最常见的错误是给每个查询列都创建独立的索引，或者按照错误的顺序创建了多列索引

• 在多个列上创建独立的单列索引大部分情况下，并不能提高MySQL的查询性能

• MySQL 5.0以及以后的版本虽然提供了“索引合并”（index merge）的功能，但实际上大部分情况说明了表的索引创建得很糟糕：

• 出现多个索引的求交操作时（and操作），通常表明包含相关列的多列索引更合适

• 出现多个索引做求并操作时（or操作），通常需要消耗大量的cpu和内存资源，用于数据缓存、排序和合并的操作上

• 最重要的是，查询优化器并不会把上面的消耗计入“查询成本”中，优化器只关心随机页面读取，这会使得查询成本被“低估”，导致“索引合并”的查询性能有时都不如全表扫描的性能来的好

选择合适的索引列顺序

• 正确的索引列顺序，除了考虑索引列的选择性之外，考虑如何满足好查询的排序和分组要求也同样重要

• 在不需要考虑排序和分组操作时，将选择性高的列放在索引的前面通常是不错的选择

• 有分组和排序操作的情况下，实际上我们不能单独考虑单个查询的性能最优，我们更要看不同查询维度的频率，我们需要通过调整索引列顺序，来让查询频率最高的查询性能最好

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据的存储方式（索引组织表），当表以“聚簇索引”的方式存储时，它的数据行是存放在索引的叶子页的，而节点页只包含了索引列，“聚簇”表示数据行和相邻的键值紧凑地存储在一起，但在innodb中包含相邻键值页面可能会相距甚远

对应地有非聚簇索引（二级索引），其存储的是主键值，因此非主键索引在查找后还需要用主键索引检索才能获取记录，过程称为回表

• 优点：

• 将相关的数据保存在一起，减少IO次数

• 因为索引和数据保存在一起，数据访问更快

• 索引覆盖的查询，可以直接使用页节点中的主键值

• 缺点：

• 如果数据能全部放入内存中，聚簇索引就没什么优势了

• 插入速度严重依赖插入顺序，如果非主键顺序插入则插入速度低

• 更新聚簇索引的代价很高，需要移动行，会有随机io

• 在页已满的情况下，新插入数据会导致页分裂，页分裂会导致更多的io写，也会导致表占用更多的磁盘空间

• 聚簇索引在行比较稀疏的情况下，全表扫描变慢（读取更多的页）

• 较长的主键会导致二级索引占用空间较大

• 二级索引的访问需要两次索引查询，而不是一次

一般场景下从性能和存储空间上考量推荐使用自增主键

1. 新增记录时不需要挪动其他记录，也不会触发页分裂

2. 存储上，普通索引的叶子节点只用的空间大小较业务字段小

覆盖索引

如果索引的叶子节点中，已经包含要查询的数据，则称之为“覆盖索引”，只扫描索引而不需要做回表操作

优点：

1. 因为索引的条目通常远小于数据行的大小，只读取索引的情况下，可以大大减少读取的io操作

2. 由于索引是顺序存储的，范围查询的io操作也会减少

3. 部分存储引擎如MyISAM在内存中只缓存索引，要访问数据时需要执行系统调用，有了覆盖索引可以极大提升性能

使用索引扫描来完成排序

一个索引即可以满足查询，也可以用于排序，当索引的列顺序和order by子句的顺序一致，且所有列的排序方向都一样时，MySQL就能够使用索引来实现对结果集的排序

冗余索引和重复索引

重复索引：在相同的列上创建多个类型相同的索引

冗余索引：

• 如果已经创建了索引（A，B），则再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引

• 另一种情况是创建索引（A，ID），其中ID的主键，对于Innodb来说主键列已经包含在二级索引中，所以这也是冗余的

• 通常情况下我们应该避免冗余索引，但是有时候出于性能的考虑需要创建冗余索引，因为扩展索引时，会使索引变大，从而影响其他使用该索引的查询性能

索引条件下推

mysql5.6引入的特性，在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。例如联合索引（a,b），查询条件abc，其中a为范围查询，索引下推会过滤掉不满足c条件的记录

使用explain分析索引的的使用

explain {查询语句}
mysql> explain select * from t where id > 10000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 50128 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
复制

explain返回的字段信息

type字段

extra字段

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化