MySQL中索引的常見問題與性能優(yōu)化詳解

更新時(shí)間：2025年11月20日 09:52:25 作者：劉大華

索引就像圖書館的檢索系統(tǒng),設(shè)計(jì)得好,找書會(huì)很快；設(shè)計(jì)的不好,反而會(huì)越用越慢,下面小編就和大家詳細(xì)介紹一下MySQL中索引的常見問題與性能優(yōu)化吧

什么是索引

想象一下，你要在100萬(wàn)人的花名冊(cè)里面找到"張三"：

沒有索引：從頭到尾逐頁(yè)翻找，可能要找50萬(wàn)次
有索引：相當(dāng)于有目錄，直接查目錄，2-3次就找到了

索引的本質(zhì)：索引是一種數(shù)據(jù)結(jié)構(gòu)，幫助數(shù)據(jù)庫(kù)快速定位數(shù)據(jù)，避免全表掃描。

-- 創(chuàng)建用戶表的正確方式
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)  -- 為username字段創(chuàng)建索引
);

這里為username字段創(chuàng)建了普通索引，當(dāng)按用戶名查詢時(shí)會(huì)使用這個(gè)索引，所以查詢速度會(huì)提升。

-- 查看索引使用情況
EXPLAIN SELECT * FROM users WHERE username = '張三';

使用EXPLAIN可以查看MySQL如何執(zhí)行查詢，是否使用了索引。

EXPLAIN結(jié)果關(guān)鍵字段解讀：

字段	說(shuō)明	理想值
type	查詢類型	const, eq_ref, ref
key	實(shí)際使用的索引	顯示索引名稱
key_len	使用的索引長(zhǎng)度	越長(zhǎng)越好
rows	預(yù)估掃描行數(shù)	越少越好
Extra	額外信息	Using index

type字段詳細(xì)說(shuō)明：

const：通過主鍵或唯一索引查詢，最多返回一行
eq_ref：聯(lián)表查詢時(shí)使用主鍵或唯一索引
ref：使用普通索引查詢
range：使用索引進(jìn)行范圍查詢
index：全索引掃描
ALL：全表掃描（需要優(yōu)化）

索引的底層原理

MySQL索引主要使用B+樹結(jié)構(gòu)，就像一本多層目錄的書：

根節(jié)點(diǎn)：最頂層目錄
中間節(jié)點(diǎn)：章節(jié)目錄
葉子節(jié)點(diǎn)：具體的頁(yè)碼

為什么用B+樹？

平衡：左右子樹高度差不超過1，查詢穩(wěn)定
有序：數(shù)據(jù)按順序存儲(chǔ)，范圍查詢效率高
扇出高：每個(gè)節(jié)點(diǎn)可以存儲(chǔ)很多指針，減少IO次數(shù)

常見問題

問題1：索引越多越好嗎

-- 錯(cuò)誤示范：盲目創(chuàng)建索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    status TINYINT,
    -- 問題：每個(gè)索引都要維護(hù)，寫操作變慢
    INDEX idx_name (name),           -- 可能很少按name單獨(dú)查詢
    INDEX idx_category (category),   -- 可能很少按category單獨(dú)查詢
    INDEX idx_price (price),         -- 可能很少按price單獨(dú)查詢
    INDEX idx_status (status),       -- 狀態(tài)只有幾個(gè)值，索引效果差
    INDEX idx_name_category (name, category)  -- 與單列索引重復(fù)
);

這里創(chuàng)建了5個(gè)索引，但很多可能用不上，反而影響性能

每個(gè)索引的代價(jià)：

占用磁盤空間：每個(gè)索引都是一個(gè)B+樹
降低寫性能：INSERT/UPDATE/DELETE需要更新所有索引
增加優(yōu)化器負(fù)擔(dān)：需要評(píng)估多個(gè)索引的選擇

正確做法：按實(shí)際查詢需求創(chuàng)建

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    status TINYINT DEFAULT 1,
    -- 根據(jù)業(yè)務(wù)查詢模式創(chuàng)建索引
    INDEX idx_category_status_price (category, status, price),  -- 聯(lián)合索引
    INDEX idx_name (name)  -- 只有經(jīng)常單獨(dú)按name查詢才需要
);

使用聯(lián)合索引覆蓋多個(gè)查詢條件，比多個(gè)單列索引更高效

問題2：在低選擇性字段上創(chuàng)建索引

-- 選擇性太低的索引
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('男','女'),        -- 只有2個(gè)可能值
    status TINYINT DEFAULT 1,      -- 1激活, 0禁用, 2刪除
    department VARCHAR(50),
    INDEX idx_gender (gender),
    INDEX idx_status (status) 
);

gender和status字段值重復(fù)度高，創(chuàng)建索引效果很差

假設(shè)表有10000條數(shù)據(jù)：

gender索引：2個(gè)不同值
status索引：3個(gè)不同值
查詢時(shí)可能返回大量數(shù)據(jù)，索引效果差

正確做法：選擇高區(qū)分度字段

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('男','女'),
    status TINYINT DEFAULT 1,
    department VARCHAR(50),
    employee_code VARCHAR(20) UNIQUE,  -- 員工編號(hào)，唯一性高
    email VARCHAR(100),                -- 郵箱，區(qū)分度高
    
    -- 創(chuàng)建有價(jià)值的索引
    UNIQUE INDEX uk_employee_code (employee_code),
    INDEX idx_email (email),
    INDEX idx_department_gender (department, gender) -- 聯(lián)合索引選擇性較好
);

employee_code和email字段值幾乎不重復(fù)，索引效果很好

問題3：索引列參與計(jì)算或函數(shù)

-- 創(chuàng)建測(cè)試表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,                -- 日期字段，有索引
    amount DECIMAL(10,2),           -- 金額字段，有索引
    customer_name VARCHAR(100),     -- 客戶名，有索引
    INDEX idx_order_date (order_date),
    INDEX idx_amount (amount),
    INDEX idx_customer_name (customer_name)
);

錯(cuò)誤的查詢方式：索引列參與計(jì)算

SELECT * FROM orders WHERE YEAR(order_date) = 2024;        -- 索引失效！
SELECT * FROM orders WHERE amount * 1.1 > 1000;           -- 索引失效！
SELECT * FROM orders WHERE UPPER(customer_name) = 'JOHN'; -- 索引失效！
SELECT * FROM orders WHERE order_date + INTERVAL 1 DAY > '2024-01-01'; -- 索引失效！

在索引列上使用函數(shù)或計(jì)算，MySQL無(wú)法使用索引，會(huì)導(dǎo)致全表掃描

正確的查詢方式

SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';  -- 使用索引

SELECT * FROM orders WHERE amount > 1000 / 1.1;                  -- 使用索引

SELECT * FROM orders WHERE customer_name = 'john';               -- 使用索引

SELECT * FROM orders WHERE order_date > '2024-01-01' - INTERVAL 1 DAY;  -- 使用索引

保持索引列干凈，把計(jì)算移到等號(hào)右邊，MySQL就能使用索引

問題4：最左前綴原則

什么是聯(lián)合索引？ 聯(lián)合索引也叫復(fù)合索引，是在多個(gè)列上創(chuàng)建的索引。

-- 創(chuàng)建聯(lián)合索引
CREATE TABLE sales (
    id INT PRIMARY KEY,
    region VARCHAR(50),      -- 地區(qū)
    city VARCHAR(50),        -- 城市
    sale_date DATE,          -- 銷售日期
    amount DECIMAL(10,2),
    INDEX idx_region_city_date (region, city, sale_date)  -- 聯(lián)合索引
);

這個(gè)聯(lián)合索引按region→city→sale_date的順序組織數(shù)據(jù)

為什么使用聯(lián)合索引？ 1.減少索引數(shù)量：一個(gè)聯(lián)合索引替代多個(gè)單列索引 2.覆蓋更多查詢：支持多種查詢條件組合 3.避免回表：如果查詢字段都在索引中，不需要訪問數(shù)據(jù)行 4.排序優(yōu)化：天然支持按索引順序排序

聯(lián)合索引的性價(jià)比體現(xiàn)在：

存儲(chǔ)成本：1個(gè)聯(lián)合索引 < 3個(gè)單列索引
查詢性能：聯(lián)合索引可以一次性滿足復(fù)雜查詢
維護(hù)成本：只需要維護(hù)1個(gè)索引結(jié)構(gòu)

-- 能充分利用聯(lián)合索引的查詢
SELECT * FROM sales WHERE region = '北京';  -- 使用索引
SELECT * FROM sales WHERE region = '北京' AND city = '朝陽(yáng)區(qū)'; -- 使用索引
SELECT * FROM sales WHERE region = '北京' AND city = '朝陽(yáng)區(qū)' AND sale_date = '2024-01-01'; -- 使用索引
SELECT * FROM sales WHERE region = '北京' ORDER BY city;  -- 使用索引

這些查詢都能充分利用聯(lián)合索引，因?yàn)闂l件從最左列開始

-- 不能使用或不能充分利用聯(lián)合索引的查詢
SELECT * FROM sales WHERE city = '朝陽(yáng)區(qū)'; -- 無(wú)法使用索引
SELECT * FROM sales WHERE sale_date = '2024-01-01'; -- 無(wú)法使用索引
SELECT * FROM sales WHERE region = '北京' AND sale_date = '2024-01-01';  -- 只能使用region部分
SELECT * FROM sales WHERE city = '朝陽(yáng)區(qū)' AND sale_date = '2024-01-01';  -- 無(wú)法使用索引

缺少最左列region，索引無(wú)法使用或只能部分使用

最佳實(shí)踐

實(shí)踐1：選擇合適的索引類型

1.主鍵索引（聚集索引）- 最重要的索引

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自動(dòng)創(chuàng)建聚集索引
    name VARCHAR(50)
);

主鍵索引決定數(shù)據(jù)物理存儲(chǔ)順序，一個(gè)表只能有一個(gè)

2. 唯一索引 - 保證數(shù)據(jù)唯一性

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,      -- 唯一索引
    phone VARCHAR(20) UNIQUE        -- 唯一索引
);

唯一索引既保證數(shù)據(jù)唯一，又提供查詢加速

3. 聯(lián)合索引 - 多條件查詢的最佳選擇

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    created_at DATETIME,
    INDEX idx_user_status_created (user_id, status, created_at)
);

聯(lián)合索引的順序很重要，應(yīng)該把最常用的等值查詢條件放在前面

4. 前綴索引 - 處理長(zhǎng)文本字段

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(500),
    content TEXT,
    INDEX idx_title_prefix (title(50))  -- 只索引前50個(gè)字符
);

對(duì)于長(zhǎng)文本字段，可以只索引前N個(gè)字符，平衡性能與存儲(chǔ)

實(shí)踐2：覆蓋索引

什么是覆蓋索引？

當(dāng)查詢的所有字段都包含在索引中時(shí)，MySQL只需要訪問索引而不需要回表查詢數(shù)據(jù)行。

-- 創(chuàng)建測(cè)試表
CREATE TABLE user_activities (
    id INT PRIMARY KEY,
    user_id INT,
    activity_type VARCHAR(50),
    activity_time DATETIME,
    description TEXT,  -- 大文本字段
    INDEX idx_user_activity_time (user_id, activity_type, activity_time)
);

-- 需要回表查詢的例子
SELECT * FROM user_activities 
WHERE user_id = 123 AND activity_type = 'login';

執(zhí)行過程：

1.在索引idx_user_activity_time中找到匹配記錄

2.獲取對(duì)應(yīng)的主鍵id

3.通過主鍵id到數(shù)據(jù)行中讀取所有字段（包括大文本description）

4.返回結(jié)果

-- 覆蓋索引的例子
SELECT user_id, activity_type, activity_time 
FROM user_activities 
WHERE user_id = 123 AND activity_type = 'login';

執(zhí)行過程：

1.在索引idx_user_activity_time中找到匹配記錄

2.直接返回索引中的字段值（user_id, activity_type, activity_time都在索引中）

3.不需要訪問數(shù)據(jù)行

覆蓋索引的優(yōu)勢(shì)：

性能提升：避免回表操作，減少IO
減少內(nèi)存使用：不需要加載整行數(shù)據(jù)
查詢更快：特別是在有TEXT/BLOB字段的表上

實(shí)踐3：索引維護(hù)和監(jiān)控

1. 查看索引使用情況

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database' 
AND TABLE_NAME = 'your_table';

查看表的索引結(jié)構(gòu)和字段信息

2. 查找冗余索引

SELECT 
    t.TABLE_NAME,
    s.INDEX_NAME,
    GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) as columns
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t ON s.TABLE_NAME = t.TABLE_NAME 
WHERE s.TABLE_SCHEMA = 'your_database'
GROUP BY t.TABLE_NAME, s.INDEX_NAME
ORDER BY t.TABLE_NAME, s.INDEX_NAME;

找出可能重復(fù)或冗余的索引

3. 重建索引優(yōu)化性能

OPTIMIZE TABLE your_table;
-- 或者
ALTER TABLE your_table ENGINE=InnoDB;

重建表可以消除索引碎片，提高性能

不同業(yè)務(wù)的不同策略

場(chǎng)景1：電商商品搜索優(yōu)化

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    category_id INT,
    brand_id INT,
    price DECIMAL(10,2),
    status TINYINT DEFAULT 1,  -- 1上架 0下架
    stock_count INT,
    created_at DATETIME,
    
    -- 針對(duì)電商常見查詢模式設(shè)計(jì)索引
    INDEX idx_category_status_price (category_id, status, price),
    INDEX idx_brand_status (brand_id, status),
    INDEX idx_name_category (name, category_id),
    INDEX idx_created_status (created_at, status)
);

高頻查詢優(yōu)化：

查詢1：分類頁(yè)面商品列表

SELECT * FROM products 
WHERE category_id = 5 AND status = 1 
ORDER BY price DESC LIMIT 20;

索引使用：idx_category_status_price 說(shuō)明：等值查詢category_id和status，按price排序，完美匹配索引

查詢2：品牌商品搜索

SELECT * FROM products 
WHERE brand_id = 10 AND status = 1 
ORDER BY created_at DESC LIMIT 10;

索引使用：idx_brand_status 說(shuō)明：等值查詢brand_id和status，索引覆蓋查詢條件

查詢3：商品搜索

SELECT * FROM products 
WHERE name LIKE '手機(jī)%' AND category_id = 5 AND status = 1;

索引使用：idx_name_category 說(shuō)明：前綴匹配name，等值查詢category_id和status

場(chǎng)景2：社交平臺(tái)消息系統(tǒng)

CREATE TABLE messages (
    id BIGINT PRIMARY KEY,
    from_user_id BIGINT,
    to_user_id BIGINT,
    content TEXT,
    is_read TINYINT DEFAULT 0,
    created_at DATETIME,
    
    -- 針對(duì)消息查詢模式優(yōu)化
    INDEX idx_to_user_created (to_user_id, created_at),
    INDEX idx_from_user_created (from_user_id, created_at),
    INDEX idx_conversation (LEAST(from_user_id, to_user_id), GREATEST(from_user_id, to_user_id), created_at)
);

典型查詢優(yōu)化：

查詢1：查看收件箱（最新消息在前）

SELECT * FROM messages 
WHERE to_user_id = 123 
ORDER BY created_at DESC 
LIMIT 20;

索引使用：idx_to_user_created 說(shuō)明：等值查詢to_user_id，按created_at排序，完美匹配索引

查詢2：查看對(duì)話歷史

SELECT * FROM messages 
WHERE LEAST(from_user_id, to_user_id) = 123 
  AND GREATEST(from_user_id, to_user_id) = 456 
ORDER BY created_at;

索引使用：idx_conversation 說(shuō)明：使用函數(shù)索引優(yōu)化對(duì)話查詢，避免OR條件

場(chǎng)景3：日志分析系統(tǒng)

CREATE TABLE access_logs (
    id BIGINT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    resource_path VARCHAR(500),
    ip_address VARCHAR(45),
    access_time DATETIME,
    response_time INT,
    
    -- 日志分析查詢優(yōu)化
    INDEX idx_access_time (access_time),
    INDEX idx_user_action_time (user_id, action, access_time),
    INDEX idx_action_response (action, response_time)
);

分析查詢優(yōu)化：

查詢1：時(shí)間范圍統(tǒng)計(jì)

SELECT action, COUNT(*) 
FROM access_logs 
WHERE access_time BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY action;

索引使用：idx_access_time 說(shuō)明：范圍查詢access_time，索引快速定位時(shí)間范圍

查詢2：用戶行為分析

SELECT user_id, action, COUNT(*) 
FROM access_logs 
WHERE user_id = 123 
  AND access_time >= '2024-01-01'
GROUP BY user_id, action;

索引使用：idx_user_action_time 說(shuō)明：等值查詢user_id，范圍查詢access_time，索引覆蓋查詢條件

性能優(yōu)化

索引設(shè)計(jì)檢查清單：

只為高頻查詢條件創(chuàng)建索引
選擇性 > 10% 的字段才考慮索引
復(fù)合索引遵循最左前綴原則
避免索引列參與計(jì)算或函數(shù)
考慮覆蓋索引優(yōu)化
定期監(jiān)控索引使用情況
刪除長(zhǎng)時(shí)間未使用的索引

查詢優(yōu)化技巧：

使用EXPLAIN分析執(zhí)行計(jì)劃
避免SELECT *，只取需要的字段
大數(shù)據(jù)量表考慮分區(qū)
合理使用LIMIT限制結(jié)果集
避免在WHERE子句中使用NOT、!=、<>操作

總結(jié)

1.理解業(yè)務(wù)需求：索引設(shè)計(jì)要從實(shí)際查詢模式出發(fā) 2.平衡讀寫性能：索引加速查詢但降低寫性能 3.精準(zhǔn)設(shè)計(jì)：聯(lián)合索引比多個(gè)單列索引更高效 4.關(guān)注選擇性：高選擇性字段更適合創(chuàng)建索引 5.持續(xù)優(yōu)化：隨著業(yè)務(wù)發(fā)展調(diào)整索引策略

"為你的查詢?cè)O(shè)計(jì)索引，而不是為你的表設(shè)計(jì)索引"

以上就是MySQL中索引的常見問題與性能優(yōu)化詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: