前面我們已經(jīng)完整攻克了整套緩存體系：從緩存雙寫一致性的3種落地策略、Caffeine本地緩存與Redis分布式緩存的多級(jí)架構(gòu)整合，到分布式多實(shí)例緩存同步的Redis發(fā)布訂閱方案，每一步都貼合企業(yè)級(jí)高并發(fā)落地標(biāo)準(zhǔn)。

緩存作為系統(tǒng)性能優(yōu)化的“上層手段”，核心作用是減輕數(shù)據(jù)庫(kù)的查詢壓力、縮短接口響應(yīng)時(shí)間，但很多開發(fā)同學(xué)會(huì)陷入一個(gè)致命誤區(qū)：只要加了緩存，系統(tǒng)性能就一定能達(dá)標(biāo)。

實(shí)際上，在企業(yè)真實(shí)生產(chǎn)項(xiàng)目中，80%以上的系統(tǒng)性能瓶頸，根源都不在緩存，而在底層MySQL數(shù)據(jù)庫(kù)本身。小數(shù)據(jù)量（幾萬(wàn)條以內(nèi)）場(chǎng)景下，哪怕是全表掃描、劣質(zhì)SQL，也能做到毫秒級(jí)響應(yīng)，看不出任何問(wèn)題；但一旦單表數(shù)據(jù)量突破幾十萬(wàn)、幾百萬(wàn)，甚至上千萬(wàn)，沒(méi)有合理的索引、不規(guī)范的SQL寫法、大量慢查詢，會(huì)直接導(dǎo)致接口耗時(shí)從幾十毫秒飆升到幾百毫秒、幾秒，甚至幾十秒，MySQL的CPU、磁盤IO會(huì)被直接打滿，數(shù)據(jù)庫(kù)連接池耗盡。

更關(guān)鍵的是：哪怕你的緩存架構(gòu)設(shè)計(jì)得再完美，底層數(shù)據(jù)庫(kù)本身扛不住流量，緩存也會(huì)失去意義——一旦緩存失效、擊穿，海量請(qǐng)求會(huì)瞬間涌入早已不堪重負(fù)的數(shù)據(jù)庫(kù)，直接引發(fā)系統(tǒng)雪崩。

這里必須強(qiáng)調(diào)一個(gè)性能優(yōu)化的核心優(yōu)先級(jí)：先優(yōu)化SQL與數(shù)據(jù)庫(kù)索引，再做緩存優(yōu)化，最后做架構(gòu)層面（分庫(kù)分表、讀寫分離）的優(yōu)化。數(shù)據(jù)庫(kù)是系統(tǒng)性能的“根基”，只有把根基打牢，緩存才能真正發(fā)揮最大價(jià)值，否則一切都是空中樓閣。

一、SpringBoot 環(huán)境下開啟MySQL慢查詢?nèi)罩?/h2>

想要優(yōu)化慢查詢，第一步永遠(yuǎn)是“精準(zhǔn)定位慢SQL”——只有找到所有執(zhí)行耗時(shí)過(guò)長(zhǎng)的SQL，才能針對(duì)性地進(jìn)行優(yōu)化。MySQL內(nèi)置了完善的慢查詢?nèi)罩竟δ?，能夠自?dòng)記錄所有超過(guò)閾值的SQL，包含完整的執(zhí)行明細(xì)，是線上排查慢查詢的最權(quán)威工具。

結(jié)合SpringBoot項(xiàng)目的開發(fā)、生產(chǎn)環(huán)境，我們提供兩種配置方式，適配不同場(chǎng)景，全部可直接復(fù)制落地。

1. 方式一：SQL動(dòng)態(tài)配置（線上推薦，無(wú)需重啟MySQL）

線上生產(chǎn)環(huán)境禁止隨意重啟MySQL服務(wù)（重啟會(huì)導(dǎo)致服務(wù)中斷），因此我們優(yōu)先使用SQL命令動(dòng)態(tài)開啟慢查詢?nèi)罩?，即時(shí)生效，無(wú)需重啟數(shù)據(jù)庫(kù)，排查完成后還可以動(dòng)態(tài)關(guān)閉，不影響線上服務(wù)。

完整動(dòng)態(tài)配置SQL

-- 1. 開啟慢查詢?nèi)罩荆?=開啟，0=關(guān)閉），全局生效
SETGLOBAL slow_query_log =ON;
-- 2. 設(shè)置慢查詢閾值，單位：秒，這里設(shè)置為0.5秒（500ms），適配普通業(yè)務(wù)接口
SETGLOBAL long_query_time =0.5;
-- 3. 開啟“記錄未使用索引的查詢”（非常關(guān)鍵！即使SQL執(zhí)行耗時(shí)未超過(guò)閾值，只要沒(méi)走索引，也會(huì)被記錄）
-- 避免遺漏“隱性慢查詢”（比如數(shù)據(jù)量增長(zhǎng)后，未走索引的SQL會(huì)逐漸變成慢查詢）
SETGLOBAL log_queries_not_using_indexes =ON;
-- 4. 設(shè)置慢查詢?nèi)罩镜拇鎯?chǔ)路徑（可選，默認(rèn)路徑可通過(guò)SHOW VARIABLES查看）
-- 注意：路徑需確保MySQL用戶有讀寫權(quán)限，避免日志無(wú)法生成
SETGLOBAL slow_query_log_file ='/var/lib/mysql/slow.log';
-- 5. 設(shè)置日志輸出格式（可選，默認(rèn)FILE，即輸出到文件；可設(shè)置為TABLE，存儲(chǔ)到mysql.slow_log表中）
SETGLOBAL log_output ='FILE,TABLE';
-- 6. 查看所有慢查詢配置是否生效（驗(yàn)證配置）
SHOW VARIABLES LIKE'%slow_query%';  -- 查看慢查詢?nèi)罩鞠嚓P(guān)配置
SHOW VARIABLES LIKE'long_query_time';  -- 查看慢查詢閾值
SHOW VARIABLES LIKE'log_queries_not_using_indexes';  -- 查看未走索引查詢的記錄配置

注意事項(xiàng)

• 修改 GLOBAL 全局參數(shù)后，需要重新斷開數(shù)據(jù)庫(kù)連接（比如重啟SpringBoot服務(wù)、重新連接Navicat），新的連接才會(huì)加載最新的配置；已存在的連接，依然使用舊的配置。
• 線上環(huán)境排查完成后，建議關(guān)閉“記錄未使用索引的查詢”（SET GLOBAL log_queries_not_using_indexes = OFF），避免大量無(wú)索引的普通SQL占用日志空間，影響慢查詢?nèi)罩镜目勺x性。
• 如果慢查詢?nèi)罩疚募^(guò)大（超過(guò)1G），可以使用 mysqldumpslow 工具進(jìn)行分析，或者手動(dòng)清空日志（echo "" > /var/lib/mysql/slow.log），避免占用過(guò)多磁盤空間。

2. 方式二：修改MySQL配置文件（永久生效，適合開發(fā)/測(cè)試環(huán)境）

適合開發(fā)環(huán)境、測(cè)試環(huán)境，或者新項(xiàng)目初始化配置，修改MySQL的配置文件后，重啟MySQL服務(wù)即可永久生效，無(wú)需每次手動(dòng)執(zhí)行SQL配置。

不同系統(tǒng)的配置文件路徑

• Linux系統(tǒng)（CentOS、Ubuntu）：/etc/my.cnf 或 /etc/mysql/my.cnf；
• Windows系統(tǒng)：MySQL安裝目錄/my.ini（比如 C:Program FilesMySQLMySQL Server 8.0my.ini）；
• Docker部署的MySQL：需要掛載配置文件，或者進(jìn)入容器內(nèi)部修改 /etc/my.cnf。

完整配置內(nèi)容

[mysqld]
# 開啟慢查詢?nèi)罩荆ū靥睿?
slow_query_log = ON
# 慢查詢閾值，單位：秒，設(shè)置為0.5秒（500ms）（必填）
long_query_time = 0.5
# 慢查詢?nèi)罩敬鎯?chǔ)路徑（必填，確保路徑可寫）
slow_query_log_file = /var/lib/mysql/slow.log
# 記錄所有未使用索引的查詢（開發(fā)環(huán)境建議開啟，線上排查時(shí)開啟，平時(shí)可關(guān)閉）
log_queries_not_using_indexes = ON
# 日志輸出格式：FILE（輸出到文件）+ TABLE（存儲(chǔ)到mysql.slow_log表），方便多方式查看
log_output = FILE,TABLE
# 忽略系統(tǒng)數(shù)據(jù)庫(kù)（mysql、information_schema等）的慢查詢，避免日志冗余
ignore_db_dirs = mysql,information_schema,performance_schema,sys
# 記錄慢查詢的詳細(xì)信息（可選，默認(rèn)開啟）
log_slow_admin_statements = ON# 記錄管理員操作中的慢查詢（如alter table）
log_slow_slave_statements = ON# 主從復(fù)制場(chǎng)景下，記錄從庫(kù)的慢查詢

配置生效步驟

1. 修改配置文件后，保存退出；

2. 重啟MySQL服務(wù)（不同系統(tǒng)重啟命令不同）：

• Linux（CentOS）：systemctl restart mysqld；
• Linux（Ubuntu）：systemctl restart mysql；
• Windows：在服務(wù)中找到“MySQL”，右鍵重啟；
• Docker：docker restart 容器ID。

3. 重啟后，連接MySQL，執(zhí)行 SHOW VARIABLES LIKE '%slow_query%'，驗(yàn)證配置是否生效。

3. SpringBoot 項(xiàng)目配置：打印SQL執(zhí)行日志（本地開發(fā)調(diào)試）

開發(fā)環(huán)境中，我們可以通過(guò)配置SpringBoot的日志，直接打印SQL的執(zhí)行語(yǔ)句、執(zhí)行耗時(shí)，方便本地快速排查慢SQL，無(wú)需依賴MySQL的慢查詢?nèi)罩尽?/p>

以下配置適配MyBatis、MyBatis-Plus，復(fù)制到 application.yml 即可生效：

spring:
  datasource:
    # 數(shù)據(jù)庫(kù)連接配置（替換為自己的數(shù)據(jù)庫(kù)信息）
    url:jdbc:mysql://localhost:3306/springboot_demo?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username:root
    password:123456
    driver-class-name:com.mysql.cj.jdbc.Driver
# MyBatis-Plus 配置（如果使用原生MyBatis，配置類似）
mybatis-plus:
mapper-locations:classpath:mapper/*.xml# mapper文件路徑
type-aliases-package:com.xxx.entity# 實(shí)體類包路徑
configuration:
    # 打印完整SQL語(yǔ)句、執(zhí)行耗時(shí)（本地開發(fā)開啟，線上關(guān)閉）
    log-impl:org.apache.ibatis.logging.stdout.StdOutImpl
    # 開啟駝峰命名映射（可選，避免字段名與實(shí)體類屬性不匹配）
    map-underscore-to-camel-case:true
# 日志配置（可選，細(xì)化日志輸出，避免冗余）
logging:
level:
    # 打印指定包下的SQL日志（替換為自己的mapper包路徑）
    com.xxx.mapper:debug
    # 關(guān)閉其他無(wú)關(guān)日志，提升可讀性
    org.springframework:warn
    com.baomidou.mybatisplus:warn

配置生效后，啟動(dòng)SpringBoot項(xiàng)目，執(zhí)行接口請(qǐng)求，控制臺(tái)會(huì)輸出類似如下日志，清晰看到SQL執(zhí)行耗時(shí)：

==>  Preparing: SELECT id,name,age,create_time FROM user WHERE id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, age, create_time
<==        Row: 1, 張三, 25, 2024-01-01 10:00:00
<==      Total: 1
<==  Updates: 0
<==   Elapsed: 12.35 ms  # 執(zhí)行耗時(shí)，一目了然

4. 慢查詢?nèi)罩静榭磁c分析工具

慢查詢?nèi)罩旧珊?，我們需要?duì)日志進(jìn)行分析，提取出耗時(shí)最長(zhǎng)、執(zhí)行最頻繁的慢SQL，針對(duì)性優(yōu)化。這里推薦4種常用工具，適配不同場(chǎng)景。

（1）原生日志查看

直接通過(guò)命令行查看慢查詢?nèi)罩荆m合線上臨時(shí)排查，無(wú)需安裝額外工具：

# 1. 實(shí)時(shí)查看慢查詢?nèi)罩荆ㄗ钚碌穆齋QL會(huì)實(shí)時(shí)輸出）
tail -f /var/lib/mysql/slow.log
# 2. 查看日志的前10行（快速了解日志格式）
head -n 10 /var/lib/mysql/slow.log
# 3. 統(tǒng)計(jì)日志中所有慢SQL的數(shù)量
grep -c "Query_time" /var/lib/mysql/slow.log
# 4. 查找耗時(shí)超過(guò)1秒的慢SQL
grep "Query_time>1" /var/lib/mysql/slow.log

（2）mysqldumpslow

MySQL自帶的慢查詢?nèi)罩痉治龉ぞ?，無(wú)需額外安裝，能夠?qū)β齋QL進(jìn)行匯總、排序，快速找到最耗時(shí)、最頻繁的慢SQL，線上最常用。

常用命令（復(fù)制可用）：

# 1. 按執(zhí)行耗時(shí)排序，查看耗時(shí)最高的10條慢SQL（最常用）
mysqldumpslow -s t -n 10 /var/lib/mysql/slow.log
# 2. 按執(zhí)行次數(shù)排序，查看最頻繁執(zhí)行的10條慢SQL
mysqldumpslow -s c -n 10 /var/lib/mysql/slow.log
# 3. 按鎖定時(shí)間排序，查看鎖定時(shí)間最長(zhǎng)的10條慢SQL
mysqldumpslow -s l -n 10 /var/lib/mysql/slow.log
# 4. 過(guò)濾指定數(shù)據(jù)庫(kù)的慢SQL（比如只查看springboot_demo庫(kù)的慢SQL）
mysqldumpslow -d springboot_demo /var/lib/mysql/slow.log
# 5. 輸出詳細(xì)的慢SQL信息（包含執(zhí)行時(shí)間、掃描行數(shù)、返回行數(shù)）
mysqldumpslow -v /var/lib/mysql/slow.log

命令參數(shù)說(shuō)明：-s 表示排序方式（t=耗時(shí)、c=次數(shù)、l=鎖定時(shí)間），-n 表示顯示的條數(shù)，-d 表示指定數(shù)據(jù)庫(kù)，-v 表示顯示詳細(xì)信息。

（3）pt-query-digest

Percona Toolkit中的核心工具，比 mysqldumpslow 功能更強(qiáng)大，能夠?qū)β樵內(nèi)罩具M(jìn)行深度分析，生成詳細(xì)的統(tǒng)計(jì)報(bào)告，適合慢SQL數(shù)量多、場(chǎng)景復(fù)雜的線上環(huán)境。

安裝命令（Linux）：yum install percona-toolkit -y（CentOS）、apt install percona-toolkit -y（Ubuntu）。

常用命令：

# 分析慢查詢?nèi)罩?，生成詳?xì)報(bào)告（輸出到屏幕）
pt-query-digest /var/lib/mysql/slow.log
# 分析慢查詢?nèi)罩荆瑢?bào)告輸出到文件（方便后續(xù)查看）
pt-query-digest /var/lib/mysql/slow.log > slow_query_analysis.log

報(bào)告核心信息：會(huì)按SQL執(zhí)行頻率、耗時(shí)排序，標(biāo)注每條SQL的掃描行數(shù)、返回行數(shù)、執(zhí)行用戶、執(zhí)行時(shí)間，甚至?xí)o出優(yōu)化建議，非常實(shí)用。

（4）可視化工具

開發(fā)環(huán)境中，我們可以使用可視化工具查看慢查詢?nèi)罩?，操作?jiǎn)單、直觀：

注意：線上環(huán)境建議使用 mysqldumpslow 或 pt-query-digest 分析慢查詢?nèi)罩?，避免使用可視化工具（需要連接線上數(shù)據(jù)庫(kù)，存在安全風(fēng)險(xiǎn)，且可能占用數(shù)據(jù)庫(kù)資源）。

二、Explain 執(zhí)行計(jì)劃全字段詳解

找到慢SQL后，下一步就是分析“為什么這條SQL執(zhí)行慢”——核心工具就是 explain 執(zhí)行計(jì)劃。

explain 是MySQL提供的一個(gè)核心命令，在SQL語(yǔ)句前加上 explain，可以查看MySQL優(yōu)化器對(duì)這條SQL的執(zhí)行計(jì)劃，包括：SQL的執(zhí)行方式（全表掃描還是索引掃描）、使用了哪個(gè)索引、掃描了多少行數(shù)據(jù)、返回多少行數(shù)據(jù)、是否使用了臨時(shí)表、是否進(jìn)行了排序等關(guān)鍵信息。

掌握 explain 的使用，是區(qū)分“新手”和“資深開發(fā)者”的關(guān)鍵，也是面試高頻考點(diǎn)。下面我們結(jié)合SpringBoot項(xiàng)目中的真實(shí)SQL，逐字段詳解 explain 執(zhí)行計(jì)劃，確保每個(gè)人都能看懂、會(huì)用。

1. Explain 基本使用方法

使用非常簡(jiǎn)單，在需要分析的SQL語(yǔ)句前加上 explain 即可，示例：

-- 分析單表查詢
EXPLAIN SELECT id, name, age FROMuserWHERE age >20;
-- 分析多表關(guān)聯(lián)查詢
EXPLAIN SELECT u.id, u.name, o.order_no FROMuser u LEFTJOIN `order` o ON u.id = o.user_id WHERE u.age >20;
-- 分析更新、刪除語(yǔ)句（查看執(zhí)行計(jì)劃，判斷是否走索引）
EXPLAIN UPDATEuserSET name ='李四'WHERE id =1;

執(zhí)行后，MySQL會(huì)返回一個(gè)包含12個(gè)字段的表格，每個(gè)字段都對(duì)應(yīng)SQL執(zhí)行的關(guān)鍵信息，我們逐一拆解。

2. Explain 12個(gè)字段逐字詳解

我們以SpringBoot項(xiàng)目中的商品表（product）為例，表結(jié)構(gòu)如下（復(fù)制可創(chuàng)建）：

CREATE TABLE `product` (
  `id` bigintNOT NULL AUTO_INCREMENT COMMENT '商品ID（主鍵）',
  `name` varchar(100) NOT NULL COMMENT '商品名稱',
  `category_id` bigintNOT NULL COMMENT '分類ID',
  `price` decimal(10,2) NOT NULL COMMENT '商品價(jià)格',
  `stock` intNOT NULL COMMENT '庫(kù)存',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime NOT NULL COMMENT '更新時(shí)間',
PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),  -- 分類ID索引
  KEY `idx_create_time` (`create_time`)  -- 創(chuàng)建時(shí)間索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

我們以這條SQL為例，分析 explain 執(zhí)行計(jì)劃：EXPLAIN SELECT id, name, price FROM product WHERE category_id = 10 AND create_time > '2024-01-01'

執(zhí)行后返回的執(zhí)行計(jì)劃表格，以及每個(gè)字段的詳細(xì)說(shuō)明如下（重點(diǎn)字段標(biāo)紅）：

（1）id：SQL執(zhí)行的順序標(biāo)識(shí)

核心作用：標(biāo)識(shí)SQL語(yǔ)句中每個(gè)查詢塊的執(zhí)行順序，有以下三種情況：

示例中，id=1，說(shuō)明只有一個(gè)查詢塊，按順序執(zhí)行即可。

（2）select_type：查詢類型

標(biāo)識(shí)當(dāng)前查詢的類型，決定了查詢的復(fù)雜度和執(zhí)行方式，常見(jiàn)值及說(shuō)明（重點(diǎn)記前5個(gè)）：

示例中，select_type=SIMPLE，說(shuō)明是簡(jiǎn)單查詢，無(wú)復(fù)雜嵌套。

（3）table：當(dāng)前查詢涉及的表

顯示當(dāng)前查詢塊正在操作的表名，如果是子查詢、派生表，會(huì)顯示臨時(shí)表的名稱（如derived2、union1）。

示例中，table=product，說(shuō)明當(dāng)前查詢操作的是商品表。

（4）type：訪問(wèn)類型（判斷是否走索引）

這是 explain 中最核心的字段，標(biāo)識(shí)MySQL訪問(wèn)表的方式，即“如何獲取數(shù)據(jù)”，決定了查詢的效率，按效率從高到低排序（重點(diǎn)記前6個(gè)）：

面試必背：type 字段的優(yōu)化目標(biāo)是“至少達(dá)到 range 級(jí)別，最好達(dá)到 ref 或 const 級(jí)別”，如果出現(xiàn) ALL（全表掃描），說(shuō)明沒(méi)有走索引，需要優(yōu)先優(yōu)化。

示例中，type=ref，說(shuō)明通過(guò)普通索引 idx_category_id 查詢，效率較高。

（5）possible_keys：可能使用的索引

顯示MySQL優(yōu)化器認(rèn)為當(dāng)前查詢“可能”使用的索引，不一定會(huì)實(shí)際使用（可能有多個(gè)，用逗號(hào)分隔）。

示例中，possible_keys=idx_category_id,idx_create_time，說(shuō)明優(yōu)化器認(rèn)為可能使用分類ID索引或創(chuàng)建時(shí)間索引。

（6）key：實(shí)際使用的索引

顯示MySQL優(yōu)化器實(shí)際使用的索引，如果為NULL，說(shuō)明沒(méi)有使用任何索引（走全表掃描）。

示例中，key=idx_category_id，說(shuō)明實(shí)際使用的是分類ID索引，與possible_keys中的一個(gè)一致。

關(guān)鍵注意：如果 possible_keys 有值，但 key 為 NULL，說(shuō)明索引建立不合理，或者SQL寫法有問(wèn)題，導(dǎo)致優(yōu)化器放棄使用索引。

（7）key_len：實(shí)際使用的索引長(zhǎng)度（單位：字節(jié)）

核心作用：判斷索引的使用情況，尤其是聯(lián)合索引，通過(guò)key_len可以判斷聯(lián)合索引使用了哪些字段（遵循最左前綴匹配原則）。

計(jì)算規(guī)則（簡(jiǎn)單記）：
varchar(100)：utf8mb4編碼，每個(gè)字符占4字節(jié)，100*4=400字節(jié)，加上null標(biāo)識(shí)（1字節(jié)），共401字節(jié)；bigint：8字節(jié)，int：4字節(jié)，datetime：8字節(jié)；如果字段為NOT NULL，不需要null標(biāo)識(shí)，減少1字節(jié)。

示例中，key=idx_category_id（category_id是bigint NOT NULL），key_len=8，符合計(jì)算規(guī)則，說(shuō)明索引使用正常。

（8）ref：與索引匹配的列或常量

顯示與當(dāng)前使用的索引匹配的列名，或者常量值，說(shuō)明索引是如何被使用的。

示例中，ref=const，說(shuō)明category_id=10（常量），與索引idx_category_id匹配，符合查詢條件。

（9）rows：MySQL預(yù)估要掃描的行數(shù)

顯示MySQL優(yōu)化器預(yù)估的、需要掃描的行數(shù)，不是實(shí)際掃描的行數(shù)，但能反映查詢的效率——行數(shù)越少，查詢效率越高。

示例中，rows=100，說(shuō)明優(yōu)化器預(yù)估需要掃描100行數(shù)據(jù)就能找到符合條件的結(jié)果；如果rows=100000，說(shuō)明需要掃描10萬(wàn)行數(shù)據(jù)，效率極低，大概率是全表掃描。

關(guān)鍵注意：如果rows數(shù)值很大，但實(shí)際返回的行數(shù)很少，說(shuō)明索引建立不合理，或者查詢條件過(guò)濾性差，需要優(yōu)化。

（10）Extra：額外信息

這是 explain 中最靈活、最有價(jià)值的字段，包含了SQL執(zhí)行的額外細(xì)節(jié)，很多慢查詢的問(wèn)題都能從這里找到原因，常見(jiàn)值及說(shuō)明（重點(diǎn)記紅框內(nèi)的）：

? 理想狀態(tài)（優(yōu)化到位）：

Using index：使用了覆蓋索引（查詢的字段都在索引中，無(wú)需回表查詢數(shù)據(jù)），效率極高，是優(yōu)化的目標(biāo)；

Using where：使用了where條件過(guò)濾數(shù)據(jù)，過(guò)濾效果較好；

Using index condition：使用了索引條件推送（ICP），減少回表查詢的次數(shù)，提升效率。

? 需要優(yōu)化的狀態(tài)（慢查詢常見(jiàn)）：

Using filesort：無(wú)法使用索引排序，需要在磁盤或內(nèi)存中進(jìn)行排序（文件排序），耗時(shí)極長(zhǎng)，尤其是數(shù)據(jù)量大時(shí)；

Using temporary：需要?jiǎng)?chuàng)建臨時(shí)表存儲(chǔ)查詢結(jié)果，再進(jìn)行后續(xù)操作（比如group by、distinct、union），耗時(shí)較長(zhǎng)；

Using join buffer：多表關(guān)聯(lián)時(shí)，沒(méi)有使用索引，需要使用連接緩沖區(qū)存儲(chǔ)關(guān)聯(lián)數(shù)據(jù)，效率低；

Using where; Using filesort：使用了where過(guò)濾，但排序沒(méi)有使用索引，需要優(yōu)化排序字段的索引；

Using where; Using temporary; Using filesort：最糟糕的情況，需要?jiǎng)?chuàng)建臨時(shí)表、進(jìn)行文件排序，必須優(yōu)先優(yōu)化。

示例中，Extra=Using index condition; Using where，說(shuō)明使用了索引條件推送和where過(guò)濾，執(zhí)行效率較好。

（11）filtered：過(guò)濾比例（百分比）

顯示經(jīng)過(guò)where條件過(guò)濾后，剩余數(shù)據(jù)占總掃描行數(shù)的比例，比例越高，說(shuō)明過(guò)濾效果越好（查詢條件越精準(zhǔn)）。

示例中，filtered=80，說(shuō)明經(jīng)過(guò)where條件過(guò)濾后，剩余80%的掃描行數(shù)是符合條件的，過(guò)濾效果較好；如果filtered=1，說(shuō)明過(guò)濾效果極差，大部分掃描的行數(shù)都不符合條件，需要優(yōu)化查詢條件。

（12）partitions：分區(qū)表相關(guān)（可選）

如果表是分區(qū)表，顯示當(dāng)前查詢涉及的分區(qū)；如果不是分區(qū)表，顯示為NULL。

示例中，partitions=NULL，說(shuō)明商品表不是分區(qū)表。

3. Explain 實(shí)戰(zhàn)案例（判斷慢SQL原因）

結(jié)合上面的字段詳解，我們用一個(gè)實(shí)戰(zhàn)案例，演示如何通過(guò) explain 分析慢SQL的原因。

案例：慢SQL語(yǔ)句

-- 商品表有80萬(wàn)條數(shù)據(jù)，查詢分類ID為10、價(jià)格大于100的商品，耗時(shí)1.2s
SELECT * FROM product WHERE category_id = 10 AND price > 100;

執(zhí)行 Explain 后的關(guān)鍵字段：

分析原因：

雖然建立了 idx_category_id 索引，但查詢條件中包含 price > 100，而 price 字段沒(méi)有建索引，且 idx_category_id 是單字段索引，MySQL優(yōu)化器判斷“使用索引后，還需要回表查詢price字段，再過(guò)濾”，效率不如直接全表掃描，因此放棄使用索引，導(dǎo)致慢查詢。

優(yōu)化方案：

建立聯(lián)合索引 idx_category_price（category_id, price），遵循最左前綴匹配原則，查詢條件中的category_id在前，price在后，能夠直接命中索引，同時(shí)過(guò)濾兩個(gè)條件，無(wú)需回表。

優(yōu)化后 Explain 關(guān)鍵字段：

優(yōu)化后，SQL執(zhí)行耗時(shí)從1.2s降至20ms，性能提升60倍。

面試實(shí)戰(zhàn)題：如何通過(guò) Explain 判斷SQL是否走索引？如何判斷慢SQL的原因？（標(biāo)準(zhǔn)回答）

三、InnoDB索引底層原理

很多開發(fā)同學(xué)只會(huì)建索引、用索引，卻不懂索引的底層原理，導(dǎo)致遇到索引失效、性能不達(dá)標(biāo)的問(wèn)題時(shí)，無(wú)法從根源上解決。想要真正做好索引優(yōu)化，必須先吃透InnoDB索引的底層實(shí)現(xiàn)——畢竟，所有的索引優(yōu)化技巧，都源于對(duì)底層原理的理解。

InnoDB是MySQL最常用的存儲(chǔ)引擎（企業(yè)生產(chǎn)環(huán)境首選），其索引底層基于B+樹實(shí)現(xiàn)，這也是MySQL索引高效的核心原因。下面我們不搞復(fù)雜的理論堆砌，結(jié)合實(shí)戰(zhàn)場(chǎng)景，拆解B+樹索引的核心特性、索引類型及底層存儲(chǔ)邏輯，重點(diǎn)解決“為什么這么建索引高效”“為什么有些索引會(huì)失效”的問(wèn)題。

1. InnoDB B+樹索引核心特性

B+樹是一種平衡多路查找樹，InnoDB對(duì)其進(jìn)行了優(yōu)化，使其更適配數(shù)據(jù)庫(kù)的讀寫場(chǎng)景，核心特性如下（直接決定索引的使用效率）：

2. InnoDB 兩大索引類型：聚簇索引 vs 非聚簇索引

InnoDB有兩種核心索引類型，兩者的存儲(chǔ)邏輯、查詢效率差異極大，很多慢查詢都源于對(duì)這兩種索引的混淆，必須嚴(yán)格區(qū)分。

（1）聚簇索引（主鍵索引，Clustered Index）

聚簇索引是InnoDB的核心索引，也是表的“主索引”，每個(gè)表只能有一個(gè)聚簇索引，其底層存儲(chǔ)邏輯如下：

舉個(gè)例子：查詢select * from product where id = 10（id是主鍵，聚簇索引），MySQL會(huì)通過(guò)B+樹找到id=10的葉子節(jié)點(diǎn)，直接讀取該節(jié)點(diǎn)中的完整商品數(shù)據(jù)，無(wú)需額外操作，耗時(shí)通常在10ms以內(nèi)。

（2）非聚簇索引（二級(jí)索引，Secondary Index）

非聚簇索引是除聚簇索引以外的所有索引（如普通索引、聯(lián)合索引、唯一索引），也叫二級(jí)索引，一個(gè)表可以有多個(gè)非聚簇索引，其底層存儲(chǔ)邏輯與聚簇索引完全不同：

舉個(gè)例子：查詢select * from product where category_id = 10（category_id是普通索引，非聚簇索引），查詢流程如下：

關(guān)鍵結(jié)論：非聚簇索引查詢會(huì)多一次回表操作，比聚簇索引查詢效率低；如果能避免回表查詢，就能大幅提升非聚簇索引的查詢效率——這就是“覆蓋索引”的核心價(jià)值（后面會(huì)詳細(xì)講解）。

面試必背：聚簇索引與非聚簇索引的核心區(qū)別？

3. 回表查詢與覆蓋索引（優(yōu)化非聚簇索引的核心）

通過(guò)上面的講解，我們知道：非聚簇索引的查詢效率低，核心原因是“回表查詢”——多一次磁盤IO操作，尤其是數(shù)據(jù)量大、查詢頻繁時(shí)，回表會(huì)嚴(yán)重拖慢查詢性能。而解決這個(gè)問(wèn)題的核心方案，就是覆蓋索引。

（1）回表查詢的危害

假設(shè)商品表（product）有80萬(wàn)條數(shù)據(jù)，非聚簇索引idx_category_id（category_id），執(zhí)行查詢select * from product where category_id = 10：

可見(jiàn)，回表查詢的磁盤IO開銷極大，是慢查詢的常見(jiàn)誘因之一，而覆蓋索引能徹底解決這個(gè)問(wèn)題。

（2）覆蓋索引的定義與實(shí)戰(zhàn)用法

定義：如果非聚簇索引的索引鍵，包含了查詢語(yǔ)句中所有需要的字段（select后面的字段），那么通過(guò)這個(gè)非聚簇索引查詢時(shí)，無(wú)需回表，直接從索引中獲取所有數(shù)據(jù)，這個(gè)非聚簇索引就是覆蓋索引。

核心邏輯：讓非聚簇索引的葉子節(jié)點(diǎn)，不僅存儲(chǔ)主鍵值，還存儲(chǔ)查詢所需的其他字段，從而避免回表。

實(shí)戰(zhàn)案例（延續(xù)前文商品表）：

（3）覆蓋索引的使用技巧

4. 索引失效的底層原因

前面我們提到“SQL寫法不規(guī)范、索引建立不合理會(huì)導(dǎo)致索引失效”，但背后的底層原因，都與InnoDB B+樹索引的存儲(chǔ)邏輯有關(guān)，總結(jié)3個(gè)核心底層原因：

如果你在實(shí)戰(zhàn)中遇到問(wèn)題，歡迎在評(píng)論區(qū)留言交流，一起避坑、一起進(jìn)步！

別忘了點(diǎn)贊+在看+收藏三連，關(guān)注我，解鎖更多 SpringBoot AOP 實(shí)戰(zhàn)干貨，下期再見(jiàn)??

• Navicat：連接MySQL后，點(diǎn)擊「工具」→「慢查詢?nèi)罩尽?，即可查看、篩選慢SQL；

• IDEA：安裝「Database Tools」插件，連接MySQL后，在「Database」面板中找到「Slow Queries」，即可查看慢查詢?nèi)罩荆?/p>

• phpMyAdmin：登錄后，點(diǎn)擊「狀態(tài)」→「慢查詢?nèi)罩尽?，即可查看和分析?/p>

• id相同：執(zhí)行順序由上到下（單表查詢、簡(jiǎn)單多表關(guān)聯(lián)）；

• id不同：id值越大，執(zhí)行優(yōu)先級(jí)越高（子查詢場(chǎng)景，先執(zhí)行子查詢，再執(zhí)行主查詢）；

• id為NULL：最后執(zhí)行（比如union查詢的匯總操作）。

• system：表中只有一行數(shù)據(jù)（系統(tǒng)表），效率最高，幾乎不會(huì)出現(xiàn)；

• const：通過(guò)主鍵或唯一索引查詢，只匹配一行數(shù)據(jù)，效率極高（比如 where id = 1）；

• eq_ref：多表關(guān)聯(lián)時(shí)，通過(guò)主鍵或唯一索引關(guān)聯(lián)，每行數(shù)據(jù)只匹配一行關(guān)聯(lián)數(shù)據(jù)（比如user表和order表，通過(guò)user.id=order.user_id關(guān)聯(lián)，user.id是主鍵）；

• ref：通過(guò)普通索引查詢，匹配多行數(shù)據(jù)（比如 where category_id = 10，category_id是普通索引）；

• range：通過(guò)索引范圍查詢（比如 where id > 10、where age between 20 and 30），效率比ref略低；

• index：全索引掃描（掃描整個(gè)索引表，不掃描數(shù)據(jù)），效率較低；

• ALL：全表掃描（掃描整個(gè)表的數(shù)據(jù)），效率最低，慢查詢的主要原因之一。

• Using index：使用了覆蓋索引（查詢的字段都在索引中，無(wú)需回表查詢數(shù)據(jù)），效率極高，是優(yōu)化的目標(biāo)；

• Using where：使用了where條件過(guò)濾數(shù)據(jù)，過(guò)濾效果較好；

• Using index condition：使用了索引條件推送（ICP），減少回表查詢的次數(shù)，提升效率。

• Using filesort：無(wú)法使用索引排序，需要在磁盤或內(nèi)存中進(jìn)行排序（文件排序），耗時(shí)極長(zhǎng)，尤其是數(shù)據(jù)量大時(shí)；

• Using temporary：需要?jiǎng)?chuàng)建臨時(shí)表存儲(chǔ)查詢結(jié)果，再進(jìn)行后續(xù)操作（比如group by、distinct、union），耗時(shí)較長(zhǎng)；

• Using join buffer：多表關(guān)聯(lián)時(shí)，沒(méi)有使用索引，需要使用連接緩沖區(qū)存儲(chǔ)關(guān)聯(lián)數(shù)據(jù)，效率低；

• Using where; Using filesort：使用了where過(guò)濾，但排序沒(méi)有使用索引，需要優(yōu)化排序字段的索引；

• Using where; Using temporary; Using filesort：最糟糕的情況，需要?jiǎng)?chuàng)建臨時(shí)表、進(jìn)行文件排序，必須優(yōu)先優(yōu)化。

• type = ALL（全表掃描）

• possible_keys = idx_category_id

• key = NULL（未使用任何索引）

• rows = 800000（預(yù)估掃描80萬(wàn)行）

• Extra = Using where（只使用where過(guò)濾，無(wú)索引）

• type = range（索引范圍查詢）

• possible_keys = idx_category_price

• key = idx_category_price（實(shí)際使用聯(lián)合索引）

• rows = 5000（預(yù)估掃描5000行，大幅減少）

• Extra = Using index condition; Using where（使用索引條件推送，過(guò)濾效果好）

1. 判斷是否走索引：看 type 字段（是否為const、ref、range）和 key 字段（是否為非NULL）；如果key為NULL、type為ALL，說(shuō)明未走索引；

2. 判斷慢SQL原因：結(jié)合 rows（掃描行數(shù)）、Extra（是否有filesort、temporary）、type 字段，比如：rows過(guò)大說(shuō)明掃描行數(shù)多，Extra出現(xiàn)filesort說(shuō)明排序無(wú)索引，type為ALL說(shuō)明全表掃描。

• 平衡樹結(jié)構(gòu)，查詢效率穩(wěn)定：B+樹的高度固定（一般為3-4層），無(wú)論查詢哪個(gè)數(shù)據(jù)，都只需要3-4次磁盤IO操作，耗時(shí)穩(wěn)定（磁盤IO是MySQL性能瓶頸，減少IO次數(shù)就是提升性能）。比如單表數(shù)據(jù)量千萬(wàn)級(jí)時(shí)，B+樹高度僅為4層，查詢耗時(shí)可控制在10ms以內(nèi)。

• 葉子節(jié)點(diǎn)有序且相連，支持范圍查詢：B+樹的所有葉子節(jié)點(diǎn)按順序排列，且葉子節(jié)點(diǎn)之間通過(guò)指針相連，這也是“range查詢”（如id>10、age between 20 and 30）高效的核心原因——MySQL只需找到范圍的起始葉子節(jié)點(diǎn)，就能通過(guò)指針遍歷所有符合條件的節(jié)點(diǎn)，無(wú)需回表掃描整個(gè)索引。

• 非葉子節(jié)點(diǎn)只存索引鍵，葉子節(jié)點(diǎn)存完整數(shù)據(jù)（聚簇索引）：這是InnoDB索引與MyISAM索引的核心區(qū)別，也是理解“回表查詢”“覆蓋索引”的關(guān)鍵，后面會(huì)詳細(xì)拆解。

• 索引鍵有序，支持排序優(yōu)化：B+樹的索引鍵是有序存儲(chǔ)的，因此當(dāng)SQL中包含order by、group by時(shí)，如果排序字段與索引鍵一致，MySQL可以直接利用索引的有序性完成排序，避免出現(xiàn)“Using filesort”（文件排序），大幅提升排序效率。

• 索引鍵：默認(rèn)使用**主鍵（primary key）**作為索引鍵；如果表沒(méi)有主鍵，MySQL會(huì)自動(dòng)選擇一個(gè)唯一非空字段作為聚簇索引；如果沒(méi)有唯一非空字段，MySQL會(huì)自動(dòng)生成一個(gè)隱藏的row_id作為聚簇索引。

• 存儲(chǔ)結(jié)構(gòu)：B+樹的非葉子節(jié)點(diǎn)存儲(chǔ)主鍵值，葉子節(jié)點(diǎn)存儲(chǔ)整個(gè)行的數(shù)據(jù)（而非指針）。也就是說(shuō)，聚簇索引的葉子節(jié)點(diǎn)就是表的實(shí)際數(shù)據(jù)行，索引與數(shù)據(jù)是“聚簇”在一起的。

• 查詢效率：通過(guò)聚簇索引查詢時(shí)，找到葉子節(jié)點(diǎn)就直接獲取到了完整行數(shù)據(jù)，無(wú)需回表，效率極高（type可達(dá)const級(jí)別）。

• 索引鍵：可以是任意字段（或字段組合），如category_id、create_time、(name, age)等。

• 存儲(chǔ)結(jié)構(gòu)：B+樹的非葉子節(jié)點(diǎn)存儲(chǔ)非聚簇索引的鍵值，葉子節(jié)點(diǎn)不存儲(chǔ)完整行數(shù)據(jù)，只存儲(chǔ)聚簇索引的鍵值（主鍵值）。

• 查詢流程（重點(diǎn)！回表查詢的根源）：通過(guò)非聚簇索引查詢時(shí)，首先找到葉子節(jié)點(diǎn)中的主鍵值，然后再通過(guò)聚簇索引（主鍵索引）查找對(duì)應(yīng)的葉子節(jié)點(diǎn)，才能獲取到完整的行數(shù)據(jù)——這個(gè)“通過(guò)非聚簇索引找到主鍵，再通過(guò)聚簇索引找數(shù)據(jù)”的過(guò)程，就是回表查詢。

1. 通過(guò)非聚簇索引（idx_category_id）的B+樹，找到所有category_id=10的葉子節(jié)點(diǎn)，獲取對(duì)應(yīng)的主鍵值（id）；

2. 再通過(guò)聚簇索引（主鍵id）的B+樹，根據(jù)主鍵值找到對(duì)應(yīng)的葉子節(jié)點(diǎn)，獲取完整的商品數(shù)據(jù)；

3. 將所有符合條件的商品數(shù)據(jù)匯總，返回給客戶端。

1. 存儲(chǔ)內(nèi)容：聚簇索引葉子節(jié)點(diǎn)存完整行數(shù)據(jù)，非聚簇索引葉子節(jié)點(diǎn)存主鍵值；

2. 數(shù)量限制：聚簇索引每個(gè)表只能有一個(gè)，非聚簇索引可以有多個(gè)；

3. 查詢效率：聚簇索引無(wú)需回表，效率更高；非聚簇索引需回表，效率較低；

4. 索引鍵：聚簇索引默認(rèn)用主鍵，非聚簇索引可自定義字段。

• 如果沒(méi)有覆蓋索引：需要先通過(guò)idx_category_id找到所有category_id=10的主鍵id（約5000條），再通過(guò)聚簇索引逐一回表查詢5000條數(shù)據(jù)，共產(chǎn)生5001次磁盤IO（1次找主鍵，5000次回表），執(zhí)行耗時(shí)約500ms；

• 如果有覆蓋索引：無(wú)需回表，直接從非聚簇索引中獲取所有需要的字段，僅需1次磁盤IO，執(zhí)行耗時(shí)可降至20ms以內(nèi)。

• 慢查詢語(yǔ)句：select id, name, price from product where category_id = 10（當(dāng)前索引：idx_category_id，僅包含category_id）；

• 問(wèn)題：查詢需要id、name、price三個(gè)字段，idx_category_id僅包含category_id，葉子節(jié)點(diǎn)只存主鍵id，因此需要回表查詢name和price字段，耗時(shí)約500ms；

• 優(yōu)化方案：創(chuàng)建聯(lián)合索引idx_category_name_price（category_id, name, price），該索引包含了查詢所需的所有字段（category_id用于過(guò)濾，name、price用于返回結(jié)果）；

• 優(yōu)化后：通過(guò)該聯(lián)合索引查詢時(shí)，葉子節(jié)點(diǎn)包含category_id、name、price、主鍵id，無(wú)需回表，執(zhí)行耗時(shí)降至20ms以內(nèi)，Explain的Extra字段會(huì)顯示“Using index”（標(biāo)識(shí)使用了覆蓋索引）。

• 避免濫用select *：select *會(huì)查詢表中所有字段，幾乎不可能使用覆蓋索引（除非索引包含所有字段，這會(huì)導(dǎo)致索引過(guò)大），因此盡量只查詢需要的字段；

• 聯(lián)合索引的字段順序：將查詢條件中的過(guò)濾字段（where后面的字段）放在聯(lián)合索引的前面，查詢所需的返回字段放在后面，既保證能命中索引，又能實(shí)現(xiàn)覆蓋；

• 避免索引過(guò)大：覆蓋索引雖好，但不能包含過(guò)多字段（尤其是大字段，如varchar(255)、text），否則會(huì)導(dǎo)致索引體積過(guò)大，增加磁盤占用和寫入開銷（insert/update/delete時(shí)需要維護(hù)索引）。

• 索引鍵無(wú)法有序匹配：B+樹索引的查詢依賴于索引鍵的有序性，如果SQL寫法破壞了索引鍵的有序性（如函數(shù)操作、運(yùn)算、左模糊查詢），MySQL無(wú)法通過(guò)索引鍵快速定位數(shù)據(jù)，只能放棄索引，走全表掃描；

• 索引過(guò)濾性太差：低區(qū)分度字段（如status、gender）的索引，無(wú)法有效過(guò)濾數(shù)據(jù)，MySQL優(yōu)化器判斷“使用索引的開銷（回表、索引掃描）大于全表掃描的開銷”，會(huì)放棄使用索引；

• 聯(lián)合索引不滿足最左前綴匹配：聯(lián)合索引的B+樹，是按“最左前綴”的順序構(gòu)建的，若查詢條件不包含最左前綴字段，無(wú)法命中索引（后面會(huì)詳細(xì)拆解）。

以上就是SpringBoot數(shù)據(jù)庫(kù)索引優(yōu)化指南的詳細(xì)內(nèi)容，更多關(guān)于SpringBoot數(shù)據(jù)庫(kù)索引優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論