MySQL性能優化(三)：索引

索引原理

如果一本新華字典假如沒有目錄，想要查找某個字，就不得不從第一頁開始查找，一直找到最後一頁(如果要找的字在最後一頁)，這個過程非常耗時，這種場景相當于資料庫中的全表掃描的概念，也就是循環表中的每一條記錄看看該記錄是否滿足條件，掃描次數為表的總記錄數。

新華字典中都會有目錄都有查找方法(比如按拼音查找、按部首查找)，假如按拼音查找，我們根據拼音就能瞬速定位到要找的漢字，而這個漢字後面還有這個漢字對應的頁數，我們直接翻到該頁就能找到，整個查找過程非常快，用時非常短。這個原理就是資料庫中索引的原理。這裡的按拼音查找、按部首查找是兩種不同的查找方式，通過每種方式都能快速找到，在資料庫中也有很多查找方式，稱之為索引方法，有BTREE、HASH兩種方式

BTREE：一顆倒立的樹，每個節點都有父節點，父節點下面的節點稱之為子節點(葉子節點)，比父節點值小的位于父節點下面的左方，比父節點值大的子節點放置在父節點下面的右下方。

記錄索引列的值和對應的記錄所在的磁盤位置，每次排除掉一半， 檢索一次相當排除掉2的n次幂，使用二叉樹排除30次相當于全表排除10億次。比如查詢id=11的值，首先和6比，比6大就排除掉左邊的，繼續和9比較，11比9大，又排除掉左邊的一般，和11進行比較，相等就找到了結果。當資料量很大的時候，每次都排除掉一半，排除的資料量是非常驚人的。

Hash：Hash索引隻能等值比對，想範圍查詢，左字首查詢都不适用， 其餘大部分場景

為什麼要使用索引？

索引大大減少了存儲引擎需要掃描的資料量

索引可以幫助我們進行排序以避免使用臨時表

索引可以把随機IO變為順序IO

索引類型

主鍵索引(primary key)：添加了主鍵就有了主鍵索引，可以在建立表的時候指定主鍵，也可以在建立成功之後再增加

唯一索引(unique)：添加了唯一限制就有了唯一索引,唯一索引可以有多個null

普通索引(normal)：一般是先建表，後面再建立索引，普通索引使用的最多

全文索引(fulltext)：主要針對文本段落等，全文索引隻能應用MyISAM引擎

空間索引(spatial): 使用較少，并且mysql支援的還不好

關于唯一性有兩種做法：

通過程式來保證資料的唯一性

業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。說明:不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的;另外即使在應用層做了非常完善的校驗控制，隻要沒有唯一索引，根據墨菲定律，必然有髒資料産生。（來自阿裡巴巴Java開發手冊）

全文索引隻能用于MyISAM引擎，通常如果用到全文索引一般通過Elasticsearch、Solr、Lucene等技術來實作。

索引文法

主鍵索引名為 pk_字段名，pk即 primary key

唯一索引名為 uk_字段名; uk即 unique key

普通索引名則為 idx_字段名；idx即index的簡稱。

③索引字段

一個索引可以針對一個字段進行建立，也可以指定多個字段建立複合索引。

④在哪些列上适合添加索引

頻繁作為查詢條件的列或者連接配接條件的列适合建立索引，即Where中的列或者是連接配接子句指定的列

唯一性太差的字段不适合建立索引，如性别

更新非常頻繁的字段不适合建立索引

不作為where條件的字段不要建立索引

選用NOT NULL的列

盡量使用字段長度小的列作為索引

使用資料類型簡單的列（int 型，固定長度）

ASC | DESC 選項 除非顯式指定降序 (DESC)，否則列以升序 (ASC) 排序。不管索引是升序排列還是降序排列，在執行升序或降序 ORDER BY 操作時都會使用索引。但是如果通過混合的升序和降序屬性來執行 ORDER BY，則僅當索引是用同樣的升序和降序屬性建立的時才使用索引。

如果是先建表，表中有比較多的資料，此時再建立索引，建立完索引需要等一會，讓索引在背景建立完再使用

索引數量控制，單張表中索引數量不應超過5個，單個索引中的字段數不超過5個。

索引存儲的位置位于mysql安裝的/xxx/data目錄下, 索引能提高查詢速度，但對update/delete/insert變慢,因為還要重新維護索引檔案，一般情況下查詢次數遠大于增删改

一般是先删除再建立