这是学习笔记的第 1978 篇文章
今天优化了几个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是最全面的信息,后续的信息都是以它作为基础,而输出结果是只有50行。
所以从优化上来说,既然优化器看不到这个边界,我们可以间接告诉它。
即把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秒,相比之前的方式快了许多。