如何提高此查詢性能

我有一個執行以下查詢的應用程序

SELECT SQL_NO_CACHE p.*,
                   product_shop.*,
                   stock.out_of_stock,
                   IFNULL(stock.quantity, 0)                            as quantity,
                   MAX(product_attribute_shop.id_product_attribute)        id_product_attribute,
                   product_attribute_shop.minimal_quantity              AS product_attribute_minimal_quantity,
                   pl.`description`,
                   pl.`description_short`,
                   pl.`available_now`,
                   pl.`available_later`,
                   pl.`link_rewrite`,
                   pl.`meta_description`,
                   pl.`meta_keywords`,
                   pl.`meta_title`,
                   pl.`name`,
                   MAX(image_shop.`id_image`)                              id_image,
                   il.`legend`,
                   m.`name`                                             AS manufacturer_name,
                   cl.`name`                                            AS category_default,
                   DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
                                                              INTERVAL 200
                                                              DAY)) > 0 AS new,
                   product_shop.price                                   AS orderprice
FROM `ps_category_product` cp
        LEFT JOIN `ps_product` p
                  ON p.`id_product` = cp.`id_product`
        INNER JOIN ps_product_shop product_shop
                   ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
        LEFT JOIN `ps_product_attribute` pa
                  ON (p.`id_product` = pa.`id_product`)
        LEFT JOIN ps_product_attribute_shop product_attribute_shop
                  ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND
                      product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1)
        LEFT JOIN ps_stock_available stock
                  ON (stock.id_product = p.id_product AND
                      stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND
                      stock.id_shop = 1 AND stock.id_shop_group = 0)
        LEFT JOIN `ps_category_lang` cl
                  ON (product_shop.`id_category_default` = cl.`id_category`
                      AND cl.`id_lang` = 1 AND cl.id_shop = 1)
        LEFT JOIN `ps_product_lang` pl
                  ON (p.`id_product` = pl.`id_product`
                      AND pl.`id_lang` = 1 AND pl.id_shop = 1)
        LEFT JOIN `ps_image` i
                  ON (i.`id_product` = p.`id_product`)
        LEFT JOIN ps_image_shop image_shop
                  ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover = 1)
        LEFT JOIN `ps_image_lang` il
                  ON (image_shop.`id_image` = il.`id_image`
                      AND il.`id_lang` = 1)
        LEFT JOIN `ps_manufacturer` m
                  ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1
 AND cp.`id_category` = 26655
 AND product_shop.`active` = 1
 AND product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY cp.`position` asc
LIMIT 0,12

我想提高它的性能，但我不知道從哪裡開始。

從我在所有連接表中看到的情況來看，主鍵都有索引，但問題來自創建一個臨時表，它需要大約 3 秒才能將數據複製到。

有什麼想法可以解決這個問題並提高性能嗎？

硬體規格是

Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz
64GB RAM
1TB Pci-E NVME DISK

我根據建議在此處添加表結構

ps_category_product

create table ps_category_product
(
   id_category int unsigned not null,
   id_product int unsigned not null,
   position int unsigned default 0 not null,
   primary key (id_category, id_product)
);

create index id_product
   on ps_category_product (id_product);

ps_product

create table ps_product
(
   id_product int unsigned auto_increment
       primary key,
   id_supplier int unsigned null,
   id_manufacturer int unsigned null,
   id_category_default int unsigned null,
   id_shop_default int unsigned default 1 not null,
   id_tax_rules_group int(11) unsigned not null,
   on_sale tinyint(1) unsigned default 0 not null,
   online_only tinyint(1) unsigned default 0 not null,
   ean13 varchar(13) null,
   upc varchar(12) null,
   ecotax decimal(17,6) default 0.000000 not null,
   quantity int(10) default 0 not null,
   minimal_quantity int unsigned default 1 not null,
   price decimal(20,6) default 0.000000 not null,
   wholesale_price decimal(20,6) default 0.000000 not null,
   unity varchar(255) null,
   unit_price_ratio decimal(20,6) default 0.000000 not null,
   additional_shipping_cost decimal(20,2) default 0.00 not null,
   reference varchar(32) null,
   supplier_reference varchar(32) null,
   location varchar(64) null,
   width decimal(20,6) default 0.000000 not null,
   height decimal(20,6) default 0.000000 not null,
   depth decimal(20,6) default 0.000000 not null,
   weight decimal(20,6) default 0.000000 not null,
   out_of_stock int unsigned default 2 not null,
   quantity_discount tinyint(1) default 0 null,
   customizable tinyint(2) default 0 not null,
   uploadable_files tinyint default 0 not null,
   text_fields tinyint default 0 not null,
   active tinyint(1) unsigned default 0 not null,
   redirect_type enum('', '404', '301', '302') default '' not null,
   id_product_redirected int unsigned default 0 not null,
   available_for_order tinyint(1) default 1 not null,
   available_date date not null,
   `condition` enum('new', 'used', 'refurbished') default 'new' not null,
   show_price tinyint(1) default 1 not null,
   indexed tinyint(1) default 0 not null,
   visibility enum('both', 'catalog', 'search', 'none') default 'both' not null,
   cache_is_pack tinyint(1) default 0 not null,
   cache_has_attachments tinyint(1) default 0 not null,
   is_virtual tinyint(1) default 0 not null,
   cache_default_attribute int unsigned null,
   date_add datetime not null,
   date_upd datetime not null,
   advanced_stock_management tinyint(1) default 0 not null,
   netopricew tinyint default 0 null,
   netopricew01 tinyint default 0 null,
   netopricer tinyint default 0 null
);

create index date_add
   on ps_product (date_add);

create index id_category_default
   on ps_product (id_category_default);

create index indexed
   on ps_product (indexed);

create index product_manufacturer
   on ps_product (id_manufacturer);

create index product_supplier
   on ps_product (id_supplier);

product_shop

create table ps_product_shop
(
   id_product int unsigned not null,
   id_shop int unsigned not null,
   id_category_default int unsigned null,
   id_tax_rules_group int(11) unsigned not null,
   on_sale tinyint(1) unsigned default 0 not null,
   online_only tinyint(1) unsigned default 0 not null,
   ecotax decimal(17,6) default 0.000000 not null,
   minimal_quantity int unsigned default 1 not null,
   price decimal(20,6) default 0.000000 not null,
   wholesale_price decimal(20,6) default 0.000000 not null,
   unity varchar(255) null,
   unit_price_ratio decimal(20,6) default 0.000000 not null,
   additional_shipping_cost decimal(20,2) default 0.00 not null,
   customizable tinyint(2) default 0 not null,
   uploadable_files tinyint default 0 not null,
   text_fields tinyint default 0 not null,
   active tinyint(1) unsigned default 0 not null,
   redirect_type enum('', '404', '301', '302') default '' not null,
   id_product_redirected int unsigned default 0 not null,
   available_for_order tinyint(1) default 1 not null,
   available_date date not null,
   `condition` enum('new', 'used', 'refurbished') default 'new' not null,
   show_price tinyint(1) default 1 not null,
   indexed tinyint(1) default 0 not null,
   visibility enum('both', 'catalog', 'search', 'none') default 'both' not null,
   cache_default_attribute int unsigned null,
   advanced_stock_management tinyint(1) default 0 not null,
   date_add datetime not null,
   date_upd datetime not null,
   primary key (id_product, id_shop)
);


create index date_add
   on ps_product_shop (date_add, active, visibility);

create index id_category_default
   on ps_product_shop (id_category_default);

ps_product_attribute

create table ps_product_attribute
(
   id_product_attribute int unsigned auto_increment
       primary key,
   id_product int unsigned not null,
   reference varchar(32) null,
   supplier_reference varchar(32) null,
   location varchar(64) null,
   ean13 varchar(13) null,
   upc varchar(12) null,
   wholesale_price decimal(20,6) default 0.000000 not null,
   price decimal(20,6) default 0.000000 not null,
   ecotax decimal(17,6) default 0.000000 not null,
   quantity int(10) default 0 not null,
   weight decimal(20,6) default 0.000000 not null,
   unit_price_impact decimal(17,2) default 0.00 not null,
   default_on tinyint(1) unsigned default 0 not null,
   minimal_quantity int unsigned default 1 not null,
   available_date date null,
   active tinyint default 1 null,
   upcoming_info varchar(80) null,
   upcoming_info_upd date null,
   by_order tinyint default 0 null
);

create index id_product_id_product_attribute
   on ps_product_attribute (id_product_attribute, id_product);

create index product_attribute_product
   on ps_product_attribute (id_product);

create index product_default
   on ps_product_attribute (id_product, default_on);

create index reference
   on ps_product_attribute (reference);

create index supplier_reference
   on ps_product_attribute (supplier_reference);

請求的配置資訊

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_io_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 4000  |
+------------------------+-------+
2 rows in set (0.00 sec)

聞起來像“爆炸-內爆”。當您這樣做時會發生這種情況JOINs，然後通過GROUP BY.

通常的解決方法是首先設計最小查詢來查找主表 ( )的 ids (或者PRIMARY KEY顯然是什麼)：id_product``product_shop

SELECT id_product
   FROM product_shop
   JOIN ... -- only if you need to filter on some other tables
   ORDER BY .. LIMIT .. -- only if you are filtering this way, too

然後將其用作主查詢中的“派生”表：

SELECT ...   -- everything you need
   FROM ( the-derived-table-above ) AS x
   JOIN product_shop AS p USING(product_id)  -- since you need other columns from it
   JOIN ...  -- whatever else you need
   WHERE ...
   ORDER BY ... -- you cannot trust the order by of the derived table
   LIMIT ...  -- probably not needed, if the derived table suffices

在某些情況下（我不確定您的情況），您可以將派生表中的行數減少到 12 的事實對性能有兩個潛在的重大影響：只JOINs需要 12 組，最後ORDER BY .. LIMIT（如果需要）非常快，因為只有 12 行。

但是，存在一個風險，即JOINs不會真正讓您將其移動LIMIT到派生表中——JOINs可能會導致多於或少於 12 行。 INNER JOIN 可能會導致更少或更多的行；LEFT JOIN不能導致更少的行。（你兩者都有，我不知道你的數據。）

（我認為我從未見過有用的配置文件——注意 99.9% 的時間是如何花在無用的“複製到 tmp 表”上的。）

其他問題…

FROM `ps_category_product` cp
LEFT JOIN `ps_product` p
              ON p.`id_product` = cp.`id_product`
INNER JOIN ps_product_shop product_shop
               ON (product_shop.id_product = p.id_product
               AND product_shop.id_shop = 1)
WHERE product_shop.`id_shop` = 1
 AND cp.`id_category` = 26655
 AND product_shop.`active` = 1
 AND product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY cp.`position` asc
LIMIT 0,12

由於 上有WHERE子句product_shop，因此它並不是真正的LEFT聯接。請刪除LEFT，這樣我和優化器都不必站在我們的頭上發現這一點。

-- for `product_shop`:
INDEX(id_shop, active,   -- in either order
     visibility,        -- next; is IN
     id_product)        -- (not sure if this will help)
-- for `ps_category_product`:
INDEX(id_category)   -- unless it is `PRIMARY KEY(id_category)`

抱歉，我不確定這些。我真的需要看看SHOW CREATE TABLE才能知道是什麼PRIMARY KEYs。優化器選擇從ps_category_product; 我不知道這是因為它更好還是因為缺少索引。似乎可以先點擊 ps_category_product 或 product_shop 。我需要更多資訊來就索引更改提供建議。

SELECT 查詢的前兩行請求前兩個表中的 x.* 所有列。這種 * 的使用導致大約 80 列（x 平均行長度）的數據被推送到“複製到 Tmp 表”的世界中。是否有可能 - 命名您真正需要的列（對於 JOIN 和 WHERE，處理）將減少 MySQL 分析器報告的 3.3 秒？在任何慢查詢中首先要查找的是 *（星號）並考慮使用最少的數據量來完成查詢（需要處理）。

您可以…嗎

SET @sdateless200d=DATE_SUB(NOW(), INTERVAL 200 DAY);

在 SELECT 查詢之前並更改您的 DATEDIFF 行以將其用作帶有類似內容的常量

DATEDIFF(product_shop,`date_add`, @sdateless200d) > 0 AS new, AS orderprice

避免 NON-DETERMINISTIC 並避免 NOW() 的系統呼叫？

感謝 innodb_io_capacity% 結果。額定容量很可能比您目前的 2000 高得多。取您的供應商 NVME 額定容量數 * .4（保守一點）並在您的配置中使用 innodb_io_capacity。保持 innodb_io_capacity_max OUT 的配置和啟動將乘以 2 為最大值。

引用自：https://dba.stackexchange.com/questions/240896