MySQL使用limit优化
MySQL(假设使用Innodb引擎) limit是一个很有意思的选项，当使用limit限定结果输出的
时候，如果是少量的行,mysql可能非常快,
表结构如下:
create table tt (a int primary key auto_increment,b int, c int);
create index ix_b on tt(b);
1,只使用limit子句限定所需要的行
mysql> explain select * from tt limit 0,10;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | tt | ALL | NULL | NULL | NULL | NULL | 9981864 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)
mysql> explain select count(c) from tt;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | tt | ALL | NULL | NULL | NULL | NULL | 9981864 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)
虽然计划执行看起来没有区别，都是走全表扫描，但是实际上的执行速度却千差万别。
mysql> select * from tt limit 0,10;
+----+------+------+
| a | b | c |
+----+------+------+
| 1 | 10 | 11 |
| 2 | 20 | 12 |
| 3 | 30 | 13 |
| 4 | 40 | 14 |
| 5 | 50 | 15 |
| 6 | 60 | 16 |
| 7 | 70 | 17 |
| 8 | 80 | 18 |
| 9 | 90 | 19 |
| 10 | 100 | 20 |
+----+------+------+
10 rows in set (0.00 sec)
mysql> select count(c) from tt;
+----------+
| count(c) |
+----------+
| 10000000 |
+----------+
1 row in set (1.88 sec)
可以看到第一种速度会非常快，mysql的innodb使用了btree的表存储结构，因此这种情况
下只需要从btree的最左边开始扫描十行就返回了，不需要走全表扫描。而且我们可以发现这种情况下
limit的返回行的顺序就是主键的顺序.
2,limit和order by子句，这里有分两种情况:
1),根据二级索引的顺序，扫描二级索引，根据索引返回需要的行即可，整个过程也不需要排序;
mysql> explain select * from tt order by b limit 0,10000;
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | tt | index | NULL | ix_b | 5 | NULL | 10000 | NULL |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)
从extra中我们没有发现使用排序的提示
2),直扫描表中所有行的，再进行排序，然后再输出limit限制的行数
mysql> explain select * from tt order by b limit 0,50000;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | tt | ALL | NULL | NULL | NULL | NULL | 9981864 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.01 sec)
可以看到现在是走全表扫描，然后通过排序来完成查询的需求。使用第一种算法还是第二种法，优化器根据cost来决定.
所以在某些时候limit的行数不同会引起执行计划的变化。
3,使用limit+主键进行分页优化
1),select * from tt limit 0,10000;
2),select * from tt limit 10000,10000
当有这种分页的查询的时候，对于第1个查询mysql会从btree的最左边开始扫描，计算10000行返回结果，而对于第二种查询
也需要从最左边开始扫描，计算10000,丢弃，然后从当前的位置再开始扫描，所以这也是使用limit进行分而后到后面的查询
会越来越慢的原因.
mysql> show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------+
| 1 | 0.01392800 | select * from tt limit 10000,10000 |
| 2 | 0.00930800 | select * from tt limit 0,10000 |
+----------+------------+------------------------------------+
因此会改成写where条件中加主键的形式.
select * from tt where a>10000 limit 0,10000
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 1 | 0.01392800 | select * from tt limit 10000,10000 |
| 2 | 0.00930800 | select * from tt limit 0,10000 |
| 3 | 0.01035200 | select * from tt where a>10000 limit 0,10000 |
+----------+------------+-----------------------------------------------+
4,如果使用的是limit 0,mysql会判断这种情况不可能发生，因此显示Impossible WHERE
mysql> explain extended select b,count(*) from tt group by b limit 0 ;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)