目录

Mysql 踩坑及优化笔记

Q: server has gone away

排查方向:

1. 查看 server 端的 wait_timeout

mysql> show global variables like '%timeout%';

2. client 端使用连接池,并把连接池的超时检测值设置为小于 wait_timeout 的值。

Q: 使用了 order by limit 语句导致查询慢

排查方向:

1. 查看执行计划

explain select ...

2. 检查 session 数,是不是有太多并发连接

mysql> show processlist;

3. 变更 sql 语句

SELECT * FROM LARGE ORDER BY id LIMIT 10000, 30 
 
# 变更为 (id加到条件里)
SELECT * FROM LARGE WHERE id > 10000 ORDER BY id LIMIT 30 
# 或
SELECT * FROM LARGE WHERE id >=(SELECT id FROM LARGE ORDER BY id LIMIT 10000, 1) ORDER BY id LIMIT 30