MySQL中SQL查詢速度優(yōu)化的20個技巧分享
更新時間：2025年10月28日 10:10:25 作者：劉大華
這篇文章和大家系統(tǒng)分享了20種SQL優(yōu)化實用技巧,涵蓋查詢,索引,設計及工具使用,通過真實案例解析,幫助開發(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)化方案！
一、基礎優(yōu)化篇
1. 只查詢需要的字段：告別SELECT *
錯誤示范：
SELECT * FROM users WHERE status = 1;
問題分析：
- 查詢所有字段，包括大文本字段
- 網(wǎng)絡傳輸數(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ù)量場景（子查詢結果<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ù)量場景（子查詢結果>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：明確是否需要去重
需要去重：
-- 性能較差，但結果準確 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. 掌握最左前綴原則
復合索引： (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)絡IO和事務開銷
}
正確做法：
-- 一次批量插入
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指導優(yōu)化器
SELECT p.* FROM products p STRAIGHT_JOIN categories c ON p.category_id = c.id WHERE c.type = 'electronic';
12. 覆蓋索引：避免回表查詢
什么是回表查詢？
-- 假設在age字段有索引 SELECT name FROM users WHERE age > 18;
需要先查索引找到主鍵，再用主鍵查數(shù)據(jù)行。
覆蓋索引解決方案：
-- 建立復合索引 CREATE INDEX idx_users_age_name ON users(age, name); -- 現(xiàn)在查詢直接在索引中完成 SELECT name FROM users WHERE age > 18;
性能提升： 減少一次磁盤IO，性能提升30%-50%。
四、設計優(yōu)化篇
13. 選擇合適的數(shù)據(jù)類型
常見誤區(qū)：
-- 錯誤選擇
CREATE TABLE users (
id VARCHAR(50), -- 應該用INT/BIGINT
age VARCHAR(10), -- 應該用TINYINT
create_time VARCHAR(20) -- 應該用DATETIME
);
優(yōu)化方案：
-- 正確選擇
CREATE TABLE users (
id BIGINT AUTO_INCREMENT,
age TINYINT UNSIGNED,
create_time DATETIME,
PRIMARY KEY(id)
);
14. 謹慎使用NULL值
NULL值的問題：
-- 查詢變得復雜 SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; -- 聚合函數(shù)忽略NULL SELECT AVG(age) FROM users; -- 忽略NULL值
解決方案：
-- 設置默認值
CREATE TABLE users (
phone VARCHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
);
15. 反規(guī)范化：用空間換時間
規(guī)范化設計（3NF）：
-- 多表關聯(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ī)范化設計：
-- 單表查詢（在orders表中冗余用戶和商品信息） SELECT order_no, user_name, product_name FROM orders WHERE user_id = 123;
適用場景：
- 讀多寫少的業(yè)務
- 報表統(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)建復合索引
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)化結果： 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：建立匯總表
-- 每日預聚合
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í)行計劃
關鍵指標解讀：
EXPLAIN SELECT * FROM users WHERE age > 18;
重點關注：
- type：ALL(全表掃描) → index → range → ref → eq_ref → const
- key：實際使用的索引
- rows：預估掃描行數(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;
總結
SQL優(yōu)化不是一蹴而就的，需要持續(xù)觀察、分析和調(diào)整。索引是利器，但同時也要用對地方。
到此這篇關于MySQL中SQL查詢速度優(yōu)化的20個技巧分享的文章就介紹到這了,更多相關MySQL SQL優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
mysql error:#1062 Duplicate entry ‘***′ for key 1問題解決方法
今天公司的一個網(wǎng)站突然提示MySQL Error Duplicate entry '96624' for key 1錯誤，經(jīng)過分析這個問題是由于mysql表中的一個id自增長字段導致。2011-09-09
在本篇文章里小編給大家整理的是一篇關于mysql中寫判斷語句的方法總結內(nèi)容，需要的朋友們可以學習參考下。2020-09-09
MySQL?優(yōu)化?index?merge引起的死鎖分析
這篇文章主要介紹了MySQL?優(yōu)化?index?merge引起的死鎖分析,MySQL通過優(yōu)化索引合并是遇到的死鎖問題，下面具體分析需要的小伙伴可以參考一下2022-04-04
前幾天開發(fā)的同事反饋一個利用load data infile命令導入數(shù)據(jù)主鍵沖突的問題，分析后確定這個問題可能是mysql的一個bug，這里提出來給大家分享下。以免以后有童鞋遇到類似問題百思不得其解，難以入眠，哈哈。2014-08-08
本文將提供一些標準的SQL句，可供一些有需求的朋友參考2012-11-11
Linux環(huán)境下設置MySQL表名忽略大小寫的方法小結
在MySQL中,表名的大小寫敏感性取決于操作系統(tǒng)和MySQL的配置,在Unix/Linux系統(tǒng)上,表名通常是區(qū)分大小寫的,由于之前MySQL未設置忽略表名大小寫導致數(shù)據(jù)查詢失敗等問題,所以本文給大家介紹了Linux環(huán)境下設置MySQL表名忽略大小寫的方法,需要的朋友可以參考下2024-06-06
mysql中的Load data的使用方法,需學習Load data使用的朋友可以參考下2013-01-01
這篇文章主要介紹了MySQL綠色解壓縮版安裝與配置操作步驟,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2018-05-05
這篇文章主要介紹了MySQL的mysqldump工具用法詳解,同時附帶了相關Source命令的用法,詳解需要的朋友可以參考下2015-07-07
這篇文章主要介紹了Mysql中varchar長度設置方法的相關資料,本文還給大家?guī)砹藇alar類型的變化及char()和varchar()的區(qū)別介紹，非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-07-07