引言

• 今天怎么都得看完，不然項目都沒時間整了，簡歷這周怎么都得搞完，不然肯定得掛！不行！
• 這里重在于講述索引的東西，先回顧一下索引基礎知識、然后就是索引的原理以及如何設計索引才能更加高效。
• 數據來源主要有兩部分，分別是
  • B站的SQL進階教程
  • 數據庫教程網站

正文

B站網課

索引基礎

• 索引是能夠提高數據庫從表中檢索數據行的速度的一種數據結構，但是需要額外的寫入和存儲來維護

  • 查詢優化器，可以使用索引來快速定位數據，不需要全表逐行掃描

• 索引很小，大部分都是保存在內存中的，所以，從內存中讀取數據總是比磁盤中讀取數據來得快
  
  索引的代價

• 增加數據庫空間消耗，作為數據庫的一部分，會一直和表格一塊保存

• 每次增刪改都需要的維護更新索引

綜上

• 為性能關鍵的查詢保留索引，需要基于查詢創建索引，主要是為了加快部分查詢十分緩慢的查找。

創建索引

• 通過Explain關鍵字查看是否使用了索引，主要是通過Type關鍵字和row關鍵字查看
  • type是使用的索引類型
  • row是查詢的行數
    
    創建索引，加速查詢的過程

create index idx_state on table_name(col_name);

• possible_keys
  • 在查詢過程中，可能用到的索引，這里是使用了idx_state，如果是聯合查詢，會是多個索引值
• key
  • 在實際查詢中，所使用索引或者鍵，上面那個是可能使用的，下面那個是實際使用的
    

select customer_id  from customers where points > 100;   // 選擇所有分數大于100的顧客的id
create index idx_points on customers(pointers);     // 對customer表格的pointer列創建索引

如何在一個表中查看索引

show indexes in customers；

• 聚簇索引
• 二級索引
  • 每一次創建一個二級索引，都會自動將主鍵索引加入到對應二級索引中
  • 二級索引：二級索引字段 + 主鍵索引

為字符串建立索引

• 主要是為
  • char、varchar、text和blob創建索引
  • 這類索引會占據大量空間，無法達到很好的性能。所以，這里要盡量包含字符串的前幾個字母，也就是前綴。

創建一個長度為20的前綴索引

• 創建針對customers表格的last_name列的前二十個字符構成的字符串

create index idx_lastname on customers (last_name(20));

• 這個長度選擇，要能偶盡可能在短的情況下，遍歷到所有的數據

全文索引

• 查詢文章或者題目中和“react redux”相關的所有的posts，下述方法存在一些問題
  • 隨著數據庫越來越大，搜索的范圍越大，時間越慢
  • 全表掃描，沒有索引
    
    創建全文索引==》實現模糊查詢，像搜索引擎一樣

# 創建全文索引
create fulltext index on table_name(col_A,col_B);
# 使用全文索引
select *
from table_name 
where match(col_A,col_B) against ('這里輸入相關的關鍵字');

• 相關性得分
  • MySQL會基于若干因素，為包含了搜索短語的每一行計算相關性得分，是0到1之間的浮點數。

全文索引的兩種方式

• 自然語言模式，默認模式，就是上文使用模式
• 布爾模式，包括或者是排除某些單詞
  • 下述使用的布爾模式，查詢包含了react和form，但是不包含redux 關鍵字的。
    

復合索引

• 找到位于加州并且收入大于8000的，這里加州和收入是兩張表里面的數據

# 這里是做了一個連表查詢
select customers_id from customers where state ='CA' and points > 1000;

• 具體執行過程
  • 因為只用到一個索引，所以就是先找到所有州為CA的用戶，然后在查詢這些用的points
  • 因為只用到了一個索引，如果能夠CA里面是有points >1000的索引，就快很多了。這就是聯合索引的作用
    
• 對于州和point兩個列建立聯合索引，然后能夠通過州和point快速訪問到目標

create index idx_state_points on customers(state,points);

補充

• 復合索引如果匹配到的范圍查找，就不走索引了，后續會走索引下推
• 復合索引的最左匹配原則，不是說順序，是說具體的值，where a and b and c 對于索引（a,b,c）是滿足最左匹配原則的，但是如果是where c and b就不滿足了，因為少了一個。

復合索引中的排序問題

遵循以下兩個原則

• 使用最頻繁的列放在前面
  • 將使用最頻繁的放在前面，能夠有效縮減搜索范圍
• 將基數最高的放在前面
  • 可以將總得樣本，劃分成數量跟少的樣本，前面的搜索的范圍會更小
• 關注查詢本身，根據查詢本身進行優化，盡量縮減問題搜索的空間

在州和用戶姓氏兩個關鍵字上創建一個聯合索引

create index idx_lastname_satte on customer(last_name,state);

• 下述是last_name在前，state在后，掃描的列是40
  
• 下述是使用州在前，然后姓氏在后，僅僅查詢了兩行，效率更好
  

索引失效的情況

• 下述是使用or進行索引聯合，通過explain可以看到，是查詢了1010個數據，相當于全表掃描
  

• 下述是使用union將兩個子索引查詢進行拼接，總共掃描了660個數據，遠遠小于第一個索引拼接方式

調用列進行了相關的運算

• 下述進行了數字遷移，然后掃描量變成了3，因為雖然使用了比較函數，但是并沒有調用對應對的列進行運算

使用索引進行排序

• 添加索引的時候，MySQL會獲取該列中的所有的值，并對結果進行排序，并將他們存儲在索引中

使用沒有對應索引的列進行排序==》產生外部排序，外部排序十分耗時，通過下圖可以看到外部排序的時間耗費是第一個時間復雜度的10倍。using filesort關鍵字進行排序

• 下述做的排序是一個全標掃描，進行的排序
  
• 正常情況下，如果你要對數據進行排序，而且使用的是聯合索引的中的兩個列，那么必須要按照的相同順序或者相同的升降順序進行查詢和排序的，否則會增加消耗時間。
  
  特殊情況：一定要按照的聯合索引的列進行排序查詢，否則就會出現對應的全表掃描
• 因為建立聯合索引的時候，實現按照第一個列state進行分類的，然后在同一個state中，是按照points進行排序的。現在要直接points進行全部排序，就用不到索引了。
  
  

覆蓋索引

• 下述是覆蓋索引，需要查詢的數據在索引中就存在，不需要在會表進行查詢即可獲得，效率很高。這種現象就是索引覆蓋！
• select子句中查看的所有的數據列，都在索引中，就不需要在通過回表進行查詢，這就是索引覆蓋
  

維護索引

• 重復索引：相同列的不同順序（A，B，C）和（B，A，C）
• 多余索引：索引重復的情況，A和（A，B）

數據庫基礎——文檔資料學習整理

創建索引

索引的定義

• 索引是一種能夠加快數據檢索的數據結構，但是需要額外的寫入和存儲來維護
• 查詢優化器能夠通過索引，快速定位數據，不必掃描表中的每一行
• 索引本身和數據一起存儲在同一表中。

聚簇索引和非聚簇索引

• 聚簇索引：
  • 使用主鍵或者唯一鍵創建表的時候，會自動創建一個名為primary的索引
• 非聚簇索引
  • 二級索引或者非聚簇索引是除了聚簇索引以外的索引。

創建索引的語法

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

• 使用explain來查看對應的SQL執行情況
  
• 查看當前已經創建的索引

刪除索引

• 索引需要有空間代價和時間代價，所以需要刪除

drop index index_name on table_name;

創建唯一索引

• 雖然已經有了主鍵索引唯一索引，但是有的時候，還是需要創建自增的數字列，比如說訂單表中的訂單編號，用戶表中的電子郵件等

create unique index index_name on table_name （col_name）；

索引提示

• MySQL的查詢優化器為SQL語句制定最佳執行計劃，根據索引基數進行決策，有的時候，你創建了索引但是沒有使用也是因為索引基數不對。
• 使用use index強制sql語句建議查詢優化器使用指定的索引。

SELECT column_list
FROM table_name
USE INDEX (index_list)
WHERE condition;

復合索引

定義

• 復合索引又稱為組合索引或者是多列索引，最多能夠創建16個列

創建語法

CREATE INDEX index_name
ON table_name(column_1, column_2, column_3);

復合索引規則

• 將where子句中常用的列放在索引列列表的開頭
• 將不常用的列放在索引列列表的后面

MySQL總是按照最左匹配的原則展開對應的查詢語句

CREATE INDEX index_name
ON table_name(a, b, c);

• 在上述索引的情況下，如果查詢使用的是下述順序都會使用對應的索引，a先滿足了，然后在去看b

WHERE a = v1 and b = v2 and c = v3;
WHERE a = v1 and b = v2;
WHERE a = v1;

• 如果是下述情況，就不會使用對應的索引

WHERE b = v2 and c = v3;
WHERE c = v3

聚集索引

定義

• 聚集索引是一種特殊的索引，該索引中的鍵值順序決定了表中相應行的物理順序。
• 表格中的數據，只能按照一種順序進行存儲，所以表中只能有一個聚集索引。

InnoDB中的MySQL聚集索引

• 如果指定了主鍵，主鍵就是聚集索引
• 如果沒有逐漸，第一個非空的列，并且是Unique的列，就是聚集索引
• 如果沒有合適的，MySQL會在內部生成一個隱藏的聚集索引。
• InnoDB中二級索引中的每條記錄都包含該行的主鍵列和非聚集索引指定的列

索引基數

定義

• 一個索引的基數，就是這個索引列中唯一值的數量
  • 是根據統計信息生成的估計值，并不準確
• 是查詢優化器決定是否索引的依據，基數越高，索引越有效！
• 基數越低，索引越無效，還不如全表掃描！

查看索引基數

show indexes from table_name;

字符串前綴索引

定義

• 為字符串列創建前綴索引，
• 相比于對整個字符串創建索引，前綴索引能夠減少磁盤的使用量，提高索引的寫入速度。

具體語法

create index idx_name on table_name (col(prefix_length));

• 如果你使用對應的where的部分匹配，如果這對對應的字段創建了前綴索引就會使用對應索引加快速度，而不是全局掃描

select  * from table_name where first_name like 'ge%';

索引順序

定義

• 在創建索引的時候，指定索引的順序，默認情況下，是按照升序存儲的。

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_name [ASC | DESC], ...)
[algorithm_option | lock_option];

對索引的隱式類型轉換

• 如果索引字段是字符串類型，但是在條件查詢中，輸入的參數是整型的話，會走全表掃描。
  • 如果索引字段是 整型類型，查詢條件中輸入的參數是字符串，不會導致索引失效的

MySQL在遇到字符串和數字比較的時候，會自動把字符串轉為數字，然后在進行比較

• 自動類型轉換是函數操作，CAST操作

# 下述兩個SQL語句是等價的
select * from t_user where phone = 1300000001;
select * from t_user where CAST(phone AS signed int) = 1300000001;  # 這里是對phone進行了函數操作
# 下述的SQL語句會使用索引進行掃描，因為是將輸入的參數的進行類型轉換，而不是將索引進行類型轉換
select * from t_user where id = "1";
select * from t_user where id = CAST("1" AS 1);

索引下推

這部分的所有資料都是來自這個鏈接——五分鐘搞懂MySQL索引下推
定義

• 能夠減少回表查詢的次數，提高查詢的效率
• 將部分上層也就是服務層負責的事情，交給下層引擎層去處理
• 通過Extra中using index condition來進行判定

原理

• 沒有索引下推的情況

  • 存儲引擎讀取索引記錄
  • 根據索引中的主鍵值，定位并讀取完整的行記錄。
  • 存儲引擎吧記錄交給Server層，檢測記錄是否滿足Where子句條件

• 有索引下推的情況

  • 存儲引擎讀取索引記錄
  • 新增操作：判斷where條件部分能夠用索引中的列來檢查，條件不滿足，就處理下一行索引記錄
  • 條件滿足，使用索引中的之間，去定位并讀取完整的行記錄，也就是回表
  • 存儲引擎吧記錄交給Server層，檢測記錄是否滿足Where子句條件

具體執行實例

• 這里已經建立了聯合索引，會按照最左匹配的原則，先查的name然后就是age

select * from tuser where name like '張%' and age = 10;

• 沒有ICP的時候

  • 數據引擎僅僅會使用第一個索引，返回所有姓張的列，然后由server層進行過濾，每一個姓張的樣本都會進行回表查詢，效率低

• 有ICP

  • 數據引擎會在底層同時使用兩個索引，查找到姓張的行后，會對年齡進行判定， 減少了回表的次數，效率比較高！

這里還是看一下原來的鏈接更容易理解

索引下推使用的條件

• 只能用于range、ref、eq_ref、ref_of_null訪問方法
• 對于InnoDB來說，只能用于二級索引
• 使用子查詢的條件不能下推
• 引用了存儲函數條件的不能下推

具體應用場景

• 聯合索引在遇到范圍查詢時，會停止匹配，后續的字段就不會在使用

select * from t_user where age >20 and reward = 10;

• 有了索引下推之后，即使reward無法走索引，但是在二級索引里面，會在存儲引擎中進行過濾，減少回表次數

結合面試題回顧

如何避免索引失效

個人學完之后的回答

• 不要使用or語句，如果是or的話，建議拆成多個不同的語句進行拼接
• 不要對創建索引的列加上對應的運算，比如說加減乘除等，col + 2 > edge，不如改成 col > edge -2;
• 如果是聯合索引，在where子句中，一定要先先使用滿足最左列，然后依次往后
• 盡量使用基數比較大的索引。

參考回答

• 使用左或者左右模糊匹配的時候，會造成索引失效，比如說like %xxx 或者 like %xxx%
• 在查詢條件中對索引列做了計算、函數、類型轉換的操作，會造成索引失效的。
• 聯合索引要遵循最左匹配原則，按照最左有限的方式進行索引匹配，否則會導致索引失效。
• 使用where子句時，or的前列是索引列，后列不是索引列，索引會失效

如何優化索引

• 在創建聯合索引的時候，觀察select對應列，盡量創建覆蓋索引，避免回表，減少大量的IO操作性能。
• 防止索引失效，盡量不要寫會讓索引失效的SQL語句
• 主鍵索引最好是單調遞增的值
  • 主鍵是隨機的值，插入會引起頁分裂現象，導致大量的內存碎片
• 對于大的字符串索引，考慮使用前綴索引只對前綴部分簡歷索引，節省索引的存儲空間。

總結

• 如果我在面試拼多多之前，就把這個東西整理了，或者說看了，也就不會那么尷尬，現在已經去實習了，很難頂！
• 不過等到秋招的正式批，這個問題應該是難不倒我了，看一下，回顧一下就行了！
• 加油吧！整理這個的時候，滿心都是后悔，下次不能讓這種事情發生！