高性能索引创建策略

17.1 创建策略

17.1.1 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT，这是因为：

• 数据类型越小，在查询时进行的比较操作越快（CPU层次）
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个策略对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/0。

17.1.2 利用索引选择性和前缀索引

① 索引的选择性/离散性

创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（N）的比值，范围从 1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。很差的索引选择性就是列中的数据重复度很高，比如性别字段。

② 前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，应该使用前缀索引，因为 MySQL 不允许索引这些列的完整长度）。按照《阿里最新Java编程规范泰山版》中《索引规约》中的说法：

需要注意的是，推荐长度为 20 这只是默认情况下的推荐，长度为 15 和长度为 20 区分度可能仅有千分之几的差距。所以需要使用说明中的公式来计算区分度，按需选择合适的长度创建前缀索引。前缀索引虽然是一种能使索引更小、更快的有效办法，但另一方面也有其缺点 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 操作，也无法使用前缀索引做覆盖扫描。

:::color2
后缀索引

顾名思义，后缀索引就是将数据倒排，然后截取部分创建后缀索引。MySQL 原生并不支持后缀索引，但是可以把字符串反转后存储，并基于此建立前缀索引。（应用场景：找到某个域名的所有电子邮件地址）

:::

17.1.3 只为用于搜索、排序或分组的列创建索引

只为出现在 WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了。除非是需要使用覆盖索引，又或者为出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。

17.1.4 合理设计多列索引

一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询需求。

多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。

然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

17.1.5 处理冗余和重复索引

MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

17.1.6 删除未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。

17.2 三星索引

17.2.1 什么是三星索引

对于一个查询而言，一个三星索引，可能是其最好的索引。如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书

The index earns one star if it places relevant rows adjacent to each other,

索引将相关的记录放到一起则获得一星,

a second star if its rows are sorted in the order the query needs,

如果索引中的数据顺序和查找中的排列顺序一致则获得二星,

and a final star if it contains all the columns needed for the query.

如果索引中的列包含了查询中需要的全部列则获得三星。

17.2.2 三星索引解释

★☆☆

• 定义：如果与一个查询相关的索引行是相邻的，或者相距足够靠近的话，那这个索引就可以标记上一颗星。
• 收益：它最小化了必须扫描的索引片的宽度。
• 实现：把 WHERE 后的等值条件列作为索引最开头的列，如此，必须扫描的索引片宽度就会缩至最短。

★★☆

• 定义：如果索引行的顺序与查询语句的需求一致，则索引可以标记上第二颗星。
• 收益：它排除了排序操作。
• 实现：将 ORDER BY 列加入到索引中，保持列的顺序

★★★

• 定义：如果索引行中包含查询语句中的所有列，那么这个索引就可以标记上第三颗星。
• 收益：这避免了访问表的操作（避免了回表操作），只访问索引就可以满足了。
• 实现：将查询语句中剩余的列都加入到索引中。

17.2.3 案例