无用索引导致数据库慢查询问题处理

问题排查

• 新接手的一个网站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预计。