最近Mysql v8.20 48c 256g的数据库服务器每天固定时间CPU飙升，告警平台超过90%的警告信息不断的报😟😟😟 同时附带/tmp使用超95%告警😟😟😟
在领导不断安排下，几天后终于开始着手处理这个事情了。
对于这种突发的CPU冲高，大部分还是因为某条SQL语句效率很低，同时有伴随着大于低效SQL运行效率的并发出现，造成了服务器资源不断被占用，直到服务器资源耗尽（这是一种平衡被打破的情况，具体大家可以体会一下），所以在排除OS问题后，思路还是先找到那条SQL语句。 附带一个抓取大量耗费CPU的SQL的小脚本yw1_mysql_get_top5_sql.sh，有兴趣可以自己本地化一下，废话不说，上脚本，当然也有慢日志可以挖。
#!/bin/bash
###Top 5 sql
###If you want to get top 10 sql,please modify variable yw1_mysql_top_threads's -A 10
###Author:yideng
yw1_config_file_hidden=".config.cnf"
yw1_current_path=`pwd`
yw1_log_file_name="$yw1_current_path/log/cpu_top5_`date +%Y%m%d%H%M%S`.log"
[ ! -d log ] && mkdir ./log
#get username and passwd
yw1_mysql_user=`cat $yw1_current_path/$yw1_config_file_hidden |awk -F'=' '/USER/{print $2}'`
yw1_mysql_pd=`cat $yw1_current_path/$yw1_config_file_hidden |awk -F'=' '/PASSWORD/{print $2}'`
#echo -e "hint of command:top and top -Hp"
#read -p "mysql PID:" yw1_mysql_PID
echo "=====`date +%F' '%T`=====" >> $yw1_log_file_name
#get MYSQL PID
yw1_mysql_PID=`ps -aux | grep "mysql" | grep "mysql.sock"|grep -v "grep"|awk '{print $2}'`
top -b -n 1 | grep 'top -' -A 11 >> $yw1_log_file_name
echo -e "\n=========yw1_mysql_PID_tag=========">> $yw1_log_file_name
top -n 1 -Hp $yw1_mysql_PID | grep 'PID USER' -A 5 >> $yw1_log_file_name;
yw1_mysql_top_threads=`cat $yw1_log_file_name |grep "yw1_mysql_PID_tag" -A 6 |grep -v "PID USER" |grep -v "yw1_mysql_PID_tag"|awk '{print$2}'`
echo -e "\n===========TOP 5 SQL===========">> $yw1_log_file_name
for line in $yw1_mysql_top_threads;do
yw1_thread_id=`mysql -u "$user" -p${yw1_mysql_pd} -N -e "select yw1_thread_id from performance_schema.threads where THREAD_OS_ID=$line;" 2> /dev/null`
echo -e "\nthread:$line yw1_thread_id:$yw1_thread_id\n------------------------------">> $yw1_log_file_name
mysql -u"$yw1_mysql_user" -p${yw1_mysql_pd} -N -e "select DIGEST_TEXT from performance_schema.events_statements_current where yw1_thread_id=$yw1_thread_id;" 2> /dev/null >> $yw1_log_file_name
done
cat $yw1_log_file_name
echo -e "\n=======collect completed======="
echo -e "log path:\n$yw1_log_file_name"
这条奇怪的SQL终于找到了。我看到这条SQL都蒙了，结果就一条为什么还要分页？明明只需要统计name的数量，为什么还要列出b.id？id列是个主键，为什么还要group by？（大量group by排序在Mysql里将用/tmp目录）不过这些都不是主要的。
测试+慢日志追踪它，发现不阻塞的时候执行时间是5秒多，阻塞的时候执行效率是30多秒，有时高达120多秒。
SELECT COUNT(DISTINCT(r.xxx_name))
FROM (SELECT b.id, b.xxx_name
FROM bbb_bbbb_bbbbb_info b
LEFT JOIN elel_ell_list el
ON b.ENT_abcd = el.ENT_abcdefg
WHERE b.status = 1
and abcd_abc_type = 1
GROUP BY b.id) r LIMIT 14300,
100;
先全表数据量了解一下：
el表数据量7.6万，b表数据量75多万，明明是两个不太大的表，为何效率会这么低下呢？
让我们再深入了解一下SQL使用的列的数据量与结构：
- el与b表的id列均为主键；
- abcd_abc_type列是el表的，有索引（绝大部分值都是1，而且此列在el表上的基数为2，还不如不要索引）；
- b.status表大概有60多万行值为1，其余乱七八糟有几个其他值（无索引，这种列建了索引也没什么意义）；
- b.ENT_abcd类型varchar(50)对于75万行数据的B表基数为5W不到一点，比例仅为6.3%；（重点在此）
- el.ENT_abcdefg类型varchar(40)列基数很高，比例90%左右。
- xxx_name字段无索引。
了解到这里，有经验的小伙伴都知道了慢的原因了，下面我们继续通过执行计划进行求证。
+----+-------------+------------+------------+-------+-------------------------------------+--------------------+---------+-----------------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------------------------+--------------------+---------+-----------------+--------+----------+------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 36367 | 100.00 | NULL |
| 2 | DERIVED | b | NULL | index | PRIMARY,idx_xxxx_xxx_xx_xx | PRIMARY | 8 | NULL | 727364 | 10.00 | Using where |
| 2 | DERIVED | el | NULL | ref | idx_xxxx_xxxx_xxxx,idx_xxx_xxx_xxxx | idx_xxx_xxxxx_xxxx | 162 | xxxx.b.xxx_xxxx | 1 | 50.00 | Using index condition; Using where |
+----+-------------+------------+------------+-------+-------------------------------------+--------------------+---------+-----------------+--------+----------+------------------------------------+
3 rows in set, 1 warning (0.01 sec)
我们看下 LEFT JOIN elel_ell_list el ON b.ENT_abcd = el.ENT_abcdefg b.ENT_abcd 作为join的主要条件，基数比例仅为6%，这点从执行计划里也看到了，filtered仅为10%左右。
不过我们还是没有放弃，在先收集统计信息ANALYZE TABLE，做碎片整理后。 然后再不断尝试改写，加hit的情况下，最终执行效率到达了2.25s👏 但是并未达到业务需求，果断要求改业务逻辑，为何用如此低基数的列做为匹配条件，难道就没有其他表能实现此业务吗？ 经过开发小哥的思考，终于找到其他表来实现此业务，单表主键查询就可以了👏
至此，生产环境优化结束。
最终必须在测试环境大概重现出此语句执行情况：
mysql> select * from t100w limit 1; --t100w，testjoin两张表结构相同
+----+-------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+----+-------+------+------+---------------------+
| 1 | 25503 | 0M | IJ56 | 2019-08-12 11:41:16 |
+----+-------+------+------+---------------------+
1 row in set (0.00 sec)
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 750000 |
+----------+
1 row in set (0.05 sec)
mysql> select count(*) from testjoin;
+----------+
| count(*) |
+----------+
| 80000 |
+----------+
1 row in set (0.00 sec)
--基数比更低一些
mysql> select count(*) from (select count(*) as c1 from t100w t group by k1) c2 ;
+----------+
| count(*) |
+----------+
| 1225 |
+----------+
1 row in set (0.62 sec)
mysql> select count(*) from (select count(*) as c1 from t100w t group by k2) c2 ;
+----------+
| count(*) |
+----------+
| 1296 |
+----------+
1 row in set (0.83 sec)
--索引创建情况，索引基本相同，字段k2上无索引
mysql> show index from t100w;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t100w | 0 | PRIMARY | 1 | id | A | 748032 | NULL | NULL | | BTREE | | | YES | NULL |
| t100w | 1 | k1 | 1 | k1 | A | 1310 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
mysql> show index from testjoin;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| testjoin | 0 | PRIMARY | 1 | id | A | 77683 | NULL | NULL | | BTREE | | | YES | NULL |
| testjoin | 1 | idx_k1 | 1 | k1 | A | 493 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
--测试环境模拟的语句--后面两个=1的条件省略了，因为基数太小，基本等于1=1;
select distinct(count(r.k2)) from (select t1.k2 from t100w t1 left join testjoin t2 on t1.k1=t2.k1 ) r;
+---------------+
| (count(r.k2)) |
+---------------+
| 55355739 |
+---------------+
1 row in set (22.10 sec)
--真实执行计划
desc analyze select distinct(count(r.k2)) from (select t1.k2 from t100w t1 left join testjoin t2 on t1.k1=t2.k1 ) r;
| EXPLAIN
| -> Aggregate: count(t1.k2) (cost=24584501.44 rows=117868903) (actual time=29190.135..29190.136 rows=1 loops=1)
-> Nested loop left join (cost=12797611.14 rows=117868903) (actual time=0.111..26300.421 rows=55355740 loops=1)
-> Table scan on t1 (cost=77077.32 rows=748032) (actual time=0.059..345.737 rows=750000 loops=1)
-> Covering index lookup on t2 using idx_k1 (k1=t1.k1) (cost=1.25 rows=158) (actual time=0.021..0.030 rows=74 loops=750000)
|
1 row in set (29.19 sec)
--tree格式，执行计划
desc format=tree select distinct(count(r.k2)) from (select t1.k2 from t100w t1 left join testjoin t2 on t1.k1=t2.k1 ) r;
| EXPLAIN
| -> Aggregate: count(t1.k2) (cost=24584501.44 rows=117868903)
-> Nested loop left join (cost=12797611.14 rows=117868903)
-> Table scan on t1 (cost=77077.32 rows=748032)
-> Covering index lookup on t2 using idx_k1 (k1=t1.k1) (cost=1.25 rows=158)
|
--json格式，执行计划
desc format=json select distinct(count(r.k2)) from (select t1.k2 from t100w t1 left join testjoin t2 on t1.k1=t2.k1 ) r;
| EXPLAIN
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12797611.14"
},
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 748032,
"rows_produced_per_join": 748032,
"filtered": "100.00",
"cost_info": {
"read_cost": "2274.12",
"eval_cost": "74803.20",
"prefix_cost": "77077.32",
"data_read_per_join": "28M"
},
"used_columns": [
"k1",
"k2"
]
}
},
{
"table": {
"table_name": "t2",
"access_type": "ref",
"possible_keys": [
"idx_k1"
],
"key": "idx_k1",
"used_key_parts": [
"k1"
],
"key_length": "9",
"ref": [
"test.t1.k1"
],
"rows_examined_per_scan": 157,
"rows_produced_per_join": 117868902,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "933643.52",
"eval_cost": "11786890.30",
"prefix_cost": "12797611.14",
"data_read_per_join": "4G"
},
"used_columns": [
"k1"
]
}
}
]
}
} |
就算使用id主键作为join匹配条件耗时也较久：
mysql> select distinct(count(r.k2)) from (select t1.k2 from t100w t1 left join testjoin t2 on t1.id=t2.id ) r;
+---------------+
| (count(r.k2)) |
+---------------+
| 750000 |
+---------------+
1 row in set (3.92 sec)
才疏学浅，目前只能做到这里了，拿这样的业务逻辑SQL有种无奈的感觉🤦♂️
希望业务从设计开始就能避免这样的情况出现🤣
如果有大佬能指点一下，不胜感激！💕
