一、定義

• MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
  可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。
• 索引的目的在于提高查詢效率，可以類比字典，
  如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要a----z，如果我想找到Java開頭的單詞呢？或者Oracle開頭的單詞呢？
• 可以簡單理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”。
• 在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。下圖就是一種可能的索引方式示例：

左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址
為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄。

• 一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。

• 通過索引列對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗

• 思考：delete操作后，索引怎么辦？
  delete操作實(shí)際是將數(shù)據(jù)激活標(biāo)志位設(shè)為非激活狀態(tài)，邏輯上不存在，物理上還存在，目的：1.為了保存數(shù)據(jù)狀態(tài)，用來做數(shù)據(jù)分析；2.為了索引。
  所以頻繁刪除、更新的數(shù)據(jù)不適合建索引。

• 我們平常說的索引，如果沒有特別指明，都是B樹（多路搜索樹，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是B+樹索引，統(tǒng)稱索引。當(dāng)然除了B+樹這種類型的索引之外，還有哈希索引

  二、優(yōu)缺點(diǎn)

1.優(yōu)點(diǎn)：

• 類似大學(xué)圖書館建書目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本
• 通過索引列對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗

2.缺點(diǎn)：

• 雖然索引大大提高了查詢速度，同時(shí)卻會降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息。
• 實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的.

三、mysql索引分類

1.單值索引
即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引
語法:

隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);
  
單獨(dú)建單值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
刪除索引：
DROP INDEX idx_customer_name  on customer;

2.唯一索引
索引列的值必須唯一，但允許有空值
語法：

隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
  
單獨(dú)建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
刪除索引：
DROP INDEX idx_customer_no on customer ;

3.主鍵索引
設(shè)定為主鍵后數(shù)據(jù)庫會自動建立索引，innodb為聚簇索引

隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 單獨(dú)建主鍵索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
刪除建主鍵索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主鍵索引：
必須先刪除掉(drop)原索引，再新建(add)索引

4.復(fù)合索引
即一個(gè)索引包含多個(gè)列

語法：

 隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
單獨(dú)建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
刪除索引：
DROP INDEX idx_no_name  on customer ;

5. 增/刪/查

創(chuàng)建
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 
alter mytable add [unique] index [indexname] on (column))

刪除
DROP INDEX [indexName] ON mytable; 

查看
SHOW INDEX FROM table_name\G


使用ALTER命令
有四種方式來添加數(shù)據(jù)表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。主鍵索引
 
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會出現(xiàn)多次）。唯一索引
 
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現(xiàn)多次。
 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為 FULLTEXT ，用于全文索引。
 

四. Mysql索引結(jié)構(gòu)

Mysql索引結(jié)構(gòu)有BTree索引，Hash索引，full-text全文索引，R-Tree索引，這里主要研究BTree索引。

BTree索引結(jié)構(gòu)圖解

【初始化介紹】
一顆b樹，淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），
如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，
P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。
真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非葉子節(jié)點(diǎn)不存儲真實(shí)的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。

【查找過程】
如果要查找數(shù)據(jù)項(xiàng)29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。

真實(shí)的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

五、哪些情況適合建索引

1.適合建立索引

• 主鍵自動建立唯一索引
• 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引（如銀行卡號，電信部門手機(jī)號）
• 查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引（如：員工表和部門表id）
• 單鍵/組合索引的選擇問題， 組合索引性價(jià)比更高（在高并發(fā)下傾向于創(chuàng)建組合索引）
• 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度（排序字段順序和索引建立的順序一致）
• 查詢中統(tǒng)計(jì)或者分組字段

2.不適合建立索引

• 表記錄太少

• 經(jīng)常增刪改的表，提高了查詢速度，同時(shí)會降低更新表的速度，如對表進(jìn)行insert，update和delete，因?yàn)槊看胃虏粏螁问歉铝擞涗?，還會更新索引保存一下索引文件。

• 數(shù)據(jù)重復(fù)且分布平均的表字段（如國籍）

  假如有一個(gè)表10萬行記錄，有一個(gè)字段A只有True和False兩種值，且每個(gè)值得分布概率大約為50%，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度。
  索引的選擇性是指索引列中不同值得數(shù)目與表中記錄數(shù)得比。如果一個(gè)表中有2000條記錄，表索引列有1980個(gè)不同的值，那么這個(gè)索引的選擇性就是1980/2000=0.99。一個(gè)索引的選擇性越接近于1，這個(gè)索引的效率就越高。

• where條件里用不到的字段不創(chuàng)建索引

六、優(yōu)化性能分析

1.Mysql Query Optimizer（自帶的優(yōu)化器）

• Mysql中有專門負(fù)責(zé)優(yōu)化select語句的優(yōu)化器模塊，主要功能：通過計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息，為客戶端請求的Query提供它認(rèn)為最優(yōu)的執(zhí)行計(jì)劃（它認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式，但不見得是DBA認(rèn)為最優(yōu)的）

• 當(dāng)客戶端想Mysql請求一條Query，命令解析器模塊完成請求分類，區(qū)別出是select并轉(zhuǎn)發(fā)給Mysql Query Optimizer時(shí)，Mysql Query Optimizer首先會對整條Query進(jìn)行優(yōu)化，處理掉一些常量表達(dá)式的預(yù)算，直接換算成常量值。并對Query中的查詢條件進(jìn)行簡化和轉(zhuǎn)換，如去掉一些無用或者顯而易見的條件、結(jié)構(gòu)調(diào)整等。然后分析Query中的Hint信息（如果有），看顯示Hint信息是否可以完全確定Query的執(zhí)行計(jì)劃。如果沒有Hint或者Hint信息還不足以完全確定執(zhí)行計(jì)劃，則會讀取涉及對象的統(tǒng)計(jì)信息，根據(jù)Query進(jìn)行寫相應(yīng)的計(jì)算分析，然后再得出最后的執(zhí)行計(jì)劃。

2.Mysql 常見瓶頸

• cpu在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)的時(shí)候
• IO：磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候
• 服務(wù)器硬件性能瓶頸：top, free, iostat 和vmstat來查看系統(tǒng)的性能狀態(tài)

3.Explain執(zhí)行計(jì)劃

1.定義
使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是
如何處理你的SQL語句的。分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸
官網(wǎng)介紹： http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

2.explain能干嘛

• 表的讀取順序
• 哪些索引可以使用
• 數(shù)據(jù)讀取操作的操作類型
• 哪些索引被實(shí)際使用
• 表之間的引用
• 每張表有多少行被優(yōu)化器查詢

3.explain 的使用
explain+select語句

mysql> explain select * from customer;
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | customer | index | NULL          | customer_no | 406     | NULL |    1 | Using index |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+

建表測試：

 CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 
 
  INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
 
  INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
  
  INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
    
  INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

字段解釋：
1）id

• id相同，執(zhí)行順序由上至下

  
  
  id相同

• id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行

  
  
  id不同

• id相同不同，同時(shí)存在

id不同相同

id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行
衍生 = DERIVED, 衍生虛表s3。
<derived2>的2表示id的那個(gè)2.

id號每個(gè)號碼，表示一趟獨(dú)立的查詢。一個(gè)sql 的查詢趟數(shù)越少越好。

2)select_type

select_type種類

查詢的類型，主要是用于區(qū)別 普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢

• SIMPLE
  簡單的 select 查詢,查詢中不包含子查詢或者UNION

  
  
  simple

• PRIMARY
  查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為Primary

  
  
  primary
  
  

  衍生表a是最外層查詢，標(biāo)記為primary。

• DERIVED
  在FROM列表中包含的子查詢被標(biāo)記為DERIVED(衍生) MySQL會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時(shí)表里。

  
  
  derived

• SUBQUERY
  在SELECT或WHERE列表中包含了子查詢

  
  
  subquery

• DEPENDENT SUBQUERY
  在SELECT或WHERE列表中包含了子查詢,子查詢基于外層

  
  
  dependent subquery

• UNCACHEABLE SUBQUREY
  DEPENDENT SUBQUERY查詢不同于UNCACHEABLE SUBQUREY查詢。對于DEPENDENT SUBQUERY查詢，對于來自其外部上下文的變量的每組不同值，子查詢只重新計(jì)算一次。對于UNCACHEABLE SUBQUREY，將對外部上下文的每一行重新評估子查詢。

uncacheable subquery

• UNION
  若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION； 若UNION包含在FROM子句的子查詢中,外層SELECT將被標(biāo)記為：DERIVED

  
  
  union

• UNION RESULT
  從UNION表獲取結(jié)果的SELECT

union result

3）table
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

4） type

type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從最好到最壞依次是： 
 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > 
unique_subquery > index_subquery > range > index > ALL 
 
工作中常見：
system>const>eq_ref>ref>range>index>ALL
 
 
一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

顯示查詢使用了何種類型， 從最好到最差依次是： system>const>eq_ref>ref>range>index>ALL

• system
  表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時(shí)不會出現(xiàn)，這個(gè)也可以忽略不計(jì)

• const （常量）
  表示通過索引一次就找到了,const用于比較primary key或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快 如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量

  
  
  const

• eq_ref
  唯一性索引掃描，對于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描

  
  
  eq_ref
  
  

  t1加載過來進(jìn)行全表掃描，t2中只有一條記錄匹配。（ceo)

• ref
  非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行. 本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而， 它可能會找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體

  
  

ref

• range
  只檢索給定范圍的行,使用一個(gè)索引來選擇行。key 列顯示使用了哪個(gè)索引 一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢 這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束語另一點(diǎn)，不用掃描全部索引。

  
  

range

• index
  出現(xiàn)index是sql使用了索引但是沒用通過索引進(jìn)行過濾，一般是使用了覆蓋索引或者是利用索引進(jìn)行了排序分組

  
  
  index

• all
  Full Table Scan，將遍歷全表以找到匹配的行

• index_merge
  在查詢過程中需要多個(gè)索引組合使用，通常出現(xiàn)在有 or 的關(guān)鍵字的sql中

  
  
  index_merge

• ref_or_null
  對于某個(gè)字段既需要關(guān)聯(lián)條件，也需要null值得情況下。查詢優(yōu)化器會選擇用ref_or_null連接查詢。

  
  
  ref_or_null

• index_subquery
  利用索引來關(guān)聯(lián)子查詢，不再全表掃描。

  
  

index_subquery

• unique_subquery
  該聯(lián)接類型類似于index_subquery。 子查詢中的唯一索引

unique_subquery

備注：一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

5. possible_keys
   顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。 查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用.

6)key
實(shí)際使用的索引。如果為NULL，則沒有使用索引
查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊

覆蓋索引

possible_keys理論上用到了，key實(shí)際上用到了。

• 理論上用到了，實(shí)際上沒有用到，索引失效
• 理論上沒有用到，實(shí)際上用到了，覆蓋索引
• 理論上用到了，實(shí)際上也用到了

7）key_len
表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度。 在不損失精確性的情況下，長度越短越好。
key_len顯示的值為索引
key_len字段能夠幫你檢查是否充分的利用上了索引

key_len

是如何計(jì)算的呢？
1 、先看索引上字段的類型+長度比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char這種字符串字段，視字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，
3 、varchar這種動態(tài)字符串要加2個(gè)字節(jié)
4、 允許為空的字段要加1個(gè)字節(jié)

emp

計(jì)算

8）ref
顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)。哪些列或常量被用于查找索引列上的值

REF

9）rows
rows列表示Mysql認(rèn)為執(zhí)行查詢必須檢查的行數(shù)。對于InnoDB表，這個(gè)數(shù)字只是一個(gè)估計(jì)值，可能并不總是準(zhǔn)確的。
越少越好

10. extra

包含不適合在其他列中顯示但十分重要的額外信息

• using filesort
  出現(xiàn)filesort的情況

  
  
  filesort

優(yōu)化后，不再出現(xiàn)filesort的情況：

filesort優(yōu)化后

• using temporary
  優(yōu)化前存在 using temporary 和 using filesort

優(yōu)化前存在 using temporary 和 using filesort

優(yōu)化前存在 using temporary 和 using filesort 不在，性能發(fā)生明顯變化：

優(yōu)化后

使了用臨時(shí)表保存中間結(jié)果,MySQL在對查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于排序 order by 和分組查詢 group by。

• using index
  表示相應(yīng)的select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數(shù)據(jù)行，效率不錯(cuò)！ 如果同時(shí)出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找; 如果沒有同時(shí)出現(xiàn)using where，表明索引只是用來讀取數(shù)據(jù)而非利用索引執(zhí)行查找。
  利用索引進(jìn)行了排序或分組

• using where
  表明使用了where過濾

• using join buffer

  
  
  using join buffer

使用了連接緩存：

• impossible where
  where子句的值總是false，不能用來獲取任何元組

  
  
  impossible where

• select tables optimized away
  在沒有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者 對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算， 查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化。

在Innodb中

在innodb中

在Myisam中

在Myisam中