BATJTMD 等大廠的面試難度越來越高，但無論從大廠還是到小公司，一直不變的重點(diǎn)就是對(duì) SQL 優(yōu)化經(jīng)驗(yàn)的考察。一提到數(shù)據(jù)庫，面試官就會(huì)問“先說一說你對(duì) SQL 優(yōu)化的見解吧?”。

[[337421]]

 

圖片來自 Pexels

SQL 優(yōu)化已經(jīng)成為衡量程序猿優(yōu)秀與否的硬性指標(biāo)，甚至在各大廠招聘崗位職能上都有明碼標(biāo)注，如果是你，在這個(gè)問題上能吊打面試官還是會(huì)被吊打呢?

有朋友疑問到，SQL 優(yōu)化真的有這么重要么?如下圖所示，SQL 優(yōu)化在提升系統(tǒng)性能中是：成本最低和優(yōu)化效果最明顯的途徑。

如果你的團(tuán)隊(duì)在 SQL 優(yōu)化這方面搞得很優(yōu)秀，對(duì)你們整個(gè)大型系統(tǒng)可用性方面無疑是一個(gè)質(zhì)的跨越，真的能讓你們老板省下不止幾沓子錢。

 

優(yōu)化成本：硬件>系統(tǒng)配置>數(shù)據(jù)庫表結(jié)構(gòu)>SQL 及索引。

優(yōu)化效果：硬件<系統(tǒng)配置<數(shù)據(jù)庫表結(jié)構(gòu)

String result = "嗯，不錯(cuò)，"; 
 
if ("SQL優(yōu)化經(jīng)驗(yàn)足") { 
    if ("熟悉事務(wù)鎖") { 
        if ("并發(fā)場景處理666") { 
            if ("會(huì)打王者榮耀") { 
                result += "明天入職"  
            } 
        } 
    } 
} else { 
    result += "先回去等消息吧"; 
}  
 
Logger.info("面試官：" + result ); 

別看了，上面這是一道送命題。

好了我們言歸正傳，首先，對(duì)于MySQL層優(yōu)化我一般遵從五個(gè)原則：

• 減少數(shù)據(jù)訪問：設(shè)置合理的字段類型，啟用壓縮，通過索引訪問等減少磁盤 IO。
• 返回更少的數(shù)據(jù)：只返回需要的字段和數(shù)據(jù)分頁處理，減少磁盤 IO 及網(wǎng)絡(luò) IO。
• 減少交互次數(shù)：批量 DML 操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)。
• 減少服務(wù)器 CPU 開銷：盡量減少數(shù)據(jù)庫排序操作以及全表查詢，減少 CPU 內(nèi)存占用。
• 利用更多資源：使用表分區(qū)，可以增加并行操作，更大限度利用 CPU 資源。

總結(jié)到 SQL 優(yōu)化中，就如下三點(diǎn)：

• 最大化利用索引。
• 盡可能避免全表掃描。
• 減少無效數(shù)據(jù)的查詢。

理解 SQL 優(yōu)化原理 ，首先要搞清楚 SQL 執(zhí)行順序。

SELECT 語句，語法順序如下：

1. SELECT  
2. DISTINCT <select_list> 
3. FROM <left_table> 
4. <join_type> JOIN <right_table> 
5. ON <join_condition> 
6. WHERE <where_condition> 
7. GROUP BY <group_by_list> 
8. HAVING <having_condition> 
9. ORDER BY <order_by_condition> 
10.LIMIT <limit_number> 

SELECT 語句，執(zhí)行順序如下：

FROM 
<表名> # 選取表，將多個(gè)表數(shù)據(jù)通過笛卡爾積變成一個(gè)表。 
ON 
<篩選條件> # 對(duì)笛卡爾積的虛表進(jìn)行篩選 
JOIN <join, left join, right join...>  
<join表> # 指定join，用于添加數(shù)據(jù)到on之后的虛表中，例如left join會(huì)將左表的剩余數(shù)據(jù)添加到虛表中 
WHERE 
<where條件> # 對(duì)上述虛表進(jìn)行篩選 
GROUP BY 
<分組條件> # 分組 
<SUM()等聚合函數(shù)> # 用于having子句進(jìn)行判斷，在書寫上這類聚合函數(shù)是寫在having判斷里面的 
HAVING 
<分組篩選> # 對(duì)分組后的結(jié)果進(jìn)行聚合篩選 
SELECT 
<返回?cái)?shù)據(jù)列表> # 返回的單列必須在group by子句中，聚合函數(shù)除外 
DISTINCT 
# 數(shù)據(jù)除重 
ORDER BY 
<排序條件> # 排序 
LIMIT 
<行數(shù)限制> 

以下 SQL 優(yōu)化策略適用于數(shù)據(jù)量較大的場景下，如果數(shù)據(jù)量較小，沒必要以此為準(zhǔn)，以免畫蛇添足。

避免不走索引的場景

①盡量避免在字段開頭模糊查詢，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

如下：

SELECT * FROM t WHERE username LIKE '%陳%' 

優(yōu)化方式：盡量在字段后面使用模糊查詢。

如下：

SELECT * FROM t WHERE username LIKE '陳%' 

如果需求是要在前面使用模糊查詢：

• 使用 MySQL 內(nèi)置函數(shù) INSTR(str，substr)來匹配，作用類似于 Java 中的 indexOf()，查詢字符串出現(xiàn)的角標(biāo)位置。
• 使用 FullText 全文索引，用 match against 檢索。
• 數(shù)據(jù)量較大的情況，建議引用 ElasticSearch、Solr，億級(jí)數(shù)據(jù)量檢索速度秒級(jí)。
• 當(dāng)表數(shù)據(jù)量較少(幾千條兒那種)，別整花里胡哨的，直接用 like '%xx%'。

②盡量避免使用 in 和 not in，會(huì)導(dǎo)致引擎走全表掃描

如下：

SELECT * FROM t WHERE id IN (2,3) 

優(yōu)化方式：如果是連續(xù)數(shù)值，可以用 between 代替。

如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3 

如果是子查詢，可以用 exists 代替。

如下：

-- 不走索引 
select * from A where A.id in (select id from B); 
-- 走索引 
select * from A where exists (select * from B where B.id = A.id); 

③盡量避免使用 or，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

如下：

SELECT * FROM t WHERE id = 1 OR id = 3 

優(yōu)化方式：可以用 union 代替 or。

如下：

SELECT * FROM t WHERE id = 1 
   UNION 
SELECT * FROM t WHERE id = 3 

④盡量避免進(jìn)行 null 值的判斷，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

如下：

SELECT * FROM t WHERE score IS NULL 

優(yōu)化方式：可以給字段添加默認(rèn)值 0，對(duì) 0 值進(jìn)行判斷。

如下：

SELECT * FROM t WHERE score = 0 

⑤盡量避免在 where 條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)，如下：

-- 全表掃描 
SELECT * FROM T WHERE score/10 = 9 
-- 走索引 
SELECT * FROM T WHERE score = 10*9 

⑥當(dāng)數(shù)據(jù)量大時(shí)，避免使用 where 1=1 的條件

通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫引擎會(huì)放棄索引進(jìn)行全表掃描。

如下：

SELECT username, age, sex FROM T WHERE 1=1 

優(yōu)化方式：用代碼拼裝 SQL 時(shí)進(jìn)行判斷，沒 where 條件就去掉 where，有 where 條件就加 and。

⑦查詢條件不能用 <> 或者 !=

使用索引列作為條件進(jìn)行查詢時(shí)，需要避免使用<>或者!=等判斷條件。

如確實(shí)業(yè)務(wù)需要，使用到不等于符號(hào)，需要在重新評(píng)估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

⑧where 條件僅包含復(fù)合索引非前置列

如下：復(fù)合(聯(lián)合)索引包含 key_part1，key_part2，key_part3 三列，但 SQL 語句沒有包含索引前置列"key_part1"，按照 MySQL 聯(lián)合索引的最左匹配原則，不會(huì)走聯(lián)合索引。

select col1 from table where key_part2=1 and key_part3=2 

⑨隱式類型轉(zhuǎn)換造成不使用索引

如下 SQL 語句由于索引對(duì)列類型為 varchar，但給定的值為數(shù)值，涉及隱式類型轉(zhuǎn)換，造成不能正確走索引。

select col1 from table where col_varchar=123;  

⑩order by 條件要與 where 中條件一致，否則 order by 不會(huì)利用索引進(jìn)行排序

如下：

-- 不走age索引 
SELECT * FROM t order by age; 
 
-- 走age索引 
SELECT * FROM t where age > 0 order by age; 

對(duì)于上面的語句，數(shù)據(jù)庫的處理順序是：

• 第一步：根據(jù) where 條件和統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃，得到數(shù)據(jù)。
• 第二步：將得到的數(shù)據(jù)排序。當(dāng)執(zhí)行處理數(shù)據(jù)(order by)時(shí)，數(shù)據(jù)庫會(huì)先查看第一步的執(zhí)行計(jì)劃，看 order by 的字段是否在執(zhí)行計(jì)劃中利用了索引。如果是，則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。如果不是，則重新進(jìn)行排序操作。
• 第三步：返回排序后的數(shù)據(jù)。

當(dāng) order by 中的字段出現(xiàn)在 where 條件中時(shí)，才會(huì)利用索引而不再二次排序，更準(zhǔn)確的說，order by 中的字段在執(zhí)行計(jì)劃中利用了索引時(shí)，不用排序操作。

這個(gè)結(jié)論不僅對(duì) order by 有效，對(duì)其他需要排序的操作也有效。比如 group by 、union 、distinct 等。

⑪正確使用 hint 優(yōu)化語句

MySQL 中可以使用 hint 指定優(yōu)化器在執(zhí)行時(shí)選擇或忽略特定的索引。

一般而言，處于版本變更帶來的表結(jié)構(gòu)索引變化，更建議避免使用 hint，而是通過 Analyze table 多收集統(tǒng)計(jì)信息。

但在特定場合下，指定 hint 可以排除其他索引干擾而指定更優(yōu)的執(zhí)行計(jì)劃：

• USE INDEX 在你查詢語句中表名的后面，添加 USE INDEX 來提供希望 MySQL 去參考的索引列表，就可以讓 MySQL 不再考慮其他可用的索引。

例子: SELECT col1 FROM table USE INDEX (mod_time, name)...

• IGNORE INDEX 如果只是單純的想讓 MySQL 忽略一個(gè)或者多個(gè)索引，可以使用 IGNORE INDEX 作為 Hint。

例子: SELECT col1 FROM table IGNORE INDEX (priority) ...

• FORCE INDEX 為強(qiáng)制 MySQL 使用一個(gè)特定的索引，可在查詢中使用FORCE INDEX 作為 Hint。

例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

在查詢的時(shí)候，數(shù)據(jù)庫系統(tǒng)會(huì)自動(dòng)分析查詢語句，并選擇一個(gè)最合適的索引。但是很多時(shí)候，數(shù)據(jù)庫系統(tǒng)的查詢優(yōu)化器并不一定總是能使用最優(yōu)索引。

如果我們知道如何選擇索引，可以使用 FORCE INDEX 強(qiáng)制查詢使用指定的索引。

例如：

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC; 

SELECT 語句其他優(yōu)化

①避免出現(xiàn) select *

首先，select * 操作在任何類型數(shù)據(jù)庫中都不是一個(gè)好的 SQL 編寫習(xí)慣。

使用 select * 取出全部列，會(huì)讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，會(huì)影響優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇，也會(huì)增加網(wǎng)絡(luò)帶寬消耗，更會(huì)帶來額外的 I/O，內(nèi)存和 CPU 消耗。

建議提出業(yè)務(wù)實(shí)際需要的列數(shù)，將指定列名以取代 select *。具體詳情見《為什么大家都說SELECT * 效率低》

②避免出現(xiàn)不確定結(jié)果的函數(shù)

特定針對(duì)主從復(fù)制這類業(yè)務(wù)場景。由于原理上從庫復(fù)制的是主庫執(zhí)行的語句，使用如 now()、rand()、sysdate()、current_user() 等不確定結(jié)果的函數(shù)很容易導(dǎo)致主庫與從庫相應(yīng)的數(shù)據(jù)不一致。

另外不確定值的函數(shù)，產(chǎn)生的 SQL 語句無法利用 query cache。

③多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后

在 MySQL 中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的(Oracle 相反)，第一張表會(huì)涉及到全表掃描。

所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前 100 行就符合返回條件并 return 了。

例如：表 1 有 50 條數(shù)據(jù)，表 2 有 30 億條數(shù)據(jù);如果全表掃描表 2，你品，那就先去吃個(gè)飯?jiān)僬f吧是吧。

④使用表的別名

當(dāng)在 SQL 語句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)列名上。這樣就可以減少解析的時(shí)間并減少哪些友列名歧義引起的語法錯(cuò)誤。

⑤用 where 字句替換 HAVING 字句

避免使用 HAVING 字句，因?yàn)?HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾，而 where 則是在聚合前刷選記錄，如果能通過 where 字句限制記錄的數(shù)目，那就能減少這方面的開銷。

HAVING 中的條件一般用于聚合函數(shù)的過濾，除此之外，應(yīng)該將條件寫在 where 字句中。

where 和 having 的區(qū)別：where 后面不能使用組函數(shù)。

⑥調(diào)整 Where 字句中的連接順序

MySQL 采用從左往右，自上而下的順序解析 where 子句。根據(jù)這個(gè)原理，應(yīng)將過濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。

增刪改 DML 語句優(yōu)化

①大批量插入數(shù)據(jù)

如果同時(shí)執(zhí)行大量的插入，建議使用多個(gè)值的 INSERT 語句(方法二)。這比使用分開 INSERT 語句快(方法一)，一般情況下批量插入效率有幾倍的差別。

方法一：

insert into T values(1,2);  
 
insert into T values(1,3);  
 
insert into T values(1,4); 

方法二：

Insert into T values(1,2),(1,3),(1,4);  

選擇后一種方法的原因有三：

• 減少 SQL 語句解析的操作，MySQL 沒有類似 Oracle 的 share pool，采用方法二，只需要解析一次就能進(jìn)行數(shù)據(jù)的插入操作。
• 在特定場景可以減少對(duì) DB 連接次數(shù)。
• SQL 語句較短，可以減少網(wǎng)絡(luò)傳輸?shù)?IO。

②適當(dāng)使用 commit

適當(dāng)使用 commit 可以釋放事務(wù)占用的資源而減少消耗，commit 后能釋放的資源如下：

• 事務(wù)占用的 undo 數(shù)據(jù)塊。
• 事務(wù)在 redo log 中記錄的數(shù)據(jù)塊。
• 釋放事務(wù)施加的，減少鎖爭用影響性能。特別是在需要使用 delete 刪除大量數(shù)據(jù)的時(shí)候，必須分解刪除量并定期 commit。

③避免重復(fù)查詢更新的數(shù)據(jù)

針對(duì)業(yè)務(wù)中經(jīng)常出現(xiàn)的更新行同時(shí)又希望獲得改行信息的需求，MySQL 并不支持 PostgreSQL 那樣的 UPDATE RETURNING 語法，在 MySQL 中可以通過變量實(shí)現(xiàn)。

例如，更新一行記錄的時(shí)間戳，同時(shí)希望查詢當(dāng)前記錄中存放的時(shí)間戳是什么?

簡單方法實(shí)現(xiàn)：

Update t1 set time=now() where col1=1;  
 
Select time from t1 where id =1; 

使用變量，可以重寫為以下方式：

Update t1 set time=now () where col1=1 and @now: = now ();  
 
Select @now;  

前后二者都需要兩次網(wǎng)絡(luò)來回，但使用變量避免了再次訪問數(shù)據(jù)表，特別是當(dāng) t1 表數(shù)據(jù)量較大時(shí)，后者比前者快很多。

④查詢優(yōu)先還是更新(insert、update、delete)優(yōu)先

MySQL 還允許改變語句調(diào)度的優(yōu)先級(jí)，它可以使來自多個(gè)客戶端的查詢更好地協(xié)作，這樣單個(gè)客戶端就不會(huì)由于鎖定而等待很長時(shí)間。改變優(yōu)先級(jí)還可以確保特定類型的查詢被處理得更快。

我們首先應(yīng)該確定應(yīng)用的類型，判斷應(yīng)用是以查詢?yōu)橹鬟€是以更新為主的，是確保查詢效率還是確保更新的效率，決定是查詢優(yōu)先還是更新優(yōu)先。

下面我們提到的改變調(diào)度策略的方法主要是針對(duì)只存在表鎖的存儲(chǔ)引擎，比如 MyISAM 、MEMROY、MERGE，對(duì)于 Innodb 存儲(chǔ)引擎，語句的執(zhí)行是由獲得行鎖的順序決定的。

MySQL 的默認(rèn)的調(diào)度策略可用總結(jié)如下：

• 寫入操作優(yōu)先于讀取操作。
• 對(duì)某張數(shù)據(jù)表的寫入操作某一時(shí)刻只能發(fā)生一次，寫入請(qǐng)求按照它們到達(dá)的次序來處理。
• 對(duì)某張數(shù)據(jù)表的多個(gè)讀取操作可以同時(shí)地進(jìn)行。

MySQL 提供了幾個(gè)語句調(diào)節(jié)符，允許你修改它的調(diào)度策略：

• LOW_PRIORITY 關(guān)鍵字應(yīng)用于 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE。
• HIGH_PRIORITY 關(guān)鍵字應(yīng)用于 SELECT 和 INSERT 語句。
• DELAYED 關(guān)鍵字應(yīng)用于 INSERT 和 REPLACE 語句。

如果寫入操作是一個(gè) LOW_PRIORITY(低優(yōu)先級(jí))請(qǐng)求，那么系統(tǒng)就不會(huì)認(rèn)為它的優(yōu)先級(jí)高于讀取操作。

在這種情況下，如果寫入者在等待的時(shí)候，第二個(gè)讀取者到達(dá)了，那么就允許第二個(gè)讀取者插到寫入者之前。

只有在沒有其它的讀取者的時(shí)候，才允許寫入者開始操作。這種調(diào)度修改可能存在 LOW_PRIORITY 寫入操作永遠(yuǎn)被阻塞的情況。

SELECT 查詢的 HIGH_PRIORITY(高優(yōu)先級(jí))關(guān)鍵字也類似。它允許 SELECT 插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優(yōu)先級(jí)更高。

另外一種影響是，高優(yōu)先級(jí)的 SELECT 在正常的 SELECT 語句之前執(zhí)行，因?yàn)檫@些語句會(huì)被寫入操作阻塞。

如果希望所有支持 LOW_PRIORITY 選項(xiàng)的語句都默認(rèn)地按照低優(yōu)先級(jí)來處理，那么請(qǐng)使用--low-priority-updates 選項(xiàng)來啟動(dòng)服務(wù)器。

通過使用 INSERTHIGH_PRIORITY 來把 INSERT 語句提高到正常的寫入優(yōu)先級(jí)，可以消除該選項(xiàng)對(duì)單個(gè) INSERT 語句的影響。

查詢條件優(yōu)化

①對(duì)于復(fù)雜的查詢，可以使用中間臨時(shí)表暫存數(shù)據(jù)

②優(yōu)化 group by 語句

默認(rèn)情況下，MySQL 會(huì)對(duì) GROUP BY 分組的所有值進(jìn)行排序，如 “GROUP BY col1，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1，col2，...;” 。

如果顯式包括一個(gè)包含相同的列的 ORDER BY 子句，MySQL 可以毫不減速地對(duì)它進(jìn)行優(yōu)化，盡管仍然進(jìn)行排序。

因此，如果查詢包括 GROUP BY 但你并不想對(duì)分組的值進(jìn)行排序，你可以指定 ORDER BY NULL 禁止排序。

例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ; 

③優(yōu)化 join 語句

MySQL 中可以通過子查詢來使用 SELECT 語句來創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。

使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的 SQL 操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接(JOIN)..替代。

例子：假設(shè)要將所有沒有訂單記錄的用戶取出來，可以用下面這個(gè)查詢完成：

ELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 

如果使用連接(JOIN)..來完成這個(gè)查詢工作，速度將會(huì)有所提升。

尤其是當(dāng) salesinfo 表中對(duì) CustomerID 建有索引的話，性能將會(huì)更好，查詢?nèi)缦拢?/p>

SELECT col1 FROM customerinfo  
   LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID  
      WHERE salesinfo.CustomerID IS NULL 

連接(JOIN)..之所以更有效率一些，是因?yàn)?MySQL 不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。

④優(yōu)化 union 查詢

MySQL 通過創(chuàng)建并填充臨時(shí)表的方式來執(zhí)行 union 查詢。除非確實(shí)要消除重復(fù)的行，否則建議使用 union all。

原因在于如果沒有 all 這個(gè)關(guān)鍵詞，MySQL 會(huì)給臨時(shí)表加上 distinct 選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性校驗(yàn)，這樣做的消耗相當(dāng)高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10  
 
UNION ALL  
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10  
 
UNION  
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

⑤拆分復(fù)雜 SQL 為多個(gè)小 SQL，避免大事務(wù)

如下：

• 簡單的 SQL 容易使用到 MySQL 的 QUERY CACHE。
• 減少鎖表時(shí)間特別是使用 MyISAM 存儲(chǔ)引擎的表。
• 可以使用多核 CPU。

⑥使用 truncate 代替 delete

當(dāng)刪除全表中記錄時(shí)，使用 delete 語句的操作會(huì)被記錄到 undo 塊中，刪除記錄也記錄 binlog。

當(dāng)確認(rèn)需要?jiǎng)h除全表時(shí)，會(huì)產(chǎn)生很大量的 binlog 并占用大量的 undo 數(shù)據(jù)塊，此時(shí)既沒有很好的效率也占用了大量的資源。

使用 truncate 替代，不會(huì)記錄可恢復(fù)的信息，數(shù)據(jù)不能被恢復(fù)。也因此使用 truncate 操作有其極少的資源占用與極快的時(shí)間。另外，使用 truncate 可以回收表的水位，使自增字段值歸零。

⑦使用合理的分頁方式以提高分頁效率

使用合理的分頁方式以提高分頁效率 針對(duì)展現(xiàn)等分頁需求，合適的分頁方式能夠提高分頁的效率。

案例 1：

select * from t where thread_id = 10000 and deleted = 0  
   order by gmt_create asc limit 0, 15; 

上述例子通過一次性根據(jù)過濾條件取出所有字段進(jìn)行排序返回。數(shù)據(jù)訪問開銷=索引 IO+索引全部記錄結(jié)果對(duì)應(yīng)的表數(shù)據(jù) IO。

因此，該種寫法越翻到后面執(zhí)行效率越差，時(shí)間越長，尤其表數(shù)據(jù)量很大的時(shí)候。

適用場景：當(dāng)中間結(jié)果集很小(10000 行以下)或者查詢條件復(fù)雜(指涉及多個(gè)不同查詢字段或者多表連接)時(shí)適用。

案例 2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15) a, t  
      where a.id = t.id; 

上述例子必須滿足 t 表主鍵是 id 列，且有覆蓋索引 secondary key：(thread_id, deleted, gmt_create)。

通過先根據(jù)過濾條件利用覆蓋索引取出主鍵 id 進(jìn)行排序，再進(jìn)行 join 操作取出其他字段。

數(shù)據(jù)訪問開銷=索引 IO+索引分頁后結(jié)果(例子中是 15 行)對(duì)應(yīng)的表數(shù)據(jù) IO。因此，該寫法每次翻頁消耗的資源和時(shí)間都基本相同，就像翻第一頁一樣。

適用場景：當(dāng)查詢和排序字段(即 where 子句和 order by 子句涉及的字段)有對(duì)應(yīng)覆蓋索引時(shí)，且中間結(jié)果集很大的情況時(shí)適用。

建表優(yōu)化

①在表中建立索引，優(yōu)先考慮 where、order by 使用到的字段。

②盡量使用數(shù)字型字段(如性別，男：1 女：2)，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。

這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

③查詢數(shù)據(jù)量大的表 會(huì)造成查詢緩慢。主要的原因是掃描行數(shù)過多。這個(gè)時(shí)候可以通過程序，分段分頁進(jìn)行查詢，循環(huán)遍歷，將結(jié)果合并處理進(jìn)行展示。

要查詢 100000 到 100050 的數(shù)據(jù)，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,*  
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050 

④用 varchar/nvarchar 代替 char/nchar。

盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時(shí)，空間就固定了， 不管是否插入值(NULL 也包含在內(nèi))，都是占用 100 個(gè)字符的空間的，如果是 varchar 這樣的變長字段， null 不占用空間。

作者：_陳哈哈

編輯：陶家龍

出處：https://sohu.gg/FGG98i