为什么mysql 走同样的索引耗时差别却巨大?

Question

耗时查询：

mysql> select * from channel_details where channel like "%" and type=29 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 (16.10 sec)

mysql> explain select * from channel_details where channel like "%" and type=29 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       | ref  | year,type     | type | 4       | const | 4969150 |     5.56 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

不耗时查询：

mysql> explain select * from channel_details where channel like "%" 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          | type | 4       | const | 4969150 |    11.11 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

mysql> select * from channel_details where channel like "%" and type=29  limit 10;
+----+------+-------+---------------+-------------+---------+------+-----+---------------------+
| id | year | month | point_of_time | channel     | ratings | type | age | time_at             |
+----+------+-------+---------------+-------------+---------+------+-----+---------------------+
|  1 | 2016 |     9 | 02:00         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  2 | 2016 |     9 | 02:01         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  3 | 2016 |     9 | 02:02         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  4 | 2016 |     9 | 02:03         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  5 | 2016 |     9 | 02:04         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  6 | 2016 |     9 | 02:05         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  7 | 2016 |     9 | 02:06         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  8 | 2016 |     9 | 02:07         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
|  9 | 2016 |     9 | 02:08         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
| 10 | 2016 |     9 | 02:09         | ??????????? |       0 |   29 |  40 | 2016-12-22 22:59:24 |
+----+------+-------+---------------+-------------+---------+------+-----+---------------------+
10 rows in set (0.11 sec)

明明都是走了type索引，为什么时间差别这么大？

Answer 1

离散度这么低的值，，索引意义不大啊。
最后一个查询，你可以创建一个 key(year,type)的聚合索引啊，sql查询一个语句只能用一个索引(子查询算是另一个语句)，另外，注意下索引前缀。

Answer 2

你最后的SQL语句没有用到索引,你参考楼下的回答就好

Answer 3

1、两个查询可以创建一个(type,year)的复合索引来用
2、查询时间差异大是因为你的条件不一样，索引检索的顺序规则要明白，第一条：顺序检索type索引，找到对应的主键，再到表数据文件中查找满足year>=2017的数据，找到10条为止，由于2017之前还有很多数据，比如2016的，顺序检索完所有2016才能找到2017的数据，直到找到10条满足条件的记录。第二条：顺序检索type索引，找到10条记录即可！这就是区别！！

Answer 4

你的第一条查询语句有个year条件，这个字段的值目测离散度不大，建索引的话也占用很大空间，这样Mysql在运行时会判断如果索引值占的比重太大，就会直接去全表扫描，这就会造成查询时间缓慢的原因。
就算没有去全表扫描，但是你这year的离散度真是太低了，数据量大的时候索引在进行二分法对比时也要花费一定的时间。