oracle中limit可以走索引吗,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)