01、索引到底是怎麼回事？
前面兩篇說到了索引原理即資料結構，同時還講到了其索引原則。那麼查詢一條SQL語句到底是怎麼執行的呢？或者說除了主鍵索引，其他的索引到底是怎麼一回事？今天的這篇主要任務就是理清這個關系，然後說說怎麼優化建立的索引？這個很重要，絕對對你的工作有很大幫助，希望能細心的體會，并且深入了解，那麼就開始吧。
先來了解概念，如下：
主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵也被稱為聚簇索引（clustered index）。
非主鍵索引的葉子節點内容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。
根據上面的索引結構說明，我們來讨論一個問題：基于主鍵索引和普通索引的查詢有什麼差別？
如果語句是 ，下面這條：
SELECT * from userinfo where id= 655;
即主鍵查詢方式，則隻需要搜尋 ID 這棵 B+ 樹；
如果語句是，下面這條：
SELECT * from userinfo where name= 'aaron';
即普通索引查詢方式，則需要先搜尋name 索引樹，得到 ID 的值為 655，再到 ID 索引樹搜尋一次。這個過程稱為回表。
也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。是以，我們在應用中應該盡量使用主鍵查詢。
索引維護
先從問題開始入手考慮，索引維護這個主題。
為什麼公司規定主鍵ID一般是自增長，且是整型？
性能方面考慮：
B+ 樹為了維護索引有序性，在插入新值的時候需要做必要的維護.如果插入新的行 ID 值為 700，則隻需要在 R5 的記錄後面插入一個新記錄。如果新插入的 的 ID 值為 400，就相對麻煩了。需要邏輯上挪動後面的資料，空出位置。
而更糟的情況是，如果 R5 所在的資料頁已經滿了，根據 B+ 樹的算法，這時候需要申請一個新的資料頁，然後挪動部分資料過去。這個過程稱為頁分裂。在這種情況下，性能自然會受影響。
當然有分裂就有合并。當相鄰兩個頁由于删除了資料，使用率很低之後，會将資料頁做合并。合并的過程，可以認為是分裂過程的逆過程。
自增主鍵的插入資料模式，正符合了我們前面提到的遞增插入的場景。每次插入一條新記錄，都是追加操作，也不會觸發葉子節點的分裂。而有業務邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫資料成本相對較高。
存儲方面考慮：
除了考慮性能外，我們還可以從存儲空間的角度來看。假設你的表中确實有一個唯一字段，比如字元串類型的身份證号，那應該用身份證号做主鍵，還是用自增字段做主鍵呢？
由于每個非主鍵索引的葉子節點上都是主鍵的值。如果用身份證号做主鍵，那麼每個二級索引的葉子節點占用約 20 個位元組，而如果用整型做主鍵，如果是長整型（bigint）則是 8 個位元組。
顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小。
是以，從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇。
有沒有什麼場景适合用業務字段直接做主鍵的呢？
比如，有些業務的場景需求是這樣的：
1.隻有一個索引；
2.該索引必須是唯一索引。
這就是典型的 KV 場景。直接将這個索引設定為主鍵，可以避免每次查詢需要搜尋兩棵樹。
02、高性能的索引政策
高效地選擇和使用索引有很多種方式，其中有些是針對特殊案列的優化方式，有些則是針對特定行為的優化。
1.獨立的列
是指索引列不能是表達式的一部分，也不能是函數的參數。
錯誤的方式，肉眼可以看出，但是mysql并不能識别出。
SELECT id FROM userinfo where id + 1 = 2;
正确的方式是：
SELECT id FROM userinfo where id = 1;
2.字首索引和索引選擇性
有時候需要索引很長的字元串，這會讓索引變得大且慢。一個政策是哈希索引。通常可以索引開始的部分字元，這樣可以大大節約索引空間，進而提高索引效果。但這樣會降低索引的選擇性。索引的選擇性是指，不重複的索引值(也稱為基數)和資料表的記錄總數(#T)的比值，範圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引讓mysql在查找時過濾掉更多的行。
3.多列索引
一個常見的錯誤就是，為每個列建立獨立的索引，或者按照錯誤的順序建立多列索引。
creat table t( c1 int, c2 int, c3 int, key(c1), key(c2), key(c3) );
把"where 條件裡的列都建上索引"，這個是有點錯誤的想法，這個可能隻是"一星"索引，其性能比起真正最優的索引可能差幾個數量級。有時如果無法設計一個"三星"索引，那麼不如忽略掉where子句，集中精力優化索引列的順序，或者建立一個全覆寫索引。進而，引出"索引合并"：
SELECT id FROM userinfo where age > 10 or sex ='M';
一般會使用全表掃描的，除非改寫成如下的兩個表查詢union的方式:
SELECT id FROM userinfo where age > 10 union all SELECT id FROM userinfo where sex ='M';
如果對 age和sex進行索引合并，還是一樣的查詢：
SELECT id FROM userinfo where age > 10 or sex ='M';
可能将會大大改善其結果。
索引合并政策有時候是一種優化的結果，但實際上更多時候說明了表上的索引建的很糟糕:
1.當出現伺服器對多個索引做相交操作時(通常有多個AND條件)，通常意味着需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。
2.當伺服器需要對多個索引做聯合操作時(通常有多個or條件)，通常需要耗費大量CPU和記憶體資源在算法的緩存、排序和合并操作上。特别是當其中有些索引的選擇性不高，需要合并掃描傳回的大量資料的時候。
4.選擇合适的索引列順序
在一個多列B-Tree索引中，索引列的順序意味着索引首先按照最左列進行排序，其次是第二列等等。是以，索引可以按照升序或者降序進行掃描。以滿足精确符合順序的order by、Group by 和 distinct 等子句的查詢需求。
當不需要考慮排序和分組時，将選擇性最高的列放在前面通常是很好的。這時候索引的作用隻是用于優化where條件的查找。在這種情況下，這樣的設計的索引确實能夠最快地過濾出需要的行，對于where子句中隻使用了索引部分字首的查詢來說選擇性也更高。然而，性能不隻是依賴于所有索引列的選擇性(整體基數)，也和查詢條件的具體值有關，也就是和值的分布有關。
那麼我們應該怎麼選擇組合索引字段的順序呢？
select * from userinfo where id = 2 and customer_id = 584;
是應該建立一個(id,customer_id)索引還是應該颠倒一下順序？可以跑一些查詢來确定在這個表中值的分布情況，并卻确定那個列的選擇性更高。
先使用sum函數來看看where條件的分支對應的資料基數有多大:
select sum(id = 2 ), sum (customer_id = 584) from userinfo ;
結果為:
sum(id = 2 ): 7992 ; sum (customer_id = 584) : 30
根據前面的法則，應該将索引列 customer_id 放到前面，因為對應條件customer_id 數量更小。最後，盡管關于選擇性和基數的經驗法則值得去研究和分析，但一定要記住别忘記了where子句的排序、分組和範圍條件等其他因素。
5.聚族索引
聚族索引并不是一種單獨的索引類型，而是一種資料存儲方式。當表有聚族索引時，它的資料行實際上存放在索引的葉子頁中。
InnoDB将通過主鍵聚族資料，如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隐式定義一個主鍵來作為聚族索引。InnoDB隻聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠。
優點：
1.可以把相關的資料儲存在一起。例如實作電子郵箱時，可以根據使用者id來聚集資料，這樣隻需要從磁盤讀取少數的資料頁就能擷取某個使用者的全部郵件。如果沒有使用聚族索引，則每封郵件都可能導緻一次磁盤i/o。
2.資料通路更快。
3.使用覆寫索引掃描的查詢可以直接使用頁節點中的主鍵值。
缺點：
1.聚族資料最大限度地提高了i/o密集型應用的性能，但如果資料全部都放在記憶體中，則通路的順序就沒那麼重要了，聚族索引也就沒什麼優勢了。
2.插入速度嚴重依賴插入順序。按照主鍵的順序插入是加載資料到InnoDB表中最快的方式。
3.更新聚族索引列的代價很高。
4.聚族索引可能導緻全表掃描行，尤其是行比較稀疏，或者由于頁分裂導緻資料不連續的時候。
建議：最好盡可能使用單調增加的聚族鍵的值來插入新行。這樣可以減少寫入的時候分頁相關操作。
6.覆寫索引
通常大家都會根據查詢的where條件來建立合适的索引，不過這隻是索引優化的一個方面。設計優秀的索引應該考慮到整個查詢，而不是單單是where條件部分。如果一個索引包含(或者說覆寫)所有需要查詢的字段的值，我們就從稱之為"覆寫索引".
優點：
1.索引條目通常遠小于資料行大小，是以如果隻需要讀取索引，那mysql就會極大地減少資料通路量。
2.因為索引是按照列值順序存儲的，是以對于i/o密集型的範圍查詢會比随機從磁盤讀取每一行資料的i/o要少得多。
3.由于InnoDB的聚族索引，覆寫索引對InnoDB表特别有用。InnoDB的二級索引在葉子節點中儲存了行的主鍵值，是以如果二級主鍵能夠覆寫查詢，則可以避免對主鍵索引的二次查詢。
發起一個被被覆寫的查詢，在EXPLAIN 的 Extra列可以看到"Using index" 的資訊。那就可以使用這個索引做覆寫索引。
注意：Extra列 的 "Usering where" 是不可以做索引的。有可能是字段使用了like %%。
EXPLAIN SELECT ua.account_id,ua.user_id from user_account ua
可以根據account_id,user_id 做覆寫索引，也可以根據where條件所有值做覆寫索引。
7.使用索引掃描來做排序
mysql有兩種方式可以生成有序的結果：通過排序操作；或者按照索引順序掃描。如果EXPLAIN 出來的type列的值為"index"，則說明mysql使用了索引掃描來做排序。
隻要當索引的列順序和order by子句的順序完全一緻，并且所有列的排序方向都一樣時，mysql才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則隻有當order by 子句引用的字段全部為第一個表時，才能使用索引做排序order by子句和查找型查詢的限制是一樣的。
對于有這樣一個表 rental 在列(rental_date,inventory_id,customer_id)建立這樣一個索引。
where rental_date='2019-04-05' order by inventory_id,customer_id ;
即使order by 子句不滿足索引的最左字首的要求，也可以用于查詢排序，這是因為索引的第一列被指定為一個常數。
where rental_date = '2019-04-05' order by inventory_id desc;
可以利用查詢為索引的第一列提供了常量條件，而使用第二列進行排序，将兩列組合在一起，就形成了索引的最左字首。
#下面這個也是最左字首索引： where rental_date = '2019-04-05' order by rental_date , inventory_id ; #下面是一些不能使用索引做排序的查詢： #使用了兩種不同的排序方向，但是索引列都是正序排序的： where rental_date = '2019-04-05' order by inventory_id desc , customer_id asc; #查詢的order by 子句中引用了一個不在索引中的列： where rental_date = '2019-04-05' order by inventory_id , staff_id; #查詢的where 和 order by 中的列無法組合成索引的最左字首： where rental_date = '2019-04-05' order by customer_id; #查詢在索引的第一列上是範圍條件，是以mysql無法使用索引的其餘列： where rental_date > '2019-04-05' order by inventory_id ,customer_id; #查詢在inventory_id 列上有多個條件，對于排序來說，這也是一種範圍查詢: where rental_date = '2019-04-05' and inventory_id in (1,2) order by customer_id ;
8. 備援和重複索引
mysql 允許在相同列上建立多個索引，mysql需要單獨維護重複的索引，并且優化器在優化查詢的時候也需要逐個地進行考慮，這會影響性能。重複索引是指在相同的列上按照相同的順序建立的相同類型的索引。應該避免這樣建立重複索引，發現以後也應該立即移除。
creat table test( ID int not null primary key, A int not null, UNIQUE(ID), index(ID))
這個在建立的時候，就已經建立了重複的索引。
大多數情況下都不需要備援索引，應該盡量擴充已有的索引而不是建立新索引。一般來說表中的索引越多插入速度會越慢(這個在項目中已經實驗過了，資料遷移項目)，同時增加新索引将會導緻insert / update / delete 等操作的速度變慢，特别是當新增索引後導緻達到了記憶體瓶頸的時候。
9.索引和鎖
索引可以讓查詢鎖定更少的行。如果你的查詢從不通路哪些不需要的行，那麼就會鎖定更少的行。首先，雖然InnoDB的行鎖效率很高，記憶體使用頁很少，但是鎖定行的時候，仍然會帶來額外開銷；其次，鎖定超過需要的行會增加鎖争用并減少并發性。
InnoDB隻有在通路行的時候才會對其加鎖。而索引能夠減少InnoDB通路的行數，進而減少鎖的數量。但隻有當InnoDB在存儲引擎層能夠過濾掉所有不需要的行時才有效。
實戰，案例一：
1. 支援多種過濾條件
比如有個表國家(country)列，這個列選擇性通常不高，但可能會查詢都會用到。sex的列選擇性肯定也很低，但也會在很多查詢中用到。是以考慮到使用的頻率，還是建議在建立不同組合索引的時候将(sex,country)列作為字首。
問題是，如果某個查詢不限制性别，那麼我們應該怎麼做？
可以通過在查詢條件中新增and sex in('m','f')來讓mysql選擇該索引。mysql能夠比對索引的最左字首。但如果列有太多的值，就會讓in()清單太長(這篇關于in語句與between對比文章)，這樣做久不太行了。
設計的原則：
考慮表上所有的選項。
當設計索引時，不要隻考慮需要哪些索引，還需要考慮對查詢進行優化。如果發現某些查詢需要建立新索引，但是這個索引會降低另一些查詢的效率，那麼應該想一下是否能優化原來的查詢。應該同時優化查詢和索引以找到最佳的平衡。
接下來，需要考慮其他常見的where 條件的組合，并需要了解哪些組合在沒有合适索引情況下會很慢。其實這個索引(sex,country)還可以加上(sex,country,region,age).
2.避免多個範圍條件
什麼是範圍查詢？
從explain的輸出很難區分mysql是要查詢範圍值。但是我們可以從值的範圍和多個等于條件來得出不同。
假設有這樣一個語句：
where eye_color in('blue') and hair_color in('black','red') and sex in('m','f') and last_online > DATE_SUB(NOW(),INTERVAL 7 DAY) and age between 18 and 20
這個查詢有一個問題，它有兩個範圍條件，last_online and age , mysql last_online 列索引 and age 列索引，但無法同時使用它們。這個是無法解決的，建議多建立幾個組合索引，但是也不要建立太多，索引建立太多，可能導緻插入很慢。
3.優化排序
例如，如果where 子句隻有sex 列，如何排序？
對于那些選擇性非常低的列，可以增加一些特殊的索引來做排序。 例如，可以建立(sex, rating) 索引用于下面的查詢：
select * from profiles where sex = 'm' order by rating limit 10;
如果需要翻頁：
select * from profiles where sex = 'm' order by rating limit 100000, 10;
無論如何建立索引，這種查詢都是個嚴重的問題。因為随着偏移量的增加，mysql 需要花費大量的時間來掃描需要丢棄的資料。反範式化，預先計算和緩存可能是解決這類查詢的僅有政策，一個更好的辦法是限制使用者能夠翻頁查詢的數量，實際上這對使用者體驗的影響不大，因為使用者很少真正在乎搜尋結果的第10000頁。
優化這類索引的另一個比較好的政策是使用延遲關聯，通過使用覆寫索引查詢傳回需要的主鍵，在根據主鍵關聯原表獲得需要的行。這可以減少mysql掃描哪些需要丢棄的行數。
如何高效的利用(sex, rating)索引進行排序和分頁：
select * from profiles inner join ( select (primary key) from profiles where x.sex = 'm' order by rating limit 100000, 10 ) as x using (primary key) ;
實戰演練二：如何給字元串建立索引？
現在，幾乎所有的系統都支援郵箱登入，如何在郵箱這樣的字段上建立合理的索引？
create table SUser( ID bigint unsigned primary key, email varchar(64) )engine=innodb;
一般的sql是這樣的：
select f1, f2 from SUser where email='xxx';
如果 email 這個字段上沒有索引，那麼這個語句就隻能做全表掃描。建立索引：
alter table SUser add index index1(email); #或 alter table SUser add index index2(email(6));
第一個語句建立的 index1 索引裡面，包含了每個記錄的整個字元串；而第二個語句建立的index2 索引裡面，對于每個記錄都是隻取前 6 個位元組。
那麼，這兩種不同的定義在資料結構和存儲上有什麼差別呢？
最大的差別是：存儲的資料變大。
email(6) 這個索引結構中每個郵箱字段都隻取前 6 個位元組，是以占用的空間會更小，這就是使用字首索引的優勢。但，這同時帶來的損失是，可能會增加額外的記錄掃描次數。
select id,name,email from SUser where email='[email protected]';
如果使用的是 index1（即 email 整個字元串的索引結構），執行順序是這樣的：
1.從 index1 索引樹找到滿足索引值是’[email protected]’的這條記錄，取得 ID2 的值；
2.到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正确的，将這行記錄加入結果集；
3.取 index1 索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足 email='[email protected]’的條件了，循環結束。
這個過程中，隻需要回主鍵索引取一次資料。
如果使用的是 index2（即 email(6) 索引結構），執行順序是這樣的：
1.從 index2 索引樹找到滿足索引值是’fanron’的記錄，找到的第一個是 ID1；
2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’[email protected]’，這行記錄丢棄；
3.取 index2 上剛剛查到的位置的下一條記錄，發現仍然是’fanron’，取出 ID2，再到 ID 索引上取整行然後判斷，這次值對了，将這行記錄加入結果集；
4.重複上一步，直到在 idxe2 上取到的值不是’fanron’時，循環結束。
在這個過程中，要回主鍵索引取 4 次資料，也就是掃描了 4 行。對于這個查詢語句來說，如果你定義的 index2而是 email(7），也就是說取 email 字段的前 7 個位元組來建構索引的話，即滿足字首’fanrong’的記錄隻有一個，也能夠直接查到 ID2，隻掃描一行就結束了。使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。
那麼問題來了，當要給字元串建立字首索引時，有什麼方法能夠确定我應該使用多長的字首呢？
實際上，我們在建立索引時關注的是區分度，區分度越高越好。因為區分度越高，意味着重複的鍵值越少。是以，我們可以通過統計索引上有多少個不同的值來判斷要使用多長的字首。
首先，你可以使用下面這個語句，算出這個列上有多少個不同的值：
select count(distinct email) as L from SUser; #依次選取不同長度的字首來看這個值，比如我們要看一下 4~7 個位元組的字首索引，可以用這個語句： select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7, from SUser;
字首索引對覆寫索引的影響：
select id,email from SUser where email='[email protected]'; #要求傳回 id 和 email 字段。 select id,name,email from SUser where email='[email protected]';
如果使用 index1（即 email 整個字元串的索引結構）的話，可以利用覆寫索引，從index1 查到結果後直接就傳回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即email(6) 索引結構）的話，就不得不回到 ID 索引再去判斷 email 字段的值。
也就是說，使用字首索引就用不上覆寫索引對查詢性能的優化了，這也是你在選擇是否使用字首索引時需要考慮的一個因素。
那麼有沒有更好的方式呢？其它方式：
比如，我們國家的身份證号，一共 18 位，其中前 6 位是位址碼，是以同一個縣的人的身份證号前 6 位一般會是相同的。按照我們前面說的方法，可能你需要建立長度為 12 以上的字首索引，才能夠滿足區分度要求。
但是，索引選取的越長，占用的磁盤空間就越大，相同的資料頁能放下的索引值就越少，搜尋的效率也就會越低。
如果我們能夠确定業務需求裡面隻有按照身份證進行等值查詢的需求還有沒有别的處理方法呢？
第一種方式是使用倒序存儲。如果你存儲身份證号的時候把它倒過來存，每次查詢的時候，你可以這麼寫：
select field_list from t where id_card = reverse('input_id_card_string');
第二種方式是使用 hash 字段。你可以在表上再建立一個整數字段，來儲存身份證的校驗碼，時在這個字段上建立索引。
alter table t add id_card_crc int unsigned, add index(id_card_crc);
然後每次插入新記錄的時候，都同時用 crc32() 這個函數得到校驗碼填到這個新字段。由于校驗碼可能存在沖突，也就是說兩個不同的身份證号通過 crc32() 函數得到的結果可能是相同的，是以你的查詢語句 where 部分要判斷 id_card 的值是否精确相同。
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
這樣，索引的長度變成了 4 個位元組，比原來小了很多。使用倒序存儲和使用 hash 字段這兩種方法的異同點。
首先，它們的相同點是，都不支援範圍查詢。倒序存儲的字段上建立的索引是按照倒序字元串的方式排序的，已經沒有辦法利用索引方式。
1.從占用的額外空間來看，倒序存儲方式在主鍵索引上不會消耗額外的存儲空間，而 hash 字段方法需要增加一個字段。當然，倒序存儲方式使用 4 個位元組的字首長度應該是不夠的，如果再長一點，這個消耗跟額外這個 hash 字段也差不多抵消了。
2.在 CPU 消耗方面，倒序方式每次寫和讀的時候，都需要額外調用一次 reverse 函數，而 hash字段的方式需要額外調用一次 crc32() 函數。如果隻從這兩個函數的計算複雜度來看的話，reverse 函數額外消耗的 CPU 資源會更小些。
3.從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩定一些。因為 crc32 算出來的值雖然有沖突的機率，但是機率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序存儲方式畢竟還是用的字首索引的方式也就是說還是會增加掃描行數。
總結：
1.直接建立完整索引，這樣可能比較占用空間；
2.建立字首索引，節省空間，但會增加查詢掃描次數，并且不能使用覆寫索引。
3.倒序存儲，再建立字首索引，用于繞過字元串本身字首的區度不夠的問題；
4.建立 hash 字段索引，查詢性能穩定，有額外的存儲和計算消算消耗，跟第三種方式一樣，都不支援範圍掃描。
03、選擇索引依據是什麼？
前面都是直接給出索引規則，這個應該怎麼操作，這個應該使用什麼索引。但是到底怎麼會選擇這個索引呢，主鍵索引？
假設你在維護一個市民系統，每個人都有一個唯一的身份證号，而且業務代碼已經保證了不會寫入兩個重複的身份證号。如果市民系統需要按照身份證号查姓名，就會執行類似這樣的 SQL 語
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
是以，你一定會考慮在 id_card 字段上建索引。由于身份證号字段比較大，我不建議你把身份證号當做主鍵，那麼現在你有兩個選擇，要麼給id_card 字段建立唯一索引，要麼建立一個普通索引。如果業務代碼已經保證了不會寫入重複的身份證号，那麼這兩個選擇邏輯上都是正确的。
1.從性能的角度考慮，你選擇唯一索引還是普通索引呢？選擇的依據是什麼呢？
第一個節點以後說了主鍵索引和普通索引的差別。假設，執行查詢的語句是：
SELECT * from userinfo where name='1000';
這個查詢語句在索引樹上查找的過程，先是通過 B+ 樹從樹根開始，按層搜尋到葉子節點，然後可以認為資料頁内部通過二分法來定位記錄。
對于普通索引來說，查找到滿足條件的第一個記錄 (1000,5000)後，需要查找下一個記錄，直到碰到第一個不滿足 id=5 條件的記錄。對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄後，就會停止繼續檢索。
那麼，這個不同帶來的性能差距會有多少呢？答案是，微乎其微。
InnoDB 的資料是按資料頁為機關來讀寫的。也就是說，當需要讀一條記錄的時候，并不是将這個記錄本身從磁盤讀出來，而是以頁為機關，将其整體讀入記憶體。在 InnoDB 中，每個資料頁的大小預設是 16KB。
因為引擎是按頁讀寫的，是以說，當找到 name='1000' 的記錄的時候，它所在的資料頁就都在記憶體裡了。那麼，對于普通索引來說，要多做的那一次“查找和判斷下一條記錄”的操作，就隻需要一次指針尋找和一次計算。
2.更新過程
當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一緻性的前提下，InooDB 會将這些更新操作緩存在change buffer 中，這樣就不需要從磁盤中讀入這個資料頁了。在下次查詢需要通路這個資料頁的時候，将資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正确性。
change buffer，實際上它是可以持久化的資料。也就是說，change buffer 在記憶體中有拷貝，也會被寫入到磁盤上。将 change buffer 中的操作應用到原資料頁，得到最新結果的過程稱為 merge。除了通路這個資料頁會觸發 merge 外，系統有背景線程會定期 merge。在資料庫正常關閉（shutdown）的過程中，也會執行 merge。
顯然，如果能夠将更新操作先記錄在 change buffer.，減少讀磁盤，語句的執行速度會得到明顯的提升。而且，資料讀入記憶體是需要占用 buffer pool的，是以這種方式還能夠避免占用記憶體，提高記憶體使用率。
2.1 什麼條件下可以使用 change buffer 呢？
對于唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性限制。比如，要插入(4,400) 這個記錄，就要先判斷現在表中是否已經存在 name=4 的記錄，而這必須要将資料頁讀入記憶體才能判斷。如果都已經讀入到記憶體了，那直接更新記憶體會更快，就沒必要使用 change buffe了。
是以，唯一索引的更新就不能使用 change buffer，實際上也隻有普通索引可以使用。再一起來看看如果要在這張表中插入一個新記錄 (4,400) 的話，InnoDB 的處理流程是怎樣的。
第一種情況是，這個記錄要更新的目标頁在記憶體中。這時，InnoDB 的處理流程如下：
1.對于唯一索引來說，找到 3 和 5 之間的位置，判斷到沒有沖突，插入這個值，語句執行結束；
2.對于普通索引來說，找到 3 和 5 之間的位置，插入這個值，語句執行結束。
第二種情況是，這個記錄要更新的目标頁不在記憶體中。這時，InnoDB 的處理流程如下：
對于唯一索引來說，需要将資料頁讀入記憶體，判斷到沒有沖突，插入這個值，語句執行結束；
對于普通索引來說，則是将更新記錄在 change buffe，語句執行就結束了。
change buffer 因為減少了随機磁盤通路，是以對更新性能的提升是會很明顯的。
2.2 change buffer 的使用場景
change buffer 隻限于用在普通索引的場景下，而不适用于唯一索引。
注意：是以，對于寫多讀少的業務來說，頁面在寫完以後馬上被通路到的機率比較小，此時 change buffer 的使用效果最好。這種業務模型常見的就是賬單類、日志類的系統。反過來，假設一個業務的更新模式是寫入之後馬上會做查詢，将更新先記錄在 change buffer，但之後由于馬上要通路這個資料頁，會立即觸發 merge 過程。這樣change buffer 反而起到了副作用。
redo log 主要節省的是随機寫磁盤的 IO 消耗（轉成順序寫），而 change buffer 主要節省的則是随機讀磁盤的 IO 消耗。
04、總結
在選擇索引和編寫利用這些索引的查詢時，有如下三個原則始終需要記住：
1.單通路時很慢的。特别是在機械鍵盤存儲中。如果伺服器從存儲中讀取一個資料塊隻是為了擷取其中一行，那麼久浪費了很多工作。最好讀取的塊中能包含盡可能多所需要的行。使用索引可以建立位置引用以提升效率。
2.按順序通路範圍資料是很快的，這有兩個原因。第一，順序I/O不需要多行磁盤尋道，是以比随機I/O要快很多。第二是，如果伺服器能夠按需要順序讀取資料，那麼就不再需要額外的排序操作，并且Group By 查詢也無須再做排序和将行按組進行聚合計算了。
3.索引覆寫查詢是快的。如果一個索引包含了查詢需要的所有列，那麼存儲引擎就不需要再回表查找行。這避免了大量的單行通路。
版權聲明：本文為CSDN部落客「weixin_33901843」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。
原文連結：https://blog.csdn.net/weixin_33901843/article/details/92260322