Mysql limit 分页用法及优化

 

 如题。自我总结。

1、limit用法格式

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

　LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 

SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

如果只给定一个参数，它表示返回最大的记录行数目：

 SELECT * FROM table LIMIT 5; //检索前 5 个记录行

　换句话说，LIMIT n 等价于 LIMIT 0,n。

2、limit分页优化。当数据较多之后，查询分页将会变得很慢。影响性能。

例：数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是主键，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。 

最后collect 为 10万条记录，数据库表占用硬盘1.6G。OK ,看下面这条sql语句： 

1）select id,title from collect limit 1000,10; 很快；基本上0.01秒就OK，再看下面的 

2）select id,title from collect limit 90000,10; 从9万条开始分页，结果，8-9秒完成。当数据多时，查询将变慢。(此时，只用的id索引，且由于title数据量大，影响速度)

            可改为select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;这样，先在9万条时只针对id，然后再查询10条id和title数据就ok。

           其实，对于很长的二进制文本，是不放在数据库里的，另放文件里，减少数据库负荷。

3）select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了8-9秒。虽然vtype用了索引，但数据一多，mysql就慢。

       这时，最好的办法就是建立复合索引vtype_id（vtype，id），注意前缀索引顺序，最前缀一定要是where子句后面的字段。这样，只有0.04秒完成。

 

综上，limit分页并不是万能，当数据量大的时候并不都是速度一样。

当然，大多数时间，我们在建表之后都会建立索引，以上仅从limit自身功能讨论。

另，建表时，占空间的二进制图片和文件要单独另放、常变更的字段要分表存储、索引怎样建立更好，这些对性能的影响更是必须慎重。