文章詳情頁(yè)

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

瀏覽：194日期：2023-05-08 10:17:46

1 數(shù)據(jù)準(zhǔn)備

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

CREATE TABLE `player` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "主鍵",  `player_id` varchar(256) NOT NULL COMMENT "運(yùn)動(dòng)員編號(hào)",  `player_name` varchar(256) NOT NULL COMMENT "運(yùn)動(dòng)員名稱",  `height` int(11) NOT NULL COMMENT "身高",  `weight` int(11) NOT NULL COMMENT "體重",  `type` varchar(256) DEFAULT "0" COMMENT "球員類型",  `game_performance` text COMMENT "最近一場(chǎng)比賽表現(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ù)表聲明三個(gè)索引：

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

1.2 新增100萬(wàn)條數(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ǔ)知識(shí)

2.1 explain type

執(zhí)行計(jì)劃中訪問(wèn)類型是重要分析指標(biāo)：

2.2 explain Extra

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

3 索引失效場(chǎng)景

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

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

3.1 查詢類型錯(cuò)誤

3.1.1 失效場(chǎng)景

explain select * from player where type = 0

3.1.2 解決方案

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

3.2 索引列參與運(yùn)算

3.2.1 失效場(chǎng)景

explain select * from player where height + 1 > 189

3.2.2 解決方案

explain select * from player where height > 188

3.3 MySQL放棄使用索引

3.3.1 失效場(chǎng)景

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

explain select * from player where height > 187

3.3.2 解決方案一

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

explain select * from player where height > 188

3.3.3 解決方案二

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

3.4 錯(cuò)誤使用通配符

3.4.1 數(shù)據(jù)準(zhǔn)備

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

update player set player_name = "測(cè)試球員" where id = 1

3.4.2 失效場(chǎng)景一

explain select * from player where player_name like "%測(cè)試"

3.4.3 失效場(chǎng)景二

explain select * from player where player_name like "%測(cè)試%"

3.4.4 解決方案

explain select * from player where player_name like "測(cè)試%"

3.5 OR連接無(wú)索引字段

3.5.1 失效場(chǎng)景

type有索引，weight無(wú)索引：

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

3.5.2 解決方案

weight新增索引，union拼裝查詢數(shù)據(jù)

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

3.6 未用到覆蓋索引

3.6.1 失效場(chǎng)景

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

explain select * from player where player_name like "測(cè)試%"

3.6.2 解決方案

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

explain select id,player_name,height,weight from player where player_name like "測(cè)試%"

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 失效場(chǎng)景一：索引不完整

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

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

3.7.3 失效場(chǎng)景二：索引中斷

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

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

3.7.4 失效場(chǎng)景三：非等值匹配

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

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

3.7.5 失效場(chǎng)景四：最左索引缺失

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

explain select * from player where weight = 150

4 文章總結(jié)

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

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

MySQL

上一條：MySQL獲取binlog的開始時(shí)間和結(jié)束時(shí)間(最新方法)下一條：MySQL中的 inner join 和 left join的區(qū)別解析(小結(jié)果集驅(qū)動(dòng)大結(jié)果集)

相關(guān)文章：

1. MySQL索引優(yōu)化Explain詳解

排行榜

					
					Mysql入門系列：安排預(yù)防性的維護(hù)MYSQL數(shù)據(jù)庫(kù)服務(wù)器
Oracle數(shù)據(jù)庫(kù)物理文件大小的限制簡(jiǎn)介
用腳本和查詢主動(dòng)監(jiān)視Oracle 9i性能
SQL Server數(shù)據(jù)庫(kù)開發(fā)之觸發(fā)器的實(shí)際應(yīng)用
Windows下在DOS用mysql命令行導(dǎo)入.sql文件
MySQL 8.0新特性 — 管理端口的使用簡(jiǎn)介
Windows下不能啟動(dòng)mysql服務(wù)--錯(cuò)誤總結(jié)
MySQL的binlog日志使用詳解
MySQL慢查詢以及解決方案詳解
Oracle 10g DBMS_SCHEDULER的中度解析
Oracle和MySQL的一些簡(jiǎn)單命令對(duì)比
				