慢查询性能优化实践原创
一、背景
项目组接到两家客户反馈，部分生产统计报表查询缓慢，甚至某些报表无法查询出1个月的统计数据。收到问题反馈后，运维同学远程至客户服务，验证生产问题现状，以PM工时卡片（公司研发内控平台）形式转至研发生产问题甬道。
传统站务系统部署于客户本地机房，使用Mysql-5.7.37数据库；这两家客户均已上线多年，软件版本较老；以他们订单表单表为例，均拥有百万量级数据。单站站务的报表具有两个特性，一是以业务驱动，二是满足财务报表要求（交付类项目通病）；这就使得报表的级联关系较多，sql语句比较复杂，以下是针对生产慢查询的优化思路。
1.1 慢查询优化思路
- 一、分析sql，优化sql（检查sql语句本身/聚合/拆分sql）；
- 二、创建合适的索引；
- 三、检查mysql服务器配置；
- 四、增加缓存、使用读库（读写分离）；
- 五、若有代码，优化代码逻辑；
- 六、提升服务器IO性能，如提高硬件配置，机械盘换成SSD固态盘；
- 七、清洗表、中间件（如ES）；
- 八、分库分表（通常分库分表是最后的手段）；
以上是站务精英总结的慢查询优化思路，可能还有其他办法，顺序也不一定正确，需要根据实际的情况灵活处理。比如有钱，升级机器的成本代价可能小于分库分表带来的研发成本代价。
二、慢sql举例
select
s.*
from
tbl_zw_sell_ticket s
left join tbl_sys_user u on u.code = s.ticket_seller_id
left join tbl_sys_role r on u.role_code = r.code
where
s.leave_date between DATE_FORMAT('2022-12-01 00:00:00', '%Y-%m-%d') and DATE_FORMAT('2022-12-30 23:59:59', '%Y-%m-%d')
and s.ticket_state not in ('锁票', '解锁')
order by
s.sell_time desc
limit 50
2.1 检查sql（not in）
观察sql，最先发现 ‘s.ticket_state not in (‘锁票’, ‘解锁’)’。老生常谈的问题，not in 到底走不走索引？
MySQL 会在选择索引的时候进行优化，如果 MySQL 认为全表扫描比走索引+回表效率高， 那么他会选择全表扫描。注意，我们的sql里面是 select s.* ，按理会出现大量的回表; 但是在sql的末尾 limit 50 , 因为 limit 的增加，让 MySQL 优化的时候发现，索引 + 回表的性能更高一些。
如果不放心的同学，可以把 not in 换成 not exists 提升执行效率（取决于not exists里面的内容是否多于主表）。
三、Explain
3.1 explain
老办法，首先想到explain，查看sql的执行计划。在使用explain之前，执行上述sql查询一个月的数据，竟然耗时2分钟左右，赶紧使用explain查看执行计划。
百万量级的 tbl_zw_sell_ticket 表 s 命中 cr_date 索引（sell_time）字段，执行计划显示扫描1167行。
‘order by s.sell_time desc’ 语句进入视线，s 表命中这个排序的索引，并不能减少查询量级，这段sql中 s.leave_date 对订单时间过滤是减少查询量级的关键，没有命中 leave_date 的索引将造成不可避免的慢查询。
<font color="red"> 对生产执行第一个优化 </font>
++对生产执行第一个优化++：
drop index idx_leave_date on tbl_zw_sell_ticket;
create index idx_leave_date on tbl_zw_sell_ticket (leave_date, sell_time);
force index(idx_leave_date);
更换原来的 leave_date 索引，改为联合索引 leave_date,sell_time 。（注意：原来生产单独的 sell_time 索引不要动，涉及到联合索引命中顺序的问题，可能引起其他报表卡顿）
对这个报表单独使用的sql，增加强制索引语句，force index(idx_leave_date) ，这轮优化做完后，sql查询一个月数据，已达到秒级/毫秒级的执行速度。
3.2 order by的警惕
- 一、尽量使用index排序，避免filesort排序；
- 二、检查mysql的配置文件 sort_buffer_size 大小；
四、mysql服务配置
4.1 my.cnf
join_buffer_size = 2M
sort_buffer_size = 2M
innodb_buffer_pool_size=2G
生产配置，sort_buffer_size 为 2M，join_buffer_size 为 2M，innodb_buffer_pool_size=2G，而整体服务器的内存占用只达到了50%，如何判断 innodb_buffer_pool_size=2G 内存页是否合理 ？
4.1.1 内存页是否合理判断
通过 show status like ‘Innodb_buffer_pool_%’;可以看到跟buffer pool有关的一些信息。
Innodb_buffer_pool_read_requests表示读请求的次数。
Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数
pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。
4.1.2 max_connections等待sql配置
判断max_connections 等待sql排队执行的情况，在生产环境max_connections=5000，不用修改。
<font color="red"> 对生产执行第二个优化 </font>
++对生产执行第二个优化++：
join_buffer_size = 64M
sort_buffer_size = 64M
innodb_buffer_pool_size = 4G
4.2 sql监控-profiling
如果执行计划是正确的，SQL语句的性能还是很慢，可通过MySQL中的Profiling工具进一步定位问题。 通过开启 profiling 查看sql 执行流程，临时开启 set profiling=ON; 查看开启sql监控的情况show variables like ‘profiling’;
然后正常执行sql语句,如下：
select count(*) from checkticket_record where DEPARTURE_DATE >= ‘2023-02-01 00:00:00’ and DEPARTURE_DATE <= ‘2023-05-04 00:00:00’ and CURRENT_STATE = ‘ALREADY_CHECK’；
执行 show profiles; 查看mysql最新执行的所有sql语句，找到刚刚执行的sql的id，执行show profile for query id;
+——————————+———-+
| Status | Duration |
+——————————+———-+
| Sending data | 0.000197 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000225 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000235 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000187 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000208 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000212 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000222 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000179 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000195 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000202 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000206 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000188 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000196 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000185 |
| Waiting for query cache lock | 0.000016 |
五、Waiting for query cache lock
使用Profiling工具后，发现一段简单的sql执行，出现大量的 Waiting for query cache lock ！！！！
在my.cnf 的配置中，我发现有一段 query_cache_type = 1 的配置，该配置存储SELECT语句及其产生的数据结果，特别适用于表数据变化不是很频繁的场景。
生产上的两家客户，并未启用读库，统计报表依然从写库查询，而在写库的mysql配置里面存在 query_cache_type = 1 的配置。对于更新压力大的写库来说，查询缓存的命中率也会非常低。
<font color="red"> 对生产执行第三个优化（写库不要开启 query_cache） </font>
++对生产执行第三个优化（写库不要开启 query_cache）++：
query_cache_type = 0
# query_cache_size=6M
我们可以将参数 query_cache_type 设置成 DEMAND(按需及用)方式,这样对于默认的SQL语句不使用查询缓存，而对于确定要使用query cache的SQL语句，可以用sql_cache的方法指定，例如：
select sql_cache * from table_name; 或 select sql_cache count(*) from table_name;
以下是query_cache_type三个参数的含义:
query_cache_type=0（OFF）关闭
query_cache_type=1（ON）缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存
query_cache_type=2(DEMAND)，只缓存select语句中通过SQL_CACHE指定需要缓存的查询
六、总结
性能优化是一个庞大的课题，要善用工具，本文主要是针对sql自身、索引、mysql的配置对生产报表查询进行了一轮简单的优化，利用到 sql、索引基础知识，explain 执行命令、Profiling工具等，问题因环境而不同，不同的问题处理手段也不相同，但大致的排查问题思路可以保持一致。
