為什么mysql 走同樣的索引耗時差別卻巨大?

耗時查詢：

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索引，為什么時間差別這么大？

回答

編輯回答

風(fēng)畔

1、兩個查詢可以創(chuàng)建一個(type,year)的復(fù)合索引來用
2、查詢時間差異大是因為你的條件不一樣，索引檢索的順序規(guī)則要明白，第一條：順序檢索type索引，找到對應(yīng)的主鍵，再到表數(shù)據(jù)文件中查找滿足year>=2017的數(shù)據(jù)，找到10條為止，由于2017之前還有很多數(shù)據(jù)，比如2016的，順序檢索完所有2016才能找到2017的數(shù)據(jù)，直到找到10條滿足條件的記錄。第二條：順序檢索type索引，找到10條記錄即可！這就是區(qū)別??！

2018年1月6日 23:49

編輯回答

悶油瓶

你最后的SQL語句沒有用到索引,你參考樓下的回答就好

2017年1月27日 05:14

編輯回答

尐潴豬

你的第一條查詢語句有個year條件，這個字段的值目測離散度不大，建索引的話也占用很大空間，這樣Mysql在運行時會判斷如果索引值占的比重太大，就會直接去全表掃描，這就會造成查詢時間緩慢的原因。
就算沒有去全表掃描，但是你這year的離散度真是太低了，數(shù)據(jù)量大的時候索引在進(jìn)行二分法對比時也要花費一定的時間。

2017年12月9日 11:38

編輯回答