缘起

• 查询数据过多
• 关联了太多的表，太多 JOIN
  • JOIN 原理：用 A 表的每一条数据 扫描 B 表的所有数据，所以尽量先过滤
• 没有利用好索引
  • 索引针对 列 建索引，但并不可能每一列都建索引
  • 索引并非越多越好，当数据更新了，索引会进行调整。也会很消耗性能
  • MySQL 并不会把所有索引都用上，只会根据其算法挑一个索引使用

常见通用的 JOIN 查询

SQL 执行顺序

手写顺序

SELECT DISTINCT
	< SELECT_LIST > 
FROM
	< LEFT_TABLE > < JOIN_TYPE >
	JOIN < RIGHT_TABLE > ON < JOIN_CONDITION > 
WHERE
	< WHERE_CONDITION > 
GROUP BY
	< GROUP_BY_LIST > 
HAVING
	< HAVING_CONDITION > 
ORDER BY
	< ORDER_BY_CONDITION > 
	LIMIT < LIMIT_NUMBER >

机读顺序

1 FROM < LEFT_TABLE >
2 ON < JOIN_CONDITION >
3 < JOIN_TYPE > JOIN < RIGHT_TABLE >
4 WHERE < WHERE_CONDITION >
5 GROUP BY < GROUP_BY_LIST >
6 HAVING < HAVING_CONDITION >
7 SELECT
8 DISTINCT < SELECT_LIST >
9 ORDER BY < ORDER_BY_CONDITION >
10 LIMIT < LIMIT_NUMBER >

随着 MySQL 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序

七种 JOIN 理论

• MySQL 的 JOIN 算法原理基于嵌套循环查询，但是由于简单的嵌套循环查询存在效率低下，频繁与磁盘进行 I/O 的问题，故MySQL会分别从减少内层循环次数，以及减少I/O次数两个层面对 JOIN 的简单嵌套循环查询进行优化

• 从本质上来说，MySQL的join算法基于简单嵌套循环查询，即外层表的记录作为条件，循环遍历内层表进行查询，返回内层循环中满足条件的记录，对于SQL语句：SELECT * FROM a LEFT JOIN b ON a.id = b.id; 如下图所示

for(i from 外层表){
    for(j from 内层表){
        if(i.id = j.id){
            return;
        }
    }
}

• 但是简单的嵌套循环查询效率比较低，本质上是因为每次查询时都是从外层表取出一次记录，即进行一个 I/O，并且对内层表的查询是全表扫描，故可以从这两个角度对简单嵌套循环查询进行优化

内层循环的查询可以通过索引进行优化，通过索引进行查找的次数只为索引 B 树的高度，这就是索引嵌套循环连接，对于上述的SQL语句：SELECT * FROM a LEFT JOIN b ON a.id = b.id;，如果在 b 表对 id 字段建立了索引，则外层的循环查询在内层循环中会使用到索引，从而减少了查询的次数

另外一种优化的方法就是从优化 I/O 的角度，每次取外层表的多条记录进行缓存，多条记录同时在内层循环中进行查找

下面以 Employee 表和 Department 作为演示案例

左连接

左连接将左表作为主表，右表作为从表，左表作为外层循环，在右表中进行匹配，如果左表的记录在右表中没有匹配，则将该左表记录的右表项补空值

SELECT * FROM employee a LEFT JOIN department b ON a.deptId = b.id;

右连接

右连接将右表作为主表，左表作为从表，右表作为外层循环，在左表中进行匹配，如果右表的记录在左表中没有匹配，则将该右表记录的左表项补空值

SELECT * FROM employee a RIGHT JOIN department b ON a.deptId = b.id;

内连接

内连接将左表和右表对于条件相匹配的项进行组合，在结果中只会出现同时在左表和右表出现的项

SELECT * FROM employee a INNER JOIN department b ON a.deptId = b.id;

左外连接

左外连接选择将左表作为主表，右表作为从表，循环遍历右表，查找与条件满足的项，如果在右表中没有匹配的项，则补空值，并且在结果集中选择只在左表中存在的数据

SELECT * FROM employee a LEFT JOIN department b ON a.deptId = b.id WHERE b.id IS NULL;

右外连接

右外连接选择将右表作为主表，左表作为从表，循环遍历左表，查找与 JOIN 条件满足的项，如果在左表中没有匹配的项，则补空值，并且在结果集中选择只在右表中存在的数据

SELECT * FROM employee a RIGHT JOIN department b ON a.deptId = b.id WHERE a.deptId IS NULL;

全连接

全外连接将左表和右表的所有记录进行匹配，如果在另外表项中不存在记录，则补空值

SELECT * FROM employee a LEFT JOIN department b ON a.deptId = b.id 
UNION
SELECT * FROM employee a RIGHT JOIN department b ON a.deptId = b.id;

全外连接

全外连接将全连接中左右表相交的部分排除

SELECT * FROM employee a LEFT JOIN department b ON a.deptId = b.id WHERE b.id IS NULL 
UNION
SELECT * FROM employee a RIGHT JOIN department b ON a.deptId = b.id WHERE a.deptId IS NULL;

索引介绍

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构
• 排好序的快速查找数据结构
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
• 我们平常所说的索引，如果没有特别指明，都是指 B 树 (多路搜索树，并不一定是二叉的) 结构组织的索引，其中聚集索引，次要索引，覆盖索引，
  复合索引，前缀索引，唯一索引默认都是使用 B+ 树索引，统称索引，当然，除了 B+ 树这种类型的索引之外，还有哈稀索引 (hash index) 等

优势

• 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE，因为更新表时，MySQL不仅要保存数据，还要保存索引文件，每次更新添加了索引列的字段都会调整更新所带来的键值变化后的索引信息
• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

索引结构

B-Tree 索引

m阶B树需满足以下要求

• m 阶 B 树：m 阶指的是分叉的个数最多为 m 个，即一个非叶子节点最多可以有m个子节点。
• 子节点：一个叉连接的表示一个子节点，一个非叶子节点可以表示为 [A0 k1 A1 k2 A2……kn An]，其中ceil(m/2) -1 <= k <= m-1个，因此 ceil(m/2) <= A <= m，A表示指向子节点的指针
• 根节点至少有两个子节点
• 所有的叶子在同一层
• 叶子结点不包含元素，所以可以把叶子结点看成在树里实际上并不存在外部结点，指向这些外部结点的指针为空，叶子结点的数目正好等于树中所包含的元素总个数加1

特点

• 元素集合分布在整颗树中
• 任何一个元素出现且只出现在一个节点中
• 搜索有可能在非叶子节点结束
• 因为每个节点中的元素和子树都是有序的**，**其搜索性能等价于在元素集合内做一次二分查找
• B 树在插入删除新的数据记录会破坏 B-Tree 的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持 B-Tree 性质

初始化介绍

一颗 B 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块，真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99，非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如 17、35并不真实存在于数据表中

查找过程

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO

为什么要使用 B 树作为数据库索引而不是使用二叉树？

二叉树的搜索效率是十分高的，可以达到 logN，但是由于数据量巨大时，索引的大小甚至可以达到 G 级别，因为索引是存储在磁盘中的，每次查找只能逐一将索引树的节点加载至内存中，如果使用二叉树则 I/O 操作将会非常频繁，I/O 次数取决于二叉树的深度。这样索引速度非常慢，因此采用 B 树这种多路二叉搜索树大大减少 I/O 次数，其中多路指的是一个节点有多个子树，并且由于所有叶子节点都在同一层，因此也是平衡树

**磁盘页：**查询索引时，逐一加载磁盘页，这里的磁盘页对应索引树的节点，对于 m 阶 B 树，m的大小取决于磁盘页的大小

B+Tree索引

特点

• 有 k 个子树的中间节点包含有 k 个元素（ B树中是 k-1个 ），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点中
• 所有的叶子结点中包含了全部元素的信息，叶子节点本身根据元素的大小顺序链接
• 所有的中间节点元素都同时存在子节点，在子节点元素中是最大（ 或最小 ）元素

B+Tree 与 B-Tree对比

• 在内存有限的情况下，B+TREE 永远比 B-TREE 好，无限内存则后者方便
• B- 树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息
• B+ 树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中 ( 一次查询可能进行两次i/o操作 )
• 在 B- 树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在，而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字，从这个角度看 B- 树的性能好像要比 B+ 树好，而在实际应用中却是 B+ 树的性能要好些，因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B- 树多，树高比 B- 树小，这样带来的好处是减少磁盘访问次数
• 尽管B+树找到一个记录所需的比较次数要比 B- 树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+ 树的性能可能还会好些，而且 B+ 树的叶子节点使用指针连接在一起，方便顺序遍历（ 例如查看一个目录下的所有文件，一个表中的所有记录等 ），这也是很多数据库和文件系统使用B+ 树的缘故

为什么说 B+ 树比 B- 树更适合实际应用中操作系统的文件索引和数据库索引？

• B+ 树的磁盘读写代价更低，B+ 树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对 B 树更小，如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了
• B+ 树的查询效率更加稳定，由于最终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以任何关键字的查找必须走一条从根结点到叶子结点的路，所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

什么是卫星数据？

索引元素所指向的数据记录，比如数据库中的某一行

在 B 树中无论是中间节点还是叶子节点都带有卫星数据，但是在B+树中只有叶子节点带有卫星数据，中间节点仅仅是索引

聚簇索引与非聚簇索引

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB 的聚族索引实际上在同一个结构中保存了 B-Tree 索引和数据行

• 当表有聚族索引时，它的数据行存放在索引的叶子页中，术语“聚族”表示数据行和相邻的键值紧凑的存储在一起，因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚族索引。

• 因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚族索引，这里我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚族索引的存储引擎都是适用的

• 在 InnoDB 中通过主键聚集数据，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚族索引，InnoDB 只聚集在同一个页面中的记录，包含相邻键的页面可能会相距甚远

优点

• 可以把相关数据保存在一起。例如实现电子邮件时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件，如果没有使用聚族索引，则每封邮件都可能导致一次磁盘I/O
• 数据访问更快，聚族索引将索引和数据保存在同一个 B-Tree 中，因此从聚族索引中获取数据通常比在非聚族索引中查找更快
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值

聚族索引的缺点

• 聚簇数据最大限度的提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有那么优势了
• 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表
• 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作，页分裂会导致表占用更多的磁盘空间
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列
• 二级索引访问需要两次索引查找，而不是一次

备注：有关二级索引需要两次索引查找的问题？

要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行，这里做了重复的工作：两次B-Tree查找而不是一次，对于InnoDB，自适应哈希索引能够减少这样的重复工作

Full-Text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果

SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

Hash 索引

• Hash索引只有 Memory, NDB 两种引擎支持，Memory 引擎默认支持 Hash 索引，如果多个 Hash 值相同，出现哈希碰撞，那么索引以链表方式存储
• NoSql采用此中索引结构

R-Tree索引

• R-Tree在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有 myisam、bdb、innodb、ndb、archive 几种。

• 相对于 b-tree，r-tree 的优势在于范围查找

索引分类

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

# 单独建主键索引
ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);  
 
# 删除主键索引：
ALTER TABLE customer DROP PRIMARY KEY;  

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);
 
 
# 单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
 
# 删除索引
DROP INDEX idx_customer_name ;

唯一索引

索引列的值必须唯一，但允许有空值

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);

# 单独建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
# 删除索引
DROP INDEX idx_customer_no on customer ;

复合索引

• 即一个索引包含多个列
• 在数据库操作期间，复合索引比单值索引所需要的开销更小 (对于相同的多个列建索引)
• 当表的行数远大于索引列的数目时可以使用复合索引
• 遵循最左匹配原则，创建的符合索引为 ( a, b, c )，那么相当于是 ( a,b,c )，( a,b )，( a )，这样查询才会走索引、

关于复合索引

MySQL 的最左匹配原则指的是 WHERE 之后的查询字段

SELECT 之后的字段只要在索引中，就会走索引

自 MySQL 5.7 之后，查询的字段只要包含第一个索引字段，MySQL 会自动优化排序，将该字段放在最前面

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
# 单独建索引
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 

# 删除索引
DROP INDEX idx_no_name on customer ;

覆盖索引

• 查询的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 SELECT 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
• 使用 Explain，可以通过输出的 extra 列来判断，对于一个索引覆盖查询，显示为 using index，MySQL 查询优化器在执行查询前会决定是否有索引覆盖查询

哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引 ( where 后面的语句 )
• 查询中与其它表关联的字段，外键关系建立索引
• 在高并发下倾向创建组合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

哪些情况不需要创建索引

• 表记录太少
• 经常增删改的表
  • 提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE
  • 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
• Where 条件里用不到的字段不创建索引
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

性能分析

MySQL常见瓶颈

• CPU
  • SQL中对大量数据进行比较、关联、排序、分组
• IO
  • 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO
  • 查询执行效率低，扫描过多数据行
• 锁
  • 不适宜的锁的设置，导致线程阻塞，性能下降
  • 死锁，线程之间交叉调用资源，导致死锁，程序卡住

Explain

id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序 id 相同，执行顺序由上至下

id 相同

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

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

id 不同

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

id 既有重复的也有不同的

id 如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id 值越大，优先级越高，越先执行

衍生表 = derived2 --> derived + 2（ 2 表示由 id = 2 的查询衍生出来的表，type 肯定是 all ，因为衍生的表没有建立索引 ）

select_type

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

SIMPLE

简单的 select 查询,查询中不包含子查询或者 UNION

PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary

DERIVED

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

DERIVED 是通过子查询查出来的临时表

SUBQUERY

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

DEPENDENT SUBQUERY

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

SUBQUERY 与 DEPENDENT SUBQUERY

SUBQUERY ：子查询结果为多值

DEPENDENT SUBQUERY：查询结果为单值

UNCACHEABLE SUBQUREY

无法被缓存的子查询，例如环境参数

UNION

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

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

UNION RESULT

从 UNION 表获取结果的 SELECT

table

显示这一行的数据是关于哪张表的

type

type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range (尽量保证 ) > index > ALL

system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

system

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

const

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

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 连接查询

index_subquery

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

unique_subquery

该联接类型类似于 index_subquery，子查询中的唯一索引

备注：一般来说，得保证查询至少达到 range 级别，最好能达到ref

possible_keys

显示可能应用在这张表中的索引，一个或多个

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

key_len 字段能够帮你检查是否充分的利用上了索引

ref

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

rows

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

Extra

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

Using filesort

MySQL 中无法利用索引完成的排序操作称为“文件排序”

说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

查询排序的字段，排序字段若通过索引去访问将大大提高排序速度

Using temporary

使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表

常见于排序 order by 和分组查询 group by

USING index

表示相应的 select 操作中使用了覆盖索引 (Covering Index) ，避免访问了表的数据行

如果同时出现 using where，表明索引被用来执行索引键值的查找

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

Using where

表明使用了 where 过滤

using join buffer

使用了连接缓存

impossible where

where子句的值总是 false，不能用来获取任何元组

select tables optimized away

在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

MyISAM 中会维护总行数 ( 还有其他参数 ) 这个参数，所以在执行查询时不会进行全表扫描，而是直接读取这个数，但会对增删产生一定的影响，根据业务情况决定谁好谁坏，Innodb 中没有这个机制

查询优化

使用索引

全值匹配

最佳左前缀法则

• 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
• AND 会忽略左右关系，即使没有没有按顺序查询，也会被优化器自动优化
• 经过试验结论，建立了 idx_name_age 索引，id 为主键
• 当使用覆盖索引的方式时，( select name/age/id from staffs where age=10; ( 后面没有其他没有索引的字段条件 ) )，即使不是以 name 开头，也会使用idx_name_age 索引，如果 select 后的字段有索引，where 后的字段也有索引，则无关执行顺序
• 除开上述条件 才满足最左前缀法则

不在索引列上做任何操作（计算、函数、( 自动or手动 ) 类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引 ( 只访问索引的查询 ( 索引列和查询列一致 ) )，减少 select *

MySQL 在使用不等于 ( != 或者 <> ) 的时候无法使用索引会导致全表扫描

is not null 也无法使用索引,但是 is null 是可以使用索引的

LIKE 以通配符开头 ('%abc...') MySQL 索引失效，会变成全表扫描的操作

解决 LIKE '%字符串%' 时索引不被使用的方法？

使用覆盖索引

字符串不加单引号索引失效

底层使用了字符串转换函数导致索引失效

少用 or，用它来连接时会索引失效

总结

假设设置 index(a,b,c)

单表查询优化

关联查询优化

保证被驱动表的 JOIN 字段已经被索引 （ JOIN 之后的表 ）

连接查询时，选择小表作为驱动表，大表作为被驱动表，例如：LEFT JOIN 时一定是左边是驱动表，右边是被驱动表

INNER JOIN 时，MySQL 会自己帮你把小结果集的表选为驱动表

子查询尽量不要放在被驱动表，有可能使用不到索引，若必须用到子查询，可将子查询设置为驱动表，因为驱动表的 type 肯定是 all，而子查询返回的结果表没有索引，必定也是 all

子查询优化

有索引的情况下用 INNER JOIN > IN > EXISTS

ORDER BY 优化

ORDER BY 子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

Index 排序

• Index 是 MySQL 扫描索引本身完成排序

• 使用 Index 满足条件

  • ORDER BY 语句使用索引最左前列
  • 使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最左前列
  • WHERE 子句中如果出现索引的范围查询 (即 explain 中出现 range ) 会导致 ORDER BY 索引失效

FileSort

• 双路排序：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和 ORDER BY 列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段
• 单路排序：从磁盘读取查询所需要的所有列，按照 ORDER BY 在 buffer 对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里

单路排序的问题

在 sort_buffer 中，单路排序比双路排序要多占用很多空间，因为单路排序是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取 sort_buffer 容量大小，再排……从而多次I/O

优化策略

• 增大 sort_buffer_size 参数的设置，提高单路排序的内存大小
  • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
• 增大 max_length_for_sort_data 参数的设置，提高单次排序字段大小
  • 提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率
• 去掉 select 后面不需要的字段
  • 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会使用单路排序， 否则用多路排序
  • 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size

LIMIT

场景：ORDER BY 后的字段有索引，sql 中有 LIMIT 时

• 当 select id 或其他索引包含的字段时，显示 using index
• 当 select 后的字段含有 ORDER BY 字段索引不包含的字段时，将显示 using filesort

GROUP BY

• GROUP BY 实质是先排序后进行分组，遵照索引的最佳左前缀
• 当无法使用索引列，增大 max_length_for_sort_data 参数的设置和增大 sort_buffer_size 参数的设置
• where 高于having，能写在 where 限定的条件就不要去 having 限定了

去重优化

尽量不要使用 distinct