快捷導(dǎo)航

MySQL中進(jìn)行SQL調(diào)優(yōu)的方法步驟

更新時(shí)間：2025年11月28日 09:03:36 作者：q***7666

文章主要介紹了SQL調(diào)優(yōu)的方法,包括合理設(shè)計(jì)索引、避免使用SELECT*、避免函數(shù)計(jì)算、避免使用%LIKE、滿足最左匹配原則、避免對無索引字段進(jìn)行排序以及利用緩存來優(yōu)化,需要的朋友可以參考下

1) 合理設(shè)計(jì)索引,利用聯(lián)合索引進(jìn)行覆蓋索引的優(yōu)化,避免回表的發(fā)生,減少一次查詢和隨機(jī) I/O
2) 避免 SELECT *,只查詢必要的字段
3) 避免在 SQL 中進(jìn)行函數(shù)計(jì)算等操作,使得無法命中索引
4) 避免使用 %LIKE,導(dǎo)致全表掃描
5) 注意聯(lián)合索引需滿足最左匹配原則
6) 不要對無索引字段進(jìn)行排序操作

SQL實(shí)戰(zhàn)演示

7) 連表查詢需要注意不同字段的字符集是否一致,否則也會導(dǎo)致全表掃描

重點(diǎn)

平時(shí)進(jìn)行 SQL 調(diào)優(yōu),主要是通過觀察慢 SQL,然后利用 explain 分析查詢語句的執(zhí)行計(jì)劃,識別性能瓶頸,優(yōu)化查詢語句。

1) 合理設(shè)計(jì)索引,利用聯(lián)合索引進(jìn)行覆蓋索引的優(yōu)化,避免回表的發(fā)生,減少一次查詢和隨機(jī) I/O

回表：索引無法滿足查詢所需的所有列數(shù)據(jù)，需要回到主表獲取額外的數(shù)據(jù)。
避免回表：創(chuàng)建覆蓋索引(索引包含了查詢所需的所有列)，讓查詢可以直接從索引中獲取所有數(shù)據(jù)，無需訪問主表。

例子：

建表和建立索引：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender CHAR(1),
    city VARCHAR(50)
);
CREATE INDEX idx_name_age_gender ON user(name, age, gender);

建立了聯(lián)合索引：name，age，gender。

若執(zhí)行SELECT city FROM user WHERE name = 'John' AND age = 25; 因?yàn)?select 需要返回city。索引中沒有city列的數(shù)據(jù)，還需要根據(jù)索引條目中包含的主鍵信息（雖然例子中沒有顯式指定，但通常索引會包含指向主鍵的指針）回到 user 表的主鍵索引中，去查找完整的行數(shù)據(jù)，這個(gè)“回到主表查找 city 列”的過程就是回表。

2) 避免 SELECT *,只查詢必要的字段

3) 避免在 SQL 中進(jìn)行函數(shù)計(jì)算等操作,使得無法命中索引

4) 避免使用 %LIKE,導(dǎo)致全表掃描

5) 注意聯(lián)合索引需滿足最左匹配原則

解釋最左匹配原則：最左匹配原則是指在使用聯(lián)合索引時(shí)，必須按照索引的順序從左到右使用，不能跳過索引中的列。

1. SQL 實(shí)戰(zhàn)理解最左匹配原則

建表語句:假設(shè)我們有一個(gè)用戶訂單表，包含用戶ID、訂單日期和訂單金額三個(gè)字段，我們對這三個(gè)字段創(chuàng)建一個(gè)聯(lián)合索引。

CREATE TABLE user_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    INDEX idx_user_date_amount (user_id, order_date, order_amount)
);

Python腳本生成測試數(shù)據(jù)：

from datetime import datetime, timedelta
import random

# 生成INSERT語句
def generate_insert_statements():
    start_date = datetime(2023, 1, 1)
    statements = []
    
    for _ in range(4200):
        user_id = random.randint(1, 1000)
        days = random.randint(0, 365)
        order_date = (start_date + timedelta(days=days)).strftime('%Y-%m-%d')
        order_amount = round(random.uniform(10.0, 1000.0), 2)
        
        insert_sql = f"INSERT INTO user_orders (user_id, order_date, order_amount) VALUES ({user_id}, '{order_date}', {order_amount});"
        statements.append(insert_sql)
    
    # 將所有INSERT語句寫入文件
    with open('insert_data.sql', 'w') as f:
        f.write('
'.join(statements))
        
    print("INSERT語句已生成到 insert_data.sql 文件中")

if __name__ == "__main__":
    generate_insert_statements()

測試不同查詢場景：

-- 完全滿足最左匹配原則（使用全部索引列）
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 100 AND order_date = '2023-05-01' AND order_amount = 500;

-- 滿足最左匹配原則（使用索引的前兩列）
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 100 AND order_date = '2023-05-01';

explain 結(jié)果：

-- 滿足最左匹配原則（只使用第一列）
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 100;

explain 結(jié)果：

-- 不滿足最左匹配原則（跳過`user_id`）
EXPLAIN SELECT * FROM user_orders 
WHERE order_date = '2023-05-01' AND order_amount = 500;

explain 結(jié)果：

-- 不滿足最左匹配原則（只使用order_date）
EXPLAIN SELECT * FROM user_orders 
WHERE order_date = '2023-05-01';

explain 結(jié)果：

-- 不滿足最左匹配原則（只使用order_amount）
EXPLAIN SELECT * FROM user_orders 
WHERE order_amount = 500;

explain 結(jié)果：

從上述explain 的結(jié)果看出，不滿足最左匹配原則，filitered 都很低。

6) 不要對無索引字段進(jìn)行排序操作

強(qiáng)制使用文件排序(filesort)：
當(dāng)對無索引字段排序時(shí)，MySQL無法利用索引的有序性，必須將數(shù)據(jù)加載到內(nèi)存中進(jìn)行排序，這就是filesort，filesort是一個(gè)非常耗費(fèi)資源的操作。
內(nèi)存開銷大
如果排序數(shù)據(jù)量小，MySQL會在內(nèi)存中完成排序，如果數(shù)據(jù)量超過sort_buffer_size，會發(fā)生磁盤文件排序，磁盤排序涉及臨時(shí)文件的創(chuàng)建和多次IO，性能更差！

SQL實(shí)戰(zhàn)演示

-- 創(chuàng)建測試表
CREATE TABLE worker(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    department VARCHAR(50),
    INDEX idx_salary (salary)  -- 只對salary創(chuàng)建索引
);

-- 插入測試數(shù)據(jù)
INSERT INTO worker(name, salary, department) VALUES
('張三', 5000, '技術(shù)部'),
('李四', 6000, '市場部'),
('王五', 4500, '技術(shù)部'),
('趙六', 7000, '銷售部');


-- 會使用索引排序的情況：
-- 只查詢索引列
SELECT salary FROM employees ORDER BY salary;
-- 或者
SELECT id, salary FROM employees ORDER BY salary;
-- 結(jié)果顯示: Using index for order by

會導(dǎo)致filesort的情況：
-- 特例：查詢所有列（SELECT *）
SELECT * FROM employees ORDER BY salary;

當(dāng)使用SELECT *時(shí)，需要回表獲取所有列的數(shù)據(jù)，這種情況下，MySQL認(rèn)為使用索引排序的成本比filesort更高。
– 對無索引的department字段排序
EXPLAIN SELECT * FROM employees ORDER BY department;
– 結(jié)果顯示: Using filesort