mysql死磕7天第六天

mysql查询优化

explain命令

各个属性的含义:

id

select查询的序列号,她的值越大,优先级越高,就越先执行,如果id相同,执行顺序自上而下。当引用其他查询结果做union时,该值为null,且table列的值为union(m,n),意思是把id为m和n的查询结果做union。

select_type

select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

  • simple:简单查询,查询中不包含子查询或者union。

  • primary:查询中若包含复杂的子部分,则最外层查询为primary。

  • union:若第二个select出现在union之后,则被标记为union,如果有多个union,则除第一个select之外,后续的都是union。

  • dependent union:在union中的第二个及以后的查询语句,依赖于外层查询。(关联查询?)

  • union result:union的结果,id列为null,table列显示了是由哪几个查询的结果做的union。

  • subquery:子查询中的第一个查询。(第二个及以后的呢?)

  • dependent query:子查询中的第一个查询,依赖于外部查询。(关联子查询?)

  • derived:衍生查询(from子句中的子查询)???

  • materialized:物化子查询?什么意思?

  • uncacheable subquery:查询结果不能缓存的子查询,对于外部查询中的每一行都必须重新计算。

  • uncacheable union:该查询是union查询中的第二个及以后的查询语句,且整个union查询语句是一个uncacheable subquery。

    关于dependent subquery和uncacheable subquery,官方文档是这样解释的:

DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.

翻译如下: 
dependent subquery评估和uncacheable subquery评估不同。dependent subquery对于外部查询中的不同的值只计算一次。而uncacheable subquery对于外部查询中的每一行都重新评估一次。 
(由此可见两者的执行效率完全不同,极端情况下能差好几个数量级)

table

查询结果出自哪张表。可以是具体的表名,也可以是以下的值:

  • union M,N:id为M和N的查询结果做union

  • derivedN:参考id为N的查询的衍生查询

  • subqueryN:参考id为N的物化子查询

type

表连接类型,访问方式。

type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是: 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 
一般来说,得保证查询至少达到range级别,最好能达到ref。

以下按照从好到坏的顺序介绍访问类型:

  • system:表中只有一行数据(=system table),是const类型的特殊情况。

  • const:表中最多只有一行符合条件的行,该行在查询一开始就会被读取。因为只有一行,所以优化器可以把这一值看做常数。const连接类型速度非常快,因为只读取一次。经常用在在主键或者唯一索引上做等值查询。在如下的查询中,tb1_name可以当做const表。

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name  WHERE primary_key_part1=1 AND primary_key_part2=2;1234
  • eq_ref:可以理解为利用主键或者唯一非空索引做等值唯一连接。从表里取出一行来,与从之前的表里取出的行做连接。不同于system和const,这个是最常用的连接方式。这是用在索引的全部都用来做连接,并且该索引为主键或唯一非空索引时。eq_ref用在索引列做等值连接。与之相比较的值可以是常数,或者与之前表的列有关的表达式。在如下的查询中,mysql使用eq_ref连接来处理ref_table:

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;123456
  • ref:可以理解为非唯一性索引扫描。从索引中将对应值的行取出来,跟之前表中取出的数据做连接。如果只使用了索引的左前缀,或者非主键非唯一索引做链接时,用ref。(换句话说,对于给定的值,返回的行数不止一行。)如果给定的值只返回很少的行,这是个很好的连接方式。 
    ref可以用于在索引列上做=或>=,<=操作时。在下面的例子中,mysql使用ref连接方式处理ref_table:

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;12345678
  • fulltext:全文索引。

  • ref_or_null:连接方式跟ref相似,但是增加了一个额外的搜索条件,包含null值。这种连接方式优化大多用于解决子查询问题。在下面的例子中,mysql使用ref_or_null连接方式处理ref_table:

SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;12
  • index merge:这种连接方式表明使用了index merge优化。在这种情况下,explain输出的列中,key列包含用到的索引的列表,key_len列包含用到的列的最大长度的列表。see Section 8.2.1.4, “Index Merge Optimization”.

  • unique_subquery:这种方式用于在使用in关键字进行子查询时,代替ref连接方式。 
    unique_subquery是一个索引查询方法,用来代替子查询以提供更好的效率。 
    用于类似如下查询方式:

...... value IN (SELECT primary_key FROM single_table WHERE some_expr)1
  • index_subquery:这种连接方式类似于unique_subquery,代替了IN方式的子查询,但是用于非唯一索引的子查询。用于类似如下查询方式:

......value IN (SELECT key_column FROM single_table WHERE some_expr)1
  • range:索引范围查询。利用索引查询,返回给定范围内的行。在explain的输出中,key列显示使用了哪个索引,key_len列显示使用到的列的最长部分,在这种情况下,ref列为null。 
    range用于key列与常数作比较,操作符可以是 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或者 IN(),例如:

SELECT * FROM tbl_name  WHERE key_column = 10;SELECT * FROM tbl_name  WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name  WHERE key_column IN (10,20,30);SELECT * FROM tbl_name  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);1234567891011
  • index:index: 连接类型跟 all 一样,不同的是它只扫描索引树。 
    有两种情况: 
    (1)如果索引是覆盖索引,所有需要的数据都可以从索引中获得,此时只扫描索引。在这种情况下,explain命令的输出中,Extra列显示 Using index。它通常会比 all快点,因为索引文件通常比数据文件小。 
    (2)通过查询索引,以索引的排列顺序做全表扫描。在这种情况下,Extra列不会显示using index。

  • all:全表扫描。

possible_keys

指 mysql在搜索表记录时可能使用哪个索引。 
possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null,就表示没有索引被用到。 
这种情况下,就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。

key

key字段显示了mysql实际上要用的索引。 
当没有任何索引被用到的时候,这个字段的值就是null。 
想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表,可以在查询语句中使用关键字force index, use index,或 ignore index。 
如果是 myisam 和 bdb 类型表,可以使用 analyzetable 来帮助分析使用使用哪个索引更好。 
如果是 myisam类型表,运行命令 myisamchk –analyze也是一样的效果。 
详细的可以查看章节”14.5.2.1 analyze tablesyntax”和”5.7.2 table maintenance and crash recovery”。

key_len

显示MySQL决定使用的键长度。表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。可以告诉你在联合索引中mysql会真正使用了哪些索引。 
注:key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

ref

ref 字段显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。

rows

rows 字段显示了mysql认为在查询中应该检索的记录数。 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的。

Extra

extra列包含了mysql处理sql的一些附加信息。 
如果你想让查询速度尽可能的快,那么就要注意Extra列中值为using filesort和using temporary。

  • Child of ‘table’ pushed join@1 
    仅适用于mysql cluster。在NDB内核中,该表被当做子表,查询条件下推(push down)到各个节点处理,避免节点间不符合条件的数据的交互,减少网络和系统负载。(我这么认为的)

  • const row not found 
    在类似于SELECT … FROM tbl_name 这样的查询中,被查询的表是空表。

  • Deleting all rows 
    某些引擎(例如myisam)在删除全部数据的时候,支持handle method,简单快速的删除所有数据。如果引擎使用了这种方式优化,则extra列显示此信息。

  • distinct 
    mysql当找到当前记录的匹配联合结果的第一条记录之后,就不再搜索其他记录了。

  • FirstMatch(tbl_name)

  • Full scan on NULL key

  • Impossible HAVING

  • Impossible WHERE

  • Impossible WHERE noticed after reading const tables

  • LooseScan(m..n)

  • Materialize, Scan

  • No matching min/max row

  • no matching row in const table

  • No matching rows after partition pruning

  • No tables used

  • Not exists 
    mysql在查询时做一个 left join优化时,当它在当前表中找到了和前一条记录符合 left join条件后,就不再搜索更多的记录了。下面是一个这种类型的查询例子: 
    select * from t1 left join t2 on t1.id=t2.id where t2.id isnull; 
    假使 t2.id 定义为 not null。这种情况下,mysql将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时,这就意味着 t2.id 肯定不会都是null,就不会再在 t2 中查找相同 id值的其他记录了。也可以这么说,对于 t1 中的每个记录,mysql只需要在t2 中做一次查找,而不管在 t2 中实际有多少匹配的记录。

  • Range checked for each record (index map: N) 
    mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。这个过程不会很快,但总比没有任何索引时做表连接来得快。

  • Scanned N databases

  • Select tables optimized away

  • Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table

  • Start materialize, End materialize, Scan

  • Start temporary, End temporary

  • unique row not found 
    类似于SELECT … FROM tbl_name这样的查询,该表上没有满足条件的唯一索引或者主键的行。(不好翻译,具体看官档吧)

  • Using filesort 
    mysql需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了,对应的记录也会按照排好的顺序取出来。See Section 8.2.1.15, “ORDER BY Optimization”.

  • Using index 
    字段的信息直接从索引树中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。 
    如果extra列中同时还有using where,意思是这个索引时用来查找键值,然后回表。 
    如果没有using where,优化器可能是为了避免回表。 
    例如,如果索引时覆盖索引,优化器扫描索引,而不是用索引来回表。 
    对于有聚集索引的innodb表,即使extra列中没有using index,也可以使用索引。这适用于type列是index,key列是primary的情况。

  • Using index condition 
    ICP优化相关,将数据过滤条件从server层面下推到存储引擎层,在存储引擎层根据索引元祖过滤数据,避免不符合条件的数据传给server。(非翻译,整理自网络。)

  • Using index for group-by 
    与using index类似,using index for group-by表明mysql找到一个索引,该索引可以返回所有group-by或distinct查询所需的列,不需要回表。

  • Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) 
    做连接时,当前表之前的表被分多次读入join buffer中,然后join buffer中的表再跟当前表做join。(Block Nested Loop) 表明使用BNL算法。 (Batched Key Access)表明使用BKA算法。explain中,上一行的keys会被读入buffer,然后当前表的相应的行会被分批获取。(具体什么意思,再查!)

  • Using MRR 
    MRR优化方式,具体再查。

  • Using sort_union(…), Using union(…), Using intersect(…)

  • Using temporary 
    mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了groupby 和 order by 子句,它以不同的方式列出了各个字段。

  • Using where 
    过滤符合条件的行。 
    当extra列中没有using where并且表连接类型为all或者index时,你可能执行了错误的查询,除非你要获取或检查所有的行。

  • Using where with pushed condition 
    仅适用于NDB。从各个节点过滤数据。后面的省略翻译。

举例

mysql> explain extended
    -> select count(emp_no) from dept_emp where dept_no = 
    -> (select dept_no from departments where dept_name='Sales');
+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows  | filtered | Extra                    |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+
|  1 | PRIMARY     | dept_emp    | ref   | dept_no       | dept_no   | 4       | const | 91566 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | departments | const | dept_name     | dept_name | 42      | const |     1 |   100.00 | Using index              |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+2 rows in set, 1 warning (0.00 sec)12345678910
mysql> explain extended    -> select count(first_name) from employees    -> union    -> select count(distinct(first_name)) from employees    -> union    -> select count(last_name) from employees;+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+|  1 | PRIMARY      | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            ||  2 | UNION        | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            ||  3 | UNION        | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            || NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL |   NULL |     NULL | Using temporary |+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+4 rows in set, 1 warning (0.00 sec)


打赏请扫码~