前缀索引和索引的选择性
索引的选择性
索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（＃T）的比值，范围从1/＃T到1之间。
例如，如果一张表记录了n个不同国家的信息，这些国家的名称互不相同，在国家名称上建立的索引，其选择性就是 n/n=1，而它们使用的官方语言可以重复，因此在官方语言上建立的索引选择性就小于1了。
索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
前缀索引
为什么提到索引的选择性呢？其实是因为有这样一个问题：对于BLOB、TEXT或者很长的VARCHAR类型的列，如果对其完整内容进行索引，对空间和性能的消耗都非常大，所以MySQL也不支持这种索引，但是可以只对字段的前一部分字符进行索引，即前缀索引，这样做的缺点是，会降低索引的选择性，因为越短的前缀能过滤的行越少。
这里的关键点在于，如何选取前缀的长度，既要保证较高的选择性，同时不能太长（节约空间）。那么有没有一种方法使得前缀索引的选择性接近于索引整列，使前缀的“基数”应该接近于完整列的“基数”呢？
确定合适的前缀长度
书中给了这样一个例子：
现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：
可以看到每个前缀都比原来的城市名出现的次数更多，也就是说，长度为3的前缀过滤的行比原来的少。需要继续增加前缀长度。我们增加，直到这个前缀的选择性接近完整列的选择性。经过实验后发现，前缀长度为7时比较合适，接近45～65次：
计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近完整列的选择性。
查询显示，当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。
以上计算的其实都是是平均选择性，只看平均选择性是不够的，还有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。观察前缀为4的最常出现城市的次数，可以看到明显不均匀：
如果前缀是4字节，则最常出现的前缀（”San ”）出现的次数比最常出现的城市出现的次数要多很多，这意味着靠”San ”这个前缀过滤掉的行其实很少，即这些值的选择性比平均选择性要低，这是因为很多城市都以这个词开头。所以，我们还要继续增加前缀长度，在平均选择性接近的情况下平考虑数据分布不均的情况。
如何创建前缀索引
前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。
一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。
多列索引
很多小白会想当然的听从建议，为每列创建独立的索引，这时的表结构可能长这样：
单列索引和索引合并
在多列上独立地创建多个单列索引，在大部分情况下并不能提高MySQL的查询性能。MySQL引入了一种叫“索引合并”（index merge）的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行。在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。对多个单列索引得到的结果进行合并，这就是索引合并策略做的事情。
这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。
下面的查询就使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：
索引合并策略有时候效果非常不错，但更多的时候，它说明了表中的索引建得很糟糕：
- 当优化器需要对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
- 当优化器需要对多个索引做联合操作时（通常有多个OR条件），通常需要在算法的缓存、排序和合并操作上耗费大量CPU和内存资源，尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
- 更重要的是，优化器不会把这些操作计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响。
如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的，是否需要增加多列索引避免索引合并。也可以通过参数optimizer_switch来关闭索引合并功能，还可以使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。
选择合适的索引列顺序
在一个多列B-tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，在索引第一列有序的情况下，其次排序第二列，等等。即后一列的有序性是基于前一列的有序性的。
在从左往右依赖有序的情况下，索引才可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。所以，多列索引的列顺序至关重要。
对于如何选择索引的列顺序有一个重要的经验法则：将选择性最高的列放到索引最前列。这个建议准确吗？在很多场景中可能有帮助，但是要全面地考虑各种场景的话，考虑如何避免大量随机I/O和排序可能更重要。还要具体场景具体分析。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时索引的作用只是优化查询语句中的WHERE条件。在这种情况下，按这个原则设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说，选择性也更高。
然而，性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面选择前缀的长度需要考虑的因素一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
以下面一个具体查询为例：
（staff_id、customer_id）索引的顺序应该如何呢？这时，可以通过运行某些查询来确定在这个表中值的分布情况，并确定哪列的选择性更高。先用下面的查询预测一下，看看各个WHERE条件的分支对应的数据基数有多大：
应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小，过滤了更多的行。我们再来看看对于这个customer_id的条件值，对应的staff_id列的选择性如何：
当然，如果表中的其他数据查询出来都是这种customer_id基数大（过滤掉更多行），staff_id基数小（更多重复）的话，这种索引对这个查询来说很合适，但是如果情况不是这样，即一部分的数据可能是staff_id基数更大，这个索引可能对这部分条件值的查询不公平。
如果没有运行类似的查询来确认实际的情况，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：
customer_id的选择性更高，所以答案是将其作为索引的第一列。
聚簇索引
如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”，这可能会导致非常高的锁竞争，从而出现性能问题。
聚集的数据有一些重要的优点：
- 减少磁盘I/O：例如，在实现电子邮箱应用时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
- 数据访问更快：聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
同时，聚簇索引也有一些缺点：
- 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
- 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
- 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
- 二级索引（非聚簇索引）可能比想象中的更大，因为二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。
顺序插入和随机插入
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这就是最糟糕的情况，数据本身没有任何聚集特性。
我们来做如下两个基准测试。第一个使用整数ID插入userinfo表，第二个是userinfo_uuid表。除了将主键改为UUID，其余的和userinfo表完全相同：：
向InnoDB表中插入数据的测试结果
主键字段更长，页分裂和碎片，导致的随机的UUID插入无论在时间还是空间都欠佳。
下面是总结的一些缺点：
- 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，InnoDB在插入之前不得不先找到，并从磁盘将目标页读取到内存中。这将导致大量的随机I/O。
- 因为写入是乱序的，所以InnoDB不得不频繁地做页分裂操作，以便为新记录分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个（原页、新页、父页）。
- 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
然而，顺序插入也会有糟糕的时候，对于高并发的工作负载，在InnoDB中按主键顺序插入可能会造成明显的写入竞争。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。
覆盖索引
如果索引的叶子节点中已经包含要查询的列，那么还有什么必要再回表查询呢？这个时候可以使用索引直接获取查询的数据。如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。
如果查询只需要扫描索引而无须回表，会带来多少好处：
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存型的应用负载非常重要，因为在这种情况下，响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
- 因为索引是按照列值的顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。可以通过OPTIMIZE命令使得索引完全实现顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
- 由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有用。所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
当执行一个被索引覆盖的查询（也叫作索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。
使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果在EXPLAIN的输出结果中，type列的值为“index”，则说明MySQL使用了索引扫描来做排序（注意，不要和Extra列的“Using index”搞混）。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录都回表查询一次对应的记录。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的应用负载上。MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两项任务，这样是最好的。
下面是使用索引扫描来做排序的适用条件：
只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时（就是不能一个字段是正序一个是反序），MySQL才能使用索引来对结果做排序。
如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。
ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。
如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。
例如，表rental在列（rental_date，inventory_id，customer_id）上建有名称为rental_date的索引：
MySQL可以使用rental_date索引为下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序（filesort）操作：
此外，下面的情况也都使用索引做排序：
下面是一些不能使用索引做排序的查询：
注意到，最后一个带有迷惑性，需要比较区分。
此外，下面这种查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询，不能使用索引做排序：
在下面这个例子中，理论上是可以使用索引进行联接排序的，但由于优化器在优化时将film_actor表当作联接的第二张表，所以实际上无法使用索引：
冗余和重复索引
MySQL允许在相同列上创建多个相同的索引
上面的写法实际上在相同的列上创建了三个重复的索引，PRIMARY KEY、UNIQUE(ID)、INDECX(ID) 都是通过索引实现的，重复索引应该避免。
而冗余索引则不一样，在一些时候是可以容忍的。
如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，因此，索引（A，B）也可以当作索引（A）来使用（这种冗余只是对B-tree索引来说的）。
但是如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。
另外，如果新建的是其他不同类型的索引（例如，哈希索引或者全文索引），那么无论覆盖了哪些索引列，也不会是B-tree索引的冗余索引。
大多数情况下都不需要冗余索引，虽然建议尽量扩展已有的索引而不是创建新的索引，但有时候出于性能方面的考虑也需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那么性能可能会急剧下降。特别是当有查询把这个索引当作覆盖索引使用的时候。
例如，userinfo表的state_id上有索引，可以用于优化如下的查询（Q1）：
而对于下面的查询（Q2），由于索引没有覆盖多出的两个列，回表操作会导致性能不佳：
提升该查询性能的最简单办法就是扩展索引为（state_id，city，address），让索引能覆盖查询：
但是扩展索引后（原先的单列索引已经DROP了），Q2变快了，但是Q1却变慢了（索引大小变大了），如果我们想让两个查询都变得更快，就需要两个索引，但这样一来原来的单列索引就是冗余的了。
建两个索引的缺点是，会带来一定的维护成本。一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后达到了内存瓶颈的时候。
解决冗余索引和重复索引的方法很简单，删除这些索引就可以了，但首先要做的是找出这样的索引。可以针对INFORMATION_SCHEMA表编写各种复杂的查询来识别这类索引，也有更简单的技术，比如可以使用Percona工具箱中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复索引。
在删除或扩展索引的时候要非常小心。回忆一下，在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列（A）上的索引就相当于在（A，ID）上的索引。如果有像WHERE A=5 ORDER BY ID这样的查询，这个索引会很有用。但如果将索引扩展为（A，B），则实际上就变成了（A，B，ID），那么上面查询的ORDER BY子句就无法使用该索引做排序，而只能用文件排序了。所以，建议使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。
对于上述的两种情况，都可以考虑使用MySQL 8.0的不可见索引特性，而不是直接删除索引。要使用这个特性，可以通过ALTER TABLE语句，改变索引的一个标志位，使得优化器在确定执行计划时，忽略该索引。如果你发现计划删除的索引依旧有非常重要的作用，可以直接把索引改成可见，而不需要重新构建该索引。
未使用的索引
应该删除不会被使用到的索引，找到未使用索引的最好办法就是使用系统数据库performance_schema和sys。在sys数据库中，在table_io_waits_summary_by_index_usage视图中可以非常简单地知道哪些索引从来没有被使用过：
减少索引和数据的碎片
B-tree索引可能会产生碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。
根据设计，B-tree索引需要随机磁盘访问才能定位到叶子页，所以随机访问总是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多；对于索引覆盖扫描，这一点会表现得更加明显。
表的数据存储也可能发生碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。
- 行碎片（Row fragmentation）:这种碎片指的是数据行被存储在多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
- 行间碎片（Intra-row fragmentation）行间碎片是指逻辑上顺序的页或者行，在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
- 剩余空间碎片（Free space fragmentation）剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
可以通过执行
OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于那些不支持OPTIMIZE TABLE的存储引擎，可以通过一个不做任何操作（n o-o p）的ALTER TABLE操作来重建表。只需将表的存储引擎修改为当前的引擎即可：
