MySQL慢查询分析二

2017-02-20 留言

记录一次SQL优化

最近在使用MySQL时候，发现了一个索引设计有问题导致的性能问题，记录下，
接口从MySQL返回一个Json，MySQL连接大概500个的时候，每次要花20s才能返回数据，这简直没法用了。
进入MySQL之后，show processlist;发现大量都卡住在sending data上，搜索一圈没结果，尝试explain解决。

表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
create table cookies (
    pk int(11) not null auto_increment primary key,
    host varchar(100) not null, 
    url varchar(256) not null, 
    cookie varchar(1024) not null,
    ip varchar(32) not null, 
    port smallint not null default 80, 
    proxy varchar(32) default null,  
    headers varchar(1024)  default null,
    status tinyint(1) not null default 1,
    _md5 varchar(63) not null unique
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这时候还没有加索引。数据大约10W条数据其中host字段只有两个，是www.google.com.hk和www.baidu.com，大约各占一半，
status只有2个，0和1，其中1的记录只有20条，其他都是0的。

发现这个SQL语句的host没加索引，就加上索引，但是并没有用处。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select count(*) from cookies where status=1;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | cookies | ALL  | NULL          | NULL | NULL    | NULL | 97866 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT count(*) FROM cookies WHERE host='www.baidu.com' AND status=1;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | cookies | ALL  | NULL          | NULL | NULL    | NULL | 97866 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT count(*) FROM cookies WHERE host='www.baidu.com';
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | cookies | ALL  | NULL          | NULL | NULL    | NULL | 97866 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

其中rows还是要遍历全表的。

在status上加索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> ALTER TABLE `cookies` ADD INDEX host_index (`host`);
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain SELECT count(*) FROM cookies WHERE host='www.baidu.com';
+----+-------------+---------+------+---------------+------------+---------+-------+-------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows  | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | cookies | ref  | host_index    | host_index | 302     | const | 48933 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+-------+--------------------------+
1 row in set (0.00 sec)
ALTER TABLE `cookies` ADD INDEX status_index (`status`);
mysql> explain SELECT count(*) FROM cookies WHERE host='www.baidu.com' AND status=1;
+----+-------------+---------+------+-------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys           | key          | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+-------------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | cookies | ref  | host_index,status_index | status_index | 1       | const |   20 | Using where |
+----+-------------+---------+------+-------------------------+--------------+---------+-------+------+-------------+
1 row in set (0.01 sec)
mysql> explain SELECT count(*) FROM cookies WHERE status=1;
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | cookies | ref  | status_index  | status_index | 1       | const |   20 | Using index |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

可以看出，在status上加索引，执行计划中会提高很多性能，因为直接通过status过滤出来数据很少。
给status加上索引后，API的响应时间回归正常了。

这次经验是:

1.对于重复量很大的字段，无需建立索引，比如性别，因为这样无法有效过滤;
2.加上索引，要通过explain执行计划结合不同查询条件，看看索引是否有效，而不是简单的加上索引就完了。

PS:如果以后host字段的值增多了，比如至少100个的话，那么加上索引还是有用的。

#MySQL