这是学习笔记的第 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的执行路径类似于下面的形式:

SQL优化案例一则_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秒,相比之前的方式快了许多。

SQL优化案例一则_SQL优化案例一则_02