MySQL索引优化

1.数据库优化方案

问题：

哪些方法可以进行数据库调优？

解决方案:

• 索引失效,没有充分利用到索引; 索引建立
• 关联查询太多join(设计缺陷或不得已的需求);SQL优化
• 数据过多; 分库分表

• 服务器调优及各个参数设置(缓冲,线程数等); 调整my.conf

2、性能分析（EXPLAIN）

2.1EXPLAIN是什么?

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

2.2EXPLAIN的用法

用法:

EXPLAIN+SQL语句

数据准备:

USE atguigudb;
 
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, content1 VARCHAR(100) NULL, content2 VARCHAR(100) NULL, PRIMARY KEY (id));

CREATE INDEX idx_content1 ON t4(content1);  -- 普通索引

# 以下新增sql多执行几次，以便演示
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(content1, content2) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)), CONCAT('t4_',FLOOR(1+RAND()*1000)));

2.3、各字段解释

2.3.1、table

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

•  多表关联: t1为驱动表,t2为被驱动表

2.3.2、id

表示查询中执行select子句或操作表的顺序

• id相同：执行顺序由上至下

EXPLAIN SELECT * FROM t1, t2, t3;

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

 注意：查询优化器可能对涉及子查询的语句进行优化，转为连接查询

EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2 WHERE content = 'a');

•  id为null:最后执行

EXPLAIN SELECT * FROM t1 union SELECT *FROM t2;

小结：

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

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

• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

2.3.3、select_type

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

• SIMPLE:简单查询,查询中不包含子查询或者union;

EXPLAIN SELECT * FROM t1;

•  PRIMARY:主查询,查询中若包含子查询,则最外层查询被标记为PRIMARY.
• SUBQUERY:子查询,在select或where列表中包含了子查询

EXPLAIN SELECT * FROM t3 WHERE id = (SELECT id FROM t2 WHERE content = 'a');

•  DEPENDENT SUBQUREY : 如果包含了子查询,并且查询语句不能被转化器转化为连接查询,并且子查询是相关子查询(子查询位于外部数据列),则子查询就是DEPENDENT SUBQUREY

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

•  UNCACHEABLE SUBQUERY:表示这个subquery的查询要受到外部系统变量的影响

EXPLAIN SELECT * FROM t3 
WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

• UNION：对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。

• UNION RESULT：UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是"UNION RESULT"。

• DEPENDENT UNION：子查询中的UNION或者UNION ALL，除了最左边的查询是DEPENDENT SUBQUREY，其余的查询都是DEPENDENT UNION。  

 EXPLAIN SELECT * FROM t1 WHERE content IN
 (
 SELECT content FROM t2 
 UNION 
 SELECT content FROM t3
 );

• DERIVED：在包含派生表（子查询在from子句中）的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。  

 EXPLAIN SELECT * FROM (
   SELECT content, COUNT(*) AS c FROM t1 GROUP BY content
) AS derived_t1 WHERE c > 1;

 

补充：MySQL在处理带有派生表的语句时，优先尝试把派生表和外层查询进行合并，如果不行，再把派生表物化掉（执行子查询，并把结果放入临时表），然后执行查询。下面的例子就是就是将派生表和外层查询进行合并的例子：  

EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE content = 't1_832') AS derived_t1;

•  MATERIALIZED：优化器对于包含子查询的语句，如果选择将子查询物化后再与外层查询连接查询，该子查询的类型就是MATERIALIZED。如下的例子中，查询优化器先将子查询转换成物化表，然后将t1和物化表进行连接查询。

 EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2);

2.3.4、partitions

代表分区表中的命中情况，非分区表，该项为NULL

2.3.5、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

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

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

EXPLAIN SELECT * FROM t1;

• index：当使用覆盖索引，但需要扫描全部的索引记录时

覆盖索引：如果能通过读取索引就可以得到想要的数据，那就不需要读取用户记录，或者不用再做回表操作了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

 -- 只需要读取聚簇索引部分的非叶子节点，就可以得到id的值，不需要查询叶子节点
EXPLAIN SELECT id FROM t1;

 -- 只需要读取二级索引，就可以在二级索引中获取到想要的数据，不需要再根据叶子节点中的id做回表操作
EXPLAIN SELECT id, deptId FROM t_emp;

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

 EXPLAIN SELECT * FROM t1 WHERE id IN (1, 2, 3);

•  ref：通过普通二级索引列与常量进行等值匹配时

explain select*from t_emp where deptId =1;

•  eq_ref：连接查询时通过主键或不允许NULL值的唯一二级索引列进行等值匹配时

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

•  const：根据主键或者唯一二级索引列与常数进行匹配时

EXPLAIN SELECT * FROM t1 WHERE id = 1;

 

•  system：MyISAM引擎中，当表中只有一条记录时。（这是所有type的值中性能最高的场景）

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

 其他不太常见的类型（了解）：

• index_subquery：利用普通索引来关联子查询，针对包含有IN子查询的查询语句。content1是普通索引字段

EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content1 FROM t4 WHERE t1.content = t4.content2) OR content = 'a';

2.3.6、possible_keys 和 keys ☆

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

• keys表示实际使用的索引。如果为NULL，则没有使用索引。

EXPLAIN SELECT id FROM t1 WHERE id = 1;

2.3.7、key_len ☆

表示索引使用的字节数，根据这个值可以判断索引的使用情况，检查是否充分利用了索引，针对联合索引值越大越好。

如何计算：

1. 先看索引上字段的类型+长度。比如：int=4 ; varchar(20) =20 ; char(20) =20

2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf8要乘 3，如果是utf8mb4要乘4，GBK要乘2

3. varchar这种动态字符串要加2个字节

4. 允许为空的字段要加1个字节

-- 创建索引
CREATE INDEX idx_age_name ON t_emp(age, `name`);
-- 测试1
EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` = 'ab%';
-- 测试2
EXPLAIN SELECT * FROM t_emp WHERE age = 30;

2.3.8、ref

 显示与key中的索引进行比较的列或常量

-- ref=atguigudb.t1.id   关联查询时出现，t2表和t1表的哪一列进行关联
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

-- ref=const  与索引列进行等值比较的东西是啥，const表示一个常数
EXPLAIN SELECT * FROM t_emp WHERE age = 30;

2.3.9、rows ☆

MySQL认为它执行查询时必须检查的行数。值越小越好。

-- 如果是全表扫描，rows的值就是表中数据的估计行数
EXPLAIN SELECT * FROM t_emp WHERE empno = '10001';

-- 如果是使用索引查询，rows的值就是预计扫描索引记录行数
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

2.3.10、filtered

 最后查询出来的数据占所有服务器端检查行数（rows）的百分比。值越大越好。

-- 先根据二级索引deptId找到数据的主键，有3条记录满足条件，
-- 再根据主键进行回表，最终找到3条记录，有100%的记录满足条件
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

-- 这个例子如果name列是索引列则 filtered = 100 否则filtered = 10(全表扫描)
EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';

2.3.11、Extra ☆

 包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个，这里只挑介绍比较重要的介绍。

• Impossible WHERE：where子句的值总是false

EXPLAIN SELECT * FROM t_emp WHERE 1 != 1;

•  Using where：使用了where，但在where上有字段没有创建索引

EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';

•  Using temporary：使了用临时表保存中间结果

EXPLAIN SELECT DISTINCT content FROM t1;

•  Using filesort：

在对查询结果中的记录进行排序时，是可以使用索引的，如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY id;

如果排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort），如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY content;

•  Using index：使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

 EXPLAIN SELECT id, content1 FROM t4;

 EXPLAIN SELECT id FROM t1;

•  Using index condition：叫作 Index Condition Pushdown Optimization （索引下推优化）

• 如果没有索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。主键值进行回表，返回完整的记录给server层，server层再判断其他的搜索条件是否成立。如果成立则保留该记录，否则跳过该记录，然后向存储引擎层要下一条记录。

• 如果使用了索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。不着急执行回表，而是在这条记录上先判断一下所有关于idx_content1索引中包含的条件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立。如果这些条件不成立，则直接跳过该二级索引记录，去找下一条二级索引记录；如果这些条件成立，则执行回表操作，返回完整的记录给server层。

-- content1列上有索引idx_content1
EXPLAIN SELECT * FROM t4 WHERE content1 > 'z' AND content1 LIKE '%a';

3、准备数据

在做优化之前，要准备大量数据。接下来创建两张表，并往员工表里插入50W数据，部门表中插入1W条数据。

怎么快速插入50w条数据呢？ 存储过程

怎么保证插入的数据不重复？函数

部门表：

• id：自增长

• deptName：随机字符串，允许重复

• address：随机字符串，允许重复

• CEO：1-50w之间的任意数字

员工表：

• id：自增长

• empno：可以使用随机数字，或者从1开始的自增数字，不允许重复

• name：随机生成，允许姓名重复

• age：区间随机数

• deptId：1-1w之间随机数

总结：需要产生随机字符串和区间随机数的函数。

3.1、创建表

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

.2、创建函数

-- 查看mysql是否允许创建函数：
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-- 命令开启：允许创建函数设置：（global-所有session都生效）
SET GLOBAL log_bin_trust_function_creators=1; 

-- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO  
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $$

-- 假如要删除
-- drop function rand_string;

-- 用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;  
END$$

-- 假如要删除
-- drop function rand_num;

3.3、创建存储过程

-- 插入员工数据   没有返回值
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit设置成0  
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_emp;

-- 插入部门数据
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_dept;

3.4、调用存储过程

-- 执行存储过程，往dept表添加1万条数据
CALL insert_dept(10000); 

-- 执行存储过程，往emp表添加50万条数据，编号从100000开始
CALL insert_emp(100000,500000); 

3.5、批量删除表索引

DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
#若没有数据返回,程序继续,并将变量done设为2
        OPEN _cur;
        FETCH _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
END$$

-- 执行批量删除：dbname 库名称, tablename 表名称
CALL proc_drop_index("dbname","tablename");

3.6、开启SQL执行时间的显示

为了方便后面的测试中随时查看SQL运行的时间，测试索引优化后的效果，我们开启profiling

-- 显示sql语句执行时间
SET profiling = 1;
SHOW VARIABLES  LIKE '%profiling%';
SHOW PROFILES;

4、单表索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

我们创建索引后，用不用索引，最终是优化器说了算。优化器会基于开销选择索引，怎么开销小就怎么来。不是基于规则，也不是基于语义。

另外SQL语句是否使用索引，和数据库的版本、数据量、数据选择度（查询中选择的列数）都有关系。

-- 创建索引
CREATE INDEX idx_name ON emp(`name`);

4.1、计算、函数导致索引失效

-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

 

4.2、LIKE以%开头索引失效

6、子查询优化

6.1、查询非掌门人

-- 不推荐
SELECT * FROM t_emp emp WHERE emp.id NOT IN 
(SELECT dept.ceo FROM t_dept dept WHERE dept.ceo IS NOT NULL);
-- 推荐
SELECT emp.* FROM t_emp emp 
LEFT JOIN t_dept dept ON emp.id = dept.ceo WHERE dept.id IS NULL;

6.2、总结

子查询的执行效率不高。原因：

① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表

中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会

受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询

要快 ，如果查询中使用索引的话，性能就会更好。

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

7、排序优化

7.1、索引失效的情况

以下三种情况不走索引：

1. 无过滤，不索引

2. 顺序错，不索引

3. 方向反，不索引

准备：

-- 删除现有索引
CALL proc_drop_index("atguigudb","emp");
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

无过滤，不索引：

-- 使用了索引：
-- 注意：key_len = 5是where语句使用age索引的标记，order by语句使用索引不在key_len中体现。
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;

-- 使用了索引：
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`; 

-- 没有使用索引：因为索引列中不存在empno
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;

-- 没有使用索引：order by 后的排序条件的顺序，与索引顺序不一致
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;

-- 没有使用索引：出现的顺序要和复合索引中的列的顺序一致！
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

方向反，不索引：

-- 使用了索引：排序条件和索引一致，并方向相同，可以使用索引
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;

-- 没有使用索引：两个排序条件方向相反
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;

7.2、索引优化案例

排序优化的目的是，去掉 Extra 中的 using filesort（手工排序）

准备：

-- 删除现有索引
CALL proc_drop_index("atguigudb","emp");
-- 这个例子结合 show profiles; 查看运行时间
SET profiling = 1;

需求：查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序

测试1：很显然，type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

EXPLAIN SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY `name`;
-- 然后查看一下SQL的执行时间

 优化思路： 尽量让where的过滤条件和排序使用上索引

step1： 我们建一个三个字段的组合索引可否？

CREATE INDEX idx_age_empno_name ON emp (age,empno,`name`);

最后的name索引没有用到，出现了Using filesort。原因是，因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。所以我们建一个3值索引是没有意义的

step2：那么我们先删掉这个索引：

DROP INDEX idx_age_empno_name ON emp; 

为了去掉filesort我们可以把索引建成，也就是说empno 和name这个两个字段我只能二选其一。

CREATE INDEX idx_age_name ON emp(age,`name`);

这样我们优化掉了 using filesort。但是经过测试，性能反而下降

 step3：如果我们选择那个范围过滤，而放弃排序上的索引呢?

DROP INDEX idx_age_name ON emp;
CREATE INDEX idx_age_empno ON emp(age,empno);

7.3、双路排序和单路排序

如果排序没有使用索引，引起了filesort（手工排序），那么filesort有两种算法

• 双路排序

• 单路排序

双路排序（慢）

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。

• 首先，根据行指针从磁盘取排序字段，在buffer进行排序。

• 再按照排序字段的顺序从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描。众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快）

• 从磁盘读取查询需要的所有字段，按照order by列在buffer对它们进行排序。

• 然后扫描排序后的列表进行输出。

它的效率更快一些，因为只读取一次磁盘，避免了第二次读取数据。并且把随机IO变成了顺序IO。但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

• 由于单路是后出的，总体而言好过双路

• 但是在sort_buffer中，单路比多路要多占用更多空间

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

  • 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

• 减少select 后面的查询的字段：Order by时select * 是一个大忌。查询字段过多会占用sort_buffer的容量。

• 增大sort_buffer_size参数的设置：不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL8.0，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%'; --默认1MB 

增大max_length_for_sort_data参数的设置：MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是4096字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用双路排序算法，否则使用单路排序算法。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整。  

SHOW VARIABLES LIKE '%max_length_for_sort_data%'; --默认4K

8、分组优化

• group by 使用索引的原则几乎跟order by一致。但是group by 即使没有过滤条件用到索引，也可以直接使用索引（Order By 必须有过滤条件才能使用上索引）

• group by 先排序再分组，遵照索引建的最佳左前缀法则。

• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

• where效率高于having，能写在where限定的条件就不要写在having中了

• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

9、覆盖索引优化

select 与 from 之间的字段 都是索引！

标志：using index