如何創(chuàng)建高性能的索引

EXPLAIN 類型分析

explain 指令可以幫助我們查看查詢優(yōu)化器處理執(zhí)行計(jì)劃的一些細(xì)節(jié)信息

語法: explain + 執(zhí)行計(jì)劃

假如我們有這樣的兩張表(分類表和商品表),我們將結(jié)合explain 字段進(jìn)行說明:

#分類
CREATE TABLE category (
id int primary key auto_increment,
name varchar(50) not null,
c_desc varchar(200),
create_time datetime not null default now(),
key name_index(`name`)
)ENGINE = INNODB;

#商品
CREATE TABLE product (
p_id int PRIMARY KEY auto_increment,
category_id int not null,
name VARCHAR(50) not null
)ENGINE = INNODB;
復(fù)制代碼

字段說明:

id:選擇標(biāo)識(shí)符

通過這個(gè)字段,我們可以知道sql語句執(zhí)行的順序.

當(dāng)id相同時(shí),從上到下執(zhí)行,id不同時(shí),越大的越先執(zhí)行

select_type:表示查詢的類型

SIMPLE : 簡(jiǎn)單查詢

PRIMARY : 子查詢時(shí)外面的語句會(huì)被標(biāo)記為 PRIMARY,主表

UNION : 使用UNION 連接表查詢時(shí)處于后面的查詢

DEPENDENT UNION : UNION中的第二個(gè)或后面的select語句,取決于外面的查詢

UNION RESULT : UNION的結(jié)果,union語句中第二個(gè)查詢語句開始以及后面所有select的結(jié)果集

SUBQUERY : 子查詢中的第一個(gè)SELECT , 結(jié)果不依賴于外部查詢

DEPENDENT SUBQUERY : 子查詢中的第一個(gè)SELECT , 結(jié)果依賴外部查詢

DERIVED : 派生表的SELECT, FROM子句的子查詢

UNCACHEABLE SUBQUERY : 一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行

table:輸出結(jié)果集的表

這一行分析對(duì)應(yīng)的是哪一張數(shù)據(jù)庫(kù)表,如果有別名則會(huì)顯示別名

partitions:匹配的分區(qū) (5.5及之前的版本是沒有的,需要使用explain partitions select ……來顯示帶有partitions 的列)

表示使用的哪個(gè)分區(qū),如果沒有對(duì)表進(jìn)行顯式分區(qū)是看不到的

type:使用的索引星級(jí)

從優(yōu)到劣依次為: system > const > eq_ref > ref > range > index > all

索引優(yōu)化一般需要達(dá)到最少range級(jí)別

blog.csdn.net/weixin_4434…

possible_keys:表示查詢時(shí)，可能使用的索引

表示此次查詢可以用到的索引,如果沒有相關(guān)的索引,此列是NULL

key:表示實(shí)際使用的索引

如果此次查詢沒有用到索引,此列是NULL

key_len:索引字段的長(zhǎng)度

索引使用到的字節(jié)數(shù),該列顯示的為索引字段的最大可能長(zhǎng)度,并非世紀(jì)使用的長(zhǎng)度,在定義索引是計(jì)算出來的,并非實(shí)時(shí)計(jì)算出來.

長(zhǎng)度計(jì)算公式:

varchr(10)變長(zhǎng)字段且允許NULL = 10 * ( character set：uff8mb4=4, utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長(zhǎng)字段)

varchr(10)變長(zhǎng)字段且不允許NULL = 10 *( character set：uff8mb4=4,utf8=3,gbk=2,latin1=1)+2(變長(zhǎng)字段)

char(10)固定字段且允許NULL = 10 * ( character set：uff8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允許NULL = 10 * ( character set：uff8mb4=4,utf8=3,gbk=2,latin1=1)

ref: 引用到上一個(gè)表的列

rows:掃描出的行數(shù)(估算的行數(shù))

filtered:按表?xiàng)l件過濾的行百分比

Extra:額外的信息說明

這里挑比較常見的幾個(gè)做下說明:

Using index: 使用到了覆蓋索引

Using where:

1.查詢的列未被索引覆蓋,where子句篩選條件不滿足前綴索引(索引失效)

2.查詢的列未被索引覆蓋,where子句篩選條件非索引列

Using index condition: 范圍過濾索引

Using filesort: 使用到了磁盤排序,這是應(yīng)該避免的

Using temporary : 使用到了臨時(shí)表,這是應(yīng)該避免的

索引是什么?

索引是幫助查詢快速找到記錄的一種高效的查詢數(shù)據(jù)結(jié)構(gòu).

索引是針對(duì)查詢優(yōu)化最有效的手段.索引能夠?qū)⒉樵冃阅芴岣吆脦讉€(gè)數(shù)量級(jí).

索引是如何工作的?

在Mysql中,首先存儲(chǔ)引擎會(huì)根據(jù)匹配到的索引,在索引上找到對(duì)應(yīng)的值,然后根據(jù)匹配上的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行.

這個(gè)過程就像,我們嘗試在一本書上找到指定知識(shí)點(diǎn)的內(nèi)容,會(huì)先翻到書的“目錄”,然后根據(jù)指定“目錄”找到對(duì)應(yīng)的頁(yè)碼.

CREATE TABLE hero (
id int primary key auto_increment,
name varchar(50) not null,
hero_desc varchar(200),
key name_index(`name`)
)ENGINE = INNODB;

INSERT INTO hero VALUES(1,'張三','法外狂徒...');
INSERT INTO hero VALUES(2,'李四','...');
INSERT INTO hero VALUES(3,'王五','...');
INSERT INTO hero VALUES(4,'趙六','...');
INSERT INTO hero VALUES(5,'馮七','...');
INSERT INTO hero VALUES(6,'莫八','...');
INSERT INTO hero VALUES(7,'莫九','...');
復(fù)制代碼

假如我們要查找“name”為“張三”的數(shù)據(jù).會(huì)先根據(jù)name_index 索引查找到name='張三' 的數(shù)據(jù)行的主鍵id,然后通過 id = 1查詢主鍵索引找到指定的數(shù)據(jù)行返回

select * from hero where name = '張三';
復(fù)制代碼

索引的類型

索引的類型有很多,我們可以根據(jù)不同的場(chǎng)景選擇不同的索引.mysql的索引是在存儲(chǔ)引擎層使用的.不同的存儲(chǔ)引擎實(shí)現(xiàn)索引的方式也不相同.每個(gè)存儲(chǔ)引擎支持的索引也不相同,也不是所有的存儲(chǔ)引擎都支持所有的索引類型.

我們常用的存儲(chǔ)引擎一般是INNODB , 如果沒有特殊說明,使用的索引默認(rèn)為B-Tree 索引.

B-Tree 索引

B-Tree 使用 B+ 樹數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)數(shù)據(jù).大多數(shù)的mysql 存儲(chǔ)引擎都支持這種索引.存儲(chǔ)引擎以不同的方式使用B-Tree索引,性能也各有不同.例如MyISAM 引擎使用前綴壓縮技術(shù)來使索引更小,而InnoDB 則按照原始數(shù)據(jù)格式進(jìn)行存儲(chǔ).MyISAM索引通過數(shù)據(jù)存儲(chǔ)的物理位置來引用索引列,而InnoDB則根據(jù)主鍵來引用索引列.

B-Tree 通常意味著所有的值都是按順序存儲(chǔ)的,并且每一個(gè)葉子頁(yè)到跟的距離相同.

建立在 B+ Tree數(shù)據(jù)結(jié)構(gòu)上的索引(InnoDB引擎):

B-tree能夠加快數(shù)據(jù)訪問的速度,因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來找到指定的數(shù)據(jù).它從樹結(jié)構(gòu)的根結(jié)點(diǎn)出發(fā),跟節(jié)點(diǎn)種存放了指向子節(jié)點(diǎn)的指針,存儲(chǔ)引擎根據(jù)這些指針向下層查找.通過比

較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下一層子節(jié)點(diǎn).這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的下限和上限.存儲(chǔ)引擎要么最終找到要查找的值,要么該值不存在.

葉子節(jié)點(diǎn)不用于子節(jié)點(diǎn),它的指針指向的是被索引的數(shù)據(jù),可能是指向聚簇索引的指針或數(shù)據(jù)行.

可以使用B-Tree 索引的查詢類型:

B-Tree 適合用于全鍵值,鍵值范圍或鍵前綴查找.其中鍵前綴查找只適用于鍵最左前綴的查找.

創(chuàng)建一張商品表.創(chuàng)建主鍵索引,單值索引,復(fù)合索引.

create table product(
id int primary key auto_increment,
name varchar(50) not null comment '商品名稱',
category_id int not null comment '分類id',
price decimal not null comment '價(jià)格',
key product_name(`name`),
key category_and_name(`category_id`,`name`)
)engine = innodb;
復(fù)制代碼

全值匹配:

全值匹配指的是和索引中所有列進(jìn)行匹配.如:

select * from product where name = '筆記本';
復(fù)制代碼

select * from product where category_id = 1 and name = '阿巴阿巴';
復(fù)制代碼

匹配最左前綴:

最左前綴適用于復(fù)合索引(多列索引),如上面的category_and_name 索引.

最左前綴,故名思義,從左到右匹配索引.如 a ,b c 字段建立復(fù)合索引(a,b,c):

那么索引在什么時(shí)候能生效呢?

a ,

a b ,

a b c

select * from product where category_id = 1;
復(fù)制代碼

匹配列前綴:

匹配某一索引列的值的開頭的部分,like 'a%',like 'b%' . startWith

select * from product where name like '筆記%';
復(fù)制代碼

匹配范圍值:

對(duì)索引列的范圍查找,如 between , > < ,≥,≤

select * from product where id < 5;
復(fù)制代碼

select * from product where id between 1 and 5;
復(fù)制代碼

精確匹配某一列并范圍匹配另一列:

select * from product where name = '筆記本' and category_id > 1;
復(fù)制代碼

只訪問索引的查詢:

即覆蓋索引 , 不用再回表查詢數(shù)據(jù)行 , 使用explain 分析時(shí),extra 列為 Using index;

select name from product where name = '筆記本' ;
復(fù)制代碼

B-Tree 索引采用B+ Tree的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu),因此索引樹中的節(jié)點(diǎn)是有序的,所以除了按值查找之外,索引還可以用戶查詢中的Order By 操作(按順序查找); 一般來說,如果B-Tree 可以按照某種方式查找到值,那么也可以按照這個(gè)方式進(jìn)行排序.

所以說Order by 子句如果滿足上面幾種查詢類型,則這個(gè)索引也可以滿足對(duì)應(yīng)的排序需求.

上面說了幾種可以使用索引的查詢類型,遵循上述的查詢規(guī)則可以合理的使用索引,提高查詢效率.

哈希索引

哈希表是基于哈希表實(shí)現(xiàn)的 , 只有精準(zhǔn)匹配索引所有列的查詢才有效.

對(duì)與索引列的每一行數(shù)據(jù),存儲(chǔ)引擎都會(huì)為其計(jì)算出來一個(gè)hash碼,哈希碼是一個(gè)較小的值,不同鍵值的行計(jì)算出來的hash碼也不一樣.哈希索引將所有哈希碼存儲(chǔ)在索引中,同時(shí)在哈希表中保存一個(gè)每個(gè)數(shù)據(jù)行的指針.如果發(fā)生了hash碰撞就會(huì)以鏈表的形式存放在一個(gè)hash條目中.

在mysql中,只有Memory引擎顯式支持哈希索引.在這里我們不過多的去解釋他.

空間數(shù)據(jù)索引

MyISAM引擎支持空間索引,可以用作地理數(shù)據(jù)存儲(chǔ).

與B-Tree索引不同(主要與存儲(chǔ)結(jié)構(gòu)有關(guān)),它無需前綴索引.空間索引會(huì)從所有維度來索引數(shù)據(jù).查詢時(shí),可以有效地使用任意維度來組合查詢.必須使用MYSQL的GIS相關(guān)函數(shù)如 MBRCONTAINS()等來維護(hù)數(shù)據(jù).MySQL·對(duì)GIS的支持并不完善,所以這個(gè)索引基本不會(huì)被使用.

開源關(guān)系庫(kù)系統(tǒng)中對(duì)GIS的解決方案做得比較好的是PostgreSQL的 PostGIS;

全文索引

全文索引是一種特殊類型的索引,他查找的是文本中的關(guān)鍵詞,而不是直接比較索引中的值.全文索引更類似于搜索引擎做的事情,而不是簡(jiǎn)單的where匹配,這里不去過多解釋它.

使用索引有哪些好處?

最直接的,索引可以幫助我們從大量數(shù)據(jù)行中快速的找到我們需要的數(shù)據(jù).像最常見的B-Tree索引,因其存儲(chǔ)結(jié)構(gòu),可以幫助我們進(jìn)行Order by 和 Group by操作.總結(jié)以下優(yōu)點(diǎn):

1.大大減少了服務(wù)器所需要掃描的數(shù)據(jù)行數(shù),提高了查詢的效率

2.避免了生成 temp table 以及file sorted

3.避免了隨機(jī)I/O,將其轉(zhuǎn)換為有序I/O

索引不是越多越好,數(shù)據(jù)量越大,建立,使用,維護(hù)索引的成本越大.當(dāng)數(shù)據(jù)表體量較小時(shí),直接全表掃描比查詢索引更快(需要回表查詢的時(shí)候).只有當(dāng)索引幫助存儲(chǔ)引擎快速查找到記錄帶來的好處大于其帶來的額外工作時(shí),索引才是最有效的.

如果創(chuàng)建高性能的索引?

遵循索引的特性,避免索引失效.

索引失效的情況:

使用 or (除非所有or字段都添加了索引)

不滿足最佳左前綴原則.

like 查詢時(shí) 以 % 開頭.

匹配字符串時(shí)不加“”號(hào),需要類型轉(zhuǎn)換

使用范圍查詢后,后邊的查詢條件都不能使用索引匹配

查詢條件中索引列使用了函數(shù);

mysql優(yōu)化器優(yōu)化后,認(rèn)為全表掃描更快時(shí)

前綴索引和索引選擇性

有時(shí)候需要索引很長(zhǎng)的字符串,這會(huì)讓索引變的很大,并且越來越慢.

通?？梢运饕_始的部分字符,這樣可以大大節(jié)約索引空間,從而提升索引效率.但這樣也會(huì)降低索引的選擇行.

索引的選擇性是指,不重復(fù)的索引值(也稱為索引基數(shù)) 和數(shù)據(jù)表的記錄的總條目(#T)的比值. 范圍從 1/#T 到 1 之間.

索引的選擇性越高則查詢效率越高,因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過濾掉更多的行(降低索引命中行數(shù)).

比如說主鍵索引,唯一索引的選擇性是1,這是最好的索引選擇性,性能也是最好的.

InnoDB引擎的前綴長(zhǎng)度可以達(dá)到767字節(jié),如果啟用了innodb_large_prefix選項(xiàng),則可以達(dá)到3072字節(jié).

MyISAM引擎的前綴限制的1000字節(jié).text,blob,或者很長(zhǎng)的varchar類型的列必須使用前綴索引

前綴索引最好保證較高的選擇性的同時(shí)也不能太長(zhǎng).

前綴索引的基數(shù)應(yīng)該趨近于完成列的基數(shù),

1.索引列基數(shù) ≈ 完整列的基數(shù) (索引列基數(shù)/完整列的基數(shù)≈1)

2.索引基數(shù) / 索引總條目數(shù)≈ 完整列基數(shù) / 完整列總條目數(shù)

前綴索引對(duì)覆蓋索引的影響

使用前綴索引將無法利用覆蓋索引的優(yōu)化。

查詢時(shí)，系統(tǒng)并不確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?/p>

復(fù)合索引(多列索引)

索引能夠同時(shí)覆蓋多個(gè)數(shù)據(jù)列,對(duì)于復(fù)合索引來說:

mysql從左到右的使用索引中的字段,一個(gè)查詢可以只使用索引的一部分,但是只能從最左側(cè)開始.

例如:

我們定義了復(fù)合索引 index(c1,c2,c3),則我們進(jìn)行查找的時(shí)候可以 c1 , c2 ,c3 | c1 ,c2 | c1 這三種組合來查找,只能從最左邊來開始,

如果使用c2 , c3 進(jìn)行查找則索引會(huì)失效.當(dāng)最左側(cè)字段是常量引用時(shí),索引就十分有效.

當(dāng)我們需要頻繁的用到某些字段并且我們能確定使用字段的順序時(shí),我們就可以創(chuàng)建復(fù)合索引;12

但如果我們并不確定要用到哪些字段時(shí)就只能單獨(dú)的為這些字段添加索引,添加無用的復(fù)合索引會(huì)引起索引失效的同時(shí)給mysql更改添加刪除帶來壓力.

復(fù)合索引對(duì)排序的優(yōu)化：

我們要知道復(fù)合索引只會(huì)對(duì)與創(chuàng)建索引時(shí)的排序順序完全相同或相反的 order by語句進(jìn)行優(yōu)化

索引列的順序

索引的順序是至關(guān)重要的,正確的順序依賴于使用該索引的查詢,并且同時(shí)需要考慮如何更好地滿足排序和需要.

根據(jù)復(fù)合索引的最佳左前綴原則,意味著索引首先按照最左列進(jìn)行排序,然后依次往后排列.

當(dāng)不需要考慮排序和分組時(shí),應(yīng)該優(yōu)先考慮把選擇性高的索引列放在前面.

假如我們有這樣一張章節(jié)表:

CREATE TABLE `chapter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '章節(jié)名稱',
  `category_id` int(11) NOT NULL COMMENT '分類id',
  `project_id` int(11) NOT NULL COMMENT '項(xiàng)目 id',
  `subject_id` int(11) NOT NULL COMMENT '科目 id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
復(fù)制代碼

按照業(yè)務(wù)需求,我們希望根據(jù)分類,項(xiàng)目,科目這些查詢條件,查詢出符合條件的章節(jié),我們應(yīng)該如何設(shè)計(jì)一個(gè)復(fù)合索引?

首先我們先嘗試計(jì)算這些列的選擇性.

得出選擇性最高的列依次為subject_id,project_id,category_id,在不考慮分組和排序的情況下,索引應(yīng)該建立為:

ALTER TABLE `chapter` 
ADD INDEX `chapter_category`(`subject_id`, `project_id`, `category_id`)
復(fù)制代碼

聚簇索引

聚簇：表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。

聚簇索引通過關(guān)鍵字 primary key 來聲明,一個(gè)表只能有一個(gè)聚簇索引(覆蓋索引可以模擬多個(gè)聚簇索引).

聚簇索引并不是一種索引類型,而是一種數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)(B-Tree).

不同于Mysql中其他是 B-Tree類型索引,聚簇索引在葉子頁(yè)中還保存了數(shù)據(jù)行,而其他B-Tree類型索引中葉子頁(yè)存儲(chǔ)的是主鍵id,也就是聚簇索引的key,當(dāng)其查詢結(jié)果不能滿足當(dāng)前查詢時(shí),會(huì)

通過“回表”操作,查詢聚簇索引.

因?yàn)椴皇撬械拇鎯?chǔ)引擎都支持聚簇索引,在這里我們主要關(guān)注InnoDB ,但原理針對(duì)于任何支持聚簇索引的存儲(chǔ)引擎都是支持的.

下圖展示了聚簇索引中的記錄是如何存放的. 注意到,葉子頁(yè)包含了行的全部數(shù)據(jù),但是節(jié)點(diǎn)也只包含了索引列.

Innodb 的主鍵索引被定義為聚簇索引, 如果一個(gè)表沒有主動(dòng)聲明primary key ,InnoDB會(huì)選擇一個(gè)唯一非空的索引代替,如果都沒有,InnoDb會(huì) 隱式定義一個(gè)主鍵來作為聚簇索引.

InoDB只聚集同一個(gè)頁(yè)面中的索引,相鄰鍵值的頁(yè)面可能會(huì)相距很遠(yuǎn).

聚簇主鍵可能對(duì)性能有幫助,但也可能導(dǎo)致嚴(yán)重的性能問題.所以需要仔細(xì)地考慮聚簇索引,尤其是將表的存儲(chǔ)引擎從InnoDb鈣哼其他引擎的時(shí)候(反之亦然).

聚簇索引的優(yōu)點(diǎn)：

可以把相關(guān)數(shù)據(jù)保存在一起

數(shù)據(jù)訪問更快(聚集索引將索引和數(shù)據(jù)保存在同一個(gè)b-tree中)

使用覆蓋索引掃描的查詢可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值

聚簇索引的缺點(diǎn)：

聚簇?cái)?shù)據(jù)提高了IO性能，如果數(shù)據(jù)全部放在內(nèi)存中，則訪問的順序就沒那么重要了.

插入速度嚴(yán)重依賴插入順序。按主鍵的順序插入是速度最快的。但如果不是按照主鍵順序加載數(shù)據(jù)，則需在加載完成后最好使用optimize table重新組織一下表.

更新聚簇索引列的代價(jià)很高。因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置.

基于聚簇索引的表在插入新行，或主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨頁(yè)分裂的問題。頁(yè)分裂會(huì)導(dǎo)致表占用更多的磁盤空間.

聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí).

非聚集索引比想象的更大，因?yàn)槎?jí)索引的葉子節(jié)點(diǎn)包含了引用行的主鍵列.

非聚集索引訪問需要兩次索引查找(非聚集索引中葉子節(jié)點(diǎn)保存的行指針指向的是行的主鍵值)，對(duì)于innodb自適應(yīng)哈希索引可以減少這樣的重復(fù)工作.

InnoDB 和 MyISAM 的數(shù)據(jù)分布對(duì)比

聚簇索引與非聚簇索引的數(shù)據(jù)分布是有一定區(qū)別的,非聚簇索引的葉子結(jié)點(diǎn)存儲(chǔ)的是聚簇索引的key,也就是主鍵id.

在使用非聚簇索引查詢時(shí),往往需要查詢兩邊索引,先根據(jù)索引列查詢到主鍵id,然后根據(jù)主鍵id從聚簇索引中查找到指定的數(shù)據(jù)行.

假如有這樣一張表:

CREATE TABLE layout_test(
  col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY(col1),
    KEY(col2)
);

insert into layout_test values (99,8),(12,56),(3000,62),....(18,8),(4700,13),(3,93);
復(fù)制代碼

MyISAM:

MyISAM 是不支持聚簇索引的,相對(duì)來說數(shù)據(jù)分布比較簡(jiǎn)單.

MyISAM的每個(gè)索引中葉子結(jié)點(diǎn)存儲(chǔ)的都是指向數(shù)據(jù)行的地址值.我們用兩張圖大致來說明存儲(chǔ)方式.

layou_test表的數(shù)據(jù)分布:

MyISAM 按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤上.

col1 主鍵索引分布:

col2 普通索引分布:

這兩個(gè)存儲(chǔ)結(jié)構(gòu)是一致的,除了節(jié)點(diǎn)存儲(chǔ)的key 是根據(jù)索引列順序排列的.

InnoDB:

InnoDB區(qū)分聚簇索引與非聚簇索引.

col1主鍵索引分布:

聚簇索引的每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值、事務(wù)ID、用于事務(wù)和MVVC的回滾指針以及所有剩余列

col2普通索引分布:

葉子結(jié)點(diǎn)存儲(chǔ)的是聚簇索引的節(jié)點(diǎn) key

不同點(diǎn):

在InnoDB表中按主鍵順序插入行的重要性

數(shù)據(jù)頁(yè):

每個(gè)頁(yè)默認(rèn)大小是16k(在mysql5.6之后可以通過 innodb_page_size進(jìn)行選擇8/16k).

每個(gè)頁(yè)最多可以存儲(chǔ) 16k/2 - 200 = 7992行記錄(每個(gè)記錄最少2條記錄,每個(gè)頁(yè)都需要預(yù)留200byte)

每次加載數(shù)據(jù)頁(yè)從磁盤到內(nèi)存都需要進(jìn)行一次I/O讀取.

每個(gè)頁(yè)最少要存儲(chǔ)2行記錄(虛擬記錄,用來限定記錄的邊界,最大虛擬記錄-最小虛擬記錄)

InnoDB 的數(shù)據(jù)頁(yè)由以下 7 個(gè)部分組成：

文件頭(File Header) 固定 38 個(gè)字節(jié) (頁(yè)的位置，上一頁(yè)下一頁(yè)位置，checksum , LSN)
數(shù)據(jù)頁(yè)頭( Page Header)固定 56 個(gè)字節(jié) 包含slot數(shù)目，可重用空間起始地址，第一個(gè)記錄地址，記錄數(shù)，最大事務(wù)ID等
虛擬的最大最小記錄 (Infimum + Supremum Record)
用戶記錄 (User Records) 包含已經(jīng)刪除的記錄以鏈表的形式構(gòu)成可重用空間
待分配空間 (Free spaces) 未分配的空間
頁(yè)目錄 (Page Directory) slot 信息
文件尾 (File Trailer) 固定8個(gè)字節(jié),用來保證頁(yè)的完整性

非葉子節(jié)點(diǎn)存儲(chǔ)的是key + 指針,假設(shè)key是 bigint類型 , 則一個(gè)節(jié)點(diǎn)可以存儲(chǔ) 16k/(8byte + 6byte)

如果表中數(shù)據(jù)量不大,并且后期增長(zhǎng)穩(wěn)定,不需要考慮分表,那么最好可以定義一個(gè)可以控制插入順序的主鍵.最簡(jiǎn)單的方法是使用聲明auto_increment 自增列.這樣既可以保證數(shù)據(jù)行寫入的順序,對(duì)于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會(huì)更好.(外鍵關(guān)聯(lián)并不推薦,建議業(yè)務(wù)上做關(guān)聯(lián))

最好避免隨機(jī)的(不連續(xù)且值的分布范圍非常大)聚簇索引,特別是對(duì)于I/O密集型的應(yīng)用. 如,從性能的角度考慮,使用UUID來作為聚簇索引是非常糟糕的.它使得聚簇索引的插入變得完全隨機(jī),這是最壞的情況,使得數(shù)據(jù)沒有任何聚集特性.

因?yàn)槭琼樞虼鎯?chǔ)的,同頁(yè)的上一條數(shù)據(jù)與下一條數(shù)據(jù)總是相鄰的.當(dāng)頁(yè)背填充到最大閾值時(shí),就會(huì)生成新的頁(yè)來存儲(chǔ)后面的記錄.之后的數(shù)據(jù)就會(huì)按照順序存儲(chǔ)到新的頁(yè)中.

這總能保證每個(gè)頁(yè)都能被填滿.

但是如果不是順序存儲(chǔ)的,就可能會(huì)出現(xiàn)需要頻繁分裂頁(yè),數(shù)據(jù)的碎片化,需要更多的存儲(chǔ)空間,隨機(jī)的I/O讀取,這往往需要mysql做更多的處理操作.

因?yàn)樾虏迦氲逆I值不一定比之前插入的鍵值大,索引InnoDB無法簡(jiǎn)單地總是把新行插入到索引的最后,而是需要為新的行尋找合適的位置,通常是已由數(shù)據(jù)的中間位置,并且為之分配空間.這會(huì)增加很多額外的工作,并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化.

缺點(diǎn):

寫入的目標(biāo)頁(yè)可能已經(jīng)刷到磁盤中并從緩存中移除,或者是還沒有被加載到內(nèi)存中,InnoDB在插入之前不得不先找到并從磁盤讀取目標(biāo)頁(yè)到內(nèi)存中.這將導(dǎo)致大量的隨機(jī)I/O
因?yàn)閷懭胧菬o序的,InnoDB不得不頻繁地做頁(yè)分裂操作,以便為新的行分配空間.頁(yè)分裂會(huì)導(dǎo)致移動(dòng)大量數(shù)據(jù),一次插入最少需要修改三個(gè)頁(yè)而不是一個(gè)頁(yè)
頻繁的頁(yè)分裂,會(huì)導(dǎo)致數(shù)據(jù)存儲(chǔ)稀疏并且不規(guī)則,導(dǎo)致數(shù)據(jù)碎片化.

通過比較,很直觀的可以看出保證索引鍵值插入順序帶來的好處.

覆蓋索引

覆蓋索引是優(yōu)化查詢的一種手段,避免二級(jí)索引進(jìn)行回表查詢,只使用二級(jí)索引樹提供的信息就能滿足當(dāng)前查詢.

在我們創(chuàng)建索引時(shí),我們總是根據(jù)where 條件的需要?jiǎng)?chuàng)建合適的索引,這只用到了索引一方面的特性.在我們創(chuàng)建索引時(shí)應(yīng)該考慮到整個(gè)查詢,不單單是where條件的部分 , 還有 select 結(jié)果集部分.

如果索引能滿足我們需要的結(jié)果集,就不需要再去讀取數(shù)據(jù)行了. 當(dāng)我們只是需要查詢一本書指定目錄的標(biāo)題時(shí),目錄頁(yè)就能滿足我們的需求,我們也沒有必要去翻到指定頁(yè)再查看目錄了.

如果一個(gè)索引包含所有需要查詢的字段(select 后面的部分)的值,我們就稱為“覆蓋索引”.我們應(yīng)該指明我們需要select 的字段,以便mysql能對(duì)查詢進(jìn)行進(jìn)一步的優(yōu)化.

假如有這樣一張分類表:

CREATE TABLE category (
id int primary key auto_increment,
name varchar(50) not null,
c_desc varchar(200),
create_time datetime not null default now(),
key name_index(`name`)
)ENGINE = INNODB;

INSERT INTO category VALUES(1,'分類1','',default);
INSERT INTO category VALUES(2,'分類2','',default);
INSERT INTO category VALUES(3,'分類3','',default);
INSERT INTO category VALUES(4,'分類4','',default);
INSERT INTO category VALUES(5,'分類5','',default);
INSERT INTO category VALUES(6,'分類6','',default);
INSERT INTO category VALUES(7,'分類7','',default);
復(fù)制代碼

有一個(gè)需求,希望出對(duì)應(yīng) 分類 name的id

1.直接 select * 查詢?nèi)孔侄?/p>

EXPLAIN SELECT * FROM category WHERE name = '分類1';
復(fù)制代碼

使用explain 指令分析, 可以看到 mysql使用了name_index索引進(jìn)行查詢,但Extra中沒有任何額外信息,說明該查詢進(jìn)行了回表操作

(Mysql5.6之后加入了減少回表查詢次數(shù)以及Mysql server層和存儲(chǔ)引擎層交互的次數(shù)的優(yōu)化技術(shù)ICP[index condiyion pushdown])

2.select id,name 查詢指定字段

EXPLAIN SELECT id FROM category WHERE name = '分類1';
復(fù)制代碼

使用explain 指令分析, 可以看到 mysql使用了name_index索引進(jìn)行查詢, Extra = Using index,說明使用到了覆蓋索引.

覆蓋索引能帶來什么好處? 覆蓋索引避免了回表查詢,使查詢效率大大提高.

不同于主鍵索引,二級(jí)索引上葉子節(jié)點(diǎn)存放的是索引列+主鍵id,如果只需要讀取二級(jí)索引便能滿足我們的查詢,不必再回表查詢,極大的減少了數(shù)據(jù)訪問量.
要知道m(xù)ysql是分頁(yè)從磁盤上讀取數(shù)據(jù)到緩存中的,磁盤的讀取是比較耗時(shí)的.索引相對(duì)較小,更容易全部放入到內(nèi)存中,減少了I/O消耗,對(duì)I/O密集型應(yīng)用很有幫助.
因?yàn)樗饕前凑账饕兄淀樞虼鎯?chǔ)的,所以對(duì)于I/O密集型的范圍查詢會(huì)比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的I/O要少得多.(如果進(jìn)行回表查詢,是根據(jù)主鍵id去匹配的,id之間是不連續(xù)的,會(huì)造成大量的隨機(jī)I/O)

當(dāng)一個(gè)查詢被覆蓋索引優(yōu)化式,我們可以通過EXPLAIN 指令在Extra 列中看到 Using index.

需要注意的一些點(diǎn)

1.使用索引掃描來做排序

 mysql有兩種方式可以生成有序的結(jié)果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序
復(fù)制代碼

掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄就得回表查詢一次對(duì)應(yīng)的行

這基本都是隨機(jī)IO，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描慢

mysql可以使用同一個(gè)索引即滿足排序，又用于查找行，如果可能的話，設(shè)計(jì)索引時(shí)應(yīng)該盡可能地同時(shí)滿足這兩種任務(wù)。

只有當(dāng)索引的列順序和order by子句的順序完全一致，并且所有列的排序方式都一樣時(shí)，mysql才能夠使用索引來對(duì)結(jié)果進(jìn)行排序(Mysql8.0之后增加了尾部索引的特性,支持desc從大到

小).如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)orderby子句引的字段全部為第一張表時(shí)，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的，需要滿足索引的最左前綴的要求，

否則，mysql都需要執(zhí)行順序操作，而無法利用索引排序

2.避免冗余和重復(fù)的索引

MySQL允許在相同列上創(chuàng)建多個(gè)索引,并且需要單獨(dú)維護(hù)重復(fù)的索引.如果在查詢時(shí)能夠利用到這些索引,那么查詢優(yōu)化器在評(píng)估成本時(shí)也需要逐個(gè)進(jìn)行考慮,增加性能損耗.

同一個(gè)列可以創(chuàng)建順序相同,索引字段相同不同索引名稱的索引.我們應(yīng)該避免這樣的操作.在發(fā)現(xiàn)后也應(yīng)該立即移除.如

create index index_name1(name);

create index index_name2(name);

create unique index index_name3(name);

冗余索引與重復(fù)索引有些不同,如已經(jīng)創(chuàng)建了索引(A,B),再創(chuàng)建了索引(A),這就屬于冗余索引,因?yàn)?A,B)的前綴已經(jīng)包含了A.

對(duì)于InnoDB來說主鍵列應(yīng)包含在二級(jí)索引中了,所以這也是冗余的,站在性能方面,但這是不可避免的.

大多數(shù)情況下都不需要冗余索引,當(dāng)一個(gè)索引不滿足我們的查詢時(shí),我們首先想到的應(yīng)該是如何擴(kuò)展它.盡量不要?jiǎng)?chuàng)建新的索引,不合理索引的存在只會(huì)增大我們的空間占用和加劇性能的損耗.

但有些時(shí)候出于性能方面的考慮需要冗余索引,因?yàn)閿U(kuò)展已有的索引會(huì)導(dǎo)致其變得太大,從而影響其他查詢使用該索引時(shí)的查詢性能.

3.索引和鎖

索引可以讓查詢鎖定更少的行.如果查詢公布訪問那些不需要的行,那么就會(huì)鎖定更少的行,從這兩個(gè)方面來看這對(duì)性能都有好處.雖然InnnoDB行鎖效率很高,內(nèi)存使用也很小,但是鎖定行的時(shí)候仍然會(huì)帶來額外的鎖開銷;其次,鎖定超過需要的行也會(huì)增加鎖爭(zhēng)用并減少并發(fā)性.

4.減少數(shù)據(jù)和索引的碎片

首先碎片化的原因,碎片化是如何產(chǎn)生的.大量的刪除數(shù)據(jù),修改索引列的值,不規(guī)則的隨機(jī)插入都會(huì)造成數(shù)據(jù)和索引的碎片化

B-Tree索引可能會(huì)碎片化,這會(huì)降低查詢的效率.碎片化的索引可能會(huì)以很差或者無序的方式存儲(chǔ)在磁盤上.

根據(jù)設(shè)計(jì),B-Tree需要隨機(jī)磁盤訪問才能定位到葉子頁(yè),所以隨機(jī)訪問是不可避免的.

如果葉子頁(yè)在物理分布上是順序且緊密的,那么查詢的性能就會(huì)更好.

否則,對(duì)于范圍查詢,索引覆蓋掃描等操作來說,速度可能會(huì)降低很多倍,對(duì)于索引覆蓋掃描咋餓一點(diǎn)更加明顯.

表的數(shù)據(jù)存儲(chǔ)也可能碎片化.然而,數(shù)據(jù)存儲(chǔ)的碎片化比索引更加復(fù)雜.有三種類型的數(shù)據(jù)碎片:

行碎片(Row fragmentation)

這種碎片指的是數(shù)據(jù)行被存儲(chǔ)為多個(gè)地方的多個(gè)片段中.即使查詢只從索引種訪問一行記錄,行碎片也會(huì)導(dǎo)致性能下降

行間碎片(Intra-row fragmentation)

行間碎片是指邏輯上順序的頁(yè),或者在磁盤上不是順序存儲(chǔ)的.行間碎片對(duì)諸如全表掃描和聚簇索引掃描之類的操作有很大的影響,應(yīng)為這些操作原本能夠從磁盤上順序存儲(chǔ)的數(shù)據(jù)中獲益.

剩余空間碎片(Free space fragmentation)

剩余空間碎片是指數(shù)據(jù)頁(yè)中有大量的空余空間.這會(huì)導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù),從而造成浪費(fèi).

對(duì)于MyISAM表,這三類碎片化都可能發(fā)生.但I(xiàn)nnoDB不會(huì)出現(xiàn)短小的行碎片.InnoDB會(huì)移動(dòng)短小的行并重寫到一個(gè)片段中.

可以通過執(zhí)行 OPTIMIZE TABLE(運(yùn)行過程中,會(huì)鎖表) 或者導(dǎo)出再導(dǎo)入的方式重新整理數(shù)據(jù).這對(duì)多數(shù)處處引擎都是有效的.

對(duì)于InnoDB的表，MySQL實(shí)現(xiàn)原理其實(shí)是在線重建了表及其索引，并重新收集了統(tǒng)計(jì)信息。

MySQL關(guān)于表碎片整理OPTIMIZE TABLE操作的官方建議:

1.MySQL官方建議不要經(jīng)常(每小時(shí)或每天)進(jìn)行碎片整理，一般根據(jù)實(shí)際情況，只需要每周或者每月整理一次即可，可以寫成定時(shí)任務(wù)來做。

2.OPTIMIZE TABLE只對(duì)MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，并不是所有表都需要進(jìn)行碎片整理，一般只需要對(duì)包含上述可變長(zhǎng)度的文本數(shù)據(jù)類

型的表進(jìn)行整理即可。

3.在OPTIMIZE TABLE運(yùn)行過程中:

MyISAM會(huì)一直鎖著表. InnoDB在Mysql 5.6.17之前，優(yōu)化表不使用在線DDL。因此，當(dāng)優(yōu)化表運(yùn)行時(shí)，不允許在表上并發(fā)DML (INSERT, UPDATE, DELETE)

4.默認(rèn)情況下，直接對(duì)InnoDB引擎的數(shù)據(jù)表使用OPTIMIZE TABLE，可能會(huì)顯示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。這個(gè)時(shí)候，我們可以

用alter table T engine=InnoDB 來重建索引.

總結(jié)

在MySQL中, 大多數(shù)情況下都會(huì)B-Tree索引.如果在合適的場(chǎng)景中使用索引,將大大提高查詢的響應(yīng)時(shí)間,

在選擇索引和使用這些索引進(jìn)行查詢時(shí),有三個(gè)原則需要記住:

單行訪問是很慢的，特別是機(jī)械硬盤存儲(chǔ)中.
按順序訪問范圍數(shù)據(jù)是很快的。
順序的I/O不需要多次磁盤尋道，比隨機(jī)I/O快，
假如服務(wù)器可以順序讀取，就無需額外的排序操作，而且 GROUP BY 也無需再次排序和將行按組進(jìn)行聚合計(jì)算。
索引覆蓋是很快的.假如索引包含所需要查詢的所有列，那么存儲(chǔ)引擎就無需回表查找，避免大量的單行訪問.

作者：不是很蔡
鏈接：https://juejin.cn/post/7049926130572197896
來源：稀土掘金
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。

如何創(chuàng)建高性能的索引

如何創(chuàng)建高性能的索引

EXPLAIN 類型分析

id:選擇標(biāo)識(shí)符

select_type:表示查詢的類型

table:輸出結(jié)果集的表

partitions:匹配的分區(qū) (5.5及之前的版本是沒有的,需要使用explain partitions select ……來顯示帶有partitions 的列)

type:使用的索引星級(jí)

possible_keys:表示查詢時(shí)，可能使用的索引

key:表示實(shí)際使用的索引

key_len:索引字段的長(zhǎng)度

ref: 引用到上一個(gè)表的列

rows:掃描出的行數(shù)(估算的行數(shù))

filtered:按表?xiàng)l件過濾的行百分比

Extra:額外的信息說明

索引是什么?

索引是如何工作的?

索引的類型

B-Tree 索引

可以使用B-Tree 索引 的查詢類型:

哈希索引

空間數(shù)據(jù)索引

全文索引

使用索引有哪些好處?

如果創(chuàng)建高性能的索引?

遵循索引的特性,避免索引失效.

前綴索引和索引選擇性

復(fù)合索引(多列索引)

聚簇索引

聚簇索引

InnoDB 和 MyISAM 的數(shù)據(jù)分布對(duì)比

MyISAM:

InnoDB:

不同點(diǎn):

在InnoDB表中按主鍵順序插入行的重要性

覆蓋索引

需要注意的一些點(diǎn)

1.使用索引掃描來做排序

2.避免冗余和重復(fù)的索引

3.索引和鎖

4.減少數(shù)據(jù)和索引的碎片

總結(jié)

possible_keys:表示查詢時(shí)，可能使用的索引

可以使用B-Tree 索引的查詢類型: