大家好,我是一个捡起蜗牛的小男孩。
当我们每天都需要每天的要求时,我们通常会以极限的极限实施,但是当偏移量特别大时,查询效率变得较低。本文将分为4个解决方案,以讨论如何优化MySQL百万数据的深页,并且附加最新优化SQL的实际战斗案例。
首先查看下面的表结构:
假设执行深页SQL如下:
该SQL的执行时间如下:
执行需要0.742秒,为什么深点页较慢?如果更换,则只需0.006秒
让我们看一下此SQL的执行过程:
SQL执行过程
执行计划如下:
SQL慢的原因有两个:
因为上述SQL,已返回100010次。实际上,我们只需要10个数据,也就是说,我们只需要偿还10次。因此,我们可以通过减少退货数来进行优化。
那么,如何减少回报次数?让我们首先回顾B+树索引结构?
在InnoDB中,索引子键索引(集群索引)和辅助索引
如果我们转移查询条件并将其转移回主键索引树,则不会减少申报表的数量。如果您转移到主键索引树查询,则必须更改查询条件。在这些条件下,我该怎么办?
孩子如何查询?因为第二级索引叶节点具有主密钥ID,因此我们可以根据主键的条件直接检查主密钥ID。
查询效果是相同的,只有0.038秒的执行时间!
让我们看一下执行计划
从执行计划中学到的查询表查询使用索引。首先,我在索引上获得了索引索引的主要密钥ID,消除了恢复操作,然后直接检查了第一个查询10查询ID!
因此,此解决方案还可以?
延迟关联的优化与子查询的优化相同:条件转移到主密钥索引树,然后减少回报表。区别在于,延迟关联使用内部连接而不是内部。子 - Query。
优化的SQL如下:
查询效果也是杠杆,只有0.034秒
执行计划如下:
查询想法是查询通过第二级索引树满足条件的主要密钥ID,然后通过主密钥ID连接到原始表。这样,主键索引被直接采用,并减少了返回表。
深度分页问题的基本原因是:偏移量越大,MySQL扫描越多,然后放弃它。这导致查询性能下降。
实际上,我们可以使用标签录制方法,也就是说,要标记最后一次,然后在下次进行检查,从文章开始并扫描。它就像读书一样。无论您在哪里看到,都可以折叠或剪辑书签。下次查看它时,您将直接转动。
假设最后一个记录为100,000,可以修改SQL:
在这种情况下,无论打开了多少页,性能都会好,因为索引受到打击。但是您,此方法是有限的:需要连续的自我提示领域。
很多时候,查询可以转换为已知位置,以便MySQL可以通过扫描获得相应的结果。
如果您知道边界值为100,000、100010,则可以以这种方式优化:
让我们看一个实际情况。Puspose有一个表如下,并且有200万个数据。
业务需求是:在2021年获取最多的类型帐户数据并将其报告到大数据平台。
许多合作伙伴收到了这样的需求,并将直接意识到:
上面的实施方案将存在限制深度分页问题,因为帐户表的数据数据量是数百万。如何优化?
实际上,您可以使用标签记录方法。一些合作伙伴可能有疑问。ID的主要密钥不是连续的。您真的可以使用标签记录吗?
当然,ID不是连续的,我们可以使其连续。优化方案如下:
//查询最低ID
字符串lastId = accountdao.queryminid();
//查询与最大ID相对应的SQL
选择最小(ID)
从帐户
其中create_time> ='2021-01-01 00:00:00'
和type ='a'
//一页号
整数pageize = 100;
列表
做 {
list = listAccountBypage(lastId,pageize);
//标签记录方法,上次记录ID I查询
lastId = list.get(list,size()-1).getID();
//报告大数据
Postbigdata(列表);
} while(collectionutils.isnotempty(list));
选择 *
从帐户
其中create_time> ='2021-01-01 00:00:00'
和id>#{lastId}
和type ='a'
通过ID ASC订购
限制#{pagesize}