记一次数据问题,使用 profiling 分析sql性能

2022-01-13 本文已影响0人 Sven_qi

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 SELECT statement, 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.表数据量太大，计划分表