深入分析MySQL數據庫索引

mysql索引在數據庫中非常的重要了，數據加沒索引對於我們網站性能有巨大的區別了，今天我們來看一篇關於MySQL數據庫索引的介紹。

什麼是索引

索引是存儲引擎用於快速找到記錄的一種數據結構，索引類似一本書的目錄，我們根據目錄可以快速的查找到我們感興趣的內容。索引就是存儲引擎的目錄,如果沒有索引存儲引擎必須遍歷整個數據庫表來查詢符合條件的記錄,索引的建立和優化應該是提升查詢性能最有效的手段了。

索引的類型

索引是在MYSQL的存儲引擎層中實現的，而不是在服務層實現的。所以每種存儲引擎的索引都不一定完全相同，也不是所有的存儲引擎都支持所有的索引類型。即使多個存儲引擎支持同一種類型的索引，其底層實現也可能不同。

B-Tree索引

B-Tree是MyISAM和InnoDB引擎默認索引類型，也可以在創建索引時通過USING BTTREE來顯示指定。B-Tree是一種多叉平衡樹，B-Tree 結構可以顯著減少定位記錄時所經歷的中間過程，從而加快存取速度。一般用於數據庫的索引，綜合效率較高。

B-Tree索引的應用場景

等值匹配

可用於= != <> IN NOT IN <=>查詢語句的優化

范圍匹配

可用於 > >= < <= BTEWEEN AND等范圍查詢語句的優化

匹配最左前綴

對於 name like bai% 這種後模糊匹配的查詢，是可以利用name字段上建立的索引來優化查詢的，但是對於name like %bai這種前模糊匹配的查詢則沒有辦法使用索引了

覆蓋索引

覆蓋索引是指所有需要查詢的字段都在索引已經存在了，那麼就不需要再去查詢數據了，這種查詢效率很高。
select id where id >100

排序

B-Tree索引是排好序的,所以MySQL可以用來做ORDER BY 和 GROUP BY操作。

哈希索引(HASH)

哈希索引基於哈希表實現,只有Memory引擎顯示支持哈希索引，使用哈希索引可以一次定位，所以 Hash 索引的查詢效率要遠高於 B-Tree 索引。但是哈希索引是有很多限制的:

只有精確匹配索引所有列的查詢才有效，因為哈希索引是利用索引的所有列的字段值來計算哈希值的，
只支持等值比較查詢，不能用於范圍查詢。

哈希索引的只包含索引字段的哈希值he和指向數據的指針，所以不能使用索引中的值來避免讀取行。
哈希索引的數據並不是順序存儲的，無法用於排序。

全文索引(FULLTEXT)

全文索引，是一種通過建立倒排索引，快速匹配文檔的方式。

空間索引(SPATIAL)

聚集索引&非聚集索引

聚集索引

聚集索引並不是一種單獨的索引類型，而是一種數據存儲方式，Innode的聚集索引實際上是將主鍵(PRIMARY kEY )與數據行存放在同一個文件的,一張表只能有一個聚集索引。 

InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會用一個唯一且不為空的索引列做為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵。

INNODB的普通索引(二級索引)的葉子節點中存放的是PRIMARY KEY的值,所以需要先查詢普通索引(二級索引)的葉子節點找到對應的主鍵值，然後再根據主鍵值去聚集索引中查詢到對應的數據。 

InnoDB將主鍵與數據聚集在一起的方式，使得按主鍵順序的插入和查詢效率會很高，但是更新主鍵的字段或者不按主鍵的順序插入數據的代價會比較高，所以主鍵的選取很重要(使用AUTO INCREMENT字段或者應用程序生成的順序遞增字段要比無序的UUID好的多)
二級索引會保存主鍵的值，所以主鍵的值不要太大。

非聚集索引

非聚集索引的索引與數據是存在在不同文件的,對於MyISAM引擎的一張表,會有三種文件:FRM(表結構)、MYD（數據，就是數據庫中的每個行）、MYI（索引）。

MySQl使用索引查詢數據時，先到MYI文件中找出數據存儲的位置指針，然後再到MYD文件中讀取數據。 

MyISAM中主鍵索引和其他索引在結構上沒有什麼不同，主鍵索引就是一個名為PRIMARY的唯一非空索引。

索引操作

創建

在執行CREATE TABLE語句時可以創建索引，也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引。

CREATE TABLE
CREATE TABLE  table_name(
column_name data_type,
......
[UNIQUE|FULLTEXT|SPATIAL] {INDEX|KEY} index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
);
ALTER TABLE
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
ALTER TABLE table_name ADD PRIMARY KEY (col_name [(length)] [ASC | DESC]..)
CREATE INDEX
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING {BTREE | HASH}] ON tbl_name (col_name [(length)] [ASC | DESC],...)

刪除

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

查看

SHOW INDEX FROM table_name

高效索引策略

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.