給Myql創(chuàng)建索引的方法

這篇文章主要介紹給Myql創(chuàng)建索引的方法，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

為了提升Mysql的性能我們可以創(chuàng)建索引，來提升Mysql的搜索速度，還可以緩解對Mysql數(shù)據(jù)庫的壓力，下面我們來說說關(guān)于Mysql的索引和一些高級用法。

所有MySQL列類型可以被索引。根據(jù)存儲引擎定義每個表的最大索引數(shù)和最大索引長度。
所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的限制。

索引的存儲類型目前只有兩種（btree和hash），具體和存儲引擎模式相關(guān)：
MyISAM        btree
InnoDB        btree
MEMORY/Heap   hash，btree

默認情況MEMORY/Heap存儲引擎使用hash索引

MySQL的btree索引和hash索引的區(qū)別
hash 索引結(jié)構(gòu)的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像btree(B-Tree)索引需要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次的IO訪問，所以 hash 索引的查詢效率要遠高于 btree(B-Tree) 索引。

雖然 hash 索引效率高，但是 hash 索引本身由于其特殊性也帶來了很多限制和弊端，主要有以下這些。
（1）hash 索引僅僅能滿足=，<=>，IN，IS NULL或者IS NOT NULL查詢，不能使用范圍查詢。
由于 hash 索引比較的是進行 hash 運算之后的 hash 值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經(jīng)過相應(yīng)的 hash 算法處理之后的 hash 值的大小關(guān)系，并不能保證和hash運算前完全一樣。

（2）hash 索引無法被用來避免數(shù)據(jù)的排序操作。
由于 hash 索引中存放的是經(jīng)過 hash 計算之后的 hash 值，而且hash值的大小關(guān)系并不一定和 hash 運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算；

（3）hash 索引不能利用部分索引鍵查詢。
對于組合索引，hash 索引在計算 hash 值的時候是組合索引鍵合并后再一起計算 hash 值，而不是單獨計算 hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，hash 索引也無法被利用。

（4）hash 索引在任何時候都不能避免表掃描。
前面已經(jīng)知道，hash 索引是將索引鍵通過 hash 運算之后，將 hash運算結(jié)果的 hash 值和所對應(yīng)的行指針信息存放于一個 hash 表中，由于不同索引鍵存在相同 hash 值，所以即使取滿足某個 hash 鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 hash 索引中直接完成查詢，還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較，并得到相應(yīng)的結(jié)果。

（5）hash 索引遇到大量hash值相等的情況后性能并不一定就會比B-Tree索引高。
對于選擇性比較低的索引鍵，如果創(chuàng)建 hash 索引，那么將會存在大量記錄指針信息存于同一個 hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數(shù)據(jù)的訪問，而造成整體性能低下


B-Tree 索引是 MySQL 數(shù)據(jù)庫中使用最為頻繁的索引類型，除了 Archive 存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。不僅僅在 MySQL 中是如此，實際上在其他的很多數(shù)據(jù)庫管理系統(tǒng)中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree 索引的存儲結(jié)構(gòu)在數(shù)據(jù)庫的數(shù)據(jù)檢 索中有非常優(yōu)異的表現(xiàn)。
   一般來說， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結(jié)構(gòu)來存儲的，也就是所有實際需要的數(shù)據(jù)都存放于 Tree 的 Leaf Node ，而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的，所以我們大家都稱之為 B-Tree 索引當(dāng)然，可能各種數(shù)據(jù)庫（或 MySQL 的各種存儲引擎）在存放自己的 B-Tree 索引的時候會對存儲結(jié)構(gòu)稍作改造。
如 Innodb 存儲引擎的 B-Tree 索引實際使用的存儲結(jié)構(gòu)實際上是 B+Tree ，也就是在 B-Tree 數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上做了很小的改造，在每一個Leaf Node 上面出了存放索引鍵的相關(guān)信息之外，還存儲了指向與該 Leaf Node 相鄰的后一個 LeafNode 的指針信息，這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。
   在 Innodb 存儲引擎中，存在兩種不同形式的索引，一種是 Cluster 形式的主鍵索引（ Primary Key ），另外一種則是和其他存儲引擎（如 MyISAM 存儲引擎）存放形式基本相同的普通 B-Tree 索引，這種索引在 Innodb 存儲引擎中被稱為 Secondary Index 。
   在 Innodb 中如果通過主鍵來訪問數(shù)據(jù)效率是非常高的，而如果是通過 Secondary Index 來訪問數(shù)據(jù)的話， Innodb 首先通過 Secondary Index 的相關(guān)信息，通過相應(yīng)的索引鍵檢索到 Leaf Node之后，需要再通過 Leaf Node 中存放的主鍵值再通過主鍵索引來獲取相應(yīng)的數(shù)據(jù)行。
MyISAM 存儲引擎的主鍵索引和非主鍵索引差別很小，只不過是主鍵索引的索引鍵是一個唯一且非空 的鍵而已。而且 MyISAM 存儲引擎的索引和 Innodb 的 Secondary Index 的存儲結(jié)構(gòu)也基本相同，主要的區(qū)別只是 MyISAM 存儲引擎在 Leaf Nodes 上面出了存放索引鍵信息之外，
再存放能直接定位到 MyISAM 數(shù)據(jù)文件中相應(yīng)的數(shù)據(jù)行的信息（如 Row Number ），但并不會存放主鍵的鍵值信息。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。
MySQL索引類型包括：
（1）普通索引，這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：

-- 創(chuàng)建索引

CREATE INDEX indexName ON mytable(username(10));               -- 單列索引

-- CREATE INDEX indexName ON mytable(username(10),city(10));   -- 組合索引

-- indexName為索引名，mytable表名，username和city為列名，10為前綴長度，即索引在該列從最左字符開始存儲的信息長度，單位字節(jié)

-- 如果是CHAR，VARCHAR類型，前綴長度可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 前綴長度，下同。

-- 修改表結(jié)構(gòu)來創(chuàng)建索引

ALTER TABLE mytable ADD INDEX indexName (username(10));

-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));

-- 此處 indexName 索引名可不寫，系統(tǒng)自動賦名 username ，username_2 ，username_3，...

-- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

INDEX indexName (username(10))-- INDEX indexName (username(10),city(10))

);

-- 此處 indexName 索引名同樣可以省略

（2）唯一索引，它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式（僅僅在創(chuàng)建普通索引時關(guān)鍵字 INDEX 前加 UNIQUE）：

-- 創(chuàng)建索引

CREATE UNIQUE INDEX indexName ON mytable(username(10));

-- 修改表結(jié)構(gòu)來創(chuàng)建索引

ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- 也可簡寫成 ALTER TABLE mytable ADD UNIQUE indexName (username(10));

-- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

UNIQUE INDEX indexName (username(10)) -- 也可簡寫成 UNIQUE indexName (username(10))

);

（3）主鍵索引，它是一種特殊的唯一索引，不允許有空值。在建表的時候同時創(chuàng)建的主鍵即為主鍵索引
主鍵索引無需命名，一個表只能有一個主鍵。主鍵索引同時可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引：

-- 修改表結(jié)構(gòu)來創(chuàng)建索引ALTER TABLE mytable ADD PRIMARY KEY (id);

-- 創(chuàng)建表的時候直接指定CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,PRIMARY KEY(id)
);

（4）全文索引，InnoDB存儲引擎不支持全文索引：

-- 創(chuàng)建索引CREATE FULLTEXT INDEX indexName ON mytable(username(10));

-- 修改表結(jié)構(gòu)來創(chuàng)建索引ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));

-- 也可簡寫成 ALTER TABLE mytable ADD FULLTEXT indexName (username(10));

-- 創(chuàng)建表的時候直接指定CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName (username(10))

-- 也可簡寫成 FULLTEXT indexName (username(10)))ENGINE=MYISAM;

-- 建表時創(chuàng)建全文索引，要設(shè)置該表的存儲引擎為MYISAM,新版mysql默認InnoDB存儲引擎不支持全文索引

-- 刪除索引DROP INDEX indexName ON mytable;

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴(yán)重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。

以上是“給Myql創(chuàng)建索引的方法”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！