預(yù)備知識(shí) B+樹(shù): http://www.reibang.com/p/5e0818b7c1ea

索引基礎(chǔ)

索引的作用是：

• 加速查找
• 約束

索引優(yōu)化是對(duì)查詢性能優(yōu)化最有效的手段了鼓黔。索引能輕易將查詢性能提高幾個(gè)數(shù)量級(jí)既绩，最優(yōu)的索引有時(shí)比一個(gè)好的索引性能要好兩個(gè)數(shù)量級(jí)糙捺。

如果沒(méi)有使用索引，相當(dāng)于從前往后整個(gè)表進(jìn)行查找笋额。而使用索引，相當(dāng)于擁有了一個(gè)目錄劳闹，可以大大加速查找掘鄙。

創(chuàng)建索引的過(guò)程可以理解為數(shù)據(jù)庫(kù)額外創(chuàng)建了一個(gè)文件（某種格式存儲(chǔ)）耘戚，作為目錄。在查找時(shí)操漠，先在這個(gè)目錄中查找收津。即存儲(chǔ)引擎先再索引中找到對(duì)應(yīng)值，然后根據(jù)匹配的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行浊伙。

舉例：

# 一開(kāi)始name列沒(méi)有索引
select * from userinfo3 where name='alxe';    // 300w數(shù)據(jù)里 接近7s找到
# 為name列創(chuàng)建一個(gè)普通的索引 相當(dāng)于創(chuàng)建了一個(gè)目錄文件
create index ix_name on userinfo3(name);
# 再次執(zhí)行同樣的查找
select * from userinfo3 where name='alxe';   // 僅0.07秒就可以完成
# 刪除索引 使用 drop idex 索引名 on 表名

索引的種類

• 普通索引
  • 加速查找
• 主鍵索引
  • 加速查找 + 不能為空 + 不能重復(fù)
• 唯一索引
  • 加速查找 + 不能重復(fù)
• 組合索引
  • 多列組成一個(gè)索引
    • 聯(lián)合主鍵索引
    • 聯(lián)合唯一索引
    • 聯(lián)合普通索引

以及兩種特殊索引名詞

• （覆蓋索引）能直接在索引文件獲取到數(shù)據(jù)
  • select id from tb where id = 1;
• （索引合并）多個(gè)單列索引合并使用
  • select * from tb where id=1 and name='xixi';

組合索引效率 > 索引合并

索引類型

索引是在存儲(chǔ)引擎層而不是服務(wù)層實(shí)現(xiàn)的撞秋。不同的存儲(chǔ)引擎的索引的工作方式并不相同，也不是所有的存儲(chǔ)引擎都支持所有類型的索引吧黄。即使多個(gè)存儲(chǔ)引擎支持同一種類型的索引部服，其底層的實(shí)現(xiàn)也可能不同唆姐。這里只提兩個(gè)常見(jiàn)的索引類型拗慨。

B-Tree索引

• B-Tree
  • 常用
  • 將索引放置于樹(shù)中
  • 有利于范圍查找

存儲(chǔ)引擎以不同的方式使用B-Tree索引，性能也各有不同，各有優(yōu)劣赵抢。例如剧蹂，MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)烦却。再如MyISAM索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行宠叼，而InnoDB則根據(jù)主鍵引用被索引的行。

B-Tree通常意味著所有的值都是按順序存儲(chǔ)的其爵，并且每個(gè)葉子頁(yè)到根的距離相同冒冬。

B-Tree索引能夠加快訪問(wèn)數(shù)據(jù)的速度，因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來(lái)獲取需要的數(shù)據(jù)摩渺，取而代之的是從索引的根節(jié)點(diǎn)開(kāi)始進(jìn)行搜索简烤。根節(jié)點(diǎn)的槽中存放了指向子結(jié)點(diǎn)的指針，存儲(chǔ)引擎根據(jù)這些指針向下層查找摇幻。通過(guò)比較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下層子結(jié)點(diǎn)横侦，這些指針實(shí)際上定義了子結(jié)點(diǎn)頁(yè)中值的上限和下限。最終存儲(chǔ)引擎要么是找到對(duì)應(yīng)的值绰姻，要么該記錄不存在枉侧。

葉子節(jié)點(diǎn)比較特別，它們的指針指向的是被索引的數(shù)據(jù)狂芋，而不是其他的節(jié)點(diǎn)頁(yè)榨馁。

image

B-Tree對(duì)索引列是順序組織存儲(chǔ)的，所以很適合查找范圍數(shù)據(jù)帜矾。

hash索引

• hash索引
  • 創(chuàng)建了一個(gè)索引哈希表辆影，記錄了每個(gè)索引的哈希值已經(jīng)數(shù)據(jù)存儲(chǔ)地址。
  • 哈希索引表里和原表順序不同 例如查找id<3的值黍特，則效率慢蛙讥。但是如果查找id=3，則效率很快灭衷。即范圍查找慢次慢，單值查找快。
  • 少用

哈希索引基于哈希表實(shí)現(xiàn)翔曲。只有精確匹配索引所有列的査詢才有效迫像。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼（hash code),哈希碼是一個(gè)較小的值瞳遍，并且不同鍵值的行計(jì)算出來(lái)的哈希碼也不一樣闻妓。哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)在哈希表中保存指向每個(gè)數(shù)據(jù)行的指針掠械。

MySQL中由缆，只有Memory引擎顯式支持哈希索引注祖。這也是Memory引擎表的默認(rèn)索引類型，Memory引擎同時(shí)也支持B-Tree索引均唉。值得一提的是是晨，Memory引擎是支持非唯一哈希索引的，這在數(shù)據(jù)庫(kù)世界里面是比較與眾不同的舔箭。如果多個(gè)列的哈希值相同罩缴，索引會(huì)以鏈表的方式存放多個(gè)記錄指針到同一個(gè)哈希條目中。

因?yàn)樗饕淮鎯?chǔ)對(duì)應(yīng)的哈希值层扶，所以索引的結(jié)構(gòu)十分緊湊箫章，這也讓哈希索引查找的速度非常快镜会。

但是哈希索引包含以下缺點(diǎn)：

1. 哈希索引只包含哈希值和行指針炉抒，而不存儲(chǔ)數(shù)據(jù)值，所以無(wú)法通過(guò)索引來(lái)避免讀取行稚叹。（當(dāng)select只取索引值時(shí)）
2. 哈希表中的哈希值是有序的焰薄，但導(dǎo)致索引值是無(wú)序的，所以無(wú)法用于排序
3. 哈希索引不支持部分索引列匹配索引扒袖。因?yàn)?strong>哈希值是根據(jù)所有使用的索引列進(jìn)行計(jì)算塞茅。所以當(dāng)索引為(A,B)時(shí)，如果只使用A季率，索引無(wú)效野瘦。
4. 哈希索引只支持等值查詢。即 = 飒泻、 IN()鞭光、 <=>。不支持范圍查詢
5. 當(dāng)出現(xiàn)哈希沖突時(shí)泞遗，需要遍歷對(duì)應(yīng)鏈表的所有行指針惰许，逐行比較。
6. 如果哈希沖突很多時(shí)史辙，一些索引維護(hù)操作的代價(jià)也會(huì)很高汹买。例如當(dāng)做對(duì)應(yīng)的刪除操作時(shí)，需要遍歷對(duì)應(yīng)鏈表的所有行指針聊倔，找到并刪除對(duì)應(yīng)行的引用晦毙。

因?yàn)檫@些限制，哈希索引只適用于某些特定的場(chǎng)合耙蔑。而一旦適合哈希索引见妒，則它帶來(lái)的性能提升將非常顯著。

Innodb引擎有一個(gè)特殊的功能叫“自適應(yīng)哈希索引”甸陌。 當(dāng)InnoDB注意到某些索引值被使用得非常頻繁時(shí)须揣，它會(huì)在內(nèi)存中盐股，基于B-Tree之上再創(chuàng)建一個(gè)哈希索引，這樣就讓B-Tree索引也具有哈希索引的一些優(yōu)點(diǎn)返敬，比如快速的哈希查找遂庄。這是一個(gè)完全自動(dòng)的寥院、內(nèi)部行為劲赠，用戶無(wú)法控制或配置，但是可以關(guān)閉秸谢。

自定義哈希索引 **** 實(shí)用

如果存儲(chǔ)引擎不支持哈希索引凛澎，可以模擬InnoDB創(chuàng)建哈希索引，這可以享受一些哈希索引的便利估蹄，例如只需要很小的索引就可以為超長(zhǎng)的鍵創(chuàng)建索引塑煎。

當(dāng)需要存儲(chǔ)大量的URL，并需要根據(jù)URL進(jìn)行搜索查找臭蚁。如果使用B-Tree來(lái)存儲(chǔ)URL最铁，存儲(chǔ)的內(nèi)容就會(huì)很大，因?yàn)閁RL本身都很長(zhǎng)垮兑。

常規(guī)查找：

SELECT id FROM url WHERE url="http://www.mysql.com";

若刪除原來(lái)URL列上的索引冷尉，新增一個(gè)被索引的url_crc列，使用CRC32做哈希系枪，就可以使用下面的方式查詢：

SELECT id FROM url WHERE url="http://www.mysql.com"
    AND url_crc=CRC32("http://www.mysql.com");

這樣做的性能會(huì)非常高雀哨，因?yàn)镸ySQL優(yōu)化器會(huì)使用這個(gè)選擇性很高而體積很小的基于url_crc列的索引來(lái)完成查找。只需要根據(jù)哈希值做快速的整數(shù)比較就能找到索引條目私爷。相比于對(duì)完整URL字符串做索引雾棺，效率高很多。

這樣實(shí)現(xiàn)的缺陷是需要維護(hù)哈希值衬浑“坪疲可以手動(dòng)通過(guò)觸發(fā)器實(shí)現(xiàn)。

1. 創(chuàng)建表如下：

CREATE TABLE url_hash_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL ,
  `url_crc` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 創(chuàng)建觸發(fā)器

DELIMITER //
create trigger url_insert before Insert on url_hash_test for each row
begin
set new.url_crc = crc32(new.url);
end;
//
create trigger url_update before update on url_hash_test for each row
begin
set new.url_crc = crc32(new.url);
end;
//
DELIMITER ;

盡量不要使用SHA1()\MD5()作為哈希函數(shù)工秩。因?yàn)檫@兩個(gè)函數(shù)計(jì)算出來(lái)的哈希值是非常長(zhǎng)的字符串嘉栓，會(huì)浪費(fèi)大量空間，比較時(shí)也會(huì)更慢拓诸。這兩個(gè)函數(shù)都是強(qiáng)加密函數(shù)侵佃，設(shè)計(jì)目標(biāo)是最大限度消除重讀，但這里并不需要這樣搞的要求奠支。

一旦出現(xiàn)哈希沖突

SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com");

是無(wú)法正確執(zhí)行的（但是可以用于統(tǒng)計(jì)記錄數(shù)）馋辈。所以我們采用哈希值加原數(shù)據(jù)進(jìn)行匹配，保證準(zhǔn)確性倍谜。

SELECT id FROM url WHERE url="http://www.mysql.com"
    AND url_crc=CRC32("http://www.mysql.com");

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

• 大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
• 幫助服務(wù)器避免排序和臨時(shí)表
• 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O

建立索引的缺點(diǎn)

• 額外的文件保存特殊的數(shù)據(jù)結(jié)構(gòu)
• 插入和更新刪除效率降低（需要更新索引文件）
• 需要命中索引才能發(fā)揮作用

索引并不總是最好的工具迈螟〔媛眨總的來(lái)說(shuō)，只有當(dāng)索引幫助存儲(chǔ)引擎快速查找到記錄帶來(lái)的好處大于其帶來(lái)的額外工作時(shí)答毫，索引才是有效的褥民。

• 對(duì)于非常小的表，大部分情況下簡(jiǎn)單的全表掃描更高效洗搂。
• 對(duì)于中到大型的表消返，索引就非常有效
• 對(duì)于特大型表，建立和使用索引的代價(jià)將隨之增長(zhǎng)耘拇。

對(duì)于特大型表的情況下撵颊，需要一種技術(shù)可以直接區(qū)分出查詢需要的一組數(shù)據(jù)，而不是一條一條記錄地匹配惫叛。例如 分區(qū)技術(shù)倡勇。如果表的數(shù)據(jù)特別多，可以建立一個(gè)元數(shù)據(jù)信息表嘉涌，用來(lái)查詢需要用到的某些特性妻熊。對(duì)于TB級(jí)別的數(shù)據(jù)，定位單條記錄的意義不大仑最，所以經(jīng)常會(huì)使用塊級(jí)別元數(shù)據(jù)技術(shù)來(lái)替代索引扔役。

索引策略

正確地創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢的基礎(chǔ)。

有效索引

可以使用B-Tree索引的查詢類型词身。B-Tree索引適用于全鍵值厅目、鍵值范圍或鍵前綴查找。其中鍵前綴查找只適用于根據(jù)最左前綴的查找法严。前面所述的索引對(duì)如下類型的查詢有效损敷。

前提： key (last_name, first_name, dob)

• 全值匹配

全值匹配指的是和索引中的所有列進(jìn)行匹配，
例如前面提到的索引可用于查找姓名為Cuba Allen深啤、出生于1960-01-01的人拗馒。

• 匹配最左前綴

前面提到的索引可用于查找所有姓為Allen的人，即只使用索引的第一列溯街。
匹配列前綴也可以只匹配某一列的值的開(kāi)頭部分诱桂。
例如前面提到的索引可用于查找所有以J開(kāi)頭的姓的人。這里也只使用了索引的第一列呈昔。

• 匹配范圍值

例如前面提到的索引可用于查找姓在Allen和Barrymore之間的人挥等。
這里也只使用了索引的第一列。

• 精確匹配到某一列并范圍匹配另外一列

前面提到的索引也可用于查找所有姓為Allen堤尾，并且名字是字母K開(kāi)頭（比如Kim肝劲、Karl等）的人。
即第一列l(wèi)ast_name全匹配，第二列first_name范圍匹配辞槐。

• 只訪問(wèn)索引的查詢

B-Tree通持朗可以支持“只訪問(wèn)索引的查詢”，即查詢只需要訪問(wèn)索引榄檬，而無(wú)須訪問(wèn)數(shù)據(jù)行卜范。

因?yàn)樗饕龢?shù)中的節(jié)點(diǎn)是有序的，所以除了按值查找之外鹿榜，索引還可以用于查詢中的ORDER BY操作（按順序查找）海雪。一般來(lái)說(shuō)，如果B-Tree可以按照某種方式查找到值犬缨，那么也可以按照這種方式用于排序喳魏。所以棉浸，如果ORDER BY子句滿足前面列出的幾種查詢類型怀薛，則這個(gè)索引也可以滿足對(duì)應(yīng)的排序需求。

下面是一些關(guān)于B-Tree索引的限制：

• 如果不是按照索引的最左列開(kāi)始查找迷郑，則無(wú)法使用索引枝恋。例如上面例子中的索引在每用于查找名字為Bill的人，也無(wú)法查找某個(gè)特定生日的人嗡害，因?yàn)檫@兩列都不是最左數(shù)據(jù)列焚碌。類似地，也無(wú)法查找姓氏以某個(gè)字母結(jié)尾的人霸妹。
• 不能跳過(guò)索引中的列十电。也就是說(shuō)，前面所述的索引無(wú)法用于查找姓為Smith并且在某個(gè)特定日期出生的人叹螟。如果不指定名（first_name）鹃骂，則MySQL只能使用索引的第一列。
• 如果查詢中有某個(gè)列的范圍(like between > < 都算范圍查詢)查詢罢绽，則其右邊所有列都無(wú)法使用索引優(yōu)化查找畏线。例如有查詢WHERE lastname='Smith’AND firstname like '%J%'AND dob＝’1976-12-23＇，這個(gè)查詢只能使用索引的前兩列良价，因?yàn)檫@里的like是一個(gè)范圍條件（但是服務(wù)器可以把其余列用于其他目的）寝殴。如果范圍查詢列值的數(shù)量有限局齿，那么可以通過(guò)使用多個(gè)等于條件來(lái)代替范圍條件晌涕。

所以前面提到的索引列的順序是多么的重要：這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候垂涯，可能需要使用相同的列但順序不同的索引來(lái)滿足不同類型的查詢需求痊银。

也有些限制并不是B-Tree本身導(dǎo)致的抵蚊，而是MySQL優(yōu)化器和存儲(chǔ)引擎使用索引的方式導(dǎo)致的，這部分限制在未來(lái)的版本中可能就不再是限制了。

高效使用索引的策略

獨(dú)立的列

索引不能是表達(dá)式的一部分泌射，也不能是函數(shù)的參數(shù)粘姜。

SELECT actor_id FROM actor WHERE actor_id + 1 =5;

如以上查詢，無(wú)法使用actor_id列索引熔酷。MySQL無(wú)法自動(dòng)解析這個(gè)方程孤紧，這完全是用戶行為。我們應(yīng)該養(yǎng)成化簡(jiǎn)WHERE條件的習(xí)慣拒秘，始終將索引列單獨(dú)放在比較符號(hào)的一側(cè)号显。

前綴索引和索引選擇性

有時(shí)候索引很長(zhǎng)的字符列，這會(huì)讓索引變得大且慢躺酒。通逞涸椋可以索引開(kāi)始的部分字符，這樣可以大大節(jié)約索引空間羹应，從而提高索引效率揽碘。但這樣也會(huì)降低索引的選擇性（可以理解為準(zhǔn)確度，唯一索引的選擇性為1）园匹。

所以我們要再選擇性足夠高的前提下雳刺，減少索引值的長(zhǎng)度。

SELECT COUNT(*)AS cnt, LEFT(city, 3) AS pref
    FROM city GROUP BY pref ORDER BY cnt DESC LIMIT 10;

通過(guò)left截取長(zhǎng)度裸违，分組排序掖桦。然后和原數(shù)據(jù)進(jìn)行比較，查看選擇性足夠高時(shí)供汛，合適的字符長(zhǎng)度枪汪。

選擇合適的索引列順序（B-Tree）

由于哈希或者其他類型的索引不會(huì)像B-Tree索引一樣按順序存儲(chǔ)數(shù)據(jù)怔昨，本節(jié)只適合B-Tree索引雀久。

經(jīng)驗(yàn)法則：將選擇性最高的列放到索引最前列。（在某些場(chǎng)景有效朱监，需要更全面考慮）

當(dāng)不需要考慮排序和分組時(shí)岸啡，將選擇性最高的列放在前面通常是很好的。因?yàn)榇藭r(shí)索引的作用只是用于優(yōu)化WHERE條件查找赫编，確實(shí)能夠最快地過(guò)濾出需要的行巡蘸。

然而，性能不只依賴于所有索引列的選擇性擂送，也和查詢條件的具體值有關(guān)悦荒，也就是和值的分布有關(guān)∴诙郑可能需要根據(jù)那些運(yùn)行頻率最高的查詢來(lái)調(diào)整索引列的順序搬味，讓這種情況下索引的選擇性最高。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

可以通過(guò)類似代碼，計(jì)算出不同列的選擇性碰纬，然后將選擇性大的放于前面萍聊。

當(dāng)某些特殊值作為查詢條件導(dǎo)致性能很差時(shí)，也可以查詢某個(gè)值在該列的占比悦析，當(dāng)占比很高寿桨，代表選擇性很低，不適合作為索引條件查詢强戴⊥っ可以禁止這些特殊值應(yīng)用于某個(gè)查詢。

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型骑歹，而是一種數(shù)據(jù)存儲(chǔ)方式预烙。InnoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。

當(dāng)表有聚簇索引時(shí)道媚，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)中扁掸。因?yàn)闊o(wú)法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能由一個(gè)聚簇索引衰琐。

索引是由存儲(chǔ)引擎實(shí)現(xiàn)的也糊，所以并不是所有的存儲(chǔ)引擎都支持聚簇索引炼蹦。此處主要關(guān)注InnoDB

[圖片上傳失敗...(image-3c31d-1565862000648)]
如圖羡宙，葉子頁(yè)包含了行的全部數(shù)據(jù)，但是節(jié)點(diǎn)頁(yè)只包含了索引列掐隐。

InnoDB將通過(guò)主鍵聚集數(shù)據(jù)狗热。（一些數(shù)據(jù)庫(kù)服務(wù)允許選擇哪個(gè)索引作為聚簇索引。）

如果沒(méi)有定義主鍵虑省，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替匿刮。
如果也沒(méi)有，則會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引探颈。
InnoDB只聚集在同一個(gè)頁(yè)面中的記錄熟丸。包含相鄰鍵值的頁(yè)面可能會(huì)相距甚遠(yuǎn)。

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

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

• 可以把相關(guān)數(shù)據(jù)保存在一起
• 數(shù)據(jù)訪問(wèn)更快伪节。由于索引和數(shù)據(jù)保存在一起光羞，因此從聚簇索引中獲取數(shù)據(jù)通常比非聚簇索引中查找要快
• 使用覆蓋索引掃描的查詢可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值。

缺點(diǎn)：

• 聚簇?cái)?shù)據(jù)最大限度地提高了I/O密集型應(yīng)用的性能怀大，但如果數(shù)據(jù)都放在內(nèi)存中纱兑，則訪問(wèn)順序就沒(méi)那么重要了，聚簇索引就沒(méi)有什么優(yōu)勢(shì)了化借。
• 插入速度嚴(yán)重依賴于插入順序潜慎。（內(nèi)部是按主鍵排序的。如果亂序插入后，建議使用OPTIMIZE TABLE命令重新組織一下表）
• 更新聚簇索引列的代價(jià)很高铐炫。會(huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置垒手。
• 在插入新行或者主鍵更新時(shí)，可能某個(gè)頁(yè)已滿倒信，需要進(jìn)行頁(yè)分裂淫奔。影響效率，并且占用更多的磁盤(pán)空間堤结。
• 全表掃描更慢唆迁，尤其是行比較稀疏的情況，或者由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)竞穷。
• 二級(jí)索引（非聚簇索引）更大唐责，因?yàn)樾枰鎯?chǔ)主鍵列。
• 二級(jí)索引訪問(wèn)需要兩次查找瘾带。（在二級(jí)索引中鼠哥，先找到主鍵值，再去聚簇索引找對(duì)應(yīng)的行）

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

MyISAM中主鍵索引和其他所有在結(jié)構(gòu)上沒(méi)有什么不同看政。主鍵索引就是一個(gè)名為PRIMART的唯一非空索引朴恳。

在InnoDB中，聚簇索引“就是”表允蚣，每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值于颖、事務(wù)ID、用于事務(wù)和MVCC的回滾指針以及所有的剩余列嚷兔。而在二級(jí)索引（非聚簇索引）中森渐，葉子節(jié)點(diǎn)中存儲(chǔ)的不是“行指針”，而是主鍵值冒晰，通過(guò)主鍵值在聚簇索引中找到對(duì)應(yīng)行同衣。雖然占用更多空間，但在移動(dòng)行時(shí)無(wú)需更新二級(jí)索引中的這個(gè)“指針”壶运。

image

在InnoDB表中按主鍵順序插入行

推薦使用自增AUTO_INCREMENT列作為主鍵耐齐，這樣可以保證數(shù)據(jù)行是按順序?qū)懭耄瑢?duì)于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會(huì)更好蒋情。

最好避免不連續(xù)且值分布范圍非常大的聚簇索引埠况，特別是對(duì)于I/O密集型的應(yīng)用。從性能的角度考慮恕出，使用UUID來(lái)作為聚簇索引則會(huì)很糟糕：它使得聚簇索引的插入變得完全隨機(jī)询枚，這是最壞的情況，使得數(shù)據(jù)沒(méi)有任何聚集特性浙巫。

如果主鍵是順序的金蜀，InnoDB只需要把插入是記錄放在上一條記錄的后面刷后。當(dāng)達(dá)到頁(yè)的最大填充因子時(shí)（InnoDB默認(rèn)的最大填充因子是頁(yè)大小的15/16，留出部分空間用于以后修改）渊抄，下一條記錄就會(huì)寫(xiě)入新的頁(yè)中尝胆。一旦數(shù)據(jù)按照這樣順序加載，那么主鍵頁(yè)就會(huì)近似于被順序的記錄填滿（利用率高护桦，而不是一個(gè)頁(yè)只保存稀疏的數(shù)據(jù)）含衔，這頁(yè)正是所期待的結(jié)果。

如果采用類似uuid的隨機(jī)主鍵值二庵，每插入一條數(shù)據(jù)InnoDB都需要尋找合適的位置并分配空間（頻繁地做頁(yè)分裂操作）贪染。這會(huì)增加很多額外工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化（頁(yè)變得稀疏）催享，并且導(dǎo)致最終數(shù)據(jù)會(huì)有碎片杭隙。

順序的主鍵什么時(shí)候會(huì)造成更壞的結(jié)果？

對(duì)于高并發(fā)工作負(fù)載因妙，如果按照順序插入痰憎，會(huì)造成明顯的爭(zhēng)用。主鍵的上界會(huì)成為“熱點(diǎn)”攀涵，因?yàn)樗械牟迦攵及l(fā)生在這里铣耘，所以并發(fā)插入可能導(dǎo)致間隙鎖競(jìng)爭(zhēng)。另一個(gè)熱點(diǎn)可能是AUTO_INCREMENT鎖機(jī)制以故。如果遇到這個(gè)問(wèn)題蜗细，可能需要考慮重新設(shè)計(jì)表或應(yīng)用，或者更改innodb_autoinc_lock_mode配置据德。

覆蓋索引

如果可以使用索引來(lái)直接獲取列的數(shù)據(jù)鳄乏，不需要回表查詢，則稱為覆蓋索引棘利。

好處：

• 索引條目通常小于數(shù)據(jù)行大小，會(huì)極大減少數(shù)據(jù)訪問(wèn)量朽缴。這對(duì)緩存的負(fù)載非常重要善玫，因?yàn)檫@種情況下響應(yīng)實(shí)際大部分花費(fèi)在數(shù)據(jù)拷貝上。對(duì)于I/O密集型的應(yīng)用密强，因?yàn)樗斜葦?shù)據(jù)更小茅郎，更容易全部放入內(nèi)存中。
• 如果二級(jí)主鍵能夠覆蓋查詢或渤，則可以避免對(duì)主鍵索引的二次查詢

覆蓋索引必須要存儲(chǔ)索引列的值系冗，而哈希索引、空間索引等都不存儲(chǔ)索引列的值薪鹦，所以mysql只能使用B-Tree索引做覆蓋索引掌敬。

索引掃描來(lái)做排序

mysql由兩種方式生成有序的結(jié)果

• 通過(guò)排序
• 按索引順序掃描惯豆。

掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)掃緊接著的下一條記錄奔害。但如果索引不能覆蓋查詢所需的全部列楷兽，那么就不得不掃描一條索引記錄就回表查詢一次對(duì)于的行。這基本上都是隨機(jī)I/O华临。因此按索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描慢芯杀，尤其是在I/O密集型的工作負(fù)載時(shí)。

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致雅潭，并且所有列的排序方向都一樣時(shí)揭厚，mysql才能使用索引來(lái)對(duì)結(jié)果做排序。

不滿足最左前綴也能利用索引排序：即前綴列的條件為常量時(shí)

# key(date, id)
where date='2005-05-25' order by id

壓縮（前綴壓縮）索引

MyISAM使用前綴壓縮來(lái)減少索引的大小扶供，從而讓更多的索引可以放入內(nèi)存中棋弥，這在某些情況下能極大地提高性能。默認(rèn)只壓縮字符串诚欠，但通過(guò)參數(shù)也可以對(duì)整數(shù)做壓縮顽染。

壓縮每個(gè)索引塊的方法是，先完全保存索引塊中的第一個(gè)值轰绵，然后將其他值和第一個(gè)值進(jìn)行比較得到相同前綴的字節(jié)數(shù)和剩余不同的后綴部分粉寞，把這部分存儲(chǔ)起來(lái)。

索引塊第一個(gè)值是“perform”
第二個(gè)值是 “performance”
則第二個(gè)值存儲(chǔ)的類似 7左腔，ance

壓縮塊使用更少的空間唧垦，代價(jià)是某些操作可能更慢。因?yàn)槊總€(gè)值的壓縮前綴都依賴于前面的值液样，導(dǎo)致的缺點(diǎn)是：

• 無(wú)法在索引塊使用二分查找
• 正序掃描速度還行振亮，倒敘很差
• 隨即查找速度差

冗余和重復(fù)、未使用索引

重復(fù)鞭莽、未使用的索引無(wú)疑要?jiǎng)h除坊秸。（例如對(duì)主鍵列又設(shè)置了唯一索引）

冗余索引則需要分情況討論。

如果創(chuàng)建索引(A, B)再創(chuàng)建索引(A)就冗余了澎怒，因?yàn)檫@只是前一個(gè)索引的前綴索引（只對(duì)B-Tree索引來(lái)說(shuō)）褒搔。但如果創(chuàng)建(B, A)、(B)則不是冗余索引喷面，因?yàn)樗鼈兌疾皇?A, B)的最左前綴列星瘾。

冗余索引通常發(fā)生在為表添加新索引的時(shí)候。例如在已有索引(A)惧辈，不考慮擴(kuò)展成(A, B)而直接添加新索引(A, B)

大多數(shù)時(shí)候都不需要冗余索引琳状，應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新的索引。

但是如果額外需要添加索引是類似一個(gè)很長(zhǎng)的VARCHAR列盒齿，如果采用擴(kuò)展的策略念逞，對(duì)于前一個(gè)索引來(lái)說(shuō)性能可能會(huì)急劇下降困食。特別是有查詢把這個(gè)索引當(dāng)作覆蓋索引，或者是MyISAM表并且有很多防衛(wèi)查詢的時(shí)候肮柜。

總結(jié)

• 單行訪問(wèn)是很慢的陷舅。如果從存儲(chǔ)中讀取一個(gè)數(shù)據(jù)塊只是為了獲取其中一行，則浪費(fèi)了很多工作审洞。最好讀取的塊中能包含盡可能多所需要的行莱睁。使用索引可以創(chuàng)建位置引用以提高效率
• 按順序訪問(wèn)訪問(wèn)數(shù)據(jù)是很快的。兩個(gè)原因：順序I/O不需要多次磁盤(pán)尋道芒澜，比隨機(jī)I/O快很多仰剿。二，如果能按需要順序取數(shù)據(jù)痴晦，則不需要額外的排序操作
• 索引覆蓋查詢很快南吮。

命中索引

1. like
   • 模糊查詢以%開(kāi)頭，會(huì)導(dǎo)致索引失效
   • 即使不以%開(kāi)頭誊酌，使用like也會(huì)降低查詢效率
   • 如果用戶量很大的話部凑，不使用like 而會(huì)導(dǎo)入第三方工具處理文字
2. 避免使用函數(shù)
   • 例如使用reverse(email) 會(huì)導(dǎo)致索引失效
   • 盡量將類似的功能在代碼中完成
3. or
   • 當(dāng)or條件中有未建立的索引列會(huì)失效
   • 例： SELECT * FROM TB WHERE 索引列 or 非索引列 則在此 索引失效
   • 但是 SELECT * FROM TB WHERE 索引列 or 非索引列 and 索引列 則會(huì)只用首尾的索引列
4. 類型不一致
   • 即傳入的數(shù)據(jù)類型要與列類型相符 不然索引失效
   • 例如列類型為字符 而傳入數(shù)
5. ！= >
   • 普通索引使用 碧浊！= 索引失效 但主鍵有效
   • 普通索引字符型 > 索引失效 數(shù)字或者主鍵有效
6. order by
   • 當(dāng)根據(jù)索引排序時(shí)涂邀，選擇的映射如果不是索引，則失效
   • 例如： select name from tb where email='123@' email是索引箱锐，但映射name 索引失效
   • 但是對(duì)主鍵排序比勉，索引有效
7. 最左前綴
   • 如果組合索引為（name，email）
   • name and email 有效
   • name 有效
   • email 失效
8. 一個(gè)表建的索引盡量不要超過(guò)5個(gè)驹止。
9. 盡量使用覆蓋索引浩聋。
10. 盡量不要在重復(fù)數(shù)據(jù)多的列上建索引。
11. ...

Mysql

在Inodb存儲(chǔ)引擎中臊恋，也有頁(yè)的概念衣洁，默認(rèn)每個(gè)頁(yè)的大小為16K，也就是每次讀取數(shù)據(jù)時(shí)都是讀取4 * 4K的大小捞镰。

image

在某個(gè)頁(yè)內(nèi)插入新行時(shí)闸与，為了減少數(shù)據(jù)的移動(dòng)，通常是插入到當(dāng)前行的后面或者是已刪除行留下來(lái)的空間岸售，所以在某一個(gè)頁(yè)內(nèi)的數(shù)據(jù)并不是完全有序的，但是為了數(shù)據(jù)訪問(wèn)順序性厂画，在每個(gè)記錄中都有一個(gè)指向下一條記錄的指針凸丸，以此構(gòu)成了一條單向有序鏈表，不過(guò)在這里為了方便演示我是按順序排列的袱院！

每個(gè)數(shù)據(jù)頁(yè)都會(huì)為存儲(chǔ)在它里邊兒的記錄生成一個(gè)頁(yè)目錄屎慢，在通過(guò)主鍵查找某條記錄的時(shí)候可以在頁(yè)目錄中使用二分法快速定位到對(duì)應(yīng)的槽瞭稼，然后再遍歷該槽對(duì)應(yīng)分組中的記錄即可快速找到指定的記錄

由于數(shù)據(jù)還比較少，一個(gè)頁(yè)就能容下腻惠，所以只有一個(gè)根結(jié)點(diǎn)环肘，主鍵和數(shù)據(jù)也都是保存在根結(jié)點(diǎn)（左邊的數(shù)字代表主鍵，右邊名字集灌、性別代表具體的數(shù)據(jù)）悔雹。假設(shè)我們寫(xiě)入10條數(shù)據(jù)之后，Page1滿了欣喧，再寫(xiě)入新的數(shù)據(jù)會(huì)怎么存放呢腌零？

image

這時(shí)候需要進(jìn)行頁(yè)分裂，產(chǎn)生一個(gè)新的Page唆阿。在innodb中的流程是：

1. 產(chǎn)生新的Page2益涧，然后將Page1的內(nèi)容復(fù)制到Page2
2. 產(chǎn)生新的Page3，將新插入的數(shù)據(jù)（“秦壽生”）放入Page3
3. 原來(lái)的Page1依然作為根結(jié)點(diǎn)驯鳖，但是變成了一個(gè)不存放數(shù)據(jù)只存索引的頁(yè)闲询，并且有兩個(gè)子結(jié)點(diǎn)Page2、Page3

這里有兩個(gè)問(wèn)題：

1. 為什么要復(fù)制Page1為Page2而不是創(chuàng)建一個(gè)新的頁(yè)作為根結(jié)點(diǎn)浅辙，這樣就少了一步復(fù)制的開(kāi)銷(xiāo)了扭弧？

如果是重新創(chuàng)建根結(jié)點(diǎn)，那根結(jié)點(diǎn)存儲(chǔ)的物理地址可能經(jīng)常會(huì)變摔握，不利于查找寄狼。并且在innodb中根結(jié)點(diǎn)是會(huì)預(yù)讀到內(nèi)存中的，所以結(jié)點(diǎn)的物理地址固定會(huì)比較好氨淌！

2. 原來(lái)Page1有10條數(shù)據(jù)泊愧，在插入第11條數(shù)據(jù)的時(shí)候進(jìn)行裂變，根據(jù)前面對(duì)B-Tree盛正、B+Tree特性的了解删咱，那這至少是一顆11階的樹(shù)，裂變之后每個(gè)結(jié)點(diǎn)的元素至少為11/2=5個(gè)豪筝，那是不是應(yīng)該頁(yè)裂變之后主鍵1-5的數(shù)據(jù)還是在原來(lái)的頁(yè)痰滋，主鍵6-11的數(shù)據(jù)會(huì)放到新的頁(yè)，根結(jié)點(diǎn)存放主鍵6续崖？

如果是這樣的話新的頁(yè)空間利用率只有50%敲街，并且會(huì)導(dǎo)致更為頻繁的頁(yè)分裂。所以innodb對(duì)這一點(diǎn)做了優(yōu)化严望，新的數(shù)據(jù)放入新創(chuàng)建的頁(yè)多艇，不移動(dòng)原有頁(yè)面的任何記錄。

每次新增數(shù)據(jù)像吻，都是將一個(gè)頁(yè)寫(xiě)滿峻黍，然后新創(chuàng)建一個(gè)頁(yè)繼續(xù)寫(xiě)复隆，這里其實(shí)是有個(gè)隱含條件的，那就是主鍵自增姆涩！主鍵自增寫(xiě)入時(shí)新插入的數(shù)據(jù)不會(huì)影響到原有頁(yè)挽拂，插入效率高！且頁(yè)的利用率高骨饿！但是如果主鍵是無(wú)序的或者隨機(jī)的亏栈，那每次的插入可能會(huì)導(dǎo)致原有頁(yè)頻繁的分裂，影響插入效率样刷！降低頁(yè)的利用率仑扑！這也是為什么在innodb中建議設(shè)置主鍵自增的原因！

這棵樹(shù)的非葉子結(jié)點(diǎn)上存的都是主鍵置鼻，那如果一個(gè)表沒(méi)有主鍵會(huì)怎么樣镇饮？在innodb中，如果一個(gè)表沒(méi)有主鍵箕母，那默認(rèn)會(huì)找建了唯一索引的列储藐，如果也沒(méi)有，則會(huì)生成一個(gè)隱形的字段作為主鍵嘶是！

有數(shù)據(jù)插入那就有刪除钙勃，如果這個(gè)用戶表頻繁的插入和刪除，那會(huì)導(dǎo)致數(shù)據(jù)頁(yè)產(chǎn)生碎片聂喇，頁(yè)的空間利用率低辖源，還會(huì)導(dǎo)致樹(shù)變的“虛高”，降低查詢效率希太！這可以通過(guò)索引重建來(lái)消除碎片提高查詢效率克饶！

innodb引擎數(shù)據(jù)查找

1. 找到數(shù)據(jù)所在頁(yè)。這個(gè)查找過(guò)程和B+樹(shù)的搜索過(guò)程意義誊辉，從根結(jié)點(diǎn)開(kāi)始查找一直到葉子結(jié)點(diǎn)矾湃。
2. 在頁(yè)內(nèi)找具體的數(shù)據(jù)。讀取第一步找到的葉子結(jié)點(diǎn)數(shù)據(jù)到內(nèi)存中堕澄，然后通過(guò)分塊查找的方法找到具體的數(shù)據(jù)邀跃。

這跟我們?cè)谛氯A字典中找某個(gè)漢字是一樣的，先通過(guò)字典的索引定位到該漢字拼音所在的頁(yè)蛙紫，然后到指定的頁(yè)找到具體的漢字拍屑。innodb中定位到頁(yè)后用了哪種策略快速查找某個(gè)主鍵呢？這我們就需要從頁(yè)結(jié)構(gòu)開(kāi)始了解坑傅。

image

• 左邊藍(lán)色區(qū)域稱為Page Directory丽涩，這塊區(qū)域由多個(gè)slot組成，是一個(gè)稀疏索引結(jié)構(gòu)裁蚁，即一個(gè)槽中可能屬于多個(gè)記錄矢渊，最少屬于4條記錄，最多屬于8條記錄枉证。槽內(nèi)的數(shù)據(jù)是有序存放的矮男，所以當(dāng)我們尋找一條數(shù)據(jù)的時(shí)候可以先在槽中通過(guò)二分法查找到一個(gè)大致的位置。
• 右邊區(qū)域?yàn)閿?shù)據(jù)區(qū)域室谚，每一個(gè)數(shù)據(jù)頁(yè)中都包含多條行數(shù)據(jù)毡鉴。注意看圖中最上面和最下面的兩條特殊的行記錄Infimum和Supremum，這是兩個(gè)虛擬的行記錄秒赤。在沒(méi)有其他用戶數(shù)據(jù)的時(shí)候Infimum的下一條記錄的指針指向Supremum猪瞬，當(dāng)有用戶數(shù)據(jù)的時(shí)候，Infimum的下一條記錄的指針指向當(dāng)前頁(yè)中最小的用戶記錄入篮，當(dāng)前頁(yè)中最大的用戶記錄的下一條記錄的指針指向Supremum陈瘦，至此整個(gè)頁(yè)內(nèi)的所有行記錄形成一個(gè)單向鏈表。
• 行記錄被Page Directory邏輯的分成了多個(gè)塊潮售，塊與塊之間是有序的痊项，也就是說(shuō)“4”這個(gè)槽指向的數(shù)據(jù)塊內(nèi)最大的行記錄的主鍵都要比“8”這個(gè)槽指向的數(shù)據(jù)塊內(nèi)最小的行記錄的主鍵要小。但是塊內(nèi)部的行記錄不一定有序酥诽。
• 每個(gè)行記錄的都有一個(gè)nowned的區(qū)域（圖中粉紅色區(qū)域）鞍泉，nowned標(biāo)識(shí)這個(gè)這個(gè)塊有多少條數(shù)據(jù)，偽記錄Infimum的nowned值總是1肮帐，記錄Supremum的nowned的取值范圍為[1,8]咖驮，其他用戶記錄nowned的取值范圍[4,8]，并且只有每個(gè)塊中最大的那條記錄的nowned才會(huì)有值训枢，其他的用戶記錄的n_owned為0主胧。
• 所以當(dāng)我們要找主鍵為6的記錄時(shí)吹埠，先通過(guò)二分法在稀疏索引中找到對(duì)應(yīng)的槽，也就是Page Directory中“8”這個(gè)槽，“8”這個(gè)槽指向的是該數(shù)據(jù)塊中最大的記錄臣缀，而數(shù)據(jù)是單向鏈表結(jié)構(gòu)所以無(wú)法逆向查找，所以需要找到上一個(gè)槽即“4”這個(gè)槽胳喷，然后通過(guò)“4”這個(gè)槽中最大的用戶記錄的指針沿著鏈表順序查找到目標(biāo)記錄吨拍。

聚集索引 & 非聚集索引

• 聚集索引：數(shù)據(jù)與索引存放在一起，找到索引就找到了數(shù)據(jù)婆誓。
• 非聚集索引：將數(shù)據(jù)和索引分開(kāi)存儲(chǔ)吃环，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向數(shù)據(jù)的對(duì)于行

InnoDB中，在聚簇索引之上創(chuàng)建的索引稱之為輔助索引洋幻。輔助索引訪問(wèn)數(shù)據(jù)總是需要二次查找郁轻，非聚簇索引都是輔助索引，例如：復(fù)合索引、前綴索引好唯、唯一索引竭沫，輔助索引葉子結(jié)點(diǎn)存儲(chǔ)的不再是行的物理地址，而是主鍵值骑篙。InnoDB 只聚集在同一個(gè)頁(yè)面中的記錄蜕提。包含相鄰健值的頁(yè)面可能相距甚遠(yuǎn)。

聚簇索引具有唯一性靶端。由于將數(shù)據(jù)和索引結(jié)構(gòu)存放在一起谎势，因此一個(gè)表僅有一個(gè)聚簇索引。

聚簇索引默認(rèn)是主鍵杨名，如果表中沒(méi)有主鍵脏榆，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替。如果也沒(méi)有台谍，InnoDB會(huì)隱式的定義一個(gè)主鍵來(lái)作為聚簇索引须喂。

聚簇索引性能最好而且具有唯一性，所以非常珍貴典唇，必須慎重設(shè)置镊折。一般要根據(jù)這個(gè)表最常用的SQL查詢方式來(lái)進(jìn)行選擇，某個(gè)字段作為聚簇索引介衔，或組合聚簇索引恨胚，這個(gè)要看實(shí)際情況。

我們最終目的就是在相同結(jié)果集情況下炎咖，盡可能減少邏輯IO赃泡。

image

image

1. InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹(shù)中乘盼，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上升熊，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹(shù)的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)绸栅，之后獲得行數(shù)據(jù)级野。
2. 若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹(shù)中檢索Name粹胯，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵蓖柔。第二步使用主鍵在主索引B+樹(shù)種再執(zhí)行一次B+樹(shù)檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)风纠。（重點(diǎn)在于通過(guò)其他鍵需要建立輔助索引）拿聚集索引的key到主鍵索引樹(shù)上查找對(duì)應(yīng)的數(shù)據(jù)况鸣，這個(gè)過(guò)程稱為回表！
3. MyISM使用的是非聚簇索引竹观，非聚簇索引的兩棵B+樹(shù)看上去沒(méi)什么不同镐捧，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已潜索，主鍵索引B+樹(shù)的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹(shù)存儲(chǔ)了輔助鍵懂酱。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方竹习，這兩顆B+樹(shù)的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來(lái)說(shuō)玩焰，這兩個(gè)鍵沒(méi)有任何差別由驹。由于索引樹(shù)是獨(dú)立的，通過(guò)輔助鍵檢索無(wú)需訪問(wèn)主鍵的索引樹(shù)昔园。

聚簇索引的優(yōu)勢(shì)、劣勢(shì)

看上去聚簇索引的效率明顯要低于非聚簇索引并炮，因?yàn)槊看问褂幂o助索引檢索都要經(jīng)過(guò)兩次B+樹(shù)查找默刚，這不是多此一舉嗎？聚簇索引的優(yōu)勢(shì)在哪逃魄？

1. 由于行數(shù)據(jù)和葉子節(jié)點(diǎn)存儲(chǔ)在一起荤西，同一頁(yè)中會(huì)有多條行數(shù)據(jù)，訪問(wèn)同一數(shù)據(jù)頁(yè)不同行記錄時(shí)伍俘，已經(jīng)把頁(yè)加載到了Buffer中邪锌，再次訪問(wèn)的時(shí)候，會(huì)在內(nèi)存中完成訪問(wèn)癌瘾，不必訪問(wèn)磁盤(pán)觅丰。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了妨退，如果按照主鍵Id來(lái)組織數(shù)據(jù)妇萄，獲得數(shù)據(jù)更快。
2. 輔助索引使用主鍵作為"指針"而不是使用地址值作為指針的好處是咬荷，減少了當(dāng)出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁(yè)分裂時(shí)輔助索引的維護(hù)工作冠句，使用主鍵值當(dāng)作指針會(huì)讓輔助索引占用更多的空間，換來(lái)的好處是InnoDB在移動(dòng)行時(shí)無(wú)須更新輔助索引中的這個(gè)"指針"幸乒。也就是說(shuō)行的位置（實(shí)現(xiàn)中通過(guò)16K的Page來(lái)定位）會(huì)隨著數(shù)據(jù)庫(kù)里數(shù)據(jù)的修改而發(fā)生變化（前面的B+樹(shù)節(jié)點(diǎn)分裂以及Page的分裂）懦底，使用聚簇索引就可以保證不管這個(gè)主鍵B+樹(shù)的節(jié)點(diǎn)如何變化，輔助索引樹(shù)都不受影響罕扎。
3. 聚簇索引適合用在排序的場(chǎng)合聚唐，非聚簇索引不適合
4. 取出一定范圍數(shù)據(jù)的時(shí)候，使用用聚簇索引
5. 二級(jí)索引需要兩次索引查找壳影，而不是一次才能取到數(shù)據(jù)拱层，因?yàn)榇鎯?chǔ)引擎第一次需要通過(guò)二級(jí)索引找到索引的葉子節(jié)點(diǎn)，從而找到數(shù)據(jù)的主鍵宴咧，然后在聚簇索引中用主鍵再次查找索引根灯，再找到數(shù)據(jù)
6. 可以把相關(guān)數(shù)據(jù)保存在一起。例如實(shí)現(xiàn)電子郵箱時(shí)，可以根據(jù)用戶 ID 來(lái)聚集數(shù)據(jù)烙肺，這樣只需要從磁盤(pán)讀取少數(shù)的數(shù)據(jù)頁(yè)就能獲取某個(gè)用戶的全部郵件纳猪。如果沒(méi)有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤(pán) I/O桃笙。

存在的劣勢(shì)：

1. 維護(hù)索引很昂貴氏堤，特別是插入新行或者主鍵被更新導(dǎo)至要分頁(yè)(page split)的時(shí)候。建議在大量插入新行后搏明，選在負(fù)載較低的時(shí)間段鼠锈，通過(guò)OPTIMIZE TABLE優(yōu)化表，因?yàn)楸仨毐灰苿?dòng)的行數(shù)據(jù)可能造成碎片星著。使用獨(dú)享表空間可以弱化碎片
2. 表因?yàn)槭褂肬UId（隨機(jī)ID）作為主鍵购笆，使數(shù)據(jù)存儲(chǔ)稀疏，這就會(huì)出現(xiàn)聚簇索引有可能有比全表掃面更慢虚循，所有建議使用主鍵自增同欠。
   
   image
   
   
   image
   
   主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲(chǔ)在上一條記錄的后面横缔。當(dāng)達(dá)到頁(yè)的最大填充因子時(shí)（InnoDB 默認(rèn)的最大填充因子是頁(yè)大小的 15/16铺遂，留出部分空間用于以后修改），下一條記錄就會(huì)寫(xiě)入新的頁(yè)中茎刚。一旦數(shù)據(jù)按照這種順序的方式加載襟锐，主鍵頁(yè)就會(huì)近似于被順序的記錄填滿（二級(jí)索引頁(yè)可能是不一樣的）
3. 如果主鍵比較大的話，那輔助索引將會(huì)變的更大斗蒋，因?yàn)檩o助索引的葉子存儲(chǔ)的是主鍵值捌斧；過(guò)長(zhǎng)的主鍵值，會(huì)導(dǎo)致非葉子節(jié)點(diǎn)占用占用更多的物理空間

為什么都建議使用自增主鍵泉沾？

因?yàn)槭褂米栽鲋麈I可以避免頁(yè)分裂捞蚂。

在InnoDB中，底層的數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)跷究。所謂的索引其實(shí)就是一顆B+樹(shù)姓迅，一個(gè)表有多少個(gè)索引就會(huì)有多少棵B+樹(shù)，mysql中的數(shù)據(jù)都是按順序保存在B+樹(shù)上的（所以說(shuō)索引本身是有序的俊马。）

mysql在底層又是以數(shù)據(jù)頁(yè)為單位來(lái)存儲(chǔ)數(shù)據(jù)的丁存，一個(gè)數(shù)據(jù)頁(yè)大小默認(rèn)為16K，當(dāng)然也可以自定義大小柴我。如果一個(gè)數(shù)據(jù)頁(yè)存滿了解寝，mysql就會(huì)去申請(qǐng)一個(gè)新的數(shù)據(jù)頁(yè)來(lái)存儲(chǔ)數(shù)據(jù)。

如果主鍵為自增id的劃艘儒，mysql在寫(xiě)滿一個(gè)數(shù)據(jù)頁(yè)的時(shí)候聋伦，直接申請(qǐng)另一個(gè)數(shù)據(jù)頁(yè)接著寫(xiě)就可以了夫偶。聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的觉增，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤(pán)上的兵拢。如果主鍵不是自增id，那么可以想 象逾礁，它會(huì)干些什么说铃，為了確保索引有序，mysql就需要將每次插入的數(shù)據(jù)都放到合適的位置上嘹履。不斷地調(diào)整數(shù)據(jù)的物理地址腻扇、分頁(yè)（需要把上個(gè)數(shù)據(jù)頁(yè)的部分?jǐn)?shù)據(jù)挪到新的數(shù)據(jù)頁(yè)上。這就造成了頁(yè)分裂植捎，這個(gè)大量移動(dòng)數(shù)據(jù)的過(guò)程是會(huì)嚴(yán)重影響插入效率的衙解。），當(dāng)然也有其他一些措施來(lái)減少這些操作焰枢，但卻無(wú)法徹底避免。但舌剂，如果是自增的济锄，那就簡(jiǎn)單了，它只需要一 頁(yè)一頁(yè)地寫(xiě)霍转，索引結(jié)構(gòu)相對(duì)緊湊荐绝，磁盤(pán)碎片少，效率也高避消。

另外在滿足業(yè)務(wù)需求的情況下低滩，盡量使用占空間更小的主鍵id，因?yàn)槠胀ㄋ饕娜~子結(jié)點(diǎn)上保存的是主鍵id的值岩喷，如果主鍵id占空間較大的劃恕沫，會(huì)成倍增加mysql空間占用大小。

因?yàn)镸yISAM的主索引并非聚簇索引纱意，那么他的數(shù)據(jù)的物理地址必然是凌亂的婶溯，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取偷霉，于是開(kāi)始不停的尋道不停的旋轉(zhuǎn)迄委。聚簇索引則只需一次I/O。（強(qiáng)烈的對(duì)比）
不過(guò)类少，如果涉及到大數(shù)據(jù)量的排序叙身、全表掃描、count之類的操作的話硫狞，還是MyISAM占優(yōu)勢(shì)些信轿，因?yàn)樗饕伎臻g小晃痴，這些操作是需要在內(nèi)存中完成的。

innodb與MyISAM對(duì)比

image

MyISAM主鍵索引的存儲(chǔ)結(jié)構(gòu)與innodb不同的是：

1. 主鍵索引樹(shù)的葉子結(jié)點(diǎn)的數(shù)據(jù)區(qū)域沒(méi)有存放實(shí)際的數(shù)據(jù)虏两，存放的是數(shù)據(jù)記錄的地址
2. 數(shù)據(jù)的存儲(chǔ)不是按主鍵順序存放的愧旦，按寫(xiě)入的順序存放的

也就是說(shuō)innodb引擎數(shù)據(jù)在物理上是按主鍵順序存放，而MyISAM引擎數(shù)據(jù)在物理上按插入的順序存放定罢。并且MyISAM的葉子結(jié)點(diǎn)不存放數(shù)據(jù)笤虫，所以與 非聚集索引的存儲(chǔ)結(jié)構(gòu)與聚集索引類似，在使用非聚集索引查找數(shù)據(jù)的時(shí)候通過(guò)非聚集索引樹(shù)就能直接找到數(shù)據(jù)的地址了祖凫，不需要回表琼蚯，這比innodb的搜索效率會(huì)更高呢！

引用自推文1

引用自推文2

文章

《高性能mysql》