MySQL中如何進行SQL調(diào)優(yōu)舉例詳解
重點
平時進行 SQL 調(diào)優(yōu),主要是通過觀察慢 SQL,然后利用 explain 分析查詢語句的執(zhí)行計劃,識別性能瓶頸,優(yōu)化查詢語句。
1) 合理設(shè)計索引,利用聯(lián)合索引進行覆蓋索引的優(yōu)化,避免回表的發(fā)生,減少一次查詢和隨機 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; 因為 select 需要 返回city。 索引中沒有city列的數(shù)據(jù)，還需要根據(jù)索引條目中包含的主鍵信息（雖然例子中沒有顯式指定，但通常索引會包含指向主鍵的指針）回到 user 表的主鍵索引中，去查找完整的行數(shù)據(jù)，這個“回到主表查找 city 列”的過程就是回表 。
2) 避免 SELECT *,只查詢必要的字段
3) 避免在 SQL 中進行函數(shù)計算等操作,使得無法命中索引
4) 避免使用 %LIKE,導致全表掃描
5) 注意聯(lián)合索引需滿足最左匹配原則
解釋最左匹配原則：最左匹配原則是指在使用聯(lián)合索引時，必須按照索引的順序從左到右使用，不能跳過索引中的列。
1. SQL 實戰(zhàn)理解 最左匹配原則建表語句:假設(shè)我們有一個用戶訂單表，包含用戶ID、訂單日期和訂單金額三個字段，我們對這三個字段創(chuàng)建一個聯(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('\n'.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) 不要對無索引字段進行排序操作
強制使用文件排序(filesort)：
當對無索引字段排序時，MySQL無法利用索引的有序性，必須將數(shù)據(jù)加載到內(nèi)存中進行排序，這就是filesort，filesort是一個非常耗費資源的操作。內(nèi)存開銷大
如果排序數(shù)據(jù)量小，MySQL會在內(nèi)存中完成排序，如果數(shù)據(jù)量超過sort_buffer_size，會發(fā)生磁盤文件排序，磁盤排序涉及臨時文件的創(chuàng)建和多次IO，性能更差！
SQL實戰(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
會導致filesort的情況： -- 特例：查詢所有列（SELECT *） SELECT * FROM employees ORDER BY salary;
- 當使用
SELECT *時，需要回表獲取所有列的數(shù)據(jù)，這種情況下，MySQL認為使用索引排序的成本比filesort更高。
-- 對無索引的department字段排序 EXPLAIN SELECT * FROM employees ORDER BY department; -- 結(jié)果顯示: Using filesort
7) 連表查詢需要注意不同字段的字符集是否一致,否則也會導致全表掃描
除此之外,還可以利用緩存來優(yōu)化,一些變化少或者訪問頻繁的數(shù)據(jù)設(shè)置到緩存中,減輕數(shù)據(jù)庫的壓力,提升查詢的效率。
還可以通過業(yè)務來優(yōu)化,例如少展示一些不必要的字段,減少多表查詢的情況,將列表查詢替換成分頁分批查詢等等。
總結(jié)
到此這篇關(guān)于MySQL中如何進行SQL調(diào)優(yōu)的文章就介紹到這了,更多相關(guān)MySQL進行SQL調(diào)優(yōu)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于mysql查看死鎖與去除死鎖的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
MySQL 社區(qū)版 5.7.17 發(fā)布了，MySQL 是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典 MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品，是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。下面這篇文章主要介紹了Mysql 5.7.17 解壓版的安裝步驟，并且介紹了可能會遇到的坑，需要的朋友可以參考下。2017-01-01
mysql安裝navicat之后,出現(xiàn)2059,Authentication plugin及本地鏈接虛擬機docker,
這篇文章主要介紹了mysql安裝navicat之后,出現(xiàn)2059,Authentication plugin及本地鏈接虛擬機docker,遠程鏈接服務器,需要的朋友可以參考下2020-06-06
本文給大家分享的是個人在使用mysql5.7修改root密碼的時候遇到的問題，以及解決方法，推薦給有相同需求的小伙伴參考下。2016-02-02
這篇文章主要給大家介紹了關(guān)于mysql自增id超大問題的排查與解決方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
MySQL中字段類型為longtext的值導出后顯示二進制串方式
這篇文章主要介紹了MySQL中字段類型為longtext的值導出后顯示二進制串方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-07-07
這篇文章主要介紹了MySQL8.0 DDL原子性特性及實現(xiàn)原理，本文給大家介紹的非常詳細，具有一定的參考借鑒價值 ,需要的朋友可以參考下2019-07-07
MySQL數(shù)據(jù)庫執(zhí)行Update卡死問題的解決方法
最近開發(fā)的時候debug到一條update的sql語句時程序就不動了,然后我就在plsql上試了一下,發(fā)現(xiàn)plsql一直在顯示正在執(zhí)行,等了好久也不出結(jié)果,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫執(zhí)行Update卡死問題的解決方法,需要的朋友可以參考下2022-05-05
這篇文章主要介紹了詳解Navicat遠程連接mysql很慢(以及數(shù)據(jù)庫連接報錯"Too many connections")解決方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
本文詳細介紹了如何使用Docker配置MySQL主從復制,首先創(chuàng)建了兩個文件夾并分別配置了`my.cnf`文件,通過執(zhí)行腳本啟動容器并配置好主從關(guān)系,文中還提到了一些常見問題的解決方法,如配置路徑錯誤導致的沖突和slave的change連接錯誤2025-01-01