上亿级的表-TiDB列表查询优化-CSDN博客

本文链接：https://blog.csdn.net/chenhua141/article/details/141687973

效果

优化之前，查询近一个月接口响应时间:20秒，优化之后，接口响应时间:3-4秒

背景

运维监控TiBD数据库运行情况，发现某些时段CPU过高，然后查看慢SQL日志，把慢SQL反馈给开发人员。SQL的业务背景，分页查询订单，并把符合查询条件的金额等数值进行统计

问题分析

1、该接口主要是分页查询+业务统计查询，分页查询用了插件，其中插件中的count()查询可在业务统计查询中所得
2、分页SQL查询慢，尝试能否优化

优化方案

1、取消分页插件，手动分页
2、从统计SQL中获取总条数(分页所需的数据)，减少一次SQL查询
3、改造分页SQL，先根据查询条件得到id集合，再根据id集合查询最终所需的数据

SQL优化

优化前的SQL(提供参考)

分页

SELECT o.id                              AS id,
       o.order_id                        AS orderId,
       o.user_id                         AS userId,
       o.merchant_id                     AS merchantId,
       o.amount                          AS amount
FROM order o
<include refid="order_where_inside"/>
ORDER BY po.create_time DESC
LIMIT #{dto.limitBegin}, #{dto.pageSize}

统计

SELECT
/*+ read_from_storage(tiflash[o]) */
o.merchant_id AS merchantId,
sum(o.amount) AS amount
FROM
order o
<include refid="order_where_inside"/>
GROUP BY merchant_id

优化后的SQL(提供参考)

分页

SELECT o.id
FROM order o
<include refid="order_where_inside"/>
ORDER BY o.create_time DESC
LIMIT #{dto.limitBegin}, #{dto.pageSize}
		
SELECT o.id                              AS id,
       o.order_id                        AS orderId,
       o.user_id                         AS userId,
       o.merchant_id                     AS merchantId,
       o.amount                          AS amount
FROM order o
WHERE o.id IN
<foreach collection="ids" item="item" open="(" close=")" separator=",">
    #{item}
</foreach>
ORDER BY o.create_time DESC

统计

SELECT
/*+ read_from_storage(tiflash[o]) */
o.merchant_id AS merchantId,
sum(o.amount) AS amount,
...
-- 分页所用的count
count(o.id) AS totalOrder
FROM
order o
<include refid="order_where_inside"/>
GROUP BY merchant_id