mysql - 由于 order by ，简单查询非常慢

Question

请问有人可以帮忙解决以下问题吗？ (180352 行)

SELECT COUNT(p.stock_id) AS num_products,
p.master_photo, p.product_photo, p.stock_id, p.master, p.title, p.price, p.stock_level, p.on_order, p.location, p.supplier, p.category, p.sub_category, p.reorder
FROM products AS p
WHERE p.sub_category != 'Subscriptions'
GROUP BY p.master
ORDER BY p.stock_id ASC
LIMIT 0, 20

运行时间为 6 秒。

当我删除订单时，它在 0.0023 秒运行。

当我删除分组依据时也是一样。

stock_id(唯一)和 sub_category 已编入索引。

我想不出另一种方法来处理这样的查询，因为我按主数据进行分组以获取产品变体的数量至关重要，而且可以订购它们也很重要(不一定是按 stock_id，但这是默认)。

谢谢

<小时/>

按照e4c5的要求，下面是按顺序解释的结果

id: 1
select_type: SIMPLE
table: p
type: range
possible_keys: sub_category
key: sub_category
key_len: 52
ref: NULL
rows: 181691
Extra: Using where; Using temporary; Using filesort

然后没有顺序

id: 1
select_type: SIMPLE
table: p
type: index
possible_keys: sub_category
key: master
key_len: 52
ref: NULL
rows: 21
Extra: Using where

<小时/>

然后下面是创建表

CREATE TABLE IF NOT EXISTS `products` (
  `stock_id` varchar(50) NOT NULL,
  `conv_stock_id` varchar(100) NOT NULL,
  `conv_quantity` decimal(10,2) NOT NULL,
  `master` varchar(50) NOT NULL,
  `master_photo` varchar(255) NOT NULL,
  `free_guide_photo` varchar(255) NOT NULL,
  `product_var_photo` varchar(255) NOT NULL,
  `master_title` varchar(255) NOT NULL,
  `master_slug` varchar(255) NOT NULL,
  `master_page_title` varchar(255) NOT NULL,
  `product_photo` varchar(255) NOT NULL,
  `original_product_photo` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `orig_title` varchar(255) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  `slug` varchar(255) NOT NULL,
  `custom_url` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  `supplier` varchar(50) NOT NULL,
  `supplier_stock_id` varchar(50) NOT NULL,
  `supplier_discount` int(11) NOT NULL,
  `category` varchar(50) NOT NULL,
  `sub_category` varchar(50) NOT NULL,
  `cost_price` decimal(10,2) NOT NULL,
  `discount_cost_price` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `sale_price` decimal(10,2) NOT NULL,
  `sale_price_startdate` date NOT NULL,
  `sale_price_enddate` date NOT NULL,
  `orig_price_trail` int(3) NOT NULL,
  `price_trail` varchar(50) NOT NULL,
  `price_rule` int(1) NOT NULL,
  `pack_size` int(11) NOT NULL,
  `parcel_size` int(1) NOT NULL,
  `packaging_rule` int(11) NOT NULL,
  `cut_tear` int(1) NOT NULL,
  `oversized_parcel` int(1) NOT NULL,
  `print_label` int(1) NOT NULL,
  `stock_level` decimal(10,1) NOT NULL,
  `stock_level_group` varchar(50) NOT NULL,
  `stock_level_increment` decimal(10,2) NOT NULL,
  `stock_check_date` datetime NOT NULL,
  `reorder` int(1) NOT NULL,
  `reorder_level` decimal(10,1) NOT NULL,
  `reorder_quantity` decimal(10,1) NOT NULL,
  `reorder_attempts` int(1) NOT NULL,
  `unit_size` decimal(10,1) NOT NULL,
  `on_order` decimal(10,1) NOT NULL,
  `date_ordered` datetime NOT NULL,
  `back_order` decimal(10,1) NOT NULL,
  `uom` decimal(10,1) NOT NULL,
  `uom_value` varchar(100) NOT NULL,
  `stock_estimate` int(1) NOT NULL,
  `due_date` datetime NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `colour` varchar(255) NOT NULL,
  `colour_family` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `style` varchar(255) NOT NULL,
  `pattern` varchar(255) NOT NULL,
  `shape` varchar(255) NOT NULL,
  `design` varchar(255) NOT NULL,
  `fibre` varchar(255) NOT NULL,
  `material` varchar(255) NOT NULL,
  `pattern_for` varchar(255) NOT NULL,
  `difficulty` varchar(255) NOT NULL,
  `fabric_count` varchar(255) NOT NULL,
  `yarn_thickness` varchar(255) NOT NULL,
  `suggested_needle_size` varchar(255) NOT NULL,
  `tension` varchar(255) NOT NULL,
  `collections` varchar(255) NOT NULL,
  `product_features` varchar(255) NOT NULL,
  `size` varchar(255) NOT NULL,
  `actual_size` varchar(255) NOT NULL,
  `length` varchar(255) NOT NULL,
  `width` varchar(255) NOT NULL,
  `weight` varchar(255) NOT NULL,
  `weight_gsm` varchar(255) NOT NULL,
  `brand` varchar(255) NOT NULL,
  `designer` varchar(255) NOT NULL,
  `composition` varchar(255) NOT NULL,
  `washing_instructions` varchar(255) NOT NULL,
  `matching_thread` varchar(50) NOT NULL,
  `sample` varchar(50) NOT NULL,
  `fat_quarter` varchar(50) NOT NULL,
  `barcode` varchar(13) NOT NULL,
  `list_international` int(1) NOT NULL,
  `token` varchar(50) NOT NULL,
  `create_sample` int(1) NOT NULL,
  `create_fatquarter` int(1) NOT NULL,
  `create_listing_type` int(1) NOT NULL,
  `create_listing_size` int(11) NOT NULL,
  `create_listing_price` decimal(10,2) NOT NULL,
  `create_listing_price_rule` int(11) NOT NULL,
  `create_listing_sale_price` decimal(10,2) NOT NULL,
  `create_listing_parcelsize` int(1) NOT NULL,
  `create_listing_barcode` varchar(13) NOT NULL,
  `auto_listing` int(1) NOT NULL,
  `custom_bridal` int(1) NOT NULL,
  `pickwave_assign` int(1) NOT NULL,
  `kit_product` int(11) NOT NULL,
  `fatquarter_product` int(1) NOT NULL,
  `sample_product` int(1) NOT NULL,
  `grouped_product` int(1) NOT NULL,
  `grouped_product_quantity` decimal(10,1) NOT NULL,
  `multiple_product` int(1) NOT NULL,
  `freepost_product` int(1) NOT NULL,
  `status` int(1) NOT NULL,
  `update_stock_level` int(1) NOT NULL,
  `force_product_photo` int(1) NOT NULL,
  `created_master_photo` int(1) NOT NULL,
  `force_master_photo` int(1) NOT NULL,
  `created_free_guide_photo` int(1) NOT NULL,
  `force_free_guide_photo` int(1) NOT NULL,
  `created_product_var_photo` int(1) NOT NULL,
  `force_product_var_photo` int(1) NOT NULL,
  `force_additional_photo` int(1) NOT NULL,
  `created_price_levelling` int(1) NOT NULL,
  `created_grouped_product` int(1) NOT NULL,
  `updated_stock_level` int(1) NOT NULL,
  `create_multiple_listing` int(1) NOT NULL,
  `create_freepost_listing` int(1) NOT NULL,
  `create_freeguide_info` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  UNIQUE KEY `stock_id` (`stock_id`),
  KEY `token` (`token`),
  KEY `title` (`title`),
  KEY `stock_level_group` (`stock_level_group`),
  KEY `sub_category` (`sub_category`),
  KEY `stock_level` (`stock_level`),
  KEY `category` (`category`),
  KEY `conv_stock_id` (`conv_stock_id`),
  KEY `conv_quantity` (`conv_quantity`),
  KEY `created_price_levelling` (`created_price_levelling`),
  KEY `master` (`master`),
  KEY `colour` (`colour`),
  KEY `auto_listing` (`auto_listing`),
  KEY `multiple_product` (`multiple_product`),
  KEY `status` (`status`),
  KEY `ebay_master` (`ebay_master`),
  KEY `parcel_size` (`parcel_size`),
  KEY `grouped_product` (`grouped_product`),
  KEY `sample_product` (`sample_product`),
  KEY `fatquarter_product` (`fatquarter_product`),
  KEY `created_grouped_product` (`created_grouped_product`),
  KEY `price` (`price`),
  KEY `freepost_product` (`freepost_product`),
  KEY `master_title` (`master_title`),
  KEY `c_sub_category_master` (`sub_category`,`master`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

您尚未提供解释的输出，但是根据您的查询，ORDER BY 似乎强制进行全表扫描。这会使查询非常慢。

当您不使用 ORDER BY 时，数据库会读取前 20 个 master 值(可能有相当多)的结果，并将它们分组在一起并返回结果。

当您按 stock_id 订购时，需要查看整个表格以查找哪些 master 与最低值 stock_id 相关联

也许可以通过 sub_category,master 上的复合索引来提高性能，但是除非您共享 SHOW CREATE TABLES, EXPLAIN 输出，否则无法得出结论。

更新根据您的 CREATE TABLE 语句，我发现您的数据库未标准化。例如，为什么我感觉以下列应该在自己的表格中？

   supplier varchar(50) NOT NULL,
   supplier_stock_id varchar(50) NOT NULL,
   supplier_discount int(11) NOT NULL,

您的产品表中应该只有一个 supplier_stock_id(供应商表的外键)。有一些类似的列确实应该移出。

这样做时，您可以在此表上创建更精简、更精简的索引。但这并不是 table 变窄的全部。这反过来意味着全表扫描的最坏情况实际上变得更快。

我还注意到该表没有主键。这是一个很大的禁忌。 stock_id 如果是数字则应为主键。如果它不是数字，它可能仍然是主键的最佳候选，但这是您需要决定的事情。