共計(jì) 5081 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。
這篇文章主要講解了“SQL 優(yōu)化技巧有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學(xué)習(xí)“SQL 優(yōu)化技巧有哪些”吧！
一、索引優(yōu)化
索引的數(shù)據(jù)結(jié)構(gòu)是 B+Tree，而 B+Tree 的查詢性能是比較高的，所以建立索引能提升 SQL 的查詢性能。
1、建立普通索引
對經(jīng)常出現(xiàn)在 where 關(guān)鍵字后面的表字段建立對應(yīng)的索引。
2、建立復(fù)合索引
如果 where 關(guān)鍵字后面常出現(xiàn)的有幾個(gè)字段，可以建立對應(yīng)的 復(fù)合索引。要注意可以優(yōu)化的一點(diǎn)是：將單獨(dú)出現(xiàn)最多的字段放在前面。
例如現(xiàn)在我們有兩個(gè)字段 a 和 b 經(jīng)常會同時(shí)出現(xiàn)在 where 關(guān)鍵字后面：
select * from t where a = 1 and b = 2; \* Q1 *\也有很多 SQL 會單獨(dú)使用字段 a 作為查詢條件：
select * from t where a = 2; \* Q2 *\此時(shí)，我們可以建立復(fù)合索引 index(a,b)。因?yàn)椴坏?Q1 可以利用復(fù)合索引，Q2 也可以利用復(fù)合索引。
3、最左前綴匹配原則
如果我們使用的是復(fù)合索引，應(yīng)該盡量遵循 最左前綴匹配原則。MySQL 會一直向右匹配直到遇到范圍查詢 (、、between、like) 就停止匹配。
假如此時(shí)我們有一條 SQL：
select * from t where a = 1 and b = 2 and c 3 and d = 4;那么我們應(yīng)該建立的復(fù)合索引是：index(a,b,d,c) 而不是 index(a,b,c,d)。因?yàn)樽侄?c 是范圍查詢，當(dāng) MySQL 遇到范圍查詢就停止索引的匹配了。
大家也注意到了，其實(shí) a,b,d 在 SQL 的位置是可以任意調(diào)整的，優(yōu)化器會找到對應(yīng)的復(fù)合索引。
還要注意一點(diǎn)的是：最左前綴匹配原則不但是復(fù)合索引的最左 N 個(gè)字段; 也可以是單列 (字符串類型) 索引的最左 M 個(gè)字符。
例如我們常說的 like 關(guān)鍵字，盡量不要使用全模糊查詢，因?yàn)檫@樣用不到索引;
所以建議是使用右模糊查詢：select * from t where name like 李 % (查詢所有姓李的同學(xué)的信息)。
4、索引下推
很多時(shí)候，我們還可以復(fù)合索引的 索引下推 來優(yōu)化 SQL。
例如此時(shí)我們有一個(gè)復(fù)合索引：index(name,age)，然后有一條 SQL 如下：
select * from user where name like 張 % and age = 10 and sex = m根據(jù)復(fù)合索引的最左前綴匹配原則，MySQL 匹配到復(fù)合索引 index(name,age) 的 name 時(shí)，就停止匹配了; 然后接下來的流程就是根據(jù)主鍵回表，判斷 age 和 sex 的條件是否同時(shí)滿足，滿足則返回給客戶端。
但是由于有索引下推的優(yōu)化，匹配到 name 時(shí)，不會立刻回表; 而是先判斷復(fù)合索引 index(name,age) 中的 age 是否符合條件; 符合條件才進(jìn)行回表接著判斷 sex 是否滿足，否則會被過濾掉。
那么借著 MySQL 5.6 引入的索引下推優(yōu)化，可以做到減少回表的次數(shù)。
5、覆蓋索引
很多時(shí)候，我們還可以 覆蓋索引 來優(yōu)化 SQL。
情況一：SQL 只查詢主鍵作為返回值。
主鍵索引 (聚簇索引) 的葉子節(jié)點(diǎn)是整行數(shù)據(jù)，而普通索引 (二級索引) 的葉子節(jié)點(diǎn)是主鍵的值。
所以當(dāng)我們的 SQL 只查詢主鍵值，可以直接獲取對應(yīng)葉子節(jié)點(diǎn)的內(nèi)容，而避免回表。
情況二：SQL 的查詢字段就在索引里。
復(fù)合索引：假如此時(shí)我們有一個(gè)復(fù)合索引 index(name,age)，有一條 SQL 如下：
select name,age from t where name like 張 %由于是字段 name 是右模糊查詢所以可以走復(fù)合索引，然后匹配到 name 時(shí)，不需要回表，因?yàn)?SQL 只是查詢字段 name 和 age，所以直接返回索引值就 ok 了。
6、普通索引
盡量 使用普通索引 而不是唯一索引。
首先，普通索引和唯一索引的查詢性能其實(shí)不會相差很多; 當(dāng)然了，前提是要查詢的記錄都在同一個(gè)數(shù)據(jù)頁中，否則普通索引的性能會慢很多。
但是，普通索引的更新操作性能比唯一索引更好; 其實(shí)很簡單，因?yàn)槠胀ㄋ饕芾?change buffer 來做更新操作; 而唯一索引因?yàn)橐袛喔碌闹凳欠袷俏ㄒ坏模悦看味夹枰獙⒋疟P中的數(shù)據(jù)讀取到 buffer pool 中。
7、前綴索引
我們要學(xué)會巧妙的使用 前綴索引，避免索引值過大。
例如有一個(gè)字段是 addr varchar(255)，但是如果一整個(gè)建立索引 [index(addr) ]，會很浪費(fèi)磁盤空間，所以會選擇建立前綴索引 [ index(addr(64)) ]。
建立前綴索引，一定要關(guān)注字段的區(qū)分度。例如像身份證號碼這種字段的區(qū)分度很低，只要出生地一樣，前面好多個(gè)字符都是一樣的; 這樣的話，最不理想時(shí)，可能會掃描全表。
前綴索引避免不了回表，即無法使用覆蓋索引這個(gè)優(yōu)化點(diǎn)，因?yàn)樗饕抵皇亲侄蔚那?n 個(gè)字符，需要回表才能判斷查詢值是否和字段值是一致的。
怎么解決?
倒序存儲：像身份證這種，后面的幾位區(qū)分度就非常的高了; 我們可以這么查詢：
select field_list from t where id_card = reverse(input_id_card_string2. 增加 hash 字段并為 hash 字段添加索引。
8、干凈的索引列
索引列不能參與計(jì)算，要保持索引列“干凈”。
假設(shè)我們給表 student 的字段 birthday 建立了普通索引。
下面的 SQL 語句不能利用到索引來提升執(zhí)行效率：
select * from student where DATE_FORMAT(birthday, %Y-%m-%d) = 2020-02-02我們應(yīng)該改成下面這樣：
select * from student where birthday = STR_TO_DATE(2020-02-02 , %Y-%m-%d9、擴(kuò)展索引
我們應(yīng)該盡量 擴(kuò)展索引，而不是新增索引，一個(gè)表最好不要超過 5 個(gè)索引; 一個(gè)表的索引越多，會導(dǎo)致更新操作更加耗費(fèi)性能。
二、SQL 優(yōu)化
1、Order By 優(yōu)化
1.order by 后面的字段盡量是帶索引的，這樣能避免使用 sort_buffer 進(jìn)行排序。
假如有一條 SQL，根據(jù)生日查詢所有學(xué)生的信息：select * from student order by birthday desc;
那么為了提升 SQL 的查詢性能，我們可以為 birthday 字段建立索引：
CREATE INDEX index_birthday ON student(birthday);2.select 后面不要帶上不必要的字段，因?yàn)槿绻麊涡虚L度太長導(dǎo)致查詢數(shù)據(jù)太多，MySQL 會利用 rowid 排序來代替全字段排序，這樣會導(dǎo)致多了回表的操作。
如果我們只是查詢學(xué)生的姓名、年齡和生日，千萬不要寫 select *;
而是只查詢需要的字段：select name, age, birthday。
2、Join 優(yōu)化
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS 技術(shù)社區(qū)
在使用 join 的時(shí)候，應(yīng)該讓小表做驅(qū)動表。小表：總數(shù)據(jù)量最小的表
使用 join 語句，最好保證能利用被驅(qū)動表的索引，不然只能使用 BNL(Block Nested-Loop Join)算法，還不如不用。
啟用 BKA(Batched Key Access) 算法，使得 NLJ 算法也能利用上 join_buffer，被驅(qū)動表可以批量查詢到符合條件的值，然后可以利用 MMR(Multi-Range Read) 的順序讀盤特性來提升回表效率。
如果一定要用 join，而且被驅(qū)動表沒有索引可以使用，那么我們可以利用臨時(shí)表 (create temporary table xx(…)engine=innodb;) 來讓 BNL 算法轉(zhuǎn)為 BKA 算法，從而提升查詢性能。
join_buffer 是一個(gè)無序數(shù)組，所以每次判斷都需要遍歷整個(gè) join_buffer。我們可以在業(yè)務(wù)端實(shí)現(xiàn) hash join 來提升 SQL 的執(zhí)行速度。
3、Group By 優(yōu)化
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS 技術(shù)社區(qū)
如果對 group by 語句的結(jié)果沒有排序要求，要在語句后面加 order by null。
盡量讓 group by 過程用上表的索引，不但不需要臨時(shí)表，還不需要額外的排序。
如果 group by 需要統(tǒng)計(jì)的數(shù)據(jù)量不大，盡量只使用內(nèi)存臨時(shí)表; 也可以通過適當(dāng)調(diào)大 tmp_table_size 參數(shù)，來避免用到磁盤臨時(shí)表。
如果數(shù)據(jù)量實(shí)在太大，使用 SQL_BIG_RESULT 這個(gè)提示，來告訴優(yōu)化器直接使用排序算法得到 group by 的結(jié)果。
4、OR 優(yōu)化
在 Innodb 引擎下 or 關(guān)鍵字無法使用組合索引。
假設(shè)現(xiàn)在關(guān)于訂單表有一條 SQL：
select id，product_name from orders where mobile = 12345678900 or user_id = 6;一般我們?yōu)榱颂嵘厦?SQL 的查詢效率，會想著為字段 mobile 和 user_id 建立一個(gè)復(fù)合索引 index(mobile,user_id);
可是我們使用 explain 可以發(fā)現(xiàn)執(zhí)行計(jì)劃里面并沒有提示到使用復(fù)合索引，所以 or 關(guān)鍵字無法命中 mobile + user_id 的組合索引。
那么我們可以分別為兩個(gè)字段建立普通索引，然后采用 union 關(guān)鍵字，如下所示：
(select id，product_name from orders where mobile = 12345678900) union (select id，product_name from orders where user_id = 6);此時(shí) mobile 和 user_id 字段都有索引，查詢才最高效。
5、IN 優(yōu)化
in 關(guān)鍵字適合主表大子表小，exist 關(guān)鍵字適合主表小子表大。由于查詢優(yōu)化器的不斷升級，很多場景這兩者性能差不多一樣了，可以嘗試改為 join 查詢。
假設(shè)我們現(xiàn)在有一條 SQL，要查詢 VIP 用戶的所有訂單數(shù)據(jù)：
select id from orders where user_id in (select id from user where level = VIP我們可以發(fā)現(xiàn)不會有任何關(guān)于索引的優(yōu)化，所以我們可以采用 join 查詢，如下所示：
select o.id from orders o join user u on o.user_id = u.id and u.level = VIP此時(shí)被驅(qū)動表應(yīng)該是 user，那么可以利用到 user 表的主鍵索引，即可以使用 BKA 算法來提升 join 查詢的性能。
6、Like 優(yōu)化
like 用于模糊查詢，但是如果是全模糊查詢，將不能命中對應(yīng)字段的索引。
假設(shè)現(xiàn)在關(guān)于學(xué)生表有一條 SQL：
SELECT name,age,birthday FROM student WHERE name like % 張 %使用 explain 可以發(fā)現(xiàn)執(zhí)行計(jì)劃提示查詢未命中索引。
因?yàn)楸緛硇枨缶褪遣樵冃諒埖乃型瑢W(xué)信息，所以沒必要使用全模糊查詢，使用右模糊查詢即可。
換成下面的寫法：
SELECT name,age,birthday FROM student WHERE name like 張 %但是產(chǎn)品經(jīng)理一定要前后模糊匹配呢? 全文索引 FULLTEXT 可以嘗試一下，但是 MySQL 的全文索引不支持中文查詢的。
所以說 Elasticsearch 才是終極武器!
三、數(shù)據(jù)表設(shè)計(jì)優(yōu)化
1、數(shù)據(jù)類型：應(yīng)該選擇更簡單或者占用空間更小的類型。
整型選擇：可以根據(jù)長度選擇 tinyint、smallint、medium_int，而不是直接使用 int。
字符串選擇：能確定字符串長度的，盡量使用 char 類型，而不是變長的 varchar 類型。
浮點(diǎn)型選擇：精度要求比較高的使用 decimal 而不是 double; 也可以考慮使用 BIGINT 來保存，小數(shù)位保存可以使用乘以整百來解決。
日期選擇：盡量使用 timestamp 而不是 datetime。
2、避免空值：
NULL 值依然會占用空間，并且會使索引更新更加復(fù)雜，更新 NULL 時(shí)容易發(fā)生索引分裂的現(xiàn)象。
可以使用有意義的值來代替 NULL 值，例如“none”字符串等等。
3、超長字符串：
一般超長字符串，varchar 難以存儲，我們一般會使用 text 類型。
但是 text 類型的字段盡量避免放在主表中，而是抽出來在子表里，用業(yè)務(wù)主鍵關(guān)聯(lián)。
感謝各位的閱讀，以上就是“SQL 優(yōu)化技巧有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對 SQL 優(yōu)化技巧有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！
