MySQL高级-索引优化（超详细）

性能分析

MySQL Query Optimizer

Mysql中由专门负责优化SELECT语句的优化器，主要功能就是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的，但**不一定是DBA觉得最优的，这部分最耗时间**）。

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

MySQL常见瓶颈

CPU

• CPU在包和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

IO

• 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。

服务器硬件

• 硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态。

Explain（重点）

是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从知道MySQL是如何处理你的SQL语句的，分析你查询语句或者是表结构的性能瓶颈。

在 MySQL 5.7 中，会对衍生表进行合并优化，如果要直观的查看 select_type 的值，需要临时关闭该功能（默认是打开的）

-- 关闭衍生表的合并优化
set global optimizer_switch='derived_merge=off';

-- 关闭衍生表的合并优化（只对该会话有效）
set session optimizer_switch='derived_merge=off'; 

-- 打开衍生表的合并优化（只对该会话有效）
set session optimizer_switch='derived_merge=on';

能干嘛

1. 表的读取顺序
2. 哪些索引可以使用
3. 数据读取操作的操作类型
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

怎么玩

Explain + SQL语句即可。

各字段分析

测试案例的sql

 CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
  INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
  INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
  INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

id字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

情况一：id相同

id相同，执行顺序由上至下

EXPLAIN SELECT * 
FROM t1, t2, t3
WHERE 
    t1.id = t2.id 
    AND t2.id = t3.id;

此例中，执行了 where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id，而 t2.id 的结果是建立在 t2.id = t3.id 的基础上。

也可能会出现执行顺序为 t1 -> t3 -> t2 的情况。

情况二：id不同

EXPLAIN SELECT t2.* 
FROM t2 
WHERE id = (SELECT t1.id            
            FROM t1            
            WHERE id = (
                SELECT t3.id                        
                FROM t3));

由结果我们可以看出，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行。

可以理解为，括号的优先级。

id递增 -> id值越大 -> 优先级越高 -> 越先被执行

情况三：id相同不同，同时存在

EXPLAIN SELECT t2.* 
FROM (SELECT t3.id
      FROM t3) AS s1, t2
WHERE s1.id = t2.id;

id如果相同，可以认为是一组，组内从上往下执行，所以在所有组中，id值越大优先级越高，优先级越高的那一组，越先执行。

结果一度让我震惊，因为没有出现衍生表。我去查了一下，出现派生表并不是一个好的查询方案，派生表本质也就是临时表，如果过大也许不得不在磁盘上创建而非内存中，这样创建和访问都是要消耗IO资源的，可能在MySQL5.7中的优化器，会自动帮忙优化派生表，将派生表合并到外层查询中。

比如：

-- 优化前
SELECT * FROM (SELECT * FROM t1) AS s1;

-- 优化后
SELECT * FROM t1;

select_type

查询的类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。

有哪些

总和就这么多：

下面我们来逐个的看一下。

• SIMPLE

简单的SELECT查询，查询中不包含子查询或者UNION

• PRIMARY

  查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY，最后执行的那个。

​ 5.7版本的优化器直接优化了，这里用别人的图。

• DERIVER

在FROM列表中包含的子查询被标记为DERIVERD（派生）MySQL会递归执行这些子查询，把结果放在临时表中。

• SUBQUERY

在SELECT或WHERE列表中包含了子查询。

• DEPENDENT SUBQUERY（5.7中没有）

在SELECT或WHERE列表中包含了子查询，子查询基于最外层

DEPENDENT SUBQUERY 与 SUBQUERY 的区别

依赖子查询：子查询结果为多值 | 子查询：查询结果为单值。

• UNCACHEABLE SUBQUREY（5.7中没有）

无法被缓存的子查询，估计就是临时表太大了。

• UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；

若UNION包含在FROM子句的子查询中，外层的SELECT将会被标记为DERIVED

• UNION RESULT

使用 union 关键词后会生成一个临时表，对于这个临时表的 select 。

table

对应行查询的表。

• 当 from 子句中如果有子查询的话，table 列为是 deriverN 的格式，表示这一行执行的是 id = N 行的查询。
• 当有 union 时，table 的数据为 <union M,N> 的格式， M 和 N 表示参与 union 的 select 行 id。

type（非常重要）

type表示这行查询的关联类型（访问类型，或查询类型），通过该值可以了解该行查询数据记录的大概范围。

他的值与我们是否优化过SQL息息相关。

常见的值依次从最优到最差分别为：system > const > eq_fef > ref > range > index > all；

一般情况下，我们要保证效率的话，要优化我们的语句至少使其达到 range 级别，如果可能的话最好优化到 ref； range 级别一般用于范围查找，所以换句话说，除了范围查找，我们其它查询语句最好是优化到 ref 级别。

有哪些

常见的就只有这些：

下面我们就来逐一看一下总共会出现多少种情况：

• NULL

  • 表示MySQL能够在优化阶段分解查询语句，在执行阶段不用访问表和索引。

• system / const

  • MySQL能对某个查询部分进行优化并将其转化成一个常量（可以通过 show warnings 查看优化的结果），主要是查询主键（primary key）或唯一索引（Unique Key）对应的记录，因为不存在重复，所以最多只能查询出一条记录，所以速度比较快。system 是 const的特例，当临时表只有一条记录时为system。

• eq_ref

  • 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。

  

• ref

  • 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

• range

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

• index

  • Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
  • 也就是说虽然all和index都是读全表，但是index是从索引中读取，all是从硬盘中读取的。
    

• all

  • Full Table Scan，将遍历全表以找到匹配的行。

• index_merge

  • 在查询过程中需要多个索引组合使用，通常出现在有 or 关键字的 sql 中。

• ref_or_null

  • 对于某个字段既需要关联条件，也需要null值的情况下，查询优化器会选择用ref_or_null连接查询。
  • 对比上面，主键id一般不能为null所以不是ref_or_null。

  

• index_subquery

  • 利用索引来关联子查询，不再扫描全表。

• unique_subquery

  • 该连接类型类似于index_subquery。子查询中唯一索引。
    

possible_keys

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

key

• 实际使用的索引。如果为null，则没有使用索引
• 查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
  • 也就是说，当查询某一字段时，且那个字段有对应索引时，key的值会显示为索引，而不是null。

key_len

• 表示索引中使用的字节数，通过该列计算查询中使用的索引的长度，在不损失精度的情况下，通常越少越好。
• 它显示的是最大可能长度，而并非一定就是实际使用长度。
• 即它是根据表定义计算得到而不是通过表内检索而得到的。
• key_len字段能够帮你检查是否充分的利用上了索引。

ref

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

rows

• rows列显示MySQL认为它执行查询时必须检查的行数。

Extra（重要）

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

1. Using filesort

   • 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取的。
   • MySQL中无法利用索引完成的排序操作称为“文件内排序”。
   • 出现这种情况等于说是九死一生了。
   • 比如说复合索引，只用到了复合索引的一个字段，那么它是没法排序的，就会出现。

2. Using temporary

   • 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。
   • 常见于排序 order by 和分组查询 group by。
   • 要在分组中使用索引，你也得遵循索引的定义顺序，不能空中楼阁，否则就容易引起这个Extra，顺带再带一个上面的Extra

3. Using index

   • 表示相应的select操作中使用了覆盖索引(Covering Index)，避免了表的数据行，效率不错！

   • 如果同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

   • 覆盖索引

     • 简单来说就是我**建立的复合索引的字段，恰好是我要查找的所有字段，并且顺序一致，实际是索引扫描INDEX**。
     • 索引是高效找那行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读行了。
     • ①一个索引②包含了或覆盖了 select子句与查询条件 where子句中③所有需要的字段就叫做覆盖索引。
     • SELECT id , name FROM t_xxx WHERE age = 18;
     • 有一个组合索引 idx_id_name_age_xxx 包含了 id,name,age 三个字段。查询时直接将建立了索引的列取出来了，而不用再去查找所在行的其他数据，效率变高了。
     • 感觉可以用在数据量较大，并且固定字段查询情况多的适合可以使用这种索引。
     • 注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，并且列涵括在覆盖索引中。不可以select *，如果将所有字段一起做索引会导致索引文件过大，