Mysql优化（出自官方文档） - 第一篇（SQL优化系列）

Mysql优化（出自官方文档） - 第一篇

目录

• Mysql优化（出自官方文档） - 第一篇
  • 1 WHERE Clause Optimization
  • 2 Range Optimization
    • • Skip Scan Range Access Method
  • 3 Index Merge Optimization
    • • Index Merge Intersection Access Algorithm
      • Index Merge Union Access Algorithm
      • Index Merge Sort-Union Access Algorithm
  • 4 Engine Condition Pushdown Optimization
  • 5 Index Condition Pushdown Optimization(ICP)

1 WHERE Clause Optimization

1.1 从MySQL8.0开始，当where后面的数字超过类型范围的时候，将自动转换为where 1， 如：

# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
  SELECT * FROM t WHERE c ≪ 256;
-≫ SELECT * FROM t WHERE 1; /*自动转换为where 1*/

1.2 当COUNT(*)的时候，对于MyISAM或者其他内存数据库，结果将直接从表信息中获取，不用检索数据；

1.3 在一个多表查询中，所有的const table都被优先读出来，const table的定义如下：

• 一个空的表或者只有一行数据的表

• 在where中，限定primary key或者unique index等于一个常量值，如下面所示：

  SELECT * FROM t WHERE primary_key=1;
  SELECT * FROM t1,t2
    WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
  

1.4 在一个join中，当order by和group by的目标列都出自同一张表，那么这张表在join中会被优先当做第一张表来处理。

1.5 如果说order by 和group by的目标列不同，或者order by 和group by的目标列来自于多张表，而不是join队列里面的第一张表时，那么一个临时表会被创建。

1.6 当使用SQL_SMALL_RESULT（在Mysql中成为查询提示符）标识符的时候，那么Mysql会在内存中创建一个临时表，如：

select SQL_SMALL_RESULT a.id, count(1) from t1 a straight_join t2 b on a.id= b.id1 group by a.id 

1.7 曾经的Mysql在决定使用index还是table scan时，采取的判断依据是最好的index是否覆盖超过30%的表数据，但是现在不这样做了，现在的判断因素比较多，会根据表size，行数和I/O block size等因素来判断。

2 Range Optimization

• 对于range查找的定义

  分为两种，分别为HASH和BTREE索引：

  • 对于HASH索引，当条件里出现=, <=>, IN, IS NULL, IS NOT NULL
  • 对于BTREE索引，当条件里出现>, <, >=, <=, BETREEN, !=, <>, LIKE时，需要注意的是，对于LIKE，不能以通配符开头。

• 对于单列的range查找(Range Access Method for Single-Part Indexes)

  Mysql首先会将where后面的条件尝试优化为单纯的range查找，下面的操作将会被执行。

  • 不能构造range的条件将会被去掉
  • 产生空集的条件被删掉。

  如下面的例子：

  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z')
  

  首先，条件中的nonkey = 4和key1 LIKE '%b'不属于范围查找的范畴，因此会被替换为TRUE,替换结果为：

  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
  (key1 < 'bar' AND TRUE) OR
  (key1 < 'uux' AND key1 > 'z')
  

  然后，继续对里面的条件进行压缩，可以看到下面的条件是可以直接得到结果的：

  • (key1 LIKE 'abcde%' OR TRUE) 该条件始终为TRUE；
  • (key1 < 'uux' AND key1 > 'z') 该条件始终为FALSE

  替换后的结果为：

  (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
  

  删除掉所有的TRUE和FALSE

  (key1 < 'abc') OR (key1 < 'bar')
  

  最后，对该条件进行进一步的压缩，得到的最终结果为：

  (key1 < 'bar')
  

  ​ 虽然这种做法可能会导致最终结果的范围被放大，但是MySQL在上面的range查询完成后，还会根据非range的条件在做最后的过滤，从而得到正确的结果。比如：nonkey = 4和LIKE '%b'会在最终返回给用户前做过滤。

• 对于多列的range查找(Range Access Method for Multiple-Part Indexes)

  多列range查找在MySQL内部实际上是单列的一个扩展，对于多列的range查找，会被优化一个元组，然后在进行查找，如下面的例子：

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

  该range查找设计到了三个key，分别为key_part1,key_part2和key_part3，该条件会被优化为：

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

  inf表示无穷大的意思，虽然优化后的范围变大了，但是这样子更有利于Mysql进行范围查找。

  对于带有or的条件，会被优化为两个范围，如下面的例子：

  (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)
  

• 对于range查找数据量的评估(Equality Range Optimization of Many-Valued Comparisons)

  Mysql评估range查找的时候，主要使用两种技术，index dives和index statistics，这两种技术各有优缺点：

  • index dives：会对每一行进行评估，随着比较的表达式增加，优化器也需要花费更多的时间去生成对row进行评估的方法，结果准确，但是花费时间较多。
  • index statistics：准确度不如index dives，但是速度快

  在Mysql里面，可以使用eq_range_index_dive_limit系统变量来决定使用哪种策略的条件。

• Skip Scan Range Access Method

  通常来讲，range scan的效率要远远高于full index scan的效率，但是有时候，我们没办法使用range scan的技术，比如下面这个例子：

  CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
  INSERT INTO t1 VALUES
    (1,1), (1,2), (1,3), (1,4), (1,5),
    (2,1), (2,2), (2,3), (2,4), (2,5);
  INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
  INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
  INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
  INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
  ANALYZE TABLE t1;
  
  EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
  

  因为select查询的条件只作用于f2，但是索引创建的是f1和f2，在这种场景下，Mysql会使用一种叫做Skip Scan的技术，其类似于Loose Index Scan，操作的步骤如下：

  • 首先在第一个index列中查找出不同的值，该例子中为f1
  • 然后在第二个index列中进行一个range scan操作，在本例子中就是分别在f1 = 1和f1 = 2的条件下，分别进行f2 > 40的操作。

  以上面的为例，操作步骤如下：

  • 1 首先查找出f1中所有的不同的列，先限定f1 = 1
  • 2 在f1 = 1和f2 > 40的条件下，进行range scan操作
  • 3 限定f1 = 2
  • 4 在f1 = 2和f2 > 40的条件下，进行range scan操作

3 Index Merge Optimization

当查询语句中有多个range查询的时候（只针对一张表），Mysql就会使用The Index Merge Access技术将扫描出来的多个结果（注意不一定会使用多个索引）合并为一个返回给SQL层，这个过程可以对底层的scan产生类似于union，intersection，或者unions-of-intersections的效果。

类似于下面的SQL语句，就可以使用到The Index Merge Access优化技术。

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);

Index Merge Optimization已知有以下约束：

• 如果where后面的条件嵌套层次过深，那么将无法优化，请尝试手工的将这些条件展开为等价条件：

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

• 无法适用于full-text 索引。

在EXPLAIN的结果里面，如果使用到了Index Merge Optimization技术，在type列，会显示index_merge，key列会包含一系列被用到的索引，在Extra列，会显示用到的算法，分别有：Using intersect, Using uion, Using sort_union。

接下来，详细介绍每个算法适用的场景：

Index Merge Intersection Access Algorithm

该场景适用于使用了一个或多个key，并且用AND连接，比如下面的：

• 索引有多个列，假设有N个列，每个列都会覆盖到：

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

• 任何针对于primary key的range查找，仅限于Innodb表。比如下面的语句：

  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;
  

  The Index Merge intersection算法会在底层并发的使用涉及到的索引进行扫描，并将扫描结果作一个intersection操作返回给上层。

  如果查询语句使用到了索引涉及到的所有列，那么不会去查询表的完整行，比如下面的语句：

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

  相反的，如果没有覆盖所有行，那么Mysql就会获取整行，用来判断是否满足查询条件。

  如果已经merge的所有条件中，有某几个条件是针对于primary key的，那么这些primary key并不会用来作为获取行的工具，而是用来对查出来的行进行过滤的。

Index Merge Union Access Algorithm

当WHERE条件可以转换为使用了多个索引的OR语句，那么就可以使用这种算法，并且，OR中的每一个子条件满足下面的要求：

• 即Index Merge Intersection Access Algorithm需要满足的条件。

这种场景下的语句举例如下：

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;

Index Merge Sort-Union Access Algorithm

当WHERE条件可以转换为多个OR连接的条件，但是Index Merge Union Access Algorithm却无法适用，此时会用到该技术，比如下面的语句：

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;

和Index Merge Union Access Algorithm的区别是：该算法必须先获取所有行的rowID，并且在返回前对其进行排序。

最后，通过 index_merge, index_merge_intersection, index_merge_union, 和index_merge_sort_union可以控制这些算法的开关。

4 Engine Condition Pushdown Optimization

引擎条件下推优化：目前仅支持NDB存储引擎，可以避免节点之间发送没有匹配到的数据。

举例，假设有下面这张表：

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

下面这条语句会使用条件下推的方式进行优化：

SELECT a, b FROM t1 WHERE b = 10;

使用EXPLAIN看到的结果如下：

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

但是下面的语句将不能使用该优化技术：

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

原因如下：

• 第一条语句作用的范围在索引上，使用索引查询的效率要比条件下推的效率高很多
• 第二条语句虽然作用在索引上，但是并不是直接的使用索引，如果改成b = 9，那么，就会采用引擎下推的优化技术。

符合下面的比较条件的，均可以使用条件下推优化：

• column [NOT] LIKE pattern
• column IS [NOT] NULL
• column IN (value_list)
• column BETWEEN constant1 AND constant2

其中的pattern,value_list均必须是常量或者字面量

5 Index Condition Pushdown Optimization(ICP)

类似于上一节的优化，索引条件下推优化可以让Mysql将这些条件下推到存储引擎中，从而可以保证存储引擎只返回匹配的数据，避免了存储引擎遍历索引带来的过多IO问题，同样的，根据其名字可以看出来，该种优化技术只适用于索引上，其他条件不适用。

首先讲解下使用该种技术和不适用的区别，当不适用该种优化技术时，Mysql的处理流程如下：

• 首先获取到下一行的索引，然后利用索引读取到整行出来（该过程需要存储引擎来做）
• 对该行进行where 过滤，然后继续处理下一行

当使用该种技术时，处理流程如下：

• 获取到下一行的索引，但是并不先读取整行
• 对索引列进行where过滤（该过程由存储引擎完成）
• 如果满足条件，在利用索引列读取到整行出来（该过程由存储引擎完成）
• 继续对剩余的列进行where过滤

这种优化技术适用于以下条件：

• 当需要访问表的整行时，ICP适用于range, ref, eq_ref, ref_or_null（注： EXPLAIN返回的结果，详情可见这里。）

• 适用于InnoDB和MyISAM

• 对于InnoDB来讲，只适用于二级索引，因为InnoDB的聚集索引，完整的记录已经被读上来了，所以不能起到减少IO的作用

• 对于InnoDB来讲，该优化适用于创建在virtual generated columns.上面的二级索引。

• 子查询中的条件无法被下推

• stored functions无法被下推，存储引擎无法调用stored functions

• 触发器无法被下推

举个例子，假设有一张表叫做people，其索引列为：(zipcode, lastname, firstname)，有下面的SQL查询语句：

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

如果没有索引条件下推优化，那么存储引擎就需要读取出所有zipcode='95054'的行，然后在进行where条件过滤；

如果使用了索引条件下推优化，存储引擎还可以对lastname进行匹配过滤，如果匹配的话，在进行表整行的读取。