错误的索引导致mysql查询缓慢的故障排查

Mysql中对允许为null的字段的索引有个很坑人的性能BUG,以下是排除过程:

有一个ZCMS项目的客户抱怨后台卡顿,他们每天有上千篇文章投稿,后台需要做各种审核和编辑工作,因此数据库压力比较大 ,使用

top

命令 看一下CPU利用率,mysql进程的CPU使用率达到了3000%(32核)。 执行命令:

mysql -uroot -p

输入密码后执行:

show full processlist;

发现很多会话都是同一句SQL:

select count(1) from zccontent a where -1<>122 and  a.cataloginnercode like '001314%' and a.contenttypeid='Article' and a.status=30;

状态都是Sending data,时间都持续了几十秒甚至几分钟。 把这句SQL拿出来执行,结果如下:

mysql> select count(1) from zccontent a where -1<>122 and a.contenttypeid='Article' and a.status=30 and a.cataloginnercode like '001314%';
+----------+| count(1) |
+----------+|   214556 |
+----------+1 row in set (1 min 6.23 sec)

确实执行时间超过了1分钟,多次执行甚至有超过了3分钟的情况。很显然,这里有两个问题,第一没有开启查询缓存(不然多次执行后执行时间要接近0),每次都要重新执行SQL。第二,这个mysql实例使用了innodb引擎,给innodb分配了16G缓存,100万条记录完全可以全部缓存到内存中,为什么这么一个简单的查询会这么慢?一般来说这样的数量级下查询这样的简单SQL不应该超过1秒。通过explain获得这句SQL的查询计划:

mysql> explain  select count(1) from zccontent a where -1<>122 and  a.cataloginnercode like '001314%' and a.contenttypeid='Article' and a.status=30;
+----+-------------+-------+------+----------------------------+---------------+---------+-------+--------+-------------+| id | select_type | table | type | possible_keys              | key           | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+----------------------------+---------------+---------+-------+--------+-------------+|  1 | SIMPLE      | a     | ref  | idx_article2,idx_article16 | idx_article16 | 153     | const | 583821 | Using where |
+----+-------------+-------+------+----------------------------+---------------+---------+-------+--------+-------------+1 row in set (0.00 sec)

发现有两个可能的索引idx_article2和idx_article16,但会实际使用idx_article16。通过

show index from zccontent

查看这两个索引:

+-----------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zccontent |          1 | idx_article2       |            1 | CatalogInnerCode | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| zccontent |          1 | idx_article16      |            1 | ContentTypeID    | A         |         524 |     NULL | NULL   | YES  | BTREE      |         |               |

经过分析,发现因为绝大部分数据都是ContentTypeID='Article',所以走idx_article16相当于强制走了全表扫描。所以第一反应是这个索引帮了倒忙,执行SQL:

alter table zccontent drop index idx_article16;

再次执行:

mysql> select count(1) from zccontent a where -1<>122 and a.contenttypeid='Article' and a.status=30 and a.cataloginnercode like '001314%';
+----------+| count(1) |
+----------+|   218033 |
+----------+1 row in set (0.72 sec)

变成了0.72秒,算是比较正常的性能了。再次explain,结果为:

mysql> explain  select count(1) from zccontent a  use index (idx_article2)  where -1<>122 and  a.cataloginnercode like '001314%' and a.contenttypeid='Article' and a.status=30;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | a     | ALL  | idx_article2  | NULL | NULL    | NULL | 1105366 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)

并没有走idx_article2,是走的全表扫描,但也 只用了0.72秒!(一般来说possibl_keys只剩下idx_article2了,应该会使用idx_article2,但 因为这个SQL对应的数据量占了总记录数的22%,所以mysql选择不使用索引直接全表扫描)那么全表扫描只需要0.72秒,为什么使用ContentTypeID单字段索引需要几分钟呢?这中间性能差异两个数量级呀。这个索引一定有非常大的问题,以至于只要不走这个索引就可以提高100倍的性能!

惊叹之后各种实验和对比,发现是因为ContentTypeID字段允许为空导致的。 如果使用:

alter table zccontent modify ContentTypeID varchar(50) not null;

将字段设成不允许空后再重建这个索引:

create index idx_article16 on zccontent (ContentTypeID);

再次执行上面的SQL语句,发现果然变成了0.7秒!

结论:

  1. 对于值绝大部分相同的字段,没必要使用索引,这个索引会妨碍mysql使用其它索引。

  2. 如果字段值实际上不允许为null,则务必要设成not null

  3. mysql下如果某个字段值允许为null(哪怕实际上全部都有值),会极大影响索引性能,具体原因待查。

  4. 务必要设置query_cache_size为大于0,一般可以设成100M。mysql的查询缓存在表中的数据有更新情况会失效,而这个客户的系统数据操作非常频繁,基本上每隔几秒表的数据就会有更新。但是在这样频繁的操作下,查询缓存依旧发挥了作用,有约60%的命中率。如果更新不这么频繁,命中率还会上升。

  5. 去掉不必要的ContentTypeID索引以及设置query_cache_size=204800000后,服务器的CPU使用率在10%左右波动,后台非常流畅。

另外的索引常识

  1. tag like '%name%' 这种语句完全用不上索引,因此没有必要因为这个为tag字段建索引,完全是浪费。

  2. tag like 'name%' 可以使用索引。

hackIE