MySQL索引及優(yōu)化的知識(shí)點(diǎn)有哪些

這篇文章主要介紹“MySQL索引及優(yōu)化的知識(shí)點(diǎn)有哪些”的相關(guān)知識(shí)，小編通過實(shí)際案例向大家展示操作過程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL索引及優(yōu)化的知識(shí)點(diǎn)有哪些”文章能幫助大家解決問題。

索引是什么？

• 索引是幫助MySQL進(jìn)行高效查詢的一種數(shù)據(jù)結(jié)構(gòu)。好比一本書的目錄，能加快查詢的速度

索引的結(jié)構(gòu)？

索引可以有B-Tree索引，Hash索引。索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的

InnoDB / MyISAM 僅支持 B-Tree索引

Memory/Heap 支持B-Tree索引和Hash索引

• B-Tree

  B-Tree是一種非常適合用于磁盤操作的數(shù)據(jù)結(jié)構(gòu)。它是一棵多路平衡查找樹。其高度一般在2-4，其非葉子節(jié)點(diǎn)，葉子節(jié)點(diǎn)，都會(huì)存儲(chǔ)數(shù)據(jù)。其所有的葉子節(jié)點(diǎn)，都在同一層。下圖是一顆B-Tree

•  B+ Tree：B+樹是在B-Tree基礎(chǔ)上的一種優(yōu)化。它和B樹的主要區(qū)別在于：B+樹的數(shù)據(jù)全部存儲(chǔ)在葉子節(jié)點(diǎn)中，且葉子節(jié)點(diǎn)被一個(gè)鏈表串了起來(lái)。下圖是一顆B+樹

InnoDB中一個(gè)頁(yè)的大小為16KB（一個(gè)頁(yè)即B+樹上的一個(gè)節(jié)點(diǎn)），若表的主鍵為INT，大小為4字節(jié)，那一個(gè)節(jié)點(diǎn)也能夠存儲(chǔ)4K個(gè)鍵值，假設(shè)指針和鍵值都占相同大小，那么高度為3的B+樹，第二層有2048個(gè)節(jié)點(diǎn)，第三層的葉子節(jié)點(diǎn)數(shù)為2048*2048 = 4194304，一個(gè)節(jié)點(diǎn)為16KB，則一共可容納67108864KB，即65536MB，即64G的數(shù)據(jù)。

由于葉子節(jié)點(diǎn)是被一個(gè)鏈表串起來(lái)的，所以若order by 索引列，則默認(rèn)已經(jīng)是排好序的，所以效率會(huì)很高。

• MyISAM索引
  MyISAM的索引和數(shù)據(jù)是分開存放的。在MyISAM的主鍵索引中，B+樹葉子節(jié)點(diǎn)里，存的是記錄的地址，故MyISAM通過索引查詢，需要經(jīng)過2次IO

MyISAM的輔助索引和主鍵索引一樣，唯一的區(qū)別是，輔助索引中的key可以重復(fù)，而主鍵索引的key不能重復(fù)

• InnoDB索引
  InnoDB的數(shù)據(jù)和索引是存放在一起的，又稱聚集索引。數(shù)據(jù)通過主鍵索引，存放在主鍵索引B+樹的葉子節(jié)點(diǎn)上。
  InnoDB主鍵索引，數(shù)據(jù)已經(jīng)包含在了葉子節(jié)點(diǎn)中，即索引和數(shù)據(jù)存放在一起，是為聚集索引。

 InnoDB的輔助索引，葉子節(jié)點(diǎn)中存的是主鍵值，而不是地址。走輔助索引，需要檢索2次。

InnoDB和MyISAM索引的區(qū)別：

• InnoDB使用聚集索引，其主鍵索引葉子節(jié)點(diǎn)中直接存儲(chǔ)了數(shù)據(jù)，而其輔助索引中葉子節(jié)點(diǎn)存的是主鍵的值

• MyISAM使用非聚集索引，數(shù)據(jù)和索引不在同一個(gè)文件中，其主鍵索引中葉子節(jié)點(diǎn)上存的是該行記錄所在的地址，其輔助索引中葉子節(jié)點(diǎn)上存的也是記錄所在的地址，只是輔助索引的key可以重復(fù)，而主鍵索引的key不能重復(fù)
   

問題：

• InnoDB為什么不要使用過長(zhǎng)的字段做主鍵？
  過長(zhǎng)的主鍵，會(huì)使得輔助索引所占空間變得很大

• 為什么推薦InnoDB使用自增主鍵？
  若使用自增主鍵，則每次插入新的記錄，就會(huì)順序的將新記錄添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，一頁(yè)寫滿了，才會(huì)進(jìn)行開辟新的一頁(yè)，這樣使得索引結(jié)構(gòu)很緊湊，且每次插入時(shí)不需要移動(dòng)已有數(shù)據(jù)，非常高效。而如果不使用自增主鍵，則每次插入新記錄時(shí)，都要選擇一個(gè)插入位置，并且可能需要移動(dòng)數(shù)據(jù)，使得效率不高，且索引結(jié)構(gòu)不緊湊

• 為什么要用B+樹，不用B樹

索引存在哪兒？

• 索引本身也比較大，一般會(huì)存儲(chǔ)在磁盤中，索引和數(shù)據(jù)可能是分開存放的（MyISAM的非聚集索引），也可能是一起存放的（InnoDB的聚集索引）

索引的優(yōu)缺點(diǎn)?

• 優(yōu)點(diǎn)

• 降低IO成本，提高數(shù)據(jù)查詢效率

• 降低排序成本（被索引的列會(huì)自動(dòng)排序，使用order by 效率會(huì)提高很多）

• 缺點(diǎn)

• 索引會(huì)額外占據(jù)存儲(chǔ)空間

• 索引會(huì)降低更新表數(shù)據(jù)的效率。進(jìn)行增刪改操作時(shí)，不僅要保存數(shù)據(jù)，還要更新對(duì)應(yīng)的索引

索引的分類

• 單列索引

• 主鍵索引

• 唯一索引

• 普通索引

• 組合索引

 索引使用

• 建立索引

 CREATE INDEX index_name ON table_name(col_name);
-- 或者
ALTER TABLE table_name ADD INDEX index_name(col_name)

• 刪除索引

DROP INDEX index_name ON table_name;

• 需要建立索引的場(chǎng)景

• 頻繁作為查詢條件的列，需建索引

• 多表關(guān)聯(lián)中，關(guān)聯(lián)字段需建索引

• 查詢中排序的字段，需建索引

• 不適用索引的場(chǎng)景

• 寫多讀少的表，不適合建索引

• 頻繁更新的字段，不適合建索引

explain執(zhí)行計(jì)劃

現(xiàn)有一張user表，其索引如下所示

其中name,age,address 三個(gè)字段作為一個(gè)組合索引

可以使用explain對(duì)某個(gè)SQL語(yǔ)句進(jìn)行性能分析

explain select * from user where name = 'am';

possible_keys
可能用到的索引
key
實(shí)際用到的索引
key_len
用于查詢的索引的長(zhǎng)度
ref
如果是等值查詢，這里會(huì)會(huì)是const
rows
預(yù)計(jì)需要掃描的行數(shù)（不是精確值）
extra

額外信息，如

• using where
  表示存儲(chǔ)引擎返回的結(jié)果，還需要在SQL Layer層過濾

• using index
  表示不需要回表查詢，一般在使用了覆蓋索引時(shí)會(huì)是這個(gè)值。覆蓋索引指的是，select中的列，全是索引列。不需要回表查詢指的是，直接走輔助索引，就能拿到索引列的值，不需要再去主鍵索引上取記錄了

• using index condition
  MySQL 5.6.x之后支持ICP特性（Index Condition Pushdown），可以把檢查條件下推到存儲(chǔ)引擎層，不符合條件的記錄，直接不讀取，而不是像原來(lái)一樣，先讀取出來(lái)，再在SQL Layer層過濾，這樣減少了存儲(chǔ)引擎層掃描的行數(shù)

• using filesort
  排序時(shí)無(wú)法用到索引

type

• system : 表中只有1行數(shù)據(jù)，或空表

• const : 使用唯一索引或主鍵索引，且用where等值查詢，返回記錄是1行，又叫唯一索引掃描

• ref : 針對(duì)非唯一索引，使用等值where條件，或者最左前綴規(guī)則的查詢。

下面是滿足了最左前綴規(guī)則，即對(duì)idx_name_age_add來(lái)說(shuō)，滿足了最左前綴，第一個(gè)索引為name

• range：索引范圍掃描，常見于>，<，between，in，like等查詢

注意like時(shí)，通配符%不能放在開頭，否則會(huì)導(dǎo)致全表掃描

• index ： 沒有完全匹配上索引，但不用回表查詢的

• all: 全表掃描，然后再在SQL Layer層過濾符合要求的記錄

索引使用規(guī)范（索引失效分析）

1. 全值匹配
   在索引列上使用等值查詢

explain select * from user where name = 'y' and age = 15;

2. 最左前綴

組合索引中，查詢條件要從組合索引的最左列開始，如上述example中組合索引idx_name_age_add，是建立在三個(gè)列name，age，address的，若跳過name，直接用age查詢，則會(huì)變?yōu)槿頀呙?/p>

explain select * from user where age = 15;

3. 不要在索引列上做計(jì)算

4. 范圍條件右側(cè)的索引列會(huì)失效

看到第一個(gè)SQL語(yǔ)句，沒有用上addresss索引

5. 盡量使用覆蓋索引

explain select name,age from user where name = 'y' and age = 1;

可以避免回表查詢

6. 索引字段不要使用不等（!= 或 <>）,不要判斷null（is null/ is not null）
會(huì)導(dǎo)致索引失效，轉(zhuǎn)為全表掃描

7. 索引字段上使用like時(shí)，不要以%開頭

8. 索引字段如果是字符串，記得加單引號(hào)

9. 索引字段不要用or

例子總結(jié)：

關(guān)于“MySQL索引及優(yōu)化的知識(shí)點(diǎn)有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。