mysql - 两个相似查询(MySQL ORDER 子句)的巨大速度差异

Question

重要更新(说明):我意识到，具有单个 DESC 顺序的查询比具有 ASC 顺序的相同查询慢 10 倍。有序字段有一个索引。这是正常行为吗？

带有查询的原始问题:

我有一个 mysql 表，其中包含数百个产品项。 (对我来说)令人惊讶的是 2 个相似的 sql 查询在性能方面有何不同。我不知道为什么。您能给我一个提示或解释一下为什么差异如此之大吗？

此查询需要 3 毫秒:

SELECT
    *
FROM
    `product_items`
WHERE
    (product_items.shop_active = 1)
    AND (product_items.active = 1)
    AND (product_items.active_category_id is not null)
    AND (has_picture is not null)
    AND (price_orig is not null)
    AND (category_min_discount IS NOT NULL)
    AND (product_items.slug is not null)
    AND `product_items`.`active_category_id` IN (6797, 5926, 5806, 6852)
ORDER BY
    price asc
LIMIT 1

但是下面的查询已经花费了 169 毫秒... 唯一的区别是 order 子句包含 2 列。 “价格”值包含每种产品，而“价格最高”值大约只包含 1% 的产品。

SELECT
    *
FROM
    `product_items`
WHERE
    (product_items.shop_active = 1)
    AND (product_items.active = 1)
    AND (product_items.active_category_id is not null)
    AND (has_picture is not null)
    AND (price_orig is not null)
    AND (category_min_discount IS NOT NULL)
    AND (product_items.slug is not null)
    AND `product_items`.`active_category_id` IN (6797, 5926, 5806, 6852)
ORDER BY
    price asc,
    price_top desc
LIMIT 1

表结构如下所示:

CREATE TABLE `product_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_id` int(11) DEFAULT NULL,
  `item_id` varchar(255) DEFAULT NULL,
  `productname` varchar(255) DEFAULT NULL,
  `description` text,
  `url` text,
  `url_hash` varchar(255) DEFAULT NULL,
  `img_url` text,
  `price` decimal(10,2) DEFAULT NULL,
  `price_orig` decimal(10,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `discount_percent` decimal(10,2) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `delivery_date` varchar(255) DEFAULT NULL,
  `categorytext` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `active_category_id` int(11) DEFAULT NULL,
  `shop_active` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT '0',
  `price_top` decimal(10,2) NOT NULL DEFAULT '0.00',
  `attention_priority` int(11) DEFAULT NULL,
  `attention_priority_over` int(11) DEFAULT NULL,
  `has_picture` varchar(255) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  `category_min_discount` int(11) DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_product_items_on_url_hash` (`url_hash`),
  KEY `index_product_items_on_shop_id` (`shop_id`),
  KEY `index_product_items_on_active_category_id` (`active_category_id`),
  KEY `index_product_items_on_productname` (`productname`),
  KEY `index_product_items_on_price` (`price`),
  KEY `index_product_items_on_discount_percent` (`discount_percent`),
  KEY `index_product_items_on_price_top` (`price_top`)
) ENGINE=InnoDB AUTO_INCREMENT=1715708 DEFAULT CHARSET=utf8;

更新我意识到差异主要在于排序类型:如果我对两列都使用 asc+asc，查询大约需要 6 毫秒，如果我使用 asc+desc 或 desc+asc，查询大约需要 160 毫秒..

谢谢。

Answer 1

如果创建索引来帮助 ORDER BY 没有帮助，请尝试创建一个同时帮助 WHERE 和 ORDER BY 的索引:

CREATE INDEX product_items_i1 ON product_items (
    shop_active,
    active,
    active_category_id,
    has_picture,
    price_orig,
    category_min_discount,
    slug,
    price,
    price_top DESC
)

显然，这有点笨拙，您必须在查询的性能增益与维护索引的成本之间取得平衡。