1、索引的作用
（1）如果索引為唯一索引，可以保證數據庫中每一行數據的唯一性
（2）索引如果創建的合適，會大幅度提高數據庫的查詢性能，這也是索引最大的作用
（3）索引能夠使得在查詢過程中，使用到數據庫的查詢優化器，極大提高系統的性能
2、索引的分類
（1）按照數據結構和使用的算法劃分：
B+Tree索引:
內部實現採用了B+Tree數據結構，數據全部存在葉子節點上。本質上是一棵平衡排序樹，由二叉樹進化而來，各個葉結點由指針相連，按照從左到右的順序讀取葉子結點上的數據，會得到一個有序的數列。
—————————————
Hash索引:
內部實現採用了Hash算法，數據的保存方式為一對一，一個鍵對應一條唯一的記錄，類似於Redis或者Memcached中的K-V存儲結構。
—————————————
R-Tree索引:
內部實現採用了R-Tree數據結構，R-Tree是一種空間索引的數據結構，它是B樹向多維空間發展的另外一種形式，在地理位置測繪領域有所應用，其他場景幾乎沒有應用，瞭解即可。
（2）按照類型劃分：
普通索引:
普通索引是一種最基本的索引，只是為了提高數據的查詢效率，也是開發中使用比較多的一種索引，允許索引列重複。
—————————————
主鍵索引:
用來唯一標識數據庫中的一條記錄，常用於保證數據庫中記錄的參照完整性，既不可為空，也不能重複。
—————————————
唯一索引:
用來唯一標識數據庫中的一條記錄，但是與主鍵索引稍有不同，唯一索引允許索引列的值為空，但是不允許索引列的值發生重複。
—————————————
聯合索引/組合索引:
指在數據庫表中的某幾個字段上同時建立的索引，即：這個索引會關聯不止一個列。使用的時候需要特別注意，這種索引遵循最左前綴匹配規則，在下面的索引使用中會詳細介紹。
—————————————
全文索引:
用來完成某一段文字中的關鍵字查找，可以簡單理解為like的加強版，不過使用方法和like不同，全文索引比較像一個搜索引擎。它目前支持的數據類型有：char，varchar和text類型。
3、索引的創建和查看
（1）索引的創建
語法：
方法一：使用CREATE INDEX方法
CREATE INDEX index_name ON table_name();
方法二：使用修改表結構的方法
ALTER TABLE table_name ADD INDEX index_name ON table_name();
方法三：在創建表的時候指定
CREATE TABLE table_name ( field1 INT NOT NULL AUTO_INCREMENT, field2 INT , field3 INT , PRIMARY KEY(field_name), UNIQUE index_name(field(len)), INDEX index_name(field(len)) );
示例：
示例1：創建一張t_user測試表，字段包含：[id（主鍵），user_no（用戶編號），login_name（登錄名稱），login_pass（登錄密碼），phone（手機號）]，要求：id作為主鍵，user_no列上建立唯一索引，login_name和login_pass兩個列上建立聯合索引，phone列上建立普通索引：
方法一：創建表的時候指定
CREATE TABLE t_user( id INT NOT NULL AUTO_INCREMENT, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL, PRIMARY KEY(id), #主鍵索引 UNIQUE user_no_ind(user_no), #唯一索引 INDEX name_pass_ind(login_name,login_pass), #聯合索引 INDEX phone_ind(phone) #普通索引 )ENGINE = InnoDB DEFAULT CHARSET = UTF8;
方法二：使用CREATE INDEX創建索引，此種方式不能創建主鍵索引
#創建表的時候先不指定索引： CREATE TABLE t_user( id INT NOT NULL AUTO_INCREMENT, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用CREATE INDEX命令創建表上的索引
CREATE UNIQUE INDEX user_no_ind ON t_user(user_no);
CREATE INDEX name_pass_ind ON t_user(login_name,login_pass);
CREATE INDEX phone_ind ON t_user(phone);
方法三：使用ALTER TABLE修改表結構的方式創建索引
#創建表結構 CREATE TABLE t_user( id INT NOT NULL, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用ALTER TABLE命令創建索引
ALTER TABLE t_user ADD PRIMARY KEY(id);
ALTER TABLE t_user ADD UNIQUE INDEX user_no_ind(user_no);
ALTER TABLE t_user ADD INDEX name_pass_ind(login_name,login_pass);
ALTER TABLE t_user ADD INDEX phone_ind(phone);
示例2：創建一張帖子內容表，並在帖子內容列創建全文索引
方法一：創建表結構的時候指定索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id), FULLTEXT(note_content) #添加全文索引 ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
方法二：使用CREATE INDEX方式創建全文索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
CREATE FULLTEXT INDEX note_ind ON t_note(note_content);
方法三：使用ALTER TABLE修改表結構的方式創建全文索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
ALTER TABLE t_note ADD FULLTEXT note_ind(note_content);
（2）索引信息的查看
語法：
SHOW INDEX FROM table_name ;
SHOW INDEXES FROM table_name ;
注意：SHOW後面可以為INDEX或者INDEXES，可以使用WHERE條件根據索引名稱查看索引信息
示例：查看t_user表上所創建的索引
SHOW INDEXES FROM t_user \G
*************************** 1. row ***************************
Table: t_user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
…其他行略…
結果字段解釋：
Table：索引所在的表名稱
Non_unique：是否為非唯一的索引，對唯一索引和主鍵索引，此值為0，因為主鍵和唯一鍵必須唯一
Key_name：索引的名稱
Seq_in_index：索引中該列的位置，在
Column_name：索引所在列的列名稱
Collation：列使用哪種方式存儲在索引中，B+數索引總是A，表示排過序的。對於其他的如Hash索引，此處可能為NULL，因為Hash索引並未排序
Cardinality：索引中非胃一直的數目的估計值，通常用於優化器去判斷是否查詢時使本索引
Sub_part：是否只是用了列的一部分作為索引。比如：在某個非常長的字段上的前多少個字符上創建索引的情況
Packed：關鍵字如何被壓縮，Null表示未被壓縮
Null：索引的列中是否含有Null值，主鍵索引，此處為空，表示不含有Null值
Index_type：索引類型，InnoDB存儲引擎，此處為B+樹
Comment：索引列的註釋
Index_comment：索引的註釋
注意：上述字段中，Cardinality字段相對來說比較重要，可以通過該字段來判斷當前的索引是否最優，通常如果索引的利用率比較高的話，這個值會比較接近於表中的記錄數，即：和表中的記錄數接近於1:1，但是這個值並不是實時維護，索引當相差比較大的時候，可以使用”ANALYZE TABLE table_name”命令去更新下這個值，有利於優化器對索引使用的判斷。
4、索引的修改和刪除
（1）索引的刪除
語法：
DROP INDEX index_name ON table_name;
示例：
示例1：刪除t_user表中phone列上的phone_ind索引
DROP INDEX phone_ind ON t_user;
示例2：刪除t_user表中的id列上的主鍵索引
ALTER TABLE t_user DROP PRIMARY KEY;
（2）索引的修改
索引的修改過程其實是先刪除索引，在重新創建索引，可以按照上述的刪除索引和創建索引步驟完成。
5、索引使用注意事項
（1）使用場景
a.業務場景中，讀多寫少的場景
b.SQL查詢場景中，常用於WHERE語句之後的過濾條件；區分度大於80%；WHERE語句之後的過濾字段在過濾時不參與運算；
（2）以下的情況，對於有索引的列，查詢時也不會使用索引
a.當優化器判斷使用索引和不適用索引差別不大時，將不會使用索引，比如：性別列創建的索引
b.查詢條件中發生計算，優化器將不使用索引，比如：WHERE SUBSTR(name,5) = ‘BING’
c.查詢條件中包含了隱士類型轉換，比如：WHERE phone = 13520277199
d.反向查詢不會使用索引，比如：!=,<>,NOT IN,NOT LIKE等，比如：WHERE name != ‘BING’;
e.LIKE的左模糊匹配，將不會使用索引，比如：WHERE name LIKE ‘%BING’，右匹配查詢會走索引;
f.聯合索引中，不滿足左前綴規則，則MySQL不會使用索引。比如：對於name,pass,user_no列的聯合索引，下述情況將不會使用索引：
WHERE pass = ‘value’
WHERE user_no = ‘value’
WHERE pass = ‘123’ AND user_no = ‘123’
而如下的情況將會使用到索引：
WHERE name = ‘bing’
WHERE name = ‘bing’ AND pass = ‘123’;
WHERE name = ‘bing’ AND user_no = ‘021250’;
WHERE pass = ‘123’ AND name = ‘bing’;
WHERE name = ‘bing’ AND pass = ‘123’ AND user_no = ‘021250’;
g.某個帶索引的列和不帶索引的列中間使用OR連接，則帶索引的列也不會使用索引，如：user_no列帶有索引，phone_未帶索引，則：
不會使用索引：WHERE user_no = ‘123’ OR phone = ‘13520277898’
會使用索引：WHERE user_no = ‘123’ AND phone = ‘15265648758’
h.如果在聯合索引中有範圍查詢，如果字段之間使用OR連接，則整個查詢條件不會使用索引，如果字段之間使用AND連接，則從第一個範圍查詢開始之後的條件都不會使用索引
比如：name,score,usre_no列上的聯合索引，則：
不會使用索引：WHERE name = ‘bing’ OR score = 123 OR user_no = ‘02311’;
不會使用索引：WHERE name = ‘bing’ AND score = 123 OR user_no = ‘01231’;
會使用索引：WHERE name = ‘bing’ AND score = 123 AND user_no = ‘021321’;
name列會使用索引，name之後的列不會使用索引：
WHERE name = ‘bing’ AND score > 120 AND user_no = ‘021321’;
6、執行計劃查看
語法：
EXPLAIN
示例：
示例1：查看t_user表上面的id列查詢執行計劃：
EXPLAIN SELECT * FROM t_user WHERE id = 1 \G
********** 1. row ******
id: 1
select_type: SIMPLE
table: t_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
輸出字段說明：
id:查詢編號，如果有多個大小相同的編號，則執行順序為從上到下，如果大小相同，則編號大的先執行
select_type:表示查詢類型，有以下幾種值：
a.SIMPLE:表示該查詢為一個簡單查詢，如上述的根據id查詢就是一個簡單查詢
b.SUBQUERY:表示該查詢是WHERE字句中的子查詢
c.DERIVED:表示該查詢是FROM字句中的子查詢
d.PRIMARY:表示一個負責嵌套查詢最外層的查詢
e.UNION:表示UNION查詢的第二個SELECT子查詢
f.UNION RESULT:表示該結果是從UNION表中查詢出的結果
table:表示查詢時所關聯的表
type:表示關聯類型或者訪問類型，常見的幾種值如下：
ALL:表示未使用索引，掃描全表
index:表示掃描所有的索引，通過掃描索引樹去定位所有待查詢數據
range:表示按照索引的範圍掃描，即：從某索引的某個位置開始，到索引的另外一個位置結束，找出這個範圍內每個索引對應的數據，範圍查詢會出現range
ref:非唯一索引掃描，MySQL將返回匹配這個索引的所有行
eq_ref:唯一索引掃描，即：通過該索引只能定位到表中的一條數據，主鍵索引和唯一索引屬於這種類型
possible_keys:查詢優化器可能使用到的索引，但是不一定使用
key:查詢優化器真正使用的索引
key_len:使用的索引長度，比如：在某個比較長的列上，通常只會給前多少個字符創建索引，這個長度就表示索引字符的長度
ref:表示表的連接匹配條件中，哪些列或者常量被用於查找索引列上的值。可以理解為如果要完成這個查詢，需要關聯其他表中的哪個列或者常量
rows:要查詢到目標數據，需要掃描的行數
Extra:其他額外信息，常見的有以下幾種：
a.Using Where:表示查詢結果需要在存儲引擎層通過Where條件完成過濾
b.Using index:表示該Where條件的查詢使用到了覆蓋索引，即：該索引包括了滿足查詢所有數據的需求；
c.Using tempory:表示該查詢使用到了臨時表來存儲中間結果集，通常在排序或者分組查詢中會出現
d.Using filesort:表示該查詢使用到了寫磁盤的方式來存儲結果集，出現這種情況，表示查詢性能極差，已經發生了磁盤IO
以上的查詢計劃中主要關注的列：
type:查詢類型，該列出現了ALL的查詢類型，就表示查詢語句有問題，需要根據索引使用的注意事項來排查
key:表示查詢是否用到了索引，如果該列為NULL，表示索引未起到實際作用
rows:查詢行數，如果特別大，和表中的數據條數相差不大，則表示索引利用率特別低，需要優化索引
Extra:如果發現有Using filesort，表示索引的效率特別差，已經發生了磁盤IO，需要排查對應的語句和索引使用情況
示例2：查詢t_user表中id大於4的記錄，觀察其執行計劃
EXPLAIN SELECT * FROM t_user WHERE id > 4\G
********* 1. row *********
id: 1
select_type: SIMPLE
table: t_user
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
從上述輸出結果看出：type列為range，possible_keys和key都為PRIMARY，表示該查詢使用到了範圍查詢，而且用到了主鍵索引。
示例3：查看t_user表中login_name列的索引使用情況
EXPLAIN SELECT * FROM t_user WHERE login_name = ‘aaa’ \G
*********** 1. row **********
id: 1
select_type: SIMPLE
table: t_user
type: ref
possible_keys: name_pass_ind
key: name_pass_ind
key_len: 152
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
由結果可以看出，login_name列的條件查詢用到了login_name,login_pass列的聯合索引。
示例4：查看t_user表中id最大的記錄的執行計劃
EXPLAIN SELECT * FROM t_user WHERE id = (SELECT MAX(id) FROM t_user) \G
*********** 1. row ********
id: 1
select_type: PRIMARY
table: t_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
************ 2. row ********
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
2 rows in set (0.00 sec)
由上述結果可以看出，位於WHERE之後的子查詢的查詢類型為SUBQUERY，直接查詢最大id不會使用到索引。
7、索引優化
（1）對於主鍵索引，最好採用整型數字的格式，因為在普通索引中，索引樹的葉子節點上存儲的是主鍵索引的值，這個值如果過大，會導致普通索引會變的特別大
（2）在更新頻繁的列上最好少創建索引，因為更新需要維護索引樹，而這個維護過程是很耗時的
（3）創建聯合索引時，應該考慮哪些組合列上的查詢需求最大，從而確定聯合索引的順序，因為聯合索引有左前綴規則
（4）索引並不是越多越好，索引如果過多，會導致磁盤大量浪費，而且在更新這些查詢較少的列時，會產生很大的IO操作，造成服務器資源浪費
（5）索引可以在後期通過監控MySQL數據庫中的慢SQL再來優化和添加。最好的辦法還是開始就考慮周全，創建好索引
（6）添加索引的時候，需要注意是否有慢SQL，如果有慢SQL，會阻塞索引的添加操作，一直處於等待中
文章為作者原創，轉載請務必標註出處：https://www.jinnianshizhunian.vip
手機端顯示格式的問題，好多文章發佈之後拍板會混亂，如果需要，可以複製上述作者網址，在PC端查看更多文章。
