目錄

• 1 數據準備
• 1.1 新建數據表
• 1.2 新增100萬條數據
• 2 基礎知識
• 2.1 explain type
• 2.2 explain Extra
• 3 索引失效場景
• 3.1 查詢類型錯誤
• 3.1.1 失效場景
• 3.1.2 解決方案
• 3.2 索引列參與運算
• 3.2.1 失效場景
• 3.2.2 解決方案
• 3.3 MySQL放棄使用索引
• 3.3.1 失效場景
• 3.3.2 解決方案一
• 3.3.3 解決方案二
• 3.4 錯誤使用通配符
• 3.4.1 數據準備
• 3.4.2 失效場景一
• 3.4.3 失效場景二
• 3.4.4 解決方案
• 3.5 OR連接無索引字段
• 3.5.1 失效場景
• 3.5.2 解決方案
• 3.6 未用到覆蓋索引
• 3.6.1 失效場景
• 3.6.2 解決方案
• 3.7 聯合索引失效
• 3.7.1 完整使用
• 3.7.2 失效場景一：索引不完整
• 3.7.3 失效場景二：索引中斷
• 3.7.4 失效場景三：非等值匹配
• 3.7.5 失效場景四：最左索引缺失
• 4 文章總結

1 數據準備

1.1 新建數據表

CREATE TABLE `player` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "主鍵",  `player_id` varchar(256) NOT NULL COMMENT "運動員編號",  `player_name` varchar(256) NOT NULL COMMENT "運動員名稱",  `height` int(11) NOT NULL COMMENT "身高",  `weight` int(11) NOT NULL COMMENT "體重",  `type` varchar(256) DEFAULT "0" COMMENT "球員類型",  `game_performance` text COMMENT "最近一場比賽表現",  PRIMARY KEY (`id`),  KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),  KEY `idx_type` (`type`),  KEY `idx_height` (`height`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

以上數據表聲明三個索引：

• 聯合索引：idx_name_height_weight
• 普通索引：idx_type
• 普通索引：idx_height

1.2 新增100萬條數據

@SpringBootTest(classes = TestApplication.class)@RunWith(SpringJUnit4ClassRunner.class)public class PlayerServiceTest {    @Resource    private PlayerRepository playerRepository;    @Test    public void initBigData() {for (int i = 0; i < 1000000; i++) {    PlayerEntity entity = new PlayerEntity();    entity.setPlayerId(UUID.randomUUID().toString());    entity.setPlayerName("球員_" + System.currentTimeMillis());    entity.setType("0");    entity.setWeight(150);    entity.setHeight(188);    entity.setGamePerformance("{\"runDistance\":8900.0,\"passSuccess\":80.12,\"scoreNum\":3}");    playerRepository.insert(entity);}    }}

2 基礎知識

2.1 explain type

執行計劃中訪問類型是重要分析指標：

2.2 explain Extra

Extra表示執行計劃擴展信息：

3 索引失效場景

本章節介紹索引失效十種場景：

• 查詢類型錯誤
• 索引列參與運算
• 錯誤使用通配符
• 未用到覆蓋索引
• OR連接無索引字段
• MySQL放棄使用索引
• 聯合索引失效
  • 索引不完整
  • 索引中斷
  • 非等值匹配
  • 最左索引缺失

3.1 查詢類型錯誤

3.1.1 失效場景

explain select * from player where type = 0

3.1.2 解決方案

數據表定義type字段為varchar類型，查詢必須使用相同類型：

3.2 索引列參與運算

3.2.1 失效場景

explain select * from player where height + 1 > 189

3.2.2 解決方案

explain select * from player where height > 188

3.3 MySQL放棄使用索引

3.3.1 失效場景

MySQL發現如果使用索引性能低于全表掃描則放棄使用索引。例如在表中100萬條數據height字段值全部是188，所以執行如下語句時放棄使用索引：

explain select * from player where height > 187

3.3.2 解決方案一

調整查詢條件值：

explain select * from player where height > 188

3.3.3 解決方案二

強制指定索引，這種方法不一定可以提升性能：

3.4 錯誤使用通配符

3.4.1 數據準備

避免出現3.3章節失效問題此處修改一條數據：

update player set player_name = "測試球員" where id = 1

3.4.2 失效場景一

explain select * from player where player_name like "%測試"

3.4.3 失效場景二

explain select * from player where player_name like "%測試%"

3.4.4 解決方案

explain select * from player where player_name like "測試%"

3.5 OR連接無索引字段

3.5.1 失效場景

type有索引，weight無索引：

explain select * from player where type = "0" or weight = 150

3.5.2 解決方案

weight新增索引，union拼裝查詢數據

explainselect * from player where type = "0" unionselect * from player where weight = 150

3.6 未用到覆蓋索引

3.6.1 失效場景

Using index condition表示使用索引，但是需要回表查詢

explain select * from player where player_name like "測試%"

3.6.2 解決方案

覆蓋索引含義是查詢時索引列完全包含查詢列，查詢過程無須回表（需要在同一棵索引樹）性能得到提升。Using Index; Using where表示使用覆蓋索引并且用where過濾查詢結果：

explain select id,player_name,height,weight from player where player_name like "測試%"

3.7 聯合索引失效

3.7.1 完整使用

聯合索引idx_name_height_weight完整使用key_len=778：

explain select * from player where player_name = "球員_1682577684751" and height = 188 and weight = 150

3.7.2 失效場景一：索引不完整

weight不在查詢條件，所以只用到idx_name_height，所以key_len= 774：

explain select * from player where player_name = "球員_1682577684751" and height = 188

3.7.3 失效場景二：索引中斷

height不在查詢條件，所以只用到idx_name，所以key_len= 770：

explain select * from player where player_name = "球員_1682577684751" and weight = 150

3.7.4 失效場景三：非等值匹配

height非等值匹配，所以只用到idx_name_height，所以key_length=774：

explain select * from player where player_name="球員_1682577684751" and height > 188 and weight = 150

3.7.5 失效場景四：最左索引缺失

player_name最左索引不在查詢條件，全表掃描

explain select * from player where weight = 150

4 文章總結

本文第一進行測試數據準備，第二介紹執行計劃相關知識，第三介紹索引失效10種場景：查詢類型錯誤，索引列參與運算，錯誤使用通配符，未用到覆蓋索引，OR連接無索引字段，MySQL放棄使用索引，聯合索引中索引不完整，索引中斷，非等值匹配，最左索引缺失。

以上就是MySQL索引失效十種場景與優化方案的詳細內容，更多關于MySQL索引失效的資料請關注其它相關文章！