1、sql优化思路

①、慢查询的开启与捕获
②、explain+慢SQL分析
③、show profile 查询SQL在Mysql服务器里面执行细节和生命周期情况
④、SQL数据库服务器的参数调优

2、优化查询

2.1、永远小表驱动大表

类似嵌套循环Nested Loop，

1
2
3
4
5
6
for(int i=0;i<5;i++){
  ...
  for(int j=0;j<1000;j++){
    ...
  }
}

对于Java而言，也许1000和5次的循环顺序无所谓，因为结果一致，而对于MySQL数据库，如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。
这里可以关联到IN和EXISTA效率问题（注意A表和B表的都有建立主键或者唯一索引约束）：
①、当B表的数据集必须小于A表的数据集时，使用in优于exists

1
2
3
4
select * from A  where id in (select id from B)
#等价于：
for select id from B
for select * from A where A.id = B.id

②、当B表的数据集大于A表的数据集时，使用exists优于in

1
2
3
4
select * from A where exists (select 1 from B where B.id = A.id)
#等价于
for select id from A
for select * from B where B.id = A.id

注：exists

1
select ... from table where exists(subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或者false）来决定主查询的数据结果是否得以保留。
提示：
①、exits（subquery）只返回TRUE或者FALSE，因此子查询中的select * 也可以是select 1 或者其他，官方说法是其实执行时忽略select清单，因此没有区别；
②、exists子查询的实际执行过程可能经过了优化而不是我们理解哈桑的逐条对比，如果担心效率问题，可以进行实际检验以确定是否有效率问题；
③、exits子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优化需要具体分析。

2.2、order by优化

MySQL支持两种方式排序，filesort（文件排序）和index（索引排序）排序，index效率高，它指的是MySQL扫描索引本身完成排序，filesort方式效率低。所以order by排序应避免使用filesort排序。

2.2.1、filesort排序

如果不在索引列上，filesort有两种算法，MySQL就要启动双路排序和单路排序。
双路排序：
MySQL4.1之前是使用双路排序，字面意思就是两次扫描，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列的值重新从列表中读取对应的数据输出，从磁盘取出排序字段，在buffer进行排序，再从磁盘取其他字段。
单路排序： 取一次数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在MySQL4.1之后，出现了第二种改进的算法，就是单路排序，从磁盘读取查询需要的数据列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它一次把每一行都保存在内存中了。

2.2.2、案例测试

我们举个case来测试下order by的排序，sql语句：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE tbA(
 id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
 age INT ,
 birth TIMESTAMP NOT NULL
);

INSERT INTO tbA(age,birth) VALUES(22,NOW());
INSERT INTO tbA(age,birth) VALUES(23,NOW());
INSERT INTO tbA(age,birth) VALUES(24,NOW());

CREATE INDEX idx_A_ageBirth ON tbA(age,birth);

CASE1:

1
2
3
4
explain select * from tbA where age>20 order by age;
explain select * from tbA where age>20 order by age,birth;
explain select * from tbA where age>20 order by birth;
explain select * from tbA where age>20 order by birth,age;

图片
测试结果为第1、2条sql语句使用Using index排序，第3、4条sql语句不仅出现了Using index，还出先了Using filesort排序。
CASE2:

1
2
3
4
explain select * from tbA order by birth;
explain select * from tbA where birth>'2019-07-03 10:10:00' order by birth;
explain select * from tbA where birth>'2019-07-03 10:10:00' order by age;
explain select * from tbA where birth>'2019-07-03 10:10:00' order by age asc ,birth desc;

图片
测试结果为第3条sql语句使用Using index排序，第1、2、4条sql语句不仅出现了Using index，还出先了Using filesort排序。

2.2.3、结论和引申出的问题

①、order by字段尽可能的在索引列上排序，遵守索引建的最佳左前缀原则，以下分别是order by字段分别是使用索引排序还是文件排序：

KEY a_b_c(a,b_c)
order by能使用索引最左前缀
-ORDER BY a
-ORDER BY a,b
-ORDER BY a,b,c
-ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
-WHERE a=const ORDER BY b,c
-WHERE a=const AND b=const ORDER BY c
-WHERE a=const AND b>const ORDER BY b,c

不能使用索引进行排序
-ORDER BY a AES,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 /对于排序来说，多个相等条件也是范围查询/

②、如果对order by做了优化后，还不好使。这可能是由于单路照成的，在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排序再取sort_buffer容量大小，再排……从而多次I/O。
解决办法：
增大sort_buffer_size参数的设置，增大max_length_for_sort_data参数设置

1.在使用order by时，不要用select *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作；
2.尝试提高sort_buffer_size，无论是文件排序还是索引排序，增大此参数都会提高效率；
3.尝试提高max_length_for_sort_data，提高该参数可以增大使用单路排序的概率。

2.3、group by排序

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了

最后更新： 2019年07月03日 09:51

原始链接： https://www.sunnymaple.cn/2019/07/03/sql优化分析/

赏
× 请我吃糖~
打赏二维码