查詢優(yōu)化的核心本質(zhì)：在消耗MySQL最少系統(tǒng)資源（CPU、內(nèi)存、磁盤I/O、網(wǎng)絡(luò)帶寬）的前提下，實現(xiàn)最快的查詢響應(yīng)速度。而實現(xiàn)這一目標(biāo)的最佳實踐，就是合理、高效地利用好索引——索引是MySQL查詢的“加速鍵”，用好索引能避免全表掃描、減少資源消耗，反之則會導(dǎo)致查詢效率低下，浪費系統(tǒng)資源。以下是經(jīng)過實戰(zhàn)驗證的核心優(yōu)化技巧，兼顧實用性和可操作性。

一、基礎(chǔ)查詢優(yōu)化（減少無效資源消耗）

1. 只查詢需要的字段，拒絕“SELECT *”

避免寫法：SELECT * FROM table_name;（查詢表中所有字段，無論是否需要）

推薦寫法：SELECT id, name, age FROM table_name;（精準(zhǔn)指定所需字段）

核心原因：

• 減少網(wǎng)絡(luò)傳輸量：無需傳輸無用字段的數(shù)據(jù)，尤其當(dāng)表中存在TEXT、BLOB等大字段時，優(yōu)化效果更明顯；
• 增加覆蓋索引的使用機會：若查詢的所有字段都包含在索引中，MySQL無需回表查詢，直接從索引中獲取數(shù)據(jù)，大幅提升速度；
• 降低內(nèi)存消耗：減少查詢結(jié)果集的體積，降低MySQL緩沖區(qū)和應(yīng)用層內(nèi)存的占用。

2. 善用覆蓋索引，避免回表查詢

覆蓋索引（Covering Index）是指查詢的所有列（SELECT字段、WHERE條件字段）都包含在同一個索引中，MySQL無需通過索引再去硬盤查詢數(shù)據(jù)表的完整數(shù)據(jù)行（即“回表”），直接從內(nèi)存中的索引獲取所有所需數(shù)據(jù)，查詢速度極快。

• 示例：若users表的name字段建有索引，執(zhí)行SELECT id, name FROM users WHERE name = 'Alice'時，id（主鍵，默認(rèn)包含在二級索引中）和name都在索引中，無需回表，查詢效率大幅提升。
• 關(guān)鍵技巧：創(chuàng)建索引時，可將常用查詢字段（SELECT、WHERE、ORDER BY、GROUP BY涉及的字段）納入索引，構(gòu)建“查詢友好型”覆蓋索引。

3. 禁止在索引列上做運算或函數(shù)操作，避免索引失效

MySQL的優(yōu)化器無法識別索引列上的運算或函數(shù)操作，會直接放棄使用索引，轉(zhuǎn)而執(zhí)行全表掃描，導(dǎo)致資源消耗劇增、查詢變慢。

• 避免寫法：WHERE YEAR(create_time) = 2026;（對索引列create_time執(zhí)行YEAR函數(shù)，索引失效）
• 推薦寫法：WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';（直接對字段值進(jìn)行范圍判斷，正常使用索引）
• 延伸提醒：類似的錯誤寫法還有WHERE id + 1 = 100、WHERE SUBSTR(name, 1, 1) = '張'，均會導(dǎo)致索引失效，需提前規(guī)避。

4. 優(yōu)化LIKE查詢，避免前綴模糊匹配

LIKE查詢的模糊匹配方式，直接影響索引的使用，核心原則是：避免“前綴模糊”（%開頭），優(yōu)先使用“后綴模糊”（%結(jié)尾）。

• 避免寫法：WHERE name LIKE '%張%';（前綴和后綴都模糊，無法使用索引，觸發(fā)全表掃描）
• 推薦寫法：WHERE name LIKE '張%';（僅后綴模糊，可正常使用name字段的索引）
• 特殊場景處理：若必須實現(xiàn)“前綴模糊”查詢（如模糊搜索用戶名），可使用MySQL全文索引（FULLTEXT INDEX）替代LIKE，提升查詢效率。

5. 優(yōu)化OR和IN，避免索引失效

OR查詢優(yōu)化

OR連接的多個條件中，只要有一個條件對應(yīng)的列沒有索引，整個查詢就可能放棄索引，執(zhí)行全表掃描。建議將OR查詢拆分為UNION或UNION ALL（無重復(fù)數(shù)據(jù)時優(yōu)先使用，效率更高）。

-- 索引可能失效（假設(shè)age字段無索引）
SELECT * FROM users WHERE name = '張三' OR age = 25;

-- 優(yōu)化為UNION（去重，效率略低）
SELECT * FROM users WHERE name = '張三'
UNION
SELECT * FROM users WHERE age = 25;

-- 優(yōu)化為UNION ALL（無重復(fù)數(shù)據(jù)，效率更高，優(yōu)先推薦）
SELECT * FROM users WHERE name = '張三'
UNION ALL
SELECT * FROM users WHERE age = 25;

IN查詢優(yōu)化

IN列表中的值不要過多（建議不超過1000個），否則MySQL優(yōu)化器可能放棄使用索引，轉(zhuǎn)為全表掃描。若IN列表值過多，可拆分為多個小批量IN查詢，或使用JOIN替代。

6. 優(yōu)化分頁查詢（LIMIT），解決深分頁問題

深分頁（如LIMIT 1000000, 10）是MySQL分頁查詢的常見性能瓶頸，原因是MySQL會掃描前1000010條數(shù)據(jù)，再丟棄前1000000條，僅返回10條，導(dǎo)致磁盤I/O和CPU消耗劇增。推薦兩種優(yōu)化方案：

延遲關(guān)聯(lián)（先查主鍵，再JOIN原表）

先通過索引查詢出需要的主鍵ID，再通過主鍵JOIN原表獲取完整數(shù)據(jù)，避免掃描大量無用數(shù)據(jù)。

SELECT t1.* FROM table t1
INNER JOIN (
    SELECT id FROM table WHERE status = 2 
    ORDER BY id LIMIT 1000000, 10
) t2 ON t1.id = t2.id;

記錄上次位置（適用于連續(xù)翻頁）

通過主鍵ID的范圍查詢替代LIMIT偏移量，直接定位到需要的數(shù)據(jù)，無需掃描前面的無效數(shù)據(jù)，效率極高。

-- 假設(shè)上一頁最后一條數(shù)據(jù)的id為1000000
SELECT * FROM table WHERE id > 1000000 LIMIT 10;

7. 補充基礎(chǔ)優(yōu)化細(xì)節(jié)

• 優(yōu)先使用UNION ALL替代UNION：UNION會進(jìn)行去重操作（需排序），消耗額外資源；UNION ALL不去重，效率更高，無重復(fù)數(shù)據(jù)時必用。
• LIMIT查詢必須顯式使用ORDER BY：若不指定ORDER BY，MySQL返回的結(jié)果順序不確定，且可能無法利用索引優(yōu)化，建議始終搭配ORDER BY（如按主鍵排序）。
• ORDER BY列值有重復(fù)時，需加唯一索引列聯(lián)合排序：避免排序結(jié)果混亂，同時提升排序效率（如ORDER BY age ASC, id asc，id為唯一主鍵）。

二、關(guān)聯(lián)查詢優(yōu)化（JOIN優(yōu)化，減少關(guān)聯(lián)消耗）

JOIN查詢是MySQL中最常用的復(fù)雜查詢方式，優(yōu)化核心是“減少循環(huán)次數(shù)、利用索引、避免臨時表”，具體技巧如下：

1. 小表驅(qū)動大表，減少循環(huán)次數(shù)

JOIN查詢的底層邏輯是“嵌套循環(huán)”，即通過小表的每條數(shù)據(jù)，去匹配大表的對應(yīng)數(shù)據(jù)。小表驅(qū)動大表（小表作為驅(qū)動表，大表作為被驅(qū)動表）能大幅減少循環(huán)次數(shù)，降低CPU消耗。

示例：用用戶表（小表，1000條數(shù)據(jù)）驅(qū)動訂單表（大表，100萬條數(shù)據(jù)），循環(huán)次數(shù)為1000次；若用訂單表驅(qū)動用戶表，循環(huán)次數(shù)為100萬次，效率天差地別。

2. JOIN的連接字段必須加索引

JOIN的連接字段（如orders.user_id = users.id）是關(guān)聯(lián)查詢的核心，必須給這兩個字段都建立索引，否則會導(dǎo)致被驅(qū)動表全表掃描，關(guān)聯(lián)效率極低。

推薦做法：給orders.user_id建立二級索引，給users.id建立主鍵索引（默認(rèn)已存在），確保關(guān)聯(lián)時能快速匹配數(shù)據(jù)。

3. 明確ON與WHERE的區(qū)別，合理放置條件

ON和WHERE的條件放置位置，會影響查詢結(jié)果和效率，尤其對LEFT JOIN影響極大，核心區(qū)別如下：

INNER JOIN：ON和WHERE條件效果基本一致，MySQL優(yōu)化器會自動合并處理，無需刻意區(qū)分。

LEFT JOIN：

• ON中的條件：在生成臨時表時過濾數(shù)據(jù)，不影響左表數(shù)據(jù)的返回（左表所有數(shù)據(jù)都會保留，右表匹配不到的字段為NULL）；
• WHERE中的條件：在臨時表生成后過濾數(shù)據(jù)，會過濾掉左表中右表為NULL的行，導(dǎo)致查詢結(jié)果退化為INNER JOIN的效果；
• 優(yōu)化點：若想利用右表的索引過濾數(shù)據(jù)，且保留左表所有行，將條件寫在ON中；若想強制過濾左表數(shù)據(jù)，將條件寫在WHERE中。

4. 盡量避免子查詢，用JOIN替代

子查詢（尤其是非關(guān)聯(lián)子查詢）會創(chuàng)建臨時表，臨時表無法利用索引，且會消耗額外的內(nèi)存和磁盤資源，效率較低。優(yōu)先用JOIN替代子查詢，提升查詢效率。

# 差寫法：子查詢創(chuàng)建臨時表，效率低
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 20);

# 優(yōu)寫法：JOIN替代，利用索引，效率高
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.age > 20;

5. 限制關(guān)聯(lián)表數(shù)量

MySQL對多表關(guān)聯(lián)的優(yōu)化能力有限，關(guān)聯(lián)表數(shù)量越多，嵌套循環(huán)次數(shù)越多，臨時表體積越大，查詢效率越低。建議關(guān)聯(lián)表數(shù)不超過5個，若超過，可拆分為多個查詢，在應(yīng)用層聚合數(shù)據(jù)。

三、排序與分組優(yōu)化（GROUP BY、ORDER BY優(yōu)化）

GROUP BY和ORDER BY是查詢中最消耗資源的操作之一（易觸發(fā)文件排序、臨時表），優(yōu)化核心是“利用索引避免文件排序和臨時表”。

1. 優(yōu)化GROUP BY

• 關(guān)閉默認(rèn)排序，避免無用消耗：GROUP BY默認(rèn)會對分組結(jié)果進(jìn)行排序，若無需排序，用ORDER BY NULL關(guān)閉，避免觸發(fā)文件排序。
• 用索引優(yōu)化GROUP BY：創(chuàng)建包含GROUP BY字段的索引，讓MySQL直接從索引中分組，避免創(chuàng)建臨時表和文件排序。

2. 優(yōu)化ORDER BY

• 索引覆蓋排序，避免文件排序：讓ORDER BY的字段包含在索引中（結(jié)合WHERE過濾字段），構(gòu)建聯(lián)合索引，讓MySQL直接從索引中獲取排序后的數(shù)據(jù)，避免觸發(fā)文件排序。
• 避免混合排序：不要同時使用ASC（升序）和DESC（降序）排序（除非MySQL 8.0+，支持降序索引），否則會觸發(fā)文件排序。

提示：如果數(shù)據(jù)量很大，文件排序會使用磁盤排序，產(chǎn)生大量磁盤I/O，導(dǎo)致查詢性能急劇下降，務(wù)必避免。

四、特殊場景優(yōu)化（避坑指南）

1. 避免隱式類型轉(zhuǎn)換，防止索引失效

索引字段的類型與查詢值的類型不一致時，MySQL會觸發(fā)隱式類型轉(zhuǎn)換，導(dǎo)致索引失效，轉(zhuǎn)而執(zhí)行全表掃描。

-- 表中user_id是INT類型（建有索引）
-- 差：查詢值為字符串'100'，隱式轉(zhuǎn)為INT，索引失效
SELECT * FROM orders WHERE user_id = '100';

-- 優(yōu)：類型匹配，正常使用索引
SELECT * FROM orders WHERE user_id = 100;

2. 優(yōu)化范圍查詢，合理設(shè)計聯(lián)合索引順序

聯(lián)合索引中，范圍查詢（>、<、BETWEEN、IN）后的字段無法使用索引，因此設(shè)計聯(lián)合索引時，需將范圍查詢字段放在最后，確保前面的字段能正常使用索引。

-- 索引：idx_orders_status_create_time (status, create_time)
-- 優(yōu)：范圍字段create_time放最后，status字段正常使用索引
SELECT * FROM orders WHERE status=1 AND create_time > '2026-01-01';

-- 差：范圍字段create_time放前面，status字段無法使用索引，觸發(fā)全表掃描
SELECT * FROM orders WHERE create_time > '2026-01-01' AND status=1;

3. 優(yōu)化NULL值查詢，減少索引統(tǒng)計復(fù)雜度

IS NULL/IS NOT NULL可以使用索引（前提是字段有索引），但NULL值會讓索引統(tǒng)計和比較變得復(fù)雜，盡量避免頻繁查詢NULL值。

-- 有索引時可用，但不推薦頻繁使用
SELECT * FROM orders WHERE remark IS NULL;

-- 優(yōu)化方案：給字段設(shè)置默認(rèn)值（如空字符串），替代NULL
ALTER TABLE orders MODIFY COLUMN remark VARCHAR(255) DEFAULT '';
SELECT * FROM orders WHERE remark = '';

4. 優(yōu)化批量操作（插入/更新），減少事務(wù)和網(wǎng)絡(luò)開銷

批量插入優(yōu)化

單條插入會頻繁建立和釋放事務(wù)、發(fā)起網(wǎng)絡(luò)請求，效率極低；批量插入可減少網(wǎng)絡(luò)交互和事務(wù)開銷，提升插入效率。

-- 差：單條插入，效率低
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (2, 200);

-- 優(yōu)：批量插入，效率提升明顯
INSERT INTO orders (user_id, amount) VALUES (1, 100), (2, 200);

批量更新優(yōu)化

多次單條更新會觸發(fā)多次事務(wù)和索引維護(hù)，用CASE WHEN替代多次UPDATE，實現(xiàn)一次更新多條數(shù)據(jù)，減少資源消耗。

-- 差：多次更新，效率低
UPDATE orders SET amount=150 WHERE order_id=1;
UPDATE orders SET amount=250 WHERE order_id=2;

-- 優(yōu)：一次更新多條數(shù)據(jù)，效率高
UPDATE orders 
SET amount = CASE 
    WHEN order_id=1 THEN 150 
    WHEN order_id=2 THEN 250 
END 
WHERE order_id IN (1,2);

5. 避免使用SELECT DISTINCT，優(yōu)先用GROUP BY

SELECT DISTINCT會通過排序?qū)崿F(xiàn)去重，開銷較大；若需去重，可改用GROUP BY + ORDER BY NULL，效果相同且優(yōu)化器更友好。

-- 差：DISTINCT觸發(fā)排序，消耗資源
SELECT DISTINCT user_id FROM orders WHERE status=1;

-- 優(yōu)：GROUP BY + ORDER BY NULL 避免排序，效率更高
SELECT user_id FROM orders WHERE status=1 GROUP BY user_id ORDER BY NULL;

提示：若字段有索引，或使用MySQL 8.0+版本，SELECT DISTINCT的可讀性更好，且與GROUP BY的效率差異極小，可根據(jù)可讀性選擇。

6. 避免IN子句中包含子查詢

舊版本MySQL中，IN子句中的子查詢會生成衍生表，無法使用索引，執(zhí)行效率極低；推薦改寫為JOIN，利用索引提升效率。

-- ? 低效：IN子句包含子查詢，生成衍生表，無索引可用
SELECT * FROM A WHERE id IN (SELECT id FROM B WHERE status = 1);

-- ? 推薦：改寫為JOIN，利用索引，效率大幅提升
SELECT A.* FROM A INNER JOIN B ON A.id = B.id WHERE B.status = 1;

7. 避免NOT IN子查詢

若NOT IN子查詢的結(jié)果中包含NULL，整個查詢結(jié)果會為空，且執(zhí)行效率極差；推薦用NOT EXISTS或LEFT JOIN ... WHERE ... IS NULL替代，兼容性和效率更優(yōu)。

-- ? 低效且易出錯：NOT IN子查詢含NULL時結(jié)果為空
SELECT * FROM A WHERE id NOT IN (SELECT id FROM B WHERE status = 1);

-- ? 推薦寫法1：NOT EXISTS（效率高，優(yōu)先選擇）
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.id = B.id AND B.status = 1);

-- ? 推薦寫法2：LEFT JOIN + IS NULL（兼容性好）
SELECT A.* FROM A LEFT JOIN B ON A.id = B.id AND B.status = 1 WHERE B.id IS NULL;

8. 規(guī)避相關(guān)子查詢陷阱

若子查詢引用了外層表的列（即相關(guān)子查詢），子查詢會對外層表的每一行都執(zhí)行一次，循環(huán)次數(shù)多、效率極低；推薦改寫為JOIN或使用MySQL 8.0+的窗口函數(shù)。

-- ? 低效：相關(guān)子查詢，對外層users每一行執(zhí)行一次子查詢
SELECT u.name, u.salary,
    (SELECT AVG(salary) FROM employees e WHERE e.dept_id = u.dept_id) AS dept_avg
FROM users u WHERE u.status = 'active';

-- ? 推薦寫法1：JOIN替代（兼容所有MySQL版本）
SELECT u.name, u.salary, d.dept_avg
FROM users u
JOIN (SELECT dept_id, AVG(salary) AS dept_avg FROM employees GROUP BY dept_id) d
ON u.dept_id = d.dept_id
WHERE u.status = 'active';

-- ? 推薦寫法2：MySQL 8.0+ 窗口函數(shù)（更簡潔）
SELECT u.name, u.salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM users u WHERE u.status = 'active';

9. 正確使用COUNT函數(shù)，選擇最優(yōu)方式

COUNT函數(shù)的不同用法，效率差異較大，InnoDB引擎下的最優(yōu)選擇如下：

• COUNT(*)：MySQL專門優(yōu)化的寫法，無需判斷列是否為NULL，直接統(tǒng)計行數(shù)，速度最快，優(yōu)先推薦；
• COUNT(col)：需要判斷列值是否為NULL，跳過NULL值統(tǒng)計，速度稍慢；
• COUNT(1)：與COUNT()在InnoDB中的性能幾乎一致，但習(xí)慣上優(yōu)先使用COUNT()，可讀性更好。

五、索引設(shè)計優(yōu)化（核心中的核心）

索引是查詢優(yōu)化的基礎(chǔ)，索引設(shè)計不合理，再優(yōu)的查詢語句也無法發(fā)揮作用。核心原則：索引不在多，在于精，既要提升查詢效率，也要減少索引維護(hù)成本。

1. 用聯(lián)合索引替代多個單列索引

若多個查詢場景都涉及多個相同字段（如WHERE a=? AND b=?、ORDER BY a,b），創(chuàng)建聯(lián)合索引（a,b）替代單獨的索引a和索引b，既能減少索引數(shù)量，又能提升多字段查詢的效率。

注意：聯(lián)合索引遵循“最左前綴原則”，查詢條件必須從第一個字段開始，不能跳過前面的字段直接查詢后面的字段（如聯(lián)合索引(a,b,c)，無法直接用b或c作為查詢條件使用索引）。

2. 區(qū)分度高的列優(yōu)先建索引

索引的區(qū)分度（ cardinality ）是指字段中不同值的數(shù)量占比，區(qū)分度越高（重復(fù)值越少），索引的篩選效果越好，查詢效率越高。

推薦建索引：id、username、phone等區(qū)分度高的字段；

不推薦單獨建索引：性別（男/女）、狀態(tài)（0/1）等區(qū)分度低的字段，單獨建索引的效果甚至不如全表掃描，可納入 聯(lián)合索引的末尾。

3. 控制索引數(shù)量，平衡查詢與寫入效率

索引不是越多越好：

• 索引會占用磁盤空間，索引越多，磁盤占用越大；
• INSERT、UPDATE、DELETE操作時，MySQL需要同步維護(hù)所有相關(guān)索引，索引越多，寫入效率越低。

建議：單表索引數(shù)量不超過5個，優(yōu)先保留常用查詢場景的索引，刪除無用、冗余的索引。

4. 字符串字段適當(dāng)使用前綴索引

對于很長的字符串字段（如URL、備注），直接建索引會導(dǎo)致索引體積過大，占用大量磁盤空間，且查詢效率受影響。可只索引字符串的前N個字符（前綴索引），減小索引體積，提升效率。

-- 對url字段建立前綴索引（只索引前20個字符）
CREATE INDEX idx_url_prefix ON table_name(url(20));

注意：前綴長度需合理（如URL的前20個字符已能區(qū)分大部分?jǐn)?shù)據(jù)），避免過短導(dǎo)致區(qū)分度降低，過?無法減小索引體積。

5. 選擇合適的數(shù)據(jù)類型，減少資源消耗

數(shù)據(jù)類型的選擇直接影響索引效率和磁盤占用，核心原則：夠用就好，盡量精簡。

• 整數(shù)類型：能用TINYINT（1字節(jié)）就不用INT（4字節(jié)），能用INT就不用BIGINT（8字節(jié)）；
• 字符串類型：能用VARCHAR就別用TEXT（除非需要存儲大量文本），VARCHAR需指定合理長度（避免過長）；
• 盡量定義為NOT NULL：NULL值會增加索引統(tǒng)計和查詢的復(fù)雜度，還會占用額外存儲空間，建議給字段設(shè)置默認(rèn)值替代NULL。

六、總結(jié)

MySQL查詢優(yōu)化的核心邏輯的是“減少無效消耗、利用索引加速”：

• 查詢語句層面，避免全表掃描、文件排序、臨時表；
• 索引層面，合理設(shè)計索引、控制索引數(shù)量、利用覆蓋索引；
• 關(guān)聯(lián)和批量操作層面，減少循環(huán)和事務(wù)開銷。

優(yōu)化的關(guān)鍵不是“堆砌技巧”，而是結(jié)合實際業(yè)務(wù)場景，分析查詢執(zhí)行計劃（EXPLAIN），找到性能瓶頸，針對性優(yōu)化——只有讓每一次查詢都盡可能少地消耗CPU、內(nèi)存、磁盤I/O，才能實現(xiàn)最快的查詢速度，同時保證MySQL系統(tǒng)的穩(wěn)定高效運行。

以上就是一文分享30個MySQL中的查詢優(yōu)化實用技巧的詳細(xì)內(nèi)容，更多關(guān)于MySQL查詢優(yōu)化技巧的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論