未建立索引

當數據表沒有設計相關索引時，查詢會掃描全表。

create table test_temp
(test_id     int auto_incrementprimary key,field_1     varchar(20) null,field_2     varchar(20) null,field_3     bigint      null,create_date date        null
);

explain 
select * from test_temp where field_1 = 'testing0';

建議

查詢頻繁是數據表字段增加合適的索引。

查詢結果集是原表中的大部分數據

當數據庫查詢命中索引時，數據庫會首先利用索引列的值定位到對應的數據節點。這個數據節點上記錄了對應數據行的行標識符（Row Identifier）。然而，如果查詢需要獲取該行其他列的數據，就需要進行回表操作。

在回表操作中，數據庫會使用行標識符再次訪問數據節點或磁盤上的實際數據行，以獲取完整的數據。這個過程被稱為回表。回表操作可能會增加額外的磁盤訪問和數據檢索的開銷，因此，在某些情況下，當MySQL判斷回表所需的資源大于直接掃描全表時，它可能選擇不走索引，而是執行全表掃描。

建議

1. 索引覆蓋：酌情考慮創建包含查詢所需列的索引，查詢結果集全部被索引覆蓋，無需回表。
2. 調整查詢語句：查詢必要的列、使用Join語句優化查詢語句，減少回表次數。
3. 當表數據量較大時，需考慮其他存儲服務。

使用函數、隱式轉換

使用函數

隱式轉換

數據準備:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (`id` int NOT NULL,`name` varchar(255) NOT NULL,`price` decimal(10,2) NOT NULL,`description` text,`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`type` tinyint NOT NULL COMMENT '商品類型',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- ----------------------------
-- Records of products
-- ----------------------------
BEGIN;
INSERT INTO `products` VALUES (1, 'Product A', 10.99, 'This is the description for Product A', '2023-08-11 03:47:06', '2023-08-11 03:49:24', 1);
INSERT INTO `products` VALUES (2, 'Product B', 19.99, 'This is the description for Product B', '2023-08-11 03:47:07', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (3, 'Product C', 5.99, 'This is the description for Product C', '2023-08-11 03:47:07', '2023-08-11 03:49:25', 3);
INSERT INTO `products` VALUES (4, 'Product D', 8.49, 'This is the description for Product D', '2023-08-11 03:47:07', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (5, 'Product E', 15.99, 'This is the description for Product E', '2023-08-11 03:47:07', '2023-08-11 03:49:25', 2);
INSERT INTO `products` VALUES (6, 'Product F', 12.99, 'This is the description for Product F', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (7, 'Product G', 7.99, 'This is the description for Product G', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (8, 'Product H', 9.99, 'This is the description for Product H', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (9, 'Product I', 14.99, 'This is the description for Product I', '2023-08-11 03:47:09', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (10, 'Product J', 11.99, 'This is the description for Product J', '2023-08-11 03:47:09', '2023-08-11 03:49:24', 2);
COMMIT;SET FOREIGN_KEY_CHECKS = 1;

增加索引

ALTER TABLE products
ADD INDEX idx_type (type);

復現:

explain
select * from products where type in ('1','2');

由于type是tinyint類型，因此，以上SQL等效為:

SELECT * FROM products WHERE type in CAST('1' AS tinyint,'2' as tinyint);

由于使用了CAST()函數，會導致不走索引的現象。
還有一種情況是：在關聯查詢時，驅動表關聯字段兩者排序規則不一致時也會導致不走索引。

in/not in <>條件導致不走索引

in、not in、<>不走索引的原因是相似的，以下基于in語句分析。
in條件導致不走索引的情況：

in條件過多

explain
select * from products where type in (1,2,3,4,5,6,7);

如果 IN 條件中包含太多的值，超出了數據庫管理系統的限制，它可能會選擇不使用索引。

建議

當in條件中的數據是連續時，可以使用between and代替in。
分而治之，將一次查詢分為多次查詢，最后取并集。
使用UNION語句，類似方案一，只不過該方案是在SQL層面完成。

SELECT column1, column2, ...
FROM your_table
WHERE column IN (value1, value2, ..., valueN)
UNION
SELECT column1, column2, ...
FROM your_table
WHERE column IN (valueN+1, valueN+2, ..., valueM)

統計信息不準確

SHOW ENGINE INNODB STATUS;

該命令會查詢出MySQL Inndb存儲引擎的操作情況，信息包含Innodb各種統計信息:

• Inserts：已插入的行數。
• Updates：已更新的行數。
• Deletes：已刪除的行數。
• Reads：已讀取的行數。

innodb表的統計信息并不是實時統計更新，如果統計信息和實際的索引信息差異很大，就會導致優化器計算各個索引成本后，做出非預期的選擇。出現這種現象的場景是：當有大量數據在短時間內落庫時，Innodb還沒更新統計相關信息，此時來了一個查詢，MySQL會基于歷史數據做出錯誤的判斷：當前表數據量少，不走索引更高效。

建議

基于此問題的解決方案是：手動更新相關統計數據。
請參考：
www.modb.pro/db/46678

like語句

like語句無法命中索引的情況：

前導通配符：%value
通配符在字符串的中間：value%value
通配符"_"出現在開頭

建議

盡量避免在模式的開頭使用前導通配符 %
如果無法避免第一種，根據實際業務和查詢語句考慮使用后綴索引
將通配符 % 放在模式的末尾，以便進行前綴匹配。
如果需要在模式的中間使用通配符 %，可以考慮使用全文搜索引擎或其他更適合模式匹配的技術。
對于固定長度的模式匹配，可以考慮使用其他操作符，如 = 或 <>