如何优化这个特定的MySQL查询,检查2.84M行和29.49k InnoDB不同的页面
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何优化这个特定的MySQL查询,检查2.84M行和29.49k InnoDB不同的页面，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6228字，纯文字阅读大概需要9分钟。
内容图文
我试图找出为什么一个查询需要?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条件使其等效.
内容总结
以上是互联网集市为您收集整理的如何优化这个特定的MySQL查询,检查2.84M行和29.49k InnoDB不同的页面全部内容，希望文章能够帮你解决如何优化这个特定的MySQL查询,检查2.84M行和29.49k InnoDB不同的页面所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】