當sql語句非常復雜的時候，會有一個id序號的排列，根據序號的排列能顯示出來哪個子查詢或者子句優先執行，哪個字句后執行，僅此而已，有時候需要看，有時候不需要看，她不是一個關鍵信息

查詢的類型（簡單查詢、聯合查詢、子查詢），一般沒什么用

sql語句執行的表的名稱

很重要，表示查詢對應的類型，mysql默認的是ALL

有這幾種

system

const

ref

range

index

all

從前往后，效率依次降低，即system的效率是最高的，all要進行全表掃描，效率低，所以我們最少要保證type在range這個級別（通過加索引、調整當前子句…），達到ref更好但是某些情況是沒法優化的，優化知識為了在一定程度上解決問題，并不是一定有解。可以優化但并不是優化完一定有效果

有的sql語句非常簡單，sql語句越簡單，優化的程度就越低

可能用到的索引把可能用到的索引都列出來，意義不大

很重要，表示當前的sql語句中到底有沒有用到索引，這個值盡量不要為空

過濾的行數，只是預估值，不是精確值

比較重要，表示額外的信息

當出現的是using index：表示使用了索引覆蓋

using index condition表示使用了索引下推

using filesort表示使用了臨時空間進行排序，沒有使用索引進行排序

2、索引介紹

2.1、什么是索引？

索引是數據結構，可以高效地獲取數據

索引存儲在文件系統中

索引的文件存儲形式與存儲引擎有關

索引文件結構：

hash

二叉樹

B樹

B+樹 （MySql索引文件結構）

2.2、索引分類

主鍵是一種唯一性索引，她必須指定為PRIMARY KEY，不能為空，每個表只能有一個主鍵

一個主鍵并非一定只有一個列，也可以是多個列組成的聯合主鍵

MySql會自動為主鍵創建索引

索引列的所有值都只能出現一次，即必須唯一，值可以為空；一張表可以在不同的字段建多個唯一索引

基本的索引類型，值可以為空，沒有唯一性的限制

全文索引的索引類型為FULLTEXT。全文索引可以在varchar、char、text類型的列上創建

多列值組成一個索引，專門用于組合搜索

又稱：復合索引、聯合索引

3、索引 增刪查改

3.1、增加（創建）

推薦

適用于表創建完畢之后再添加

alter [???lt?r] 修改、更改

ALTER TABLE 表名 ADD 索引類型 (unique,primary key,fulltext,index) [索引名](字段名)

 -- 索引名，可要可不要；如果不要，當前的索引名就是該字段名 ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`)   ALTER TABLE `table_name` ADD UNIQUE (`column_list`)  ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`)  ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

適用于表創建完畢之后再添加

CREATE INDEX 可對表增加 普通索引 或 UNIQUE索引

 -- 只能添加  普通索引 或 UNIQUE索引 CREATE INDEX index_name ON table_name (column_list)  CREATE UNIQUE INDEX index_name ON table_name (column_list)

不推薦

 CREATE TABLE `test1` (    `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面創建了主鍵索引，這里就不用創建了    `username` varchar(64) NOT NULL COMMENT '用戶名',    `nickname` varchar(50) NOT NULL COMMENT '昵稱/姓名',    `intro` text,    PRIMARY KEY (`id`),     UNIQUE KEY `unique1` (`username`), -- 索引名稱，可要可不要，不要就是和列名一樣    KEY `index1` (`nickname`),    FULLTEXT KEY `intro` (`intro`)  ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后臺用戶表';

3.2、刪除

 DROP INDEX `index_name` ON `talbe_name`   ALTER TABLE `table_name` DROP INDEX `index_name`  -- 這兩句都是等價的，都是刪除掉 table_name 中的索引 index_name  ALTER TABLE `table_name` DROP PRIMARY KEY -- 刪除主鍵索引，注意主鍵索引只能用這種方式刪除

3.3、查看

 show index from `table_name`;

3.4、更改

刪掉重建一個既可

3.5、創建索引的技巧

1. 維度高的列創建索引
2. 數據列中不重復值出現的個數，這個數量越高，維度就越高。
3. 如數據表中存在8行數據a，b，c，d，a，b，c，d這個表的維度為4。要為維度高的列創建索引，如性別和年齡，那年齡的維度就高于性別。性別這樣的列不適合創建索引，因為維度過低。對 where，on，group by，order by 中出現的列使用索引。對較小的數據列使用索引，這樣會使索引文件更小，同時內存中也可以裝載更多的索引鍵
4. 為較長的字符串使用前綴索引。
5. 不要過多創建索引，除了增加額外的磁盤空間外，對于DML(數據庫管理語言，如增、刪、改)操作的速度影響很大，因為其每增刪改一次就得重新建立索引。
6. 使用組合索引，可以減少文件索引大小，在使用時速度要優于多個單列索引
7. 更新頻繁，數據區分度不高的字段，不宜建立索引

4、存儲引擎

MyIsam、InnoDB、Memory

Memory 底層數據結構為哈希表

聚簇索引數據跟索引存儲在一起非聚簇索引數據跟索引不存儲在一起

5、索引 技術點

索引系統設計要點

1）索引應該存哪些信息

2）索引和數據存儲位置

對于InnoDB

索引和實際的數據都是存儲在磁盤上的，只不過在進行數據讀取的時候會優先把索引加載到內存中

存儲引擎：不同的數據文件在磁盤中有不同的組織形式

3）索引存儲什么格式的數據？

K-V格式(鍵-值對)

類似于查字典，根據頁數定位要查找的內容

4）選擇合理的數據結構進行存儲

為什么是B+數（為什么不是B樹或者hash表）

當表非常大的時候，索引會不會一起變大？ 因為往表里存數據的時候，是沒法判斷這個表能夠存多少數據的，表中數據量在增大的時候，索引也在增大 索引在變大的過程中，沒辦法直接加載到內存怎么辦？ 可能內存只有8G，但是mysql數據的索引達到了16G，則可以分塊讀取，1G 1G地讀，分而治之 盡可能多地提高IO效率 1：減少IO量 2：減少IO次數

6、操作系統基礎知識

6.1、局部性原理

之前被訪問過的數據很有可能再次被訪問

數據和程序都有聚集成群的傾向

6.2、磁盤預讀

內存跟磁盤再進行交互的時候有一個最小的邏輯單位，這個單位稱之為頁，或者datapage，一般是4kb或者8kb，由操作系統決定，我們在進行數據讀取的時候，一般是讀取頁的整數倍，也就是4k，8k，16k；Innodb存儲引擎在進行數據讀取的時候讀取的是16kb的數據

舉例：如下圖，我們可以看到，實際的文件大小是758字節，但是占用了4kb的大小（可以把磁盤看成一個一個的小格子，每一個格子都是4kb的大小，不管你有沒有占滿，都是4kb）

7、MySql 為什么選擇B+樹？

7.1、hash表

缺點：

hash存儲需要將所有的數據文件添加到內存，浪費內存空間

如果是等值查詢，hash很快；但實際工作中范圍(range)查找的更多，而不是等值查詢，所以hash就不合適了

簡要說下，類似于數據結構中簡單實現的HASH表（散列表）一樣，當我們在mysql中用哈希索引時，也是對索引列計算一個散列值（類似md5、sha1、crc32），然后對這個散列值以順序（默認升序）排列，同時記錄該散列值對應數據表中某行的指針，當然這只是簡略模擬圖

比如對姓名列建立hash索引，生成hash值按順序排列，但是順序排列的hash值并不對應表中記錄，從地址指針可反應出來，而且，hash索引可能建立在兩列或者更多列上，取得是多列數據后的hash值，她不存儲表中數據。她先計算列數據的hash值，與索引中的hash值比較，找到了然后比對列數據是否相等，可能涉及其他列條件，然后返回數據。hash當然會有沖突，即碰撞，除非有很多沖突，一般hash索引效率很高，否則hash維護成本較高，因此哈希索引通常用在選擇性較高的列上面。哈希索引的結構決定了她的特點：

1. hash索引只是hash值順序排列，跟表數據沒有關系，無法應用于order by；
2. hash索引是對她的所有列計算哈希值，因此在查詢時，必須帶上所有列，比如有(a, b)哈希索引，查詢時必須 where a = 1 and b = 2，少任何一個不行；
3. hash索引只能用于比較查詢 = 或 IN，其他范圍查詢無效，本質還是因不存儲表數據；一旦出現碰撞，hash索引必須遍歷所有的hash值，將地址所指向數據一一比較，直到找到所有符合條件的行。

7.2、二叉樹

缺點：

會因為樹的深度過深而造成IO次數變多，影響讀取效率

7.3、B樹

缺點：

會因為樹的深度過深而造成IO次數變多，影響讀取效率

7.4、B+樹

B+Tree每個節點可以包含更多的節點，這個做的原因有兩個：

1、為了降低樹的高度，減少IO次數

2、將數據范圍變為多個區間，區間越多，數據檢索越快

非葉子節點存儲key，葉子節點存儲key和數據

葉子節點兩兩指針相互連接(符合磁盤的預讀特性)，順序查詢性能更高

在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點(即數據節點)之間是一種鏈式環結構。因此可以對B+Tree進行兩種查找運算：

1、對于主鍵的范圍查找和分頁查找

2、從根節點開始，進行隨機查找

一般情況下3-4層的B+樹足以支撐千萬級的數據量存儲

Innodb是通過B+Tree結構對主鍵創建索引，然后葉子節點中存儲記錄，如果沒有主鍵，選擇唯一鍵，如果沒有唯一鍵，選擇6字節的row_id來進行存儲

如果創建的索引是其她字段(不是主鍵)，那么在葉子節點中存儲的是該記錄的主鍵(不是數據)，然后再通過主鍵索引查找對應的記錄

MyIsam 葉子節點中只存儲地址，不存具體記錄數據，通過地址再查找數據

8、索引優化

8.1、常見名詞

案例表：

1）回表

如果創建的索引是其她字段(普通索引，不是主鍵)，那么在葉子節點中存儲的是該記錄的主鍵(不是數據)，然后再通過主鍵去 主鍵索引表 查找對應的記錄

經過了兩個表的查詢：

普通索引表：查到主鍵

主鍵索引表：查詢記錄數據

-- name是普通索引，在普通索引表中存儲的是主鍵id的值，即1
-- 要想得到記錄的值，需經過二次查詢
select * from table where name='張三'

2）索引覆蓋

-- 一次查詢就可以得到結果，不需要回表
select id from table where name='張三'根據name的值去name的B+樹檢索對應的記錄，能獲取到id的屬性值，索引的葉子結點中包含了查詢的所有列，此時不需要回表，這個額過程叫做索引覆蓋，會有using index的提示，推薦使用在某些場景中，可以考慮將要查詢的所有列都變成組合索引 ，此時會使用索引覆蓋（不會回表），加快查詢效率。

3）最左匹配

-- id主鍵 (name,age)組合索引/聯合索引/復合索引
-- 會用組合索引
select * from user where name = '張三' and age = '22';
-- 會用組合索引
select * from user where name = '張三';
-- 不會用組合索引，因為沒有 name
select * from user where age = '22';
-- 會用組合索引，因為 age 和 name 都有，優化器會調整 age 和 name 的順序
select * from user where age = '22' and name = '張三';

對于復合索引來說，不總是匹配所有字段列，但是可以匹配索引中靠左的列

4）索引下推

把原來在server層進行的條件過濾下推到存儲引擎層，索引下推是默認開啟的

select * from user where name = '張三' and age = '22';沒有索引下推前

先根據 name 從存儲引擎中拉取數據到 server 層，然后在 server 層中對 age 進行數據過濾

有索引下推后

根據 name 和 age，直接在存儲引擎中做數據過濾，把結果返給 server 層

可以減少返給 server 層的數據量

5）前綴索引

如果索引列長度過長，這種列索引時將會產生很大的索引文件，不便于操作，可以使用前綴索引方式進行索引；前綴索引應該控制在一個合適的點，控制在0.31黃金值即可（大于這個值就可以創建）。

 -- 這個值大于0.31就可以創建前綴索引，Distinct去重復 SELECt COUNT(DISTINCT(LEFt(title,10)))/COUNT(*) FROM Arctic;   -- 增加前綴索引SQL，將人名的索引建立在10，這樣可以減少索引文件大小，加快索引查詢速度 ALTER TABLE user ADD INDEX `uname`(title(10)); 

8.2、優化小細節