MySQL -- 短连接 + 慢查询
短连接
- 短连接模式:连接到数据库后,执行很少的SQL后就断开,下次需要的时候再重连
- 在业务高峰期,会出现连接数突然暴涨的情况
- MySQL建立连接的成本非常昂贵
- 成本:TCP/IP三次握手 + 登录权限判断 + 获取连接的数据读写权限
max_connections
max_connections
:MySQL实例同时存在的连接数上限- 当连接数超过
max_connections
,系统会拒绝接下来的连接请求,返回:Too many connections
- 当连接被拒绝,从业务角度来看是数据库不可用
- 如果机器负载较高,处理现有请求的时间会变长,每个连接保持的时间也会变长
- 如果再有新建连接的话,很容易触发
max_connections
的限制
- 如果再有新建连接的话,很容易触发
max_connections
的目的是保护MySQL的- 如果把
max_connections
设置得过大,更多的连接就会进来,导致系统负载会进一步加大 - 大量的资源会耗费在权限验证等逻辑上,而已经拿到连接的线程会抢不到CPU资源去执行业务SQL
- 如果把
1 | mysql> SHOW VARIABLES LIKE '%max_connections%'; |
清理Sleep状态的连接
KILL CONNECTION
:主动踢除不需要保持的连接(与wait_timeout
的效果一样)
时刻 | sission A | session B | session C |
---|---|---|---|
T | BEGIN; INSERT INTO t VALUES (1,1); |
SELECT * FROM t WHERE id=1; | |
T+30s | SHOW PROCESSLIST; KILL CONNECTION |
- 踢除
Sleep
状态的连接是有损的 - 如果断开sission A的连接,会回滚事务
- 如果断开sission B的连接,没有任何影响
- 优先断开事务外空闲的连接
- 再考虑断开事务内空闲的连接
事务外空闲
trx_mysql_thread_id
:id=4
的线程还处在事务中
KILL CONNECTION
- 服务端执行
KILL CONNECTION id
,如果连接在此前处于Sleep
状态,客户端是不会立马知道 - 客户端如果发起下一个请求,报错
ERROR 2006 (HY000): MySQL server has gone away
- 因此,客户端(应用层)需要有重连机制
减少连接过程的消耗
- 数据库跳过权限验证阶段 – 风险极高
- 重启数据库,启动参数
--skip-grant-tables
- 跳过所有的权限验证阶段(连接过程+语句执行过程)
- 重启数据库,启动参数
- 从MySQL 8.0开始,启用
--skip-grant-tables
参数,默认会启用--skip-networking
(本地客户端)
慢查询
索引没有设计好
古老方案
Online DDL
–ALTER TABLE
- 主库A,备库B
- 在备库B上执行
SET sql_log_bin=OFF
(不写binlog),ALTER TABLE
加上索引 - 执行主备切换,变成主库B,备库A
- 在备库A上执行
SET sql_log_bin=OFF
(不写binlog),ALTER TABLE
加上索引
工具
gh-ost
语句没写好
1 | -- Since MySQL 5.7 |
MySQL选错索引
FORCE INDEX
query_rewrite
+FORCE INDEX
预先发现问题
- 测试环境配置:
slow_query_log=ON
+long_query_time=0
- SQL Review,留意
Rows_examined
是否与预期的一致 - 工具:
pt-query-digest
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.