前言

上一節(jié)我們詳細解釋了mysql的聚簇索引部分以及mysql的索引使用匹配規(guī)則，其中最重要的內容是最左匹配的規(guī)則，由此可以推導出很多規(guī)則的應用，所以需要重點進行關，而其他的內容只需要學習即可。

學習內容：

1. 學習如何設計索引

2. 設計索引如何避坑

3. 如何讓你的查詢百分百能用上索引

建立索引的建議

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

• 經常被查詢或者排序的字段

• 值比較多的字段，對于優(yōu)化索引的查詢價值越高

• 對于字段比較小的類型進行查詢，比如tinyint , char 等等

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

• 索引不需要設計太多

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

• 第一個范圍查詢可以用上索引，第二個無法用上索引，所以范圍查詢最好只有一個

索引的使用問題：

分頁與排序

在上一節(jié)我們討論過對于大多數(shù)的分頁以及排序，其實都是沒有辦法使用索引的，因為聯(lián)合索引必須按照最左側的方式進行查找。

案例：

比如我們在查詢省份城市和性別，有時候要按照不同的字段進行查詢，所以很多情況下無法應用最左匹配的原則。

解決辦法：

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

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

mysql執(zhí)行計劃

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

首先是執(zhí)行計劃的成本，我們在計算的時候，會把CPU的成本，符合成本就是0.2，從磁盤讀取到內存的成本被設置為1

如何計算成本：首先我們可以：show table status like "表名"，對于innodb來說，這個rows是估計值，下面是對應的估計值內容：

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

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

頁數(shù)量如何計算：

• data_length除以1024就是kb為單位的大小，然后再除以16kb（默認一頁的大?。褪怯卸嗌夙?/p>

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

索引訪問速度估算

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

2. 假設二級索引的效率為100頁，然后使用0.2也就是20，這是二級索引的速度

3. 然后二級索引需要回表操作，此時就需要回到聚簇索引的表進行查找 。

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

常量替換

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

子查詢

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

半連接的優(yōu)化

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

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

性能指標：

下面是三條基本的原則：

1. 主鍵索引查詢肯定是CONST

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

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

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

5. 普通索引的查詢方式是REF，類似INDEX(NAME, AGE)。

6. 范圍查詢的時候會使用RANGE的查詢方式

7. 針對這種只要遍歷二級索引就可以拿到你想要的數(shù)據(jù)，而不需要回源到聚簇索引的訪問方式，就叫做index訪問方式！INDEX的方式需要遍歷某個二級索引，但是因為二級索引比較小，所以遍歷性能也還可以的。

現(xiàn)在我們停一下腳步，思考一下，之前說的const、ref和range，本質都是基于索引樹的二分查找和多層跳轉來查詢，所以性能一般都是很高的，然后接下來到index這塊，速度就比上面三種要差一些了，因為他是走遍歷二級索引樹的葉子節(jié)點的方式來執(zhí)行了，那肯定比基于索引樹的二分查找要慢多了，但是還是比全表掃描好一些的。

驅動表和被驅動表

驅動表：指的是關聯(lián)查詢條件先需要進行篩選的表，通常位于表的前面

被驅動表：通常需要根據(jù)一個表的關聯(lián)數(shù)據(jù)找到另一張表的內容進行關聯(lián)，所以叫被驅動表。

驅動規(guī)則

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

比如驅動表為10次，被驅動表就需要掃描整個表10次。                     

explain計劃

基本的字段格式：

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

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

2. SelecT_TYPE：這個表示的是查詢的類型

3. Table：表名稱

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

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

6. PossiblEkeys：和type一樣確定訪問方式，確定有哪些索引可以選擇，

7. Key：確定有哪些可以提供選擇，同時提供索引的對應長度

8. Key_len： 表示的是索引的長度

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

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

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

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

下面為一個簡單的案例：

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

查詢的結果如下：

DERIVED：表示子查詢的結果會物化為一個內部的臨時表，然后外層的查詢針對臨時表物化開始進行搜索分組聚合的時候，使用的索引的方式，所以是index的掃描速度。

關于查詢等級：

1. const: 一般是主鍵查詢的時候

2. Ref: 基于某個耳機索引的時候進行查詢

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

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

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

6. Range:而查詢方式是range的話就是基于二級索引進行范圍查詢

Using filesort

這個會在排序的時候見到，特別是分頁的排序查詢，這個需要極力的避免，因為不走索引的排序是非常非常慢的，需要使用Memory表進行數(shù)據(jù)的操作。

Using temprory

通常出現(xiàn)在大量的group union distinct 等等的時候會出現(xiàn)和filesort類似，也會出現(xiàn)數(shù)據(jù)量過大而產生臨時表的問題。

結論

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

其他

關于7天是否登錄的表設計

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

設計輔助索引的案例

使用輔助索引，比如加入一個輔助的索引來加快排序和篩選操作。

寫在最后

索引的最后部分其實更多的是和執(zhí)行計劃進行結合，對于MYSQL來說優(yōu)化的最好方式就是學習好MYSQL的explain計劃即可，這是一個十分強大好用的工具。

來源：https://www.52pojie.cn/thread-1532695-1-1.html