为什么MySQL (MyISAM和InnoDB)不使用我的索引？

Question

下面是我的表格：

CREATE TABLE `wp_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,
  `meta_value_integer` int(11) DEFAULT NULL,
  `meta_value_date` date DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`),
  KEY `post_meta_integer` (`meta_key`,`meta_value_integer`),
  KEY `post_meta_date` (`meta_key`,`meta_value_date`)
) ENGINE=MyISAM AUTO_INCREMENT=2050 DEFAULT CHARSET=utf8

您可以从WordPress中识别它，但是我添加了两个额外的列来将meta_value存储为整数，一个存储为日期。我还在(meta_key，meta_value_integer)和(meta_key，meta_value_date)上添加了索引，其余的都在前面了。

现在我想知道为什么MySQL在这样的查询中选择meta_key索引而不是post_meta_integer键：

mysql> EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = 'price' AND meta_value_integer > 1000;
+----+-------------+-------------+------+-----------------------+----------+---------+-------+------+-------------+
| id | select_type | table       | type | possible_keys         | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+-----------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_postmeta | ref  | meta_key,meta_integer | meta_key | 768     | const |    9 | Using where |
+----+-------------+-------------+------+-----------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

我也尝试了一下，但是没有成功。如果我将比较更改为=，则它使用meta_integer键。也许这与我的数据集有关，我不知道。meta_value_integer和dates也有很多空值(例如，在meta_value中使用字符串)。

所以我想知道为什么MySQL更喜欢meta_key索引，我做错了什么。我真的很想优化模式，因为在post meta中可能有200,000个不同的行，而其中可能只有20行是价格。因此，理想情况下，我希望meta_value_integer索引比整个meta_key索引更小。这有可能吗？

谢谢!

Answer 1

您在meta_key上进行选择，所以它使用该索引是非常合理的。MySQL不能同时使用这两个索引。

解决方案:您可以在两列(第一列为meta_key)上创建一个组合键，并为整数字段创建一个单独的键。当过滤两列时(就像现在一样)或者只过滤第一列的meta_key时，将使用组合键。当您仅根据整型字段进行筛选时，将使用整型键。

Answer 2

如果MySQL对使用哪个索引的最佳猜测与您的想法不同，您可以尝试使用index hint。