mysql已经设置主键, 但explain显示key=null, type=all?

Question

1. 在navicat里检查, 确实id已经被设置为主键了.
2. explain显示key=null, type=all? 问题出在哪?
3. 该怎么优化呢?
4. 这个是wordpress网站, 按道理说这里的表和数据库结构都是wordpress自动生成的, 我这怎么会有这样问题呢?

MariaDB [jinz]> explain select * from wp_posts;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | wp_posts | ALL  | NULL          | NULL | NULL    | NULL |  413 |       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

MariaDB [jinz]> explain select * from wp_posts where id =1;
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

wp_posts | 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 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci 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 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci 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`)
) ENGINE=InnoDB AUTO_INCREMENT=843 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Answer 1

这里 有 Extra 域的解释。

"Impossible WHERE noticed after reading const tables" 表示它发现这个 query 选不出任何数据。

const table 就表示已经用了 Index（Primary key 或者 Unique index）:

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

==============================================

全量查当然不会用索引。

你加个 WHERE id = 1 再试试。

Answer 2

你mysql 版本是多少？
主要大小写：ID id
id 是主键了，就不要在设置索引了。
KEY type_status_date (post_type,post_status,post_date,ID),
设置这个key 加上ID是干嘛的呢