有一张很老的数据表，时间戳格式为varchar，字段如下：
id bigint
name varchar(200)
create_time varchar(20)
//索引
KEY `IDX_CREATED` (`create_time`),
数据约500多万，现在引出发现的问题，一条sql语句效率非常的低：
select name, create_time from t where create_time > 1434115807296 order by create_time limit 1000;
本机测试200s，执行计划：
> explain select name, create_time 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 name, create_time from t where create_time > 1434115807296 order by create_time
执行时间5s，执行计划：
> explain select name, create_time 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 查询慢这么多？ 用profiles看下详细信息
mysql> show profiles;
+----------+--------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+--------------+------------------------------------------------------------------------------------------------------------+
| 1 | 0.00179100 | explain select name, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
| 2 | 195.10171300 | select name, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
| 3 | 5.28713500 | select name, created_at from user where created_at > 1434115807296 order by created_at |
+----------+--------------+------------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 2;
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| starting | 0.000050 |
| checking permissions | 0.000006 |
| Opening tables | 0.000013 |
| init | 0.000017 |
| System lock | 0.000005 |
| optimizing | 0.000006 |
| statistics | 0.000018 |
| preparing | 0.000011 |
| Sorting result | 0.000002 |
| executing | 0.000002 |
| Sending data | 195.101362 |
| end | 0.000011 |
| query end | 0.000009 |
| closing tables | 0.000012 |
| freeing items | 0.000158 |
| logging slow query | 0.000006 |
| cleaning up | 0.000025 |
+----------------------+------------+
17 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000063 |
| checking permissions | 0.000007 |
| Opening tables | 0.000029 |
| init | 0.000024 |
| System lock | 0.000730 |
| optimizing | 0.000014 |
| statistics | 0.000021 |
| preparing | 0.000010 |
| Sorting result | 0.000003 |
| executing | 0.000002 |
| Sending data | 0.000006 |
| Creating sort index | 5.286166 |
| end | 0.000012 |
| query end | 0.000006 |
| closing tables | 0.000011 |
| freeing items | 0.000016 |
| cleaning up | 0.000015 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)
查询1 基本所有的耗时都在 Sending Data 时期，查询3 的耗时在创建排序索引的时期，也就是 filesort 的耗时了，Sending Data是什么鬼玩意！
Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
官方对这个状态解释的是处理查询的数据及返回给客户端，根据查询3能排除返回客户端耗时的原因。问题很早就看出来了，create_time是varchar类型，但sql对比的是一个int类型，解决方案就是改为Int/Bigint类型。
但是慢的原因还未知，随后又增加了其他的测试，例如把name换成主键id，速度立马变成不到5秒
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000061 |
| checking permissions | 0.000007 |
| Opening tables | 0.000014 |
| init | 0.000019 |
| System lock | 0.000006 |
| optimizing | 0.000006 |
| statistics | 0.000018 |
| preparing | 0.000013 |
| Sorting result | 0.000003 |
| executing | 0.000002 |
| Sending data | 4.531929 |
| end | 0.000009 |
| query end | 0.000006 |
| closing tables | 0.000010 |
| freeing items | 0.000041 |
| cleaning up | 0.000015 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
可以看到Sending data时间大大降低，是因为id是索引字段？那么我给name也增加一个索引…
结果竟然依旧是200多s，这就奇怪了，难道是因为name的长度太大？ 再新增一个sex char(2)，并建好索引，
结果依旧一样！
mysql> show profiles;
+----------+--------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+--------------+------------------------------------------------------------------------------------------------------------+
| 1 | 0.00179100 | explain select name, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
| 2 | 195.10171300 | select name, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
| 3 | 5.28713500 | select name, created_at from user where created_at > 1434115807296 order by created_at |
| 4 | 4.53215900 | select id, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
| 5 | 209.23518600 | select sex, created_at from user where created_at > 1434115807296 order by created_at limit 1000 |
+----------+--------------+------------------------------------------------------------------------------------------------------------+
这个问题有待解决！