SQL优化案例一则

这是学习笔记的第 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是最全面的信息，后续的信息都是以它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秒，相比之前的方式快了许多。