mysql事务和深度分页问题

最新推荐文章于 2025-06-03 19:48:34 发布

yeye0001

最新推荐文章于 2025-06-03 19:48:34 发布

阅读量203

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_58141314/article/details/133648567

版权

Mysql 专栏收录该内容

5 篇文章

订阅专栏

事务

● 声明式事务
声明式事务是基于AOP面向切面，它将具体业务和事务处理部分解耦，代码侵入性很低，实际开发中比较常用。我们常用TX和AOP的xml配置文件方式和@Transactional注解方式。
▪声明式事务的优点：
对代码无侵入性，方法内只需要写业务逻辑，节省很多代码量。
▪声明式事务的缺点：
1、声明式事务粒度问题：声明式事务的局限就是最小粒度要作用在方法上，且不适合耗时长、高并发场景。
2、声明式事务容易被开发者忽略，当事务嵌套的方法中存在RPC远程调用、MQ发送、Redis更行、文件写入等操作可能存在以下场景：
▪ 事务嵌套的方法中RPC调用成功了，但是本地事务回滚导致RPC调用无法回滚（暂不讨论分布式事务）。
▪事务嵌套的方法中远程调用会拉长整个事务周期，导致事务的数据库连接一致被占用，类似操作过多会导致数据库连接池耗尽。
3、声明式事务使用错误会导致在某些场景下失效。

分页

标签记录法

Limit深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉，这样就导致查询性能的下降。所以我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。具体做法方式是，查询粉丝列表中按照自增主键ID倒序查询，查询结果中返回主键ID，然后查询入参中增加maxId参数，该参数需要透传上一次请求粉丝列表中最后一条记录主键ID，第一次查询时可以为空，但是需要查询下一页时就必传。最后根据查询时返回的行数是否等于 10 来判断整个查询是否可以结束。优化后的查询sql参考如下：
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} order by id desc limit 10;

● 方案优点：避免了数据量变大时，页码查询深入的性能下降问题；经过接口压测，千万级数据量时，前 N-1页查询耗时可以控制在几十毫秒内。
● 方案缺点：只能支持按照页码顺序查询，不支持跳页，而且仅能保证前 N-1 页的查询性能；如果最后一页的表中行数量不满 10 条时，引擎不知道何时终止查询，只能遍历全表，所以当表中数据量很大时，还是会出现超时情况。

区间限制法

标签记录法最后一页查询超时就是因为不知道何时终止查询，所以我们可以提供一个区间限制范围来告诉引擎查询到此结束。
查询sql再次优化后参考如下：
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10;

由于查询时需要带上 minId 参数，所以在执行查询粉丝列表之前，我们就需要先把 minId 查询出来，查询 sql 参考如下：
select min(id) from follow_fans_1 where biz_content = #{bizContent}
由于表中数据量太大，每个表中总数据量都是上亿级别，导致第一步查询 minId就直接超时了，根本没有机会去执行第二步。但是考虑到上一个查询方案只有最后一页才会查询超时，前N-1页查询根本用不到 minId 作为区间限制。所以当表中数据量很大时，通常从第一页到最后一页查询之间会存在一定的时间差。我们就可以正好去利用这个时间差去异步查询minId，然后将查询出来的minId存储到缓存中，考虑到这个 minId 可能会被删除，可以设置一定的过期时间。最后优化后的查询流程如下：

调用查询粉丝列表方法时首先查询缓存minId；
如果缓存minId 为空，则创建异步任务去执行select min(id) 查询表中的 minId,然后回写缓存，该异步任务执行时间可能会很长，可以单独设置超时时间。
如果缓存minId不为空，则在查询sql中拼接查询条件id >={minId}，从而保证查询最后一页时不会超时。
但是在上述方案中，如果表中的数据量达到上亿级别时，第二步的异步获取minId任务还是会存在超时的风险，从而导致查询最后一页粉丝列表出现超时。所以我们又引入了离线数据计算任务，通过在大数据平台离线计算获取每个biz_content下的minId,然后将计算结果minId推送到缓存中。为了保证minId能够及时更新，我们可以自由设置该离线任务的执行周期，比如每周执行一次。通过大数据平台的离线计算minId，从而大大减少了在查询粉丝列表时执行 select min(id）的业务数据库压力。只有当缓存没有命中的时候才去执行 select min(id)，通常这些缓存没有命中的 minId 也都是一些被离线任务遗漏的少量数据，不会影响接口的整体查询性能。
● 方案优点：避免了数据量变大时，页码查询深入的性能下降问题；经过接口压测，千万级数据量时，从第一页到最后一页都控制在几十毫秒内。
● 方案缺点：只能支持按照页码顺序和主键ID倒序查询，不支持跳页查询，并且还需要依赖大数据平台离线计算和额外的缓存来存储 minId。

正确使用索引，防止索引失效

可以参考以下几点索引原则：

最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a=1 and b=2 and c>3 and d=4 ，如果建立了（a,b,c,d)顺序的索引，d是用不到索引的，如果建立（a,b,d,c)的索引则都可以用到，a、b、d的顺序可以任意调整。
=和in可以乱序，比如 a=1 and b=2 and c=3 建立（a,b,c)索引可以任意顺序，mysql的查询优化器会帮助优化成索引可以识别的形式。
尽量选择区分度高德列作为索引，区分度公式count(distinct col)/count(*)，表示字段不重复的比例。
索引列不能使用函数或参与计算，不能进行类型转换，否则索引会失效。
尽量扩展索引，不要新建索引。
减少查询字段，避免回表查询
回表查询就是先定位主键值，在根据主键值定位行记录，需要扫描两遍索引。解决方案：只需要在一颗索引树上能够获取SQL所需要的所有列数据，则无需回表查询，速度更快。可以将要查询的字段，建立到联合索引里去，这就是索引覆盖。查询sql在进行explain解析时，Extra字段为Using Index时，则触发索引覆盖。没有触发索引覆盖，发生了回表查询时，Extra字段为Using Index condition。

如果这篇【文章】有帮助到你，希望可以给笔者点个赞👍，创作不易，感兴趣的也可以关注一下笔者，后续也会更新更多的内容，都会在后续慢慢更新。