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要少;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26855487/viewspace-763406/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26855487/viewspace-763406/