为什么 MySQL 不使用索引进行简单的SELECT * FROM Table WHERE field='value'查询

你已经得到了答案，但我想我应该给出更多的背景信息.

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html解释了为什么不使用该索引:

对于字符串列和数字的比较，MySQL不能使用列上的索引来快速查找值.如果str_ol是索引字符串列，则在以下语句中执行查找时不能使用该索引:

SELECT * FROM tbl_name WHERE str_col=1;

这是因为有许多不同的字符串可以转换为值1，如‘1’、‘1’或‘1a’.

您问题中的解释报告显示为type: ALL，这意味着这是一个表扫描.它没有使用索引.

如果我们使用字符串文字，这是一个字符串到字符串的比较，所以它使用索引.

mysql> explain SELECT text FROM LogMessages where lotNumber = '5556677';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+

如果我们在计算结果为字符串值的表达式中使用数字文字，它也使用索引.有几种方法可以做到这一点:

mysql> explain SELECT text FROM LogMessages where lotNumber = 5556677 collate utf8mb4_unicode_ci;
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+

mysql> explain SELECT text FROM LogMessages where lotNumber = cast(5556677 as char);
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+

mysql> explain SELECT text FROM LogMessages where lotNumber = concat(5556677);
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+

在这三个示例中，type: ref表示它正在使用索引，执行非唯一查找.