MySQL中慢SQL的监控与优化技巧

-- 查看当前设置
show variables like 'slow_query%';
show variables like 'long_query_time';

-- 动态开启（重启失效）
set global slow_query_log = 'on';
set global long_query_time = 1; -- 1秒阈值
set global slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1 -- 记录无索引查询
log_output = file

-- 开启性能监控
update setup_consumers set enabled = 'yes' 
where name like '%events_statements%';

-- 查看慢sql统计
select * from events_statements_summary_by_digest 
where sum_timer_wait > 1000000000000; -- 查询超过1秒的sql

# time: 2023-10-05t08:12:34.123456z
# user@host: root[root] @ localhost []  id:    15
# query_time: 5.123456  lock_time: 0.001000 rows_sent: 10  rows_examined: 100000
set timestamp=1696493554;
select * from orders where status='pending' and amount > 100;

explain select * from users where age > 30 and country = 'cn';

# 安装percona toolkit
sudo apt install percona-toolkit

# 分析慢日志
pt-query-digest /var/log/mysql/slow.log

# 输出报告示例
# 220ms avg, 95% 350ms, 最大耗时2.5s
# 执行次数:120次/天
# 建议:添加索引(status,amount)

-- 添加组合索引
create index idx_status_amount on orders(status, amount);

-- 删除冗余索引
drop index idx_status on orders;

-- 优化前（函数导致索引失效）
select * from orders where year(create_time) = 2023;

-- 优化后
select * from orders 
where create_time between '2023-01-01' and '2023-12-31';

-- 优化前
select * from products;

-- 优化后
select id, name, price from products;

-- 传统分页（慢）
select * from orders limit 1000000, 20;

-- 游标分页（快）
select * from orders where id > 1000000 limit 20;

// java示例:批量插入
preparedstatement ps = conn.preparestatement("insert into logs values (?)");
for (string log : logs) {
    ps.setstring(1, log);
    ps.addbatch(); // 添加到批处理
}
ps.executebatch(); // 批量执行

-- 使用exists代替join
select * from users u
where exists (
    select 1 from orders o 
    where o.user_id = u.id and o.amount > 1000
);

-- 小事务提交
start transaction;
insert into ...; -- 少量操作
commit;

-- 创建覆盖索引
create index idx_cover on orders(user_id, status);

-- 查询只需索引
select user_id, status from orders where user_id=100;

-- 按时间分区
create table logs (
    id int,
    log_time datetime
) partition by range (year(log_time)) (
    partition p2020 values less than (2021),
    partition p2021 values less than (2022)
);

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-server:9104']
    metrics_path: /metrics