鑽石舞台

點擊關注公眾號，實用技術文章及時了解

文章目錄

一、SQL語句及索引的優化

SQL語句的優化

索引的優化/如何避免索引失效

二、數據庫表結構的優化：使得數據庫結構符合三大範式與BCNF

三、系統配置的優化

四、硬件的優化

在開始介紹如何優化sql前，先附上mysql內部邏輯圖讓大家有所了解

（1）連接器：主要負責跟客戶端建立連接、獲取權限、維持和管理連接

（2）查詢緩存：優先在緩存中進行查詢，如果查到了則直接返回，如果緩存中查詢不到，在去數據庫中查詢。

MySQL緩存是默認關閉的，也就是說不推薦使用緩存，並且在MySQL8.0 版本已經將查詢緩存的整塊功能刪掉了。這主要是它的使用場景限制造成的：

（3）解析器/分析器：分析器的工作主要是對要執行的SQL語句進行詞法解析、語法解析，最終得到抽象語法樹，然後再使用預處理器對抽象語法樹進行語義校驗，判斷抽象語法樹中的表是否存在，如果存在的話，在接着判斷select投影列字段是否在表中存在等。

（4）優化器：主要將SQL經過詞法解析、語法解析後得到的語法樹，通過數據字典和統計信息的內容，再經過一系列運算 ，最終得出一個執行計劃，包括選擇使用哪個索引

（5）執行器：根據一系列的執行計劃去調用存儲引擎提供的API接口去調用操作數據，完成SQL的執行。

一、SQL語句及索引的優化

SQL語句的優化

1. 儘量避免使用子查詢

例：

SELECT*FROMt1WHEREid(SELECTidFROMt2WHEREname='chackca');

其子查詢在Mysql5.5版本里，內部執行計劃是這樣：先查外表再匹配內表，而不是先查內表t2，當外表的數據很大時，查詢速度會非常慢。

在MariaDB10/Mysql5.6版本里，採用join關聯方式對其進行了優化，這條SQL語句會自動轉換為：SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id

但請注意的是：優化只針對SELECT有效，對UPDATE/DELETE子查詢無效，固生產環境應避免使用子查詢

由於MySQL的優化器對於子查詢的處理能力比較弱，所以不建議使用子查詢，可以改寫成Inner Join，之所以 join 連接效率更高，是因為 MySQL不需要在內存中創建臨時表

2. 用IN來替換OR

另外，MySQL對於IN做了相應的優化，即將IN中的常量全部存儲在一個數組裡面，而且這個數組是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from table_name where num in(1,2,3)對於連續的數值，能用 between 就不要用 in 了；再或者使用連接來替換。

3. 讀取適當的記錄LIMIT M,N，而不要讀多餘的記錄selectid,namefromtlimit866613,20

使用上述sql語句做分頁的時候，可能有人會發現，隨着表數據量的增加，直接使用limit分頁查詢會越來越慢。

對於limit m, n的分頁查詢，越往後面翻頁（即m越大的情況下）SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行Join關聯查詢。因為MySQL 並不是跳過 offset 行，而是取offset+N行，然後放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行 SQL 改寫。

優化的方法如下：可以取前一頁的最大行數的id（將上次遍歷到的最末尾的數據ID傳給數據庫，然後直接定位到該ID處，再往後面遍歷數據），然後根據這個最大的id來限制下一頁的起點。比如此列中，上一頁最大的id是866612。sql可以採用如下的寫法：

selectid,namefromtable_namewhereid>866612limit204. 禁止不必要的Order By排序

如果我們對結果沒有排序的要求，就儘量少用排序；

如果排序字段沒有用到索引，也儘量少用排序；

另外，分組統計查詢時可以禁止其默認排序

SELECTgoods_id,count(*)FROMtGROUPBYgoods_id;

默認情況下，Mysql會對所有的GROUP BT col1,col2…的字段進行排序，也就是說上述會對 goods_id進行排序，如果想要避免排序結果的消耗，可以指定ORDER BY NULL禁止排序：

SELECTgoods_id,count(*)FROMtGROUPBYgoods_idORDERBYNULL5. 總和查詢可以禁止排重用union all

union和union all的差異主要是前者需要將結果集合併後再進行唯一性過濾操作，這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。

當然，union all的前提條件是兩個結果集沒有重複數據。所以一般是我們明確知道不會出現重複數據的時候才建議使用union all提高速度。

6. 避免隨機取記錄SELECT*FROMt1WHERE1=1ORDERBYRAND()LIMIT4;SELECT*FROMt1WHEREid>=CEIL(RAND()*1000)LIMIT4;

以上兩個語句都無法用到索引

7. 將多次插入換成批量Insert插入INSERTINTOt(id,name)VALUES(1,'aaa');INSERTINTOt(id,name)VALUES(2,'bbb');INSERTINTOt(id,name)VALUES(3,'ccc');—>INSERTINTOt(id,name)VALUES(1,'aaa'),(2,'bbb'),(3,'ccc');8. 只返回必要的列，用具體的字段列表代替 select * 語句

SELECT * 會增加很多不必要的消耗（cpu、io、內存、網絡帶寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前者也需要經常更新。所以要求直接在select後面接上字段名。

MySQL數據庫是按照行的方式存儲，而數據存取操作都是以一個頁大小進行IO操作的，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段。所以無論取一個字段還是多個字段，實際上數據庫在表中需要訪問的數據量其實是一樣的。

但是如果查詢的字段都在索引中，也就是覆蓋索引，那麼可以直接從索引中獲取對應的內容直接返回，不需要進行回表，減少IO操作。除此之外，當存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率。

9. 區分in和existsselect*from表Awhereidin(selectidfrom表B)

上面的語句相當於：

select*from表Awhereexists(select*from表Bwhere表B.id=表A.id)

區分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那麼以外層表為驅動表，先被訪問，如果是IN，那麼先執行子查詢。所以IN適合於外表大而內表小的情況；EXISTS適合於外表小而內表大的情況。

另外，in查詢在某些情況下有可能會查詢返回錯誤的結果，因此，通常是建議在確定且有限的集合時，可以使用in。如 IN （0，1，2）。

10. 優化Group By語句

如果對group by語句的結果沒有排序要求，要在語句後面加order by null（group 默認會排序）；

儘量讓group by過程用上表的索引，確認方法是explain結果里沒有Using temporary和Using filesort；

如果group by需要統計的數據量不大，儘量只使用內存臨時表；也可以通過適當調大tmp_table_size參數，來避免用到磁盤臨時表；

使用where子句替換Having子句：避免使用having子句，having只會在檢索出所有記錄之後才會對結果集進行過濾，這個處理需要排序分組，如果能通過where子句提前過濾查詢的數目，就可以減少這方面的開銷。

11. 儘量使用數字型字段

若只含數值信息的字段儘量不要設計為字符型，這會降低查詢和連接的性能。引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對於數字型而言只需要比較一次就夠了。

12. 優化Join語句

當我們執行兩個表的Join的時候，就會有一個比較的過程，逐條比較兩個表的語句是比較慢的，因此可以把兩個表中數據依次讀進一個內存塊中，在Mysql中執行：show variables like 『join_buffer_size』，可以看到join在內存中的緩存池大小，其大小將會影響join語句的性能。在執行join的時候，數據庫會選擇一個表把他要返回以及需要進行和其他表進行比較的數據放進join_buffer。

什麼是驅動表，什麼是被驅動表，這兩個概念在查詢中有時容易讓人搞混，有下面幾種情況，大家需要了解。

1.當連接查詢沒有where條件時

2.當連接查詢有where條件時，帶where條件的表是驅動表，否則是被驅動表

假設有表如右邊：t1與t2表完全一樣，a字段有索引，b無索引，t1有100條數據，t2有1000條數據

若被驅動表有索引，那麼其執行算法為：Index Nested-Loop Join（NLJ），示例如下：

1.執行語句：select * from t1 straight_join t2 on (t1.a=t2.a)；由於被驅動表t2.a是有索引的，其執行邏輯如下：

若被驅動表無索引，那麼其執行算法為：Block Nested-Loop Join（BLJ）（Block 塊，每次都會取一塊數據到內存以減少I/O的開銷），示例如下：

2.執行語句：select * from t1 straight_join t2 on (t1.a=t2.b)；由於被驅動表t2.b是沒有索引的，其執行邏輯如下：

3.另外還有一種算法為Simple Nested-Loop Join（SLJ），其邏輯為：順序取出驅動表中的每一行數據，到被驅動表去做全表掃描匹配，匹配成功則作為結果集的一部分返回。

另外，Innodb會為每個數據表分配一個存儲在磁盤的 表名.ibd 文件，若關聯的表過多，將會導致查詢的時候磁盤的磁頭移動次數過多，從而影響性能

所以實踐中，儘可能減少Join語句中的NestedLoop的循環次數：「永遠用小結果集驅動大的結果集」

性能優化，left join 是由左邊決定的，左邊一定都有，所以右邊是我們的關鍵點，建立索引要建在右邊。當然如果索引是在左邊的，我們可以考慮使用右連接，如下

select*fromatableleftjoinbtableonatable.aid=btable.bid;--最好在bid上建索引

Tips：Join左連接在右邊建立索引；組合索引則儘量將數據量大的放在左邊，在左邊建立索引

索引的優化/如何避免索引失效1.最佳左前綴法則

如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始並且不跳過索引中的列。Mysql查詢優化器會對查詢的字段進行改進，判斷查詢的字段以哪種形式組合能使得查詢更快，所有比如創建的是(a,b)索引，查詢的是(b,a)，查詢優化器會修改成(a,b)後使用索引查詢。

2.不在索引列上做任何操作

1.計算：對索引進行表達式計算會導致索引失效，如where id + 1 = 10，可以轉換成where id = 10 -1，這樣就可以走索引

2.函數：select * from t_user where length(name)=6;此語句對字段使用到了函數，會導致索引失效

從 MySQL 8.0 開始，索引特性增加了函數索引，即可以針對函數計算後的值建立一個索引，也就是說該索引的值是函數計算後的值，所以就可以通過掃描索引來查詢數據。

altertablet_useraddkeyidx_name_length((length(name)));

（自動/手動）類型轉換

Mysql 在執行上述語句時，會把字段轉換為數字再進行比較，所以上面那條語句就相當於：select * from user where CAST(phone AS signed int) = 13030303030; CAST 函數是作用在了 phone 字段，而 phone 字段是索引，也就是對索引使用了函數！所以索引失效

MySQL 在遇到字符串和數字比較的時候，會自動把字符串轉為數字，然後再進行比較。以上這條語句相當於：select * from user where id = CAST(「1」 AS signed int)，索引字段並沒有用任何函數，CAST 函數是用在了輸入參數，因此是可以走索引掃描的。

3.存儲引擎不能使用索引中範圍條件右邊的列。

如這樣的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age會生效，phone的索引沒有用到。

4.儘量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致））

如select age from user，減少select *

5.mysql在使用負向查詢條件(!=、<>、not in、not exists、not like)的時候無法使用索引會導致全表掃描。

你可以想象一下，對於一棵B+樹，根節點是40，如果你的條件是等於20，就去左面查，你的條件等於50，就去右面查，但是你的條件是不等於66，索引應該咋辦？還不是遍歷一遍才知道。

6.is null, is not null也無法使用索引，在實際中儘量不要使用null（避免在 where 子句中對字段進行 null 值判斷） 不過在mysql的高版本已經做了優化，允許使用索引

對於null的判斷會導致引擎放棄使用索引而進行全表掃描。

7.like 以通配符開頭(%abc..)時，mysql索引失效會變成全表掃描的操作。

所以最好用右邊like 『abc%』。如果兩邊都要用，可以用select username from user where username like '%abc%'，其中username是必須是索引列，才可讓索引生效

假如index(a,b,c), where a=3 and b like 『abc%』 and c=4，a能用，b能用，c不能用，類似於不能使用範圍條件右邊的列的索引

對於一棵B+樹索引來講，如果根節點是字符def，假如查詢條件的通配符在後面，例如abc%，則其知道應該搜索左子樹，假如傳入為efg%，則應該搜索右子樹，如果通配符在前面%abc，則數據庫不知道應該走哪一面，就都掃描一遍了。

8.少用or，在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麼索引會失效。

select*fromt_userwhereid=1orage=18;--id有索引，name沒有，此時沒法走索引

因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描。

必須要or前後的字段都有索引，查詢才能使用上索引（分別使用，最後合併結果type = index_merge）

9.在組合/聯合索引中，將有區分度的索引放在前面

如果沒有區分度，例如用性別，相當於把整個大表分成兩部分，查找數據還是需要遍歷半個表才能找到，使得索引失去了意義。

10.使用前綴索引

短索引不僅可以提高查詢性能而且可以節省磁盤空間和I/O操作，減少索引文件的維護開銷，但缺點是不能用於 ORDER BY 和 GROUP BY 操作，也不能用於覆蓋索引。

比如有一個varchar(255)的列，如果該列在前10個或20個字符內，可以做到既使前綴索引的區分度接近全列索引，那麼就不要對整個列進行索引。為了減少key_len，可以考慮創建前綴索引，即指定一個前綴長度，可以使用count(distinct leftIndex(列名, 索引長度))/count(*)來計算前綴索引的區分度。

11.SQL 性能優化 explain 中的 type：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好。

當 type=index 時，索引物理文件全掃，速度非常慢。

二、數據庫表結構的優化：使得數據庫結構符合三大範式與BCNF

https://blog.csdn.net/qq_35642036/article/details/82809974

三、系統配置的優化四、硬件的優化

推薦

Java面試題寶典

技術內卷群，一起來學習！！

PS：因為公眾號平台更改了推送規則，如果不想錯過內容，記得讀完點一下「在看」，加個「星標」，這樣每次新文章推送才會第一時間出現在你的訂閱列表里。點「在看」支持我們吧！