有人能解释一下 mysql 加了索引反而超级慢吗？

[checgg]

mysql> desc channel_details;
+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment |
| year          | int(11)     | NO   | MUL | NULL              |                |
| month         | int(11)     | NO   |     | NULL              |                |
| point_of_time | varchar(32) | NO   |     | NULL              |                |
| channel       | varchar(32) | NO   | MUL | NULL              |                |
| ratings       | float       | NO   |     | NULL              |                |
| type          | int(11)     | NO   | MUL | NULL              |                |
| age           | int(11)     | NO   | MUL | NULL              |                |
| time_at       | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+-------------+------+-----+-------------------+----------------+
9 rows in set (0.01 sec)

mysql> select * from channel_details where channel like "%" and year>=2017 and year<=2017 and type=29 limit 10;
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
| id      | year | month | point_of_time | channel     | ratings | type | age | time_at             |
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
| 7124305 | 2017 |     1 | 02:00         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124306 | 2017 |     1 | 02:01         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124307 | 2017 |     1 | 02:02         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124308 | 2017 |     1 | 02:03         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124309 | 2017 |     1 | 02:04         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124310 | 2017 |     1 | 02:05         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124311 | 2017 |     1 | 02:06         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124312 | 2017 |     1 | 02:07         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124313 | 2017 |     1 | 02:08         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124314 | 2017 |     1 | 02:09         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
10 rows in set (19.53 sec)

mysql> select * from channel_details where channel like "%" and year>=2017 and year<=2017 limit 10;
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
| id      | year | month | point_of_time | channel     | ratings | type | age | time_at             |
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
| 7124305 | 2017 |     1 | 02:00         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124306 | 2017 |     1 | 02:01         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124307 | 2017 |     1 | 02:02         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124308 | 2017 |     1 | 02:03         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124309 | 2017 |     1 | 02:04         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124310 | 2017 |     1 | 02:05         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124311 | 2017 |     1 | 02:06         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124312 | 2017 |     1 | 02:07         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124313 | 2017 |     1 | 02:08         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
| 7124314 | 2017 |     1 | 02:09         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
+---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
10 rows in set (0.07 sec)

mysql> explain select * from channel_details where channel like "%" and year>=2017 and year<=2017 and type=29 limit 10;
+----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | channel_details | NULL       | ref  | type,year     | type | 4       | const | 4969150 |     5.56 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from channel_details where channel like "%" and year>=2017 and year<=2017 limit 10;
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+
| id | select_type | table           | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                         |
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+
|  1 | SIMPLE      | channel_details | NULL       | range | year          | year | 4       | NULL | 4969150 |    11.11 | Using index condition; Using where; Using MRR |
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+

mysql> select count(*) from channel_details group by year;
+----------+
| count(*) |
+----------+
|   141218 |
|  6498910 |
|  4226453 |
+----------+
3 rows in set (4.36 sec)

mysql> select count(*) from channel_details group by type;
+----------+
| count(*) |
+----------+
|  3573680 |
|  3647171 |
|  3645730 |
+----------+
3 rows in set (2.54 sec)

mysql> select count(*) from channel_details group by year,type;
+----------+
| count(*) |
+----------+
|   141218 |
|  2072158 |
|  2213376 |
|  2213376 |
|  1360304 |
|  1433795 |
|  1432354 |
+----------+
7 rows in set (27.02 sec)

都是同样的索引类型，速度却相差巨大。有人可以解释一下吗?

[stabc]

你能稍微就数据解释一下以便节省别人的分析时间么？

[kn007]

建个联合索引看看？

[kn007]

如果联合索引还是慢，可以复合 select 看看。

[kn007]

另外你可以看到，第一个查询并没有用到 index，而是直接 where 了。

[kn007]

感觉还是少了联合索引，把 year 删掉，建个 (year,type)吧。

[lyog]

like % 导致索引失效了吧

[odirus]

瞎猜一下，你的 type 字段是不是存在大量的 null 数据？

[chenqh]

感觉是 MMR 的问题吧

[chenqh]

不过第二个为什么会有 using index condition 啊

[alcarl]

用手机看得我眼快瞎了，也没看明白你在哪里建了什么索引。。。。。第一个慢可能是因为 mysql 傻傻的觉着走 type 索引更好，但没想到 type 弄出来的数太多了，在里面找了好久才找到 2017 年的吧。mysql 的大量数据查询一直是让人着急的弱

[bigpigeon]

你是不是用了组合 index，能不能用 show create table 看看表结构

[checgg]

谢谢楼上的回答。