mysql - MAX(ID) 与 IN() 比较函数性能

Question

我偶然发现了这个查询的性能问题。我已经盯着这个问题很长时间了，现在挠头了。这个查询实际上在某一时刻非常快，但是一旦数据增长，它就会变得越来越慢。 'Posts' 表有 +500 万行，'Items' 表有 +6000 行。这些表格每天都在不断增长。

SELECT Posts.itemID, Items.itemName, Items.itemImage, Items.guid, Posts.price,
Posts.quantity, Posts.date, Games.name, Items.profit FROM Items 
INNER JOIN Posts ON Items.itemID=Posts.itemID 
INNER JOIN Games ON Posts.gameID=Games.gameID 
WHERE Posts.postID IN (SELECT MAX(postID) FROM Posts GROUP BY itemID) AND Posts.gameID=:gameID 
    AND Posts.price BETWEEN :price_min AND :price_max
    AND Posts.quantity BETWEEN :quant_min AND :quant_max
    AND Items.profit BETWEEN :profit_min AND :profit_max
ORDER BY Items.profit DESC LIMIT 0, 20

在代码中，我将查询和子查询一分为二。他们一起表现得更慢。这一切都很好，直到 Posts 和 Items 中的数据开始增长。我放入 ** 中的“where”语句会根据设置的过滤器进行连接。

这是我得到的解释。 (这是没有子查询的查询) https://docs.google.com/file/d/0B1jxMdMfC35VeDBEbnJISmNGb3c/edit?usp=sharing

显示帖子的索引:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Posts |          0 | PRIMARY  |            1 | postID      | A         |     5890249 |     NULL | NULL   |      | BTREE      |         |               |
| Posts |          1 | itemID   |            1 | itemID      | A         |       16453 |     NULL | NULL   | YES  | BTREE      |         |               |
| Posts |          1 | gameID   |            1 | gameID      | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

显示项目的索引；

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Items |          0 | PRIMARY  |            1 | itemID      | A         |        6452 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

显示游戏中的索引；

 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Games |          0 | PRIMARY  |            1 | gameID      | A         |        2487 |     NULL | NULL   |      | BTREE      |         |               |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

无论如何我可以使这个查询更快吗？你们有什么建议吗？有没有更好的方法来编写这个查询？感谢所有帮助。

解释建议的查询:

  +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+
  | id | select_type | table      | type   | possible_keys         | key     | key_len | ref                        | rows    | Extra                                        |
  +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+
  |  1 | PRIMARY     | <derived2> | ALL    | NULL                  | NULL    | NULL    | NULL                       |      19 | Using temporary; Using filesort              |
  |  1 | PRIMARY     | p          | eq_ref | PRIMARY,itemID,gameID | PRIMARY | 4       | q.postID                   |       1 |                                              |
  |  1 | PRIMARY     | i          | eq_ref | PRIMARY               | PRIMARY | 2       | db323245342342345.p.itemID |       1 | Using where                                  |
  |  1 | PRIMARY     | g          | eq_ref | PRIMARY               | PRIMARY | 4       | db323245342342345.p.gameID |       1 | Using where                                  |
  |  2 | DERIVED     | p          | ref    | itemID,gameID         | gameID  | 2       |                            | 2945124 | Using where; Using temporary; Using filesort |
  |  2 | DERIVED     | i          | eq_ref | PRIMARY               | PRIMARY | 2       | db323245342342345.p.itemID |       1 | Using where                                  |
  +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+

Answer 1

尝试用JOIN 重写它。有点像

SELECT p.itemID, 
       i.itemName, 
       i.itemImage, 
       i.guid, 
       p.price,
       p.quantity, 
       p.date, 
       g.name, 
       i.profit 
  FROM 
(
    SELECT MAX(postID) postID 
      FROM Posts p JOIN Items i
        ON p.itemID = i.itemID
     WHERE p.gameID = :gameID 
       AND p.price    BETWEEN :price_min  AND :price_max
       AND p.quantity BETWEEN :quant_min  AND :quant_max 
       AND i.profit   BETWEEN :profit_min AND :profit_max
     GROUP BY itemID
) q JOIN Posts p 
    ON q.postID = p.postID JOIN Items i
    ON p.itemID = i.itemID JOIN Games g
    ON p.gameID = g.gameID
 ORDER BY i.profit DESC 
 LIMIT 0, 20