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要少;
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博客 ” ，链接：https://blog.itpub.net/26855487/viewspace-763406/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
