{eval=Array;=+count(Array);}

问答专栏Q & A COLUMN

为什么MySQL在数据库较大的时候分页查询很慢,如何优化?

王晗王晗 回答0 收藏1
问题描述:比如,select * from table order by age limit 300000,300020。
收藏问题

5条回答

CntChen

CntChen

回答于2022-06-28 14:26

使用合理的分页方式以提高分页的效率


正如楼主所说,分页查询在我们的实际应用中非常普遍,也是最容易出问题的查询场景。比如对于下面简单的语句,一般想到的办法是在name,age,register_time字段上创建复合索引。这样条件排序都能有效的利用到索引,性能迅速提升。

如上例子,当 LIMIT 子句变成 “LIMIT 100000, 50” 时,此时我们会发现,只取50条语句为何会变慢?


原因很简单,MySQL并不知道第 100000条记录从什么地方开始,即使有索引也需要从头计算一次,因此会感觉非常的慢。

通常,我们在做分页查询时,是可以获取上一页中的某个数据标志来缩小查询范围的,比如时间,可以将上一页的最大值时间作为查询条件的一部分,SQL可以优化为这样:


若对你有所帮助,欢迎点赞、关注支持哦。

评论0 赞同0
  •  加载中...
Jackwoo

Jackwoo

回答于2022-06-28 14:26

题主给的这个sql其实想要的数据也就20条吧(你那个300020应该是打错了,不可能是实际业务一页显示30多万条记录),单纯查三十多万数据其实很快,为什么分页后就很慢?

变慢的原因,一方面是select *,另一方面是数据量较大,还有一个是带有排序操作。本质是分页查询时,会先查询出limit + offset条记录,然后截取后面的offset记录。

Mysql数据库作为一款比较主流的开源关系型数据库,市场上我觉得貌似开发者没有一个没用过吧。

影响MySQL查询性能的因素有很多,比如sql,表结构设计,磁盘io,网卡io,高并发,数据库相关参数配置,还有服务器硬等。

这里面涉及最多也是面试中最常问的就是有关sql的优化。

因为很多性能上的问题来自sql的比较多,mysql数据库在数据量级达到百万以上性能是逐渐下降的。

关于sql优化又有很多优化的方向和手段。比如对表结构的字段类型,默认值,索引等最基础的做一些优化,然后编写的sql最好要能完全命中索引。

当然并不是说建索引就一定命中,不走索引就一定慢。这取决于mysql的执行计划。

还有建索引也并不是越多越好,单表索引最好不要超过6个,毕竟索引也占空间,数据更新的同时,还牵扯到索引文件的维护。

OK说了这么多,到底该怎么对这个分页又排序做优化呢?

我的做法就是合理利用主键索引来处理

select a.* from table a inner join (select id from table

limit 300000,20)

b on a.id=b.id;

然后排序最好放到代码层面上去。

希望我的回答能帮到你

评论0 赞同0
  •  加载中...
darcrand

darcrand

回答于2022-06-28 14:26

问题

我们有一个 SQL,用于找到没有主键 / 唯一键的表,但是在 MySQL 5.7 上运行特别慢,怎么办?

实验

我们搭建一个 MySQL 5.7 的环境,此处省略搭建步骤。

写个简单的脚本,制造一批带主键和不带主键的表:

执行一下脚本:

现在执行以下 SQL 看看效果:

...



执行了 16.80s,感觉是非常慢了。

现在用一下 DBA 三板斧,看看执行计划:



感觉有点惨,由于 information_schema.columns 是元数据表,没有必要的统计信息。

那我们来 show warnings 看看 MySQL 改写后的 SQL:

我们格式化一下 SQL:



可以看到 MySQL 将

select from A where A.x not in (select x from B) //非关联子查询

转换成了

select from A where not exists (select 1 from B where B.x = a.x) //关联子查询

如果我们自己是 MySQL,在执行非关联子查询时,可以使用很简单的策略:

select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录,找到满足条件的记录,存放在临时表 C 中,建好索引2. 扫描 A 表中的记录,与临时表 C 中的记录进行比对,直接在索引里比对,

而关联子查询就需要循环迭代:

select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA: 扫描 B 表,找到其中的第一条满足 rA 条件的记录。

显然,关联子查询的扫描成本会高于非关联子查询。

我们希望 MySQL 能先"缓存"子查询的结果(缓存这一步叫物化,MATERIALIZATION),但MySQL 认为不缓存更快,我们就需要给予 MySQL 一定指导。



...

可以看到执行时间变成了 0.67s。

整理

我们诊断的关键点如下:

1. 对于 information_schema 中的元数据表,执行计划不能提供有效信息。

2. 通过查看 MySQL 改写后的 SQL,我们猜测了优化器发生了误判。

3. 我们增加了 hint,指导 MySQL 正确进行优化判断。

但目前我们的实验仅限于猜测,猜中了万事大吉,猜不中就无法做出好的诊断。

评论0 赞同0
  •  加载中...
233jl

233jl

回答于2022-06-28 14:26

个人实战经验分享一下,商品表,数据量还是相对较大的,有好几百万。当时最初架构也是遇到这种问题,因为最初设计的时候没想到会有这么大数据量,也就应对10万以内的架构设计。那后来也是通过不断找寻方案,最终采用了一种桥连接表的方案。主表是商品表,几百万或者上千万商品。

第一步,建立桥数据表,一个自增ID,一个商品ID,主要这俩字段,额外排序条件也可以加进去,均int类型,不宜过大。这个表就是存商品ID用的。

第二步,查询分页的时候,先在这个桥表做分页查询,表小,都是索引,速度非常快,然后取出商品ID后,再用已知商品ID对商品表做in查询。查出具体信息。这样速度提升巨大。简单实用,仅需对分页部分做查询修改即可完成。

目前三五百万商品表,都是毫秒级查询,没改造之前需要半分钟。

再说一句是MySQL数据库,有的说什么分表分库,那都没必要,用in条件查询,那是最快的,直接告诉在哪,取数就行了。


评论0 赞同0
  •  加载中...
vvpale

vvpale

回答于2022-06-28 14:26

这个得明白mysql的存储原理 讲个例子 一个小区有很多栋 一栋有很多层 一层有很多室 你哪个小区哪一栋那一层都不告诉它 它能一下找到吗 虽然它也想快点帮你找到

评论0 赞同0
  •  加载中...

最新活动

您已邀请0人回答 查看邀请

我的邀请列表

  • 擅长该话题
  • 回答过该话题
  • 我关注的人
向帮助了您的网友说句感谢的话吧!
付费偷看金额在0.1-10元之间
<