v2-19f3b7ee997e1a4a7f27ec0a98d3f62f_r.jpeg
一、事故现场
有一张用户对 appid 的对应关系表，数据量 578261166 条，38GB 大小，data 17.3G , index 21 G
CREATE TABLE `user_subtasks_v5` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`uid_cardid` int(11) NOT NULL DEFAULT '0' COMMENT '通用uid',
`appid` varchar(50) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `u_cardid_appid` (`uid_cardid`,`appid`),
KEY `i_uid` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=578261111 DEFAULT CHARSET=utf8 COMMENT='用户任务表V5';
事故sql
-- 原sql
select appid from user_subtasks_v5 where uid_cardid=62719757;
-- 新sql
select * from user_subtasks_v5 where uid_cardid=62719757;
造成结果： 上线后 IOPS(mysql 读写次数) 飙升，读写吞吐量飙升，出现大量慢查询
F12C85C1-1F2B-4585-98E5-FF82C860A6EB.png
B4913BF4-6535-4F41-91DB-032854407D53.png
二、性能分析
1.explain 分析
- 对上个两个两个sql 进行 explain 分析
explain select appid from user_subtasks_v5 where uid_cardid=62719757;
explain select * from user_subtasks_v5 where uid_cardid=62719757;
- 上面2条语句 explain 的结构大部分是一样的，使用的索引策略也是一样的，差异是 appid 使用了索引覆盖，性能更好一些,不用回表查询
2. show processlist 分析
使用 show processlist 查看当前执行的sql语言有没有异常，查到的结果是：频繁的出现 “select * from user_subtasks_v5 where uid_cardid=62719757”，语句处于 “sending data”，状态。sending_data: The thread is reading and processing rows for a
SELECTstatement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
sending_data 的过程包括从磁盘上 “读取 + 发送” 的过程
https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html]
3. show profile 分析sql
-- 打开 profile
set profiling=on;
-- 执行sql
select * from user_subtasks_v5 where uid_cardid=62719757;
-- 执行 show profiles 查看执行id
show profiles；
-- 使用 show profile all for query 查看sql详细执行情况
show profile all for query 20;
结果如下：
D2426EE3-3452-4C5D-99E9-BF2D59A543AE.png
4C1EEBFE-CC3C-4389-9E82-1D2A33CC2DE7.png
参数解释：
QQ20220113-180048.png
可以观察到 在 Duration， Context_voluntary， Block_ops_in， Page_faults_minor 项差异较大。
问题猜测
- 1.覆盖索引问题，select appid 走覆盖索引，效率更高，不需要回表
- 2.数据量问题，select * 数据量更大，读取的页更多
- 3.有说是 参数 time_zone=system 时，查询 timestamp 字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会导致线程上下文频繁切换，CPU 使用率暴涨，系统响应变慢设置假死。 亲测： 没有什么差异。
优化方案：
- 1.缓存优化，先减少对 selelct * 的使用，解决线上问题
- 2.表数据量太大，计划分表
