mysql - 如何优化以下查询以及什么是(使用 where;使用连接缓冲区(块嵌套循环))与 EXPLAIN
问题描述
我一直面临查询问题。每当我尝试选择查询时,它需要 10 到 15 秒才能执行。以及解释中的(使用哪里;使用连接缓冲区(块嵌套循环))
查询是
SELECT wp_posts.ID, post_title, post_content, wp_pvc_total.postcount,
wp_pvc_total.postnum
FROM wp_posts
LEFT JOIN wp_term_relationships
ON ( wp_posts.ID =
wp_term_relationships.object_id )
LEFT JOIN wp_term_taxonomy
ON ( wp_term_relationships.term_taxonomy_id =
wp_term_taxonomy.term_taxonomy_id )
LEFT JOIN wp_pvc_total ON ( wp_pvc_total.postnum = wp_posts.ID )
WHERE wp_posts.post_author = 630
AND wp_posts.post_author NOT IN(675)
AND wp_posts.ID != 48075
GROUP BY wp_posts.ID
ORDER BY wp_pvc_total.postcount DESC
LIMIT 0, 9;
带解释的查询
+----+-------------+-----------------------+--------+---------------------------------------------------------------------------+-------------+---------+-------------------------------------------------------+-------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+--------+---------------------------------------------------------------------------+-------------+---------+-------------------------------------------------------+-------+--------------------------------------------------------+
| 1 | SIMPLE | wp_posts | range | PRIMARY,post_name,type_status_date,post_parent,post_author,idx_post_title | post_author | 16 | NULL | 1682 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | wp_term_relationships | ref | PRIMARY | PRIMARY | 8 | marriai1_topic.wp_posts.ID | 1 | Using index |
| 1 | SIMPLE | wp_term_taxonomy | eq_ref | PRIMARY | PRIMARY | 8 | marriai1_topic.wp_term_relationships.term_taxonomy_id | 1 | Using index |
| 1 | SIMPLE | wp_pvc_total | ALL | NULL | NULL | NULL | NULL | 19670 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-----------------------+--------+---------------------------------------------------------------------------+-------------+---------+-------------------------------------------------------+-------+--------------------------------------------------------+
MySQL 版本是 5.6,Innodb 引擎和表结构是
CREATE TABLE `wp_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`),
FULLTEXT KEY `idx_post_title` (`post_title`)
)
和
CREATE TABLE `wp_pvc_total` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`postnum` varchar(255) NOT NULL,
`postcount` int(11) NOT NULL DEFAULT '750',
UNIQUE KEY `id` (`id`)
)
解决方案
添加INDEX(postnum, postcount)
这将使接触变得wp_pvc_total
更有效率。它是“覆盖”。
BNL 效率很高,所以我不确定这个指数会不会更好。但似乎没有有用的索引是低效的。
在另一个问题上,KEY post_name
( post_name
(191)) 是有问题的。见http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
推荐阅读
- graphql - 无法为不可为空的字段返回 null,调试器不显示 null
- firebase-realtime-database - RTDB 规则 - 只能在时间之前更新(在其他路径中引用的时间)
- laravel - 如何在 Laravel 中循环基于 product_id 的控制器
- node.js - 尖锐错误:输入缓冲区包含不支持的图像格式
- sql - 将列拆分为两个相等数量的行
- javascript - 根据选择的选项更改输入的值,但对于多个集合
- mysql - 使用参数作为查询输入时,Express Server、MySQL 查询中断
- javascript - 使用 ASP 提示
- javascript - 制作一个JS纸牌游戏
- python - GEKKO 和 Scipy.optimize 导致非线性参数估计的不同结果