目錄
- 什么是索引?
- 索引類型
- 多列索引
- 使用最優(yōu)索引
總結(jié)
附錄
1. 什么是索引官还？
索引是一種通過(guò)避免查詢時(shí)全表掃描實(shí)現(xiàn)快速得到查詢結(jié)果而建立的數(shù)據(jù)結(jié)構(gòu); 以下這個(gè)例子很好的說(shuō)明了索引的一種實(shí)現(xiàn)以及它如何提升我們的查詢效率遍膜。
假設(shè)數(shù)據(jù)庫(kù)中一個(gè)表有10^6條記錄灼擂，DBMS的頁(yè)面大小為4K，并存儲(chǔ)100條記錄。
如果沒(méi)有索引，查詢將對(duì)整個(gè)表進(jìn)行掃描，最壞的情況下犬庇，如果所有數(shù)據(jù)頁(yè)都不在內(nèi)存，需要讀取10^4個(gè)頁(yè)面侨嘀，如果這10^4個(gè)頁(yè)面在磁盤上隨機(jī)分布臭挽，需要進(jìn)行10^4次I/O，假設(shè)磁盤每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間)咬腕，則總共需要100s(但實(shí)際上要好很多很多)欢峰。
如果對(duì)之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(10^6)=3次頁(yè)面讀取涨共，最壞情況下耗時(shí)30ms纽帖。^[1]
這就是索引帶來(lái)的效果，很多時(shí)候举反，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時(shí)懊直，應(yīng)該想想是否可以建索引。
2. 索引類型
MySQL中索引分為:
-
PRIMARY KEY直接通過(guò)設(shè)置主鍵也就建立了索引; -
UNIQUE一般主要用于保證數(shù)據(jù)的唯一性; -
INDEX普通索引也是最常使用的索引類型; -
FULLTEXT主要可用于列類型為(CHAR,VARCHAR,TEXT)的快速匹配查詢^[3]火鼻。
注: FULLTEXT索引僅支持MyISAM引擎, InnoDB引擎需要版本>=MySQL5.6才支持!
在WHERE語(yǔ)句使用=,>,<,BETWEEN,IN都可使用索引快速查找到特定的某條或某些記錄室囊。
3. 多列索引
MySQL中也可根據(jù)多個(gè)列創(chuàng)建索引; 例如你可以有基于三個(gè)列創(chuàng)建一個(gè)多列索引索引INDEX(col1, col2, col3), 那么可用的索引有INDEX(col1), INDEX(col1, col2)以及INDEX(col1, col2, col3)雕崩。^[4]
以下查詢是會(huì)使用索引:
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2 AND col3=val3;
以下查詢則不會(huì)使用索引
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
4. 使用最優(yōu)索引
當(dāng)表存在多個(gè)索引, MySQL只會(huì)選擇MYSQL QUERY Optimizer認(rèn)為最有的一個(gè)索引進(jìn)行查詢而放棄其他索引, 絕大多數(shù)情況下MySQL都可以自動(dòng)選擇到最優(yōu)的索引。
索引選擇策略:^([2])
-
WHRER之后的字段會(huì)被納入索引候選名單; - 存在多個(gè)索引時(shí), 優(yōu)先使用蓋索引鍵值最短的索引;
- 存在多列索引時(shí), 會(huì)使用任何最左前綴匹配的索引用于查詢;
- 根據(jù)
INDEX HINT優(yōu)先/強(qiáng)制使用某些索引^([5])融撞。
總結(jié)
-
以下情況可考慮是否索引存在問(wèn)題:
- IOPS居高不下
- CPU利用率居高不下
- SQL執(zhí)行緩慢
定期觀察SQL執(zhí)行情況, 如果有執(zhí)行時(shí)間過(guò)長(zhǎng)的SQL, 應(yīng)該EXPLAIN^([6])看看是否存在全表掃描或者過(guò)半記錄掃描盼铁。
如果
MYSQL QUERY Optimizer沒(méi)有選擇最優(yōu)的索引, 則通過(guò)INDEX HINT主動(dòng)指定當(dāng)前SQL使用的索引。
附錄
[1] 理解MySQL - 索引與優(yōu)化
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
[2] How MySQL Uses Indexes
https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
[3] InnoDB FULLTEXT Indexes
https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
[4] Multiple-Column Indexes
https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html
[5] Index Hints
https://dev.mysql.com/doc/refman/5.6/en/index-hints.html
[6] Optimizing Queries with EXPLAIN
https://dev.mysql.com/doc/refman/5.6/en/using-explain.html
[7] MySQL 索引選擇原則
http://blog.chinaunix.net/uid-26896862-id-3328675.html
