MYSQL 优化

以下一些执行非常快的示例：

SELECT COUNT(*) FROM tbl_name;
  SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
  SELECT MAX(key_part2) FROM tbl_name
    WHERE key_part1=constant;
  SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... LIMIT 10;
  SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

以下MySQL 只使用索引的情况：假定索引列是数值型的。

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; 
  SELECT COUNT(*) FROM tbl_name
    WHERE key_part1=val1 AND key_part2=val2; 
  SELECT key_part2 FROM tbl_name GROUP BY key_part1;

以下查询使用索引查询，并按照指定的排序字段顺序排序。

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... ;
  SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

对于单索引，范围索引值可以非常方便的转化为相应的WHERE条件，表示为范围查询条件。

单索引范围查询条件的定义如下：

• 包括BTREE和HASH索引，索引和常量比较，包括=, <=>, IN(), IS NULL, 或者 IS NOT NULL。
• 对于BTREE类型索引。索引和常量比较，包括 >, <, >=, <=, BETWEEN, !=, 及 <> 操作, 另外包括对比值为常量且不含通配符的 LIKE比较。
• 对于所有索引， OR 或 AND连接的条件

常量定义：

• 查询字符串常量
• 常数列或者系统表
• 非关联的子查询结果
• 任何由上述构成的表达式

如下，范围查询：

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;
 
SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);
 
SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

在优化器的常量传播过程中，非常亮值也可能会被转化为常量。

MySQL会尝试从WHERE条件提取范围查询条件。在提取过程中，无法组成范围查询的条件会被丢弃；形成重叠的会被组合，产生空范围的会被移除。

实际上，范围查询的条件限制较WHERE宽泛，MySQL 会针对范围条件进行一次额外的数据过滤。

范围提取算法可以处理同步程度的AND/OR 组合，并且，输出不依赖条件在WHERE中的顺序。

MySQL不支持空间索引多范围整合。为了解决这个问题，可以使用UNION 连接多个使用不同范围的相同SELECT语句。

多索引范围查询是但索引的扩展。限制数据结果在一个或多个索引条件范围内。

考虑如下多索引：key1(key_part1, key_part2, key_part3),

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件key_part1 = 1可以定义如下区间：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

这个区间包含第4、5、6条记录，可以工作范围查询。

相反，条件key_part3 = 'abc'无法定义唯一的区间，不能用于范围查询。

如下详细描述：

• HASH索引， 只能定义如下：

key_part1 cmp const1
  AND key_part2 cmp const2
  AND ...
  AND key_partN cmp constN;

cmp包括 =, <=>, 或者 IS NULL ，条件包含所有的索引部分（如包含三列的索引，则需要三个条件）例如，如下三部分HASH索引范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

• BTREE索引，AND/OR连接的系列关于索引部分的条件=, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, 或者 LIKE 'pattern' ('pattern'不能以通配符开始)。

当时用=, <=>, or IS NULL 时，优化器会尝试使用额外的键部分来处理查询范围；如果条件是 >, <, >=, <=, !=,<>, BETWEEN, 或者 LIKE，则不需要额外处理。如下不使用额外键条件的情景： 首先优化器使用第一个条件的= 。同时使用第二个条件的>= ，并不再使用条件三来组合范围查询：

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单个区间：

('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

创建的范围条件可能包含比初始条件跟多的数据行。

• OR 等价于UNION 组合；AND 等价于个范围条件的交集：

(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

范围为：

(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)

示例中，第一行的条件，左半部分只使用了索引一个部分，右半部分使用了两个部分；第二行则只使用了索引一个部分。执行计划中的key_len列信息，预示索引前缀使用的最大长度。

一些情况下，key_len表明使用的键并不是你期望的。例如，假如key_part1和key_part2可能为NULL，执行计划的key_len展示使用了两个键的长度：

key_part1 >= 1 AND key_part2 < 2

但是，实际上，查询条件会被转化为：

key_part1 >= 1 AND key_part2 IS NOT NULL

如下：假定col_name索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

只要col_name值等于范围中的任何一个值，条件就成立。优化器评估如下：

• 如果col_name是唯一索引，则范围评估结果只有一行数据。
• 其它非唯一索引情况根据范围条件评估。

根据index dive，优化器对各个范围逐一检索。例如， col_name IN (10, 20, 30)有三个等值范围，优化器对于每个范围执行两次检索，来生成行估计。

index dive能够提供精确的估计，但是，随着比较值的增多，预估需要花费优化器更多的时间。对于大数据量对比值估计可以使用索引统计数计以加快速度。

eq_range_index_dive_limit系统变量用以配置优化器切换使用不同预估方式的阈值。例如，最多可以使用index dive处理对比值数量N,则设置 eq_range_index_dive_limit =N+ 1，设置为0，则表明不使用索引统计信息。

使用ANALYZE TABLE更新表索引统计信息。

MySQL 8.0版本前，只有通过 eq_range_index_dive_limit 变量设置。MySQL 8.0，对于满足如下所有情景，可能跳过使用index dive。

• 单表检索。
• 单索引FORCE INDEX索引提示。如果是使用强制索引，使用index dive则也不能获取任何其它的信息。
• 非唯一索引，也不是全文索引。
• 没有子查询。
• 未使用DISTINCT,GROUP BY, 或者ORDER BY。

对于 EXPLAIN FOR CONNECTION，未使用index dive时，输入会有如下变化：

• 事务输出，rows及filtered值都是null。
• JSON 输出，没有rows_examined_per_scan和rows_produced_per_join出现，skip_index_dive_due_to_force为true，耗费计算是不准确的。

不使用FOR CONNECTION语句，在未使用index dive情况下，执行计划 EXPLAIN 输出不变。

未使用index dive情况下执行查询，相应的INFORMATION_SCHEMA.OPTIMIZER_TRACE 中的相关行会包括一条skipped_due_to_force_index 的index_dives_for_range_access。

优化器可以执行如下的范围扫描：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

如前所述，为了使用范围扫描，需要做如下转换：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

使用范围扫描需要满足如下条件：

• IN() 而不是 NOT IN().
• IN()左边只有列。
• IN()右边只有运行时常量（字面量或者常量列）。
• IN() 右边多余一个的 row constructor.

range_optimizer_max_mem_size系统变量：

• 0意为无限制。
• 大于0的值，优化器会跟踪范围查询的内存消耗，当查过设置值时，会改用其它诸如权标扫描等方法替代。这种情景不是优化所需要的，发生这种情景，我们可以看到如下警告信息（N代表 range_optimizer_max_mem_size）：
• 对于更新和删除操作，当退回到全表扫描并且启用sql_safe_updates ，就会引发错误，而不只是警告信息。因为实际上，没有使用键来决定更新记录。

Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.

对于此优化问题，可以通过调整 range_optimizer_max_mem_size来处理。为了估计范围查询需要的内存，需要执行如下指引：

• 如下查询，有一个候选键，组合OR条件，则会使用内存近230 bytes:
• 相似的使用组合AND条件，则会使用内存近125 bytes:
• 对于使用IN()的查询：

SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

这种情境下，会产生M×N.条件情景。

索引合并访问，使用多个范围扫描，然后合并查询结果为结果输出。这一方法只发生在单表查询。合并算法会产生并集，交集及交集的并集等。

如下，使用索引合并的实例：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
 
SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
 
SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;
 
SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

索引合并限制：

• WHERE 条件使用了复杂的AND/OR条件组合，MySQL 也没有选择最佳的计划，可以执行如下的等价变换。
• 不适用于全文索引。

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

索引合并在执行计划输出中展示位type列 index merge。在这种情景下，key列包含所有的使用索引，key_len展示包含最多使用索引部分的索引长度

索引合并算法：展示在Extra列中

• Using intersect(...)
• Using union(...)
• Using sort_union(...)

索引合并算法包括index_merge,index_merge_intersection,index_merge_union, 及index_merge_sort_union变量标志。默认情况下，所有的标志都是设置为on的，为了使用某一算法，则设置index_merge=off，同时设置相应的算法标志位on。

• Index Merge Intersection Access Algorithm
• Index Merge Union Access Algorithm
• Index Merge Sort-Union Access Algorithm

AND组合

• 包含索引所有部分的查询：
• Innodb的主键的范围查询

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

示例：

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;
 
SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

索引合并交集算法会同时执行所有的索引。

如果查询的列包含在索引列中，则不会访问表数据（执行计划Extra列包含 Using index提示）：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

OR组合查询条件：

• 包含索引所有部分的查询：
• 包含索引所有部分的查询：
• 交集算法

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
 
SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

OR组合的多个范围查询，单索引合并并集算法不适用的情况：

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;
 
SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

区别并计算法：排序并集算法在返回行前必须首先获取IDs，排序。

这一优化提升了非索引列和常量条件查询。这种情况下，查询将会在引擎层面执行。此优化只适用NDB引擎。

对于NDB 集群，这一优化可以避免返回不必要的数据行到服务器，加速查询。

示例如下，NDB集群表定义：

CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDB;

可以使用Condition pushdown优化的情景，非索引列的常量对比：

SELECT a, b FROM t1 WHERE b = 10;

执行计划结果显示Extra信息：

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition

如下两个查询不支持condition pushdown：

SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;

对于第一个查询，因为使用了索引列a做查询条件，索引查询更有效率。

第二个查询使用了非索引列条件，但是是非直接使用。如果要使用Condition pushdown，则需修改为b = 9。

Condition pushdown 使用情景包括：索引列使用>或<运算符条件：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition

其它：

• column [NOT] LIKE pattern。
• column IS [NOT] NULL
• column IN (value_list)

value_list必须是常量或者字面量

• column BETWEEN constant1 AND constant2

constant1和constant2必须是常量或者字面量。

Engine condition pushdown 默认启用，关闭设置： optimizer_switch：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

运行时设置：

SET optimizer_switch='engine_condition_pushdown=off';

限制：

• 只支持NDB 引擎。
• 列条件运算对比只能是常量或者常量表达式。
• 条件列类型不能是BLOB 或 TEXT 。
• 条件列和字符常量需要使用相同的排序规则
• Joins 是分别判断处理的。使用执行计划来观察使用。

Index Condition Pushdown (ICP) 即，使用索引条件查询时的Condition Pushdown 优化。如果不使用ICP，存储引擎会遍历索引定位数据表中数据，然后返回给MySQL 服务器。ICP机制，则判断，当部条件列覆盖部分索引，服务就就会将这部分条件下放到存储引擎进行处理。然后，存储引擎会对比索引与条件，只有当匹配时，才会从表中获取数据。ICP机制可以减少存储引擎访问数据表的次数及Mysql 服务器访问存储引擎的次数。

ICP应用场景：

• ICP 适用于权标扫描时的 range, ref, eq_ref和 ref_or_null访问方法。
• ICP适用于InnoDB和 MyISAM表，包括分区表。
• 对于InnoDB表，ICP 只适用于二级索引。ICP的目标值减少行读取，I/O操作。对于InnoDB聚簇索引（主索引），记录已经完全被读入InnoDB缓存，因此使用ICP 不会减少I/O操作。
• ICP 不支持建立在虚拟列上的二级索引。InnoDB支持虚拟列上建立二级索引。Cenerated Column：由其它列产生的列。virtual generated column：生成的列只保存在数据字典中，不会讲列的数据持久化磁盘中。Stored Generated Column：数据里的数据存储在磁盘上。（v5.7）

CREATE TABLE bought ( 

    id int(11) not null, 

    book_id int(11) not null, 

    price double DEFAULT 0,

    num double DEFAULT 0,

    amount DOUBLE GENERATED ALWAYS AS (price * num) VIRTUAL

);

• 子查询作为条件不适用ICP。
• 使用函数作为条件的不适用。
• 触发器作为条件不适用。

为了理解优化过程，在不使用ICP机制时，索引扫描执行过程如下：

1. 获取行数据：读取索引数组，然后定位读取表中整个数据行。
2. 匹配WHERE中的条件和读取的行数据，接受后丢弃数据。

使用ICP机制，查询执行过程：

1. 获取下一行的索引数组。
2. 匹配索引列是否满足WHERE中涉及的索引条件。
3. 如果条件满足，使用此索引数组定位读取整个数据行。
4. 匹配WHERE中剩余的条件，接受或丢弃读取的数据行。

使用ICP时，执行计划 Extra列会显示Using index condition提示，因为可能需要读取所有的表数据行，索引不显示Using index。

示例：宝航地址及姓名信息的表，索引INDEX (zipcode, lastname, firstname)。通过zipcode及模糊last name查询：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL可以使用索引条件zipcode='95054'扫描表数据行，条件第二部分 (lastname LIKE '%etrunia%')不能用来限制需要扫描的行，在不适用ICP情景下，需要扫描索引表中所有符合条件zipcode='95054'的数据行。

使用ICP时，基于索引列条件获取索引列数据，然后匹配astname LIKE '%etrunia%'条件，避免了一开始就读取所有的数据行。

ICP默认启用，配置：

SET optimizer_switch = 'index_condition_pushdown=off';//关闭 
SET optimizer_switch = 'index_condition_pushdown=on'; //启用

MySQL join算法：

• Nested-Loop Join Algorithm
• Block Nested-Loop Join Algorithm

nested-loop join (NLJ) 算法：首先从第一张表读取数据行，然后逐行循环处理下一张表数据，有多少张表联合，嵌套多少层。

如下：三表联合

Table   Join Type
t1      range
t2      ref
t3      ALL

NLJ算法执行如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

算法外部循环一次传一行数据到内部循环，因此每次都需要访问数据表读取数据（内外部）。

Nested-Loop (BNL)算法：使用缓存存储外部循环行数据，以减少需要访问表的次数。例如，外部一次读入10条记录到缓存，然后传递给内部循环，这样内部循环读取的的数据就可以一次和10条记录做匹配，极大的减少了内存循环读取表次数。

MySQL join 缓存有一下特点：

• Join buffering 应用场景： ALL ， index (没有可用的索引，需要全表扫描（索引或者全部数据）)或者 range联合类型。缓存对于外链接同样适用。
• 第一个非常量表部分join buffer，即使查询类型为 ALL 或者 index.
• 只有联合使用的列会存于join buffer，而不是整个列。
• join_buffer_size 系统变量限制每个查询可使用的缓存大小。
• 每个join会被分配使用一个buffer，一个查询可能包含多个join，因此可能使用多个buffer。
• join buffer在执行join操作之前分配，在查询结束后释放。

BNLJ：算法窒执行如下

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}
 
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

如下，S代表join buffer中的t1,t2各自的组合数，C代表buffer中所有的组合数，那么表t3需要扫描的次数：

(S * C)/join_buffer_size + 1

表扫描次数会随着join_buffer_size 变大而减少，当 join_buffer_size 足够大能够存储之前所有的行组合，再增大缓存将不会再有查询速度的提升。

MySQL对col_nameIS NULL 的优化和对col_name=constant_value的优化方式一样。如下：MySQL 可以使用索引和范围查询来处理IS NULL查询。

SELECT * FROM tbl_name WHERE key_col IS NULL;
SELECT * FROM tbl_name WHERE key_col <=> NULL;
SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

col_nameIS NULL 要求条件声明为NOT NULL，如果可能存在NULL值，那么就不会执行优化。

MySQL也可以优化类似col_name = expr OR col_name IS NULL这样的组合。这种形式通常见于子查询。通过执行计划可以观察到，当执行此类优化时，会有ref_or_null 提示信息。

优化可以对索引的任何部分执行IS NULL条件优化。

如下：可以执行优化的一些查询情景，表t2中列a 和 b上含有索引：

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null ：首先根据相关key查询记录，然后执行查询索引为NULL的数据。

优化只能执行一层IS NULL 优化。如下，MySQL只能使用(t1.a=t2.a AND t2.a IS NULL)执行查询，而无法适使用列b：

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

• 使用索引排序
• 使用文件排序

一些情境下，MySQL会主动使用索引来满足ORDER BY，避免使用文件排序filesort。

即使ORDER BY所使用的列和索引不完全匹配，只要未使用的索引部分和额外的条件列是常量，也会使用索引排序。如果索引未包含查询中所有的列，MySQL只有在使用索引代价更小的时候使用索引。

假定有索引存在于(key_part1, key_part2)上。如下查询，是否使用索引排序，取决于读取非索引列不同方式的效率。

• In this query, the index on(key_part1, key_part2)enables the optimizer to avoid sorting:

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

查询中SELECT *查询的列数量可能包括key_part1和key_part2之外的列。这种情况下，扫描整个索引查找表所有的数据来查询需要查询的非索引列，可能比单单全表扫描然后排序结果成本更高。这样优化器就不会再使用索引。如果SELECT *只包含索引列，那么查询就只会使用索引，结果也将不用再排序。

如下，使用索引查询排序：

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

• 如下查询，key_part1是常量，所以查询结果会以key_part2顺序。(key_part1, key_part2)上存在索引，当条件列具有一定的选择性，使得索引范围查询比全包扫描更有效率，就可以使用索引顺序，避免排序：
• 以下包含DESC的查询，优化判断如上：
• 如下查询，ORDER BY中并没有使用key_part1，但是选择的列中有key_part1常数列，所以仍然会使用索引：

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;
 
SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;
SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

一些情境下，即使查询使用了索引来获取满足条件的数据，也不能使用索引排序。如下：

查询使用了另外的索引列（和查询的列不同）作为排序列：

SELECT * FROM t1 ORDER BY key1, key2;

查询使用了不相邻的索引部分：

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

查询混用了ASC和DESC:

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

查询使用的索引和排序使用的索引不同：

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

• ORDER BY后使用的表达式：
• 多表联合，ORDER BY中的列不是来自第一个联合的非常量类型表（执行计划中排在第一位的，联合类型为非 const 的表）。
• ORDER BY和GROUP BY使用不同的表达式。
• 部分索引不能替代文件排序。
• 索引未排序，如对MEMORY引擎的HASH索引。

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

基于列表达式的别名，不可使用索引排序。如下，a为ABS（a）的别名，列a上的索引不可用于排序：

SELECT ABS(a) AS a FROM t1 ORDER BY a;

如下，ORDER BY 中的列不再select 中，但是a是t1中的列，可以使用索引排序：

SELECT ABS(a) AS b FROM t1 ORDER BY a;

默认情况下，MySQL会对GROUP BY col1, col2, ...进行排序，就好象使用了ORDER BY col1, col2, ...。即使严格声明了ORDER BY，MySQL也会优化掉，不会对查询速度有任何影响。

对于包含GROUP BY的查询，如果要避免排序，则需要使用ORDER BY NULL。如下：

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器仍然会选择使用排序来完成分组。ORDER BY NULL会避免对结果进行排序，而不是处理GROUP BY的排序。

Note

GROUP BY使用默认排序。如果需要确定的排序，则使用ORDER BY条件。

当索引无法满足排序，MySQL会执行filesort文件排序，读取表记录然后排序。Filesort文件按排序构成了查询中的一次额外的排序阶段。

为了获取执行filesort操作的内存，优化器会预先分配 sort_buffer_size 设定大小的内存。单个会话会根据实际需求修改此变量的值，或者动态的分配内存。

filesort文件排序操作，当内存不足时，会使用磁盘空间。一些类型的查询会使用完全的基于内存的文件排序，如下：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

查询使用LIMIT，限制查询条目。

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

对于未使用ORDER BY的慢查询，可以试着减少max_length_for_sort_data 系统变量来触发文件排序，system variable to a value that is appropriate to trigger afilesort. (max_length_for_sort_data 设置较大的值适合于高吞低CPU活跃的查询)

要提高排序速度，首先检查是否可以使用索引排序，如果不能，则执行如下步骤：

• 增加 sort_buffer_size 变量值，以足够容纳下整个结果集在内存中排序（避免磁盘读写）。但至少为15个临时磁盘文件存储大小。

考虑需要排序的列大小：max_sort_length。sort_buffer_size 需要随着变化。

• 增加read_rnd_buffer_size 变量，以一次可以读取更多的行。
• 将tmpdir 变量指向有足够空间的文件系统。比变量可以指定多个路径，轮询使用。分隔符Unix使用(:)，Windows使用 (;)。不同路径需要放置在不同的磁盘上，而不要使用同一个磁盘上的不同分区。

使用执行计划检验是否可以使用索引排序。查看Extra列是否包含Using filesort。

执行filesort时，会有如下输出：filesort_summary

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": ""
}

• : 排序sort key，读取表 rowid。
• : 排序缓存中包含排序的key和相关的查询列。
• :和如上的指示，相关的列会以紧凑的方式存储。

执行计划无法不说明文件排序是否在内存中执行。可以通过优化器输出，查看filesort_priority_queue_optimization变量。

DISTINCT结合ORDER BY大多数情况下需要使用临时表。因为DISTINCT可能使用GROUP BY。

多数情况下，DISTINCT可以等价于特殊的GROUP BY。例如，如下两个查询：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;
 
SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

此种情况下，适用GROUP BY 的优化也适用于DISTINCT。

当DISTINCT结合LIMIT row_count时，MySQL会在查找到在足够的记录数时就会立马停止查询。

当使用的列包含在所有表中，当在一个表中找到记录时，就会终止读取其它的表。如下

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

LIMIT用户查询特定数量的结果集：

• 可以使用索引时，会索引优先。
• row_count结合ORDER BY情况下，MySQL会在查找需要的记录时立刻停止排序。使用索引排序非常快。使用文件排序时，满足条件的记录会被全部读取，在找打满足limit 条件的记录前，所有记录都会被排序。余下的将不会再被排序。
• LIMIT row_count结合DISTINCT时，找到即停止。
• 一些情况下，GROUP BY可以通过索引计算获得结果。
• 在MySQL将客户端需要的行数据发送完后，就会停止查询，除非使用了SQL_CALC_FOUND_ROWS，这种情况下，可以通过使用SELECT FOUND_ROWS()查看返回了多少行。
• LIMIT 0会立马返回空集。可以用于检验查询合法性；获取结果列元数据；mysql 客户端可以通过--column-type-info 选项展示结果列类型。
• 如果服务器需要使用临时表，则服务器使用LIMIT row_count条件来估算需要的内存空间。
• 使用Limit 而不使用ORDER BY时，优化器可以使用基于内存的文件排序。

执行计划 type列显示all，则表明为全表扫描，会出现这种描述的情景包括如下：

• 表很小，全表扫描比使用索引更快，更有效。
• 对于on或者where中的索引条件列没有可用的界定限制条件。
• 索引条件列使用了常量比较值，而常量值会覆盖到表中的大部分数据，效率不如权标扫描高。
• 使用的条件列cardinality 低，使用系列查询会比全表扫描效率低。

小表全表扫描效率比较高，大表要避免全表扫描：

• 使用ANALYZE TABLE tbl_name更新优化表的键分布。
• 使用FORCE INDEX，强制使用某些索引：
• mysqld启动使用 --max-seeks-for-key=1000 选项，或者设置max_seeks_for_key=1000命令，来告知mysql服务器最大索引扫描量。.

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;