Mysql索引的优化分析-索引性能分析

一、MySql Query Optimizer(查询优化器)

1. mysql中有专门的负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为请求的query提供他认为最好的执行方法

2. 当client 向mysql 发送 一条query,命令解析器完成请求分类，区别出是select 并转发给mysql, mysql query optimizer,mysql query optimizer首先进行优化，处理一些常量表达式的计算，直接转换为常量值.并对query中的查询条件进行简化和转换。如去掉一些无用或者显而一见的条件、结构的调整。

---

二. mysql 常见瓶颈

1.CPU：cpu在饱和的时候一般发生在数据装入内存或者从磁盘上读取数据的时候

2.IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候

3.服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

---

三.Explain

1. 是什么（mysql的执行计划）
   使用expiain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的mysql语句的。分析你的查询语句或者表结构的性能瓶颈
2. 能干嘛？
   
   • 表的读取顺序
   • 数据读取操作的操作类型
   • 哪些索引可以被使用
   • 哪些索引被实际使用
   • 表之间的引用
   • 每张表有多少行被优化器查询
3. 怎么玩？
   
   • 语法 explain + sql语句
   • 执行计划包含的信息
     
4. 各个名字的解释
   

id(表的读取顺序):select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
1. id相同，执行顺序由上至下

虽然表的写的顺序是t1 ,t2, t3但是在mysql的id都为1，那么顺序执行，执行的顺序为t1,t3,t2，说明了咱们写的执行的顺序和mysql的执行顺序可能不同

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

id大的优先级别越高，所以执行的顺序为t3,t1,t2,select 中的subquery代表的是子查询，primary的代表的是最外层的查询，最后被执行

3.id相同和id不同,同时存在

id大的先执行，所以t3先执行，再按顺序执行，derived2 ，t2,顺序执行
derived代表的是衍生的表示虚表 s1 ,后面的2代表的是由id为2的表衍生而来，也就是t3

---

2.select_type(数据读取操作的操作类型)
1.有哪些？

simple:简单的select查询，查询中不包含子查询或者union

primay:查询中如包含任何的赋值的子部分，最外层的查询标记为

subquery:在select或者where列表中包含了子查询

derived:在from列表中包含的子查询被标记为derived(衍生)，mysql会递归执行这些子查询，把结果放在临时里

union:若第二个select出现在union之后则被标记为union,若union包含from字句的子查询中，外层的select将标记为;derived

union result：从union表中获取结果的select

---

3.table:显示数据是那一张表的

4.type
有哪些？

显示了查询使用了何种类型，从最好到最差的依次为：
system>const>eq_ref>ref>range>index>ALL
一般来说达到range级别就可以了，最好达到ref级别最好

system:表只有一条记录（等于系统表，这是const类型的特例），平时不会出现，这个可以忽略不计

const:表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref

ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独的值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现

range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in 等查询，这种范围扫描的所有会比全表扫描的要好，因为他只用从某个点开始，结束与另一点，不用扫描全部的索引

index:full index scan,index和all的区别为index类型只遍历索引树，通常比all块，因为索引文件通常比数据文件小，（也就是说虽然all和index都是读全表，但是index是从索引读取的，而all是从硬盘中读取的）

all:full table scan 将遍历全表找到匹配的行

---

5.possible_keys和key

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

key:实际使用的索引，如果为null,则没有使用索引，查询中如果使用了覆盖索引，则该索引仅出现在key列表中

查询的字段和索引的字段，顺序个数都一样，此时的数据可以直接从索引中获取。

6.key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确的情况下，长度越短越好.

key_len显示的值为索引字端的最大可能长度，并非实际使用长度，即key_len是根据表的定义计算而得，不是检索出来的

---

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

8.rows:这里是执行计划中估算的扫描行数，不是精确值

---

Extra:额外信息
1.Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中午无法用索引完成的排序叫做“文件排序”

Using temporary :使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询中 group by

索引建立是col1和col2只要查找col2，说明索引部分被使用

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

覆盖索引：就是select的数据列只用索引就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次的读取数据文件，总而言之就是查询列要被所建的索引覆盖

eg.