我可以在没有经纪索引的情况下将 VARCHAR 投射到 INT 吗?

Question

需要搜索表foo

FOO结构为

ID | 某物

现场something有一个INDEX

我想在 AS INT 之间搜索：

SELECT CAST(something as INT) as something_int FROM foo foo_1
WHERE something_int > 1 AND something_int < 9999

在这种情况下，索引会被使用还是被破坏？

Answer 1

WHERE some_varchar BETWEEN '1' AND '2000'  -- fast but probably incorrect
WHERE some_varchar BETWEEN 1 AND 2000      -- slow but correct
WHERE some_int BETWEEN '1' AND '2000'      -- fast
WHERE some_int BETWEEN 1 AND 2000          -- fast (same as previous)

发生了什么事情？

• 将文本与数字进行比较时，文本端将转换为数字，然后执行数字比较。
文本到
• 文本比较执行字符串比较;数字到数字执行数字比较。
• 上面，我说"慢"的意思是不能使用索引;"快速"(如果可以使用索引(。
• "不正确"的问题与在VARCHAR中对一组数字进行排序然后想知道为什么列表顺序错误相同：1,10,11,...,19,2,20,...,29,3，...
• CAST()只是我在这里谈论的隐式转换的显式版本。
• CAST('2000' TO INT)是在"编译时"完成的，因此优化器将其视为简单的2000(数字，无函数调用(。
• 另一方面，some_varchar >= 1在上面的第二个示例中变成了CAST(some_varchar TO INT) >= 1。
• 根据经验，"在函数调用中隐藏列会阻止使用索引。 请参阅维基百科中的"可优化优化"。

Answer 2

不，不会使用该索引。

CREATE TABLE foo(something varchar(20) primary key) engine=myisam;
INSERT INTO foo VALUES ('1|abc'), ('3456|def');
DESCRIBE SELECT * FROM foo WHERE CAST(something as INT) BETWEEN 1 AND 2000;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | tt    | index | NULL          | PRIMARY | 82      | NULL |    2 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
DESCRIBE SELECT * FROM foo WHERE something BETWEEN '1' AND '2000';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | tt    | range | PRIMARY       | PRIMARY | 82      | NULL |    1 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

请注意第一个查询的possible_keysNULL(找到rows2(。

注意：即使查询的字符集与索引的字符集不匹配，也会发生这种情况。

创建一个单独的 INT 索引(例如，使用函数索引语法(。