为什么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索引,为什么时间差别这么大?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
离散度这么低的值,,索引意义不大啊。
最后一个查询,你可以创建一个 key(year,type)的聚合索引啊,sql查询一个语句只能用一个索引(子查询算是另一个语句),另外,注意下索引前缀。
你最后的SQL语句没有用到索引,你参考楼下的回答就好
1、两个查询可以创建一个(type,year)的复合索引来用
2、查询时间差异大是因为你的条件不一样,索引检索的顺序规则要明白,第一条:顺序检索type索引,找到对应的主键,再到表数据文件中查找满足year>=2017的数据,找到10条为止,由于2017之前还有很多数据,比如2016的,顺序检索完所有2016才能找到2017的数据,直到找到10条满足条件的记录。第二条:顺序检索type索引,找到10条记录即可!这就是区别!!
你的第一条查询语句有个year条件,这个字段的值目测离散度不大,建索引的话也占用很大空间,这样Mysql在运行时会判断如果索引值占的比重太大,就会直接去全表扫描,这就会造成查询时间缓慢的原因。
就算没有去全表扫描,但是你这year的离散度真是太低了,数据量大的时候索引在进行二分法对比时也要花费一定的时间。