EXPLAINSELECT actor_id,(SELECT1FROM film_actor WHERE film_actor.actor_id=der_1.actor_id LIMIT1)FROM(SELECT actor_id FROM actor LIMIT5)AS der_1
UNIONALLSELECT film_id,(SELECT@var1FROM rental LIMIT1)FROM(SELECT film_id,(SELECT1FROM store LIMIT1)FROM film LIMIT5)AS der_2;
第2行的id=3,对应第3条查询(SELECT actor_id FROM actor LIMIT 5) AS der_1 这个查询的表很明显是actor,因为是FROM后面的查询所以select_type为DERIVED
第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
注意:由第二个执行计划可以分析得出,key_len只表示sql通过索引过滤结果集时所选择使用的索引的长度,并不包括order by的字段(类似的还有无需回表的快速索引扫描,select last_name from employees where first_name = ‘Tomofumi’),所以在第二个执行计划中,虽然order by也能使用到索引(可以通过Extra中是否有sort判断),但却并没有被计算在key_len内。
英: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 >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优化器在执行索引区间扫描之后也不再扫描组合索引其后面的部分。
mysql>set optimizer_switch ="index_condition_pushdown=off";
mysql>explainselect*from person where postadlcode between300000and400000and 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|Usingwhere|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
开启ICP之后,Extra信息为“Using Index Condition”
mysql>set optimizer_switch ="index_condition_pushdown=on";
mysql>explainselect*from person where postadlcode between300000and400000and 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|Usingindex condition |+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
2.12.5、Using MRR
MySQL 5.6开始支持Multi-Range Read(MRR)优化。MRR优化的目的就是为了减少磁盘的随机访问,并且将随机的访问转化为顺序访问,这对于IO-bound类型的sql查询可带来性能的极大提升,MRR可适用于range,ref,eq_ref类型的查询。 MRR优化有以下几个好处:
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 选择使用第二种单路算法来进行排序。这样可以减少大量的随机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 不得不使用临时表来进行交换排序。