一、背景

业务背景：给C端10万级别的用户，同时发送活动消息，活动消息分为6类。
数据背景：mysql表有百万级别的数据量。
问题：每次发活动消息时，数据库占用过高

在这里插入图片描述

二、原因

在这里插入图片描述
看sql日志记录，明显看出有两个问题：

1 深分页
2 查询条件执行了两次

explain下
在这里插入图片描述
type类型为 ref

三、解决

原sql

select * from channel_subscribe_relation
where external_channel=9 
and template_id = "rf2624--esUKD3O5B1Qxfe15qbfAQugk-JpFVUf55BU"
and subscribe_status=1
order by id ASC
LIMIT 50000,10

优化sql

SELECT * FROM channel_subscribe_relation
WHERE external_channel = 9 AND template_id = "dafdjlfanfdaln"AND subscribe_status = 1AND id > [Last_Page_Last_Id]
ORDER BY id ASC
LIMIT 10;

测试结果：

原sql: 平均 0.1s级别
优化sql：平均0.004 s

SELECT * FROM channel_subscribe_relation
WHERE external_channel = 9 AND template_id = "rNdk87qpBKQstZpx4hL0u1-kMCrF3phit-ySUOOt_8I"AND subscribe_status = 1AND id > 244567
ORDER BY id ASC
LIMIT 10;

在这里插入图片描述综上所述，使用主键id索引替换分页查询，查询性能：缩短了近 25 倍。

但是，问题并没有解决！因为代码是多线程去查库的，我并不知道上次上传的最后一页的最后一个主键id是多少！

所以，只能另辟蹊径。

经过百度，还可以通过通过子查询优化

1 把条件转移到主键索引树
2- NNER JOIN 延迟关联

SELECT *
FROM channel_subscribe_relation
WHERE id >= (
SELECT c.id
FROM channel_subscribe_relation c
WHERE c.external_channel = 9
AND c.template_id = “rf2624–esUKD3O5B1Qxfe15qbfAQugk-JpFVUf55BU”
AND c.subscribe_status = 1
ORDER BY c.id ASC
LIMIT 50000, 1
)
AND external_channel = 9
AND template_id = “rf2624–esUKD3O5B1Qxfe15qbfAQugk-JpFVUf55BU”
AND subscribe_status = 1
ORDER BY id ASC
limit 10

0.05s

进一步优化：

SELECT * FROM channel_subscribe_relation
WHERE id IN (
SELECT id
FROM (
SELECT c.id
FROM channel_subscribe_relation c
WHERE c.external_channel = 9
AND c.template_id = “rf2624–esUKD3O5B1Qxfe15qbfAQugk-JpFVUf55BU”
AND c.subscribe_status = 1
ORDER BY c.id ASC
LIMIT 50000, 10
) AS t
);