数据库慢查询问题处理
warning:
这篇文章距离上次修改已过1266天,其中的内容可能已经有所变动。
无用索引导致数据库慢查询问题处理
问题排查
- 新接手的一个网站CPU跑满了,通过
top
发现是mysql异常,show processlist
查看发现是大量的慢SQL到账的SELECT COUNT(*) FROM idmaps WHERE typeid=8 AND keyid=1085287221 AND showtype=0
, 查下表数据千万量级,ibd文件20几个G,数据不小呀,盘它 - 垃圾代码害死人啊,分析下问题,顺便填坑
- 检查表中索引
show create table idmaps;
CREATE TABLE `idmaps` (
`typeid` tinyint(3) unsigned NOT NULL DEFAULT '0',
`keyid` int(10) unsigned NOT NULL DEFAULT '0',
`myid` int(10) unsigned NOT NULL DEFAULT '0',
`showtype` tinyint(3) unsigned NOT NULL DEFAULT '0',
`exttype` int(10) unsigned NOT NULL DEFAULT '0',
`sortnum` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`typeid`,`myid`,`keyid`),
KEY `idx_list` (`typeid`,`keyid`,`showtype`,`sortnum`),
KEY `idx_list2` (`typeid`,`keyid`,`showtype`,`exttype`,`sortnum`) USING BTREE,
KEY `idx_ids` (`typeid`,`keyid`),
KEY `typeid` (`typeid`),
KEY `keyid` (`keyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
- 检查索引
explain SELECT COUNT(*) FROM idmaps WHERE typeid=8 AND keyid=275787765 AND showtype=0;
+----+-------------+--------+------+-------------------------------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------------------------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | idmaps | ref | PRIMARY,idx_list,idx_list2,idx_ids,typeid,keyid | PRIMARY | 1 | const | 1 | Using where |
+----+-------------+--------+------+-------------------------------------------------+---------+---------+-------+------+-------------+
- 莫名其妙的PRIMARY KEY,执行时间20s+, 访问量起来后,基本直接卡死
mysql> SELECT COUNT(*) FROM idmaps WHERE typeid=8 AND keyid=275787765 AND showtype=0;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (21.80 sec)
- 强制指定索引
force index
mysql> SELECT COUNT(*) FROM idmaps force index(idx_ids) WHERE typeid=8 AND keyid=275785755 AND showtype=0;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
- 再强制指定索引
mysql> explain SELECT COUNT(*) FROM idmaps force index(idx_list) WHERE typeid=8 AND keyid=275787765 AND showtype=0;
+----+-------------+--------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | idmaps | ref | idx_list | idx_list | 6 | const,const,const | 2 | Using index |
+----+-------------+--------+------+---------------+----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
- 再来
mysql> SELECT COUNT(*) FROM idmaps force index(keyid) WHERE typeid=8 AND keyid=275785755 AND showtype=0;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
分析过程
- 导致此问题的原因明显是索引不当,结合数据库实际读写情况,检查SQL统计,
PRIMARY KEY
基本无用,尝试调整索引解决
处理结果
- 经尝试,最简单办法直接删除
alter table idmaps drop PRIMARY KEY;
。 成功解决此慢查询问题,且不影响其它SQL预计。