limit实现原理 mysql_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 |