如何使MySQL与INTEGER字段和FLOAT字段的键相交

Question

我有以下MySQL表（表大小-大约10K条记录）：

CREATE TABLE `tmp_index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `m_id` int(11) DEFAULT NULL,
  `r_id` int(11) DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `m_key` (`m_id`),
  KEY `r_key` (`r_id`),
  KEY `price_key` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=16390 DEFAULT CHARSET=utf8;

正如您所看到的，我有两个INTEGER字段（r_id和m_id）和一个FLOAT字段（price）。对于这些字段中的每一个，我都有一个索引。

现在，当我在第一个整数上运行条件为AND在第二个整数上的查询时，一切都很好：

mysql> explain select * from tmp_index_test where m_id=1 and r_id=2;
+----+-------------+----------------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+
| id | select_type | table          | type        | possible_keys | key         | key_len | ref  | rows | Extra                                     |
+----+-------------+----------------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | tmp_index_test | index_merge | m_key,r_key   | r_key,m_key | 5,5     | NULL |    1 | Using intersect(r_key,m_key); Using where |
+----+-------------+----------------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+

MySQL似乎执行得很好，因为Extra字段中有Using intersect（r_key，m_key）。我不是MySQL专家，但据我所知，MySQL首先在索引上进行交集，然后从表本身收集交集的结果。

然而，当我运行非常相似的查询，但不是对两个整数设置条件，而是对一个整数和一个浮点设置类似的条件时，MySQL拒绝在索引上与结果相交：

mysql> explain select * from tmp_index_test where m_id=3 and price=100;
+----+-------------+----------------+------+-----------------+-----------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys   | key       | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+-----------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tmp_index_test | ref  | m_key,price_key | price_key | 5       | const |    1 | Using where |
+----+-------------+----------------+------+-----------------+-----------+---------+-------+------+-------------+

正如你所看到的，MySQL决定只使用价格指数。

我的第一个问题是为什么，以及如何解决它？

除此之外，我还需要使用MORE符号（>）而不是价格上的等号（=）来运行查询。目前的解释显示，对于此类查询，MySQL只使用整数键。

mysql> explain select * from tmp_index_test where m_id=3 and price > 100;
+----+-------------+----------------+------+-----------------+-------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys   | key   | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+-----------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | tmp_index_test | ref  | m_key,price_key | m_key | 5       | const |    2 | Using where |
+----+-------------+----------------+------+-----------------+-------+---------+-------+------+-------------+

我需要让MySQL首先在索引上进行交集。有人知道怎么做吗？

提前感谢！

Answer 1

来自MySQL手册：

如果联接仅使用键的最左边前缀，或者如果该键不是PRIMARY key或UNIQUE索引（换句话说，如果join不能基于键值选择单个行）。如果钥匙所使用的只匹配几行，这是一个很好的联接类型。

price不是唯一的或主要的，因此选择ref。我不相信你能强行交叉。