MySQL索引與索引優化

MySQL的基本架構示意圖

MySQL索引就是用於優化器上。

索引：

MySQL官方對於索引的定義為：索引是幫助MySQL高效獲取數據的數據結構。即可以理解為：索引是數據結構。

索引是對數據庫表中一個或多個列的值進行排序的結構，建立索引有助於快速獲取信息。

你也可以這樣理解：索引就是加快檢索表中數據的方法。數據庫的索引類似於書籍的索引。在書籍中，索引允許用戶不必翻閱完整個書就能迅速地找到所需要的信息。在數據庫中，索引也允許數據庫程序迅速地找到表中的數據，而不必掃描整個數據庫。

首先明白為什麼索引會增加速度？

DB在執行一條Sql語句的時候，默認的方式是根據搜索條件進行全表掃描，遇到匹配條件的就加入搜索結果集合。如果我們對某一字段增加索引，查詢時就會先去索引列表中一次定位到特定值的行數，大大減少遍曆匹配的行數，所以能明顯增加查詢的速度。

索引的數據結構：hash、二叉樹、B樹、B+樹（最終最常用的是B+樹）

hash：使用存儲在內存中的內容來創建表，而且數據全部存放在內存中。

缺點：hash沖突–擾動函數

1.Hash存儲需將所有的數據文件添加到內存中，比較浪費內存空間

2.如果所有的查詢都是等值查詢，那麼hash比較快，但範圍查找就不太適合

如果使用hash做成的索引，因為需要全部掃描，即使在內存中，速度不容樂觀。

適合場景：等值查詢的場景，就只有KV（Key，Value）的情況，例如Redis、Memcached等這些NoSQL的中間件。

二叉樹：

叉樹是有序的，所以是持範圍查詢的，但時間複雜度是O(log(N))。

缺點：二叉樹還是紅黑樹都會因為樹的深度過深而造成io次數過多，影響讀取效率，以及有可能退化為鏈表結構。

不推薦使用select * 而使用字段，因為數據是存儲在磁盤，並且MySQL服務有篩選數據，每次讀取數據都會經過服務篩選，如果都是使用select *就會增加io次數。

B樹：

同樣的元素，B樹的表示要完全平衡叉樹要“矮”，原因在於B樹中的個節點可以存儲多個元素，相對於完全平衡叉樹整體的樹降低了，磁盤IO效率提了。

從最開始的Hash不持範圍查詢，叉樹樹很，只有B樹跟B+有的。

B+樹

B+樹非葉子節點上是不存儲數據的，僅存儲鍵值，而B樹節點中不僅存儲鍵值，也會存儲數據，所有的葉子節點形成一條有序鏈表（雙向鏈表），而且數據是按照順序排列的。

MySQL的索引底層為何使用B+樹？

為了減小IO操作數量，一般把一個節點的大小設計成最小讀寫單位的大小，MySQL的存儲引擎InnoDB的最小讀寫單位是16K一頁，B+樹中個節點為或的倍數最為合適。

頁概念

先Mysql的基本存儲結構是(記錄都存在邊)

各個數據可以組成個雙向鏈表，每個數據中的記錄可以組成個單向鏈表，每個數據都會為存儲在它邊的記錄成個錄，在通過主鍵查找某條記錄的時候可以在錄中使分法快速定位到對應的槽，然後再遍曆該槽對應分組中的記錄即可快速找到指定的記錄

以其他列(主鍵)作為搜索條件：只能從最記錄開始依次遍曆單鏈表中的每條記錄。

沒有進任何優化的sql語句，默認會這樣做：

定位到記錄所在的，需要遍曆雙向鏈表，找到所在的，從所在的內中查找相應的記錄，由於不是根據主鍵查詢，只能遍曆所在的單鏈表了。

很明顯，在數據量很的情況下這樣查找會很慢！看起來跟回表有點點像

對比B樹，B+樹的優勢是

1.每個節點存儲的key數量更多，樹的高度更低。

2.所有的具體數據都存在葉子節點上，所以每次查詢都要查到葉子節點，查詢速度比較穩定。

3.所有的葉子節點構成了一個有序鏈表，做區間查詢時更方便。

結：到這可以總結出來，Mysql選B+樹這種數據結構作為索引，可以提查詢索引時的磁盤IO效率，並且可以提範圍查詢的效率，並且B+樹的元素也是有序的。

MySQL存儲引擎（存儲引擎是形容數據表的，不是數據庫）

常用有三：

meory(hash,內存存儲數據)

inndb myisam ：B+樹 （inndb可以有hash，但是是自適應hash:系統將B+樹轉化成自適應hash，人為無法判斷干預）

如何選擇引擎？

如果沒有特別的需求，使用默認的Innodb即可。

MyISAM：以讀寫插入為主的應用程序，比如博客系統、新聞門戶網站。

Innodb：更新（刪除）操作頻率也高，或者要保證數據的完整性；並發量高，支持事務和外鍵。比如OA自動化辦公系統。

聚族索引是取決於數據與索引是否存放在一起

葉子節點包含了完整的數據記錄則是聚族索引

聚集索引

（1）：聚集索引就是以主鍵創建的索引

（2）：每個表只能有個聚簇索引，因為個表中的記錄只能以種物理順序存放，實際的數據只

能按照顆 B+ 樹進排序

（3）：表記錄的排列順序和與索引的排列順序致

（4）：聚集索引存儲記錄是物理上連續存在

（5）：聚簇索引主鍵的插速度要聚簇索引主鍵的插速度慢很多

（6）：聚簇索引適合排序，聚簇索引不適合在排序的場合，因為聚簇索引葉節點本身就是索引和

數據按相同順序放置在起，索引序即是數據序，數據序即是索引序，所以很快。聚簇索引葉節點是

保留了個指向數據的指針，索引本身當然是排序的，但是數據並未排序，數據查詢的時候需要消耗額

外更多的I/O，所以較慢

（7）：更新聚集索引列的代價很，因為會強制innodb將每個被更新的移動到新的位置

聚集索引

（1）：除了主鍵以外的索引

（2）：聚集索引的葉節點就是數據節點，聚簇索引的葉節點仍然是索引節點，並保留個鏈接指

向對應數據塊

（3）：聚簇索引適合排序，聚簇索引不適合在排序的場合

（4）：聚集索引存儲記錄是物理上連續存在，聚集索引是邏輯上的連續。

使聚集索引為什麼查詢速度會變快？

使聚簇索引找到包含第個值的後，便可以確保包含後續索引值的在物理相鄰

建聚集索引有什麼需要注意的地嗎？

在聚簇索引中不要包含經常修改的列，因為碼值修改後，數據必須移動到新的位置，索引此時會重

排，會造成很的資源浪費

InnoDB 表對主鍵成策略是什麼樣的？

優先使戶定義主鍵作為主鍵，如果戶沒有定義主鍵，則選取個Unique鍵作為主鍵，如果表中

連Unique鍵都沒有定義的話，則InnoDB會為表默認添加個名為row_id隱藏列作為主鍵。

聚集索引最多可以有多少個？

每個表你最多可以建249個聚簇索引。聚簇索引需要量的硬盤空間和內存

BTree 與 Hash 索引有什麼區別？

（1）：BTree索引可能需要多次運折半查找來找到對應的數據塊

（2）：HASH索引是通過HASH函數，計算出HASH值，在表中找出對應的數據

（3）：量不同數據等值精確查詢，HASH索引效率通常B+TREE

（4）：HASH索引不持模糊查詢、範圍查詢和聯合索引中的最左匹配規則，這些Btree索引都持

Myisam是非聚族索引

inndb的B+樹葉子節點直接存儲記錄（數據），而myisam則是存儲數據地址信息，如果建立索引的話，就會有兩棵B+樹，一棵樹存儲索引，查找先找索引樹，在根據索引找存儲數據的B+樹。

innab是聚族索引，數據與索引存儲在一起

最左前綴匹配原則

。這是常重要、常重要、常重要（重要的事情說三遍）的原則，MySQL會直向右匹配直到遇到範圍查詢（>,<,BETWEEN,LIKE）就停匹配。

索引是有序的，index1索引在索引文件中的排列是有序的，首先根據a來排序，然後才是根據b來排序，最後是根據c來排序，像select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 這種類型的sql語句，在a、b走完索引後，c肯定是無序了，所以c就沒法走索引，數據庫會覺得還不如全表掃描c字段來的快。

MySQL索引類型： mysql 有4種不同的索引：

主鍵索引（PRIMARY）

數據列不允許重複，不允許為NULL，一個表只能有一個主鍵。

唯一索引（UNIQUE）

數據列不允許重複，允許為NULL值，一個表允許多個列創建唯一索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引

可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引

普通索引（INDEX）

可以通過 ALTER TABLE table_name ADD INDEX index_name (column); 創建普通索引

可以通過 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 創建組合索引

全文索引（FULLTEXT）

可以通過 ALTER TABLE table_name ADD FULLTEXT (column); 創建全文索引

索引並非是越多越好，創建索引也需要耗費資源，一是增加了數據庫的存儲空間，二是在插入和刪除時要花費較多的時間維護索引

二級索引：葉子節點中存儲主鍵值，每次查找數據時，根據索引找到葉子節點中的主鍵值，根據主鍵值再到聚簇索引中得到完整的一行記錄

聚簇索引的葉子節點存儲了一行完整的數據，而二級索引只存儲了主鍵值，相比於聚簇索引，占用的空間要少

排除緩存擾

如果我們當前的MySQL版本持緩存且我們開啟了緩存，那每次請求的查詢語句和結果都會以keyvalue的形式緩存在內存中的，個請求會先去看緩存是否存在，不存在才會解析器。

緩存失效較頻繁的原因就是，只要我們對表進更新，那這個表所有的緩存都會被清空，其實我們很少存在不更新的表，可能靜態表可以到緩存，如果數據離線分析，緩存也就沒了。

家如果是8.0以上的版本就不擔這個問題，如果是8.0之下的版本，記得排除緩存的擾。

Explain

：分析執計劃

id:選擇標識符

select_type:表示查詢的類型。

table:輸出結果集的表

partitions:匹配的分區

type:表示表的連接類型

常用的類型有：ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL將遍曆全表以找到匹配的行

index: Full Index Scan，index與ALL區別為index類型只遍曆索引樹

range:只檢索給定範圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用於查找索引列上的值

eq_ref: 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關聯條件

const、system: 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些類型訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量，system是const類型的特例，當查詢的表只有一行的情況下，使用system

NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裏選取最小值可以通過單獨索引查找完成。

possible_keys:表示查詢時，可能使用的索引

key:表示實際使用的索引

key_len:索引字段的長度

ref:列與索引的比較

rows:掃描出的行數(估算的行數)

filtered:按表條件過濾的行百分比

Extra:執行情況的描述和說明：

using filesort說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取， 稱為 “文件排序”, 效率低。

using temporary使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見於 order by 和 group by；效率低

using index表示相應的select操作使用了覆蓋索引， 避免訪問表的數據行， 效率不錯。

索引優化

最左前綴匹配原則。這是常重要、常重要、常重要（重要的事情說三遍）的原則，MySQL會直向右匹配直到遇到範圍查詢 （>,<,BETWEEN,LIKE）就停匹配。

以最左邊的為准，只要查詢條件中帶有最左邊的列，那麼查詢就會使用到索引組合索引：當我們的where查詢存在多個條件查詢的時候，我們需要對查詢的列創建組合索引，最左匹配一般組合索引一起使用。

回表：回表是發生在二級索引上的一種數據查詢操作，簡單點講就是我們要查詢的列不在二級索引的列中，那麼就必須根據二級索引查到主鍵ID，然後再根據主鍵ID到聚簇索引樹上去查詢整行的數據，這一過程就叫作回表。

索引覆蓋：當SQL語句中查詢的列都在索引中時，我們就不需要回表去把整行數據都撈出來了，可以從非聚簇索引樹中直接獲取到我們需要的列的數據，這就叫索引覆蓋,當所有的列都能在二級索引樹中查詢到，就不需要再回表了，這種情況就是索引覆蓋,覆蓋索引減少回表

索引下推：可以在索引遍曆過程中，對索引中包含的字段先做判斷，直接過濾掉不滿條件的記錄，減少回表次數。

使用ICP，當存在索引的列做為判斷條件時，MySQL服務器將這一部分判斷條件傳遞給存儲引擎，然後存儲引擎通過判斷索引是否符合MySQL服務器傳遞的條件，只有當索引符合條件時才會將數據檢索出來返回給MySQL服務器。

將與索引相關的條件由MySQL服務器向下傳遞至存儲引擎，由此減少IO次數.索引條件下推優化可以減少存儲引擎查詢基礎表的次數，也可以減少MySQL服務器從存儲引擎接收數據的次數。5.6之後才有，但有實際數據存儲問題，唯一的缺點就是需要在磁盤上多做數據篩選，原來的篩選是放在內存中的，現在放在磁盤上進行，看起來成本比較高，但數據是排序的，所有的數據都是聚集存放，所以性能並不會受到影響，反而IO量會大大減少，提升性能

索引失效條件

（1）：條件是or，如果還想讓or條件效，給or每個字段加個索引

（2）：like開頭% （3）：如果列類型是字符串，那定要在條件中將數據使引號引起來，否則不會使索引

（4）：where中索引列使了函數或有運算

SQL優化

1、查詢語句中不要使用select *

2、盡量減少子查詢，使用關聯查詢（left join,right join,inner join）替代

3、減少使用IN或者NOT IN ,使用exists，not exists或者關聯查詢語句替代

4、or 的查詢盡量用 union或者union all 代替(在確認沒有重複數據或者不用剔除重複數據時，union

all會更好) 5、應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

6、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃

描，如：select id from t where num is null 可以在num上設置默認值0，確保表中num列沒有null

值，然後這樣查詢：select id from t where num=0

數據庫索引優缺點

（1）：需要查詢，排序，分組和聯合操作的字段適合建索引

（2）：索引多，數據更新表越慢，盡量使字段值不重複比例的字段作為索引，聯合索引多個獨索引效率

（3）：對數據進頻繁查詢進建索引，如果要頻繁更改數據不建議使索引

（4）：當對表中的數據進增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速

度。