为什么使用INT来选择包含比使用字符串慢得多的数字的Varchar索引？ [英] Why is using an INT to select a Varchar index containing numbers much slower than using Strings?

问题描述

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

I have a table that contains several thousand rows, having a Varchar column that contains numbers. Despite discussing why this column is not a numeric type then, selecting rows from that table showed a strange behavior.

虽然该列上有索引，但使用数字字符串找到一行比使用Ints（0.54秒）快得多（0.01秒）。这是什么原因？它似乎无法投射和使用索引的值...

Although there is an index on that column, using numeric strings to find a row is MUCH faster (0.01 secs) than using Ints (0.54 secs). What is the reason for this? It seems not to be able to cast and use the value for the index...

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

Am I overlooking something? It looks like it is not casting the Int to use it for the index? Do I have to give hints on index usage, or is there a database switch to accomplish this? Or if I misunderstand the Explain output, why is it so much slower then?

表格布局显示一个例子：

Table layout to show an example:

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;

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

Here it is using the String for the index:

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来用于查找索引：

Here it looks like it does not cast the Int to a String to use for looking up the index:

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 |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+

推荐答案

如手册中所述：

为了比较字符串列和数字，MySQL不能使用列上的索引来快速查找值。如果 str_col 是索引字符串列，则在以下语句中执行查找时，不能使用索引：

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;

原因是有许多不同的字符串可以转换为值 1 ，例如'1'，'1'或'1a' 。

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 你的整数到一个字符串，以便利用index：

If necessary, you can always CAST your integer to a string in order to take advantage of the index:

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

这篇关于为什么使用INT来选择包含比使用字符串慢得多的数字的Varchar索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！