SQL优化案例一则 _SQL

这是学习笔记的第 1978 篇文章
今天优化了几个SQL问题，拿出来两个做下总结和分享。
【SQL优化案例一则】第一条SQL如下，内容做了删减。
SELECT p.*, m.uid, m.username, m.groupid, ....m.email, m.gender, m.showemail, m.invisible
FROM cdb_posts p
LEFT JOIN cdb_members m ON m.uid=p.authorid
LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid
WHERE p.tid='xxxxx' AND p.invisible='0' ORDER BY first DESC,dateline DESC LIMIT 13250, 50
这条语句的执行效率根据监控，平均时间在9秒，但是在测试的时候，时间执行时间远远大于9秒，我们就暂且按照9秒来估算时间成本吧。
cdb_posts表的数据有3000多万，另外两个表cdb_members,cdb_memberfields的数据量也不小，量级在七百万。
其中索引分布在如下的字段中：

索引字段：cdb_posts.authorid,tid 数据量：3000多万
索引字段：cdb_members.uid 数据量：700多万
索引字段：cdb_memberfields.uid 数据量：3000多万

对于这样一个SQL，按照目前的执行情况，基于LEFT JOIN，肯定是有一个表要“全量”了。
所以整个SQL的关注目标先在于where子句：
p.tid='xxxxx' AND p.invisible='0'
根据测试，这个数据量也相对小一些：
>>SELECT count(*)
-> FROM cdb_posts p
-> LEFT JOIN discuz.cdb_members m ON m.uid=p.authorid
-> WHERE p.tid='6297759' AND p.invisible='0' ;
+----------+
| count(*) |
+----------+
| 29625 |
+----------+
1 row in set (7.27 sec)
所以我们后续的测试会以这个数据作为基础，执行计划如下：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: displayorder,idx_tid_fir_authorid,idx_invisible
key: displayorder
key_len: 4
ref: const,const
rows: 59148
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.p.authorid
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: mf
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.m.uid
rows: 1
Extra:
3 rows in set (0.00 sec)
从执行计划来看，瓶颈点就在于第1部分了，整个SQL的执行路径类似于下面的形式：

文章插图

对于这个部分的评估，主要是做了索引的评估，发现改进力度很有限，所以我的注意力放在了逻辑部分，其中cdb_posts是最全面的信息，后续的信息都是以它a来说，既然优化器看不到这个边界，我们可以间接告诉它。
即把cdb_posts缩小为一个派生表：
select * from cdb_posts where tid='xxxx' AND invisible='0' LIMIT 11625, 50
这样的话数据量是绝对可控，而且符合逻辑的。
改造后的语句如下：
SELECT SQL_NO_CACHE p.*, m.uid, m.username,。。。m.email, m.gender, m.showemail, m.invisible 。。。
FROM (
select * from cdb_posts where tid='xxxx' AND invisible='0' LIMIT 11625, 50
)p
LEFT JOIN cdb_members m ON m.uid=p.authorid
LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid
ORDER BY dateline DESC,first asc;
改造后，执行时间为0.14秒，相比之前的方式快了许多。