在數(shù)據(jù)庫管理和優(yōu)化的世界里，MySQL作為一個(gè)流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，其性能優(yōu)化是任何數(shù)據(jù)密集型應(yīng)用成功的關(guān)鍵。優(yōu)化MySQL數(shù)據(jù)庫不僅可以顯著提高SQL查詢的效率，還能確保數(shù)據(jù)的穩(wěn)定性和可靠性。

在本文中，我將介紹12種提升SQL執(zhí)行效率的有效方法，并通過實(shí)用的代碼示例來具體展示如何實(shí)施這些優(yōu)化策略。

本文，已收錄于，我的技術(shù)網(wǎng)站 ddkk.com，有大廠完整面經(jīng)，工作技術(shù)，架構(gòu)師成長之路，等經(jīng)驗(yàn)分享文章來源地址http://www.zghlxwxcb.cn/news/detail-792552.html

1、使用索引優(yōu)化查詢

使用場景：當(dāng)你的數(shù)據(jù)庫表中有大量數(shù)據(jù)，而你需要頻繁進(jìn)行搜索查詢時(shí)，索引是提高查詢效率的關(guān)鍵。

代碼示例：

-- 假設(shè)我們有一個(gè)員工表 employees
CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id)
);

-- 為department_id字段創(chuàng)建索引
CREATE INDEX idx_department ON employees(department_id);

-- 使用索引進(jìn)行查詢
SELECT * FROM employees WHERE department_id = 5;

代碼解釋：

第一步是創(chuàng)建一個(gè)包含id, name, department_id字段的employees表。

然后為department_id字段創(chuàng)建一個(gè)索引idx_department。這個(gè)操作會(huì)讓基于department_id的查詢更快。

最后，我們執(zhí)行一個(gè)查詢，利用創(chuàng)建的索引，從而提高查詢效率。

最近無意間獲得一份阿里大佬寫的刷題筆記，一下子打通了我的任督二脈，進(jìn)大廠原來沒那么難。這是大佬寫的， 七千頁的BAT大佬寫的刷題筆記，讓我offer拿到手軟

2、優(yōu)化查詢語句

使用場景：避免使用高成本的SQL操作，如**SELECT ***，盡量指定需要的列，減少數(shù)據(jù)傳輸和處理時(shí)間。

代碼示例：

-- 不推薦的查詢方式
SELECT * FROM employees;

-- 推薦的查詢方式
SELECT id, name FROM employees;

代碼解釋：

第一個(gè)查詢語句使用了**SELECT ***，它會(huì)獲取所有列，這在數(shù)據(jù)量大時(shí)非常低效。

第二個(gè)查詢僅請求需要的id和name列，減少了數(shù)據(jù)處理的負(fù)擔(dān)。

3、使用查詢緩存

使用場景：當(dāng)相同的查詢被頻繁執(zhí)行時(shí)，使用查詢緩存可以避免重復(fù)的數(shù)據(jù)庫掃描。

代碼示例：

-- 啟用查詢緩存
SET global query_cache_size = 1000000;
SET global query_cache_type = 1;

-- 執(zhí)行查詢
SELECT name FROM employees WHERE department_id = 5;

代碼解釋：

通過設(shè)置query_cache_size和query_cache_type，我們啟用了查詢緩存。

當(dāng)我們執(zhí)行查詢時(shí)，MySQL會(huì)檢查緩存中是否已經(jīng)有了該查詢的結(jié)果，如果有，則直接返回結(jié)果，避免了重復(fù)的數(shù)據(jù)庫掃描。

4、避免全表掃描

使用場景：當(dāng)表中數(shù)據(jù)量巨大時(shí)，全表掃描會(huì)非常耗時(shí)。通過使用合適的查詢條件來避免全表掃描，可以顯著提高查詢效率。

代碼示例：

-- 假設(shè)我們需要查詢員工表中特定部門的員工
-- 不推薦的查詢方式，會(huì)導(dǎo)致全表掃描
SELECT * FROM employees WHERE name LIKE '%張%';

-- 推薦的查詢方式
SELECT * FROM employees WHERE department_id = 3 AND name LIKE '%張%';

代碼解釋：

第一個(gè)查詢使用了模糊匹配LIKE，但缺乏有效的過濾條件，可能導(dǎo)致全表掃描。

第二個(gè)查詢在name字段的模糊匹配前，增加了對department_id的條件過濾，這樣就可以先縮小查找范圍，避免全表掃描。

5、使用JOIN代替子查詢

使用場景：在需要關(guān)聯(lián)多個(gè)表的復(fù)雜查詢中，使用JOIN代替子查詢可以提高查詢效率。

代碼示例：

-- 假設(shè)我們有一個(gè)部門表 departments
CREATE TABLE departments (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

-- 不推薦的子查詢方式
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT');

-- 推薦的JOIN查詢方式
SELECT employees.* FROM employees JOIN departments ON employees.department_id = departments.id WHERE departments.name = 'IT';

代碼解釋：

第一個(gè)查詢使用了子查詢，這在執(zhí)行時(shí)可能效率較低，特別是當(dāng)子查詢或主查詢的結(jié)果集較大時(shí)。

第二個(gè)查詢使用了JOIN操作，這通常比子查詢更有效，尤其是在處理大型數(shù)據(jù)集時(shí)。

6、合理分頁

使用場景：在處理大量數(shù)據(jù)的列表展示時(shí)，合理的分頁策略可以減少單次查詢的負(fù)擔(dān)，提高響應(yīng)速度。

代碼示例：

-- 假設(shè)我們需要分頁顯示員工信息
-- 不推薦的分頁方式，尤其是當(dāng)offset值很大時(shí)
SELECT * FROM employees LIMIT 10000, 20;

-- 推薦的分頁方式，使用更高效的條件查詢
SELECT * FROM employees WHERE id > 10000 LIMIT 20;

代碼解釋：

第一個(gè)查詢使用了LIMIT和較大的偏移量offset，在大數(shù)據(jù)集上執(zhí)行時(shí)會(huì)逐行掃描跳過大量記錄，效率低下。

第二個(gè)查詢通過在WHERE子句中添加條件來避免不必要的掃描，從而提高分頁效率。

最近無意間獲得一份阿里大佬寫的刷題筆記，一下子打通了我的任督二脈，進(jìn)大廠原來沒那么難。這是大佬寫的， 七千頁的BAT大佬寫的刷題筆記，讓我offer拿到手軟

7、利用分區(qū)提高性能

使用場景：對于大型表，特別是那些行數(shù)以百萬計(jì)的表，使用分區(qū)可以提高查詢性能和數(shù)據(jù)管理效率。

代碼示例：

-- 假設(shè)我們需要對一個(gè)大型的訂單表 orders 進(jìn)行分區(qū)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id)
) PARTITION BY RANGE ( YEAR(order_date) ) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

-- 查詢特定年份的訂單
SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

代碼解釋：

我們?yōu)?strong>orders表創(chuàng)建了基于order_date字段的年份范圍分區(qū)。

查詢特定年份的數(shù)據(jù)時(shí)，MySQL只會(huì)在相關(guān)分區(qū)中搜索，提高了查詢效率。

8、利用批處理減少I/O操作

使用場景：在進(jìn)行大量數(shù)據(jù)插入或更新時(shí)，批處理可以減少數(shù)據(jù)庫的I/O操作次數(shù)，從而提高性能。

代碼示例：

-- 批量插入數(shù)據(jù)
INSERT INTO employees (name, department_id)
VALUES 
    ('張三', 1),
    ('李四', 2),
    ('王五', 3),
    -- 更多記錄
;

-- 批量更新數(shù)據(jù)
UPDATE employees
SET department_id = CASE name
    WHEN '張三' THEN 3
    WHEN '李四' THEN 2
    -- 更多條件
END
WHERE name IN ('張三', '李四', -- 更多名稱);

代碼解釋：

在批量插入示例中，我們一次性插入多條記錄，而不是對每條記錄進(jìn)行單獨(dú)的插入操作。

在批量更新示例中，我們使用CASE語句一次性更新多條記錄，這比單獨(dú)更新每條記錄更有效率。

9、使用臨時(shí)表優(yōu)化復(fù)雜查詢

使用場景：對于復(fù)雜的多步驟查詢，使用臨時(shí)表可以存儲(chǔ)中間結(jié)果，從而簡化查詢并提高性能。

代碼示例：

-- 創(chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)中間結(jié)果
CREATE TEMPORARY TABLE temp_employees
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

-- 使用臨時(shí)表進(jìn)行查詢
SELECT departments.name, temp_employees.emp_count
FROM departments
JOIN temp_employees ON departments.id = temp_employees.department_id;

代碼解釋：

首先，我們通過聚合查詢創(chuàng)建了一個(gè)臨時(shí)表temp_employees，用于存儲(chǔ)每個(gè)部門的員工計(jì)數(shù)。

然后，我們將這個(gè)臨時(shí)表與部門表departments進(jìn)行連接查詢，這樣的查詢通常比直接在原始表上執(zhí)行復(fù)雜的聚合查詢要高效。

10、優(yōu)化數(shù)據(jù)類型

使用場景：在設(shè)計(jì)數(shù)據(jù)庫表時(shí)，選擇合適的數(shù)據(jù)類型對性能有顯著影響。優(yōu)化數(shù)據(jù)類型可以減少存儲(chǔ)空間，提高查詢效率。

代碼示例：

-- 原始表結(jié)構(gòu)
CREATE TABLE example (
    id INT AUTO_INCREMENT,
    description TEXT,
    created_at DATETIME,
    is_active BOOLEAN,
    PRIMARY KEY (id)
);

-- 優(yōu)化后的表結(jié)構(gòu)
CREATE TABLE optimized_example (
    id MEDIUMINT AUTO_INCREMENT,
    description VARCHAR(255),
    created_at DATE,
    is_active TINYINT(1),
    PRIMARY KEY (id)
);

代碼解釋：

在原始表中，使用了INT和TEXT這樣的寬泛類型，這可能會(huì)占用更多的存儲(chǔ)空間。

在優(yōu)化后的表中，id字段改為MEDIUMINT，description改為長度有限的VARCHAR(255)，created_at只存儲(chǔ)日期，而is_active使用**TINYINT(1)**來表示布爾值。這樣的優(yōu)化減少了每行數(shù)據(jù)的大小，提高了存儲(chǔ)效率。

11、避免使用函數(shù)和操作符

使用場景：在WHERE子句中避免對列使用函數(shù)或操作符，可以讓MySQL更有效地使用索引。

代碼示例：

-- 不推薦的查詢方式，使用了函數(shù)
SELECT * FROM employees WHERE YEAR(birth_date) = 1980;

-- 推薦的查詢方式
SELECT * FROM employees WHERE birth_date BETWEEN '1980-01-01' AND '1980-12-31';

代碼解釋：

在第一個(gè)查詢中，使用 YEAR() 函數(shù)會(huì)導(dǎo)致MySQL無法利用索引，因?yàn)樗仨殞γ啃袛?shù)據(jù)應(yīng)用函數(shù)。

第二個(gè)查詢直接使用日期范圍，這樣MySQL可以有效利用birth_date字段的索引。

12、合理使用正規(guī)化和反正規(guī)化

使用場景：數(shù)據(jù)庫設(shè)計(jì)中的正規(guī)化可以減少數(shù)據(jù)冗余，而反正規(guī)化可以提高查詢效率。合理平衡這兩者，可以獲得最佳性能。

代碼示例：

-- 正規(guī)化設(shè)計(jì)
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (department_id)
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 反正規(guī)化設(shè)計(jì)
CREATE TABLE employees_denormalized (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_name VARCHAR(100),
    PRIMARY KEY (id)
);

代碼解釋：

在正規(guī)化設(shè)計(jì)中，departments和employees表被分開，減少了數(shù)據(jù)冗余，但可能需要JOIN操作來獲取完整信息。

在反正規(guī)化設(shè)計(jì)中，employees_denormalized表通過直接包含部門信息來簡化查詢，提高讀取性能，但可能會(huì)增加數(shù)據(jù)冗余和更新成本。

項(xiàng)目文檔&視頻：

開源：項(xiàng)目文檔 & 視頻 Github-Doc

總結(jié)

以上提到的優(yōu)化方法只是眾多MySQL優(yōu)化技術(shù)中的一小部分。在實(shí)際應(yīng)用中，應(yīng)根據(jù)具體的數(shù)據(jù)模式和查詢需求靈活選擇最合適的優(yōu)化策略。數(shù)據(jù)庫優(yōu)化是一個(gè)持續(xù)的過程，定期的性能評估和調(diào)優(yōu)是保持?jǐn)?shù)據(jù)庫高效運(yùn)行的關(guān)鍵。通過實(shí)踐這些優(yōu)化技巧，你可以顯著提升數(shù)據(jù)庫的性能和響應(yīng)速度。

本文，已收錄于，我的技術(shù)網(wǎng)站 ddkk.com，有大廠完整面經(jīng)，工作技術(shù)，架構(gòu)師成長之路，等經(jīng)驗(yàn)分享

到了這里，關(guān)于MySQL優(yōu)化：12種提升SQL執(zhí)行效率的有效方法的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！