天天看點
傳回

SQL優化2020最全幹貨總結---MySQLSELECT語句 - 文法順序：SELECT語句 - 執行順序：資料除重SQL優化政策總結

前言

BATJTMD等大廠的面試難度越來越高，但無論從大廠還是到小公司，一直未變的一個重點就是對SQL優化經驗的考察。一提到資料庫，先“說一說你對SQL優化的見解吧？”。

SQL優化已經成為衡量程式猿優秀與否的硬性名額，甚至在各大廠招聘崗位職能上都有明碼标注，如果是你，在這個問題上能吊打面試官還是會被吊打呢？

目錄

SELECT語句 - 文法順序：

SELECT語句 - 執行順序：

SQL優化政策

一、避免不走索引的場景

二、SELECT語句其他優化

三、增删改 DML 語句優化

四、查詢條件優化

五、建表優化

    • *

有朋友疑問到，SQL優化真的有這麼重要麼？如下圖所示，SQL優化在提升系統性能中是：（成本最低 && 優化效果最明顯） 的途徑。如果你的團隊在SQL優化這方面搞得很優秀，對你們整個大型系統可用性方面無疑是一個質的跨越，真的能讓你們老闆省下不止幾沓子錢。

  • 優化成本：硬體>系統配置>資料庫表結構>SQL及索引。
  • 優化效果：硬體<系統配置<資料庫表結構 
String result = "嗯，不錯，";

if ("SQL優化經驗足") {
    if ("熟悉事務鎖") {
        if ("并發場景處理666") {
            if ("會打王者榮耀") {
                result += "明天入職" 
            }
        }
    }
} else {
    result += "先回去等消息吧";
} 

Logger.info("面試官：" + result );

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

好了我們言歸正傳，首先，對于MySQL層優化我一般遵從五個原則：

  1. 減少資料通路：設定合理的字段類型，啟用壓縮，通過索引通路等減少磁盤IO
  2. 傳回更少的資料：隻傳回需要的字段和資料分頁處理 減少磁盤io及網絡io
  3. 減少互動次數：批量DML操作，函數存儲等減少資料連接配接次數
  4. 減少伺服器CPU開銷：盡量減少資料庫排序操作以及全表查詢，減少cpu 記憶體占用
  5. 利用更多資源：使用表分區，可以增加并行操作，更大限度利用cpu資源

總結到SQL優化中，就三點:

  • 最大化利用索引；
  • 盡可能避免全表掃描；
  • 減少無效資料的查詢；

了解SQL優化原理 ，首先要搞清楚SQL執行順序：

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>

FROM

<表名> # 選取表，将多個表資料通過笛卡爾積變成一個表。

ON

<篩選條件> # 對笛卡爾積的虛表進行篩選

JOIN  

# 指定join，用于添加資料到on之後的虛表中，例如left join會将左表的剩餘資料添加到虛表中

WHERE

# 對上述虛表進行篩選

GROUP BY

<分組條件> # 分組

# 用于having子句進行判斷，在書寫上這類聚合函數是寫在having判斷裡面的

HAVING

<分組篩選> # 對分組後的結果進行聚合篩選

SELECT

<傳回資料清單> # 傳回的單列必須在group by子句中，聚合函數除外

DISTINCT

資料除重

ORDER BY

<排序條件> # 排序

LIMIT

<行數限制>

聲明：以下SQL優化政策适用于資料量較大的場景下，如果資料量較小，沒必要以此為準，以免畫蛇添足。

1. 盡量避免在字段開頭模糊查詢，會導緻資料庫引擎放棄索引進行全表掃描。如下：

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

優化方式：盡量在字段後面使用模糊查詢。如下：

SELECT * FROM t WHERE username LIKE '陳%'

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

  • 使用MySQL内置函數INSTR(str,substr) 來比對，作用類似于java中的indexOf()，查詢字元串出現的角标位置
  • 使用FullText全文索引，用match against 檢索
  • 資料量較大的情況，建議引用ElasticSearch、solr，億級資料量檢索速度秒級
  • 當表資料量較少（幾千條兒那種），别整花裡胡哨的，直接用like '%xx%'。

2. 盡量避免使用in 和not in，會導緻引擎走全表掃描。如下：

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

優化方式：如果是連續數值，可以用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);

3. 盡量避免使用 or，會導緻資料庫引擎放棄索引進行全表掃描。如下：

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

優化方式：可以用union代替or。如下：

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

4. 盡量避免進行null值的判斷，會導緻資料庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE score IS NULL

優化方式：可以給字段添加預設值0，對0值進行判斷。如下：

SELECT * FROM t WHERE score = 0`

5.盡量避免在where條件中等号的左側進行表達式、函數操作，會導緻資料庫引擎放棄索引進行全表掃描。

可以将表達式、函數操作移動到等号右側。如下：

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

6. 當資料量大時，避免使用where 1=1的條件。通常為了友善拼裝查詢條件，我們會預設使用該條件，資料庫引擎會放棄索引進行全表掃描。如下：

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

優化方式：用代碼拼裝sql時進行判斷，沒 where 條件就去掉 where，有where條件就加 and。

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

使用索引列作為條件進行查詢時，需要避免使用<>或者!=等判斷條件。如确實業務需要，使用到不等于符号，需要在重新評估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

8. where條件僅包含複合索引非前置列

如下：複合（聯合）索引包含key_part1，key_part2，key_part3三列，但SQL語句沒有包含索引前置列"key_part1"，按照MySQL聯合索引的最左比對原則，不會走聯合索引。

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

9. 隐式類型轉換造成不使用索引

如下SQL語句由于索引對列類型為varchar，但給定的值為數值，涉及隐式類型轉換，造成不能正确走索引。

select col1 from table where col_varchar=123;

10. order by 條件要與where中條件一緻，否則order by不會利用索引進行排序

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

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

對于上面的語句，資料庫的處理順序是：

  • 第一步：根據where條件和統計資訊生成執行計劃，得到資料。
  • 第二步：将得到的資料排序。當執行處理資料（order by）時，資料庫會先檢視第一步的執行計劃，看order by 的字段是否在執行計劃中利用了索引。如果是，則可以利用索引順序而直接取得已經排好序的資料。如果不是，則重新進行排序操作。
  • 第三步：傳回排序後的資料。

當order by 中的字段出現在where條件中時，才會利用索引而不再二次排序，更準确的說，order by 中的字段在執行計劃中利用了索引時，不用排序操作。

這個結論不僅對order by有效，對其他需要排序的操作也有效。比如group by 、union 、distinct等。

11. 正确使用hint優化語句

MySQL中可以使用hint指定優化器在執行時選擇或忽略特定的索引。一般而言，處于版本變更帶來的表結構索引變化，更建議避免使用hint，而是通過Analyze table多收集統計資訊。但在特定場合下，指定hint可以排除其他索引幹擾而指定更優的執行計劃。

  1. USE INDEX 在你查詢語句中表名的後面，添加 USE INDEX 來提供希望 MySQL 去參考的索引清單，就可以讓 MySQL 不再考慮其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
  2. IGNORE INDEX 如果隻是單純的想讓 MySQL 忽略一個或者多個索引，可以使用 IGNORE INDEX 作為 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
  3. FORCE INDEX 為強制 MySQL 使用一個特定的索引，可在查詢中使用FORCE INDEX 作為Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

在查詢的時候，資料庫系統會自動分析查詢語句，并選擇一個最合适的索引。但是很多時候，資料庫系統的查詢優化器并不一定總是能使用最優索引。如果我們知道如何選擇索引，可以使用FORCE INDEX強制查詢使用指定的索引。

例如：

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

1. 避免出現select *

首先，select * 操作在任何類型資料庫中都不是一個好的SQL編寫習慣。

使用select * 取出全部列，會讓優化器無法完成索引覆寫掃描這類優化，會影響優化器對執行計劃的選擇，也會增加網絡帶寬消耗，更會帶來額外的I/O,記憶體和CPU消耗。

建議提出業務實際需要的列數，将指定列名以取代select *。

2. 避免出現不确定結果的函數

特定針對主從複制這類業務場景。由于原理上從庫複制的是主庫執行的語句，使用如now()、rand()、sysdate()、current_user()等不确定結果的函數很容易導緻主庫與從庫相應的資料不一緻。另外不确定值的函數,産生的SQL語句無法利用query cache。

3.多表關聯查詢時，小表在前，大表在後。

在MySQL中，執行 from 後的表關聯查詢是從左往右執行的（Oracle相反），第一張表會涉及到全表掃描，是以将小表放在前面，先掃小表，掃描快效率較高，在掃描後面的大表，或許隻掃描大表的前100行就符合傳回條件并return了。

例如：表1有50條資料，表2有30億條資料；如果全表掃描表2，你品，那就先去吃個飯再說吧是吧。

4. 使用表的别名

當在SQL語句中連接配接多個表時，請使用表的别名并把别名字首于每個列名上。這樣就可以減少解析的時間并減少哪些友列名歧義引起的文法錯誤。

5. 用where字句替換HAVING字句

避免使用HAVING字句，因為HAVING隻會在檢索出所有記錄之後才對結果集進行過濾，而where則是在聚合前刷選記錄，如果能通過where字句限制記錄的數目，那就能減少這方面的開銷。HAVING中的條件一般用于聚合函數的過濾，除此之外，應該将條件寫在where字句中。

where和having的差別：where後面不能使用組函數

6.調整Where字句中的連接配接順序

MySQL采用從左往右，自上而下的順序解析where子句。根據這個原理，應将過濾資料多的條件往前放，最快速度縮小結果集。

1. 大批量插入資料

如果同時執行大量的插入，建議使用多個值的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，采用方法二，隻需要解析一次就能進行資料的插入操作；
  • 在特定場景可以減少對DB連接配接次數
  • SQL語句較短，可以減少網絡傳輸的IO。

2. 适當使用commit

适當使用commit可以釋放事務占用的資源而減少消耗，commit後能釋放的資源如下：

  • 事務占用的undo資料塊；
  • 事務在redo log中記錄的資料塊；
  • 釋放事務施加的，減少鎖争用影響性能。特别是在需要使用delete删除大量資料的時候，必須分解删除量并定期commit。

3. 避免重複查詢更新的資料

針對業務中經常出現的更新行同時又希望獲得改行資訊的需求，MySQL并不支援PostgreSQL那樣的UPDATE RETURNING文法，在MySQL中可以通過變量實作。

例如，更新一行記錄的時間戳，同時希望查詢目前記錄中存放的時間戳是什麼，簡單方法實作：

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;

前後二者都需要兩次網絡來回，但使用變量避免了再次通路資料表，特别是當t1表資料量較大時，後者比前者快很多。

4.查詢優先還是更新（insert、update、delete）優先

MySQL 還允許改變語句排程的優先級，它可以使來自多個用戶端的查詢更好地協作，這樣單個用戶端就不會由于鎖定而等待很長時間。改變優先級還可以確定特定類型的查詢被處理得更快。我們首先應該确定應用的類型，判斷應用是以查詢為主還是以更新為主的，是確定查詢效率還是確定更新的效率，決定是查詢優先還是更新優先。

下面我們提到的改變排程政策的方法主要是針對隻存在表鎖的存儲引擎，比如 MyISAM 、MEMROY、MERGE，對于Innodb 存儲引擎，語句的執行是由獲得行鎖的順序決定的。MySQL 的預設的排程政策可用總結如下：

1）寫入操作優先于讀取操作。

2）對某張資料表的寫入操作某一時刻隻能發生一次，寫入請求按照它們到達的次序來處理。

3）對某張資料表的多個讀取操作可以同時地進行。MySQL 提供了幾個語句調節符，允許你修改它的排程政策：

  • LOW_PRIORITY關鍵字應用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；
  • HIGH_PRIORITY關鍵字應用于SELECT和INSERT語句；
  • DELAYED關鍵字應用于INSERT和REPLACE語句。

如果寫入操作是一個 LOW_PRIORITY（低優先級）請求，那麼系統就不會認為它的優先級高于讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。隻有在沒有其它的讀取者的時候，才允許寫入者開始操作。這種排程修改可能存在 LOW_PRIORITY寫入操作永遠被阻塞的情況。

SELECT 查詢的HIGH_PRIORITY（高優先級）關鍵字也類似。它允許SELECT 插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先級更高。另外一種影響是，高優先級的 SELECT 在正常的 SELECT 語句之前執行，因為這些語句會被寫入操作阻塞。如果希望所有支援LOW_PRIORITY 選項的語句都預設地按照低優先級來處理，那麼 請使用--low-priority-updates 選項來啟動伺服器。通過使用 INSERTHIGH_PRIORITY 來把 INSERT 語句提高到正常的寫入優先級，可以消除該選項對單個INSERT語句的影響。

1. 對于複雜的查詢，可以使用中間臨時表 暫存資料

2. 優化group by語句

預設情況下，MySQL 會對GROUP BY分組的所有值進行排序，如 “GROUP BY col1，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1，col2，...;” 如果顯式包括一個包含相同的列的 ORDER BY子句，MySQL 可以毫不減速地對它進行優化，盡管仍然進行排序。

是以，如果查詢包括 GROUP BY 但你并不想對分組的值進行排序，你可以指定 ORDER BY NULL禁止排序。例如：

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

3. 優化join語句

MySQL中可以通過子查詢來使用 SELECT 語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的 SQL 操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接配接(JOIN)..替代。

例子：假設要将所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：

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

如果使用連接配接(JOIN).. 來完成這個查詢工作，速度将會有所提升。尤其是當 salesinfo表中對 CustomerID 建有索引的話，性能将會更好，查詢如下：

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

連接配接(JOIN).. 之是以更有效率一些，是因為 MySQL 不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

4. 優化union查詢

MySQL通過建立并填充臨時表的方式來執行union查詢。除非确實要消除重複的行，否則建議使用union all。原因在于如果沒有all這個關鍵詞，MySQL會給臨時表加上distinct選項，這會導緻對整個臨時表的資料做唯一性校驗，這樣做的消耗相當高。

高效：

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';

5.拆分複雜SQL為多個小SQL，避免大事務

  • 簡單的SQL容易使用到MySQL的QUERY CACHE；
  • 減少鎖表時間特别是使用MyISAM存儲引擎的表；
  • 可以使用多核CPU。

6. 使用truncate代替delete

當删除全表中記錄時，使用delete語句的操作會被記錄到undo塊中，删除記錄也記錄binlog，當确認需要删除全表時，會産生很大量的binlog并占用大量的undo資料塊，此時既沒有很好的效率也占用了大量的資源。

使用truncate替代，不會記錄可恢複的資訊，資料不能被恢複。也是以使用truncate操作有其極少的資源占用與極快的時間。另外，使用truncate可以回收表的水位，使自增字段值歸零。

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

使用合理的分頁方式以提高分頁效率 針對展現等分頁需求，合适的分頁方式能夠提高分頁的效率。

案例1：

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

上述例子通過一次性根據過濾條件取出所有字段進行排序傳回。資料通路開銷=索引IO+索引全部記錄結果對應的表資料IO。是以，該種寫法越翻到後面執行效率越差，時間越長，尤其表資料量很大的時候。

适用場景：當中間結果集很小（10000行以下）或者查詢條件複雜（指涉及多個不同查詢字段或者多表連接配接）時适用。

案例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)。通過先根據過濾條件利用覆寫索引取出主鍵id進行排序，再進行join操作取出其他字段。資料通路開銷=索引IO+索引分頁後結果（例子中是15行）對應的表資料IO。是以，該寫法每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣。

适用場景：當查詢和排序字段（即where子句和order by子句涉及的字段）有對應覆寫索引時，且中間結果集很大的情況時适用。

1. 在表中建立索引，優先考慮where、order by使用到的字段。

2. 盡量使用數字型字段（如性别，男：1 女：2），若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷。

這是因為引擎在處理查詢和連接配接時會 逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。

3. 查詢資料量大的表 會造成查詢緩慢。主要的原因是掃描行數過多。這個時候可以通過程式，分段分頁進行查詢，循環周遊，将結果合并處理進行展示。要查詢100000到100050的資料，如下：

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

4. 用varchar/nvarchar 代替 char/nchar

盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段内搜尋效率顯然要高些。

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

總結

總結了2020面試題，這份面試題的包含的子產品分為19個子產品，分别是： Java 基礎、容器、多線程、反射、對象拷貝、Java Web 、異常、網絡、設計模式、Spring/Spring MVC、Spring Boot/Spring Cloud、Hibernate、MyBatis、RabbitMQ、Kafka、Zookeeper、MySQL、Redis、JVM 。

關注公衆号：程式員白楠楠

sql 存儲 Oracle 關系型資料庫 mysql java 排程 資料庫 索引 spring mysql優化總結 mysql sql優化 mysql優化建議 mysql性能優化總結 mysql優化
上一篇: 六大設計模式原則
下一篇: 七天訓練營第三天

繼續閱讀