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

Question

我正在尝试对 MySQL 5.7 数据库进行一个非常简单的查询，但查询速度很慢，而且解释显示它没有使用索引，尽管它将它列为可能的键。下面是查询、解释输出和表架构。有任何想法吗？谢谢

查询:SELECT text FROM LogMessages where lotNumber = 5556677

解释输出:

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       | ALL  | idx_LogMessages_lotNumber                                                    | NULL | NULL    | NULL | 35086603 |    10.00 | Using where |
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
1 row in set, 5 warnings (0.07 sec)

表架构:

CREATE TABLE `LogMessages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lotNumber` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idLogMessages_UNIQUE` (`id`),
  KEY `idx_LogMessages_lotNumber` (`lotNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=37545325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer 1

您已经得到了答案，但我想我会提供更多背景信息。

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

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'.

您问题中的 EXPLAIN 报告显示 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 表示它正在使用索引，进行非唯一查找。