一般刚开始学MySQL的时候,针对小数据量可以这样写
SELECT * FROM table ORDER BY id LIMIT 1000000, 10;
但在数据量达到百万级的时候,上面这种写法会很慢,常见的优化方法是这样:
SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;
速度提升到0.x秒了,看样子还行了可是,还不是完美的!
如果能够利用BETWEEN 子句,以下才是完美的,速度可以提升5倍
SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;
【提示】
对于大型系统,要慎用那种连sql语句都看不到的框架!
示例
数据表 collect ( id, title ,info ,vtype) 有4个字段,
其中 title 用定长,info 用text, id 是主键,vtype是tinyint,vtype字段上有索引。
这是一个基本的新闻系统的简单模型。现在往里面填充数据,填充10万篇新闻。
最后collect 拥有10万条记录,数据库表占用硬盘1.6G。
看下面这条sql语句:
select id,title from collect limit 1000,10;
很快;基本上0.01秒就OK,
再看下面的
select id,title from collect limit 90000,10;
从9万条开始分页,结果?
8-9秒完成
看下面一条语句:
select id from collect order by id limit 90000,10;
很快,0.04秒就OK。
为什么?因为用了id主键做索引当然快,因此改法如下:
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
这就是用了id做索引的结果。
如果问题复杂那么一点点呢,看下面的语句
select id from collect where vtype=1 order by id limit 90000,10;
很慢,用了8-9秒!
到了这里我相信很多人会和我一样,有崩溃感觉!vtype 做了索引了啊?怎么会慢呢?
vtype做了索引是不错,你直接 select id from collect where vtype=1 limit 1000,10;
是很快的,基本上0.05秒,可是提高90倍,从9万开始,那就是0.05*90=4.5秒的速度了。
和测试结果8-9秒到了一个数量级。
从这里开始有人提出了分表的思路:
建一个索引表: t (id,title,vtype) 并设置成定长,然后做分页,分页出结果再到 collect 里面去找info 。
是否可行呢?实验下就知道了。
10万条记录到 t(id,title,vtype) 里,数据表大小20M左右。
用
select id from t where vtype=1 order by id limit 90000,10; 很快了。
基本上0.1-0.2秒可以跑完。
为什么会这样呢?我猜想是因为collect 数据太多,所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描,只是因为数据量小,只有10万才快。OK, 来个疯狂的实验,加到100万条,测试性能。
加了10倍的数据,马上t表就到了200多M,而且是定长。还是刚才的查询语句,时间是0.1-0.2秒完成!分表性能没问题?错!因为我们的limit还是9万,所以快。给个大的,90万开始
select id from t where vtype=1 order by id limit 900000,10; 看看结果,时间是1-2秒!
分表了时间还是这么长,非常之郁闷!有人说定长会提高limit的性能,开始我也以为,因为一条记录的长度是固定的,mysql 应该可以算出90万的位置才对啊? 可是我们高估了mysql 的智能,他不是商务数据库,事实证明定长和非定长对limit影响不大?
下面介绍非分表法-使用复合索引,来个疯狂的测试!
一张表搞定100万记录,并且10G 数据库,如何快速分页!
加了 search(vtype,id) 这样的索引。然后测试
select id from collect where vtype=1 limit 90000,10;
非常快!0.04秒完成!
再测试
select id ,title from collect where vtype=1 limit 90000,10;
非常遗憾,8-9秒,没走search索引!
再测试:search(id,vtype),还是select id 这个语句,也非常遗憾,0.5秒。
综上:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where放第一位,limit用到的主键放第2位,而且只能select主键!
分享到:
相关推荐
Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
基本上只要是做后台开发,都会接触到分页这个需求或者功能吧。基本上大家都是会用MySQL的LIMIT来处理,而且我现在负责的项目也是这样写的。但是一旦数据量起来了,其实LIMIT的效率会极其的低,这一篇文章就来讲一下...
MySQL Limit可以分段查询数据库数据,主要应用在分页上。虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能。下面记录一些limit性能优化方法。 Limit语法: ...
本人亲自用jsp的方式开发的一个完整的增删改查的小系统,遵循MVC架构,并且实现了模糊查询和用displaytag进行了分页,对输入垃圾字符进行了过滤处理,包含完整的代码注释,并且自带了Mysql的数据库脚本,很适合新手...
1.7MySQL的开发模式32 1.8总结33 第2章MySQL基准测试35 2.1为什么需要基准测试35 2.2基准测试的策略37 2.2.1测试何种指标38 2.3基准测试方法40 2.3.1设计和规划基准测试41 2.3.2基准测试应该运行多长时间42 ...
日常开发中,我们使用mysql来实现分页功能的时候,总是会用到mysql的limit语法.而怎么使用却很有讲究的,今天来总结一下. limit语法 limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据. ...
mysql分页是我们在开发经常遇到的一个功能,最近在实现该功能的时候遇到一个问题,所以这篇文章主要给大家介绍了关于mysql分页时offset过大的Sql优化经验,文中介绍的非常详细,对大家的学习或者工作具有一定的参考...
基于Servlet和jsp-的三层开发-会员管理系统--整理总结[2019-202002] 第四个版本-也就是最终完善版(vipPlusPro) 一、注册 登录 退出系统 工具类--初始化服务器数据库 注册 登录 忘记密码重置密码 拦截器 控制...
1 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN(MySQL 不支持)、CROSS JOIN 这是在网上找到的非常好的一篇博文,图解 join 语句: CODING HORROR-A Visual Explanation of SQL Joins 下图可以很清楚的明白,join ...
本书是MySQL数据库管理员和开发人员的必备参考书。 目录 -------------------------------------------------------------------------------- 第一部分 入门 第1章 什么是MySQL 1.1 什么是...
因为AspNetPager控件和数据是独立的,因此要分页的数据可以来自任何数据源,如SQL Server、Oracle、Access、mysql、DB2等数据库以及XML文件、内存数据或缓存中的数据、文件系统等等。 AspNetPager的主要功能有: 1...
开发软件: PHPStorm 数据库: mysql 程序后台技术框架:ThinkPHP5(一个MVC框架) 后台界面采用EasyUI框架,前台界面采用Bootstrap框架,用户浏览器和服务器全程几乎采用jquery异步加载技术! 别看他只是一个简单...
开发软件: VsCode或PHPStorm或DW等 数据库:mysql 程序后台技术框架:ThinkPHP5(一个MVC框架) 后台界面采用EasyUI框架,前台界面采用Bootstrap框架,用户浏览器和服务器全程几乎采用jquery异步加载技术! ThinkPHP5...
因为AspNetPager控件和数据是独立的,因此要分页的数据可以来自任何数据源,如SQL Server、Oracle、Access、mysql、DB2等数据库以及XML文件、内存数据或缓存中的数据、文件系统等等。 AspNetPager 7.2 版发布 新增...
使用的技术栈包括 L N M R R【linux nginx mysql redis ruby 】 等! 本书分为13大章节 - 第一章:环境准备工作 - 第二章:API 版本的管理 - 第三章:实战项目分析 - 第四章:用户模块的开发 - 模型相关开发 - ...
可以生成常用格式的文件,内部支持标签写入修复了一个后台分页的css遗漏的问题修复了菜单权限显示的bug优化了路径,模板等使用了相对路径重新优化了了tag和关键词提取,增加了模块的重用性优化了自定义标签的调用...