第三节：MySQL索引优化规则套路实战1(索引分析、索引失效、查询优化)

一. 索引分析

1. 单表索引分析

数据准备：创建arcile表，并插入数据。

--创建arctile表，并插入数据
DROP TABLE IF EXISTS `article`;
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`author_id` INT(10) UNSIGNED NOT NULL COMMENT '作者id',
`category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
`views` INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
`comments` INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
`title` VARCHAR(255) NOT NULL COMMENT '标题',
`content` VARCHAR(255) NOT NULL COMMENT '正文内容'
) COMMENT '文章';
--插入数据
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`,`content`) VALUES(1,1,1,1,'1','1');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`,`content`) VALUES(2,2,2,2,'2','2');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`,`content`) VALUES(3,3,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`,`content`) VALUES(1,1,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`,`content`) VALUES(1,1,4,4,'4','4');

(1).  查询 category_id 为1且 comments 大于1的情况下， views 最多的 article_id 。

SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

　A. 查看执行计划如下：

　B. 剖析：该语句全表扫描了，并且用到了文件内排序，需要优化。 

(2). 给category_id,comments,views三个字段添加复合索引。

CREATE INDEX idx_article_ccv ON article(category_id,comments,views); 

　A. 通过指令或者客户端查看索引：

　B.  分析执行计划：

　剖析：从全表扫描变为range，即指定范围内的索引扫描，性能提升了，但是在 order by 排序的时候没有用到索引，MySQL居然还是用的 Using filesort，使用了文件内排序，需要继续优化。

　我们把最初的SQL语句改为 comments=1， 继续查看执行计划：

SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1

 　级别提升为ref了，即非唯一性索引扫描，而且也没有文件内扫描了，所以我们的出来一个结论：当 comments > 1 的时候 order by 排序 views 字段索引就用不上，但是当 comments = 1 的时候 order by 排序 views 字段索引就可以用上！！！所以，范围之后的索引会失效。

(3). 删除原先的索引，重新基于 category_id 和 views 创建索引。

DROP INDEX  idx_article_ccv on article;
CREATE INDEX idx_article_cv ON article(category_id,views);

查看最初SQL语句的执行计划： 

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

剖析：查询和排序就都用上索引了，优化完毕。 

2. 两表索引分析

数据准备：创建book表和class表。 

--创建表
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '商品类别';
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

(1). 在没有任何索引的情况下，查看下面语句的执行计划

select * from book left join class on book.card=class.card;

 剖析：两张表均为全表扫描。

 思考：那么索引是加在左表book上，还是右表class上呢？

(2). 在左表book上添加索引，然后查看执行计划

/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);
--删除索引
DROP INDEX idx_book_card ON book;

 剖析：右表依旧是全表扫描，不是我们所想要的。

(3). 在右表class上添加索引，然后查看执行计划。 （需要先删除book表上的索引）

/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);
--删除索引
DROP INDEX idx_class_card ON class;

 

剖析： 虽然坐标是全表扫描，但是Extra中没有 Nested Loop （嵌套循环）的总次数，所以比(2)中的方案好。

由此我们得出来一个结论：

　　左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适。

3. 三表索引分析 

数据准备：在上面两表的基础上再加一张phone表。

DROP TABLE IF EXISTS `phone`;
CREATE TABLE IF NOT EXISTS `phone`(
`phone_id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '手机';

(1). 在没有任何索引的情况下，查看下面语句的执行计划

select * from book 
left join class on book.card=class.card
left join phone on phone.card=class.card;

 剖析：均是全表扫描

(2). 有了上面的经验，我们在两张右表 class 和 phone表上添加索引，然后查看执行计划。

/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);
/* 在phone表上创建索引 */
CREATE INDEX idx_phone_card ON phone(card);
--删除索引
DROP INDEX idx_class_card ON class;
DROP INDEX idx_phone_card ON phone;

 剖析：如上图，book虽是ALL，但没有Nested Loop，另外两张表均达到ref级别，达到了我们所想要的优化结果。

4. 总结

　(1). 尽可能减少 JOIN 语句中的 NestedLoop （嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。

　(2). 优先优化 NestedLoop 的内层循环。

　(3). 保证 JOIN 语句中被驱动表上 JOIN 条件字段已经被索引。

　(4). 当无法保证被驱动表的 JOIN 条件字段被索引且内存资源充足的前提下，不要太吝惜 JoinBuffer 的设置。

 

二. 索引失效

数据准备： 

CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';

INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('Ringo', 18, 'manager');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');
/* 创建索引 */
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);

1. 索引失效汇总

　全值匹配我最爱。

　(2). 最佳左前缀法则。

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

　(4). 索引中范围条件右边的字段会全部失效。

　(5). 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少 SELECT * 。

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

　(7). is null 、 is not null 也无法使用索引。

　(8). like 以通配符开头 %abc 索引失效会变成全表扫描。

　(9). 字符串不加单引号索引失效。

　(10). 少用 or ，用它来连接时会索引失效。

2. 最佳左前缀法则

(1). 经典例子

/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';
/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;
/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` ='manager';
/* 索引没用上，ALL全表扫描，因为跨过了name */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';
/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

剖析，最后一句的执行计划为：

key_len：98 ，正好是是name字段上的索引  4*24+2=98，所以通过这里的计算也可以说明pos字段索引失效。 

(2). 分析

　最佳左前缀法则：如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。

　口诀：带头大哥不能死，中间兄弟不能断。

3. 索引列上不计算

(1). 需求：

　　现在要查询`name` = 'Ringo'的记录下面有两种方式来查询！

(2). 方案1

SELECT * FROM `staffs` WHERE `name` = 'Ringo';

执行计划如下：

(3). 方案2

 SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';

执行计划如下：索引失效。

总结：由此可见，在索引列上进行计算，会使索引失效。

4. 范围之后全失效

(1). 经典语句及其执行计划

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` ='manager';
/* 用到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` ='dev';

执行计划如下：

 

(2). 分析 

　name的key_len: 4*24 +2 =98

　age的key_len: 4    (age是int类型，且不为空)

　pos的key_len: 4*20+2=82

184=98+4+82， 全值匹配，102=98+4，只匹配了name和age。

总结：

　　由此可知，查询范围的字段使用到了索引，但是范围之后的索引字段会失效。口诀：范围之后全失效。

5. 覆盖索引尽量用

(1). 经典语句及其执行计划

/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` ='manager';
/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age`= 18 AND `pos` = 'manager';

执行计划如下：

(2). 分析

　通过执行计划可以明显的看出，第二条语句使用了覆盖索引。

6. 不等有时会失效

/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';

7. Like百分百加右边

(1). 全字段搜索（select *） 

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
/* 索引有效，使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';

(2). 使用覆盖索引

　如果一定要在右边加 % ，而且还要保证索引不失效，那么使用覆盖索引来编写SQL。

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE'%in';

8. 字符要加单引号

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE name = 'Ringo';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

PS：这里name = 2000 在MySQL中会发生强制类型转换，将数字转成字符串。

9. 总结

(1). 索引优化的一般性建议：

　对于单值索引，尽量选择针对当前 query 过滤性更好的索引。

　在选择复合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

　在选择复合索引的时候，尽量选择可以能够包含当前 query 中的 where 子句中更多字段的索引。

　尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的。

(2). 口诀：

　带头大哥不能死。

　中间兄弟不能断。

　索引列上不计算。

　范围之后全失效。

　覆盖索引尽量用。

　不等有时会失效。

　like百分加右边。

　字符要加单引号。

　一般SQL少用or。

10. 实战训练

(1). 数据准备

/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);
/* 插入数据 */
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('a1','a2','a3','a4','a5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('b1','b22','b3','b4','b5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('c1','c2','c3','c4','c5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('d1','d2','d3','d4','d5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('e1','e2','e3','e4','e5');
/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);

(2). 实战演练

/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */
/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';
/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';
/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';
/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';
/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';
/*
 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;
/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;
/*
 8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;
/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`,`c3`;
/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`,`c2`;
/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY c2,c3;
/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;
/*
 13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort
  因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不
用排序了！
  所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;
/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */
/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;
/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

PS：GROUP BY 基本上都需要进行排序，索引优化几乎和 ORDER BY 一致，但是 GROUP BY 会有临时表的产生。

(3). 加深印象

   Index（a,b,c）

 

 

 

三. 查询优化

1. 小表驱动大表

(1). 通过for循环理解小表驱动大表

/**
* 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
* 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
* 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
* 第一种可以理解为，和MySQL建立1000次连接每次查询5次。
*/
for(int i = 1; i <= 5; i ++){
 for(int j = 1; j <= 1000; j++){
   
 }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
 for(int j = 1; j <= 5; j++){
   
 }
}

(2). in 和 exits的用法

/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */
/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

EXISTS：

语法： SELECT....FROM tab WHERE EXISTS(subquery); 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（ true 或是false ）来决定主查询的数据结果是否得以保留。

提示：

　EXISTS(subquery) 子查询只返回 true 或者 false ，因此子查询中的 SELECT * 可以是SELECT 1 OR SELECT X ，它们并没有区别。

　EXISTS(subquery) 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。

　EXISTS(subquery) 子查询往往也可以用条件表达式，其他子查询或者 JOIN 替代，何种最优需要具体问题具体分析。

2. order by优化

数据准备：

CREATE TABLE `talA`(
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);
/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);

(1). 经典例子

/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;
/* 2.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;
/* 3.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;
/* 4.没有使用索引进行排序 产生了Using filesort ，顺序颠倒了*/
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;
/* 5.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;
/* 6.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;
/* 7.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;
/* 8.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

分析说明：

　(1). ORDER BY 子句，尽量使用索引排序，避免使用 Using filesort 排序。

　(2). MySQL支持两种方式的排序， FileSort 和 Index ， Index 的效率高，它指MySQL扫描索引本身完成排序。 FileSort 方式效率较低。

　(3). ORDER BY 满足两情况，会使用 Index 方式排序：

　　A. ORDER BY 语句使用索引最左前列。

　　B. 使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

(2).补充 FileSort 排序相关(了解即可)

　如果排序不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

A、双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 ORDER BY 列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在 buffer 中进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

B、单路排序算法：从磁盘读取查询需要的所有列，按照 ORDER BY 列在 buffer 対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。由于单路排序算法是后出的，总体而言效率好过双路排序算法。但是单路排序算法有问题：如果 SortBuffer 缓冲区太小，导致从磁盘中读取所有的列不能完全保存在 SortBuffer 缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

C. 单路复用算法的优化策略：

　增大 sort_buffer_size 参数的设置。

　增大 max_length_for_sort_data 参数的设置。

D. 提高ORDER BY排序的速度：

　ORDER BY 时使用 SELECT * 是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：当查询的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会使用单路排序算法，否则使用多路排序算法。

　两种排序算法的数据都有可能超出 sort_buffer 缓冲区的容量，超出之后，会创建 tmp 临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size 参数的设置。

　尝试提高 sort_buffer_size ：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

　尝试提高 max_length_for_sort_data ：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量 sort_buffer_size 的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

3. group by优化

(1). GROUP BY 实质是先排序后进行分组，遵照索引建的最佳左前缀。

(2). 当无法使用索引列时，会使用 Using filesort 进行排序，增大 max_length_for_sort_data参数的设置和增大 sort_buffer_size 参数的设置，会提高性能。

(3). WHERE 执行顺序高于 HAVING ，能写在 WHERE 限定条件里的就不要写在 HAVING 中了。

4. 总结

为排序使用索引，MySQL两种排序方式： Using filesort 和 Index 扫描有序索引排序。　MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。

/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)
/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;
/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;
/* 3.不能使用索引进行排序 */
ORDER BY a ASC, b DESC, c DESC; /* 排序不一致 */
WHERE g = const ORDER BY b, c;  /* 丢失a字段索引 */
WHERE a = const ORDER BY c;   /* 丢失b字段索引 */
WHERE a = const ORDER BY a, d;  /* d字段不是索引的一部分 */
WHERE a IN (...) ORDER BY b, c; /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询
*/

 

 

 

 

 

 

!

• 作       者 : Yaopengfei(姚鹏飞)
• 博客地址 : http://www.cnblogs.com/yaopengfei/
• 声     明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
• 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。