Mysql
如何優化這個檢查 2.84M 行和 29.49k InnoDB 不同頁面的特定 MySQL 查詢
我試圖弄清楚為什麼一個查詢需要大約 5 秒才能完成以及如何優化它。以下是我迄今為止收集到的資訊：
1. EXPLAIN statement for the query: EXPLAIN EXTENDED SELECT SUM(meta2.meta_value) FROM ****_posts as posts LEFT JOIN ****_postmeta AS meta ON posts.ID = meta.post_id LEFT JOIN ****_postmeta AS meta2 ON posts.ID = meta2.post_id WHERE meta.meta_key = '_customer_user' AND meta.meta_value = '15448' AND posts.post_type = 'shop_order' AND posts.post_status IN ( 'wc-processing','wc-completed' ) AND meta2.meta_key = '_order_total'; +----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+ | 1 | SIMPLE | meta2 | ref | post_id,meta_key | meta_key | 576 | const | 141630 | 100.00 | Using where | | 1 | SIMPLE | posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | barberkl_wp821.meta2.post_id | 1 | 100.00 | Using where | | 1 | SIMPLE | meta | ref | post_id,meta_key | post_id | 8 | barberkl_wp821.meta2.post_id | 18 | 100.00 | Using where | +----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+3 行，1 個警告（0.06 秒）
2. pt-query-digest stats Profile Rank Query ID Response time Calls R/Call V/M Item ==== ================== ============= ===== ====== ===== =============== 1 0x5C7505FDD18B918C 4.7628 100.0% 1 4.7628 0.00 SELECT Query 1: 0 QPS, 0x concurrency, ID 0x5C7505FDD18B918C at byte 0 ________ This item is included in the report because it matches --limit. Scores: V/M = 0.00 Time range: all events occurred at 2017-06-28 07:16:34 Attribute pct total min max avg 95% stddev median ============ === ======= ======= ======= ======= ======= ======= ======= Count 100 1 Exec time 100 5s 5s 5s 5s 5s 0 5s Lock time 100 173us 173us 173us 173us 173us 0 173us Rows sent 100 1 1 1 1 1 0 1 Rows examine 100 2.84M 2.84M 2.84M 2.84M 2.84M 0 2.84M Rows affecte 0 0 0 0 0 0 0 0 Bytes sent 100 77 77 77 77 77 0 77 Merge passes 0 0 0 0 0 0 0 0 Tmp tables 0 0 0 0 0 0 0 0 Tmp disk tbl 0 0 0 0 0 0 0 0 Tmp tbl size 0 0 0 0 0 0 0 0 Query size 100 412 412 412 412 412 0 412 InnoDB: IO r bytes 0 0 0 0 0 0 0 0 IO r ops 0 0 0 0 0 0 0 0 IO r wait 0 0 0 0 0 0 0 0 pages distin 100 29.49k 29.49k 29.49k 29.49k 29.49k 0 29.49k queue wait 0 0 0 0 0 0 0 0 rec lock wai 0 0 0 0 0 0 0 0我正在執行 Percona 5.6，如果需要，我可以提供配置。請指導我從哪裡開始。謝謝！
更新（顯示創建表）：
mysql> SHOW CREATE TABLE wpn0_postmeta \G; *************************** 1. row *************************** Table: wpn0_postmeta Create Table: CREATE TABLE `wpn0_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`(191)), KEY `woo_index1` (`post_id`,`meta_key`,`meta_value`(8)) ) ENGINE=InnoDB AUTO_INCREMENT=6681190 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE wpn0_posts \G; *************************** 1. row *************************** Table: wpn0_posts Create Table: CREATE TABLE `wpn0_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_author` bigint(20) unsigned NOT NULL DEFAULT '0', `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content` longtext NOT NULL, `post_title` text NOT NULL, `post_excerpt` text NOT NULL, `post_status` varchar(20) NOT NULL DEFAULT 'publish', `comment_status` varchar(20) NOT NULL DEFAULT 'open', `ping_status` varchar(20) NOT NULL DEFAULT 'open', `post_password` varchar(255) NOT NULL DEFAULT '', `post_name` varchar(200) NOT NULL DEFAULT '', `to_ping` text NOT NULL, `pinged` text NOT NULL, `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content_filtered` longtext NOT NULL, `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `guid` varchar(255) NOT NULL DEFAULT '', `menu_order` int(11) NOT NULL DEFAULT '0', `post_type` varchar(20) NOT NULL DEFAULT 'post', `post_mime_type` varchar(100) NOT NULL DEFAULT '', `comment_count` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `post_name` (`post_name`(191)), KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), KEY `post_parent` (`post_parent`), KEY `post_author` (`post_author`), KEY `woo_index2` (`post_type`,`post_status`,`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=349189 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
Wordpress
meta表中使用的 EAV 模式會導致需要復合索引的查詢。我建議這三個指標：****_postmeta (meta_key, meta_value(30), post_id) -- for meta ****_postmeta (meta_key, post_id, meta_value(30)) -- for meta2 ****_posts (post_type, id, post_status) -- for posts列
meta_value是類型text所以不能將整列放入索引中，所以我們只需要放入幾個第一個字元（比如(30)）。您可能還決定將列的類型更改為varchar(100)或類似的東西，但需要更多測試，首先沒有現有值更長，其次是某種未來檢查，因此任何嘗試儲存更長值的 Wordpress 外掛都不會失敗（不知道這有多容易）。另請注意，
LEFT連接可以替換為INNER連接。WHERE條件使其等效。