我试图找出为什么一个查询需要〜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,如果需要,我可以提供配置.请指导我从哪里开始.谢谢！
更新(SHOW CREATE TABLE)：
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的元表中使用的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条件使其等效.