为什么MySQL（MyISAM和InnoDB）没有使用我的索引？

Here's my table:

这是我的表：

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

You can recognize it form WordPress but I added two extra columns to store the meta_value as an integer and one as a date. I've also added indices on (meta_key, meta_value_integer) and (meta_key, meta_value_date), the rest was there before.

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

Now I'm just wondering why is MySQL picking the meta_key index and not the post_meta_integer key on a query like this:

现在我只是想知道为什么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)

I tried less than and BETWEEN too but no luck. If I change the comparison to = though it uses the meta_integer key. Maybe it's related to my dataset I don't know. There are a lot of NULL values for meta_value_integer and dates too (where strings are used in meta_value for example).

我尝试了不到和BETWEEN，但没有运气。如果我将比较更改为=虽然它使用meta_integer键。也许它与我不知道的数据集有关。 meta_value_integer和日期也有很多NULL值（例如，在meta_value中使用字符串）。

So I'd like to know why does MySQL prefer the meta_key index and what am I doing wrong. I'd really like to optimize the schema since there may be 200,000 different rows in posts meta and only 20 of them might be the prices. So ideally I'd like the meta_value_integer index to be smaller than the whole meta_key index. Is that possible?

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

Thanks!

谢谢！

2 个解决方案

#1

You're selecting on meta_key, so it's quite logical that it uses that index. MySQL cannot use both indexes.

您正在选择meta_key，因此它使用该索引是合乎逻辑的。 MySQL不能同时使用这两个索引。

Solution: You can create a combined key on both columns (meta_key being the first column) and a separate key for the integer field. The combined key will be used when filtering on both columns (like you do now) or on meta_key, the first column, alone. The integer key will be used when you filter on the integer field alone.

解决方案：您可以在两列上创建组合键（meta_key是第一列）和整数字段的单独键。在对两列（就像您现在这样）进行过滤时，或者在第一列meta_key上使用组合键。单独过滤整数字段时将使用整数键。

#2

If MySQL's best guess at which index to use is not what you had in mind, you could try using an index hint.

如果MySQL最好猜测使用哪个索引并不是你想到的，你可以尝试使用索引提示。

#1