[[405470]]

前言

面試題來自：社招一年半面經分享(含阿里美團頭條京東滴滴)

MySQL常見的優化手段分為下面幾個方面：

SQL優化、設計優化，硬件優化等，其中每個大的方向中又包含多個小的優化點

下面我們具體來看看

SQL優化

此優化方案指的是通過優化 SQL 語句以及索引來提高 MySQL 數據庫的運行效率，具體內容如下：

分頁優化

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10; 

優化方案：

• 延遲關聯

先通過where條件提取出主鍵，在將該表與原數據表關聯，通過主鍵id提取數據行，而不是通過原來的二級索引提取數據行

例如：

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id 

• 書簽方式

書簽方式說白了就是找到limit第一個參數對應的主鍵值，再根據這個主鍵值再去過濾并limit

例如：

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10; 

索引優化

正確使用索引

假如我們沒有添加索引，那么在查詢時就會觸發全表掃描，因此查詢的數據就會很多，并且查詢效率會很低，為了提高查詢的性能，我們就需要給最常使用的查詢字段上，添加相應的索引，這樣才能提高查詢的性能

• 建立覆蓋索引

InnoDB使用輔助索引查詢數據時會回表，但是如果索引的葉節點中已經包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引

例如對于如下查詢：

select name from test where city='上海' 

我們將被查詢的字段建立到聯合索引中，這樣查詢結果就可以直接從索引中獲取

alter table test add index idx_city_name (city, name); 

• 在 MySQL 5.0 之前的版本盡量避免使用or查詢

在 MySQL 5.0 之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來替代，因為早期的 MySQL 版本使用 or 查詢可能會導致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并

索引合并簡單來說就是把多條件查詢，比如or或and查詢對多個索引分別進行條件掃描，然后將它們各自的結果進行合并，因此就不會導致索引失效的問題了

如果從Explain執行計劃的type列的值是index_merge可以看出MySQL使用索引合并的方式來執行對表的查詢

關于Explain的使用可以參考我之前的文章：最完整的Explain總結，SQL優化不再困難

• 避免在 where 查詢條件中使用 != 或者 <> 操作符

SQL中，不等于操作符會導致查詢引擎放棄索引索引，引起全表掃描，即使比較的字段上有索引

解決方法：通過把不等于操作符改成or，可以使用索引，避免全表掃描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

• 適當使用前綴索引

MySQL 是支持前綴索引的，也就是說我們可以定義字符串的一部分來作為索引

我們知道索引越長占用的磁盤空間就越大，那么在相同數據頁中能放下的索引值也就越少，這就意味著搜索索引需要的查詢時間也就越長，進而查詢的效率就會降低，所以我們可以適當的選擇使用前綴索引，以減少空間的占用和提高查詢效率

比如，郵箱的后綴都是固定的“@xxx.com”，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引

alter table test add index index2(email(6)); 

使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本

需要注意的是，前綴索引也存在缺點，MySQL無法利用前綴索引做order by和group by 操作，也無法作為覆蓋索引

• 查詢具體的字段而非全部字段

要盡量避免使用select *，而是查詢需要的字段，這樣可以提升速度，以及減少網絡傳輸的帶寬壓力

• 優化子查詢

盡量使用 Join 語句來替代子查詢，因為子查詢是嵌套查詢，而嵌套查詢會新創建一張臨時表，而臨時表的創建與銷毀會占用一定的系統資源以及花費一定的時間，同時對于返回結果集比較大的子查詢，其對查詢性能的影響更大

關于Join語句使用，可以參考我之前的文章：寫出好的Join語句，前提你得懂這些

• 小表驅動大表

我們要盡量使用小表驅動大表的方式進行查詢，也就是如果 B 表的數據小于 A 表的數據，那執行的順序就是先查 B 表再查 A 表，具體查詢語句如下：

select name from A where id in (select id from B); 

不要在列上進行運算操作

不要在列字段上進行算術運算或其他表達式運算，否則可能會導致查詢引擎無法正確使用索引，從而影響了查詢的效率

select * from test where id + 1 = 50; 
select * from test where month(updateTime) = 7; 

一個很容易踩的坑：隱式類型轉換：

select * from test where skuId=123456 

skuId這個字段上有索引，但是explain的結果卻顯示這條語句會全表掃描

原因在于skuId的字符類型是varchar(32)，比較值卻是整型，故需要做類型轉換

適當增加冗余字段

增加冗余字段可以減少大量的連表查詢，因為多張表的連表查詢性能很低，所有可以適當的增加冗余字段，以減少多張表的關聯查詢，這是以空間換時間的優化策略

正確使用聯合索引

使用了 B+ 樹的 MySQL 數據庫引擎，比如 InnoDB 引擎，在每次查詢復合字段時是從左往右匹配數據的，因此在創建聯合索引的時候需要注意索引創建的順序

例如，我們創建了一個聯合索引是idx(name,age,sex)，那么當我們使用，姓名+年齡+性別、姓名+年齡、姓名等這種最左前綴查詢條件時，就會觸發聯合索引進行查詢;然而如果非最左匹配的查詢條件，例如，性別+姓名這種查詢條件就不會觸發聯合索引

Join優化

MySQL的join語句連接表使用的是nested-loop join算法，這個過程類似于嵌套循環，簡單來說，就是遍歷驅動表(外層表)，每讀出一行數據，取出連接字段到被驅動表(內層表)里查找滿足條件的行，組成結果行

要提升join語句的性能，就要盡可能減少嵌套循環的循環次數

一個顯著優化方式是對被驅動表的join字段建立索引，利用索引能快速匹配到對應的行，避免與內層表每一行記錄做比較，極大地減少總循環次數。另一個優化點，就是連接時用小結果集驅動大結果集，在索引優化的基礎上能進一步減少嵌套循環的次數

如果難以判斷哪個是大表，哪個是小表，可以用inner join連接，MySQL會自動選擇小表去驅動大表

關于Join語句使用，可以參考我之前的文章：寫出好的Join語句，前提你得懂這些

避免使用JOIN關聯太多的表

對于 MySQL 來說，是存在關聯緩存的，緩存的大小可以由join_buffer_size參數進行設置

在 MySQL 中，對于同一個 SQL 多關聯(join)一個表，就會多分配一個關聯緩存，如果在一個 SQL 中關聯的表越多，所占用的內存也就越大

如果程序中大量的使用了多表關聯的操作，同時join_buffer_size設置的也不合理的情況下，就容易造成服務器內存溢出的情況，就會影響到服務器數據庫性能的穩定性

排序優化

利用索引掃描做排序

MySQL有兩種方式生成有序結果：其一是對結果集進行排序的操作，其二是按照索引順序掃描得出的結果自然是有序的

但是如果索引不能覆蓋查詢所需列，就不得不每掃描一條記錄回表查詢一次，這個讀操作是隨機IO，通常會比順序全表掃描還慢

因此，在設計索引時，盡可能使用同一個索引既滿足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id） 
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id; 

只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時，才能夠使用索引來對結果做排序

UNION優化

MySQL處理union的策略是先創建臨時表，然后將各個查詢結果填充到臨時表中最后再來做查詢，很多優化策略在union查詢中都會失效，因為它無法利用索引

最好手工將where、limit等子句下推到union的各個子查詢中，以便優化器可以充分利用這些條件進行優化

此外，除非確實需要服務器去重，一定要使用union all，如果不加all關鍵字，MySQL會給臨時表加上distinct選項，這會導致對整個臨時表做唯一性檢查，代價很高

慢查詢日志

出現慢查詢通常的排查手段是先使用慢查詢日志功能，查詢出比較慢的 SQL 語句，然后再通過 Explain 來查詢 SQL 語句的執行計劃，最后分析并定位出問題的根源，再進行處理

慢查詢日志指的是在 MySQL 中可以通過配置來開啟慢查詢日志的記錄功能，超過long_query_time值的 SQL 將會被記錄在日志中

我們可以通過設置“slow_query_log=1”來開啟慢查詢

需要注意的是，在開啟慢日志功能之后，會對 MySQL 的性能造成一定的影響，因此在生產環境中要慎用此功能

設計優化

盡量避免使用NULL

NULL在MySQL中不好處理，存儲需要額外空間，運算也需要特殊的運算符，含有NULL的列很難進行查詢優化

應當指定列為not null，用0、空串或其他特殊的值代替空值，比如定義為int not null default 0

最小數據長度

越小的數據類型長度通常在磁盤、內存和CPU緩存中都需要更少的空間，處理起來更快

使用最簡單數據類型

簡單的數據類型操作代價更低，比如：能使用 int 類型就不要使用 varchar 類型，因為 int 類型比 varchar 類型的查詢效率更高

盡量少定義 text 類型

text 類型的查詢效率很低，如果必須要使用 text 定義字段，可以把此字段分離成子表，需要查詢此字段時使用聯合查詢，這樣可以提高主表的查詢效率

適當分表、分庫策略

分表是指當一張表中的字段更多時，可以嘗試將一張大表拆分為多張子表，把使用比較高頻的主信息放入主表中，其他的放入子表，這樣我們大部分查詢只需要查詢字段更少的主表就可以完成了，從而有效的提高了查詢的效率

分庫是指將一個數據庫分為多個數據庫。比如我們把一個數據庫拆分為了多個數據庫，一個主數據庫用于寫入和修改數據，其他的用于同步主數據并提供給客戶端查詢，這樣就把一個庫的讀和寫的壓力，分攤給了多個庫，從而提高了數據庫整體的運行效率

常見類型選擇

整數類型寬度設置

MySQL可以為整數類型指定寬度，例如int(11)，實際上并沒有意義，它并不會限制值的范圍，對于存儲和計算來說，int(1)和int(20)是相同的

VARCHAR和CHAR類型

char類型是定長的，而varchar存儲可變字符串，比定長更省空間，但是varchar需要額外1或2個字節記錄字符串長度，更新時也容易產生碎片

需要結合使用場景來選擇：如果字符串列最大長度比平均長度大很多，或者列的更新很少，選擇varchar較合適;如果要存很短的字符串，或者字符串值長度都相同，比如MD5值，或者列數據經常變更，選擇使用char類型

DATETIME和TIMESTAMP類型

datetime的范圍更大，能表示從1001到9999年，timestamp只能表示從1970年到2038年。datetime與時區無關，timestamp顯示值依賴于時區。在大多數場景下，這兩種類型都能良好地工作，但是建議使用timestamp，因為datetime占用8個字節，timestamp只占用了4個字節，timestamp空間效率更高

BLOB和TEXT類型

blob和text都是為存儲很大數據而設計的字符串數據類型，分別采用二進制和字符方式存儲

在實際使用中，要慎用這兩種類型，它們的查詢效率很低，如果字段必須要使用這兩種類型，可以把此字段分離成子表，需要查詢此字段時使用聯合查詢，這樣可以提高主表的查詢效率

范式化

當數據較好范式化時，修改的數據更少，而且范式化的表通常要小，可以有更多的數據緩存在內存中，所以執行操作會更快

缺點則是查詢時需要更多的關聯

第一范式：字段不可分割，數據庫默認支持

第二范式：消除對主鍵的部分依賴，可以在表中加上一個與業務邏輯無關的字段作為主鍵，比如用自增id

第三范式：消除對主鍵的傳遞依賴，可以將表拆分，減少數據冗余

硬件優化

MySQL 對硬件的要求主要體現在三個方面：磁盤、網絡和內存

磁盤

磁盤應該盡量使用有高性能讀寫能力的磁盤，比如固態硬盤，這樣就可以減少 I/O 運行的時間，從而提高了 MySQL 整體的運行效率

磁盤也可以盡量使用多個小磁盤而不是一個大磁盤，因為磁盤的轉速是固定的，有多個小磁盤就相當于擁有多個并行運行的磁盤一樣

網絡

保證網絡帶寬的通暢(低延遲)以及夠大的網絡帶寬是 MySQL 正常運行的基本條件，如果條件允許的話也可以設置多個網卡，以提高網絡高峰期 MySQL 服務器的運行效率

內存

MySQL 服務器的內存越大，那么存儲和緩存的信息也就越多，而內存的性能是非常高的，從而提高了整個 MySQL 的運行效率

本文轉載自微信公眾號「月伴飛魚」，可以通過以下二維碼關注。轉載本文請聯系月伴飛魚公眾號。