如题。自我总结。
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自身功能讨论。
另,建表时,占空间的二进制图片和文件要单独另放、常变更的字段要分表存储、索引怎样建立更好,这些对性能的影响更是必须慎重。