快捷導(dǎo)航

MySQL索引失效十種場景與優(yōu)化方案

更新時間：2023年05月07日 08:48:20 作者：JAVA前線

這篇文章主要介紹了MySQL索引失效十種場景與優(yōu)化方案，文中有詳細的代碼示例供參考閱讀，感興趣的朋友可以看一下

1 數(shù)據(jù)準備

1.1 新建數(shù)據(jù)表

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 '最近一場比賽表現(xiàn)',
  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

以上數(shù)據(jù)表聲明三個索引：

聯(lián)合索引：idx_name_height_weight
普通索引：idx_type
普通索引：idx_height

1.2 新增100萬條數(shù)據(jù)

@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 基礎(chǔ)知識

2.1 explain type

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

2.2 explain Extra

Extra表示執(zhí)行計劃擴展信息：

3 索引失效場景

本章節(jié)介紹索引失效十種場景：

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

3.1 查詢類型錯誤

3.1.1 失效場景

explain select * from player where type = 0

3.1.2 解決方案

數(shù)據(jù)表定義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發(fā)現(xiàn)如果使用索引性能低于全表掃描則放棄使用索引。例如在表中100萬條數(shù)據(jù)height字段值全部是188，所以執(zhí)行如下語句時放棄使用索引：

explain select * from player where height > 187

3.3.2 解決方案一

調(diào)整查詢條件值：

explain select * from player where height > 188

3.3.3 解決方案二

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

3.4 錯誤使用通配符

3.4.1 數(shù)據(jù)準備

避免出現(xiàn)3.3章節(jié)失效問題此處修改一條數(shù)據(jù)：

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拼裝查詢數(shù)據(jù)

explain
select * from player where type = '0' 
union
select * 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過濾查詢結(jié)果：

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

3.7 聯(lián)合索引失效

3.7.1 完整使用

聯(lián)合索引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 文章總結(jié)

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

以上就是MySQL索引失效十種場景與優(yōu)化方案的詳細內(nèi)容，更多關(guān)于MySQL索引失效的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片