MySQL中SQL查詢速度優(yōu)化的20個技巧分享
更新時間：2025年10月28日 10:10:25 作者：劉大華
這篇文章和大家系統(tǒng)分享了20種SQL優(yōu)化實用技巧,涵蓋查詢,索引,設(shè)計及工具使用,通過真實案例解析,幫助開發(fā)者提升數(shù)據(jù)庫性能,告別慢查詢
前言
為什么SQL需要優(yōu)化？
舉個例子：公司有個報表系統(tǒng)，每天上午9點都準時卡頓，查詢一個數(shù)據(jù)要等半分多鐘。用戶一直抱怨不停。
后來分析才發(fā)現(xiàn)是一條SQL語句沒走索引，全表掃描了上百萬條數(shù)據(jù)。優(yōu)化后，查詢時間從30秒降到了0.1秒！
為什么會這樣？
假如把數(shù)據(jù)庫比作圖書館，那么SQL語句就是找書的指令。
如果你說"給我一本小說"，管理員得去翻遍整個圖書館；但如果你說"給我編號A123架第4層的小說"，管理員很快就能找到。這個編號就相當于數(shù)據(jù)庫的索引。
下面分享20種優(yōu)化方案！
一、基礎(chǔ)優(yōu)化篇
1. 只查詢需要的字段：告別SELECT *
錯誤示范：
SELECT * FROM users WHERE status = 1;
問題分析：
- 查詢所有字段，包括大文本字段
- 網(wǎng)絡(luò)傳輸數(shù)據(jù)量大
- 內(nèi)存占用高
正確做法：
SELECT id, name, email, status FROM users WHERE status = 1;
場景舉例： 用戶表有20個字段，但列表頁只需要顯示4個字段。使用SELECT *比指定字段慢3倍！
2. EXISTS vs IN：根據(jù)數(shù)據(jù)量選擇
傳統(tǒng)認知： EXISTS 比 IN 快
實際情況： 需要看子查詢數(shù)據(jù)量
小數(shù)據(jù)量場景（子查詢結(jié)果<1000條）：
-- 兩種方式性能相當 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3); SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip_level > 3);
大數(shù)據(jù)量場景（子查詢結(jié)果>10000條）：
-- EXISTS通常更優(yōu) SELECT * FROM large_table t1 WHERE EXISTS (SELECT 1 FROM large_table t2 WHERE t2.parent_id = t1.id);
3. 避免WHERE子句中的函數(shù)計算
錯誤示范：
-- 索引失效！ SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-01'; SELECT * FROM products WHERE LOWER(name) = 'iphone';
正確做法：
-- 使用范圍查詢 SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'; -- 保持字段原樣查詢 SELECT * FROM products WHERE name = 'iPhone';
原理： 對索引字段使用函數(shù)會使索引失效，變成全表掃描。
4. UNION ALL vs UNION：明確是否需要去重
需要去重：
-- 性能較差，但結(jié)果準確 SELECT city FROM customers UNION SELECT city FROM suppliers;
不需要去重：
-- 性能更好 SELECT city FROM customers UNION ALL SELECT city FROM suppliers;
性能對比： 在100萬數(shù)據(jù)量下，UNION ALL比UNION快5-8倍！
二、索引優(yōu)化篇
5. 為高頻查詢條件建立索引
場景分析：
-- 高頻查詢1：按狀態(tài)查詢 SELECT * FROM orders WHERE status = 'pending'; -- 高頻查詢2：按用戶+時間查詢 SELECT * FROM orders WHERE user_id = 123 AND create_time > '2024-01-01'; -- 索引方案 CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_user_time ON orders(user_id, create_time);
6. 掌握最左前綴原則
復(fù)合索引： (status, create_time, user_id)
有效使用索引的查詢：
WHERE status = 'pending' -- 使用索引 WHERE status = 'pending' AND create_time > '2024-01-01' -- 使用索引 WHERE status = 'pending' AND create_time > '2024-01-01' AND user_id = 123 -- 使用索引
索引失效的查詢：
WHERE create_time > '2024-01-01' -- 索引失效！ WHERE user_id = 123 -- 索引失效！ WHERE status = 'pending' AND user_id = 123 -- 部分使用索引
7. 避免索引列參與計算
錯誤示范：
-- 索引失效的寫法 SELECT * FROM products WHERE price + 100 > 500; SELECT * FROM users WHERE YEAR(create_time) = 2024;
正確做法：
-- 優(yōu)化后的寫法 SELECT * FROM products WHERE price > 400; SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
8. 索引不是越多越好：平衡讀寫性能
索引的代價：
- 寫操作變慢：每次INSERT/UPDATE/DELETE都要更新索引
- 存儲空間增加：索引占用額外磁盤空間
- 選擇困難：過多索引讓優(yōu)化器難以選擇
建議：
- 單表索引數(shù)量控制在3-5個以內(nèi)
- 優(yōu)先為高頻查詢和WHERE條件建立索引
- 定期清理未使用的索引
三、高級技巧篇
9. 深度分頁優(yōu)化：告別LIMIT偏移量
傳統(tǒng)分頁的問題：
-- 越往后越慢！ SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
需要先掃描100000條記錄，再取20條。
優(yōu)化方案：
-- 使用游標分頁 SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20; -- 或者記錄上次查詢的最大ID SELECT * FROM orders WHERE id > last_max_id ORDER BY id LIMIT 20;
性能對比：
- LIMIT 1000,20: 0.01s
- LIMIT 100000,20: 2.3s
- WHERE id > 100000 LIMIT 20: 0.01s
10. 批量操作：大幅減少IO次數(shù)
錯誤示范（Java示例）：
for (User user : userList) {
String sql = "INSERT INTO users(name, age) VALUES(?, ?)";
// 每次插入都產(chǎn)生網(wǎng)絡(luò)IO和事務(wù)開銷
}
正確做法：
-- 一次批量插入
INSERT INTO users(name, age)
VALUES('張三', 25), ('李四', 30), ('王五', 28);
性能提升： 插入1000條數(shù)據(jù)，批量操作比單條插入快50倍！
11. JOIN優(yōu)化：理解執(zhí)行計劃
需要優(yōu)化的子查詢：
SELECT * FROM products
WHERE category_id IN (
SELECT id FROM categories WHERE type = 'electronic'
);
優(yōu)化為JOIN：
SELECT p.* FROM products p INNER JOIN categories c ON p.category_id = c.id WHERE c.type = 'electronic';
進階技巧： 使用STRAIGHT_JOIN指導(dǎo)優(yōu)化器
SELECT p.* FROM products p STRAIGHT_JOIN categories c ON p.category_id = c.id WHERE c.type = 'electronic';
12. 覆蓋索引：避免回表查詢
什么是回表查詢？
-- 假設(shè)在age字段有索引 SELECT name FROM users WHERE age > 18;
需要先查索引找到主鍵，再用主鍵查數(shù)據(jù)行。
覆蓋索引解決方案：
-- 建立復(fù)合索引 CREATE INDEX idx_users_age_name ON users(age, name); -- 現(xiàn)在查詢直接在索引中完成 SELECT name FROM users WHERE age > 18;
性能提升： 減少一次磁盤IO，性能提升30%-50%。
四、設(shè)計優(yōu)化篇
13. 選擇合適的數(shù)據(jù)類型
常見誤區(qū)：
-- 錯誤選擇
CREATE TABLE users (
id VARCHAR(50), -- 應(yīng)該用INT/BIGINT
age VARCHAR(10), -- 應(yīng)該用TINYINT
create_time VARCHAR(20) -- 應(yīng)該用DATETIME
);
優(yōu)化方案：
-- 正確選擇
CREATE TABLE users (
id BIGINT AUTO_INCREMENT,
age TINYINT UNSIGNED,
create_time DATETIME,
PRIMARY KEY(id)
);
14. 謹慎使用NULL值
NULL值的問題：
-- 查詢變得復(fù)雜 SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; -- 聚合函數(shù)忽略NULL SELECT AVG(age) FROM users; -- 忽略NULL值
解決方案：
-- 設(shè)置默認值
CREATE TABLE users (
phone VARCHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
);
15. 反規(guī)范化：用空間換時間
規(guī)范化設(shè)計（3NF）：
-- 多表關(guān)聯(lián)查詢 SELECT u.name, o.order_no, p.product_name FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE u.id = 123;
反規(guī)范化設(shè)計：
-- 單表查詢（在orders表中冗余用戶和商品信息） SELECT order_no, user_name, product_name FROM orders WHERE user_id = 123;
適用場景：
- 讀多寫少的業(yè)務(wù)
- 報表統(tǒng)計類查詢
- 需要極致性能的場景
五、實戰(zhàn)案例篇
16. 案例：電商訂單查詢優(yōu)化
原始慢查詢（執(zhí)行時間：2.3s）：
SELECT * FROM orders
WHERE user_id = 123
AND status IN ('paid', 'shipped')
AND create_time BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY create_time DESC;
優(yōu)化步驟：
步驟1：分析執(zhí)行計劃
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status IN ('paid', 'shipped');
步驟2：創(chuàng)建復(fù)合索引
CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, create_time);
步驟3：優(yōu)化查詢語句
SELECT order_id, user_id, amount, status, create_time
FROM orders
WHERE user_id = 123
AND status IN ('paid', 'shipped')
AND create_time >= '2024-01-01'
AND create_time < '2024-07-01' -- 避免BETWEEN
ORDER BY create_time DESC;
優(yōu)化結(jié)果： 2.3s → 0.02s
17. 案例：報表統(tǒng)計優(yōu)化
原始查詢（全表掃描）：
-- 每天執(zhí)行一次，但需要30秒
SELECT COUNT(*) as total_orders,
SUM(amount) as total_amount,
AVG(amount) as avg_amount
FROM orders
WHERE DATE(create_time) = CURDATE();
優(yōu)化方案：
方案1：使用范圍查詢
SELECT COUNT(*) as total_orders,
SUM(amount) as total_amount,
AVG(amount) as avg_amount
FROM orders
WHERE create_time >= DATE(CURDATE())
AND create_time < DATE(CURDATE()) + INTERVAL 1 DAY;
方案2：建立匯總表
-- 每日預(yù)聚合
CREATE TABLE order_daily_stats (
stat_date DATE,
total_orders INT,
total_amount DECIMAL(15,2),
avg_amount DECIMAL(10,2),
PRIMARY KEY(stat_date)
);
-- 查詢時直接查匯總表
SELECT * FROM order_daily_stats WHERE stat_date = CURDATE();
六、工具使用篇
18. 深入理解EXPLAIN執(zhí)行計劃
關(guān)鍵指標解讀：
EXPLAIN SELECT * FROM users WHERE age > 18;
重點關(guān)注：
- type：ALL(全表掃描) → index → range → ref → eq_ref → const
- key：實際使用的索引
- rows：預(yù)估掃描行數(shù)
- Extra：Using filesort(需要優(yōu)化), Using temporary(需要優(yōu)化)
19. 配置慢查詢?nèi)罩?/h3>
MySQL配置：
# 開啟慢查詢?nèi)罩? slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # 超過1秒的記錄 log_queries_not_using_indexes = 1 # 記錄未使用索引的查詢
分析慢查詢?nèi)罩荆?/strong>
# 使用mysqldumpslow分析 mysqldumpslow -t 10 /var/log/mysql/slow.log # 使用pt-query-digest分析 pt-query-digest /var/log/mysql/slow.log
20. 數(shù)據(jù)庫維護：定期健康檢查
日常維護命令：
-- 更新索引統(tǒng)計信息 ANALYZE TABLE users, orders, products; -- 整理表碎片（每月一次） OPTIMIZE TABLE large_table; -- 檢查未使用索引 SELECT * FROM sys.schema_unused_indexes;
自動化腳本：
-- 每周執(zhí)行一次的健康檢查 CHECK TABLE important_table; ANALYZE TABLE important_table;
總結(jié)
SQL優(yōu)化不是一蹴而就的，需要持續(xù)觀察、分析和調(diào)整。索引是利器，但同時也要用對地方。
到此這篇關(guān)于MySQL中SQL查詢速度優(yōu)化的20個技巧分享的文章就介紹到這了,更多相關(guān)MySQL SQL優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL字符串轉(zhuǎn)數(shù)字的3種方式實例
這篇文章主要給大家介紹了關(guān)于MySQL字符串轉(zhuǎn)數(shù)字的3種方式,在使用mysql中經(jīng)常遇到要將字符串數(shù)字轉(zhuǎn)換成可計算數(shù)字,文中給出了詳細的代碼示例和圖文介紹,需要的朋友可以參考下2023-08-08
本文給大家介紹Mysql取前一天、前一周、后一天等時間函數(shù),本文通過實例代碼給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧2023-11-11
MYSQL LAG()與LEAD()這兩個函數(shù)是偏移量函數(shù)，可以查出一個字段的前面N個值或者后面N個值，本文詳細的介紹一下這兩個函數(shù)的區(qū)別，感興趣的可以了解一下2023-05-05
這篇文章主要介紹了MySQL數(shù)據(jù)完整性，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
MySQL 配置文件 my.cnf / my.ini 區(qū)別解析
充分理解 MySQL 配置文件中各個變量的意義對我們有針對性的優(yōu)化 MySQL 數(shù)據(jù)庫性能有非常大的意義，這篇文章主要介紹了MySQL 配置文件 my.cnf / my.ini 區(qū)別,需要的朋友可以參考下2022-11-11
本文主要介紹了數(shù)據(jù)庫分區(qū)的基本概念,分區(qū)類型以及如何在MySQL中實現(xiàn)分區(qū),可以提高查詢性能和管理效率,實現(xiàn)分區(qū)需要根據(jù)具體的業(yè)務(wù)需求選擇合適的分區(qū)類型,感興趣的可以了解一下2024-10-10
mysql報錯：Deadlock found when trying to get lock; try restarti
這篇文章主要給大家介紹了關(guān)于mysql出現(xiàn)報錯：Deadlock found when trying to get lock; try restarting transaction的解決方法，文中通過示例代碼介紹的非常詳細，對大家具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起看看吧。2017-07-07
在MySQL中,我們經(jīng)常需要對數(shù)據(jù)進行拆分和處理,其中一個常見需求就是按逗號分割字符串,具有一定的參考價值,感興趣的可以了解一下2023-11-11
MySQL limit分頁大偏移量慢的原因及優(yōu)化方案
這篇文章主要介紹了MySQL limit分頁大偏移量慢的原因及優(yōu)化方案，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-11-11
本文給大家分享MySQL函數(shù)find_in_set場景介紹,結(jié)合實例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2025-07-07