SQL优化之索引和执行计划

/ SQL优化 / 2 条评论 / 542人围观

索引

什么是索引

索引(Index)是帮助MySQL高效获取数据的数据结构。它是一种数据结构。

举个最形象的例子:比如新华字典,平时查字典的时候,总共有三种方式能找到的字,第一种是从第一页开始一页一页的找,直到遇到跟这个字一样的就停下。第二种是根据部首索引目录,找到对应的字的对应页码来查阅。第三种,根据陌生字的大致拼音来搜索它在哪个范围页,进而缩小查找范围来查找。这三种方式都能找到,但明显有快有慢,这也是索引的本质。

请输入图片描述 mysql默认存储引擎innodb(5.5以前默认是myisam)只显式支持B-Tree( 从技术上来说是B+Tree)索引,从上面图片看,索引就是一种树形结构。

索引分类

基础语法

推荐使用工具来进行上述操作,比如navicat。

执行计划

什么是执行计划

所谓执行计划就是查询树,它由一系列的内部操作符组成,这些操作符按一定的运算关系构成查询的一个执行方案。

举个生活中的例子:从家坐地铁到公司,乘车路线就是执行计划。

执行计划的作用

执行计划信息

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

id

有三种情况:

id值越大优先级越高,越先被执行,id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。

select_type

查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询

类型描述
SIMPLE简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY查询中若包含任何复杂的子部分,最外层查询则被标记为
SUBQUERY在SELECT或WHERE列表中包含了子查询
DERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
UNION RESULT从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

partitions

type

type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般需要记住:system>const>eq_ref>ref>range>index>ALL

possible_keys

查询涉及到的字段上存在索引,则该索引将被列出,但不一定被查询实际使用

key

key列指出优化器选择使用的索引,每个表都仅使用一个索引。如果为NULL,则没有使用索引。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。例如一个列定义为varchar类型长度为255,但其中的内容仅仅占用了11,那么key_len显示还是255。这也跟不同的存储引擎有关系,默认是InnoDB。

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。

filtered

百分比值,这个值和rows的值一起使用,可以估计出那些将要和qep中的前一个表进行连接的行数。自行google吧,用的不太多。

Extra

包含不适合在其他列中显示但十分重要的额外信息。

描述
Using filesort说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Using index是否用了覆盖索引
Using where表明使用了where过滤
Using join buffer使用了连接缓存
Impossible wherewhere子句的值总是false,不能用来获取任何元组

如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

什么是覆盖索引(Using index)?

  1. 理解一:就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
  2. 理解二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意:如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select 星号 ,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。

总结

执行计划出现 type = all,Rows过大或者Extra = Using temporary; Using filesort 时有可能存在性能问题。

  1. 11111

    回复
  2. 777777777777777777777777777777777

    回复