有一张很老的数据表,时间戳格式为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 达人指教