MySQL中如何進(jìn)行SQL調(diào)優(yōu)舉例詳解
重點(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)?nbsp;select
需要 返回city
。 索引中沒有city
列的數(shù)據(jù),還需要根據(jù)索引條目中包含的主鍵信息(雖然例子中沒有顯式指定,但通常索引會(huì)包含指向主鍵的指針)回到 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('\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) 不要對無索引字段進(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會(huì)在內(nèi)存中完成排序,如果數(shù)據(jù)量超過sort_buffer_size,會(huì)發(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, '銷售部');
-- 會(huì)使用索引排序的情況: -- 只查詢索引列 SELECT salary FROM employees ORDER BY salary; -- 或者 SELECT id, salary FROM employees ORDER BY salary; -- 結(jié)果顯示: Using index for order by
會(huì)導(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
7) 連表查詢需要注意不同字段的字符集是否一致,否則也會(huì)導(dǎo)致全表掃描
除此之外,還可以利用緩存來優(yōu)化,一些變化少或者訪問頻繁的數(shù)據(jù)設(shè)置到緩存中,減輕數(shù)據(jù)庫的壓力,提升查詢的效率。
還可以通過業(yè)務(wù)來優(yōu)化,例如少展示一些不必要的字段,減少多表查詢的情況,將列表查詢替換成分頁分批查詢等等。
總結(jié)
到此這篇關(guān)于MySQL中如何進(jìn)行SQL調(diào)優(yōu)的文章就介紹到這了,更多相關(guān)MySQL進(jìn)行SQL調(diào)優(yōu)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
DQL命令查詢數(shù)據(jù)實(shí)現(xiàn)方法詳解
DQL(Data?Query?Language,數(shù)據(jù)查詢語言),查詢數(shù)據(jù)庫數(shù)據(jù),如SELECT語句,簡單的單表查詢或多表的復(fù)雜查詢和嵌套查詢,數(shù)據(jù)庫語言中最核心、最重要的語句,使用頻率最高的語句2022-09-09老鳥帶你開發(fā)專業(yè)規(guī)范的MySQL啟動(dòng)腳本
這篇文章主要介紹了老鳥帶你開發(fā)專業(yè)規(guī)范的MySQL啟動(dòng)腳本,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09深入理解MySQL主從復(fù)制線程狀態(tài)轉(zhuǎn)變
這篇文章主要給大家介紹了關(guān)于MySQL主從復(fù)制線程狀態(tài)轉(zhuǎn)變的相關(guān)資料,文中介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02小心陷阱!MySQL中處理Null時(shí)需注意兩點(diǎn)
這篇文章主要為大家介紹了MySQL中處理Null時(shí)需注意的兩點(diǎn),很關(guān)鍵的兩點(diǎn),大家千萬要小心2016-06-06