主页 / dba / 问题 / 45407
Accepted
Arash Mousavi
Asked: 2013-06-29 01:56:34 +0800 CST

更改 ORDER BY 方向会导致性能问题

  • 772

我使用 MySQL 5.6，我有两个表，每个表有 1600 万行：

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

我有一个订单查询order by newsstudios.id ASC：

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

此查询的结果是：

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

但是当我将 order by 的方向更改为DESC查询执行时间时减少了 100 倍：

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

第一：为什么这种排序方向的改变会导致性能上的巨大差异？

第二：在此查询之前，我尝试过LEFT JOIN查询WHERE IN而不是查询，WHERE EXISTS但它们有重复的结果，我应该使用GROUP BY它导致的结果using filesort，这会using temporary大大降低性能。您对查询有更好的性能有什么建议吗？

mysql performance

2 个回答

  1. Best Answer

    如果您要搜索的不同类别不是很多，并且您可以构建更复杂的查询，这将使用索引(category_id, newsstudio_id)，我希望它比您之前的查询更有效，无论是选择ASC还是DESC选择。

    构造起来有点复杂，如果类别数量为数十或数百，我预计效率会降低：

    SELECT newsstudio_id
FROM 
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5 
    ) 
    UNION 
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;
    • 4

  2. 正如 ypercube 在他的评论中所说，如果字段被反转，你的索引在这种情况下应该表现得更好。

    此外，只要每个newsstudio_categories. newsstudio_id在 中至少有一个匹配行newsstudios。id（您的模式不能保证，但似乎有可能），那么您可以通过只查看的查询来解决newsstudio_categories

    SELECT SQL_NO_CACHE DISTINCT `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
ORDER BY `newsstudio_id` DESC
LIMIT 5

    或者

    SELECT SQL_NO_CACHE `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
GROUP BY `newsstudio_id`
ORDER BY `newsstudio_id` DESC
LIMIT 5
    • 2

相关问题