mysql - 为什么使用 INT 选择包含数字的 Varchar 索引比使用字符串慢得多？

Question

我有一个包含几千行的表，有一个包含数字的 Varchar 列。尽管当时讨论了为什么此列不是数字类型，但从该表中选择行显示了一种奇怪的行为。

虽然该列上有一个索引，但使用数字字符串查找行比使用整数(0.54 秒)快得多(0.01 秒)。这是什么原因？它似乎无法转换和使用索引的值...

我是不是忽略了什么？看起来它不是在转换 Int 以将其用于索引？我是否必须提供有关索引使用的提示，或者是否有数据库切换来完成此操作？或者，如果我误解了 Explain 输出，为什么它会慢很多？

显示示例的表格布局:

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stuff` (`stuff`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里使用字符串作为索引:

explain select * from example where stuff='200';
----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | example | ref  | idx_stuff     | idx_stuff | 137     | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+

这里看起来它没有将 Int 转换为 String 以用于查找索引:

explain select * from example where stuff=200;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | example | index | idx_stuff     | idx_stuff | 137     | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+

Answer 1

如 the manual 中所述:

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

如有必要，您可以随时CAST你的整数到一个字符串，以便利用索引:

SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);