MySQL索引优化（一）性能分析-explain

一、什么情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引，每次更新不单单是更新了记录还会更新索引
5. Where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

二、什么情况下不要创建索引

1. 表记录太少
2. 经常增删改查的表
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
   注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

三、性能分析

MySql Query Optimizer

1、MysqI中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间)

2、 当客户端向MySQL请求一 条Query, 命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉-些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

执行计划–explain

通过使用关键字explain可以模拟优化器执行sql查询语句，分析查询语句或表结构的性能瓶颈。

输出结果合计12列

+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------+

从以下几点看通过这个列表能看出什么？
表的读取顺序
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

ID

表的读取顺序

其中id可表示读取顺序，有三种情况：

1. id相同——>执行顺序为由上至下。
代表示例

EXPLAIN select course.* 
from course,studentselectcourse
where course.Cno = studentselectcourse.cno

输出结果id都为1，执行顺序为输出结果的由上往下，即先执行studentselectcourse再执行course

2. id不同——>如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

EXPLAIN select *
from course
where course.Cno = (
			SELECT s.cno 
			from studentselectcourse s
			where s.cno = 'C02');

PRIMARY代表最外层查询
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。即先执行studentselectcourse

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

type

显示查询使用的哪种类型：
由好到差依次为：
system>const>eq_ref>ref>range>index>ALL
基本达到ref或者range级别即可
ref： 非唯一性索引扫描，返回匹配某个单独值的所有行。例子：查询一个准确的名字。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。一般就是在你的where语句中出现了between、<. >、in等的查询

possible_key、key

哪些索引可以使用、哪些索引被实际使用

possible_key：
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用。

key：
实际使用索引。如果为null，则没有使用。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
覆盖索引，有下面三种理解：
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
**解释三：**是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
如果要使用覆盖索引，一定 要注意select列表中只取出需要的列，不可select *：

key_len

key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是 根据表定义计算而得，不是通过表内检索出的。查询结果相同时，精度越小越好！

ref

表之间的引用
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值.
简单的说：t2.id = t1.id 其中t2为全表扫描，t1中的ref就为t2中的对应列即t2.id

rows

每张表有多少行被优化器查询
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，越少越好。

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

Using filesort：说明mysq|会对数据使用–个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
该情况比较危险，会造成查询效率下降。索引建立不遵循最左匹配原则会出现该情况。

Using temporary：使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby，索引建立不遵循最左匹配原则会出现该情况。

Using index：
表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错；
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。