索引优化-分析篇 【Explain用法】

Query语句索引优化的思路

1. 优化更需要优化的query
2. 定位优化对象的性能瓶颈
3. 明确优化目标
4. 从Explain入手

Query语句的优化原则

1. 多使用Profile
2. 永远用小结果集驱动大结果集
3. 尽可能在索引中完成排序
4. 只取自己需要的Columns
5. 仅仅使用最有效的过滤条件
6. 尽可能避免复杂的Join和子查询

执行计划Explain

Explain命令用于查看执行结果

explain的语法如下：

explain[extended] select…from…where…

索引优化 - Explain结果

• ID ：mysql query optimizer 选定的执行计划中查询的序列号

• select type : 所使用的查询类型，主要有以下几种查询类型

• table：显示这一步所访问数据库的表名

• type：告诉我们队表使用的访问方式，主要包含如下几种：

  • all:全表扫描
  • const：读常量，最多只会有一条匹配记录，由于是常量，实际上只需要读取一次
  • eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一索引来访问
  • fullext：进行全文索引检索
  • index：全索引扫描
  • index_merge：查询中同事使用两个或者多个索引，然后对索引结果再合并（merge），再读取表数据

索引优化 - Explain Key

Possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null（空） ，这项内容对优化索引调整非常重要

• Key：mysql query optimizer 从possible_keys中所选择使用的索引

• Key_len：被选中使用索引的索引长度

• Ref：列出通过常量（const），还是某个字段（如果是join）来过滤（通过key）的。

• Row：mysql query optimizer 中通过系统收集的统计信息估算出来的结果记录条数

索引优化 - Explain Extra

extra : 查询中每一步实现的额外细节信息，主要回事一下的内容

• disdict 查找distinct值，当mysql找到了第一条匹配的结果，将停止该值的查询，转为后面其他值的查询

• full scan on null key ：子查询的一种优化方式，主要在遇到无法通过索引访问null值时用

• Impossible where noticed after raading const table ：mysql query opyimizer 通过收集到的统计信息判断出不可能存在的查询

• no table ：query语句中使用from dual或者不包含任何from子句

• notexists：在某些左链接中，mysql query optimizer通过改变原有query的组成而使用夫人优化方法，可以部分减少数据访问的次数

索引优化 - explain Using

• using filesort ：当query中包含order by操作，而且无法利用索引完成排序操作的时候，mysql query optimizer不得不选择相应的排序算法来实现。指mysql将用外部排序而不是按照index顺序排列的结果。数据较少时从内存排序，否则从磁盘排序

• using index ： 所需数据只需在index即可全部获得，不需要再导表中取数据

• using index for group_by ： 数据访问和using index一样，所需的数据秩序要读取索引，当query中使用group_by或id stinct子句时，如果分组字段也在索引中，extra中的信息就会使using index for group_by

• using temporary ： 当mysql在某些操作中必须使用临时表时，在extra信息中就会出现using temporary，主要常见于group_by等操作中

• using where ：如果不读取表的数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现using where 的信息

• using where with pushed condition：这是一个仅仅在NDBCluster存储引擎中才会出现的信息

索引优化 - Using where 和 Using index

• using index 不读数据文件，只从索引文件获取数据

• using where 过滤元素和是否读取文件或索引文件没有关系

• 1 没有using where 只有 Using index ：则不读取数据文件，所有字段都可以从索引上获得（without using where,the optimizer may be reading the index to avoid reding data rows but not using it for lookups）

• 2 同时有using where 和using index：因为"without using where…"这句上下文，则意味着同时有using where 和 using index 则一定要读取数据文件

• 其实不然。“using where 只是过滤元组，和是否读取数据文件或索引文件没有关系”。

索引优化 - Profiling使用

• 优化一条query，必须清楚这条query的性能瓶颈到底在哪，是消耗的CPU操作太多还是需要的IO操作太多。通过该工具可以获取一条语句执行过程中的各种资源的消耗情况，如CPU，IO，IPC ,SEAP等，以及发生的pagerfaults,context,switche等等，同时还能得到该query执行过程中的mysql所调用的各个函数源文件中的位置。

• 通过执行 set profiling命令可以开启关闭query profiler功能set profiling = 1

• 在执行query profiler功能之后，mysql就会自动记录所有执行query的profile的信息

• 通过执行show profile 获取的那当前系统中保存的多个query的profile的概要信息

• 针对单个mysql获取详细的profile信息

  • show profile cpu , block io for query 6 ;

题后篇：
到这已经全部介绍完毕，说实话，笔者到现在还是懵懵哒，一开始思路还是很清晰的，但是越往后越乱（直到抓狂），明天抽出时间或者下班后就亲自示范一下到底怎么回事！！！！！ 刚才停了一小时的电，现在准备休息了，诸君晚安！！！