[mysql]查询截取分析-查询优化

分析SQL慢的方式

观察，至少跑1天，看看生产的慢SQL情况。
开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
explain + 慢SQL分析。
show Profile。
运维经理 OR DBA，进行MySQL数据库服务器的参数调优。

总结：
1 慢查询的捕获
2 explain+慢SQL分析
3 show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
4 SQL数据库服务器的参数调优

查询优化

1小表驱动大表

//选择这种
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++){
        
    }
}

优化原则：小表驱动大表，即小的数据集驱动大的数据集

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表的数据集是，用in优于exists


SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);
等价于：
for select * from A
for select * from B where B.id = A.id
当A表的数据集系小于B表数据集时，用exists优于in

注意：A表于B表的ID字段应建立索引

EXISTS
1. 语法：`SELECT....FROM tab WHERE EXISTS(subquery);
2. 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。
提示
1. EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
2. EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题
3. EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

2ORDER BY优化

ORDER BY后的排序条件，与索引（顺序）一致不会产生filesort

create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

select * from tblA;

案例分析

#1，不会产生filesort
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;
#2，不会产生filesort
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;
#3，产生了filesort
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;
#4，产生了filesort
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;
#5，产生了filesort
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;
#6，产生了
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;
#7，没有产生
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;
#8，产生了，order by默认升序，顺序不一致导致产生了（如果都是ASC/DESC就不会产生）
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序：
- ORDER BY语句使用索引最左前列。
- 使用WHERE子句于ORDER BY 子句条件列组合满足索引最左前列

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

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

双路排序：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。
单路算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题：
- 由于单路时后出的，总体而言好过双路。
- 使用单路的问题：
  - 在sort_ buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_ buffer的容量，导致每次只能取sort_ buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取取sort buffer容量大小，再排....从未而多次I/O:
  - 本来想省一次/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data的参数设置
Why：提高Order By速度
1. ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
- 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
- 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
1. 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
2. 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

小总结

3 GROUP BY优化

GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

posted @ 2020-09-18 16:27 HankinkK 阅读(175) 评论(0) 收藏举报

刷新页面返回顶部

HankinkK