Query语句索引优化的思路
- 优化更需要优化的query
- 定位优化对象的性能瓶颈
- 明确优化目标
- 从Explain入手
Query语句的优化原则
- 多使用Profile
- 永远用小结果集驱动大结果集
- 尽可能在索引中完成排序
- 只取自己需要的Columns
- 仅仅使用最有效的过滤条件
- 尽可能避免复杂的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 ;
题后篇：
到这已经全部介绍完毕，说实话，笔者到现在还是懵懵哒，一开始思路还是很清晰的，但是越往后越乱（直到抓狂），明天抽出时间或者下班后就亲自示范一下到底怎么回事！！！！！ 刚才停了一小时的电，现在准备休息了，诸君晚安！！！