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 基礎知識
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 解決方案一
調整查詢條件值：
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過濾查詢結果：
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 文章總結
本文第一進行測試數(shù)據(jù)準備，第二介紹執(zhí)行計劃相關知識，第三介紹索引失效10種場景：查詢類型錯誤，索引列參與運算，錯誤使用通配符，未用到覆蓋索引，OR連接無索引字段，MySQL放棄使用索引，聯(lián)合索引中索引不完整，索引中斷，非等值匹配，最左索引缺失。
以上就是MySQL索引失效十種場景與優(yōu)化方案的詳細內容，更多關于MySQL索引失效的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了mysql中的int(5)到底有是多長，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要給大家介紹了關于MYSQL字符串強轉的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
下面小編就為大家?guī)硪黄狽avicat for MySQL導出表結構腳本的簡單方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-12-12
找到一種不錯的從SQLServer轉成Mysql數(shù)據(jù)庫的方法
找到一種不錯的從SQLServer轉成Mysql數(shù)據(jù)庫的方法...2007-07-07
這篇文章主要介紹了MySQL如何查詢Binlog 生成時間,通過讀取 Binlog FORMAT_DESCRIPTION_EVENT header 時間戳來實現(xiàn)讀取 Binlog 生產時間，本文給大家詳細講解，需要的朋友可以參考下2023-03-03
FEDERATED是其中一個專門針對遠程數(shù)據(jù)庫的實現(xiàn)。一般情況下在本地數(shù)據(jù)庫中建表會在數(shù)據(jù)庫目錄中生成相應的表定義文件，并同時生成相應的數(shù)據(jù)文件2012-12-12
行是表中存儲數(shù)據(jù)的行,也稱為記錄,MySQL的行上限主要受限于兩個因素,表的大小和操作系統(tǒng)的限制,這篇文章主要給大家介紹了關于MySQL表列數(shù)和行大小限制的相關資料,需要的朋友可以參考下2024-04-04
詳解Mysql中保證緩存與數(shù)據(jù)庫的雙寫一致性
在一些高并發(fā)場景下,為了提升系統(tǒng)的性能,我們通常會將數(shù)據(jù)存儲在 Redis 緩存中,并通過 Redis 緩存來提高系統(tǒng)的讀取速度,這篇文章主要介紹了詳解Mysql中保證緩存與數(shù)據(jù)庫的雙寫一致性,需要的朋友可以參考下2024-03-03
淺談MySQL 統(tǒng)計行數(shù)的 count
這篇文章主要介紹了MySQL 統(tǒng)計行數(shù)的 count的相關資料，文中講解非常細致，代碼幫助大家更好的理解和學習，感興趣的朋友可以了解下2020-07-07
windows下MySQL5.6版本安裝及配置過程附有截圖和詳細說明
這篇文章主要介紹了windows下MySQL5.6版本安裝及配置過程附有截圖和詳細說明，需要的朋友可以參考下2013-06-06