當(dāng)sql語句非常復(fù)雜的時(shí)候，會(huì)有一個(gè)id序號的排列，根據(jù)序號的排列能顯示出來哪個(gè)子查詢或者子句優(yōu)先執(zhí)行，哪個(gè)字句后執(zhí)行，僅此而已，有時(shí)候需要看，有時(shí)候不需要看，她不是一個(gè)關(guān)鍵信息

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

sql語句執(zhí)行的表的名稱

很重要，表示查詢對應(yīng)的類型，mysql默認(rèn)的是ALL

有這幾種

system

const

ref

range

index

all

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

有的sql語句非常簡單，sql語句越簡單，優(yōu)化的程度就越低

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

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

過濾的行數(shù)，只是預(yù)估值，不是精確值

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

當(dāng)出現(xiàn)的是using index：表示使用了索引覆蓋

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

using filesort表示使用了臨時(shí)空間進(jìn)行排序，沒有使用索引進(jìn)行排序

2、索引介紹

2.1、什么是索引？

索引是數(shù)據(jù)結(jié)構(gòu)，可以高效地獲取數(shù)據(jù)

索引存儲(chǔ)在文件系統(tǒng)中

索引的文件存儲(chǔ)形式與存儲(chǔ)引擎有關(guān)

索引文件結(jié)構(gòu)：

hash

二叉樹

B樹

B+樹 （MySql索引文件結(jié)構(gòu)）

2.2、索引分類

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

一個(gè)主鍵并非一定只有一個(gè)列，也可以是多個(gè)列組成的聯(lián)合主鍵

MySql會(huì)自動(dòng)為主鍵創(chuàng)建索引

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

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

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

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

又稱：復(fù)合索引、聯(lián)合索引

3、索引 增刪查改

3.1、增加（創(chuàng)建）

推薦

適用于表創(chuàng)建完畢之后再添加

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

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

 -- 索引名，可要可不要；如果不要，當(dāng)前的索引名就是該字段名 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`)

適用于表創(chuàng)建完畢之后再添加

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, -- 注意，下面創(chuàng)建了主鍵索引，這里就不用創(chuàng)建了    `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='后臺(tái)用戶表';

3.2、刪除

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

3.3、查看

 show index from `table_name`;

3.4、更改

刪掉重建一個(gè)既可

3.5、創(chuàng)建索引的技巧

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

4、存儲(chǔ)引擎

MyIsam、InnoDB、Memory

Memory 底層數(shù)據(jù)結(jié)構(gòu)為哈希表

聚簇索引數(shù)據(jù)跟索引存儲(chǔ)在一起非聚簇索引數(shù)據(jù)跟索引不存儲(chǔ)在一起

5、索引 技術(shù)點(diǎn)

索引系統(tǒng)設(shè)計(jì)要點(diǎn)

1）索引應(yīng)該存哪些信息

2）索引和數(shù)據(jù)存儲(chǔ)位置

對于InnoDB

索引和實(shí)際的數(shù)據(jù)都是存儲(chǔ)在磁盤上的，只不過在進(jìn)行數(shù)據(jù)讀取的時(shí)候會(huì)優(yōu)先把索引加載到內(nèi)存中

存儲(chǔ)引擎：不同的數(shù)據(jù)文件在磁盤中有不同的組織形式

3）索引存儲(chǔ)什么格式的數(shù)據(jù)？

K-V格式(鍵-值對)

類似于查字典，根據(jù)頁數(shù)定位要查找的內(nèi)容

4）選擇合理的數(shù)據(jù)結(jié)構(gòu)進(jìn)行存儲(chǔ)

為什么是B+數(shù)（為什么不是B樹或者h(yuǎn)ash表）

當(dāng)表非常大的時(shí)候，索引會(huì)不會(huì)一起變大？ 因?yàn)橥砝锎鏀?shù)據(jù)的時(shí)候，是沒法判斷這個(gè)表能夠存多少數(shù)據(jù)的，表中數(shù)據(jù)量在增大的時(shí)候，索引也在增大 索引在變大的過程中，沒辦法直接加載到內(nèi)存怎么辦？ 可能內(nèi)存只有8G，但是mysql數(shù)據(jù)的索引達(dá)到了16G，則可以分塊讀取，1G 1G地讀，分而治之 盡可能多地提高IO效率 1：減少IO量 2：減少IO次數(shù)

6、操作系統(tǒng)基礎(chǔ)知識(shí)

6.1、局部性原理

之前被訪問過的數(shù)據(jù)很有可能再次被訪問

數(shù)據(jù)和程序都有聚集成群的傾向

6.2、磁盤預(yù)讀

內(nèi)存跟磁盤再進(jìn)行交互的時(shí)候有一個(gè)最小的邏輯單位，這個(gè)單位稱之為頁，或者datapage，一般是4kb或者8kb，由操作系統(tǒng)決定，我們在進(jìn)行數(shù)據(jù)讀取的時(shí)候，一般是讀取頁的整數(shù)倍，也就是4k，8k，16k；Innodb存儲(chǔ)引擎在進(jìn)行數(shù)據(jù)讀取的時(shí)候讀取的是16kb的數(shù)據(jù)

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

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

7.1、hash表

缺點(diǎn)：

hash存儲(chǔ)需要將所有的數(shù)據(jù)文件添加到內(nèi)存，浪費(fèi)內(nèi)存空間

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

簡要說下，類似于數(shù)據(jù)結(jié)構(gòu)中簡單實(shí)現(xiàn)的HASH表（散列表）一樣，當(dāng)我們在mysql中用哈希索引時(shí)，也是對索引列計(jì)算一個(gè)散列值（類似md5、sha1、crc32），然后對這個(gè)散列值以順序（默認(rèn)升序）排列，同時(shí)記錄該散列值對應(yīng)數(shù)據(jù)表中某行的指針，當(dāng)然這只是簡略模擬圖

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

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

7.2、二叉樹

缺點(diǎn)：

會(huì)因?yàn)闃涞纳疃冗^深而造成IO次數(shù)變多，影響讀取效率

7.3、B樹

缺點(diǎn)：

會(huì)因?yàn)闃涞纳疃冗^深而造成IO次數(shù)變多，影響讀取效率

7.4、B+樹

B+Tree每個(gè)節(jié)點(diǎn)可以包含更多的節(jié)點(diǎn)，這個(gè)做的原因有兩個(gè)：

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

2、將數(shù)據(jù)范圍變?yōu)槎鄠€(gè)區(qū)間，區(qū)間越多，數(shù)據(jù)檢索越快

非葉子節(jié)點(diǎn)存儲(chǔ)key，葉子節(jié)點(diǎn)存儲(chǔ)key和數(shù)據(jù)

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

在B+Tree上有兩個(gè)頭指針，一個(gè)指向根節(jié)點(diǎn)，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)，而且所有葉子節(jié)點(diǎn)(即數(shù)據(jù)節(jié)點(diǎn))之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。因此可以對B+Tree進(jìn)行兩種查找運(yùn)算：

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

2、從根節(jié)點(diǎn)開始，進(jìn)行隨機(jī)查找

一般情況下3-4層的B+樹足以支撐千萬級的數(shù)據(jù)量存儲(chǔ)

Innodb是通過B+Tree結(jié)構(gòu)對主鍵創(chuàng)建索引，然后葉子節(jié)點(diǎn)中存儲(chǔ)記錄，如果沒有主鍵，選擇唯一鍵，如果沒有唯一鍵，選擇6字節(jié)的row_id來進(jìn)行存儲(chǔ)

如果創(chuàng)建的索引是其她字段(不是主鍵)，那么在葉子節(jié)點(diǎn)中存儲(chǔ)的是該記錄的主鍵(不是數(shù)據(jù))，然后再通過主鍵索引查找對應(yīng)的記錄

MyIsam 葉子節(jié)點(diǎn)中只存儲(chǔ)地址，不存具體記錄數(shù)據(jù)，通過地址再查找數(shù)據(jù)

8、索引優(yōu)化

8.1、常見名詞

案例表：

1）回表

如果創(chuàng)建的索引是其她字段(普通索引，不是主鍵)，那么在葉子節(jié)點(diǎn)中存儲(chǔ)的是該記錄的主鍵(不是數(shù)據(jù))，然后再通過主鍵去 主鍵索引表 查找對應(yīng)的記錄

經(jīng)過了兩個(gè)表的查詢：

普通索引表：查到主鍵

主鍵索引表：查詢記錄數(shù)據(jù)

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

2）索引覆蓋

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

3）最左匹配

-- id主鍵 (name,age)組合索引/聯(lián)合索引/復(fù)合索引
-- 會(huì)用組合索引
select * from user where name = '張三' and age = '22';
-- 會(huì)用組合索引
select * from user where name = '張三';
-- 不會(huì)用組合索引，因?yàn)闆]有 name
select * from user where age = '22';
-- 會(huì)用組合索引，因?yàn)?age 和 name 都有，優(yōu)化器會(huì)調(diào)整 age 和 name 的順序
select * from user where age = '22' and name = '張三';

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

4）索引下推

把原來在server層進(jìn)行的條件過濾下推到存儲(chǔ)引擎層，索引下推是默認(rèn)開啟的

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

先根據(jù) name 從存儲(chǔ)引擎中拉取數(shù)據(jù)到 server 層，然后在 server 層中對 age 進(jìn)行數(shù)據(jù)過濾

有索引下推后

根據(jù) name 和 age，直接在存儲(chǔ)引擎中做數(shù)據(jù)過濾，把結(jié)果返給 server 層

可以減少返給 server 層的數(shù)據(jù)量

5）前綴索引

如果索引列長度過長，這種列索引時(shí)將會(huì)產(chǎn)生很大的索引文件，不便于操作，可以使用前綴索引方式進(jìn)行索引；前綴索引應(yīng)該控制在一個(gè)合適的點(diǎn)，控制在0.31黃金值即可（大于這個(gè)值就可以創(chuàng)建）。

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

8.2、優(yōu)化小細(xì)節(jié)