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

MySQL目前提供了以下四種索引

• BTREE索引：最常見的索引類型，大部分索引都支持B樹索引
• HASH索引：只有MEMORY引擎支持，使用場(chǎng)景簡(jiǎn)單
• R-tree索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型，通常使用較少
• Full-text（全文索引）：全文索引也是MyISAM的一個(gè)特殊索引類型，主要用于全文索引，InnoDB從MySQL5.6版本開始支持全文索引

通常所說的索引，若為特別指出，基本值B+樹（多路搜索樹，并不一定是二叉樹）結(jié)構(gòu)組織的索引。

其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是使用B+tree樹索引，統(tǒng)稱為索引

BTREE結(jié)構(gòu)

Btree又叫多路平衡樹，一顆m叉的btree特性如下：

• 樹中每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子
• 除樹根節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少含有兩個(gè)孩子
• 所有的葉子節(jié)點(diǎn)都在同一層
• 每個(gè)非葉子節(jié)點(diǎn)由n個(gè)key與n+1個(gè)指針組成，其中[ceil(m/2)-1]<=n<=m-1

以5叉BTREE為例

插入步驟

key的數(shù)量：[ceil(m/2) - 1]<=n<=m-1。所以2<=n<=4。當(dāng)n>4時(shí)，中間節(jié)點(diǎn)分 裂到父節(jié)點(diǎn)，兩邊節(jié)點(diǎn)分 裂

以插入C N G A H E K Q M F W L T Z D P R X Y S數(shù)據(jù)為例

演變過程：

首先，結(jié)點(diǎn)空間足夠，4個(gè)字母插入相同的結(jié)點(diǎn)中

再插入H，n>4，空間不夠，中間元素G字母向上分 裂到新的節(jié)點(diǎn)（當(dāng)H插入的時(shí)候ACGHN，其中中間節(jié)點(diǎn)為G，所以是G向上分 裂）

插入E，K，Q不需要分 裂，各節(jié)點(diǎn)空間足夠

插入M，在HKNQ塊插入M（HKMNQ中間元素是M），元素M向上分 裂到父節(jié)點(diǎn)G，HK與NQ分 裂

插入F、W、L、T不需要分 裂

插入Z，Z大于M，走最右邊指針，指向NQTW的節(jié)點(diǎn)，在這個(gè)節(jié)點(diǎn)中NQTW插入Z，Z排序后是最大，中間元素為T，但是此時(shí)n最大為4，T需要向上分 裂，同時(shí)NQ和WZ分 裂

插入D，與上述插入Z同樣的步驟，D是中間元素，向上分 裂，AC和EF分 裂，而D插入到父節(jié)點(diǎn)中還能保持平衡；然后繼續(xù)插入P、R、X、Y都滿足Btree特性，不需要分 裂

最后插入S，S大于M小于T走第四個(gè)指針，向NPQR中插入，插入后事NPQRS，key個(gè)數(shù)超過4了，所以中間元素Q向上分 裂，同時(shí)NP、RS分 裂；Q插入到父節(jié)點(diǎn)中DGMT，變成DGMQT，超過4，中間元素M向上分 裂，同時(shí)DG和QT分 裂

刪除步驟

• 首先查找B樹中需刪除的元素,如果該元素在B樹中存在，則將該元素在其結(jié)點(diǎn)中進(jìn)行刪除，如果刪除該元素后，首先判斷該元素是否有左右孩子結(jié)點(diǎn)，如果有，則上移孩子結(jié)點(diǎn)中的某相近元素到父節(jié)點(diǎn)中，然后是移動(dòng)之后的情況；如果沒有，直接刪除后，移動(dòng)之后的情況
• 刪除元素，移動(dòng)相應(yīng)元素之后，如果某結(jié)點(diǎn)中元素?cái)?shù)目（即關(guān)鍵字?jǐn)?shù)）小于ceil(m/2)-1，則需要看其某相鄰兄弟結(jié)點(diǎn)是否豐滿（結(jié)點(diǎn)中元素個(gè)數(shù)大于ceil(m/2)-1），如果豐滿，則向父節(jié)點(diǎn)借一個(gè)元素來滿足條件；如果其相鄰兄弟都剛脫貧，即借了之后其結(jié)點(diǎn)數(shù)目小于ceil(m/2)-1，則該結(jié)點(diǎn)與其相鄰的某一兄弟結(jié)點(diǎn)進(jìn)行“合并”成一個(gè)結(jié)點(diǎn)，以此來滿足條件。

例如依次刪除上述BTREE中的H、T、R、E

首先刪除元素H，當(dāng)然首先查找H，H在一個(gè)葉子結(jié)點(diǎn)中，且該葉子結(jié)點(diǎn)元素?cái)?shù)目3大于最小元素?cái)?shù)目ceil(m/2)-1=2，則操作很簡(jiǎn)單，咱們只需要移動(dòng)K至原來H的位置，移動(dòng)L至K的位置（也就是結(jié)點(diǎn)中刪除元素后面的元素向前移動(dòng)）

刪除T，因?yàn)門沒有在葉子結(jié)點(diǎn)中，而是在中間結(jié)點(diǎn)中找到，發(fā)現(xiàn)它的繼承者W(字母升序的下個(gè)元素)，將W上移到T的位置，然后將原包含W的孩子結(jié)點(diǎn)中的W進(jìn)行刪除，刪除W后，該孩子結(jié)點(diǎn)中元素個(gè)數(shù)大于2，滿足平衡條件，無需進(jìn)行合并操作。

刪除R元素，R在葉子結(jié)點(diǎn)中,但是該結(jié)點(diǎn)中元素?cái)?shù)目為2，刪除導(dǎo)致只有1個(gè)元素，已經(jīng)小于最小元素?cái)?shù)目ceil(5/2)-1=2,而由前面我們已經(jīng)知道：如果其某個(gè)相鄰兄弟結(jié)點(diǎn)中比較豐滿（元素個(gè)數(shù)大于ceil(5/2)-1=2），則可以向父結(jié)點(diǎn)借一個(gè)元素，然后將最豐滿的相鄰兄弟結(jié)點(diǎn)中上移最后或最前一個(gè)元素到父節(jié)點(diǎn)中（有沒有看到紅黑樹中左旋操作的影子?），在這個(gè)實(shí)例中，右相鄰兄弟結(jié)點(diǎn)中比較豐滿（3個(gè)元素大于2），所以先向父節(jié)點(diǎn)借一個(gè)元素W下移到該葉子結(jié)點(diǎn)中，代替原來S的位置，S前移；然后X在相鄰右兄弟結(jié)點(diǎn)中上移到父結(jié)點(diǎn)中，最后在相鄰右兄弟結(jié)點(diǎn)中刪除X，后面元素前移。

刪除元素E，因?yàn)镋所在的結(jié)點(diǎn)數(shù)目剛好滿足最小平衡所需條件元素個(gè)數(shù)，而相鄰的兄弟結(jié)點(diǎn)也是同樣的情況，刪除一個(gè)元素都不能滿足條件，所以需要該節(jié)點(diǎn)與某相鄰兄弟結(jié)點(diǎn)進(jìn)行合并操作；首先移動(dòng)父結(jié)點(diǎn)中的元素（該元素在兩個(gè)需要合并的兩個(gè)結(jié)點(diǎn)元素之間）下移到其子結(jié)點(diǎn)中，然后將這兩個(gè)結(jié)點(diǎn)進(jìn)行合并成一個(gè)結(jié)點(diǎn)。所以在該實(shí)例中，咱們首先將父節(jié)點(diǎn)中的元素D下移到已經(jīng)刪除E而只有F的結(jié)點(diǎn)中，然后將含有D和F的結(jié)點(diǎn)和含有A,C的相鄰兄弟結(jié)點(diǎn)進(jìn)行合并成一個(gè)結(jié)點(diǎn)。

但是雖然D元素滿足ACDF的平衡條件，而G節(jié)點(diǎn)不滿足平衡條件，元素個(gè)數(shù)小于ceil(5/2)-1，而所有相鄰節(jié)點(diǎn)中元素個(gè)數(shù)都剛剛滿足平衡，不能像第二步驟中那樣借，只能像第三步驟那樣合并，與兄弟結(jié)點(diǎn)進(jìn)行合并成一個(gè)結(jié)點(diǎn)，而根結(jié)點(diǎn)中的唯一元素M下移到子結(jié)點(diǎn)，這樣，樹的高度減少一層

B+TREE結(jié)構(gòu)

B+Tree為Btree的變種，區(qū)別為：

• m叉B+Tree最多含有m個(gè)key，而BTREE最多含有m-1個(gè)key
• B+Tree的葉子節(jié)點(diǎn)保存所有key信息，依key大小順序排列
• 所有非葉子節(jié)點(diǎn)都可以看做key的索引一部分

由于B+Tree只有葉子節(jié)點(diǎn)保存key信息，查詢?nèi)魏蝛ey都要從root走到葉子。所以B+Tree的查詢效率更加穩(wěn)定

MySQL中的B+TREE

MySQL索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+TREE進(jìn)行了優(yōu)化。在原有的B+Tree基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能

索引分類

• 單值索引（單列索引）：一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引
• 唯一索引：索引列的值必須唯一，但允許有空值
• 復(fù)合索引：即一個(gè)索引包含多個(gè)列

索引語法

創(chuàng)建索引

-- 創(chuàng)建索引的時(shí)候不指定索引的類型的時(shí)候默認(rèn)使用的是B+TREE索引

-- 方式一：使用create的方式創(chuàng)建索引
create [UNIQUE|FULL TEXT|SPATIAL] INDEX index_name [USING index_type] on table_name(index_col_name,...);
-- 方式二：使用alter方式創(chuàng)建索引
-- 普通的索引
alter table table_name add index index_name(columnName_list);
-- 唯一索引(索引的列里面的值除了null可以多次出現(xiàn)，其余的值必須唯一)
alter table table_name add unique index index_name(columnName_list);
-- 全文索引
alter table table_name add fulltext index_name(columnName_list);

查看索引

show index from table_name;

刪除索引

drop index index_name on table_name;

索引失效情形

定義了索引而索引失效的分析

情形一：對(duì)使用索引的字段在條件查詢的時(shí)候使用了函數(shù)

情形二：

例如一張表中字段name,status,address構(gòu)成了復(fù)合索引，順序是create index name_status_address_index on table_name(name,status,address)，其中name、status、address都是varchar類型的

• 等值條件查詢時(shí)where name = 'aa' and status ='1' and address = '蘇州市'都走索引
• 條件查詢時(shí)where name = 'aa' and status >'1' and address = '蘇州市'由于status使用了范圍查詢，右邊的列不走索引，所以此條件中只有name和status走索引，address不走索引
• 等值條件查詢時(shí)where name = 'aa' and address = '蘇州市'根據(jù)最左前綴法則，只有name走了索引
• 等值條件查詢時(shí)where address = '蘇州市'根據(jù)最左前綴法則，沒有索引可走
• 等值條件查詢時(shí)where status = '1' and address = '蘇州市' and name = 'aa'都走索引
• 等值條件查詢時(shí)where name = 'aa'走name索引

情形三：

上述案例中索引情形

等值條件查詢時(shí)where name = 'aa' and status = 1只有name走索引，status涉及了隱式轉(zhuǎn)換，不走索引

情形四：

使用or關(guān)聯(lián)條件的時(shí)候，如果or前面的字段使用了索引，而后面的字段沒有索引，那么是不走索引的

例如

select name,status,address where name = 'aa' or remark = 'bb';
-- 使用explain分析的時(shí)候name也沒有走索引，索引失效了

select name,status,address where name = 'aa' and remark = 'bb';
-- 使用explain分析的時(shí)候name是走索引的，與or相反

• 模糊匹配的時(shí)候，like的字段如果使用了%開頭，也是不走索引的，但是單獨(dú)以%結(jié)尾走索引，這種情況可以使用覆蓋索引的方式解決，即要查詢的字段有索引用到

-- 還是上述的索引案例，這里id字段是表table_name的主鍵，所以是有id字段索引的；
-- 此處即使后面模糊匹配使用的是%開頭了，但是explain執(zhí)行下面的情形的時(shí)候我們發(fā)現(xiàn)還是走name_status_address_index名字索引
select id,name,status,address where name like '%aa%';

使用in和not in的時(shí)候，使用in走索引，not in不走索引（8版本之前是這樣，8版本之后都走索引）

有表信息，里面sellerid為主鍵，name,status,address為上述描述的聯(lián)合索引

5.7版本分別執(zhí)行in和not in的案例

8版本執(zhí)行案例，in和not in都走索引

情形五：

如果MySQL評(píng)估使用索引比全表更慢，則不使用索引，如上面情形四種in和not in案例的數(shù)據(jù)，里面sellerid為主鍵，name,status,address為上述描述的聯(lián)合索引，現(xiàn)在再設(shè)置一個(gè)address的單獨(dú)的索引create index idx_seller_address on seller(address);

當(dāng)執(zhí)行select * from seller where name = '北京市';我們發(fā)現(xiàn)即使使用了定義了索引，但是使用expalin的時(shí)候卻沒有像我們預(yù)期的那樣，而是走的全表索引，'北京市’的太多，估算不走索引，走全表掃描；

但是執(zhí)行select * from seller where name = '西安市';這個(gè)時(shí)候，我們發(fā)現(xiàn)依舊走idx_seller_address索引。

也就是說索引中的某個(gè)字段的值占據(jù)的比例特別大，基本占據(jù)了全表的記錄，那么就不走索引，直接全表掃描

情形六

is null和is not null有時(shí)走索引，有時(shí)不走索引

就像上面所說的那樣，字段占據(jù)全表的記錄多為null的話執(zhí)行is null就不走索引，走is not null就會(huì)走索引；相反索引字段中值為null少的話，執(zhí)行字段is null的時(shí)候走索引，走is not null就不走索引

例如數(shù)據(jù)

name字段有索引

當(dāng)執(zhí)行elplain select * from t_user where name is null;的時(shí)候

當(dāng)執(zhí)行elplain select * from t_user where name is not null;的時(shí)候

SQL優(yōu)化的細(xì)節(jié)

• 盡量使用索引，索引使用情形配合explain分析
• 查詢時(shí)使用到索引的字段，查詢的結(jié)果盡量在索引的字段內(nèi)（也就是盡量使用覆蓋索引），避免回表（因?yàn)樗饕挥涗浟怂饕臄?shù)據(jù)，而不記錄一整行的記錄數(shù)據(jù)）

例如上述的復(fù)合情形

select * from table_name where name= 'aa';
-- 此sql語句使用explain分析的時(shí)候可以看到Extra信息為Using index Condition,就是說查詢的結(jié)果還需回表查詢所有信息

select name,status,address from table_name where name= 'aa';
-- 此sql語句使用explain分析的時(shí)候可以看到Extra信息為Using where;Using index,就是說，查詢直接用到了索引中的數(shù)據(jù)，不用再回表查詢

select name,status,remark from table_name where name = 'aa';
-- 此sql語句使用explain分析的時(shí)候可以看到Extra信息為Using index Condition,就是說查詢的結(jié)果中remark字段不再符合索引中，還需回表查詢信息，所以extra為Using index Condition

大批量導(dǎo)入數(shù)據(jù)

大批量導(dǎo)入數(shù)據(jù)使用load指令導(dǎo)入大批量數(shù)據(jù)的時(shí)候

• 數(shù)據(jù)的最好已排好主鍵順序

-- load指令，其中filepath，table_name為具體文件路徑，和表名，','表示字段值之間以逗號(hào)分隔，\n表示換行符
load data local infile 'filepath' into table `table_name` fields terminated ',' line terminated '\n';

• 第二點(diǎn)當(dāng)表結(jié)構(gòu)有唯一性校驗(yàn)的時(shí)候，導(dǎo)入之前關(guān)閉唯一性校驗(yàn)，導(dǎo)入后開啟

-- 會(huì)話級(jí)關(guān)閉
set UNIQUE_CHECKS=0;
-- 導(dǎo)入完成后，再開啟
set UNIQUE_CHECKS=1;

• 第三點(diǎn)，手動(dòng)提交事務(wù)，在導(dǎo)入前開啟手動(dòng)提交事務(wù)，導(dǎo)入后再關(guān)閉手動(dòng)提交，因?yàn)槟J(rèn)是關(guān)閉的

insert時(shí)候優(yōu)化的細(xì)節(jié)

多數(shù)據(jù)插入的時(shí)候盡量將數(shù)據(jù)拼接在一起，開啟手動(dòng)提交事務(wù)，被 插入的數(shù)據(jù)記錄對(duì)應(yīng)的主鍵有序

order by排序的時(shí)候

有兩種情形在explain的extra中的信息，一種執(zhí)行時(shí)使用到索引using index，一種執(zhí)行時(shí)沒有使用到索引Using filesort

例如有emp表，表中的age和salary是復(fù)合索引

執(zhí)行下面的情形始終在explain的extra是filesort，即使order by字段建立了索引，但是沒有使用到

但是在查詢的使用覆蓋索引的時(shí)候，即查詢的字段只有索引內(nèi)的字段，但是沒有索引外的字段，下面是只有索引內(nèi)的字段展示；一旦select后面的字段有除索引定義的字段外的字段，如name的時(shí)候就不走索引了；當(dāng)然也會(huì)出現(xiàn)一種情形，即使使用了覆蓋索引，而在排序的條件，

一個(gè)字段升序，一個(gè)字段降序，又會(huì)出現(xiàn)Using filesort的情形了；兩個(gè)字段同時(shí)升序或同時(shí)降序也會(huì)用到索引，但是字段必須按照索引定義的時(shí)候的字段先后順序，不然即便是同樣的排序方式，字段先后順序變了，也會(huì)出現(xiàn)Using filesort的情形；一個(gè)字段增降序同樣用到索引

對(duì)出現(xiàn)filesort的優(yōu)化

通過創(chuàng)建合適的索引，能夠減少Filesort 的出現(xiàn),但是在某些情況下，條件限制不能讓Filesort消失，那就需要加快Filesort的排序操作。對(duì)于Filesort，MySQL 有兩種排序算法:

1)兩次掃描算法: MySQL4.1之前，使用該方式排序。首先根據(jù)條件取出排序字段和行指針信息，然后在排序區(qū)sort buffer中排序，如果sort buffer不夠，則在臨時(shí)表temporary table中存儲(chǔ)排序結(jié)果。完成排序之后,再根據(jù)行指針回表讀取記錄，該操作可能會(huì)導(dǎo)致大量隨機(jī)I/O操作。

2.)一次掃描算法: 一次性取出滿足條件的所有字段,然后在排序區(qū)sort buffer中排序后直接輸出結(jié)果集。排序時(shí)內(nèi)存開銷較大，但是排序效率比兩次掃描算法要高。MySQL通過比較系統(tǒng)變量max_length_for_sort_data的大小和Query語句取出的字段總大小，來判定是否那種排序算法，如果max_length_for_sort_data更大，那么使用第二種優(yōu)化之后的算法；否則使用第一種。

可以適當(dāng)提高sort_buffer_size和max_length_for_sort_data系統(tǒng)變量，來增大排序區(qū)的大小，提高排序的效率。

group by語句的優(yōu)化

由于**GROUP BY實(shí)際上也同樣會(huì)進(jìn)行排序操作**，且與ORDER BY相比，GROUP BY主要只是多了排序之后的分組操作。當(dāng)然,如果在分組的時(shí)候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計(jì)算。所以，在GROUP BY的實(shí)現(xiàn)過程中，與ORDER BY一樣也可以利用到索引。

如果查詢包含group by但是用戶想要避免排序結(jié)果的消耗，則可以執(zhí)行order by null禁止排序。如下：

而上面中的Using temporary是使用到了臨時(shí)表的含義，因?yàn)閍ge沒有索引

子查詢優(yōu)化

MySQL4.1版本之后,開始支持SQL的子查詢。這個(gè)技術(shù)可以使用SELECT語句來創(chuàng)建一個(gè)單列的查詢結(jié)果 ,然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，查詢是可以被更高效的連接(JOIN )替代。

or優(yōu)化

上面說到or關(guān)鍵字的時(shí)候，若or前面的字段有索引，后面的字段沒有索引，查詢時(shí)整個(gè)是不走索引的；而若有復(fù)合索引的時(shí)候，例如有一個(gè)復(fù)合索引create index idx_age_salary on table_name (age,salary);在使用查詢的時(shí)候select * from table_name where age = 20 or salary = 3000;這條語句即便使用到了age和salary，而age和salary是復(fù)合索引，但實(shí)際查詢的時(shí)候不會(huì)走索引建議使用union替換or

情形一：

情形二：

此處使用id和age分別走不通索引名的索引，但是type是index_merge

再看看使用union的情形，此處的type分別是常量const和ref，效率還是高于上面的index_merge

limit分頁查詢優(yōu)化

分頁查詢時(shí)，通過創(chuàng)建覆蓋索引能夠比較好地提高性能。一個(gè)常見又非常頭疼的問題就是limit 2000000,10 , 此時(shí)需要MySQL排序前2000010記錄，僅僅返回2000000-2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大。

優(yōu)化思路一：在主鍵上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容

優(yōu)化思路二：此方法有一定局限性，只適用于id自增的表，且主鍵不能斷層，(若中間出現(xiàn)某個(gè)記錄被刪除，id出現(xiàn)斷層，結(jié)果就不準(zhǔn)確)，可以把limit查詢轉(zhuǎn)化成某個(gè)位置的查詢

索引提示

use index建議MySQL去使用哪個(gè)

使用use index人為干預(yù)數(shù)據(jù)庫(kù)走具體的索引，使用use index僅僅是提供參考，MySQL不一定使用

在執(zhí)行select * from tb_seller where name = ‘aa’;的時(shí)候，可能走兩個(gè)索引，雖然最終只走一個(gè)索引，看下explain具體情形，在使用use index(index_name)的時(shí)候就會(huì)指定了

ignore index忽略索引

有點(diǎn)類似于上面的use index，不過是反著來的感覺

froce index強(qiáng)制使用某個(gè)索引

例如某個(gè)字段有索引，但是執(zhí)行的時(shí)候遇到了索引值的記錄過多的情形，MySQL會(huì)認(rèn)為不走索引更高效的情形，就不走索引，而是全表掃描了

使用force index后的情況

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論