我有一个包含数千行的表,有一个包含数字的 Varchar 列。尽管讨论了为什么此列不是数字类型,但从该表中选择行显示出奇怪的行为。
尽管该列上有一个索引,但使用数字字符串查找行比使用 Ints(0.54 秒)快得多(0.01 秒)。这是什么原因呢?似乎无法强制转换和使用索引的值...
我忽略了什么吗?看起来它没有强制转换 Int 以将其用于索引?我是否必须提供有关索引使用情况的提示,或者是否有数据库切换来完成此操作?或者如果我误解了解释输出,为什么它慢得多?
表格布局显示示例:
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 转换为字符串以用于查找索引:
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
是索引字符串列,则在以下语句中执行查找时不能使用该索引:从tbl_name中选择 *,其中 str_col=1;原因是有许多不同的字符串可以转换为值
1
,例如'1'
、' 1'
或'1a'
。
如有必要,您可以随时将整数CAST
为字符串,以便利用索引:
SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);
警告:如果索引的字符集不匹配,MySQL 也可能跳过索引,即使两个值都CHAR
。如果以下查询不起作用:
SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);
然后,通过运行 show variables like 'character_set_database';
获取数据库字符集,并在 CONVERT
语句中使用它,如下所示(此示例假定您的数据库字符集latin1
- 将其替换为您的值 character_set_database
):
SELECT * FROM example WHERE stuff = CONVERT(200 USING latin1);