一、问题定位

1、慢查询日志

-- 查看当前设置
SHOW VARIABLES LIKE 'slow_query%';

-- 开启慢查询日志（my.cnf永久配置）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  -- 超过1秒的查询
log_queries_not_using_indexes = 1 -- 记录未使用索引的查询

-- 动态设置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

2、分析工具

工具	使用场景	命令示例
mysqldumpslow	官方自带，基础分析	`mysqldumpslow -s t -t 10 -g 'select' /path/to/slow.log`
pt-query-digest	高级分析，生成详细报告	`pt-query-digest slow.log > report.txt`
Percona Toolkit	专业级分析，支持多维度统计	`pt-query-digest --filter '$event->{arg} =~ m/SELECT/i' slow.log`

二、优化方案

对于慢查询SQL的优化方式中，首先需要定位慢查询SQL到底是为什么那么慢。EXPLAIN命令可以查看慢查询SQL的执行计划。

1、索引优化

针对慢查询SQL，一大部分SQL慢查询的原因是没有命中索引或者索引设计不合理或者SQL语句不合理导致全盘扫描或者索引失效。

对于索引使用尽量遵守以下原则以提高索引使用效率。

查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如： smallint ， tinyint ；
对于很长的动态字符串，考虑使用前缀索引；
对于组合索引，考虑最左侧匹配原则、覆盖索引；
尽量选择区分度高的列作为索引；该列的值相同的越少越好；
尽量扩展索引，在现有索引的基础上，添加复合索引；最多 6 个索引；
不要 select *；尽量只列出需要的列字段；方便使用覆盖索引；
索引列，列尽量设置为非空；

对于索引的使用尽量满足上述各项原则。除上述规则外还要注意一些情况是否造成索引失效导致全盘扫描。

索引失效的情况：

select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
索引字段参与运算，则索引失效；例如： from_unixtime(idx) = '2021-04-30'; 改成 idx = unix_timestamp("2021-04-30")；
索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作用了隐式转换函数；
LIKE 模糊查询，通配符 % 开头，则索引失效；
组合索引中，没使用第一列索引，索引失效；
在索引字段上使用NOT <> != 索引失效；例如：id <> 0可以修改为 id > 0 or id < 0；