MySQL使用limit优化

                             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)