一、索引的作用

索引通俗來講就相當于書的目錄，當我們根據條件查詢的時候，沒有索引，便需要全表掃描，數據量少還可以，一旦數據量超過百萬甚至千萬，一條查詢sql執行往往需要幾十秒甚至更多，5秒以上就已經讓人難以忍受了。

提升查詢速度的方向一是提升硬件(內存、cpu、硬盤)，二是在軟件上優化(加索引、優化sql；優化sql不在本文闡述范圍之內)。

能在軟件上解決的，就不在硬件上解決，畢竟硬件提升代碼昂貴，性價比太低。代價小且行之有效的解決方法就是合理的加索引。

索引使用得當，能使查詢速度提升上萬倍，效果驚人。

二、MySQL索引類型：

mysql的索引有5種：主鍵索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。

唯一索引和全文索引用的很少，我們主要關注主鍵索引、普通索引和聚合索引。

1)主鍵索引：主鍵索引是加在主鍵上的索引，設置主鍵(primary key)的時候，mysql會自動創建主鍵索引；

2)普通索引：創建在非主鍵列上的索引；

3)聚合索引：創建在多列上的索引。

三、索引的語法：

查看某張表的索引：SHOW INDEX FROM 表名；

創建普通索引：ALTER TABLE 表名?ADD INDEX ?索引名 (加索引的列)

創建聚合索引：ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2)

刪除某張表的索引：DROP INDEX?索引名 ON 表名;

四、EXPLAIN?分析SQL執行的狀態

EXPLAIN列的解釋

table? ? ? ? ? ? ? ? ? ? 顯示這一行的數據是關于哪張表的

type? ? ? ? ? ? ? ? ? ? ?這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

possible_keys? ? ?顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句

key? ? ? ? ? ? ? ? ? ? ? 實際使用的索引。如果為NULL，則沒有使用索引。

key_len? ? ? ? ? ? ? ?使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref? ? ? ? ? ? ? ? ? ? ? ?顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows? ? ? ? ? ? ? ? ? ??MYSQL認為必須檢查的用來返回請求數據的行數

Extra? ? ? ? ? ? ? ? ? ?關于MYSQL如何解析查詢的額外信息。

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Extra字段值含義：

Distinct? ? ? ? ? ? ? ? ? ?一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了

Not exists? ? ? ? ? ? ? ?MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了

Range checked for each Record(index map:#)? ? ??沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一

Using filesort? ? ? ? ??看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

Using index? ? ? ? ? ??列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候

Using temporary? ??看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Where used? ? ? ? ? ?使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋(按照效率高低的順序排序)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

type字段值含義：

const? ? ? ?表中的一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或惟一索引)。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然后把它當做常數來對待

eq_ref? ? ?連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用

ref? ? ? ? ? ?這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分(比如，利用最左邊前綴)時發生。對于之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少—越少越好

range? ? ? 這個連接類型使用索引返回一個范圍中的行，比如使用>或

index? ? ? ?這個連接類型對前面的表中的每一個記錄聯合進行完全掃描(比ALL更好，因為索引一般小于表數據)

ALL? ? ? ? ?這個連接類型對于前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免

五、性能測試

(一)、測試環境

測試環境：博主家用臺式機

處理器為AMD FX(tm)-8300 Eight-Core Processor 3.2GHz;

內存8G;

64位 windows 7。

MySQL： 5.6.17

(二)、MyISAM引擎測試

1).?創建一張測試表

DROP TABLE IF EXISTS `test_user`;

CREATE TABLE `test_user` (

`id` bigint(20) PRIMARY key not null AUTO_INCREMENT,

`username` varchar(50) DEFAULT NULL,

`email` varchar(30) DEFAULT NULL,

`password` varchar(32) DEFAULT NULL,

`status` tinyint(1) NULL DEFAULT 0

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

存儲引擎使用MyISAM是因為此引擎沒有事務，插入速度極快，方便我們快速插入千萬條測試數據，等我們插完數據，再把存儲類型修改為InnoDB。

2).? 使用存儲過程插入1千萬條數據

create procedure myproc()

begin

declare num int;

set num=1;

while num <= 10000000 do

insert into test_user(username,email,password) values(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));

set num=num+1;

end while;

end

3).? 執行??call?myproc();

由于使用的MyISAM引擎，插入1千萬條數據，僅耗時246秒，若是InnoDB引擎，插入100萬條數據就要花費數小時了。

MyISAM引擎之所以如此之快，一個原因是使用了三個文件來存儲數據，frm后綴存儲表結構、MYD存儲真實數據、MYI存儲索引數據。

每次進行插入時，MYD的內容是遞增插入，MYI是一個B+樹結構，每次的索引變更需要重新組織數據。

但相對于InnoDB來說，MyISAM更快。

4).?sql測試

1. SELECT id,username,email,password FROM test_user WHERE id=999999

耗時：0.114s。

因為我們建表的時候，將id設成了主鍵，所以執行此sql的時候，走了主鍵索引，查詢速度才會如此之快。

2. 我們再執行： SELECT id,username,email,password FROM test_user WHERE username='username_9000000'

耗時：4.613s。

用EXPLAIN分析一下：

信息顯示進行了全表掃描。

3. 那我們給username列加上普通索引。

ALTER TABLE `test_user` ADD INDEX index_name(username) ;

此時，Mysql開始對test_user表建立索引，查看mysql?數據目錄：

查看目錄文件列表，可以看到新建了三個臨時文件，新的臨時數據表MYD文件大小并未變更，臨時索引文件MYI文件大小增加了很多。

查看執行結果：

此過程大約耗時?221.792s,建索引的過程會全表掃描，逐條建索引，當然慢了。

等執行完畢后，mysql把舊的數據庫文件刪除，再用新建立的臨時文件替換掉之。(刪除索引過程也是同樣的步驟)。

4. 再來執行：select id,username,email,password from test_user where username='username_9000000'

耗時：0.001s。

可見查詢耗時提高的很可觀。

用EXPLAIN分析一下：

Extra?字段告訴我們使用到了索引?index_name，和之前的EXPLAIN結果對比，未建立索引前進行了全部掃描，建立索引后使用到了索引，查詢耗時對比明顯。

5. 再用username和password來聯合查詢

SELECT id, username, email, PASSWORD FROM test_user WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' AND username = 'username_9000000';

耗時：0.001s

執行 EXPLAIN ：

顯示使用到了?index_name?索引，條件語句不分password、useranme先后順序，結果都是一樣。說明sql優化器優先用索引命中。

6. 我們再執行：SELECT id, username, email, PASSWORD FROM test_user WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

此時雖然我們已經對 username 加了索引，但是password列未加索引，索引執行password篩選的時候，還是會全表掃描，因此此時查詢速度立馬降了下來。

耗時：5.118s。

EXPLAIN一下：

使用OR條件的時候，雖然WHERE?語句中有用到索引字段，但還是進行了全表掃描。

7. 當我們的sql有多個列的篩選條件的時候，就需要對查詢的多個列都加索引組成聚合索引：

加上聚合索引：ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

通過臨時文件的大小來看，索引文件的大小已經超過了數據文件很多了。索引側面來說，索引要合理利用，索引就是用空間換時間。

[SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

受影響的行: 10024725。

時間: 1399.785s。

8. 再來執行：[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' OR `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

耗時：4.416s。

EXPLAIN：

竟然是全表掃描，不可思議！！！?使用 OR?語句竟然沒有啟用聚合索引，也沒使用到單索引username，，，

9. 再來執行：[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' AND `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

耗時：0.001s。

EXPLAIN：

AND?語句才使用到了聚合索引，聚合索引必須使用AND條件，同時要符合最左原則，請戳我。

10. 主鍵區間查詢

[SQL]EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999990 AND id < 8999999

受影響的行: 0

時間: 0.001s。

命中7行，查詢時間很短。

[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999900 AND id < 8999999

受影響的行: 0

時間: 0.010s

[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999000 AND id < 8999999

受影響的行: 0

時間: 0.029s

[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8990000 AND id < 8999999

受影響的行: 0

時間: 0.139s

通過不斷加大區間來看，查詢時間跟查詢的數據量成相對的正比增長，同時使用到了主鍵索引。

11. 字符串區間查詢

[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

受影響的行: 0

時間: 6.059s

EXPLAIN:

未使用索引和聚合索引，進行了全表掃描。

[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_900000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

受影響的行: 0

時間: 11.488s

EXPLAIN:

也使用到了索引和聚合索引。

對比得出，字符串進行區間查詢，是否能使用到索引的條件得看mysql是如何優化查詢語句的。

12.最左原則

1].?新建?A、B、C?聚合索引

[SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_email_password(username,email,password)

受影響的行: 10024725

時間: 3171.056s

2]. SQL?測試

慎用 OR?條件，可能將會導致全表掃描。

覆蓋了 A、B、C?索引：

該語句使用了覆蓋索引，WHERE?語句的先后順序并不影響。MySQL會對SQL進行查詢優化，最終命中ABC索引。

命中了?A、B、C?索引中的 AB組合，查詢耗時很短：

沒有命中到?A、B、C?索引，所以進行了全表掃描，查詢耗時長。

小結：

要使用覆蓋索引必須都是 AND?條件，慎用 OR?條件。

要使用覆蓋索引如ABC，需滿足條件語句中有 A、AB、ABC才會使用覆蓋索引，采用最左原則。

(三)、InnoDB引擎測試

1).?新建?InnoDB ?表

根據上文的步驟，新建一個 test_user_innodb? 表，引擎使用MyISAM，然后將存儲引擎修改回InnDB。

使用如下命令：? ALTER TABLE test_user_innodb ENGINE=InnoDB; 此命令執行時間大約耗時5分鐘，耐心等待。

[SQL]ALTER TABLE test_user_innodb ENGINE=InnoDB;

受影響的行: 10024725

時間: 692.475s

執行完畢后，?test_user_innodb 表由之前的 三個文件 變為?兩個文件，test_user_innodb.frm?和?test_user_innodb.idb。

其中frm文件記錄表結構，idb文件記錄表中的數據，其實就是一個B+樹索引文件，不過該樹的葉子節點中的數據域記錄的是整行數據記錄。

所以?Innodb?的查找次數比 MyISAM 表減少一次磁盤IO查找邏輯，但相對來說，插入數據也就沒有MyISAM?快了，有所求就有所得吧！

同時?InnoDB?支持行鎖、表鎖，InnoDB?的鎖機制是建立在索引上的，所以如果沒命中索引，那么將是加表鎖。

2). SQL?測試

1. [SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

受影響的行: 0

時間: 14.540s

顯示進行了全表掃描，但跟MyISAM表對比來說，掃描的行數小了很多，可能這就是底層B+樹布局不一樣導致的吧。

2. 那我們給username列加上普通索引。

ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;

此時，Mysql開始對 test_user_innodb 表建立索引，查看mysql?數據目錄：

仔細觀察，發現只生成了一個表結構臨時文件。ibd文件容量在不斷增大。這個跟MyISAM表加索引邏輯不一樣。

[SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;

受影響的行: 0

時間: 157.679s

此過程大約耗時 157.679s,?貌似建索引的過程未進行全表掃描，對比MyISAM表減少60s左右。為何如何？估計需要看底層實現了！

3.?再執行?SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

[SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

受影響的行: 0

時間: 0.001s

可見查詢耗時減少的很可觀，對比與未加索引。用EXPLAIN分析一下，和MyISAM表沒有多少差別。

4.?再用username和password來聯合查詢

SELECT id, username, email, PASSWORD FROM test_user_innodb? WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' AND username = 'username_9000000';

耗時：0.001s

執行 EXPLAIN ：

顯示使用到了?index_name?索引，條件語句不分password、useranme先后順序，結果都是一樣。說明sql優化器優先用索引命中。

5. 我們再執行：SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

此時雖然我們已經對 username 加了索引，但是password列未加索引，索引執行password篩選的時候，還是會全表掃描，因此此時查詢速度立馬降了下來。

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

受影響的行: 0

時間: 10.719s

EXPLAIN一下：

使用OR條件的時候，雖然WHERE?語句中有用到索引字段，但還是進行了全表掃描。

對比MyISAM?表來說，沒有多大卻別，唯一的就是rows行數不一樣。

6. 加上聚合索引：ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_password(username,password)

此時，Mysql開始對 test_user_innodb 表建立索引，查看mysql?數據目錄，和之前的一樣，新增了一個臨時表結構文件，ibd文件不斷增大。

[SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_password(username,password)

受影響的行: 0

時間: 348.613s

建立索引的時間比MyISAM?快。

7. 再來執行：[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

受影響的行: 0

時間: 10.357s

對比MyISAM?竟然是慢了6s左右？?和MyISAM?的全表掃描無差別。

InnoDB的OR查詢性能沒有MyISAM?快，應該是為了實現事務導致的性能損失？

8. 再來執行：[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' AND `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

耗時：0.001s。

EXPLAIN：

AND?語句才使用到了聚合索引，聚合索引必須使用AND條件，同時要符合最左原則，請戳我。

9. 主鍵區間查詢

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999990 AND id < 8999999

受影響的行: 0

時間: 0.000s

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999900 AND id < 8999999

受影響的行: 0

時間: 0.001s

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999000 AND id < 8999999

受影響的行: 0

時間: 0.003s

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8990000 AND id < 8999999

受影響的行: 0

時間: 0.022s

通過不斷加大區間來看，查詢時間跟查詢的數據量成相對的正比增長，同時使用到了主鍵索引。

相對于MyISAM?表來說，主鍵區間查詢的耗時小很多很多！看來只能用底層的B+樹的實現不一樣來解釋了！

MyISAM?的B+樹子節點的葉子節點數據域，存儲的是數據在MYD文件中的數據地址。

InnoDB? 的B+樹子節點的葉子節點數據域，存儲的是整行數據記錄，這個節省了一次硬盤IO操作，應該是這個特點導致了主鍵區間查詢比MyISAM?快的原因。

10. 字符串區間查詢

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

受影響的行: 0

時間: 12.506s

未使用索引和聚合索引，進行了全表掃描。

縮小區間在查詢

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_900000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

受影響的行: 0

時間: 12.213s

[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_1000000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

受影響的行: 0

時間: 19.793s

11.最左原則

1].?新建?A、B、C?聚合索引

[SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_email_password(username,email,password)

受影響的行: 0

時間: 588.579s

對比MyISAM?表來說，建立該索引的時間是其的1/6之一。建立索引的時間相對可觀。磁盤占用來說InnoDB總量更小。

2]. SQL?測試

和MyISAM?表對比，竟然沒使用到全表掃描，而且使用到了聚合索引。

覆蓋了 A、B、C?索引：

該語句使用了覆蓋索引，WHERE?語句的先后順序并不影響。MySQL會對SQL進行查詢優化，最終命中ABC索引。

命中了?A、B、C?索引中的 AB組合，查詢耗時很短：

沒有命中到?A、B、C?索引最左原則，竟然不是全表掃描，而是使用了索引。

和MyISAM?表對比，MyISAM?表是全表掃描，而InnoDB卻是使用到了索引。

六、總結

兩大引擎MyISAM、InnoDB分析：

背景：

數據記錄：10024725行

表索引：? 主鍵、A、AB、ABC

相同點：

1.都是B+樹的底層實現。

2.WHERE條件都符合索引最左匹配原則。

不同點：

1.MyISAM的存儲文件有三個，frm、MYD、MYI 文件；InnoDB的存儲文件就兩個，frm、ibd文件。總文件大小InnoDB引擎占用空間更小。

2.InnoDB的存儲文件本身就是索引構成，建立新索引的時間比MyISAM快。

3.MyISAM比InnoDB查詢速度快，插入速度也快。

4.主鍵區間查詢，InnoDB查詢更快。字符串區間查詢，MyISAM相對更快。

5.有A、AB、ABC索引的情況下，A OR B 查詢，InnoDB查詢性能比MyISAM慢。不建議使用OR 條件進行查詢。

6.InnoDB表沒有命中到?A、B、C?索引最左原則時，BC組合查詢命中了索引，但還是完全掃描，比全表掃描快些。MyISAM是全表掃描。

開篇也說過軟件層面的優化一是合理加索引；二是優化執行慢的sql。

此二者相輔相成，缺一不可，如果加了索引，還是查詢很慢，這時候就要考慮是sql的問題了，優化sql。

實際生產中的sql往往比較復雜，如果數據量過了百萬，加了索引后效果還是不理想，使用集群、垂直或水平拆分。

總結

以上是生活随笔為你收集整理的mysql数值比中文检索快_MySQL 千万 级数据量根据（索引）优化 查询 速度的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。