給Myql創(chuàng)建索引

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

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

默認(rèn)情況MEMORY/Heap存儲(chǔ)引擎使用hash索引

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

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

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

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

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

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


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

索引分單列索引和組合索引。單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。組合索引，即一個(gè)索包含多個(gè)列。
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為前綴長(zhǎng)度，即索引在該列從最左字符開始存儲(chǔ)的信息長(zhǎng)度，單位字節(jié)

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

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

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

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

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

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

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)建普通索引時(shí)關(guān)鍵字 INDEX 前加 UNIQUE）：

-- 創(chuàng)建索引

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

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

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

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

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

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

);

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

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

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

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

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

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

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

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

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

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

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

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

以上是我對(duì)Mysql的創(chuàng)建索引總結(jié)出來(lái)的東西，希望今后會(huì)對(duì)大家有所幫助。

相關(guān)文章：

PHP中安插數(shù)據(jù)到myql顯示亂碼

myql5.7.7優(yōu)化配置參數(shù)_MySQL

以上就是給Myql創(chuàng)建索引的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！