MySQL——sql优化（二）

一点记录：

offset优化：

原始语句：

mysql> explain SELECT rid, qid, status, source, deleted, uid, toUid, uname, content, misFlag, createTime, uip, opTime, opUid, likeCnt, opName, auditSt, applyTime, ext, likeCnt FROM tblArticleReply1 WHERE (qid = 2819559) ORDER BY rid ASC LIMIT 41 OFFSET 24920\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tblArticleReply1
         type: ref
possible_keys: qid,qid_likecnt
          key: qid
      key_len: 4
          ref: const
         rows: 52188
        Extra: Using where
1 row in set (0.00 sec)

测试环境中执行9s时间。。数据量大时很危险

优化：

mysql> explain SELECT t2.rid, qid, status, source, deleted, uid, toUid, uname, content, misFlag, createTime, uip, opTime, opUid, likeCnt, opName, auditSt, applyTime, ext, likeCnt  FROM tblArticleReply1 t2  join (select rid from tblArticleReply1  WHERE (qid = 2819559) ORDER BY rid ASC LIMIT 41 OFFSET 24920) t1 on t1.rid = t2.rid\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 41
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t2
         type: eq_ref
possible_keys: idx_rid
          key: idx_rid
      key_len: 4
          ref: t1.rid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: tblArticleReply1
         type: ref
possible_keys: qid,qid_likecnt
          key: qid
      key_len: 4
          ref: 
         rows: 52188
        Extra: Using where; Using index
3 rows in set (0.01 sec)

这里使用了索引排序，现将查询结果集减少后再用小的结果集去连表。测试环境下执行时间0.02s，优化了不少啊！

索引排序：在mysql中索引本身就是排序好的，所以在第二条语句中只需要用where条件查询到rid，然后取出40条就行。

这里有一点需要注意：一定要用小结果集去join大结果集，否则就会有如下情况：

mysql> explain SELECT t2.rid, qid, status, source, deleted, uid, toUid, uname, content, misFlag, createTime, uip, opTime, opUid, likeCnt, opName, auditSt, applyTime, ext, likeCnt  FROM tblArticleReply1 t2 left join (select rid from tblArticleReply1  WHERE (qid = 2819559) ORDER BY rid ASC LIMIT 41 OFFSET 24920) t1 on t1.rid = t2.rid\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 38251327
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 41
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: tblArticleReply1
         type: ref
possible_keys: qid,qid_likecnt
          key: qid
      key_len: 4
          ref: 
         rows: 52188
        Extra: Using where; Using index
3 rows in set (0.01 sec)

可以看出扫描行数很多且没有使用索引。。测试环境中没跑出来。。

转载于:https://my.oschina.net/u/2563638/blog/709109