索引的概念
索引用于快速找到與特定的列值相同的行。如果沒有索引目尖，MySQL必須從第一行開始，然后通過整個表讀取找出相關(guān)行扎运。表越大越費(fèi)時瑟曲。如果表對該列建立了索引，MySQL就能夠迅速判斷绪囱，尋求到該行测蹲，而無需看所有的數(shù)據(jù)的位置。這比順序讀取每一行快得多鬼吵。
也就是說：
索引是用來解決查詢相關(guān)問題的扣甲。當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時，應(yīng)該想想是否可以建索引齿椅。你創(chuàng)建了合理的索引（實(shí)際上琉挖，索引也是一張表，該表保存了主鍵與索引字段涣脚，并指向?qū)嶓w表的記錄）示辈，MySQL就會在可以使用索引的時候（特定的操作），更快速地查詢出你想要的數(shù)據(jù)遣蚀。
索引的分類
從數(shù)據(jù)結(jié)構(gòu)角度
- B+樹索引(O(log(n)))
- hash索引：
a. 僅僅能滿足"=","IN"和"<=>"查詢矾麻，不能使用范圍查詢
b. 其檢索效率非常高，索引的檢索可以一次定位芭梯，不像B-Tree 索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)险耀，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠(yuǎn)高于 B-Tree 索引
c.只有Memory存儲引擎顯示支持hash索引 - FULLTEXT索引（現(xiàn)在MyISAM和InnoDB引擎都支持了）
- R-Tree索引（用于對GIS數(shù)據(jù)類型創(chuàng)建SPATIAL索引）
從物理存儲角度
-
聚集索引（clustered index）
Innodb存儲引擎表是索引組織表玖喘，即表中數(shù)據(jù)按主鍵順序存放甩牺。而聚集索引就是按每張表的主鍵構(gòu)造一顆B+樹。并且葉節(jié)點(diǎn)存放整張表的行記錄數(shù)據(jù)累奈。每張表只能有一個聚集索引（一個主鍵）贬派。
聚集索引的另一個好處是它對于主鍵的排序查找和范圍的速度非常快澎媒。葉節(jié)點(diǎn)的數(shù)據(jù)就是我們要找的數(shù)據(jù)搞乏。 -
非聚集索引（non-clustered index）
輔助索引（也稱非聚集索引）。葉級別不包含行的全部數(shù)據(jù)戒努，
輔助索引的存在并不影響數(shù)據(jù)再聚集索引中的組織查描，因此一個表可以有多個輔助索引。
當(dāng)通過輔助索引查找數(shù)據(jù)時，innodb會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引的主鍵冬三。然后再通過主鍵索引找到一行完整的數(shù)據(jù)。
從邏輯角度
- 主鍵索引：主鍵索引是一種特殊的唯一索引缘缚，不允許有空值
- 普通索引或者單列索引
- 多列索引（復(fù)合索引）：復(fù)合索引指多個字段上創(chuàng)建的索引勾笆，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用桥滨。使用復(fù)合索引時遵循最左前綴集合
- 唯一索引或者非唯一索引
- 空間索引：空間索引是對空間數(shù)據(jù)類型的字段建立的索引窝爪，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY齐媒、POINT蒲每、LINESTRING、POLYGON喻括。
創(chuàng)建索引
創(chuàng)建索引邀杏，就是對某個表的一個或多個列上建立索引。官方的定義語句和參數(shù)解釋非常清晰：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
1唬血、創(chuàng)建索引的時機(jī)
通常來說望蜡，應(yīng)該在創(chuàng)建的表的同時創(chuàng)建所有的索引。這一點(diǎn)對于使用InnoDB引擎的表來說尤其重要（因?yàn)橹麈I決定了記錄在數(shù)據(jù)文件中的物理結(jié)構(gòu)）拷恨。
a.創(chuàng)建表的同時創(chuàng)建索引 （詳見：CREATE TABLE
）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
-- 只看與創(chuàng)建索引相關(guān)的部分：create_definition
create_definition:
col_name column_definition
-- 主鍵索引（指定該列為主鍵脖律，同時創(chuàng)建主鍵索引）
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
-- 普通索引（為該列創(chuàng)建普通索引）
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)[index_option] ...
-- 唯一索引（指定該列為唯一鍵，同時創(chuàng)建唯一索引）
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name] [index_type] (index_col_name,...)[index_option] ...
-- 全文索引（為該列創(chuàng)建全文索引）
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)[index_option] ...
-- 外鍵索引（指定該列為外鍵腕侄，同時為該列創(chuàng)建外鍵索引）
| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
b.已創(chuàng)建的表添加索引（詳見： ALTER TABLE
）
-- 普通索引（INDEX或者KEY都會創(chuàng)建索引）
ALTER TABLE tbl_name
ADD {INDEX|KEY} [index_name][index_type] (index_col_name,...) [index_option] ...
-- 主鍵索引（雖然是添加主鍵小泉，但是同時也會創(chuàng)建索引）
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
-- 唯一索引
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name][index_type] (index_col_name,...) [index_option] ...
-- 全文索引
ALTER TABLE tbl_name
ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
-- 空間索引
ALTER TABLE tbl_name
ADD SPATIAL [INDEX|KEY] [index_name](index_col_name,...) [index_option] ...
-- 外鍵索引（雖然是添加外鍵，但是同時也會創(chuàng)建索引）
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
以上的ALTER語句后面都還可以加入：
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
從這里可以看出冕杠，并不是只有出現(xiàn)INDEX這個詞的時候微姊，才會創(chuàng)建索引。索引的分類也可以從上面總結(jié)出來拌汇。值得注意的是主鍵索引也是一種UNIQUE類型的索引柒桑，不僅不能重復(fù)，也不能含有NULL值噪舀。
從上面也可以看出魁淳，創(chuàng)建索引的方式有很多種，第一種就是在創(chuàng)建表的時候直接創(chuàng)建索引与倡，第二種就是針對已創(chuàng)建的表：通過ALERT TABLE命令或者直接用CREATE INDEX都是可以的界逛。
2、索引分類
UNIQUE
使用這個參數(shù)纺座，意味著創(chuàng)建了唯一索引：索引中所有的值都不能相同息拜，但可以放入多個NULL值（所有的引擎都支持）。如果指定了前綴長度，則必須保證在這個長度范圍內(nèi)的列值都不相同少欺。FULLTEXT
使用這個參數(shù)喳瓣，意味著創(chuàng)建了全文索引。但只有InnoDB和MyISAM引擎支持赞别，并且只能在CHAR畏陕、VARCHAR、TEXT類型的列上創(chuàng)建該索引仿滔。此時惠毁，是針對全部列值進(jìn)行索引崎页，即使定義了前綴長度也是無效的鞠绰。SPATIAL
使用這個參數(shù)，意味著創(chuàng)建了空間索引飒焦。
a.僅適用于只有InnoDB和MyISAM引擎蜈膨，其他引擎使用該索引會導(dǎo)致錯誤；
b.列值不能為NULL荒给；
c.不允許指定前綴長度丈挟，也就是說會使用整個列值進(jìn)行索引。索引的性質(zhì)（INDEX, UNIQUE, or PRIMARY KEY志电，除了SPATIAL）
a.所有支持空間類型列的存儲引擎都支持（ARCHIVE存儲引擎除外）
b.索引的列值都可為NULL（主鍵索引PRIMARY KEY除外）
c.索引類型因存儲引擎而已曙咽，目前使用的是B-tree。
d.You can add an index on a column that can have NULL values only for InnoDB, MyISAM, and MEMORY tables.
e.You can add an index on a BLOB or TEXT column only for using the InnoDB and MyISAM tables.
3挑辆、參數(shù)index_col_name
index_col_name:
col_name [(length)] [ASC | DESC]
如果指定了多個
index_col_name例朱，則意味著創(chuàng)建了聯(lián)合索引，最多含有16列鱼蝉。[ASC | DESC]
雖然可以指定洒嗤，但目前版本（截止到5.7.2）只是被解析，最后會被忽略：索引值總是以升序排列的魁亦。[(length)]：指定前綴長度
對字符串類型的列渔隶，可以只使用列值的一部分來作為來進(jìn)行索引。這么做的優(yōu)點(diǎn)是：如果在這個范圍（前綴長度）內(nèi)列值通常都不相同的話洁奈，這樣做不會降低查詢速度（相對使用整個列值而言）间唉，而且這樣會使索引文件更小，節(jié)省磁盤空間利术，甚至可能會加快插入操作的速度呈野。
對于VARCHAR、CHAR印叁、BINARY和VARBINARY類型的列來說被冒，這是可選的军掂，但是對于BLOB和TEXT類型的列，必須指定前綴長度昨悼。
這個長度是以字節(jié)來衡量的蝗锥。在創(chuàng)建表、修改表的語句中幔戏，對非二進(jìn)制字符串類型（VARCHAR玛追、CHAR、TEXT）來說闲延，[(length)]是指字符數(shù)；而對二進(jìn)制類型的字符串類型（BINARY韩玩、VARBINARY垒玲、BLOB）而言，[(length)]則是指字節(jié)數(shù)找颓。當(dāng)為一個使用了多字節(jié)字符集的非二進(jìn)制字符串類型的列指定前綴長度的時候合愈，尤其要注意。
對于空間數(shù)據(jù)類的列來說击狮，不能指定前綴長度佛析。
不同存儲引擎允許的最大前綴長度不同。
4彪蓬、參數(shù)index_option
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
KEY_BLOCK_SIZE [=] value
適用于MyISAM寸莫。index_type：指明該索引內(nèi)部使用的數(shù)據(jù)結(jié)構(gòu)類型：
| 存儲引擎 | 允許的類型 |
|---|---|
| InnoDB | BTREE |
| MyISAM | BTREE |
| MEMORY/HEAP | HASH, BTREE |
| NDB | HASH, BTREE |
可以看出，Mysql索引方式主要有兩種結(jié)構(gòu)：BTREE和HASH档冬。
HASH：將數(shù)據(jù)的索引以hash形式組織起來膘茎，因此當(dāng)查找某一條記錄的時候，速度非晨崾模快披坏。不支持范圍查找和排序等功能.
BTREE：數(shù)據(jù)結(jié)構(gòu)以平衡樹的形式來組織。因?yàn)槭菢湫徒Y(jié)構(gòu)盐数，所以更適合用來處理排序棒拂，范圍查找等功能诗舰。相對HASH索引敌蜂，BTREE在查找單條記錄的速度雖然比不上HASH索引泼差，但是更適合排序等操作楞慈，而且大部分都是范圍查找的查詢操作厌丑。
注意：不能為FULLTEXT INDEX or SPATIAL INDEX指定類型蟆炊，FULLTEXT INDEX的實(shí)現(xiàn)取決于存儲引擎猴伶，SPATIAL INDEX是使用 R-tree秩贰。
更詳情見下表：
WITH PARSER parser_name
FULLTEXT 索引時才可以使用該參數(shù)灰殴，指定解析器插件敬特。InnoDB和MyISAM支持解析器插件掰邢。COMMENT 'string'
為該索引添加注釋，最多為1024個字符伟阔。
4辣之、參數(shù)ALGORITHM 和 LOCK
這兩個參數(shù)可以影響表的復(fù)制方法和該表的讀寫并發(fā)級別。
刪除索引
DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
-- 這種是刪除主鍵皱炉，也會刪除主鍵索引
ALTER TABLE tbl_name DROP PRIMARY KEY
使用索引
當(dāng)執(zhí)行以下操作的時候怀估，MySQL會使用索引（前提是你為相應(yīng)字段創(chuàng)建了索引）：
快速找出符合where條件的行。
根據(jù)條件排除某些列合搅。如果有多個索引多搀，MySQL 通常會使用可以找出最少行的的那個索引。
如果一個表有聯(lián)合索引（multiple-column index）灾部，MySQL優(yōu)化時會選擇最左邊的索引列康铭。比如，你創(chuàng)建了含有三個列
(col1, col2, col3)的聯(lián)合索引赌髓，那么在查詢中(col1)从藤、(col1, col2)和(col1, col2, col3)時都會使用索引，其他情況則不會锁蠕。后面會解釋夷野。使用
join關(guān)鍵字從其他表中獲取記錄的時候。
如果它們（這里的它們是指join語句中on后面的等號兩邊的字段）擁有相同的類型和大小荣倾，那么MySQL使用索引的效率會更高悯搔。在這種情況下，VARCHAR和CHAR如果大小相同則認(rèn)為他們是一樣的逃呼，比如 VARCHAR(10) 和 CHAR(10)鳖孤，而VARCHAR(10) 和 CHAR(15)則不同。
如果比較字符串類型的列抡笼，那么這兩個列應(yīng)該使用相同的字符集苏揣，否則不會使用索引。找出某個創(chuàng)建了索引的列的最大或最小值推姻。
根據(jù)一個創(chuàng)建了索引的列來排序或分組
對于數(shù)據(jù)量較小的表平匈，或者需要訪問大部分?jǐn)?shù)據(jù)的查詢而言，索引并不重要藏古。
聯(lián)合索引的最左原則
假如你創(chuàng)建了這樣一張表增炭，同時創(chuàng)建了聯(lián)合索引：
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name) -- 創(chuàng)建聯(lián)合索引
);
那么，在下面的查詢語句中會使用索引：
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';
而下面的兩條查詢語句則不會使用索引：
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';
再來舉一個例子：
SELECT * FROM tbl_name WHERE col1=val1; -- a
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; --b
SELECT * FROM tbl_name WHERE col2=val2; --c
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; --d
--
假如聯(lián)合索引為 (col1, col2, col3)拧晕，只有a和b會使用索引隙姿。
雖然c和d的查詢中也包含了索引列，但是 (col2) 和(col2, col3) 不是 (col1, col2, col3)的最左前綴厂捞。
驗(yàn)證索引的使用
使用命令EXPLAIN输玷。待補(bǔ)充队丝。 Optimizing Queries with EXPLAIN
索引選擇
索引雖然可以加快查詢速度，但是也會帶來問題：索引文件會使磁盤空間的開銷變大欲鹏，修改操作由于要同時去修改索引文件所以會導(dǎo)致修改時性能變差机久。
該部分引用自：MySQL索引入門簡述和mysql索引需要了解的幾個注意
索引選擇原則
較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引
選擇性太低的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件赔嚎。選擇性：不重復(fù)的索引值（也叫基數(shù)膘盖，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T。
選擇性取值范圍為(0, 1]尤误，選擇性越高的索引價值越大侠畔。更新非常頻繁的字段不適合創(chuàng)建索引
不會出現(xiàn)在 WHERE 子句中的字段不該創(chuàng)建索引
使用短索引，如果對字符串列進(jìn)行索引损晤，應(yīng)該指定一個前綴長度践图，可節(jié)省大量索引空間，提升查詢速度沉馆；
參考
20180101--添加MySQL索引的分類
