索引 - MySQL limit对效率的影响

Question

有一张很老的数据表，时间戳格式为varchar，字段如下：

id bigint
name varchar(200)
create_time varchar(200)
//索引
KEY `IDX_CREATED` (`create_time`),

数据约500多万，现在引出发现的问题，一条sql语句效率非常的低：

select id, name from t where create_time > 1434115807296 order by create_time limit 1000;

本机测试200s，执行计划：

> explain select id, name from t where create_time > 1434115807296 order by create_time limit 1000;

+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | User  | index |  IDX_CREATED  |  IDX_CREATED  |   63    | NULL | 1000 | Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)

如果去掉 limit :

select id, name from t where create_time > 1434115807296 order by create_time

执行时间5s，执行计划：

> explain select id, name from t where create_time > 1434115807296 order by create_time

+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | User  | ALL  |  IDX_CREATED  | NULL |   NULL  | NULL | 4858500 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)    

一个 index 查询竟然比 ALL&filesort 查询慢这么多？ 请 MySQL 达人指教

Answer 1

看执行计划，这两个差别主要在于是否使用了IDX_CREATED的索引，以及filesort。

前者用上了索引，所以你前面where条件的查询速度会有很大提高，但是很奇怪没有用到filesort，所以在后面的order by阶段会消耗了很大的时间。

后者就是直接简单的查询，没有使用索引，并进行了正常的文件排序。

可以考虑强制不使用索引Ignore index来优化此句的性能。
建议你用profile跟踪再详细看一下每个阶段的时间消耗，这样会更为准确。

Answer 2

把
create_time varchar(200)
改为
create_time int(11)

你是用laravel框架自动生成的吧？傻！

另外，你这样对比不公平啊！
MYSQL 分页慢加速器 解决方案 MYSQL 分页优化 MYSQL 分页解决方案 LIMIT 优化