如何优化这个特定的MySQL查询，该查询检查2.84M行和29.49k InnoDB不同页面

Question

我试图弄清楚为什么一个查询需要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

我正在运行Percona5.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)

Answer 1

在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条件使其等价。

Answer 2

“前缀”索引通常不值得使用，特别是当它们不是复合索引的最后一部分时。

我的建议包括在多个was中更改post_meta模式；请参阅以下内容，其中包括每个步骤的基本原理：http://mysql.rjweb.org/doc.php/index_烹饪书_mysql#speeding_向上_wp_后元

CREATE TABLE wp_postmeta (
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT NOT NULL,
    PRIMARY KEY(post_id, meta_key),
    INDEX(meta_key)
    ) ENGINE=InnoDB;

INDEX(meta_key, post_id, meta_value(30))不如PRIMARY KEY(post_id, meta_key)好，因为

• 辅助键首先深入次要BTree，然后到达PRIMARY BTree；这避免了第一步。
• 前缀索引没有多大帮助。

通过拥有PRIMARY KEY(post_id, meta_key)，您可能需要扫描的所有行都是相邻的，因此需要获取的块非常少。

如前所述，更改postmeta表将加快涉及它的大多数WP查询。但SELECT的真正问题是.

最重要的过滤(user=15448)隐藏在LEFT JOIN后面的是第二个表。user_id需要在posts和索引中。或者至少删除LEFT，以便优化器可以从meta而不是posts开始。在这一点上，yper多维数据集的(meta_key, meta_value(30), post_id)将有一定的用处。唉，它不会是“覆盖”，因为不可避免的“前缀”。