MySQL索引优化 - 记一次生产问题

@auther 张念磊
@date 2021-10-01

• • 一、现象
  • 二、定位慢查询
  • 三、解决问题
  • 四、总结与思考

有一个服务每三十秒跑批一次来统计数据，按理说数据应该是每30秒刷新一次，但用户反馈数据10多分钟才能改变一次。

登录到生产的服务器，数据库服务单个线程的CPU占用率为 400%左右，这肯定是不健康的。

且该线程为mysql，基本可以断定：出现了慢查询。

找来我们的运维工程师，打开数据库的慢查询

打开数据库慢查询三步：

-- 0. 进入mysql 
mysql -uroot -pxxxx
-- 1. 将slow_query_log全局变量设为on
set global slow_query_log='ON';
-- 2. 设置慢查询日志存放的路径
set global slow_log_file='/usr/local/mysql/data/show.log';
-- 3. 查询超过n秒就记录
set global long_query_time=3;

设置的是3s，出来一堆慢查询，运维老师随便截了几个丢到群里。

好吧，那开始解决问题吧。

大家分析一通，认为是业务上的问题，因为最长的慢查询只有8s，但用户那边要十多分钟才能出数据，然后我们负责这个模块的开发同事说这个跑批的任务是以项目为维度每个项目都会有一条SQL，会不会是项目太多，导致SQL太多，继而导致执行排队。

“把其他暂时没用到的项目先暂停了！”我们的技术经理说。

大家一致认同。然后开始干了，因为负责这个模块的开发同事没带电脑，我自告奋勇，我来写SQL：先备份数据，然后将暂时没用的项目置为暂停，一通操作，SQL出来了，发到群里让大家审一下，然后交给运维人员执行。

执行之后有一些效果了

我们的运维说这是正常的，我觉的还是有些不正常

并且他说没有慢查询了。

这时用户还有有些不满意，但他还是勉强接受了，毕竟这是已经半夜12点钟了。

第二天，找到运维工程师，拿到慢查询日志。发现里面有很多 180秒的慢查询，扫描行数为3百万行。

我们开始对这条SQL进行具体分析。

先使用explain看下这两种表分别使用的是哪个索引：

用到的是dup-idx2

然后打开表设计，查看索引，

KEY `dup-idx2` (`case_type`,`case_type_name`,`valid_flag`,`corporation_no`,`project_no`,`sub_project_no`)

再来看下where后面的条件

	WHERe
		b.corporation_no = a.corporation_no 
		AND b.project_no = a.project_no 
		AND b.sub_project_no = a.sub_project_no 
		AND b.exec_status = '0' 
		AND a.case_type = b.case_type 
		AND a.belong_org_no = b.belong_org_no 
		AND a.exec_org_no = b.exec_org_no 
		AND b.valid_flag = '1' 

索引比查询条件少了3个字段 exec_status、 belong_org_no、exec_org_no 这就意味着不能依赖索引中的数据一次完成查询，mysql必须进行回表操作，即根据索引找到主键，然后去访问行数据，获取这三个字段的值，然后再进行判断、计算。

好了，这时候解决方案呼之欲出，加一个有效索引。

KEY `index_for_update_org_info` (`corporation_no`,`project_no`,`sub_project_no`,`exec_status`,`case_type`,`belong_org_no`,`exec_org_no`,`valid_flag`)

ok 问题解决，执行时间0.09s。

1. 定位问题是拿到的资料不准确会导致分析问题的方向跑偏

2. 慢查询的时间设置要具体问题具体分析。本次情况中慢查询如果一开始直接设置为30s，会让我们少一些干扰数据，少走弯路。

3. 思考：为什么我们的开发人员要在SQL中加一句 IGNORE INDEX ( PRIMARY )

SELECT
		count( 1 ) 
	FROM
		case_exec_record b IGNORE INDEX ( PRIMARY ) 
	WHERe
		b.corporation_no = a.corporation_no 
		AND b.project_no = a.project_no 
		AND b.sub_project_no = a.sub_project_no 
		AND b.exec_status = '0' 
		AND a.case_type = b.case_type 
		AND a.belong_org_no = b.belong_org_no 
		AND a.exec_org_no = b.exec_org_no 
		AND b.valid_flag = '1' 

用主键索引会不会也没问题呢？欢迎大家讨论。