MySQL优化查询与子查询 [英] MySQL optimization query with subqueries

问题描述

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

Today i received email from my hosting account saying that i need to tweak my query:

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

此查询检查65,396,669,012,829行，这在共享托管中是不可接受的.

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

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

tbh, i don't understand their explanation.. what the query actually does is to get 15 posts order by time updated, for each post i grab the latest comment, count all comments for each post.

posts table - 'the_data'

comments table = 'the_ans'

我不是mysql专家，而且我不知道如何改善此查询 任何帮助将不胜感激

i'm not a mysql guru and i don't know how to improve this query any help will be appreciated

thx

查询

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

推荐答案

快速浏览说明计划会发现没有适合MySQL使用的索引，因此它依靠全表扫描.

A quick glance at the explain plan shows that there are no suitable indexes for MySQL to use, so it's resorting to full table scans.

 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

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

To optimize the execution of the existing query, you need to add appropriate indexes. Likely candidates:

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

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

Those indexes will significantly improve the performance of both the outer query (likely eliminating the sort operation), and the numerous executions of the correlated subqueries.

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

Beyond that, you're going to need to change the query to improve performance. The LIMIT clause on the outermost query is being applied after the entire resultset is prepared, which means those two correlated subqueries are getting executed for every row in table the_data. And that's going to eat your lunch, performance wise.

要使那些相关的子查询仅在返回的(最多)15行中运行，您需要在运行这些子查询之前应用LIMIT子句.

To have those correlated subqueries run only for the (up to) 15 rows that are being returned, you need to get that LIMIT clause applied before those subqueries get run.

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

This query should return an equivalent resultset, and will avoid 34,000+ executions of each correlated subquery, which should improve performance considerably:

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次.)

(Your current query executes each of those correlated subqueries "SELECT COUNT(1) FROM the_data" times. With the rewritten query above, each of those subqueries will be executed only 15 times.)

这篇关于MySQL优化查询与子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！