mysql limit 优化_mysql limit语句优化

形如下表:

CREATE TABLE `tbl` (

`id` int(10) unsigned NOT NULL,

`v_state` tinyint(1) NOT NULL DEFAULT '1',

`log_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

初始化20w条数据;mysql> explain SELECT SQL_NO_CACHE * from tbl ORDER BY id ASC LIMIT 99999,20 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99999

Extra: Using filesort

1 row in set (0.00 sec)

ERROR:

No query specified

从上面看没有用到索引，扫描了99999行，且还用到了排序;

对应转换sql如下：

mysql>explain

select *

from tbl

where id>=(

select SQL_NO_CACHE id from tbl order by id asc limit 99999,1)limit 20 \G;

相对而言，执行消耗时间比直接limit要少;