[[405470]]

前言

面試題來(lái)自：社招一年半面經(jīng)分享(含阿里美團(tuán)頭條京東滴滴)

MySQL常見(jiàn)的優(yōu)化手段分為下面幾個(gè)方面：

SQL優(yōu)化、設(shè)計(jì)優(yōu)化，硬件優(yōu)化等，其中每個(gè)大的方向中又包含多個(gè)小的優(yōu)化點(diǎn)

下面我們具體來(lái)看看

SQL優(yōu)化

此優(yōu)化方案指的是通過(guò)優(yōu)化 SQL 語(yǔ)句以及索引來(lái)提高 MySQL 數(shù)據(jù)庫(kù)的運(yùn)行效率，具體內(nèi)容如下：

分頁(yè)優(yōu)化

例如：

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

優(yōu)化方案：

• 延遲關(guān)聯(lián)

先通過(guò)where條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過(guò)主鍵id提取數(shù)據(jù)行，而不是通過(guò)原來(lái)的二級(jí)索引提取數(shù)據(jù)行

例如：

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 

• 書簽方式

書簽方式說(shuō)白了就是找到limit第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值，再根據(jù)這個(gè)主鍵值再去過(guò)濾并limit

例如：

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

索引優(yōu)化

正確使用索引

假如我們沒(méi)有添加索引，那么在查詢時(shí)就會(huì)觸發(fā)全表掃描，因此查詢的數(shù)據(jù)就會(huì)很多，并且查詢效率會(huì)很低，為了提高查詢的性能，我們就需要給最常使用的查詢字段上，添加相應(yīng)的索引，這樣才能提高查詢的性能

• 建立覆蓋索引

InnoDB使用輔助索引查詢數(shù)據(jù)時(shí)會(huì)回表，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢的字段，那它沒(méi)有必要再回表查詢了，這就叫覆蓋索引

例如對(duì)于如下查詢：

select name from test where city='上海' 

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

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

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

在 MySQL 5.0 之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來(lái)替代，因?yàn)樵缙诘?MySQL 版本使用 or 查詢可能會(huì)導(dǎo)致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并

索引合并簡(jiǎn)單來(lái)說(shuō)就是把多條件查詢，比如or或and查詢對(duì)多個(gè)索引分別進(jìn)行條件掃描，然后將它們各自的結(jié)果進(jìn)行合并，因此就不會(huì)導(dǎo)致索引失效的問(wèn)題了

如果從Explain執(zhí)行計(jì)劃的type列的值是index_merge可以看出MySQL使用索引合并的方式來(lái)執(zhí)行對(duì)表的查詢

關(guān)于Explain的使用可以參考我之前的文章：最完整的Explain總結(jié)，SQL優(yōu)化不再困難

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

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

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

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

• 適當(dāng)使用前綴索引

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

我們知道索引越長(zhǎng)占用的磁盤空間就越大，那么在相同數(shù)據(jù)頁(yè)中能放下的索引值也就越少，這就意味著搜索索引需要的查詢時(shí)間也就越長(zhǎng)，進(jìn)而查詢的效率就會(huì)降低，所以我們可以適當(dāng)?shù)倪x擇使用前綴索引，以減少空間的占用和提高查詢效率

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

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

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

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

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

要盡量避免使用select *，而是查詢需要的字段，這樣可以提升速度，以及減少網(wǎng)絡(luò)傳輸?shù)膸拤毫?/p>

• 優(yōu)化子查詢

盡量使用 Join 語(yǔ)句來(lái)替代子查詢，因?yàn)樽硬樵兪乔短撞樵儯短撞樵儠?huì)新創(chuàng)建一張臨時(shí)表，而臨時(shí)表的創(chuàng)建與銷毀會(huì)占用一定的系統(tǒng)資源以及花費(fèi)一定的時(shí)間，同時(shí)對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響更大

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

• 小表驅(qū)動(dòng)大表

我們要盡量使用小表驅(qū)動(dòng)大表的方式進(jìn)行查詢，也就是如果 B 表的數(shù)據(jù)小于 A 表的數(shù)據(jù)，那執(zhí)行的順序就是先查 B 表再查 A 表，具體查詢語(yǔ)句如下：

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

不要在列上進(jìn)行運(yùn)算操作

不要在列字段上進(jìn)行算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則可能會(huì)導(dǎo)致查詢引擎無(wú)法正確使用索引，從而影響了查詢的效率

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

一個(gè)很容易踩的坑：隱式類型轉(zhuǎn)換：

select * from test where skuId=123456 

skuId這個(gè)字段上有索引，但是explain的結(jié)果卻顯示這條語(yǔ)句會(huì)全表掃描

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

適當(dāng)增加冗余字段

增加冗余字段可以減少大量的連表查詢，因?yàn)槎鄰埍淼倪B表查詢性能很低，所有可以適當(dāng)?shù)脑黾尤哂嘧侄危詼p少多張表的關(guān)聯(lián)查詢，這是以空間換時(shí)間的優(yōu)化策略

正確使用聯(lián)合索引

使用了 B+ 樹(shù)的 MySQL 數(shù)據(jù)庫(kù)引擎，比如 InnoDB 引擎，在每次查詢復(fù)合字段時(shí)是從左往右匹配數(shù)據(jù)的，因此在創(chuàng)建聯(lián)合索引的時(shí)候需要注意索引創(chuàng)建的順序

例如，我們創(chuàng)建了一個(gè)聯(lián)合索引是idx(name,age,sex)，那么當(dāng)我們使用，姓名+年齡+性別、姓名+年齡、姓名等這種最左前綴查詢條件時(shí)，就會(huì)觸發(fā)聯(lián)合索引進(jìn)行查詢;然而如果非最左匹配的查詢條件，例如，性別+姓名這種查詢條件就不會(huì)觸發(fā)聯(lián)合索引

Join優(yōu)化

MySQL的join語(yǔ)句連接表使用的是nested-loop join算法，這個(gè)過(guò)程類似于嵌套循環(huán)，簡(jiǎn)單來(lái)說(shuō)，就是遍歷驅(qū)動(dòng)表(外層表)，每讀出一行數(shù)據(jù)，取出連接字段到被驅(qū)動(dòng)表(內(nèi)層表)里查找滿足條件的行，組成結(jié)果行

要提升join語(yǔ)句的性能，就要盡可能減少嵌套循環(huán)的循環(huán)次數(shù)

一個(gè)顯著優(yōu)化方式是對(duì)被驅(qū)動(dòng)表的join字段建立索引，利用索引能快速匹配到對(duì)應(yīng)的行，避免與內(nèi)層表每一行記錄做比較，極大地減少總循環(huán)次數(shù)。另一個(gè)優(yōu)化點(diǎn)，就是連接時(shí)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集，在索引優(yōu)化的基礎(chǔ)上能進(jìn)一步減少嵌套循環(huán)的次數(shù)

如果難以判斷哪個(gè)是大表，哪個(gè)是小表，可以用inner join連接，MySQL會(huì)自動(dòng)選擇小表去驅(qū)動(dòng)大表

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

避免使用JOIN關(guān)聯(lián)太多的表

對(duì)于 MySQL 來(lái)說(shuō)，是存在關(guān)聯(lián)緩存的，緩存的大小可以由join_buffer_size參數(shù)進(jìn)行設(shè)置

在 MySQL 中，對(duì)于同一個(gè) SQL 多關(guān)聯(lián)(join)一個(gè)表，就會(huì)多分配一個(gè)關(guān)聯(lián)緩存，如果在一個(gè) SQL 中關(guān)聯(lián)的表越多，所占用的內(nèi)存也就越大

如果程序中大量的使用了多表關(guān)聯(lián)的操作，同時(shí)join_buffer_size設(shè)置的也不合理的情況下，就容易造成服務(wù)器內(nèi)存溢出的情況，就會(huì)影響到服務(wù)器數(shù)據(jù)庫(kù)性能的穩(wěn)定性

排序優(yōu)化

利用索引掃描做排序

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

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

因此，在設(shè)計(jì)索引時(shí)，盡可能使用同一個(gè)索引既滿足排序又用于查找行

例如：

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

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時(shí)，才能夠使用索引來(lái)對(duì)結(jié)果做排序

UNION優(yōu)化

MySQL處理union的策略是先創(chuàng)建臨時(shí)表，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來(lái)做查詢，很多優(yōu)化策略在union查詢中都會(huì)失效，因?yàn)樗鼰o(wú)法利用索引

最好手工將where、limit等子句下推到union的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化

此外，除非確實(shí)需要服務(wù)器去重，一定要使用union all，如果不加all關(guān)鍵字，MySQL會(huì)給臨時(shí)表加上distinct選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表做唯一性檢查，代價(jià)很高

慢查詢?nèi)罩?/h2>

出現(xiàn)慢查詢通常的排查手段是先使用慢查詢?nèi)罩竟δ?，查詢出比較慢的 SQL 語(yǔ)句，然后再通過(guò) Explain 來(lái)查詢 SQL 語(yǔ)句的執(zhí)行計(jì)劃，最后分析并定位出問(wèn)題的根源，再進(jìn)行處理

慢查詢?nèi)罩局傅氖窃?MySQL 中可以通過(guò)配置來(lái)開(kāi)啟慢查詢?nèi)罩镜挠涗浌δ?，超過(guò)long_query_time值的 SQL 將會(huì)被記錄在日志中

我們可以通過(guò)設(shè)置“slow_query_log=1”來(lái)開(kāi)啟慢查詢

需要注意的是，在開(kāi)啟慢日志功能之后，會(huì)對(duì) MySQL 的性能造成一定的影響，因此在生產(chǎn)環(huán)境中要慎用此功能

設(shè)計(jì)優(yōu)化

盡量避免使用NULL

NULL在MySQL中不好處理，存儲(chǔ)需要額外空間，運(yùn)算也需要特殊的運(yùn)算符，含有NULL的列很難進(jìn)行查詢優(yōu)化

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

最小數(shù)據(jù)長(zhǎng)度

越小的數(shù)據(jù)類型長(zhǎng)度通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來(lái)更快

使用最簡(jiǎn)單數(shù)據(jù)類型

簡(jiǎn)單的數(shù)據(jù)類型操作代價(jià)更低，比如：能使用 int 類型就不要使用 varchar 類型，因?yàn)?int 類型比 varchar 類型的查詢效率更高

盡量少定義 text 類型

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

適當(dāng)分表、分庫(kù)策略

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

分庫(kù)是指將一個(gè)數(shù)據(jù)庫(kù)分為多個(gè)數(shù)據(jù)庫(kù)。比如我們把一個(gè)數(shù)據(jù)庫(kù)拆分為了多個(gè)數(shù)據(jù)庫(kù)，一個(gè)主數(shù)據(jù)庫(kù)用于寫入和修改數(shù)據(jù)，其他的用于同步主數(shù)據(jù)并提供給客戶端查詢，這樣就把一個(gè)庫(kù)的讀和寫的壓力，分?jǐn)偨o了多個(gè)庫(kù)，從而提高了數(shù)據(jù)庫(kù)整體的運(yùn)行效率

常見(jiàn)類型選擇

整數(shù)類型寬度設(shè)置

MySQL可以為整數(shù)類型指定寬度，例如int(11)，實(shí)際上并沒(méi)有意義，它并不會(huì)限制值的范圍，對(duì)于存儲(chǔ)和計(jì)算來(lái)說(shuō)，int(1)和int(20)是相同的

VARCHAR和CHAR類型

char類型是定長(zhǎng)的，而varchar存儲(chǔ)可變字符串，比定長(zhǎng)更省空間，但是varchar需要額外1或2個(gè)字節(jié)記錄字符串長(zhǎng)度，更新時(shí)也容易產(chǎn)生碎片

需要結(jié)合使用場(chǎng)景來(lái)選擇：如果字符串列最大長(zhǎng)度比平均長(zhǎng)度大很多，或者列的更新很少，選擇varchar較合適;如果要存很短的字符串，或者字符串值長(zhǎng)度都相同，比如MD5值，或者列數(shù)據(jù)經(jīng)常變更，選擇使用char類型

DATETIME和TIMESTAMP類型

datetime的范圍更大，能表示從1001到9999年，timestamp只能表示從1970年到2038年。datetime與時(shí)區(qū)無(wú)關(guān)，timestamp顯示值依賴于時(shí)區(qū)。在大多數(shù)場(chǎng)景下，這兩種類型都能良好地工作，但是建議使用timestamp，因?yàn)閐atetime占用8個(gè)字節(jié)，timestamp只占用了4個(gè)字節(jié)，timestamp空間效率更高

BLOB和TEXT類型

blob和text都是為存儲(chǔ)很大數(shù)據(jù)而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲(chǔ)

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

范式化

當(dāng)數(shù)據(jù)較好范式化時(shí)，修改的數(shù)據(jù)更少，而且范式化的表通常要小，可以有更多的數(shù)據(jù)緩存在內(nèi)存中，所以執(zhí)行操作會(huì)更快

缺點(diǎn)則是查詢時(shí)需要更多的關(guān)聯(lián)

第一范式：字段不可分割，數(shù)據(jù)庫(kù)默認(rèn)支持

第二范式：消除對(duì)主鍵的部分依賴，可以在表中加上一個(gè)與業(yè)務(wù)邏輯無(wú)關(guān)的字段作為主鍵，比如用自增id

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

硬件優(yōu)化

MySQL 對(duì)硬件的要求主要體現(xiàn)在三個(gè)方面：磁盤、網(wǎng)絡(luò)和內(nèi)存

磁盤

磁盤應(yīng)該盡量使用有高性能讀寫能力的磁盤，比如固態(tài)硬盤，這樣就可以減少 I/O 運(yùn)行的時(shí)間，從而提高了 MySQL 整體的運(yùn)行效率

磁盤也可以盡量使用多個(gè)小磁盤而不是一個(gè)大磁盤，因?yàn)榇疟P的轉(zhuǎn)速是固定的，有多個(gè)小磁盤就相當(dāng)于擁有多個(gè)并行運(yùn)行的磁盤一樣

網(wǎng)絡(luò)

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

內(nèi)存

MySQL 服務(wù)器的內(nèi)存越大，那么存儲(chǔ)和緩存的信息也就越多，而內(nèi)存的性能是非常高的，從而提高了整個(gè) MySQL 的運(yùn)行效率

本文轉(zhuǎn)載自微信公眾號(hào)「月伴飛魚」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系月伴飛魚公眾號(hào)。