MySQL優化（3）：索引

MySQL優化中，最重要的優化手段就是索引，也是最常用的優化手段

索引簡介：

索引：關鍵字與資料位置之間的映射關系

關鍵字：從資料中提取，用于標識，檢索資料的特定內容

目的：加快檢索

索引檢索為什么快：

（1）關鍵字相對于資料本身，量較小

（2）關鍵字都是排序好的

MySQL中索引的型別：

普通索引，唯一索引，主鍵索引，全文索引

不同的型別只是對關鍵字的限制不同

普通索引：多索引關鍵字沒限制，有長度限制

唯一索引：要求記錄提供的關鍵字不能重復

主鍵索引：要求關鍵字不能重復而且不能為NULL

全文索引：不支持中文，后續細講

索引的語法：

查看索引：

SHOW CREATE TABLE [table-name];

 比如查到PRIMARY KEY(‘id’)，就是一個主鍵索引

創建索引：需要修改表結構和創建表時候完成，基于不同的型別，方式也不同

同時創建四個索引，由于使用到了全文索引，這里使用MYISAM引擎

CREATE TABLE USER(
ID INT AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(16),
LAST_NAME VARCHAR(16),
SN VARCHAR(16),
INFORMATION TEXT,
KEY(FIRST_NAME,LAST_NAME),
UNIQUE KEY(SN),
FULLTEXT KEY(INFORMATION)
)ENGINE=MYISAM;

索引可以命名，比如KEY NAME (FIRST_NAME,LAST_NAME)

這句話創建了一個基于FIRST_NAME和LAST_NAME的復合普通索引

UNIQUE KEY(SN) 創建了基于SN的唯一索引，默認以欄位名命名索引

最后一個全文索引很雞肋，基本不會用

在修改表結構的時候創建索引：

ALTER TABLE USER(
ADD KEY(FIRST_NAME,LAST_NAME),
ADD UNIQUE KEY(SN),
ADD FULLTEXT KEY(INFORMATION)
)

洗掉索引：

ALTER TABLE [table-name] DROP PRIMARY KEY;
ALTER TABLE [table-name] DROP KEY [key-name];

一般不簡易洗掉主鍵索引，記錄是按照主鍵來排序的，設計主鍵要注意一定與業務邏輯無關

執行計劃：

執行計劃：當MySQL執行SQL陳述句時，會分析、優化、形成執行計劃后按照執行計劃來執行

在執行計劃中可以清楚的看到當前的查詢是否需要用到索引：

EXPLAIN SELECT * FROM [table-name] WHERE ID<20;

結果中有一樣：KEY:PRIMARY，代表該查詢陳述句會用到主鍵索引

索引使用的場景：

如果兩張表，學生和班級表，多對一的關系，匯入較多的記錄來測驗

（1）WHERE查詢

EXPLAIN SELECT * FROM STUDENT WHERE ID=123456

和上面的例子一樣，可以看到使用到了主鍵索引

EXPLAIN SELECT * FROM STUDENT WHERE USERNAME="XXX"

這句話執行后會發現：KEY:NULL，沒有索引

我們給它加上索引：（這里的INDEX和上文的KEY都可以）

ALTER TABLE STUDENT ADD INDEX (USERNAME);

查看執行計劃后可以發現：KEY:USERNAME，說明使用到了新建的索引

（2）ORDER BY排序

我們有可能會遇到以下的情況：

SELECT * FROM STUDENT ORDER BY USERNAME;

查看執行計劃后可以發現沒有使用到任何索引，并且看到了Extra:Using Filesort，使用到了外部檔案排序，性能更低，需要先將資料讀取到記憶體，分段讀取合并排序

提高效率的方式是增加索引：

ALTER TABLE STUDENT ADD INDEX (USERNAME);

現在查看執行計劃后，可以發現使用到了USERNAME索引，并且沒有使用外部檔案排序，性能會有明顯提升

（3）JOIN 連接

使用到這條陳述句：

SELECT C.* COUNT(S.ID) FROM CLASS C JOIN STUDENT S ON C.ID=S.CLASS_ID GROUP BY C.ID;

當資料量巨大的時候，這句話要執行5S以上

查看執行計劃后，發現其中一張表沒有索引，且使用到了外部檔案排序

解決：

ALTER TABLE STUDENT ADD INDEX (CLASS_ID);

執行后發現速度明顯提升，并且兩張表都使用到了索引，沒有外部檔案排序

（4）索引覆寫

前三條很重要，是必須做的優化，這條只是一個現象

比如我們使用這條陳述句：建立一個復合索引

ALTER TABLE STUDENT ADD INDEX (FIRSTNAME,LASTNAME);

然后再執行：這句話沒有使用到以上三種情況

SELECT FIRSTNAME,LASTNAME FROM STUDENT;

但是查看計劃后，發現還是使用到了索引，并且Extra:Using index，說明這句話只使用了索引來完成

如果執行這句話

SELECT FIRSTNAME,LASTNAME,USER FROM STUDENT;

再查看計劃后發現沒有使用到了索引，并且進行了全表掃描

兩次的差異只是多了一個USER欄位，而復合索引沒有包含該欄位

總結：MySQL的查詢優先使用了索引，由于索引覆寫，建議SELECT后面只寫有必要的欄位，被覆寫的可能性就會提升，盡可能地優化

語法注意細節：

（1）欄位需要獨立出現

SELECT * FROM STUDENT WHERE ID+1=20;

這句話是能執行成功的，ID是主鍵，查看計劃后卻沒有使用到主鍵索引

欄位沒有獨立出現，不能觸發該欄位上的索引，避免這種情況

（2）LIKE查詢不能以通配符開頭

SELECT * FROM STUDENT WHERE USERNAME LIKE '%A%';

這句話無法使用到索引，如果是以下的情況，那么會用到索引

SELECT * FROM STUDENT WHERE USERNAME LIKE 'A%';

字串比較中，不能使用包含的邏輯，比如查詢包含Java的字串，不能寫'%Java%'，效率過低

解決辦法：全文索引，但是MySQL全文索引很雞肋，應該使用第三方的比如ES，Solr

（3）復合索引的右側欄位不能獨立使用索引

已有INDEX NAME(FIRSTNAME,LASTNAME);

使用陳述句，這句話使用到了索引

SELECT * FROM STUDENT WHERE FIRSTNAME='XXX';

而下面這句話沒有使用到索引

SELECT * FROM STUDENT WHERE LASTNAME='XXX';

原因：復合索引是按照左側欄位排序的，如果左側欄位相同再用右側欄位排序，總體上來看，右側欄位是未排序的

既然這樣為什么還要建復合索引呢？以下這種情況

SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' AND LASTNAME LIKE 'XX%';

這句話如果建立兩個索引，那么計算兩個索引的交集會更慢，所以需要復合索引

如果遇到上面的情況，再給LASTNAME建立一個索引即可

（4）早期版本NULL值無法使用索引

SELECT * FROM STUDENT WHERE FIRSTNAME=NULL;

新版本無需關心這一條

（5）OR語法保證兩邊的條件都有索引可用

SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' OR USER LIKE 'XX%';

如果USER沒有索引，那么還是會全表查詢

（6）狀態值不容易使用到索引

GENDER 0,1,2表示男，女，未知

即使在欄位上增加了索引，通常也不會起作用

SELECT * FROM STUDENT WHERE GENDER IN (0,1);

 原因：狀態值往往導致一個狀態值匹配大量記錄，查詢大量記錄的時候，MYSQL認為使用索引開銷比全表掃描都要大

如何創建索引：

（1）WHERE,ORDER BY,JOIN欄位上建立索引

（2）組合索引的建立：基于業務邏輯

（3）如果條件經常出現在一起，多欄位索引可以升級為復合索引

（4）如果通過增加個別欄位，就可以出現索引覆寫，那么增加個別欄位

（5）不會用到的索引應該刪掉

（6）常規情況下我們建立的資料庫系統本身性能就不差了

（7）有些欄位是否只使用前綴就能完成，使用前綴索引

前綴索引：INDEX(FIELD(10))

使用欄位field的前10個字符建立索引，默認是使用欄位全部內容建立索引

使用：GIT的COMMIT_ID；密碼欄位

索引的存盤結構：

BTREE索引，HASH索引，聚簇索引

以上概念指的是索引的存盤結構，資料結構上的概念，實際使用無需關心，了解即可

BTREE索引：

索引存盤在磁盤上所用的基礎的通用的存盤結構

特征：磁盤上的資料結構，不是二叉樹，一定要一個中文，那就是多路平衡查找樹

特點：一個BTREE節點，存盤多個索引關鍵字，多少由節點大小和關鍵字來確定的，

節點大小是固定的，由計算機檔案系統來確定，一次性磁盤讀取記憶體量，就是一個節點大小

由于一個節點的大小是固定的，一個節點無法容納大量關鍵字，所以分散在多個節點來存盤關鍵字

這時候如何進行排序呢？通過上層節點的子節點指標指向下層節點，用來關聯所有的節點，子節點指標位于關鍵字之間

例如每個節點存盤1000個關鍵字，深度為2的兩層BTREE大概可以存盤1000000（1000*1000）個關鍵字

查找一個關鍵字，需要讀取幾個節點的內容呢？從根開始，確定下級節點，僅僅兩次的磁盤讀取就可以做到

Btree的意義在于可以遍歷大量關鍵字，減少磁盤讀取量的開銷

聚簇索引：

關鍵字和記錄在一起進行存盤

是升級后的Btree，資料結構上的B+Tree

MySQL中只有Innodb的主鍵索引是聚簇結構

HASH索引：

當索引被載入到記憶體后采用的存盤結構，采用哈希結構存盤了，類似Java的Map，Key-Value

標籤：MySQL

上一篇：CentOS 7上更改MySQL資料庫存盤目錄淺析

下一篇：MySQL優化（4）：查詢快取