简单查询由于排序原因非常慢 [英] Simple query very slow due to order by

问题描述

请任何人帮助以下查询? (180352行)

Please could anyone help with the following query? (180352 rows)

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秒.

When I remove the order by it run's at 0.0023 seconds.

当我删除分组依据时也是如此.

And also the same when I remove the group by.

对stock_id(唯一)和sub_category建立索引.

The stock_id (unique) and sub_category are indexed.

我想不出另一种方法来处理这样的查询，因为至关重要的是，我需要与主控人员分组以获取产品变型的数量，并且至关重要的是可以订购这些产品(不一定是通过stock_id来定购的，但这就是默认).

I can't think of another way to approach a query like this as it is vital that I group by the master to get the count of product variations and also vital that they can be ordered (not necessarily by stock_id but that's the default).

谢谢

以下e4c5所要求的是解释的结果，顺序为:

As requested by e4c5 below is the result of the explain with the order by

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

---

然后是创建表

---

and then below is the create table

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;

推荐答案

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

You haven't provided the output from explain, however based on your query it would seem that ORDER BY forces a full table scan. That would make the query very slow.

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

When you don't use the ORDER BY, the db reads the results for the first 20 master values (there maybe quite a few of them) and groups them together and returns the result.

按stock_id排序时，需要查看整个表以查找哪些master s与最低值stock_id s

When you order by stock_id the whole table needs to be looked at to find which masters are associated with the lowest values stock_ids

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

It maybe possible to improve performance with a composite index on sub_category,master however a conclusion cannot be made unless you share your SHOW CREATE TABLES, EXPLAIN output.

更新 根据您的CREATE TABLE语句，我发现您的数据库未规范化.例如，为什么我会有以下几列应该放在自己的表中的感觉?

Update Based on your CREATE TABLE statements, I see that your database isn't normalized. For example Why do I get the feeling that the following columns should in a table of their own?

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

您的产品表中应仅包含supplier_stock_id(供应商表的外键).有一些类似的列实际上应该移出.

You should only have a supplier_stock_id in your products table (foreign key to the suppliers table). There are similar sets of columns which really should be moved out.

这样做时，您可以在此表上创建精简和卑鄙的索引.但这并不意味着桌子变窄了.反过来，这意味着全表扫描的最坏情况实际上要快得多.

When you do so you can create leaner and meaner indexes on this table. But that's not all the table becomes narrower. Which in turn means the worst case scenario of a full table scan actually becomes a lot faster.

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

I also noticed that the table does not have a primary key. Which is a big no-no. The stock_id if it's numeric should be primary key. If it's not numeric it might stil be the best candidate for primary key but this is something you need to decide.

这篇关于简单查询由于排序原因非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！