MySQL开发之分页优化

标签: mysql 开发 分页 | 发表时间:2018-12-20 22:27 | 作者:loris_jand
出处:https://www.iteye.com
一般刚开始学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主键!




已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql 开发 分页] 推荐:

MySQL开发之分页优化

- - 数据库 - ITeye博客
一般刚开始学MySQL的时候,针对小数据量可以这样写. 但在数据量达到百万级的时候,上面这种写法会很慢,常见的优化方法是这样:. 速度提升到0.x秒了,看样子还行了可是,还不是完美的. 如果能够利用BETWEEN 子句,以下才是完美的,速度可以提升5倍. 对于大型系统,要慎用那种连sql语句都看不到的框架.

MySQL 优化Limit分页

- - CSDN博客数据库推荐文章
     很多时候、我们需要选择出从指定位置开始的指定行数、此时、limit笑了.      对于limit的定义是:.      表示从第x行开始选择y条记录.      在业务需要分页操作的时候、我们通常采用limit+order by这对洗剪吹组合、高端洋气上档次.      然而、当翻到非常靠后的页面时、MySQL需要花费大量的时间来扫描需要丢弃的数据.

oracle、mysql和sqlserver分页

- - Oracle - 数据库 - ITeye博客
sql server row number分页:. mysql limit分页:. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

【转】【MySql】赶集网mysql开发36条军规

- - 数据库 - ITeye博客
   cpu计算务必移至业务层;.    int型不超过1000w,含char则不超过500w;.    限制单库表数量在300以内;.    字段少而精,字段数建议在20以内;.    拒绝大sql语句:big sql.    拒绝大事物:big transaction.    拒绝大批量:big batch.

互联网 MySQL 开发规范

- - leejun_2005的个人页面
对于刚加入互联网的朋友们,肯定会接触到MySQL,MySQL作为互联网最流行的关系型数据库产品,它有它擅长的地方,也有它不足的短板,针对它的特性,结合互联网大多应用的特点,笔者根据自己多年互联网公司的MySQL DBA经验,现总结出互联网MySQL的一些开发规范,仅供参考. 作者是微信订阅号yunweibang特约技术专家刘秋岐,多年数据库经验,如有问题可以订阅yunweibang并留言.

[MySQL优化案例]系列 — 分页优化

- - 学习笔记
通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询. 或者像下面这个不带任何条件的分页SQL:. 一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行耗时:. 可以看到,随着分页数量的增加,SQL查询耗时也有数十倍增加,显然不科学.

关于mysql大数据分页的一些方法。

- - CSDN博客编程语言推荐文章
select * from user  limit 0,10;   这种最普通的方法在数据量不大的时候是没问题的. 当数据量大于100W的时候 ,就要 select * from user limit 1000000,10 ;  此时数据库. 要先扫过前面的100W条记录,再来取10条,所以当数据量越来越大的时候,速度也会越来越慢.

MYSQL分页limit速度太慢优化方法

- - OurMySQL
   在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦.    当一个表数据有几百万的数据的时候成了问题.    如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决.

MySQL数据库开发的赶集网三十六条军规

- sunseesiu - MySQLOPS 数据库与运维自动化技术分享
MySQL数据库开发的三十六条军规 View more presentations from mysqlops 原创文章,转载请注明: 转载自MySQLOPS 数据库与运维自动化技术分享 本文链接地址: MySQL数据库开发的赶集网三十六条军规.

使用Hibernate + MYSQL数据库开发,链接超时问题:

- - CSDN博客Web前端推荐文章
使用Hibernate + MYSQL数据库开发,链接超时问题:. 查了一下,原来是mysql超时设置的问题. 如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat. . 如果不用hibernate的话, 则在 connection url中加参数: autoReconnect=true.