5.3 高性能的索引策略

独立的列

• 独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。【就是在索引列上做计算或函数】

索引选择性

索引选择性是指：不重复的索引值和数据表的记录总数（T）的比值，返回从1/T ~ 1。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。【个人理解就是，字段值的唯一性越高越好】

前缀索引

前缀索引是指索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。

多列索引

就是联合索引。

选择合适的索引列顺序

对于联合索引，选择合适的索引列顺序至关重要。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序（order by）和分组（group by）的需要。

经验法则一：将选择性最高（就是值的唯一性最高）的列放到索引最前列。【注意：场景不同选择不同，没有一个放之四海皆准的法则，这只是经验法则。】

———— 2021.7.6 晚

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。简单来说，就是 InnoDB 中的主键索引。表如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

优点

1. 数据访问更快。聚簇索引将索引和数据保存在同一个B+ Tree 中，因此从聚簇索引中获取数据更快。
2. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点

1. 插入速度严重依赖于插入顺序。
2. 更新聚簇索引列的代价很高。因为 InnoDB 将每个被更新的行移动到新的位置。
3. 可能面临 “页分裂” 的问题，会导致表占用更多的磁盘空间。
4. 二级索引（非聚簇索引）访问需要两次索引查找。

InnoDB 和 MyISAM 的数据分布对比

MyISAM

MyISAM 的数据分布非常简单，它为每一行生成一个递增的行号，索引树的叶子节点就是索引值 + 行号，根据索引值排序。

事实上，MyISAM 中主键索引和其他索引在结构上没有什么不同，主键索引就是一个名为 PRIMARY 的唯一非空索引。

InnoDB

因为 InnoDB 支持聚簇索引，所以使用非常不同的方式存储同样的数据。

在 InnoDB 表中按主键顺序插入行

以自增或有序增长的方式创建主键存入数据，可以直接插入主键索引树的尾部，无须拆树，效率更高。

覆盖索引

如果一个索引包含所有需要查询的字段的值（这样就不用回表查询了），我们就称为 “覆盖索引” 。覆盖索引是非常有用的工具，能够极大地提高效率。

MySQL 优化器会在执行查询前判断是否有一个索引能进行覆盖。

在 EXPLAIN 的 Extra 列出现 Using index 代表使用到了覆盖索引。

延迟关联

InnoDB 的二级索引的叶子节点都包含了主键的值，这意味着 InnoDB 的二级索引可以有效的利用这些额外的主键列来覆盖查询。例如，可以通过二级索引先查出主键，这样，相当于用到了覆盖索引，再拿着主键去查询其他信息，效率更高。

优化前：

优化后：

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：索引排序、文件排序。如果 EXPLAIN 出来的 type 列的值为 “index”，则说明 MySQL 使用了索引扫描来做排序了。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足最左前缀原则。

有一种情况下 ORDER BY 子句可以不满足索引的最左前缀原则，就是前导列为常量的时候，例如：

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即删除。

冗余索引和重复索引有一些不同，如果创建了索引（A，B），再创建索引（A）就是冗余索引。因为这只是前一个索引的前缀索引。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。例如：

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远用不到的索引。可以用以下工具进行定位。

Percona Toolkit 中的 pt-index-usage

该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN，然后打印出关于索引和查询的报告。

Shlomi Noach 的 common_schema

common_schema 是一系列可以安装到服务器上的常用的存储和试图。

索引和锁

索引可以让查询锁定更少的行。InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。

即使使用了索引，InnoDB 也有可能锁住一些不需要的数据。例如：

关于 InnoDB、索引和锁有一些很少有人知道的细节：InnoDB 在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且是的 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢的多。

5.4 索引案例学习

1. 支持多种过滤条件

通常来说，我们不应该在选择性低的列上创建索引，但有一种情况除外：当某个字段（例如性别）在多个查询中都会用到时，我们可以把该字段作为联合索引的前缀，即使未用到字段查询，也可以用 sex in (1,2) 的方式保证 SQL 能用到索引。这个案例显示了一个基本原则：考虑表上所有的选项，应该同时优化查询和索引以找到最佳的平衡。

对于范围字段（例如 age）放到索引的后面，另外也可以考虑用 IN 代替，不过每额外增加一个 IN() 条件，优化器需要做的组合都将以指数形式增加。

2. 避免多个范围条件

3. 优化排序

对排序字段增加适合的索引。