mysql order by 性能优化

Question

您好，我喜欢通过查询进行高效订单。这是我的查询:

SELECT a.status,a.title,a.id,
b.deployed,b.accepted,b.rejected,b.error
FROM a
LEFT JOIN b ON ( a.id = b.id )
WHERE b.agencyid = 1 AND a.userid = 3
ORDER BY a.date DESC
LIMIT 0,50

如果没有 ORDER BY 子句，查询需要 0.006 秒使用 ORDER BY 子句需要 1.1181 秒!!!

耗时最多的是 Copying To Tmp Table 1,1 s 99,78% 1 1,1 s如何减少复制到 tpm 表的时间？

Answer 1

首先，正如 Strawberry 所指出的，因为您在 where 子句中引用了“b”别名，并且不考虑 NULL，所以它本质上将您的左连接转换为 INNER JOIN。

SELECT 
      a.status,
      a.title,
      a.id,
      b.deployed,
      b.accepted,
      b.rejected,
      b.error
   FROM a JOIN b 
      ON a.id = b.id
   WHERE 
          a.userid = 3
      AND b.agencyid = 1 
   ORDER BY 
      a.date DESC
   LIMIT 
      0,50

如果您打算将其设置为 LEFT-JOIN(所有“a”记录，无论“b”表中是否匹配)，那么您需要将“b”限定符移至 JOIN 子句。

SELECT 
      a.status,
      a.title,
      a.id,
      b.deployed,
      b.accepted,
      b.rejected,
      b.error
   FROM a LEFT JOIN b 
      ON a.id = b.id
      AND b.agencyid = 1 
   WHERE 
      a.userid = 3
   ORDER BY 
      a.date DESC
   LIMIT 
      0,50

话虽如此，您正在寻找按日期排序的特定用户。我会让你的“a”表在(userid，date)上建立索引，这样引擎就可以在你的 WHERE 限定符和 order by 上进行优化，并且应该解决你的性能问题，无论左连接和内连接如何......

为了帮助“b”表的连接条件，请在(id，agencyid)上对其进行索引