MySQL 优化之EXPLAIN详解（执行计划）

学习MySQL时我们都知道索引对于一个SQL的优化很重要，而EXPLAIN关键字在分析是否正确以及高效的增加了索引时起到关键性的作用。

这篇文章显示了如何调用“EXPLAIN”来获取关于查询执行计划的信息，以及如何解释输出。EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法。这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，你可以学习到查询时如何执行的。学会解释EXPLAIN将帮助你了解MySQL优化器是如何工作的。

1、概念以及限制

当执行查询时,EXPLAIN会返回关于在执行计划中每一步的信息,而不是执行它。它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序。

EXPLAIN只是个近似的结果,有些时候会与真想相差甚远,下面是一些限制:

  1. EXPLAIN根本不会告诉你触发器、存储过程或UDF会如何影响性能。
  2. 它并不支持存储过程,尽管可以手动抽取查询并单独地对其进行EXPLAIN操作。
  3. 它并不会告诉你MySQL在查询中做的特定优化。
  4. 它并不会显示关于查询的执行计划的所有信息。
  5. 它并不区分具有相同名字的事物。例如,它对内存排序和临时文件都使用”filesort”,并且对于磁盘上和内存中的临时表都显示”Using temporary”。
  6. 可能会误导。例如,它会对一个很小limit的查询显示全索引扫描。

MySQL5.6之后允许解释非SELECT查询,下面的例子解释还是5.6之前的，如果需要对UPDATE和DELETE进行性能优化，需要把SQL语句转换成SELECT语句。

2、EXPLAIN输出列解释

2.1、id列

这一列总是包含一个编号,表示SELECT所属的行。

2.2、select_type列

MySQL将查询分为简单和复杂类型,复杂类型可分为3类:简单子查询、所谓的派生表(在FROM子句中的子查询)、以及UNION查询。

select_type显示了对应行是简单还是复杂SELECT。

  1. SIMPLE:不包含子查询和其他复杂语法的简单查询。
  2. PRIMARY：这是为更复杂的查询而创建的首要表（也就是最外层的表）。这个类型通常可以在 DERIVED 和 UNION 类型混合使用时见到。
  3. SUBQUERY:包含在SELECT列表中的子查询的SELECT(换句话说,不在FROM子句中),标记为 SUBQUERY。
  4. DERIVED:包含在FROM子句的子查询中的SELECT。MySQL会递归执行,并把结果放到一张临时表中。服务器内部称其”派生表”,因为该临时表示从子查询中派生来的。简单的说就是一个表不是一个物理表。
  5. UNION:在UNION中的第二个和随后的SELECT被标记为UNION。第一个SELECT语句会以它自己执行的结果来被标记,比如PRIMARY、DERIVED等。
  6. UNION RESULT:用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT。

除了这些值, SUBQUERY 和 UNION 还可以被标记为 DEPENDENT 和 UNCACHEABLE。DEPENDENT意味着SELECT依赖于外层查询中发现的数据,UNCACHEABLE意味着SELECT中的某些特性阻止结果被缓存于一个Item_cache中。(Item_cache未被文档记载,它与查询缓存不是一回事,尽管它可以被一些相同类型的构建否定,例如RAND()函数)。

2.3、table列

这一列显示了对应行正在访问哪个表,或者是sql中定义的表的别名。

但是在派生表和联合查询中就不是这么简单了。因为有时候确实没有一个表可以参考到(MySQL创建的临时表仅在查询执行过程中存在)。

当在FROM查询中有子查询时,table列是<derivedN>的形式,其中N是子查询的id。这总是”向前引用”—换言之,N指向EXPLAIN输出中的后面的一行。

下面通过一个复杂的例子来说明一下各个引用之间的关系,以及table的名字。

EXPLAIN
SELECT actor_id,
    (SELECT 1 FROM film_actor WHERE film_actor.actor_id=der_1.actor_id LIMIT 1)
FROM
    (SELECT actor_id FROM actor LIMIT 5) AS der_1
UNION ALL
SELECT film_id,
    (SELECT @var1 FROM rental LIMIT 1)
FROM
    (SELECT film_id,
        (SELECT 1 FROM store LIMIT 1)
     FROM film LIMIT 5
    ) AS der_2;

 

下面是EXPLAIN输出的结果:

idselect_typetable
1 PRIMARY <derived3>
3 DERIVED actor
2 DEPENDENT SUBQUERY film_actor
4 UNION <derived6>
6 DERIVED film
7 SUBQUERY store
5 UNCACHEABLE SUBQUERY rental
NULL UNION RESULT <union1,4>

我们来分别解释一下上面的结果是什么意思:

  1. 第1行的id=1,对应第1条查询SELECT actor_id ...这个查询被标记为<derived3>,它引用了第二行(id=2)的结果。
  2. 第2行的id=3,对应第3条查询(SELECT actor_id FROM actor LIMIT 5) AS der_1 这个查询的表很明显是actor,因为是FROM后面的查询所以select_type为DERIVED
  3. 第3行的id=2,对应第2条查询SELECT 1 FROM film_actor WHERE film_actor.actor_id=der_1.actor_id LIMIT 1 因为它依赖于外层的查询,也就是id=3的查询,而且它是子查询(不在FROM子句中),所以它的select_type为DEPENDENT SUBQUERY
  4. 第4行的id=4,对应第4条查询SELECT film_id ... 这个查询依赖于后面的查询并添加到临时表,所以查询标记为<derived6>
  5. 第5行的id=6,对应第6条查询SELECT film_id,... FROM film LIMIT 5 这个查询因为在FROM子句中,所以select_type为DERIVED
  6. 第6行的id=7,对应第7条查询SELECT 1 FROM store LIMIT 1 这个查询FROM前,所以select_type为SUBQUERY
  7. 第7行的id=5,对应第5条查询SELECT @var1 FROM rental LIMIT 1 因为在FROM前并且有用户变量@var1,所以select_type为UNCACHEABLE SUBQUERY
  8. 第8行的id=NULL,它没有对应某一行的查询,而是把临时表的数据组合起来,分别是<derived3>和<derived6>的数据,所以select_type为UNION RESULT

2.4、type列

访问类型,换言之就是MySQL如何查找表中的行。

  1. ALL:全表扫描。(例外:LIMIT,或者在Extra中显示”Using distinct/not exists”)
  2. index:这个和全表扫描一样,只不过不是扫描的行,而是扫描的索引,主要优点就是避免了排序。如果Extra看到”Using index”,说明MySQL正在使用覆盖索引。
  3. range:范围扫描,返回值域的行,显而易见范围扫描是带有BETWEEN或在WHERE子句里有>的查询。当MySQL使用索引去查找一系列值时,例如使用IN()和OR列表，也会显示为范围扫描。但是in是多个等值条件查询，它后面可以使用其他索引列,其他大多数范围扫描后面都不会用到其他索引列。这就是区别。
  4. ref:这是一种索引访问(有时也叫做索引查找),它返回所有匹配某个单个值的行。然而,它可能会找到多个符合条件的行,因此,它是查找和扫描的混合体。此类访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值比较。这个参考值或者是一个常数,或者是来自多表查询前一个表里的结果值。ref_or_null是ref之上的一个变体,它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。
  5. eq_ref:使用这种索引查找,MySQL知道最多返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到,它会将他们与某个参考值做比较,MySQL对于这类访问类型的优化做得非常好。因为它知道无须估计匹配行的范围或者找到匹配行之后再继续查找。
  6. const,system:当MySQL能对查询的某部分进行优化并将其转换成一个常量时,它就会使用这些访问类型。举例来说,如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键,MySQL就能将这个查询转换成一个常量。然后就可以高效的将表从联接执行中移除。
  7. NULL:这种方式意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着访问表或者索引。例如,从一个索引列里选取最小值可以通过单独查找索引来完成,不需要在执行时访问表。

2.5、possible_keys列

这一列显示了查询可以使用哪些索引,这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的,因此有些罗列出来的索引对后续优化是没有用的。

2.6、key列

这一列显示MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys中,那么MySQL选它就是出于另外的原因,例如它可能选择了一个覆盖索引,哪怕没有where子句。
换句话说,possible_keys揭示了哪一个索引能有助于高效的行查找,而key显示的是优化后采用哪一个索引可以最小化查询成本。下面就是一个例子。

mysql> EXPLAIN SELECT actor_id, film_id FROM film_actor;

           id:1
  select_type:SIMPLE
        table:film_actor
possible_keys:NULL
          key:idx_fx_film_id
            ...

 

2.7、key_len列

MySQL在索引里使用的字节数。它包含了在索引字段中可能的最大长度,而不是表中使用的实际字节数。

key_len的长度计算公式：

  • L：索引列所定义字段类型字符长度
  • C：不同编码下一个字符所占的字节数（如utf8=3，gbk=2）
  • N：字段为空标记，占1字节（非空字段此标记不占用字节）
  • S：索引列字段是否定长（int、char、datetime为定长，varchar为不定长），不定长字段类型需记录长度信息，占2字节
    key_len = L * C （+N）（+S）

下面我们用一个例子来分析key_len是如何计算的：

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) DEFAULT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> explain select * from employees where first_name = 'Tomofumi' and last_name = 'Asmuth';
+----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
| id | select_type | table     | type | possible_keys  | key            | key_len | ref         | rows | Extra                 |
+----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | employees | ref  | idx_first_last | idx_first_last | 95      | const,const |    1 | Using index condition |
+----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
1 row in set

mysql> explain select * from employees where first_name = 'Tomofumi' order by last_name;
+----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
| id | select_type | table     | type | possible_keys  | key            | key_len | ref   | rows | Extra                              |
+----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | employees | ref  | idx_first_last | idx_first_last | 44      | const |  244 | Using index condition; Using where |
+----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
1 row in set

 

第一个执行计划key_len = (14 + 16) * 3 + 1 + 2 * 2 = 95
第二个执行计划key_len = 14 * 3 + 2 = 44

注意：由第二个执行计划可以分析得出，key_len只表示sql通过索引过滤结果集时所选择使用的索引的长度，并不包括order by的字段（类似的还有无需回表的快速索引扫描，select last_name from employees where first_name = ‘Tomofumi’），所以在第二个执行计划中，虽然order by也能使用到索引（可以通过Extra中是否有sort判断），但却并没有被计算在key_len内。

2.8、ref列

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。

2.9、rows列

这一列是MySQL估计为了找到所需的行而要读取的行数。这个值可能不准确,例如在使用LIMIT的时候,它显示的还是不使用LIMIT的估算值。要记住这个数字是MySQL认为它要检查的行数,而不是结果集里的行数。同时也要认识到有很多优化手段,例如关联缓冲区和缓存,无法影响到行数的显示。

2.10、filtered列(5.1新加)

filtered列给出了一个百分比的值，这个百分比的值和rows列的值一起使用，可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表。

这个一列只有在 EXPLAIN EXTENDED 语句中才会出现。

2.11、partitions列

partitions列代表给定表所使用的分区。这一列只会在 EXPLAIN PARTITIONS 语句中出现。

2.12、Extra列

这一列包含的使一些额外信息。有以下几个重要的值:

  1. Using index。此值表示MySQL将使用覆盖索引,以避免访问表。
  2. Using where。这意味着MySQL服务器将在存储引擎检索行后再进行过滤,许多where条件涉及索引中的列,当它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示这个。
  3. Using temporary。这意味着MySQL对查询结果会使用一个临时表。
  4. Using filesort。这意味着MySQL会使用一个外部索引排序,而不是按索引次序从表里读取行。
  5. Using index condition(5.6新特性)。“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上，详情点这里。
  6. Not exists。MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。
  7. Range check for each record(index map: N)。这个值意味着没有好用的索引,新的索引将在链接的每一行上重新估算。N是显示在possible_keys列中索引的位图,并且是冗余的。
  8. Using join buffer。这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那么应该注意，根据查询的具体情况可能需要添加索引来改进性能。
  9. Impossible where。这个值强调了语句会导致没有符合条件的行。例如 EXPLAIN SELECT * FROM user WHERE 1=2。
  10. Select tables optimized away。这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
  11. Distinct。这个值意味着MySQL在找到第一个匹配的行之后就会停止搜索其他行。
  12. Index merges。

下面我们来结合例子重点分析几个经常出现的Extra信息。

2.12.1、Using index

看了上面的解释，可以很清晰的发现因为存在覆盖索引，不需要去访问表就可以拿到全部数据，索引效率很高。比如下面的例子（first_name上面存在索引）：

mysql> EXPLAIN select first_name from employees where first_name = 'Alejandro' 
*****************输出结果***************
    ...
    key: index_fh
    key_len: 16
    Extra: Using index

2.12.2、Using where

Using where只是起到过滤元祖的作用，并不会去读取数据文件或者索引文件。当我们查询的内容在引擎层处理后还需要在server层过滤的时候，Using where就出现了。例如下面拿到first_name=’’后还需要对日期进行过滤。

mysql> EXPLAIN select first_name from employees where first_name = 'Alejandro' and birth_date = '1958-05-21'
*****************输出结果***************
    ...
    key: index_fh
    key_len: 16
    rows: 211
    Extra: Using where

又比如没有使用索引进行全表扫描会用Using where过滤：

mysql> EXPLAIN select * from employees where a >= 100
*****************输出结果***************
    ...
    key: null
    key_len: null
    rows: 299262
    Extra: Using where

2.12.3、Using index;Using where

上面的Using index和Using where貌似都很好理解，但是Using index;Using where这又是什么鬼？我们用几个例子来说明一下：

表employees的a字段存在索引index_ab（字段a和字段b的联合索引），表里总共有数据30万+

mysql> EXPLAIN select a from employees where a >= 100;
*****************输出结果***************
    ...
    type: range
    key: index_ab
    key_len: 4
    rows: 27760
    Extra: Using where;Using index

mysql> EXPLAIN select a from employees where a = 100 order by b;
*****************输出结果***************
    ...
    type: ref
    key: index_ab
    key_len: 4
    rows: 296
    Extra: Using where;Using index

 

我们可以发现a和b是覆盖索引，查询的列也只有a字段，为何Extra显示Using where;Using index???

我又去试了一下条件是范围查询的，输出都一样。但是如果条件换为a=100就是Using index了。

这个问题在stackoverflow上也有人提问：Whats the difference between “Using index” and “Using where; Using index” in the EXPLAIN

可以看到有人是这么回答的：

英：When you see Using Index in the Extra part of an explain it means that the (covering) index is adequate for the query.
in your example: SELECT id FROM test WHERE id = 5; the server doesn’t need to access the actual table as it can satisfy the query (you only access id) only using the index (as the explain says). In case you are not aware the PK is implemented via a unique index.

When you see Using Index; Using where it means that first the index is used to retrieve the records (an actual access to the table is not needed) and then on top of this result set the filtering of the where clause is done.
n this example: SELECT id FROM test WHERE id > 5; you still fetch for id from the index and then apply the greater than condition to filter out the records non matching the condition

中：在你的第一个例子里，Using index使用了覆盖索引，mysql server不需要去访问实际的表来得到id，因为第二索引中已经存在了。
你在explain中看到 Using Index; Using where，它还是使用覆盖索引去访问记录（实际上不需要访问表），然后在此基础上使用where的条件去过滤结果集。
第二个例子中：SELECT id FROM test WHERE id > 5; 你先是从索引中获取了所有的id，然后用大于条件去过滤结果集。

目前没有更好的解释的情况下，我姑且认为是这样的原理。在索引上附加范围取值的条件时，mysql会取到全部索引值交给server层去处理where条件。

2.12.4、Using index condition（ICP）

Using index condition是mysql 5.6新加入的特性，全称为：Index Condition Pushdown(ICP,索引条件下推)

ICP是mysql利用索引（二级索引）元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的where条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过过滤不相关的数据，仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行，而不是返回所有数据给server层，然后后再根据where条件进行过滤。使用ICP（mysql 5.6版本以前）和没有使用ICP的数据访问和提取过程如下

优化器没有使用ICP时，数据访问和提取的过程如下：

  1. 当storage engine读取下一行时，首先读取索引元组（index tuple），然后使用索引元组在基表中（base table）定位和读取整行数据。
  2. sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。
  3. 执行1），直到最后一行数据。

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下：

  1. storage engine从索引中读取下一条索引元组。
  2. storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据。
  3. 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
  4. server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。

而使用ICP时，如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）。存储引擎通过索引元组的索引列数据过滤不满足下推索引条件的数据行。

索引条件下推的意思就是筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。由此可见，ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。

注意一下ICP的使用条件：

  1. 只能用于二级索引(secondary index)。
  2. explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
  3. ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

ICP的开启优化功能与关闭

MySQL5.6可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭index_condition_push优化功能，默认开启。

mysql > set optimizer_switch='index_condition_pushdown=on|off'

用explain查看执行计划时，如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown。

在mysql5.6以前，还没有采用ICP这种查询优化，where查询条件中的索引条件在某些情况下没有充分利用索引过滤数据。假设一个组合索引（多列索引）K包含（c1,c2,…,cn）n个列，如果在c1上存在范围扫描的where条件，那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据（不管不管是唯一查找还是范围查找），索引记录没有被充分利用。即组合索引前面字段上存在范围查询，那么后面的部分的索引将不能被使用，因为后面部分的索引数据是无序。比如，索引key（a，b）中的元组数据为(0,100)、(1,50)、（1，100） ，where查询条件为 a < 2 and b = 100。由于b上得索引数据并不是连续区间，因为在读取（1，50）之后不再会读取（1，100），mysql优化器在执行索引区间扫描之后也不再扫描组合索引其后面的部分。

表结构定义如下：

CREATE TABLE `person` (
`person_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`postadlcode` int(11) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`person_id`),
KEY `idx_p_a` (`postadlcode`,`age`),
KEY `idx_f_l` (`first_name`,`last_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

关闭ICP优化，Extra信息为“Using Where”

mysql> set optimizer_switch = "index_condition_pushdown=off";
mysql> explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

 

开启ICP之后，Extra信息为“Using Index Condition”

mysql> set optimizer_switch = "index_condition_pushdown=on";
mysql> explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using index condition |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+

 

2.12.5、Using MRR

MySQL 5.6开始支持Multi-Range Read(MRR)优化。MRR优化的目的就是为了减少磁盘的随机访问，并且将随机的访问转化为顺序访问，这对于IO-bound类型的sql查询可带来性能的极大提升，MRR可适用于range，ref，eq_ref类型的查询。
MRR优化有以下几个好处：

  1. MRR使数据访问变得比较顺序，在查询辅助索引时，首先根据得到的查询结果按照主键进行排序，并按照主键的顺序进行书签查找。随机io情况降到最低。
  2. 减少缓冲池中页被替换的次数。（在没有启用mrr优化之前，由于主键的id是随机取的，那么可能每次取到的数据都不在同一个页中。比如第一个数据在一个页中，第二个数据又在另一个页中，而第三个数据又在第一个页中，在缓冲池不够大的时候，这样的情况会造成缓冲池中的页不断的离开缓冲池，然后又进入缓冲池，从而造成缓冲池的热点数据频繁更新。而启用了mrr优化以后，由于主键是顺序的，则可以把此开销降为最低。）
  3. 批量处理对键值的查询操作。

对于InnoDB和MyISAM存储引擎范围查询和JOIN查询操作，MRR的工作方式如下：

  1. 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
  2. 将缓存中的键值根据RowID进行排序。
  3. 根据RowID进行排序顺序来访问实际的数据文件。

是否启用MRR优化可以通过参数optimizer_switch中的标记(flag)来控制。当mrr为on时，表示启用MRR优化，mrr_cost_based标记表示是否通过cost based的方式选择是否启用mrr。若将mrr设为on，mrr_cost_based设为off，则总是启用MRR优化。

查看MRR是否启用：

mysql> show variables like 'optimizer_switch'

设置MRR优化：

mysql> set @@optimizer_switch='mrr=on,mrr_cost_based=off'

参数read_rnd_buffer_size用来控制键值得缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256k：

mysql> select @@read_rnd_buffer_size;
****************结果****************
@@read_rnd_buffer_size: 262144

2.12.6、Using filesort

字面意思，Using filesort可能会被翻译为使用文件进行排序或在文件中进行排序。实际上这是不正确的，这是一个让人产生误解的词语，它其实和文件没有任何关系，实际上是内部的一个快速排序。

2.12.6.1、order by的两种排序方式

在MySQL中的ORDER BY有两种排序实现方式:

  1. 利用有序索引获取有序数据
  2. 文件排序

第一种方式就是我们常用的覆盖索引，这里就不讲解了，第二种就是用不到索引所以产生文件排序。（这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。）

2.12.6.2、文件排序的实现算法

文件排序是通过相应的排序算法,将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。
在MySQL中filesort 的实现算法实际上是有两种：

  1. 双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。
  2. 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要注意max_length_for_sort_data 参数的设置。如果filesort过程中，由于排序缓存的大小不够大，那么就可能会导致；临时表的使用。max_length_for_sort_data的默认值是1024。

mysql在使用双路排序的时候，需要根据排好序的key，第二次去读取真正要返回的数据的。这样就会用到read_rnd_buffer_size这个参数定义的缓冲区。将读取的数据放到这个缓冲区中。

随机读取数据缓冲区使用内存(read_rnd_buffer_size)：和顺序读取相对应，当 MySQL 进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。如根据索引信息读取表数据，根据排序后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read_rnd_buffer_size 参数所设置的内存缓冲区。read_rnd_buffer_size，默认8MB。

顺序读取数据缓冲区使用内存(read_buffer_size)：这部分内存主要用于当需要顺序读取数据的时候，如无法使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer 空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。read_buffer_size，默认2MB

2.12.6.3、优化总结

当无法避免排序操作时，又该如何来优化呢？很显然，应该尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作，很大幅度地提高排序工作的效率。

1、加大 max_length_for_sort_data 参数的设置

在 MySQL 中，决定使用老式排序算法还是改进版排序算法是通过参数 max_lengthfor sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果有充足的内存让MySQL 存放须要返回的非排序字段，就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。

2、去掉不必要的返回字段

当内存不是很充裕时，不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，否则可能会造成 MySQL 不得不将数据分成很多段，然后进行排序，这样可能会得不偿失。此时就须要去掉不必要的返回字段，让返回结果长度适应 max_length_for_sort_data 参数的限制。

3、增大 sort_buffer_size 参数设置

增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法，而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成 MySQL 不得不使用临时表来进行交换排序。

posted @ 2019-11-08 10:26  VicLW  阅读(993)  评论(0)  编辑  收藏  举报