MySQL索引优化相关原理

分手后的思念是犯贱 2023-10-07 14:47 27阅读 0赞

## 1. 前言 ##

以下对MySQL索引的优化相关原理进行了整理，分析MySQL索引使SQL语句执行加速的原理，针对使用InnoDB 5.6版本的MySQL。

关于索引条件下推的概念，可参考“MySQL如何执行查询语句”，有关于MySQL Server层与存储引擎的关系。

## 2. MySQL索引优化相关原理 ##

### 2.1. MySQL索引的作用及注意事项 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/optimization-indexes.html 。

提升SELECT操作性能的最佳方法，是在查询中检测的一个或多个列上创建索引。索引条目类似指向数据库表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。

尽管为查询中使用的每个可能的列创建索引是可以实现的，但不必要的索引使MySQL确定要使用的索引时浪费空间和时间。索引还会增加插入，更新和删除的成本，因为每个索引都需要被更新。必须找到适当的平衡，使用最佳索引集来实现快速查询。

在后续内容中有索引对插入操作影响的对比验证。

### 2.2. MySQL如何使用索引 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html 。

MySQL会使用索引进行这些操作：

*  快速查找匹配WHERE子句的行；
 *  从待考虑范围消除行；
 *  多列索引可以进行最左匹配；
 *  执行join时从其他表中检索行;
 *  查找特定索引列的MIN()或MAX()值；
 *  用于对表执行排序或分组操作，如果排序或分组在可用的最左匹配索引上完成；
 *  在某些情况下，查询可以优化为仅检索键值而无需查询数据行。为查询提供所有必要结果的索引称为覆盖索引。

### 2.3. B+树索引支持的表达式 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/index-btree-hash.html 。

B+树索引支持的比较表达式包括： =， >， >=， <， <=， BETWEEN，以及不使用通配符开头的LIKE。

### 2.4. 索引的选择性与基数 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html 。

MySQL在使用索引时，如果存在多个可以选择的索引，**MySQL通常会使用选择性最高的索引**。

参考 https://dev.mysql.com/doc/refman/5.6/en/glossary.html\#glos\_cardinality 。

基数（cardinality）指在表的列中不同值的数量。当查询具有与索引相关的列时，每列的基数会影响哪种访问方法最有效。

如果列中的值具有非常不均匀的分布，则基数可能不是确定最佳查询计划的好方法。可能需要使用索引提示，来传递对于特定查询更有效的查询方法的建议。

也可参考 https://dev.mysql.com/doc/refman/5.6/en/glossary.html\#glos\_selectivity 关于selectivity的说明。

### 2.5. 优化器与索引选择 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/where-optimization.html 。

MySQL执行的优化包括：查询表的每个索引，并 \*\* 使用最佳索引 \*\* ，除非优化器认为使用全表扫描更有效。在以前，是否使用全表扫描取决于最佳索引是否覆盖表的30%以上的数据。现在以上固定百分比已不再适用，现在的优化器更加复杂，在进行预估时基于其他因素，例如表大小，行数和I/O块大小等。

### 2.6. 索引统计数据收集 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/index-statistics.html 。

存储引擎会收集表相关的统计信息，以供优化器使用。表统计信息基于值组（value groups），值组是一组键前缀值相同的行（可理解为索引中相同的数据）。出于优化的目的，值组的平均大小是重要的统计数据。

当某个索引的值组平均大小增加（即相同数据增加）时，索引的使用价值变低，因为每次查找的平均行数增加。为了使索引更好地用于优化，最好的情况是每个索引值指向表中的少量行。当某个索引值对应大量行时，该索引的作用不大，MySQL不太可能使用它。

值组的平均大小与表的基数有关，基数即值组的数目。SHOW INDEX语句显示基于N/S的基数值，其中N是表中的行数，S是值组的平均大小。

参考 https://dev.mysql.com/doc/refman/5.6/en/show-index.html ， “SHOW INDEX FROM 表名”语句，可以显示指定表的索引信息，返回的Cardinality字段为索引的基数，即索引中唯一值的估计数量，

#### 2.6.1. NULL值与索引统计数据收集 ####

对于InnoDB和MyISAM表，可以分别通过innodb\_stats\_method和myisam\_stats\_method系统变量来控制表统计信息的收集。这些变量具有三个可能的值，如下所示：

*  nulls\_equal

当以上变量值设置为nulls\_equal时，所有NULL值都被视为相同的（即NULL值全部组成一个值组）。

如果NULL值组大小远大于非NULL值组的平均大小，则使用nulls\_equal会使平均值组大小偏大。这使得对非NULL值进行连接时，优化器会低估索引的实际作用。因此，使用nulls\_equal可能导致优化器在连接类型为ref时，应当使用索引却不使用。

*  nulls\_unequal

当以上变量值设置为nulls\_unequal时，NULL值不再被视为相同的值。每个NULL值形成一个单独的大小为1的值组。

如果有许多NULL值，使用nulls\_unequal会使平均值组的大小偏小。如果非NULL值组的平均大小很大，则将每个NULL值作为一组大小1进行计数，会导致优化器高估在连接查找非NULL值时使用索引的作用。因此，使用可能导致优化器在连接类型为ref时，存在其他可能更优的方法却使用索引（不应该使用索引却使用）。

*  nulls\_ignored

当以上变量值设置为nulls\_ignored时，NULL值会被忽略。

**innodb\_stats\_method系统变量的默认值为nulls\_equal。**

#### 2.6.2. 收集表统计信息 ####

可以强制地显式收集表统计信息，MySQL也可能会自动收集统计信息。

当需要强制收集表的统计信息时，可以按照以下步骤执行（针对InnoDB）：

*  修改innodb\_stats\_method系统变量值，该变量值是一个全局值，在修改时需要有SUPER权限（如root用户），修改命令示例为：“set @@global.innodb\_stats\_method=‘nulls\_equal’;”；
 *  更新表数据，使期统计信息过时，例如插入一行然后再删除；
 *  执行“ANALYZE TABLE”语句。

以上步骤1、2的顺序可调换，不影响最终结果。

参考 https://dev.mysql.com/doc/refman/5.6/en/analyze-table.html 。

“ANALYZE TABLE 表名”语句对指定的表执行键分布分析，并存储分布数据。

执行“ANALYZE TABLE”语句时，如果与上次键分布分析相比该表未被，则不会再次分析该表。

MySQL使用存储的键分布来决定表连接顺序，以进行除常量以外的连接。此外， **键分布还可用于决定对查询中的特定表使用哪些索引。**

对于InnoDB表，ANALYZE TABLE通过以下方式确定索引基数：对每个索引树执行随机深入，并相应地更新索引基数估计值。因为这些只是估计，所以重复运行ANALYZE TABLE可能会产生不同的数字。这使得在InnoDB表上进行ANALYZE TABLE的速度很快，但由于没有考虑所有行，因此不是完全准确。

MySQL在联接优化中使用索引基数估计值。如果连接没有以正确的方式优化，请尝试运行ANALYZE TABLE语句。在少数情况下，ANALYZE TABLE不能为特定的表提供足够好的值，可以在查询中使用FORCE INDEX来强制使用特定的索引。

### 2.7. 优化InnoDB查询 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-queries.html 。

为了优化InnoDB查询，可以遵循以下InnoDB索引准则。

*  每个InnoDB表都有一个主键，需要为每个表指定一组主键列，这些列应该是用于最重要且要求快速完成查询的列；
 *  不要在主键中指定太多或太长的列，因为这些主键列值在每个二级索引中都会重复。当索引包含不必要的数据时，用于读取数据的I/O和用于对其进行缓存的内存增加，会降低MySQL服务器的性能和可伸缩性；
 *  不要为每个列创建单独的二级索引，因为 **每个查询只能使用一个索引** 。很少使用的列或只有几个不同值（即数据区分度或离散度低）的列上的索引可能对查询没有帮助。如果对同一个表有很多查询，请尝试创建少量的联合索引，而不是大量的单列索引。如果一个索引包含结果集所需的所有列（称为覆盖索引），则查询可能完全避免读取表数据；
 *  如果索引列不能包含任何NULL值，则在创建表时将其声明为NOT NULL。当优化器知道每一列是否包含NULL值时，可以更好地确定哪个索引对查询最有效。（参考前文，MySQL对NULL值进行索引统计数据收集时包含不同的方式）

### 2.8. 覆盖索引 ###

覆盖索引需要满足以下条件：查询语句中的需要返回的字段，及查询条件中需要判断的字段，均包含在同一个索引中。二级索引中包含聚簇索引对应的列，二级索引+聚簇索引也可以当做一个索引看待。

当通过InnoDB的二级索引查找某行的数据时，需要根据二级索引中包含的聚簇索引，从聚簇索引的B+树获取对应的整行数据，这个过程通常称为回表。使用覆盖索引时，可以避免回表，提高查询效率。

后续内容有对回表及使用覆盖索引的查询效率进行对比验证。

参考 https://dev.mysql.com/doc/refman/5.6/en/glossary.html\#glos\_covering\_index 。

覆盖索引（covering index）指一个索引中包含查询检索的所有列。该查询不使用索引值作为指针查找完整表行，而是从索引结构返回相应值，从而节省了磁盘I/O。InnoDB可以将这种优化技术应用于比MyISAM更多的索引，因为InnoDB二级索引还包括主键列。InnoDB不能将这种技术应用于对事务修改的表的查询，直到事务结束才可以。

给定正确查询时，任何单列索引或联合索引都可以充当覆盖索引。设计索引和查询时，应尽可能利用覆盖索引优化技术。

### 2.9. 避免全表扫描 ###

当进行全表扫描时，直接访问聚簇索引中包含的整行数据。当使用二级索引查询时，查询到二级索引后可能需要再查询对应的聚簇索引中包含的整行数据。当MySQL认为全表扫描比使用索引查询更合适时，会使用全表扫描而不使用二级索引进行查询。

参考 https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html 。

对于小表查询，或查询处理了大部分行的大表，索引不是那么重要。当查询需要访问大部分行时，顺序读取比处理索引快。顺序读取可以最大程度地减少磁盘查找，即使查询不需要所有行。

参考 https://dev.mysql.com/doc/refman/5.6/en/table-scan-avoidance.html 。

当EXPLAIN输出的type列显示ALL时，说明MySQL使用全表扫描处理查询。全表扫描通常会在以下情况下出现：

*  表太小以至于执行全表扫描比通过索引键查找要快得多。对于少于10行且每行长度较短的表，这是常见的；
 *  ON或WHERE子句中没有可用的索引列；
 *  将索引列与常量值进行比较，且MySQL已（基于索引树）计算得出常量覆盖了表的很大部分，并且全表扫描会更快；
 *  通过另一列使用基数较低的索引键（许多行与键值匹配）。在这种情况下，MySQL假定通过使用该索引键，可能会需要执行许多键查找，全表扫描可能会更快。

对于小表，全表扫描通常是合适的，并且对性能的影响可以忽略不计。对于大表，请尝试以下方法，以避免优化器错误地选择全表扫描：

*  使用ANALYZE TABLE语句更新需要扫描的表的键分布；
 *  对扫描的表使用FORCE INDEX，强制MySQL使用指定的索引；
 *  将max\_seeks\_for\_key参数设置为较小的值，可以强制MySQL选择索引而不是全表扫描。

参考 https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html\#order-by-index-use ，如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法成本更低时，才会使用索引（即有可能不使用索引）。

### 2.10. 联合索引 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html 。

MySQL可以创建联合索引（composite indexes），即多列索引（multiple-column indexes）。一个索引最多可以包含16列。对于某些数据类型，可以对列的前缀进行索引。

MySQL可以将多列索引用于检测索引中所有列的查询，或者仅测试第几列的查询。如果以正确的列顺序定义索引，则单个联合索引可以对一个表的多种查询进行加速。

多列索引可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。

如果col1和col2列存在单独的单列索引，则优化器将尝试使用索引合并（index merge）优化，或通过确定哪个索引排除更多行，来找到限制性最强的索引，并使用该索引来获取行。

如果表具有多列索引，则优化器可以使用索引的任意最左前缀来查找行。

如果查询时列无法构成索引的最左前缀，则MySQL无法使用索引进行查找。

例如，在(col1, col2, col3)上有一个包含三列的多列索引，则对(col1)，(col1, col2)和(col1, col2, col3)列都可以使用索引进行查找，(col2)，(col2, col3)列无法使用索引进行查找。

### 2.11. 唯一索引 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/create-index.html\#create-index-unique 。

唯一索引（unique index）会创建一个约束，使索引中的所有值都必须不同。如果尝试添加键值与现有行匹配的新行，则会发生错误。如果为唯一索引中的列指定前缀值，则列值在前缀长度内必须唯一。唯一索引允许可为NULL的列包含多个NULL值。

参考（阿里巴巴）Java开发手册，对于唯一索引的说明如下：

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

后续内容会对唯一索引与非唯一索引插入速度进行对比验证。

### 2.12. NULL值比较 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/working-with-null.html 。

NULL表示“一个缺失的未知值”，且与其他值的处理方式有所不同。

使用IS NULL和IS NOT NULL运算符可以判断是否为NULL值。

不能使用算术比较运算符（例如=，<或<>）检测NULL。即使用大于、等于、不等于等条件，无法获取NULL值。

由于任何与NULL进行算术比较的结果均为NULL，因此无法从此类比较中获得任何有意义的结果。

在MySQL中，0或NULL表示false，其他表示true。布尔运算的默认真值是1。

NOT NULL字段中可以插入0或字字符串’’，0与’'均非NULL。

参考 https://dev.mysql.com/doc/refman/5.6/en/problems-with-null.html 。

使用DISTINCT，GROUP BY或ORDER BY时，所有NULL值均视为相等。

使用ORDER BY时，将首先显示NULL值，如果指定DESC以降序排序，则最后显示NULL值。

汇总函数，例如COUNT()，MIN()和SUM()等，会忽略NULL值。例外情况是COUNT(\*），它计算行而不是单个列的值。

参考 https://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html\#function\_isnull ，还可以使用ISNULL(expr)方法判断是否为NULL。

### 2.13. 在两个索引键进行搜索 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/searching-on-two-keys.html 。

使用单键的OR操作得到了很好的优化。

棘手的情况是使用OR来搜索两个不同的键。示例如下所示：

SELECT field1_index, field2_index FROM test_table
    WHERE field1_index = '1' OR  field2_index = '1'

使用索引合并可以解决以上问题。

也可以使用UNION将两个单独的SELECT语句的输出进行合并来有效解决以上问题。每个SELECT仅搜索一个索引键，可以对以上问题进行以下优化：

SELECT field1_index, field2_index
        FROM test_table WHERE field1_index = '1'
    UNION
    SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

在两个索引键进行搜索可能导致索引失效，在后续内容会进行对比验证。

### 2.14. 索引合并 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html 。

索引合并（index merge）访问方法通过多个范围扫描检索行，并将结果合并为一个。此访问方法仅合并来自单个表的索引扫描，不跨多个表扫描。索引合并可以生成基础扫描的并集（unions），交集（intersections）或交集的并集（unions-of-intersections）。

在EXPLAIN输出中，索引合并方法在type列中显示为index\_merge。在这种情况下，key列包含使用的索引列表，key\_len列包含这些索引的最长索引键长度列表。

索引合并访问方法有几种算法，显示在EXPLAIN输出的Extra字段中：

*  Using intersect(…)
 *  Using union(…)
 *  Using sort\_union(…)

索引合并的使用取决于optimizer\_switch系统变量的index\_merge，index\_merge\_intersection，index\_merge\_union和index\_merge\_sort\_union标志的值。默认情况下，所有这些标志都为打开。

参考 https://bugs.mysql.com/bug.php?id=77209 ，MySQL的编号为\#77209的Bug为“Update may use index merge without any reason (increasing chances for deadlock)”，描述为在某些情况下，当两个索引都预期检索1行时，UPDATE操作在查询时使用索引合并，该行为增加了产生死锁的可能性。

建议的修改方法为当单个索引足够好时，不要使用索引合并。尽量避免在UPDATE中使用索引合并，以防引发死锁。

### 2.15. 索引提示 ###

当MySQL优化器使用的索引并不是最优时，可以使用索引提示指定需要使用或不使用的索引。

参考 https://dev.mysql.com/doc/refman/5.6/en/index-hints.html 。

索引提示（index hints）为优化器提供了在进行查询时如何选择索引的信息。索引提示仅适用于SELECT与UPDATE语句。

USE INDEX（index\_list）提示告诉MySQL只使用其中一个命名索引来查找表中的行。IGNORE INDEX（index\_list）告诉MySQL不使用某些特定索引或索引。如果EXPLAIN显示MySQL正在使用可能索引列表中的错误索引，这些提示可以发挥作用。

FORCE INDEX与USE INDEX的作用类似。

索引提示在表名后指定。USE INDEX/FORCE INDEX之后可以指定索引列表，IGNORE INDEX之后可以指定单个索引。

每个提示都需要指定索引名称，而不是列名称。如果需要指定主键，请使用名称PRIMARY。

索引提示可以针对连接、排序或分组，语法为USE INDEX或IGNORE INDEX后指定“FOR \{JOIN|ORDER BY|GROUP BY\}”。

### 2.16. 索引条件下推 ###

当不启用索引条件下推时，存储引擎会通过索引读取不满足WHERE条件的行数据并返回给MySQL Server层，由MySQL Server层进行过滤。

当启用索引条件下推时，存储引擎在通过索引读取行数据时，对于不满足WHERE条件的行不会读取，不会返回给MySQL Server层。

参考 https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html 。

索引条件下推（Index Condition Pushdown，ICP）是针对MySQL使用索引从表中检索行时的一种优化。

如果不启用索引条件下推，则存储引擎将遍历索引以在表中定位行，并将行返回给MySQL服务器，MySQL服务器会评估这些行的WHERE条件。

启用索引条件下推后，如果部分WHERE条件仅使用索引中的列就能评估，则MySQL服务器会将WHERE条件的这一部分下推到存储引擎。存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。

索引条件下推可以减少存储引擎必须访问表的次数，以及MySQL服务器必须访问存储引擎的次数。

关于不启用及启用索引条件下推的示意图如下所示，图片来源： https://mariadb.com/kb/en/index-condition-pushdown/ 。

不启用索引条件下推如下所示，进行查询时需要读取索引对应的表记录：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ_size_16_color_FFFFFF_t_70_pic_center]

启用索引条件下推如下所示，进行查询时只需要读取满足查询中部分WHERE条件的索引对应的表记录：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ_size_16_color_FFFFFF_t_70_pic_center 1]

索引条件下推优化的适用性取决于以下条件：

*  当需要访问整个表的行时，索引条件下推用于range，ref，eq\_ref和ref\_or\_null访问方式；
 *  索引条件下推可以用于InnoDB和MyISAM表；
 *  对于InnoDB表，索引条件下推仅用于二级索引；索引条件下推的目标是减少全行读取的次数，从而减少I/O操作。对于InnoDB聚簇索引，完整的记录已被读入InnoDB缓冲区。在这种情况下使用索引条件下推不会减少I/O；
 *  使用子查询的条件无法下推；
 *  涉及存储函数的条件无法下推；存储引擎无法调用存储的函数；
 *  触发条件无法下推。

索引条件下推默认启用。可以通过设置optimizer\_switch系统变量的index\_condition\_pushdown标志来控制索引条件下推是否启用。

### 2.17. 使用索引进行排序 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html\#order-by-index-use 。

#### 2.17.1. 可以使用索引进行排序的情况 ####

某些情况下，MySQL可以使用索引来满足ORDER BY子句，并避免执行filesort操作时涉及的额外排序。

即使ORDER BY与索引不完全匹配，也可以使用索引（进行排序），只要索引的所有未使用部分和所有额外的ORDER BY列在WHERE子句中都是常量即可。

假设在(key\_part1, key\_part2)上有一个索引，以下查询可以使用该索引来解决ORDER BY部分。优化器是否使用索引来解决ORDER BY，取决于假如还必须读取索引中不存在的列，读取索引是否比全表扫描更有效。

*  当排序字段与索引字段一致时，(key\_part1, key\_part2)列上的索引使优化器避免排序。查询语句示例如下所示；

SELECT * FROM t1 ORDER BY key_part1, key_part2;

但是，以上查询使用了SELECT \*，这可能会查询比key\_part1和key\_part2更多的列。在这种情况下，扫描整个索引并查找表行以找到索引中未包含的列，可能比全表扫描并对结果排序代价更大。如果是这样，优化器可能不会使用索引。如果SELECT \*仅选择索引列，则将使用索引并避免排序。

*  当联合索引中前面的字段作为查询条件且为等于常量时，通过该索引进行访问时，结果中所有行的顺序与联合索引中后面的字段顺序一致。如果WHERE子句的选择性足以使索引范围扫描比全表扫描代价更小，则在(key\_part1, key\_part2)列上的索引可以避免排序。查询语句示例如下所示；

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

*  当排序字段的所有排序顺序均为降序时，与均为升序时情况类似（当排序字段的所有排序顺序相同时，可以使用索引进行排序）；
 *  当联合索引中前面的字段（或单列索引）作为查询条件且为范围查询，且该字段作为排序条件时，如果WHERE子句的选择性足以使索引范围扫描比全表扫描代价更小，则该索引会被使用。查询语句示例如下所示；

SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
    
    SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;

#### 2.17.2. 无法使用索引进行排序的情况 ####

在某些情况下，MySQL可以使用索引来查找与WHERE子句匹配的行，但不能使用索引来解决ORDER BY。示例如下：

*  查询时ORDER BY使用了不同的多个索引。查询语句示例如下所示；

SELECT * FROM t1 ORDER BY key1, key2;

*  查询对索引的非连续部分使用ORDER BY。查询语句示例如下所示；

以下示例，索引在(key1\_part1, key1\_part2, key1\_part3)列上。

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

*  查询ORDER BY混合了升序与降序。查询语句示例如下所示；

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

*  WHERE子句指定的列对应的索引，与ORDER BY指定的列对应的索引不同。查询语句示例如下所示；

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

*  查询将ORDER BY与表达式结合使用，该表达式包含除索引列名称以外的项。查询语句示例如下所示；

SELECT * FROM t1 ORDER BY ABS(key);
    SELECT * FROM t1 ORDER BY -key;

*  查询连接了许多表，并且ORDER BY中的列并非全部来自用于检索行的第一个非常量表（即EXPLAIN输出中的第一个没有const连接类型的表）；
 *  查询中的ORDER BY与GROUP BY表达式不同；
 *  ORDER BY子句中的列对应的索引仅在该列的前缀上。在这种情况下，索引不能用于完全解决排序顺序。例如，如果仅索引CHAR(20)列的前10个字节，则索引无法区分第10个字节之后的值，因此需要进行filesort；

#### 2.17.3. ORDER BY与GROUP BY ####

默认情况下，MySQL对GROUP BY col1, col2, …查询进行排序，就好像在查询中还包括了ORDER BY col1, col2, …一样。如果指定一个包含相同列列表的显式ORDER BY子句，则MySQL会对其进行优化，而不会造成任何速度损失，尽管排序仍然会发生。

如果查询包含GROUP BY，但希望避免对结果进行排序的开销，则可以通过指定ORDER BY NULL来禁止排序。

#### 2.17.4. 使用filesort进行排序（无法使用索引排序） ####

如果不能使用索引来满足ORDER BY子句，则MySQL执行filesort操作，该操作将读取表行并排序。filesort构成查询执行中的额外排序阶段。

为了获得用于filesort操作的内存，优化器会预先分配固定数量内在，大小为sort\_buffer\_size变量对应的字节数。

如果结果集太大而无法容纳在内存中时，则filesort操作将根据需要使用临时磁盘文件。

某些类型的查询特别适合在内存中完成filesort操作，例如查询时使用limit限定仅返回大量结果集中的少部分行。

参考《High Performance MySQL, 3rd Edition》。

当MySQL无法使用索引（存储引擎）产生排序的结果时，必须（由MySQL Server层的执行引擎）对行进行排序。可以在内存或磁盘上执行此操作，即使实际上并没有使用文件，也总是将此过程称为filesort。

如果要排序的值适合使用排序缓冲区，则MySQL可以使用快速排序完全在内存中执行排序。如果MySQL无法在内存中进行排序，则将在磁盘上对数据进行分块排序。使用快速排序对每个块进行排序，然后将排序后的块合并到结果中。

## 3. 参考资料 ##

以上参考的资料如下：

https://dev.mysql.com/doc/refman/5.6/en/

https://dev.mysql.com/doc/internals/en/

《High Performance MySQL, 3rd Edition》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200818132442553.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20200818132455267.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E4MjUxNDkyMQ==,size_16,color_FFFFFF,t_70#pic_center