一巨双、定義

• MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構挨下。
  可以得到索引的本質：索引是數(shù)據(jù)結構泼疑。
• 索引的目的在于提高查詢效率汤纸，可以類比字典送火，
  如果要查“mysql”這個單詞俐载，我們肯定需要定位到m字母都许，然后從下往下找到y(tǒng)字母汗捡，再找到剩下的sql坪蚁。如果沒有索引鹰霍，那么你可能需要a----z蒙兰，如果我想找到Java開頭的單詞呢磷瘤？或者Oracle開頭的單詞呢？
• 可以簡單理解為“排好序的快速查找數(shù)據(jù)結構”癞己。
• 在數(shù)據(jù)之外膀斋，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結構，這些數(shù)據(jù)結構以某種方式引用（指向）數(shù)據(jù)痹雅，這樣就可以在這些數(shù)據(jù)結構上實現(xiàn)高級查找算法仰担。這種數(shù)據(jù)結構，就是索引。下圖就是一種可能的索引方式示例：

左邊是數(shù)據(jù)表摔蓝，一共有兩列七條記錄赂苗，最左邊的是數(shù)據(jù)記錄的物理地址
為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹贮尉，每個節(jié)點分別包含索引鍵值和一個指向對應數(shù)據(jù)記錄物理地址的指針拌滋，這樣就可以運用二叉查找在一定的復雜度內獲取到相應數(shù)據(jù)，從而快速的檢索出符合條件的記錄猜谚。

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

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

• 思考：delete操作后览芳，索引怎么辦斜姥？
  delete操作實際是將數(shù)據(jù)激活標志位設為非激活狀態(tài)，邏輯上不存在沧竟，物理上還存在铸敏，目的：1.為了保存數(shù)據(jù)狀態(tài)，用來做數(shù)據(jù)分析悟泵；2.為了索引杈笔。
  所以頻繁刪除、更新的數(shù)據(jù)不適合建索引魁袜。

• 我們平常說的索引桩撮，如果沒有特別指明，都是B樹（多路搜索樹峰弹，并不一定是二叉的）結構組織的索引店量。其中聚集索引、復合索引鞠呈、前綴索引融师、唯一索引默認都是B+樹索引，統(tǒng)稱索引蚁吝。當然除了B+樹這種類型的索引之外旱爆，還有哈希索引

  二、優(yōu)缺點

1.優(yōu)點：

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

2.缺點：

• 雖然索引大大提高了查詢速度房待，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE桑孩。因為更新表時拜鹤，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段流椒，都會調整因為更新所帶來的鍵值變化后的索引信息敏簿。
• 實際上索引也是一張表，該表保存了主鍵與索引字段宣虾，并指向實體表的記錄惯裕，所以索引列也是要占用空間的.

三、mysql索引分類

1.單值索引
即一個索引只包含單個列安岂，一個表可以有多個單列索引
語法:

隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);
  
單獨建單值索引：
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)
);
  
單獨建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
刪除索引：
DROP INDEX idx_customer_no on customer ;

3.主鍵索引
設定為主鍵后數(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) 
);
 
 單獨建主鍵索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
刪除建主鍵索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主鍵索引：
必須先刪除掉(drop)原索引域那，再新建(add)索引

4.復合索引
即一個索引包含多個列

語法：

 隨表一起建索引：
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)
);
 
單獨建索引：
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): 該語句添加一個主鍵，這意味著索引值必須是唯一的猜煮，且不能為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索引結構

Mysql索引結構有BTree索引，Hash索引搞挣，full-text全文索引带迟，R-Tree索引，這里主要研究BTree索引囱桨。

BTree索引結構圖解

【初始化介紹】
一顆b樹仓犬，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示）和指針（黃色所示）舍肠，
如磁盤塊1包含數(shù)據(jù)項17和35搀继，包含指針P1、P2翠语、P3叽躯，
P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊肌括，P3表示大于35的磁盤塊点骑。
真實的數(shù)據(jù)存在于葉子節(jié)點即3、5、9畔况、10鲸鹦、13、15跷跪、28馋嗜、29、36吵瞻、60葛菇、75、79橡羞、90眯停、99。
非葉子節(jié)點不存儲真實的數(shù)據(jù)卿泽，只存儲指引搜索方向的數(shù)據(jù)項莺债，如17、35并不真實存在于數(shù)據(jù)表中签夭。

【查找過程】
如果要查找數(shù)據(jù)項29齐邦，那么首先會把磁盤塊1由磁盤加載到內存，此時發(fā)生一次IO第租，在內存中用二分查找確定29在17和35之間措拇，鎖定磁盤塊1的P2指針，內存時間因為非常短（相比磁盤的IO）可以忽略不計慎宾，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存丐吓，發(fā)生第二次IO，29在26和30之間趟据，鎖定磁盤塊3的P2指針券犁，通過指針加載磁盤塊8到內存，發(fā)生第三次IO之宿，同時內存中做二分查找找到29族操，結束查詢，總計三次IO比被。

真實的情況是色难，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO等缀，性能提高將是巨大的枷莉，如果沒有索引，每個數(shù)據(jù)項都要發(fā)生一次IO尺迂，那么總共需要百萬次的IO笤妙，顯然成本非常非常高冒掌。

五、哪些情況適合建索引

1.適合建立索引

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

2.不適合建立索引

• 表記錄太少

• 經常增刪改的表铃诬，提高了查詢速度，同時會降低更新表的速度苍凛，如對表進行insert趣席，update和delete，因為每次更新不單單是更新了記錄醇蝴，還會更新索引保存一下索引文件宣肚。

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

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

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

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

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

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

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

2.Mysql 常見瓶頸

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

3.Explain執(zhí)行計劃

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

2.explain能干嘛

• 表的讀取順序
• 哪些索引可以使用
• 數(shù)據(jù)讀取操作的操作類型
• 哪些索引被實際使用
• 表之間的引用
• 每張表有多少行被優(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相同不同，同時存在

id不同相同

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

id號每個號碼户誓，表示一趟獨立的查詢。一個sql 的查詢趟數(shù)越少越好幕侠。

2)select_type

select_type種類

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

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

  
  
  simple

• PRIMARY
  查詢中若包含任何復雜的子部分悼潭，最外層查詢則被標記為Primary

  
  
  primary
  
  

  衍生表a是最外層查詢，標記為primary舞箍。

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

  
  
  derived

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

  
  
  subquery

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

  
  
  dependent subquery

• UNCACHEABLE SUBQUREY
  DEPENDENT SUBQUERY查詢不同于UNCACHEABLE SUBQUREY查詢。對于DEPENDENT SUBQUERY查詢疏橄，對于來自其外部上下文的變量的每組不同值占拍，子查詢只重新計算一次。對于UNCACHEABLE SUBQUREY捎迫，將對外部上下文的每一行重新評估子查詢晃酒。

uncacheable subquery

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

  
  
  union

• UNION RESULT
  從UNION表獲取結果的SELECT

union result

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

4） type

type顯示的是訪問類型掖疮，是較為重要的一個指標，結果值從最好到最壞依次是： 
 
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
 
 
一般來說颗祝，得保證查詢至少達到range級別浊闪，最好能達到ref恼布。

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

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

• const （常量）
  表示通過索引一次就找到了,const用于比較primary key或者unique索引盖腿。因為只匹配一行數(shù)據(jù)爽待，所以很快 如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量

  
  
  const

• eq_ref
  唯一性索引掃描翩腐，對于每個索引鍵鸟款，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描

  
  
  eq_ref
  
  

  t1加載過來進行全表掃描茂卦，t2中只有一條記錄匹配何什。（ceo)

• ref
  非唯一性索引掃描，返回匹配某個單獨值的所有行. 本質上也是一種索引訪問等龙，它返回所有匹配某個單獨值的行处渣，然而， 它可能會找到多個符合條件的行蛛砰，所以他應該屬于查找和掃描的混合體

  
  

ref

• range
  只檢索給定范圍的行,使用一個索引來選擇行罐栈。key 列顯示使用了哪個索引 一般就是在你的where語句中出現(xiàn)了between、<泥畅、>荠诬、in等的查詢 這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點涯捻，而結束語另一點浅妆，不用掃描全部索引。

  
  

range

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

  
  
  index

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

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

  
  
  index_merge

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

  
  
  ref_or_null

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

  
  

index_subquery

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

unique_subquery

備注：一般來說我注，得保證查詢至少達到range級別，最好能達到ref迟隅。

5. possible_keys
   顯示可能應用在這張表中的索引但骨，一個或多個励七。 查詢涉及到的字段上若存在索引，則該索引將被列出奔缠，但不一定被查詢實際使用.

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

覆蓋索引

possible_keys理論上用到了两波，key實際上用到了。

• 理論上用到了闷哆，實際上沒有用到腰奋，索引失效
• 理論上沒有用到，實際上用到了抱怔，覆蓋索引
• 理論上用到了劣坊，實際上也用到了

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

key_len

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

emp

計算

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

REF

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

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)化后

使了用臨時表保存中間結果,MySQL在對查詢結果排序時使用臨時表。常見于排序 order by 和分組查詢 group by邪财。

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

• 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í)行階段再進行計算赠橙， 查詢執(zhí)行計劃生成的階段即完成優(yōu)化耽装。

在Innodb中

在innodb中

在Myisam中

在Myisam中