带子查询的 MySQL 优化查询

Question

今天我收到了来 self 的托管帐户的电子邮件，说我需要调整我的查询:

SELECT
  `id`, `nick`, `msg`, `uid`, `show_pic`,
  `time`,`ip`,`time_updated`,
  (SELECT COUNT(c.msg_id)
   FROM `the_ans` c
   where c.msg_id = d.id) AS counter,
  (SELECT c.msg
   FROM `the_ans` c
   WHERE c.msg_id=d.id
   ORDER BY `time` DESC LIMIT 1) as lastmsg
FROM
  `the_data` d
ORDER BY `time_updated` DESC LIMIT 26340 ,15

解释:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY d ALL 34309 Using filesort
3 DEPENDENT SUBQUERY c ALL 43659 Using where; Using filesort
2 DEPENDENT SUBQUERY c ALL 43659 Using where

This query examines 65,396,669,012,829 rows, which is unacceptable in shared hosting.

老实说，我不明白他们的解释..查询实际上做的是按更新时间顺序获取 15 个帖子，对于每个帖子，我都会获取最新评论，计算每个帖子的所有评论。

posts table - 'the_data'

comments table = 'the_ans'

我不是 mysql 高手，我不知道如何改进这个查询任何帮助将不胜感激

谢谢

查询

SELECT
  `id` , `nick` , `msg` , `uid` , `show_pic` , `time` , `ip` , `time_updated` , (
    SELECT COUNT( c.msg_id )
    FROM `the_ans` c
    WHERE c.msg_id = d.id
   ) AS counter, (
    SELECT c.msg
    FROM `the_ans` c
    WHERE c.msg_id = d.id
    ORDER BY `time` DESC
    LIMIT 1
   ) AS lastmsg
FROM `the_data` d
ORDER BY `time_updated` DESC
LIMIT 26340 , 15 

这是结果结构

id| nick  | msg  | uid   | show_pick | time      | ip |time_updated|counter|lastmsg
  |       |      |       |           |           |    |            |       |
7 | jqman | hello| 10074 |   0       |2013-21-01 | 12 |2013-21-01  | 55    |blah bl

Answer 1

快速浏览一下解释计划，发现没有适合MySQL使用的索引，因此采用全表扫描。

 EXPLAIN: 
 id select_type        table type possible_keys key key_len ref rows  Extra 
 -- ------------------ ----- ---- ------------- --- ------- --- ----- ---------------------------- 
 1  PRIMARY            d     ALL                                34309 Using filesort
 3  DEPENDENT SUBQUERY c     ALL                                43659 Using where; Using filesort 
 2  DEPENDENT SUBQUERY c     ALL                                43659 Using where

要优化现有查询的执行，您需要添加适当的索引。可能的候选人:

ON `the_data`(`time_updated`)
ON `the_ans`(`msg_id`,`time`)

这些索引将显着提高外部查询(可能消除排序操作)和相关子查询的大量执行的性能。

---

除此之外，您还需要更改查询以提高性能。最外层查询的 LIMIT 子句在准备好整个结果集后应用，这意味着这两个相关的子查询将针对表 the_data 中的每一行执行。就性能而言，这会吃掉你的午餐。

要让那些相关的子查询只对返回的(最多)15 行运行，您需要在运行这些子查询之前应用 LIMIT 子句。

此查询应返回等效的结果集，并将避免每个相关子查询执行 34,000 多次，这将显着提高性能:

SELECT d.*
     , ( SELECT COUNT( c.msg_id )
           FROM `the_ans` c
          WHERE c.msg_id = d.id
       ) AS counter
     , ( SELECT c.msg
           FROM `the_ans` c
          WHERE c.msg_id = d.id
          ORDER BY `time` DESC
          LIMIT 1
       ) AS lastmsg
  FROM ( SELECT e.`id` 
              , e.`nick`
              , e.`msg`
              , e.`uid`
              , e.`show_pic`
              , e.`time`
              , e.`ip`
              , e.`time_updated` 
           FROM `the_data` e
          ORDER
             BY e.`time_updated` DESC
          LIMIT 26340 , 15 
       ) d
 ORDER BY d.`time_updated` DESC

(您当前的查询执行每个相关子查询“SELECT COUNT(1) FROM the_data”次。使用上面重写的查询，每个子查询将只执行 15 次。)