前言

上一節(jié)我們?cè)敿?xì)解釋了mysql的聚簇索引部分以及mysql的索引使用匹配規(guī)則，其中最重要的內(nèi)容是最左匹配的規(guī)則，由此可以推導(dǎo)出很多規(guī)則的應(yīng)用，所以需要重點(diǎn)進(jìn)行關(guān)，而其他的內(nèi)容只需要學(xué)習(xí)即可。

學(xué)習(xí)內(nèi)容：

1. 學(xué)習(xí)如何設(shè)計(jì)索引

2. 設(shè)計(jì)索引如何避坑

3. 如何讓你的查詢(xún)百分百能用上索引

建立索引的建議

下面是日常建立索引的一些建議：

• 經(jīng)常被查詢(xún)或者排序的字段

• 值比較多的字段，對(duì)于優(yōu)化索引的查詢(xún)價(jià)值越高

• 對(duì)于字段比較小的類(lèi)型進(jìn)行查詢(xún)，比如tinyint , char 等等

• 盡量使用主鍵自增而不是使用uuid

• 索引不需要設(shè)計(jì)太多

• 如果使用了范圍查詢(xún)，多數(shù)情況下是無(wú)法使用索引的，所以應(yīng)該把范圍查詢(xún)放到查詢(xún)的最右邊。

• 第一個(gè)范圍查詢(xún)可以用上索引，第二個(gè)無(wú)法用上索引，所以范圍查詢(xún)最好只有一個(gè)

索引的使用問(wèn)題：

分頁(yè)與排序

在上一節(jié)我們討論過(guò)對(duì)于大多數(shù)的分頁(yè)以及排序，其實(shí)都是沒(méi)有辦法使用索引的，因?yàn)槁?lián)合索引必須按照最左側(cè)的方式進(jìn)行查找。

案例：

比如我們?cè)诓樵?xún)省份城市和性別，有時(shí)候要按照不同的字段進(jìn)行查詢(xún)，所以很多情況下無(wú)法應(yīng)用最左匹配的原則。

解決辦法：

與其如此，還不如就把類(lèi)似省份、城市和性別三個(gè)字段，都放在聯(lián)合索引的最左側(cè)，這樣跟其他字段組合，聯(lián)合索引后，讓大部分的查詢(xún)都可以直接通過(guò)索引樹(shù)就可以把where條件指定的數(shù)據(jù)篩選出來(lái)了。

建議：在設(shè)計(jì)的時(shí)候可以按照多設(shè)計(jì)幾個(gè)字段的索引并且按照從左到右的查詢(xún)方式進(jìn)行匹配，最后一個(gè)使用范圍值進(jìn)行處理，這樣就可以使得整個(gè)查詢(xún)都可以用得上索引了。

mysql執(zhí)行計(jì)劃

什么是執(zhí)行成本?

首先是執(zhí)行計(jì)劃的成本，我們?cè)谟?jì)算的時(shí)候，會(huì)把CPU的成本，符合成本就是0.2，從磁盤(pán)讀取到內(nèi)存的成本被設(shè)置為1

如何計(jì)算成本：首先我們可以：show table status like "表名"，對(duì)于innodb來(lái)說(shuō)，這個(gè)rows是估計(jì)值，下面是對(duì)應(yīng)的估計(jì)值內(nèi)容：

• Rows: 就是表里的記錄數(shù)，

• data_length: 就是表的聚簇索引的字節(jié)數(shù)大小

頁(yè)數(shù)量如何計(jì)算：

• data_length除以1024就是kb為單位的大小，然后再除以16kb（默認(rèn)一頁(yè)的大小），就是有多少頁(yè)

• IO成本就是：數(shù)據(jù)頁(yè)數(shù)量 1.0 + 微調(diào)值，CPU成本就是：行記錄數(shù) 0.2 + 微調(diào)值

索引訪(fǎng)問(wèn)速度估算

1. 首先需要估算計(jì)算的范圍，比如按照NAME的范圍值，如果只存在一個(gè)范圍，通常在一個(gè)數(shù)據(jù)頁(yè)進(jìn)行掃描。

2. 假設(shè)二級(jí)索引的效率為100頁(yè)，然后使用0.2也就是20，這是二級(jí)索引的速度

3. 然后二級(jí)索引需要回表操作，此時(shí)就需要回到聚簇索引的表進(jìn)行查找 。

常見(jiàn)的優(yōu)化方式：

常量替換

在sql進(jìn)行優(yōu)化查詢(xún)的時(shí)候，會(huì)把一些范圍查詢(xún)的值轉(zhuǎn)為常量搜索，select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1，經(jīng)過(guò)替換后如下：select t1表中id=1的那行數(shù)據(jù)的各個(gè)字段的常量值，最終替換的結(jié)果為：t2.* from t1 join t2 on t1表里x1字段的常量值 =t2.x1

子查詢(xún)

首先，一個(gè)子查詢(xún)會(huì)分為兩個(gè)語(yǔ)句，首先會(huì)根據(jù)主鍵的聚簇索引開(kāi)始對(duì)于內(nèi)容進(jìn)行查找。 對(duì)于上述的子查詢(xún)，執(zhí)行計(jì)劃會(huì)被優(yōu)化為，先執(zhí)行子查詢(xún)，也就是select x2 from t2 where x3=xxx這條SQL語(yǔ)句，把查出來(lái)的數(shù)據(jù)都寫(xiě)入一個(gè)臨時(shí)表里，也可以叫做物化表，意思就是說(shuō)，把這個(gè)中間結(jié)果集進(jìn)行物化。

半連接的優(yōu)化

首先，MYSQL沒(méi)有半鏈接這種語(yǔ)法，這是底層對(duì)于JOIN以及IN查詢(xún)的一種優(yōu)化手段，select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx，如果使用半鏈接的手段，其實(shí)可以只鏈接符合ON條件的半鏈接表即可。

   執(zhí)行計(jì)劃和SQL語(yǔ)句的關(guān)系：雖然索引可以解決不太復(fù)雜的單表查詢(xún)的情況，但是很多時(shí)候，統(tǒng)計(jì)，匯總，函數(shù)等SQL的使用還是會(huì)降低整個(gè)SQL的查詢(xún)和使用速度。

性能指標(biāo)：

下面是三條基本的原則：

1. 主鍵索引查詢(xún)肯定是CONST

2. 二級(jí)索引想要是是CONST，你的索引必須是唯一索引 才行 - UNIQUE KEY。但是如果使用 IS NULL這種查詢(xún)方式，依然使用的REF的方式。

3. 另外，如果查詢(xún)的是按照索引的順序列進(jìn)行查詢(xún)，但是WHERE條件不是，一樣可以使用索引直接找葉子節(jié)點(diǎn)的方式。

4. 類(lèi)似于select * from table where name=x and name IS NULL，那么此時(shí)在執(zhí)行計(jì)劃里就叫做ref_or_null

5. 普通索引的查詢(xún)方式是REF，類(lèi)似INDEX(NAME, AGE)。

6. 范圍查詢(xún)的時(shí)候會(huì)使用RANGE的查詢(xún)方式

7. 針對(duì)這種只要遍歷二級(jí)索引就可以拿到你想要的數(shù)據(jù)，而不需要回源到聚簇索引的訪(fǎng)問(wèn)方式，就叫做index訪(fǎng)問(wèn)方式！INDEX的方式需要遍歷某個(gè)二級(jí)索引，但是因?yàn)槎?jí)索引比較小，所以遍歷性能也還可以的。

現(xiàn)在我們停一下腳步，思考一下，之前說(shuō)的const、ref和range，本質(zhì)都是基于索引樹(shù)的二分查找和多層跳轉(zhuǎn)來(lái)查詢(xún)，所以性能一般都是很高的，然后接下來(lái)到index這塊，速度就比上面三種要差一些了，因?yàn)樗亲弑闅v二級(jí)索引樹(shù)的葉子節(jié)點(diǎn)的方式來(lái)執(zhí)行了，那肯定比基于索引樹(shù)的二分查找要慢多了，但是還是比全表掃描好一些的。

驅(qū)動(dòng)表和被驅(qū)動(dòng)表

驅(qū)動(dòng)表：指的是關(guān)聯(lián)查詢(xún)條件先需要進(jìn)行篩選的表，通常位于表的前面

被驅(qū)動(dòng)表：通常需要根據(jù)一個(gè)表的關(guān)聯(lián)數(shù)據(jù)找到另一張表的內(nèi)容進(jìn)行關(guān)聯(lián)，所以叫被驅(qū)動(dòng)表。

驅(qū)動(dòng)規(guī)則

循環(huán)嵌套規(guī)則：我們假設(shè)在驅(qū)動(dòng)表里面找到了10條數(shù)據(jù)，通過(guò)驅(qū)動(dòng)表的部分字段找到被驅(qū)動(dòng)的數(shù)據(jù)，就意味著需要在被驅(qū)動(dòng)表里面執(zhí)行驅(qū)動(dòng)表次數(shù)的查找。

比如驅(qū)動(dòng)表為10次，被驅(qū)動(dòng)表就需要掃描整個(gè)表10次。                     

explain計(jì)劃

基本的字段格式：

首先需要了解EXPLAIN的基本格式

1. 首先，一個(gè)select就會(huì)出現(xiàn)一個(gè)id, 通常在復(fù)雜的查詢(xún)里面會(huì)包含多張表的查詢(xún)，比如join, in等等

2. SelecT_TYPE：這個(gè)表示的是查詢(xún)的類(lèi)型

3. Table：表名稱(chēng)

4. Partitions：這個(gè)表示表空間，分區(qū)的概念

5. Type : 比如查詢(xún)的優(yōu)化等級(jí),  const, index, all，分別代表了聚簇索引，二級(jí)索引，全表掃描的查詢(xún)搜索方式

6. PossiblEkeys：和type一樣確定訪(fǎng)問(wèn)方式，確定有哪些索引可以選擇，

7. Key：確定有哪些可以提供選擇，同時(shí)提供索引的對(duì)應(yīng)長(zhǎng)度

8. Key_len： 表示的是索引的長(zhǎng)度

9. Ref： 等值匹配的時(shí)候出現(xiàn)的一些匹配的相關(guān)信息

10. Rows： 預(yù)估通過(guò)所索引或者別的方式讀取多少條數(shù)據(jù)

11. Filtered：經(jīng)過(guò)搜索條件過(guò)濾之后的剩余數(shù)據(jù)百分比。

12. extra：額外的信息不重要。

下面為一個(gè)簡(jiǎn)單的案例：

 復(fù)制代碼 隱藏代碼expain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 10

查詢(xún)的結(jié)果如下：

DERIVED：表示子查詢(xún)的結(jié)果會(huì)物化為一個(gè)內(nèi)部的臨時(shí)表，然后外層的查詢(xún)針對(duì)臨時(shí)表物化開(kāi)始進(jìn)行搜索分組聚合的時(shí)候，使用的索引的方式，所以是index的掃描速度。

關(guān)于查詢(xún)等級(jí)：

1. const: 一般是主鍵查詢(xún)的時(shí)候

2. Ref: 基于某個(gè)耳機(jī)索引的時(shí)候進(jìn)行查詢(xún)

3. Eq_ref: 表示連接查詢(xún)是根據(jù)二級(jí)索引索引關(guān)聯(lián)的

4. Eq_ref_null： 二級(jí)索引的關(guān)聯(lián)的時(shí)候根據(jù)Null值允許進(jìn)行關(guān)聯(lián)查詢(xún)

5. Index_merge: ×詢(xún)可能會(huì)基于多個(gè)索引提取數(shù)據(jù)后進(jìn)行合并

6. Range:而查詢(xún)方式是range的話(huà)就是基于二級(jí)索引進(jìn)行范圍查詢(xún)

Using filesort

這個(gè)會(huì)在排序的時(shí)候見(jiàn)到，特別是分頁(yè)的排序查詢(xún)，這個(gè)需要極力的避免，因?yàn)椴蛔咚饕呐判蚴欠浅７浅Ｂ模枰褂肕emory表進(jìn)行數(shù)據(jù)的操作。

Using temprory

通常出現(xiàn)在大量的group union distinct 等等的時(shí)候會(huì)出現(xiàn)和filesort類(lèi)似，也會(huì)出現(xiàn)數(shù)據(jù)量過(guò)大而產(chǎn)生臨時(shí)表的問(wèn)題。

結(jié)論

核心重點(diǎn)就是，盡量利用一兩個(gè)復(fù)雜的多字段聯(lián)合索引，抗下你80%以上的 查詢(xún)，然后用一兩個(gè)輔助索引抗下剩余20%的非典型查詢(xún)，保證你99%以上的查詢(xún)都能充分利用索引，就能保證你的查詢(xún)速度和性能！

其他

關(guān)于7天是否登錄的表設(shè)計(jì)

7天是否登錄過(guò)是一個(gè)比較常見(jiàn)的小需求了，最簡(jiǎn)單的方式其實(shí)并不是范圍查詢(xún)，而是使用增加一個(gè)是否7天登錄的flag值并且通過(guò)定時(shí)任務(wù)定時(shí)刷新這個(gè)字段的值即可。如果要讓這種查詢(xún)用上索引，可以設(shè)計(jì)一個(gè)聯(lián)合索引為：（province, city, sex, hobby, character,does_login_in_latest_7_days, age），然后搜索的時(shí)候，一定會(huì)在where條件里帶上一個(gè)does_login_in_latest_7_days=1，最后再跟上age范圍查詢(xún)，這樣就可以讓你的where條件里的字段都用索引來(lái)篩選。

設(shè)計(jì)輔助索引的案例

使用輔助索引，比如加入一個(gè)輔助的索引來(lái)加快排序和篩選操作。

寫(xiě)在最后

索引的最后部分其實(shí)更多的是和執(zhí)行計(jì)劃進(jìn)行結(jié)合，對(duì)于MYSQL來(lái)說(shuō)優(yōu)化的最好方式就是學(xué)習(xí)好MYSQL的explain計(jì)劃即可，這是一個(gè)十分強(qiáng)大好用的工具。

來(lái)源：https://www.52pojie.cn/thread-1532695-1-1.html