我正在嘗試對 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;
uj5u.com熱心網友回復：
你已經得到了答案，但我想我會提供更多的背景資訊。
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html解釋了為什么不使用索引：
對于字串列與數字的比較，MySQL 不能使用列上的索引來快速查找值。如果 str_col 是索引字串列，則在以下陳述句中執行查找時不能使用索引：
SELECT * FROM tbl_name WHERE str_col=1;原因是有許多不同的字串可以轉換為值 1，例如 '1'、'1' 或 '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表明它正在使用索引，進行非唯一查找。
uj5u.com熱心網友回復：
啊，剛剛想通了。該lotNumber欄位是一個 varchar，但我在查詢中將其作為整數輸入。如果我將5556677值放在引號中，則查詢使用索引并且幾乎是即時的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/525240.html