站长必避的3个MySQL设计陷阱:半年崩一次
|
去年1月17号凌晨三点十四分,我蹲在IDC机房门口啃冷包子,手边那杯速溶咖啡结了一层膜,底下是半坨褐色浮渣。防火墙规则里刚删掉一条匹配`/wp-content/plugins/.\\.php\\?p=`的放行条目——不是WAF策略,是云盾控制台里手动删的。删完三分钟,MySQL进程又挂了。不是OOM,不是慢查询堆积,是mysqld直接不响应,`ps aux | grep mysql`还活着,但连`mysql -uroot -e "SELECT 1"`都卡住。后台异常登录时间在02:47:13和02:48:05有两条,来源IP跟上周扫内网的蜜罐流量对得上。门禁响了一声,运维老张推门进来,没看我,先摸了下服务器机柜侧面——烫手。 这不是崩溃,是窒息。你盯着top看,CPU压根不高,IO Wait也平得像死水,`show processlist`里躺着二十多个Sleep状态的连接,`Time`列全是600、1200、1800……没人断开,没人超时,没人释放锁。`information_schema.INNODB_TRX`里六个事务堵在一行UPDATE上,`trx_state=LOCK WAIT`,`trx_wait_started`显示是昨天下午四点十七分。没人动过表结构,没人发过DDL,就是有个会员系统,用着tp5.1,中间件里配了`'persistent' => true`——对,就那个PHP原生MySQLi的持久连接,开着长连接池,连着连着,就把innodb的锁等待队列喂成了水泥管。
文章配图,仅供参考 第二个坑,藏在索引名里。`ALTER TABLE user_log ADD INDEX idx_user_action_time (user_id, action, created_at)`,名字起得挺规整,可你查`show create table user_log`就会发现:这张表有三套索引,分别建在`(user_id, action)`、`(user_id, created_at)`、`(user_id, action, created_at)`上。前两个是三年前开发留的,第三个是去年加的。innodb不合并,只叠加。写入量一上来,insert一行触发三棵B+树分裂,buffer pool碎片率冲到89%,flush线程开始抽风。你去看访问日志里的一段——`POST /api/v2/user/action HTTP/1.1`后面跟着个200,但实际返回延迟是4.7秒。不是PHP慢,是InnoDB在后台吭哧吭哧把dirty page刷到磁盘上,刷到一半,突然被另一个大事务的checkpoint拦腰截断。同事把椅子转过来又转回去,问“要不要调`innodb_io_capacity`”,我说算了,这表结构比`/etc/passwd`还难改。第三个最阴——字段类型硬扛业务膨胀。会员系统,`user_info`表里有个`remark`字段,一开始设的`TEXT`,后来运营说要存富文本,改成`MEDIUMTEXT`,再后来说要存JSON配置,开发顺手加上`json_valid(remark)`检查。问题不在JSON,而在`ALTER TABLE user_info MODIFY remark MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`这条语句执行后,MySQL偷偷把`ROW_FORMAT`从Dynamic切成了Compact。结果半年过去,表体积涨到13GB,`OPTIMIZE TABLE`跑一次要停服四小时,`SHOW TABLE STATUS`里`Data_free`长期在2.1~2.3GB跳变。更糟的是,某个凌晨批量发券脚本触发了全表扫描——它没走索引,因为条件是`WHERE remark LIKE '%优惠%'`,而`utf8mb4_unicode_ci`下这个LIKE没法用前缀索引。你查漏洞扫描报告,第三页写着:“建议启用全文索引”,但你翻遍整个DBA交接文档,没找到当年谁在`remark`字段上建过FULLTEXT。显示器上贴了张便利贴,字是蓝墨水写的:“备份前先`set global innodb_file_per_table=ON`”,但下面被油渍盖住了后半句,只看得清“ON”。 空调出风口挂了个塑料袋,风一吹就晃。我们那天最后没切主从,也没启容灾库,就硬拖着。把`wait_timeout`从28800改成300,`interactive_timeout`同理,再写了个crontab每五分钟`kill`掉所有Sleep超300秒的线程。然后把持久连接关了,让PHP自己重连。索引嘛,留着,但把那套冗余索引对应的查询全打上注释`/ NO INDEX: use idx_user_action_time only /`,塞进DAO层里当注释。`remark`字段的事,暂时不动——你见过哪个客户肯为删个索引停业两小时?谁在电话里说了句“嗯”就没声了,十秒后听筒里只剩电流音。 我现在每次部署新实例,第一件事不是建库,是跑个检查SQL:`SELECT TABLE_NAME, ROW_FORMAT, CREATE_OPTIONS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'xxx' AND (ROW_FORMAT = 'Compact' OR CREATE_OPTIONS LIKE '%partition%')`;第二件事是盯`SHOW VARIABLES LIKE 'max_connections'`——不是看值,是看它有没有被动态改过,因为有人在my.cnf里写`max_connections = 1000`,却在初始化脚本里又执行`SET GLOBAL max_connections = 200`,导致`show processlist`永远只显示200条。至于那些Sleep连接……我现在会扫一遍`INFORMATION_SCHEMA.PROCESSLIST`,抓出所有User不是'repl'、不是'backup'、State是'Sleep'且Time > 300的,发钉钉告警,标题就写:“你家应用连的还是2016年的MySQL心跳逻辑?” 证书到期提醒邮件上周又来了,subject是“[ALERT] your SSL cert for admin.xx.gov.cn expires in 4 days”,我没点开。这事我不管,但我知道,只要`user_info.remark`字段还挂着Compact格式,只要那三条索引还在,只要`mysqli::real_connect()`还在配置里开着`MYSQLI_CLIENT_COMPRESS`——我们就永远在等下一个1月17号。现在桌上那杯咖啡早凉透了,膜裂开几道缝,底下浮渣沉底成一小团暗色,像没清理干净的undo log。 (编辑:站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |


鸿蒙开发者必修:MySQL运维设计实战
站长别再单打独斗:容器化运维重塑媒体融合新逻辑
开源站长11年实战:一中心统管破局产品、数据与权限乱局
站长圈从不公开的科技真相:8年编辑拆解
无代码站长亲授:用交互式网站破局iOS同质化
传媒站长破局:3个被忽略的流量技术杠杆
站长圈淘汰只会单点优化的加载师
