mysql关键字索引_MySQL如何基于Explain关键字优化索引功能

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲&＃xff0c;它的作用就是分析查询性能。

explain关键字的使用方法很简单&＃xff0c;就是把它放在select查询语句的前面。

mysql查看是否使用索引&＃xff0c;简单的看type类型就可以。如果它是all&＃xff0c;那说明这条查询语句遍历了所有的行&＃xff0c;并没有使用到索引。

比如&＃xff1a;explain select * from company_info where cname like &＃39;%小%&＃39;

explain select * from company_info where cname like &＃39;小%&＃39;

以下

1)、id列数字越大越先执行&＃xff0c;如果说数字一样大&＃xff0c;那么就从上往下依次执行&＃xff0c;id列为null的就表是这是一个结果集&＃xff0c;不需要使用它来进行查询。

2)、select_type列常见的有&＃xff1a;

A&＃xff1a;simple&＃xff1a;表示不需要union操作或者不包含子查询的简单select查询。有连接查询时&＃xff0c;外层的查询为simple&＃xff0c;且只有一个

B&＃xff1a;primary&＃xff1a;一个需要union操作或者含有子查询的select&＃xff0c;位于最外层的单位查询的select_type即为primary。且只有一个

C&＃xff1a;union&＃xff1a;union连接的两个select查询&＃xff0c;第一个查询是dervied派生表&＃xff0c;除了第一个表外&＃xff0c;第二个以后的表select_type都是union

D&＃xff1a;dependent union&＃xff1a;与union一样&＃xff0c;出现在union 或union all语句中&＃xff0c;但是这个查询要受到外部查询的影响

E&＃xff1a;union result&＃xff1a;包含union的结果集&＃xff0c;在union和union all语句中,因为它不需要参与查询&＃xff0c;所以id字段为null

F&＃xff1a;subquery&＃xff1a;除了from字句中包含的子查询外&＃xff0c;其他地方出现的子查询都可能是subquery

G&＃xff1a;dependent subquery&＃xff1a;与dependent union类似&＃xff0c;表示这个subquery的查询要受到外部表查询的影响

H&＃xff1a;derived&＃xff1a;from字句中出现的子查询&＃xff0c;也叫做派生表&＃xff0c;其他数据库中可能叫做内联视图或嵌套select

3)、table

显示的查询表名&＃xff0c;如果查询使用了别名&＃xff0c;那么这里显示的是别名&＃xff0c;如果不涉及对数据表的操作&＃xff0c;那么这显示为null&＃xff0c;如果显示为尖括号括起来的就表示这个是临时表&＃xff0c;后边的N就是执行计划中的id&＃xff0c;表示结果来自于这个查询产生。如果是尖括号括起来的&＃xff0c;与类似&＃xff0c;也是一个临时表&＃xff0c;表示这个结果来自于union查询的id为M,N的结果集。

4)、type

依次从好到差&＃xff1a;system&＃xff0c;const&＃xff0c;eq_ref&＃xff0c;ref&＃xff0c;fulltext&＃xff0c;ref_or_null&＃xff0c;unique_subquery&＃xff0c;index_subquery&＃xff0c;range&＃xff0c;index_merge&＃xff0c;index&＃xff0c;ALL&＃xff0c;除了all之外&＃xff0c;其他的type都可以使用到索引&＃xff0c;除了index_merge之外&＃xff0c;其他的type只可以用到一个索引

A&＃xff1a;system&＃xff1a;表中只有一行数据或者是空表&＃xff0c;且只能用于myisam和memory表。如果是Innodb引擎表&＃xff0c;type列在这个情况通常都是all或者index

B&＃xff1a;const&＃xff1a;使用唯一索引或者主键&＃xff0c;返回记录一定是1行记录的等值where条件时&＃xff0c;通常type是const。其他数据库也叫做唯一索引扫描

C&＃xff1a;eq_ref&＃xff1a;出现在要连接过个表的查询计划中&＃xff0c;驱动表只返回一行数据&＃xff0c;且这行数据是第二个表的主键或者唯一索引&＃xff0c;且必须为not null&＃xff0c;唯一索引和主键是多列时&＃xff0c;只有所有的列都用作比较时才会出现eq_ref

D&＃xff1a;ref&＃xff1a;不像eq_ref那样要求连接顺序&＃xff0c;也没有主键和唯一索引的要求&＃xff0c;只要使用相等条件检索时就可能出现&＃xff0c;常见与辅助索引的等值查找。或者多列主键、唯一索引中&＃xff0c;使用第一个列之外的列作为等值查找也会出现&＃xff0c;总之&＃xff0c;返回数据不唯一的等值查找就可能出现。

E&＃xff1a;fulltext&＃xff1a;全文索引检索&＃xff0c;要注意&＃xff0c;全文索引的优先级很高&＃xff0c;若全文索引和普通索引同时存在时&＃xff0c;mysql不管代价&＃xff0c;优先选择使用全文索引

F&＃xff1a;ref_or_null&＃xff1a;与ref方法类似&＃xff0c;只是增加了null值的比较。实际用的不多。

G&＃xff1a;unique_subquery&＃xff1a;用于where中的in形式子查询&＃xff0c;子查询返回不重复值唯一值

H&＃xff1a;index_subquery&＃xff1a;用于in形式子查询使用到了辅助索引或者in常数列表&＃xff0c;子查询可能返回重复值&＃xff0c;可以使用索引将子查询去重。

I&＃xff1a;range&＃xff1a;索引范围扫描&＃xff0c;常见于使用>,

J&＃xff1a;index_merge&＃xff1a;表示查询使用了两个以上的索引&＃xff0c;最后取交集或者并集&＃xff0c;常见and &＃xff0c;or的条件使用了不同的索引&＃xff0c;官方排序这个在ref_or_null之后&＃xff0c;但是实际上由于要读取所个索引&＃xff0c;性能可能大部分时间都不如range

K&＃xff1a;index&＃xff1a;索引全表扫描&＃xff0c;把索引从头到尾扫一遍&＃xff0c;常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

L&＃xff1a;all&＃xff1a;这个就是全表扫描数据文件&＃xff0c;然后再在server层进行过滤返回符合要求的记录。

5)、possible_keys

查询可能使用到的索引都会在这里列出来

6)、key

查询真正使用到的索引&＃xff0c;select_type为index_merge时&＃xff0c;这里可能出现两个以上的索引&＃xff0c;其他的select_type这里只会出现一个。

7)、key_len

用于处理查询的索引长度&＃xff0c;如果是单列索引&＃xff0c;那就整个索引长度算进去&＃xff0c;如果是多列索引&＃xff0c;那么查询不一定都能使用到所有的列&＃xff0c;具体使用到了多少个列的索引&＃xff0c;这里就会计算进去&＃xff0c;没有使用到的列&＃xff0c;这里不会计算进去。留意下这个列的值&＃xff0c;算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意&＃xff0c;mysql的ICP特性使用到的索引不会计入其中。另外&＃xff0c;key_len只计算where条件用到的索引长度&＃xff0c;而排序和分组就算用到了索引&＃xff0c;也不会计算到key_len中。

8)、ref

如果是使用的常数等值查询&＃xff0c;这里会显示const&＃xff0c;如果是连接查询&＃xff0c;被驱动表的执行计划这里会显示驱动表的关联字段&＃xff0c;如果是条件使用了表达式或者函数&＃xff0c;或者条件列发生了内部隐式转换&＃xff0c;这里可能显示为func

9)、rows

这里是执行计划中估算的扫描行数&＃xff0c;不是精确值

10)、extra

这个列可以显示的信息非常多&＃xff0c;有几十种&＃xff0c;常用的有

A&＃xff1a;distinct&＃xff1a;在select部分使用了distinc关键字

B&＃xff1a;no tables used&＃xff1a;不带from字句的查询或者From dual查询

C&＃xff1a;使用not in()形式子查询或not exists运算符的连接查询&＃xff0c;这种叫做反连接。即&＃xff0c;一般连接查询是先查询内表&＃xff0c;再查询外表&＃xff0c;反连接就是先查询外表&＃xff0c;再查询内表。

D&＃xff1a;using filesort&＃xff1a;排序时无法使用到索引时&＃xff0c;就会出现这个。常见于order by和group by语句中

E&＃xff1a;using index&＃xff1a;查询时不需要回表查询&＃xff0c;直接通过索引就可以获取查询的数据。

F&＃xff1a;using join buffer(block nested loop)&＃xff0c;using join buffer(batched key accss)&＃xff1a;5.6.x之后的版本优化关联查询的BNL&＃xff0c;BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

G&＃xff1a;using sort_union&＃xff0c;using_union&＃xff0c;using intersect&＃xff0c;using sort_intersection&＃xff1a;

using intersect&＃xff1a;表示使用and的各个索引的条件时&＃xff0c;该信息表示是从处理结果获取交集

using union&＃xff1a;表示使用or连接各个使用索引的条件时&＃xff0c;该信息表示从处理结果获取并集

using sort_union和using sort_intersection&＃xff1a;与前面两个对应的类似&＃xff0c;只是他们是出现在用and和or查询信息量大时&＃xff0c;先查询主键&＃xff0c;然后进行排序合并后&＃xff0c;才能读取记录并返回。

H&＃xff1a;using temporary&＃xff1a;表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表&＃xff0c;执行计划中看不出来&＃xff0c;需要查看status变量&＃xff0c;used_tmp_table&＃xff0c;used_tmp_disk_table才能看出来。

I&＃xff1a;using where&＃xff1a;表示存储引擎返回的记录并不是所有的都满足查询条件&＃xff0c;需要在server层进行过滤。查询条件中分为限制条件和检查条件&＃xff0c;5.6之前&＃xff0c;存储引擎只能根据限制条件扫描数据并返回&＃xff0c;然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性&＃xff0c;可以把检查条件也下推到存储引擎层&＃xff0c;不符合检查条件和限制条件的数据&＃xff0c;直接不读取&＃xff0c;这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

J&＃xff1a;firstmatch(tb_name)&＃xff1a;5.6.x开始引入的优化子查询的新特性之一&＃xff0c;常见于where字句含有in()类型的子查询。如果内表的数据量比较大&＃xff0c;就可能出现这个

K&＃xff1a;loosescan(m..n)&＃xff1a;5.6.x之后引入的优化子查询的新特性之一&＃xff0c;在in()类型的子查询中&＃xff0c;子查询返回的可能有重复记录时&＃xff0c;就可能出现这个

除了这些之外&＃xff0c;还有很多查询数据字典库&＃xff0c;执行计划过程中就发现不可能存在结果的一些提示信息

11)、filtered

使用explain extended时会出现这个列&＃xff0c;5.7之后的版本默认就有这个字段&＃xff0c;不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后&＃xff0c;剩下多少满足查询的记录数量的比例&＃xff0c;注意是百分比&＃xff0c;不是具体记录数。

以上就是本文的全部内容&＃xff0c;希望对大家的学习有所帮助&＃xff0c;也希望大家多多支持我们。

本文标题: MySQL如何基于Explain关键字优化索引功能

本文地址: http://www.cppcns.com/shujuku/mysql/354129.html