主页 / dba / 问题 / 177489
Accepted
Daniel Kanchev
Asked: 2017-06-29 04:00:37 +0800 CST

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

  • 772

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

2 个回答

  1. Best Answer

    Wordpress 表中使用的 EAV 模式meta导致需要复合索引的查询。我建议这三个指标：

    ****_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条件使它等价。

    • 5

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

    我的建议涉及更改多个 post_meta 模式；看到这个，其中包括每个步骤的基本原理：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    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，然后进入PRIMARYBTree；这避免了第一步。
    • 前缀索引没有多大帮助。

    通过具有，您可能PRIMARY KEY(post_id, meta_key)需要扫描的所有行都是相邻的，从而导致需要获取的块很少。

    如上更改postmeta表格将加快涉及它的大多数 WP 查询。但真正的问题SELECT如下......

    最重要的过滤（用户=15448）隐藏在LEFT JOIN一个辅助表后面。user_id 需要在posts其中并编入索引。或者至少删除LEFT，以便优化器可能以而不是开始。那时 ypercube会有点用处。唉，不会因为免不了的“前缀”而被“覆盖”吧。metaposts(meta_key, meta_value(30), post_id)

    • 0

相关问题