Mysql

小數據庫大小，但性能非常慢。我能做些什麼？

  • January 4, 2016

我們有這個 opencart 安裝，數據庫大小約為 120 MB，其中 90MB 是 MyISAM，其他是 InnoDB。

大多數查詢如下：

SELECT  DISTINCT *, a.*, vd.seo, p.image, p2s.price, p.product_id,
       p.image, pd.name, 
     ( SELECT  price
           FROM  product_special ps
           WHERE  ps.product_id = p.product_id
             AND  ps.customer_group_id = '1'
             AND  ((ps.date_start = '0000-00-00'
                             OR  ps.date_start < NOW())
                     AND  (ps.date_end = '0000-00-00'
                             OR  ps.date_end > NOW()
                         )
                     AND  ps.store_id = '0' 
                  )
           ORDER BY  ps.priority ASC, ps.price ASC
           LIMIT  1
     ) AS special
   FROM  product_variation a, variation_description vd, product_to_category pc,
       variation v, product_description pd, product_to_store_alter p2s,
       product p
   WHERE  v.variation_group_id='18'
     AND  p2s.product_id = p.product_id
     AND  p2s.active = '1'
     AND  p2s.store_id = '0'
     AND  pc.product_id = p.product_id
     AND  a.variation_id = v.variation_id
     AND  vd.variation_id = v.variation_id
     AND  vd.variation_id = a.variation_id
     AND  v.variation_id = '1418'
     AND  p.product_id = pd.product_id
     AND  a.product_id = pd.product_id
     AND  p2s.quantity > '0'
     AND  p.product_id != '17230'
     AND  p.status = '1'
     AND  p.date_available <= NOW()
   GROUP BY  p.product_id
   ORDER BY  a.product_id DESC
   LIMIT  8;

執行上述查詢大約需要 2 秒。MySQL Server 5.5，key_buffer 為 256MB，innodb_buffer_pool 為 128M。排序和連接緩衝區各有 8M。

任何我可以改進的地方，都會導致此類查詢針對單個頁面執行多次。幫助將不勝感激！

更新：解釋聲明

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY v   const   PRIMARY,variation_group_id  PRIMARY 8   const,const 1   Using temporary; Using filesort
1   PRIMARY vd  ref PRIMARY PRIMARY 4   const   1   Using where
1   PRIMARY p   ref PRIMARY,status  status  1   const   13711   Using where
1   PRIMARY pd  ref PRIMARY PRIMARY 4   ninec.p.product_id  1
1   PRIMARY p2s eq_ref  PRIMARY PRIMARY 8   ninec.p.product_id,const    1   Using where
1   PRIMARY a   ref product_id,variation_id product_id  4   ninec.p2s.product_id    14  Using where
1   PRIMARY pc  ref product_id  product_id  4   ninec.p.product_id  3   Using index
2   DEPENDENT SUBQUERY  ps  ref customer_group_id,store_id,product_id   customer_group_id   4   const   1   Using where; Using filesort

更新

CREATE TABLE為上表添加語句：

CREATE TABLE variation (
 variation_id int(11) NOT NULL AUTO_INCREMENT,
 variation_group_id int(11) NOT NULL,
 sort_order int(3) NOT NULL,
 PRIMARY KEY (variation_id),
 KEY variation_group_id (variation_group_id)
) ENGINE=MyISAM

CREATE TABLE variation_description (
 variation_id int(11) NOT NULL,
 language_id int(11) NOT NULL,
 name varchar(64) COLLATE utf8_bin NOT NULL,
 description varchar(255) COLLATE utf8_bin NOT NULL,
 seo varchar(255) COLLATE utf8_bin NOT NULL,
 image varchar(255) COLLATE utf8_bin NOT NULL,
 is_moq tinyint(1) NOT NULL,
 url_link varchar(255) COLLATE utf8_bin NOT NULL,
 video text COLLATE utf8_bin NOT NULL,
 download_link varchar(255) COLLATE utf8_bin NOT NULL,
 launch_date date NOT NULL,
 PRIMARY KEY (variation_id,language_id),
 KEY seo (seo),
 KEY language_id (language_id)
) ENGINE=MyISAM

CREATE TABLE product (
 product_id int(11) NOT NULL AUTO_INCREMENT,
 model varchar(64) COLLATE utf8_bin NOT NULL,
 sku varchar(64) COLLATE utf8_bin NOT NULL,
 upc varchar(12) COLLATE utf8_bin NOT NULL,
 location varchar(128) COLLATE utf8_bin NOT NULL,
 quantity int(4) NOT NULL DEFAULT '0',
 rs_quantity int(4) NOT NULL,
 moq_quantity int(4) NOT NULL,
 stock_status_id int(11) NOT NULL,
 image varchar(255) COLLATE utf8_bin DEFAULT NULL,
 watermark_pos int(1) NOT NULL,
 watermark_colour int(1) NOT NULL,
 manufacturer_id int(11) NOT NULL,
 vendor_id varchar(25) COLLATE utf8_bin NOT NULL,
 shipping tinyint(1) NOT NULL DEFAULT '1',
 mrp_price decimal(15,2) NOT NULL,
 price decimal(15,4) NOT NULL DEFAULT '0.0000',
 org_price decimal(15,0) NOT NULL,
 moq_price decimal(15,0) NOT NULL,
 points int(8) NOT NULL DEFAULT '0',
 tax_class_id int(11) NOT NULL,
 date_available date NOT NULL,
 weight decimal(15,8) NOT NULL DEFAULT '0.00000000',
 weight_class_id int(11) NOT NULL DEFAULT '0',
 length decimal(15,8) NOT NULL DEFAULT '0.00000000',
 width decimal(15,8) NOT NULL DEFAULT '0.00000000',
 height decimal(15,8) NOT NULL DEFAULT '0.00000000',
 length_total float NOT NULL,
 length_class_id int(11) NOT NULL DEFAULT '0',
 subtract tinyint(1) NOT NULL DEFAULT '1',
 minimum int(11) NOT NULL DEFAULT '1',
 sort_order int(11) NOT NULL DEFAULT '0',
 best_seller int(1) NOT NULL,
 new_arrival int(1) NOT NULL,
 readytoship int(1) NOT NULL,
 exclusive int(1) NOT NULL,
 sale int(1) NOT NULL,
 is_readymade tinyint(1) NOT NULL,
 status tinyint(1) NOT NULL DEFAULT '0',
 date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 date_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 viewed int(5) NOT NULL DEFAULT '0',
 time_to_ship varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '5',
 wash_care varchar(255) COLLATE utf8_bin NOT NULL,
 measure_group_id int(11) NOT NULL,
 wishlist int(11) NOT NULL,
 shoppingcart int(11) NOT NULL,
 barcode varchar(99) COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (product_id),
 KEY sort_order (sort_order),
 KEY best_seller (best_seller),
 KEY new_arrival (new_arrival),
 KEY readytoship (readytoship),
 KEY exclusive (exclusive),
 KEY sale (sale),
 KEY status (status),
 KEY viewed (viewed),
 KEY time_to_ship (time_to_ship),
 KEY model (model)
) ENGINE=InnoDB


CREATE TABLE product_description (
 product_id int(11) NOT NULL AUTO_INCREMENT,
 language_id int(11) NOT NULL,
 name varchar(255) COLLATE utf8_bin NOT NULL,
 description text COLLATE utf8_bin NOT NULL,
 meta_description varchar(255) COLLATE utf8_bin NOT NULL,
 meta_keyword varchar(255) COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (product_id,language_id),
 KEY name (name),
 KEY language_id (language_id)
) ENGINE=MyISAM

CREATE TABLE product_special (
 product_special_id int(11) NOT NULL AUTO_INCREMENT,
 product_id int(11) NOT NULL,
 customer_group_id int(11) NOT NULL,
 priority int(5) NOT NULL DEFAULT '1',
 price decimal(15,4) NOT NULL DEFAULT '0.0000',
 date_start date NOT NULL DEFAULT '0000-00-00',
 date_end date NOT NULL DEFAULT '0000-00-00',
 store_id int(11) NOT NULL,
 PRIMARY KEY (product_special_id),
 KEY product_id (product_id),
 KEY customer_group_id (customer_group_id),
 KEY store_id (store_id)
) ENGINE=MyISAM

CREATE TABLE product_to_category (
 product_id int(11) NOT NULL,
 category_id int(11) NOT NULL,
 KEY product_id (product_id,category_id),
 KEY category_id (category_id,product_id)
) ENGINE=InnoDB

CREATE TABLE product_to_store_alter (
 product_id int(11) NOT NULL,
 store_id int(11) NOT NULL DEFAULT '0',
 location varchar(128) NOT NULL,
 quantity int(10) NOT NULL DEFAULT '0',
 price decimal(15,4) NOT NULL DEFAULT '0.0000',
 substract tinyint(1) NOT NULL DEFAULT '1',
 minimum int(11) NOT NULL DEFAULT '1',
 active tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (product_id,store_id)
) ENGINE=MyISAM

CREATE TABLE product_variation (
 product_id int(11) NOT NULL,
 variation_id varchar(255) COLLATE utf8_bin NOT NULL,
 language_id int(11) NOT NULL,
 KEY product_id (product_id),
 KEY variation_id (variation_id)
) ENGINE=MyISAM

請使用JOIN ... ON ...語法。

INDEX(customer_group_id, product_id, store_id)將是一個更好的 ps 指標。

DISTINCT在你有一個列表之前不要引入依賴子查詢product_ids。這樣你就不會反複評估它。那是，

  1. 刪除子查詢（“特殊”）
  2. 使用剩餘的查詢作為子查詢：

SELECT *, ( SELECT price ... ) AS special FROM ( SELECT DISTINCT ... ORDER BY ... LIMIT 8 ) AS x;

現在，您將只執行 8 次子查詢，而不是超過一百萬次。

可能其他表可能會以這種方式延遲；我沒有耐心趟過那張 7 桌JOIN。大概pc和a。

與您在和product_id中使用的內容保持一致；它們似乎都是平等的，並且優化器可能足夠聰明。建議使用，因為這是.GROUP BY``ORDER BY``p.``EXPLAIN

製作其中KEYs之一。（我假設你不想重複？）product_to_category``PRIMARY KEY

不要費心索引標誌（銷售，查看，…）

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

相關問答

Mysql

Mysql：插入性能 INNODB 與 MISAM

  • February 6, 2022
檢視問答
Mysql

從 pt-query-digest 辨識慢查詢異常值的根本原因？

  • January 21, 2022
檢視問答
Mysql

為什麼 InnoDB 上的簡單 SELECT 比 MyISAM 慢 100 倍？

  • December 31, 2021
檢視問答
Mysql

MySQL 8 中的插入比 MySQL 5.7 中的插入慢

  • May 1, 2021
檢視問答
Mysql

MyISAM 比 InnoDB 慢約 5 倍

  • May 21, 2020
檢視問答
Mysql

Mysql 將使用者名與客戶端表分開以提高性能並使用 myisam

  • March 16, 2020
檢視問答