Mysql之limit优化

1 limit的工作原理

limit的使用方式比较简单select * from table order by id limit m,n ,其工作原理就是，服务器从存储引擎取出m+n条数据，然后丢弃掉m条数据，只保留最后的n条。

显然，当m比较大的时候，如此使用limit会造成巨大的浪费（无效的数据传输）

2 limit的正确打开方式

了解了它的工作原理之后，我们就可以采用扬长避短的方式来使用它，当m比较小的时候，如何使用都不成问题；但是当m比较大的时候，我们就应该考虑性能问题了。

解决思路有两种：

就是改写sql，降低m的值。
降低无效的m的数据传输量，如至传id而不是全部字段。

常用的方法有两种：

2.1 采用更精确的查询条件，降低m的值

如我们可以通过某些标记字段，如id来代替m，将sql改写为:

select * from table where id>m order by id limit n

2.2 采用内连接的方式，降低数据传输量

select * from table inner join (select id from table order by id limit m,n ) as b using(id);

3 案例验证

3.1 案例命令

mysql> show create table testdb.user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(60) NOT NULL DEFAULT '',
  `lastname` varchar(60) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `province` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fisrt_last_idx` (`firstname`,`lastname`),
  KEY `age_idx` (`age`),
  KEY `p` (`province`),
  CONSTRAINT `fk_p` FOREIGN KEY (`province`) REFERENCES `province` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

mysql> select count(1) from testdb.user;
+----------+
| count(1) |
+----------+
|   100013 |
+----------+
1 row in set (0.02 sec)

mysql> select * from testdb.user limit 100000,1;
+--------+-----------+----------+-----+----------+
| id     | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack      | 9000     |  40 |        2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.05 sec)

mysql> select * from testdb.user where id > 100000 limit 1;
+--------+-----------+----------+-----+----------+
| id     | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack      | 9000     |  40 |        2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.00 sec)

mysql> select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+-------+-----------+----------+-----+----------+
| id    | firstname | lastname | age | province |
+-------+-----------+----------+-----+----------+
| 99460 | Jack      | 4655     |  49 |        2 |
+-------+-----------+----------+-----+----------+
1 row in set (0.03 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------+
|        1 | 0.02759500 | select count(1) from testdb.user                                                                |
|        2 | 0.05340900 | select * from testdb.user limit 100000,1                                                        |
|        3 | 0.00030100 | select * from testdb.user where id > 100000 limit 1                                             |
|        4 | 0.02979400 | select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id) |
+----------+------------+-------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000045 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000016 |
| init                 | 0.000016 |
| System lock          | 0.000008 |
| optimizing           | 0.000004 |
| statistics           | 0.000019 |
| preparing            | 0.000010 |
| executing            | 0.000002 |
| Sending data         | 0.053204 |
| end                  | 0.000010 |
| query end            | 0.000004 |
| closing tables       | 0.000008 |
| freeing items        | 0.000017 |
| logging slow query   | 0.000026 |
| cleaning up          | 0.000014 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000016 |
| init                 | 0.000022 |
| System lock          | 0.000007 |
| optimizing           | 0.000008 |
| statistics           | 0.000056 |
| preparing            | 0.000012 |
| executing            | 0.000002 |
| Sending data         | 0.000035 |
| end                  | 0.000004 |
| query end            | 0.000006 |
| closing tables       | 0.000007 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000039 |
| cleaning up          | 0.000012 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000078 |
| checking permissions | 0.000003 |
| checking permissions | 0.000005 |
| Opening tables       | 0.003272 |
| init                 | 0.000433 |
| System lock          | 0.000017 |
| optimizing           | 0.000005 |
| optimizing           | 0.000004 |
| statistics           | 0.000016 |
| preparing            | 0.000130 |
| statistics           | 0.000136 |
| preparing            | 0.000015 |
| executing            | 0.000011 |
| Sending data         | 0.000016 |
| executing            | 0.000003 |
| Sending data         | 0.025462 |
| end                  | 0.000011 |
| query end            | 0.000006 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.000007 |
| closing tables       | 0.000008 |
| freeing items        | 0.000073 |
| logging slow query   | 0.000063 |
| cleaning up          | 0.000018 |
+----------------------+----------+
24 rows in set, 1 warning (0.00 sec)

mysql> explain select * from testdb.user limit 100000,1;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 99933 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

mysql> explain select * from testdb.user where id > 100000 limit 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL |   13 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL | 99933 | NULL        |
|  1 | PRIMARY     | user       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |     1 | NULL        |
|  2 | DERIVED     | user       | index  | NULL          | age_idx | 4       | NULL | 99933 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
3 rows in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.user limit 100000,1;
+--------+-----------+----------+-----+----------+
| id     | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack      | 9000     |  40 |        2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.04 sec)

mysql> show status like '%handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 2      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 1      |
| Handler_read_key           | 1      |
| Handler_read_last          | 0      |
| Handler_read_next          | 0      |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 100001 |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
18 rows in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.user where id > 100000 limit 1;
+--------+-----------+----------+-----+----------+
| id     | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack      | 9000     |  40 |        2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.00 sec)

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+-------+-----------+----------+-----+----------+
| id    | firstname | lastname | age | province |
+-------+-----------+----------+-----+----------+
| 99460 | Jack      | 4655     |  49 |        2 |
+-------+-----------+----------+-----+----------+
1 row in set (0.03 sec)

mysql> show status like '%handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 4      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 1      |
| Handler_read_key           | 2      |
| Handler_read_last          | 0      |
| Handler_read_next          | 100000 |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 2      |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 1      |
+----------------------------+--------+
18 rows in set (0.00 sec)

3.2 案例分析

通过案例，可以看到：
采用方式1改写的sql由于减少了从存储引擎层至服务器层的数据传输条数（从10001减少到1），间接减少了数据传输量，最后查询时间大为降低（0.05秒减少至0.00秒）；
采用方式2改写的sql，虽然没有减少数据传输条数（依然是10000条），但每条的数据量大为减少（从*到id），所以也减少了数据传输量，最后查询时间也大为降低（0.05秒减少至0.03秒）；

最后编辑于：2017.12.06 22:20:07

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,444评论 6赞 496
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,421评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,036评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,363评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,460评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,502评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,511评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,280评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,736评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,014评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,190评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,848评论 5赞 338
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,531评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,159评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,411评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,067评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,078评论 2赞 352