mysql limit 优化

测试数据，mars_tianchi_user_actions（添加了id，主键索引）

问题：如何获取第10000001到10000005呢？

直接使用 limit

mysql> select * from  mars_tianchi_user_actions limit 10000000 ,5;                                                       
+----------+----------------------------------+----------------------------------+------------+-------------+----------+ 
| id       | user_id                          | song_id                          | gmt_create | action_type | ds       | 
+----------+----------------------------------+----------------------------------+------------+-------------+----------+ 
| 10000001 | c4e89a01990bf87d745aa3b0cc6bd7aa | e5b475da38985734b6848130a31545a8 | 1427508000 | 1           | 20150328 | 
| 10000002 | f54c30493238f3f4dd79a4624fa20601 | f35da5a68294505c0e2f5dde6f8a2111 | 1427490000 | 1           | 20150328 | 
| 10000003 | f54c30493238f3f4dd79a4624fa20601 | ef776ef07e703d58c0af76c1031c0483 | 1427490000 | 1           | 20150328 | 
| 10000004 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 | 
| 10000005 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 | 
+----------+----------------------------------+----------------------------------+------------+-------------+----------+ 
5 rows in set (5.50 sec)

耗时 5.50秒，查看explain

mysql> explain select * from  mars_tianchi_user_actions limit 100000000 ,5;
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+---------+
| id | select_type | table                     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
----+----------+
|  1 | SIMPLE      | mars_tianchi_user_actions | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15763020 |   100.00 | NULL  |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+---------+
1 row in set, 1 warning (0.00 sec)

哦，原来是全表扫描，如何利用上索引呢？

连接查询

mysql> select * from  mars_tianchi_user_actions m , (select id from mars_tianchi_user_actions limit 10000000 ,5) t where m.id =t.id;
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| id       | user_id                          | song_id                          | gmt_create | action_type | ds       | id       |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| 10000001 | c4e89a01990bf87d745aa3b0cc6bd7aa | e5b475da38985734b6848130a31545a8 | 1427508000 | 1           | 20150328 | 10000001 |
| 10000002 | f54c30493238f3f4dd79a4624fa20601 | f35da5a68294505c0e2f5dde6f8a2111 | 1427490000 | 1           | 20150328 | 10000002 |
| 10000003 | f54c30493238f3f4dd79a4624fa20601 | ef776ef07e703d58c0af76c1031c0483 | 1427490000 | 1           | 20150328 | 10000003 |
| 10000004 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 | 10000004 |
| 10000005 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 | 10000005 |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
5 rows in set (4.23 sec)

mysql> explain select * from  mars_tianchi_user_actions m , (select id from mars_tianchi_user_actions limit 10000000 ,5) t where m.id =t.id;
+----+-------------+---------------------------+------------+--------+---------------+---------+---------+----------+
| id | select_type | table                     | partitions | type   | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------------------------+------------+--------+---------------+---------+---------+----------+
|  1 | PRIMARY     | <derived2>                | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 10000005 |   100.00 | NULL        |
|  1 | PRIMARY     | m                         | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | t.id |        1 |   100.00 | NULL        |
|  2 | DERIVED     | mars_tianchi_user_actions | NULL       | index  | NULL          | PRIMARY | 8       | NULL | 15763020 |   100.00 | Using index |
+----+-------------+---------------------------+------------+--------+---------------+---------+---------+----------+
3 rows in set, 1 warning (0.00 sec)

耗时4.23s ，确实快了不少（思考衍生表是否还能进一步优化呢？）<derived2>

子查询

mysql> select * from mars_tianchi_user_actions m where m.id in (select id from  mars_tianchi_user_actions limit 10000000 ,5);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

doesn't yet support？？？
修改方案：

mysql> select * from mars_tianchi_user_actions m where m.id in (select t.id from (select id from  mars_tianchi_user_actions limit 10000000 ,5) t);
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| id       | user_id                          | song_id                          | gmt_create | action_type | ds       |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| 10000001 | c4e89a01990bf87d745aa3b0cc6bd7aa | e5b475da38985734b6848130a31545a8 | 1427508000 | 1           | 20150328 |
| 10000002 | f54c30493238f3f4dd79a4624fa20601 | f35da5a68294505c0e2f5dde6f8a2111 | 1427490000 | 1           | 20150328 |
| 10000003 | f54c30493238f3f4dd79a4624fa20601 | ef776ef07e703d58c0af76c1031c0483 | 1427490000 | 1           | 20150328 |
| 10000004 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
| 10000005 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
5 rows in set (15.62 sec)

居然更慢了？？？

mysql> explain select * from mars_tianchi_user_actions m where m.id in (select t.id from (select id from  mars_tianchi_user_actions limit 10000000 ,5) t);          
+----+--------------+---------------------------+------------+--------+---------------+------------+---------+-------+ 
| id | select_type  | table                     | partitions | type   | possible_keys | key        | key_len | ref           | rows     | filtered | Extra       |  
+----+--------------+---------------------------+------------+--------+---------------+------------+---------+-------+
|  1 | PRIMARY      | m                         | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL          | 15763020 |   100.00 | Using where |  
|  1 | PRIMARY      | <subquery2>               | NULL       | eq_ref | <auto_key>    | <auto_key> | 8       | alimusic.m.id |        1 |   100.00 | NULL        |  
|  2 | MATERIALIZED | <derived3>                | NULL       | ALL    | NULL          | NULL       | NULL    | NULL          | 10000005 |   100.00 | NULL        |  
|  3 | DERIVED      | mars_tianchi_user_actions | NULL       | index  | NULL          | PRIMARY    | 8       | NULL          | 15763020 |   100.00 | Using index |  
+----+--------------+---------------------------+------------+--------+---------------+------------+---------+-------+
4 rows in set, 1 warning (0.00 sec)

临时表居然是走的全表查询

mysql> explain select * from mars_tianchi_user_actions m use index(PRIMARY) where m.id in (select t.id from (select id from  mars_tianchi_user_actions limit 10000000 ,5) t);

测试依旧全表查询，只能强制使用了。

mysql> select * from mars_tianchi_user_actions m force index(PRIMARY) where m.id in (select t.id from (select id from  mars_tianchi_user_actions limit 10000000 ,5) t); +----------+----------------------------------+-----+
| id       | user_id                          | song_id                          | gmt_create | action_type | ds       |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| 10000001 | c4e89a01990bf87d745aa3b0cc6bd7aa | e5b475da38985734b6848130a31545a8 | 1427508000 | 1           | 20150328 |
| 10000002 | f54c30493238f3f4dd79a4624fa20601 | f35da5a68294505c0e2f5dde6f8a2111 | 1427490000 | 1           | 20150328 |
| 10000003 | f54c30493238f3f4dd79a4624fa20601 | ef776ef07e703d58c0af76c1031c0483 | 1427490000 | 1           | 20150328 |
| 10000004 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
| 10000005 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
5 rows in set (4.14 sec)

与连接查询相同（误差的范围内）。所以在实际使用中推荐使用连接查询替代子查询。

大于替换

mysql> select * from  mars_tianchi_user_actions where id>10000000 limit 5;
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| id       | user_id                          | song_id                          | gmt_create | action_type | ds       |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
| 10000001 | c4e89a01990bf87d745aa3b0cc6bd7aa | e5b475da38985734b6848130a31545a8 | 1427508000 | 1           | 20150328 |
| 10000002 | f54c30493238f3f4dd79a4624fa20601 | f35da5a68294505c0e2f5dde6f8a2111 | 1427490000 | 1           | 20150328 |
| 10000003 | f54c30493238f3f4dd79a4624fa20601 | ef776ef07e703d58c0af76c1031c0483 | 1427490000 | 1           | 20150328 |
| 10000004 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
| 10000005 | d09af4bcf642b21fd13e11eb672e87f5 | 18b2cd447b72712a106bca7138187534 | 1427497200 | 1           | 20150328 |
+----------+----------------------------------+----------------------------------+------------+-------------+----------+
5 rows in set (0.01 sec)

时间 0.01，时间复杂度可以说是常数。可以说速度是是非常快了，但是这种方法有一点的要求，就是id 是连续的自增，如果修改或者删除操作可能会造成结果的错误。