什么是索引？

在關(guān)系數(shù)據(jù)庫(kù)中，索引是一種單獨(dú)的、物理的對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種存儲(chǔ)結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁(yè)碼快速找到所需的內(nèi)容。

能實(shí)現(xiàn)快速定位數(shù)據(jù)的一種存儲(chǔ)結(jié)構(gòu)，其設(shè)計(jì)思想是以空間換時(shí)間。

索引的分類

按「數(shù)據(jù)結(jié)構(gòu)」分類：B+tree索引、Hash索引、Full-text索引。
按「物理存儲(chǔ)」分類：聚簇索引（主鍵索引）、二級(jí)索引（輔助索引）。
按「字段特性」分類：主鍵索引、唯一索引、普通索引、前綴索引。
按「字段個(gè)數(shù)」分類：?jiǎn)瘟兴饕?、?lián)合索引。

MySQL如何實(shí)現(xiàn)的索引機(jī)制

這個(gè)話題比較大，在MySQL中有不同的存儲(chǔ)引擎比如像InnoDB MyISAM Memory 等等,每一種存儲(chǔ)引擎在其內(nèi)部實(shí)現(xiàn)索引機(jī)制的原理也有所不同。在MySQL5.5之后默認(rèn)的就是InnoDB，并且是目前使用最廣泛的MySQL數(shù)據(jù)引擎，那我們就以InnoDB為例展開(kāi)講講。

?如果說(shuō)我們?cè)诒碇杏?00條數(shù)據(jù)，而我們要找出我們需要的數(shù)據(jù)，有哪些辦法？

• ? 我們是不是可以按照一種順序的方式一條一條往下去搜索，直到匹配到我們需要的數(shù)據(jù)，這是一種方案在時(shí)間復(fù)雜度上是O(N)，雖說(shuō)效率差但也能用。
• ? 二分查找法也是一種常用的比較高效的查詢算法，它的搜索效率為O(log(N)),雖說(shuō)查找效率是比順序查找高了不少，但是它有兩個(gè)前提條件，必須用順序存儲(chǔ)結(jié)構(gòu)比如數(shù)組，第二個(gè)是必須按照關(guān)鍵字進(jìn)行有序排序（從小到大）。
• ? 哈希查找，哈希查找的特性是能夠做到直接定址，其效率無(wú)限接近于O(1)，取決于沖突的數(shù)量。但是散列表數(shù)據(jù)是無(wú)序存儲(chǔ)的，排序要自己做，第二個(gè)是散列表還要擴(kuò)容耗時(shí)長(zhǎng)，遇到散列沖突性能不穩(wěn)定。
• ? B樹/B+樹查找的復(fù)雜度是O(log2(N)), 那么這也是InnoDB采用的數(shù)據(jù)結(jié)構(gòu)，在查找效率上的非常高的，算法具體的原理在后面介紹。

為什么InnoDB要使用B+樹作為索引結(jié)構(gòu)？

InnoDB的索引和MyISAM的索引有什么區(qū)別？

首先InnoDB和MyISAM都是使用的B+樹實(shí)現(xiàn)的，但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引，聚簇索引根據(jù)主鍵創(chuàng)建一顆B+樹，葉子節(jié)點(diǎn)則存放的是數(shù)據(jù)行記錄，也可以把葉子結(jié)點(diǎn)稱為數(shù)據(jù)頁(yè)。通俗點(diǎn)來(lái)說(shuō)就是把數(shù)據(jù)和索引存在同一個(gè)塊，找到了索引也就找到了數(shù)據(jù)。

• 因?yàn)槿~子結(jié)點(diǎn)將索引和數(shù)據(jù)放在一起，就決定了聚簇索引的唯一性，一張表里面只能有一個(gè)聚簇索引。
• InnoDB引擎默認(rèn)將主鍵設(shè)置為聚簇索引，但如果沒(méi)有設(shè)置主鍵，那么InnoDB將會(huì)選擇非空的唯一索引作為代替，如果沒(méi)有這樣的索引，InnoDB將會(huì)定一個(gè)隱式主鍵作為聚簇索引。
• 因?yàn)榫鄞厮饕厥獾奈锢斫Y(jié)構(gòu)所決定，葉子結(jié)點(diǎn)將索引和數(shù)據(jù)存放在一起，在獲取數(shù)據(jù)的速度上是比非聚簇索引快的。
• 聚簇索引數(shù)據(jù)的存儲(chǔ)是有序的，在進(jìn)行排序查找和范圍查找的速度也是非?？斓?。
• ?? 也正因?yàn)橛行蛐?，在?shù)據(jù)插入時(shí)按照主鍵的順序插入是最快的，否則就會(huì)出現(xiàn)頁(yè)分裂等問(wèn)題，嚴(yán)重影響性能。對(duì)于InnoDB我們一般采用自增作為主鍵ID。
• 第二個(gè)問(wèn)題主鍵最好不要進(jìn)行更新，修改主鍵的代價(jià)非常大，為了保持有序性會(huì)導(dǎo)致更新的行移動(dòng)，一般來(lái)說(shuō)我們通常設(shè)置為主鍵不可更新。

?在這部分只介紹InnoDB和MyISAM主鍵索引的不同？輔助索引后面在說(shuō)

而非聚簇索引是將索引和數(shù)據(jù)分開(kāi)存儲(chǔ)，那么在訪問(wèn)數(shù)據(jù)的時(shí)候就需要2次查找，但是和InnoDB的非聚簇部分還是有所區(qū)別。InnoDB是需要查找2次樹，先查找輔助索引樹，再查找聚簇索引樹（這個(gè)過(guò)程也叫回表）。而MyISAM的主鍵索引葉子結(jié)點(diǎn)的存儲(chǔ)的部分還是有所區(qū)別。InnoDB中存儲(chǔ)的是索引和聚簇索引ID，但是MyISAM中存儲(chǔ)的是索引和數(shù)據(jù)行的地址，只要定位就可以獲取到。

其實(shí)看到這個(gè)部分會(huì)有一個(gè)疑惑，那就是InnoDB的聚簇索引比MyISAM的主鍵快，那為什么會(huì)認(rèn)為MyISAM查詢效率比InnoDB快呢？

• 第一點(diǎn)，對(duì)于兩者存儲(chǔ)引擎的的性能分析不能只看主鍵索引，我們也要看看輔助索引，前頭我們介紹過(guò)InnoDB輔助索引會(huì)存在一個(gè)回表的過(guò)程。而MyISAM的輔助索引和主鍵索引的原理是一樣的，并沒(méi)有什么區(qū)別。
• (重點(diǎn)) InnoDB對(duì)MVCC的支持，事物是比較影響性能的，就算你沒(méi)用但是也省不了檢查和維護(hù)，而MyISAM這塊卻沒(méi)有這方面的影響，具體MVCC詳解將在后面章節(jié)描述。

如果一個(gè)表沒(méi)有主鍵索引那還會(huì)創(chuàng)建B+樹嗎？

答案是會(huì)的?。?！

InnoDB是MySQL中的一種存儲(chǔ)引擎，它會(huì)為每個(gè)表創(chuàng)建一個(gè)主鍵索引。如果表沒(méi)有明確的主鍵索引，InnoDB會(huì)使用一個(gè)隱藏的、自動(dòng)生成的主鍵來(lái)創(chuàng)建索引。這個(gè)隱藏的主鍵索引使用的就是B+樹結(jié)構(gòu)。因此，在InnoDB中，即使表沒(méi)有明確的主鍵索引，也會(huì)創(chuàng)建一個(gè)B+樹索引。

索引的優(yōu)缺點(diǎn)是什么？

數(shù)據(jù)是存儲(chǔ)在磁盤上的，操作系統(tǒng)讀取磁盤的最小單位是塊，如果沒(méi)有索引，會(huì)加載所有的數(shù)據(jù)到內(nèi)存，依次進(jìn)行檢索，加載的總數(shù)據(jù)會(huì)很多，磁盤IO多。

如果有了索引，會(huì)以學(xué)號(hào)為key創(chuàng)建索引，MySQL采用B+樹結(jié)構(gòu)存儲(chǔ)，一方面加載的數(shù)據(jù)只有學(xué)號(hào)和主鍵ID，另一方便采用了多叉平衡樹，定位到指定學(xué)號(hào)會(huì)很快，根據(jù)關(guān)聯(lián)的ID可以快速定位到對(duì)應(yīng)行的數(shù)據(jù)，所以檢索的速度會(huì)很快，因?yàn)榧虞d的總數(shù)據(jù)很少，磁盤IO少。

可見(jiàn)，索引可以大大減少檢索數(shù)據(jù)的范圍、減少磁盤IO，使查詢速度很快，因?yàn)榇疟PIO是很慢的，是由它的硬件結(jié)構(gòu)決定的。

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

• 索引能夠提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本。
• 通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性，創(chuàng)建唯一索引
• 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間
• 加速兩個(gè)表之間的連接，一般是在外鍵上創(chuàng)建索引

? 缺點(diǎn)

• 需要占用物理空間,建立的索引越多需要的空間越大
• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加
• 會(huì)降低表的增刪改的效率，因?yàn)槊看卧鰟h改索引需要進(jìn)行動(dòng)態(tài)維護(hù)，導(dǎo)致時(shí)間變長(zhǎng)

使用索引一定能提升效率嗎？（什么時(shí)候適合創(chuàng)建索引，什么時(shí)候不適合創(chuàng)建索引？）

答案是不一定，任何事物我們都應(yīng)該辯證的看，知道其運(yùn)行邏輯從而利用其優(yōu)點(diǎn)，盡量避開(kāi)它的缺點(diǎn)。在上面我們已經(jīng)和大家介紹了過(guò)了索引帶來(lái)的優(yōu)缺點(diǎn)，那接下來(lái)就和大家分享幾個(gè)建索引的提示。

• 對(duì)于查詢中使用的少的字段盡量不要?jiǎng)?chuàng)建索引，創(chuàng)建索引是有成本的，空間占用、創(chuàng)建和維護(hù)成本、增刪改效率降低。
• 對(duì)于數(shù)據(jù)密度小的列也不建議創(chuàng)建索引，因?yàn)镮nnoDB中索引的B+樹所決定的，你能帶來(lái)的效率提升非常有限。（但是也有例外，舉個(gè)例子枚舉值（1，2，3），頭兩個(gè)占比百分之1%，第三個(gè)占比99%，并且頭兩個(gè)搜索占比比第三個(gè)高很多，那么是可以建議加索引的）。InnoDB的輔助索引是存在回表的，如果數(shù)據(jù)密度過(guò)小，那么性能可能還不如全表掃。像上面這種場(chǎng)景具有特殊性，也說(shuō)明一個(gè)道理，在大多數(shù)場(chǎng)景下建議可能適用，但是也有不適用的時(shí)候，我們不要把這種建議當(dāng)作鐵律。

如何查看一個(gè)表的索引？

?? 上代碼 ??

show index from table_name (表名)

有哪些情況會(huì)導(dǎo)致索引失效？

這個(gè)問(wèn)題要分版本回答?。?！版本不同可能會(huì)導(dǎo)致索引失效的場(chǎng)景也不同，直接給答案的都是耍流氓?。。?/p>

這里回答基于最新MySQL8版本，MySQL8失效的以前版本也失效，MySQL8不失效的，以前可能會(huì)失效。

• 使用like并且是左邊帶%, 右邊可以帶會(huì)走索引（但是并不絕對(duì)，詳細(xì)解釋看下面like專題分析）
• 隱式類型轉(zhuǎn)換，索引字段與條件或關(guān)聯(lián)字段的類型不一致。（比如你的字段是int，你用字符串方式去查詢會(huì)導(dǎo)致索引失效）。
• 在where條件里面對(duì)索引列使用運(yùn)算或者使用函數(shù)。
• 使用OR且存在非索引列
• 在where條件中兩列做比較會(huì)導(dǎo)致索引失效
• 使用IN可能不會(huì)走索引（MySQL環(huán)境變量eq_range_index_dive_limit的值對(duì)IN語(yǔ)法有很大影響，該參數(shù)表示使用索引情況下IN中參數(shù)的最大數(shù)量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默認(rèn)值為10，之后的版本默認(rèn)值為200。我們拿MySQL8.0.19舉例，eq_range_index_dive_limit=200表示當(dāng)IN (...)中的值 >200個(gè)時(shí)，該查詢一定不會(huì)走索引。<=200則可能用到索引。）
• 使用非主鍵范圍條件查詢時(shí)，部分情況索引失效 。
• 使用order by可能會(huì)導(dǎo)致索引失效
• is null is not null ≠ 可能會(huì)導(dǎo)致索引失效

如果表中有字段為NULL 索引是否會(huì)失效？

首先講答案不一定。即使我們使用is null 或者is not null 它其實(shí)都是會(huì)走索引的。那為什么會(huì)有這樣的言論呢？這里首先就得來(lái)講講NULL值是怎么在記錄中存儲(chǔ)的，又是怎么在B+樹中存儲(chǔ)的呢。

那么在InnoDB中分為聚簇索引和非聚簇索引兩種，聚簇索引本身是不允許記錄為空的，所以可以不不用考慮，那么就剩下非聚簇索引也就是我們的輔助索引。

那既然IS NULL、IS NOT NULL、!=這些條件都可能使用到索引，那到底什么時(shí)候索引，什么時(shí)候采用全表掃描呢？

首先我們得知道兩個(gè)東西，第一個(gè)在InnoDB引擎是如何存儲(chǔ)NULL值的，第二個(gè)問(wèn)題是索引是如何存儲(chǔ)NULL值的，這樣我們才能從根上理解NULL在什么場(chǎng)景走索引，在什么場(chǎng)景不走索引。

1?? 在InnoDB引擎是如何存儲(chǔ)NULL值的？

InnoDB引擎通過(guò)使用一個(gè)特殊的值來(lái)表示null，這個(gè)值通常被稱為"null bitmap"。null bitmap是一個(gè)二進(jìn)制位序列，用來(lái)標(biāo)記表中每一個(gè)列是否為null。當(dāng)null bitmap中對(duì)應(yīng)的位為1時(shí)，表示對(duì)應(yīng)的列為null；當(dāng)null bitmap中對(duì)應(yīng)的位為0時(shí)，表示對(duì)應(yīng)的列不為null。在實(shí)際存儲(chǔ)時(shí)，InnoDB引擎會(huì)將null bitmap作為行記錄的一部分，存儲(chǔ)在行記錄的開(kāi)頭，這樣可以在讀取行記錄時(shí)快速判斷每個(gè)列是否為null。

從頭開(kāi)始說(shuō)理解起來(lái)會(huì)比較容易，理解了獨(dú)占表空間文件就更容易理解行格式了，接著往下看：

當(dāng)我們創(chuàng)建表的時(shí)候默認(rèn)會(huì)創(chuàng)建一個(gè)*.idb 文件，這個(gè)文件又稱為獨(dú)占表空間文件，它是由段、區(qū)、頁(yè)、行組成。InnoDB存儲(chǔ)引擎獨(dú)占表空間大致如下圖；

Segment(表空間) 是由各個(gè)段（segment）組成的，段是由多個(gè)區(qū)（extent）組成的。段一般分為數(shù)據(jù)段、索引段和回滾段等。

• 數(shù)據(jù)段 存放 B + 樹的葉子節(jié)點(diǎn)的區(qū)的集合
• 索引段 存放 B + 樹的非葉子節(jié)點(diǎn)的區(qū)的集合
• 回滾段 存放的是回滾數(shù)據(jù)的區(qū)的集合， MVCC就是利用了回滾段實(shí)現(xiàn)了多版本查詢數(shù)據(jù)

Extent(區(qū)) 在表中數(shù)據(jù)量大的時(shí)候，為某個(gè)索引分配空間的時(shí)候就不再按照頁(yè)為單位分配了，而是按照區(qū)（extent）為單位分配。每個(gè)區(qū)的大小為 1MB，對(duì)于 16KB 的頁(yè)來(lái)說(shuō)，連續(xù)的 64 個(gè)頁(yè)會(huì)被劃為一個(gè)區(qū)，這樣就使得鏈表中相鄰的頁(yè)的物理位置也相鄰，就能使用順序 I/O 了 。

（我們知道 InnoDB 存儲(chǔ)引擎是用 B+ 樹來(lái)組織數(shù)據(jù)的。B+ 樹中每一層都是通過(guò)雙向鏈表連接起來(lái)的，如果是以頁(yè)為單位來(lái)分配存儲(chǔ)空間，那么鏈表中相鄰的兩個(gè)頁(yè)之間的物理位置并不是連續(xù)的，可能離得非常遠(yuǎn)，那么磁盤查詢時(shí)就會(huì)有大量的隨機(jī)I/O，隨機(jī) I/O 是非常慢的。解決這個(gè)問(wèn)題也很簡(jiǎn)單，就是讓鏈表中相鄰的頁(yè)的物理位置也相鄰，這樣就可以使用順序 I/O 了，那么在范圍查詢（掃描葉子節(jié)點(diǎn)）的時(shí)候性能就會(huì)很高。）

Page(頁(yè)) 記錄是按照行來(lái)存儲(chǔ)的，但是數(shù)據(jù)庫(kù)的讀取并不以「行」為單位，否則一次讀?。ㄒ簿褪且淮?I/O 操作）只能處理一行數(shù)據(jù)，效率會(huì)非常低。

因此，InnoDB 的數(shù)據(jù)是按「頁(yè)」為單位來(lái)讀寫的，也就是說(shuō)，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)行記錄從磁盤讀出來(lái)，而是以頁(yè)為單位，將其整體讀入內(nèi)存。

默認(rèn)每個(gè)頁(yè)的大小為 16KB，也就是最多能保證 16KB 的連續(xù)存儲(chǔ)空間。

頁(yè)是 InnoDB 存儲(chǔ)引擎磁盤管理的最小單元，意味著數(shù)據(jù)庫(kù)每次讀寫都是以 16KB 為單位的，一次最少?gòu)拇疟P中讀取 16K 的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的 16K 內(nèi)容刷新到磁盤中。

頁(yè)的類型有很多，常見(jiàn)的有數(shù)據(jù)頁(yè)、undo 日志頁(yè)、溢出頁(yè)等等。數(shù)據(jù)表中的行記錄是用「數(shù)據(jù)頁(yè)」來(lái)管理的，數(shù)據(jù)頁(yè)的結(jié)構(gòu)這里我就不講細(xì)說(shuō)了，總之知道表中的記錄存儲(chǔ)在「數(shù)據(jù)頁(yè)」里面就行。

Row(行) 數(shù)據(jù)庫(kù)表中的記錄都是按行（row）進(jìn)行存放的，每行記錄根據(jù)不同的行格式，有不同的存儲(chǔ)結(jié)構(gòu)。

重點(diǎn)來(lái)了！??！

InnoDB 提供了 4 種行格式，分別是 Redundant、Compact、Dynamic和 Compressed 行格式。

• Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，現(xiàn)在基本沒(méi)人用了，那就不展開(kāi)詳講了。
• MySQL 5.0 之后引入了 Compact 行記錄存儲(chǔ)方式，由于 Redundant 不是一種緊湊的行格式，而采用更為緊湊的Compact ，設(shè)計(jì)的初衷就是為了讓一個(gè)數(shù)據(jù)頁(yè)中可以存放更多的行記錄，從 MySQL 5.1 版本之后，行格式默認(rèn)設(shè)置成 Compact。
• Dynamic 和 Compressed 兩個(gè)都是緊湊的行格式，它們的行格式都和 Compact 差不多，因?yàn)槎际腔?nbsp;Compact 改進(jìn)一點(diǎn)東西。從 MySQL5.7 版本之后，默認(rèn)使用 Dynamic 行格式。

那么我們來(lái)看看Compact里面長(zhǎng)什么樣，先混個(gè)臉熟。

這里簡(jiǎn)單介紹一下，Compact行格式其他內(nèi)容后面單獨(dú)出一個(gè)章節(jié)介紹。

• NULL值列表（本問(wèn)題介紹重點(diǎn)）

• 表中的某些列可能會(huì)存儲(chǔ) NULL 值，如果把這些 NULL 值都放到記錄的真實(shí)數(shù)據(jù)中會(huì)比較浪費(fèi)空間，所以 Compact 行格式把這些值為 NULL 的列存儲(chǔ)到 NULL值列表中。如果存在允許 NULL 值的列，則每個(gè)列對(duì)應(yīng)一個(gè)二進(jìn)制位（bit），二進(jìn)制位按照列的順序逆序排列。
• 二進(jìn)制位的值為1時(shí)，代表該列的值為NULL。二進(jìn)制位的值為0時(shí)，代表該列的值不為NULL。另外，NULL 值列表必須用整數(shù)個(gè)字節(jié)的位表示（1字節(jié)8位），如果使用的二進(jìn)制位個(gè)數(shù)不足整數(shù)個(gè)字節(jié)，則在字節(jié)的高位補(bǔ) 0。
• 當(dāng)然NULL 值列表也不是必須的。當(dāng)數(shù)據(jù)表的字段都定義成 NOT NULL 的時(shí)候，這時(shí)候表里的行格式就不會(huì)有 NULL 值列表了。所以在設(shè)計(jì)數(shù)據(jù)庫(kù)表的時(shí)候，通常都是建議將字段設(shè)置為 NOT NULL，這樣可以節(jié)省 1 字節(jié)的空間（NULL 值列表占用 1 字節(jié)空間）。
• 「NULL 值列表」的空間不是固定 1 字節(jié)的。當(dāng)一條記錄有 9 個(gè)字段值都是 NULL，那么就會(huì)創(chuàng)建 2 字節(jié)空間的「NULL 值列表」，以此類推。

2?? 索引是如何存儲(chǔ)NULL值的？

我們知道InnoDB引擎中按照物理存儲(chǔ)的不同分為聚簇索引和非聚簇索引，聚簇索引也就是主鍵索引，那么是不允許為空的。那就不再我們本問(wèn)題的討論范圍，我們重點(diǎn)來(lái)看看非聚簇索引，非聚簇索引是允許值為空的。

在InnoDB中非聚簇索引是通過(guò)B+樹的方式進(jìn)行存儲(chǔ)的

從圖中可以看出，對(duì)于s1表的二級(jí)索引idx_key1來(lái)說(shuō)，值為NULL的二級(jí)索引記錄都被放在了B+樹的最左邊，這是因?yàn)樵O(shè)計(jì)InnoDB的大叔有這樣的規(guī)定：

We define the SQL null to be the smallest possible value of a field.

也就是說(shuō)他們把SQL中的NULL值認(rèn)為是列中最小的值。在通過(guò)二級(jí)索引idx_key1對(duì)應(yīng)的B+樹快速定位到葉子節(jié)點(diǎn)中符合條件的最左邊的那條記錄后，也就是本例中id值為521的那條記錄之后，就可以順著每條記錄都有的next_record屬性沿著由記錄組成的單向鏈表去獲取記錄了，直到某條記錄的key1列不為NULL。

3?? 我們了解了上面的兩個(gè)問(wèn)題之后，我們就可以來(lái)看看，使不使用索引的依據(jù)是什么了

實(shí)際上來(lái)說(shuō)我們用is null is not null ≠ 這些條件都是能走索引的，那什么時(shí)候走索引什么時(shí)候走全表掃描呢？

總結(jié)起來(lái)就是兩個(gè)字：成本！??！

如何去度量成本計(jì)算使用某個(gè)索引執(zhí)行查詢的成本就非常復(fù)雜了，展開(kāi)講這個(gè)話題就停不下來(lái)了，后面考慮單獨(dú)列一個(gè)篇幅去講。

這里總結(jié)性講講：第一個(gè)，讀取二級(jí)索引記錄的成本，第二，將二級(jí)索引記錄執(zhí)行回表操作，也就是到聚簇索引中找到完整的用戶記錄操作所付出的成本。

要掃描的二級(jí)索引記錄條數(shù)越多，那么需要執(zhí)行的回表操作的次數(shù)也就越多，達(dá)到了某個(gè)比例時(shí)，使用二級(jí)索引執(zhí)行查詢的成本也就超過(guò)了全表掃描的成本（舉一個(gè)極端的例子，比方說(shuō)要掃描的全部的二級(jí)索引記錄，那就要對(duì)每條記錄執(zhí)行一遍回表操作，自然不如直接掃描聚簇索引來(lái)的快）

所以MySQL優(yōu)化器在真正執(zhí)行查詢之前，對(duì)于每個(gè)可能使用到的索引來(lái)說(shuō)，都會(huì)預(yù)先計(jì)算一下需要掃描的二級(jí)索引記錄的數(shù)量，比方說(shuō)對(duì)于下邊這個(gè)查詢：

SELECT * FROM s1 WHERE key1 IS NULL;

優(yōu)化器會(huì)分析出此查詢只需要查找key1值為NULL的記錄，然后訪問(wèn)一下二級(jí)索引idx_key1，看一下值為NULL的記錄有多少（如果符合條件的二級(jí)索引記錄數(shù)量較少，那么統(tǒng)計(jì)結(jié)果是精確的，如果太多的話，會(huì)采用一定的手段計(jì)算一個(gè)模糊的值，當(dāng)然算法也比較麻煩，我們就不展開(kāi)說(shuō)了），這種在查詢真正執(zhí)行前優(yōu)化器就率先訪問(wèn)索引來(lái)計(jì)算需要掃描的索引記錄數(shù)量的方式稱之為index dive。當(dāng)然，對(duì)于某些查詢，比方說(shuō)WHERE子句中有IN條件，并且IN條件中包含許多參數(shù)的話，比方說(shuō)這樣：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

這樣的話需要統(tǒng)計(jì)的key1值所在的區(qū)間就太多了，這樣就不能采用index dive的方式去真正的訪問(wèn)二級(jí)索引idx_key1，而是需要采用之前在背地里產(chǎn)生的一些統(tǒng)計(jì)數(shù)據(jù)去估算匹配的二級(jí)索引記錄有多少條（很顯然根據(jù)統(tǒng)計(jì)數(shù)據(jù)去估算記錄條數(shù)比index dive的方式精確性差了很多）。

反正不論采用index dive還是依據(jù)統(tǒng)計(jì)數(shù)據(jù)估算，最終要得到一個(gè)需要掃描的二級(jí)索引記錄條數(shù)，如果這個(gè)條數(shù)占整個(gè)記錄條數(shù)的比例特別大，那么就趨向于使用全表掃描執(zhí)行查詢，否則趨向于使用這個(gè)索引執(zhí)行查詢。

理解了這個(gè)也就好理解為什么在WHERE子句中出現(xiàn)IS NULL、IS NOT NULL、!=這些條件仍然可以使用索引，本質(zhì)上都是優(yōu)化器去計(jì)算一下對(duì)應(yīng)的二級(jí)索引數(shù)量占所有記錄數(shù)量的比值而已。

大家可以看到，MySQL中決定使不使用某個(gè)索引執(zhí)行查詢的依據(jù)很簡(jiǎn)單：就是成本夠不夠小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件。大家以后也多多辟謠吧，沒(méi)那么復(fù)雜，只是一個(gè)成本而已。

為什么LIKE以%開(kāi)頭索引會(huì)失效？

首先看看B+樹是如何查找數(shù)據(jù)的：

查找數(shù)據(jù)時(shí)，MySQL會(huì)從根節(jié)點(diǎn)開(kāi)始，按照從左到右的順序比較查詢條件和節(jié)點(diǎn)中的鍵值。如果查詢條件小于節(jié)點(diǎn)中的鍵值，則跳到該節(jié)點(diǎn)的左子節(jié)點(diǎn)繼續(xù)查找；如果查詢條件大于節(jié)點(diǎn)中的鍵值，則跳到該節(jié)點(diǎn)的右子節(jié)點(diǎn)繼續(xù)查找；如果查詢條件等于節(jié)點(diǎn)中的鍵值，則繼續(xù)查找該節(jié)點(diǎn)的下一個(gè)節(jié)點(diǎn)。

比如說(shuō)我有下面這條SQL：

select * from `user` where nickname like '%冥';

如果數(shù)據(jù)庫(kù)中存在南冥 北冥 西冥 東冥 ，那么在B+樹中搜索的效率和全表掃描還有什么區(qū)別呢？

我走聚簇索引全表掃描還不用回表。

最后在擴(kuò)展講一個(gè)點(diǎn)，其實(shí)不一定會(huì)導(dǎo)致索引失效。舉個(gè)例子：

create table `user`(
  id int primary key auto_increment,
  name varchar(20),
  index idx_name(name),
);

// 那么這種情況是會(huì)走索引的。
select id,name from `user` where name like '%冥';

為什么說(shuō)上面的例子會(huì)走索引呢？

首先我們需要查詢的id name 這兩個(gè)字段是不是都在我們的輔助索引中，葉子節(jié)點(diǎn)是不是存的索引值和主鍵值，所以我們只要查輔助索引就可以直接拿到我們的需要的結(jié)果了，那么這個(gè)叫做索引覆蓋。我們觀察執(zhí)行計(jì)劃會(huì)發(fā)現(xiàn)它的查詢級(jí)別是index ，其實(shí)也是全表遍歷了輔助索引。

第二個(gè)問(wèn)題來(lái)了，那為什么就要走輔助索引而不是走全表掃描呢？

因?yàn)檩o助索引中記錄的東西比主鍵索引少了很多，只有索引值和主鍵值，但是主鍵索引中就包含了，其他值、事物ID、MVCC的回流指針等等。再加上索引覆蓋不用回表，優(yōu)化器就認(rèn)為直接遍歷輔助索引的效率高于主鍵索引。

什么是索引覆蓋？

索引覆蓋（Index Covering）是指通過(guò)在索引中包含所有查詢語(yǔ)句中所需的列，可以避免對(duì)表中的數(shù)據(jù)進(jìn)行額外的訪問(wèn)，從而提高查詢效率。(避免了回表操作)

例如，對(duì)于一個(gè)查詢語(yǔ)句：

SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

如果在table表中建立了一個(gè)索引，包含col1、col2和col3三列，那么MySQL可以通過(guò)索引定位到符合條件的數(shù)據(jù)，并在索引中提取col1、col2和col3列的值，無(wú)需對(duì)表中的數(shù)據(jù)進(jìn)行額外的訪問(wèn)。這種方式就叫做索引覆蓋。

索引覆蓋能夠顯著提高查詢效率，因此在建立索引時(shí)應(yīng)盡量考慮包含查詢語(yǔ)句中所需的所有列。

什么是聚簇索引？

聚簇索引是一種特殊的索引，它將數(shù)據(jù)存儲(chǔ)在索引樹的葉子節(jié)點(diǎn)上。這種索引方式的優(yōu)點(diǎn)是，在查詢數(shù)據(jù)時(shí)可以減少一次查詢，因?yàn)椴樵兯饕龢涞耐瑫r(shí)就能獲取到數(shù)據(jù)。聚簇索引的缺點(diǎn)是，因?yàn)閿?shù)據(jù)存儲(chǔ)在索引樹中，所以對(duì)數(shù)據(jù)進(jìn)行修改或刪除操作時(shí)需要更新索引樹，這會(huì)增加系統(tǒng)的開(kāi)銷。

聚簇索引與非聚集索引的特點(diǎn)是什么？

在InnoDB中聚簇索引和非聚簇索引實(shí)際上是物理空間存儲(chǔ)方式的一個(gè)不同。

聚簇索引

1. 聚簇索引將數(shù)據(jù)存儲(chǔ)在索引樹的葉子節(jié)點(diǎn)上。
2. 聚簇索引可以減少一次查詢，因?yàn)椴樵兯饕龢涞耐瑫r(shí)就能獲取到數(shù)據(jù)。
3. 聚簇索引的缺點(diǎn)是，對(duì)數(shù)據(jù)進(jìn)行修改或刪除操作時(shí)需要更新索引樹，會(huì)增加系統(tǒng)的開(kāi)銷。
4. 聚簇索引通常用于數(shù)據(jù)庫(kù)系統(tǒng)中，主要用于提高查詢效率。

非聚簇索引（又稱二級(jí)索引 / 輔助索引）

1. 非聚簇索引不將數(shù)據(jù)存儲(chǔ)在索引樹的葉子節(jié)點(diǎn)上，而是存儲(chǔ)在數(shù)據(jù)頁(yè)中。
2. 非聚簇索引在查詢數(shù)據(jù)時(shí)需要兩次查詢，一次查詢索引樹，獲取數(shù)據(jù)頁(yè)的地址，再通過(guò)數(shù)據(jù)頁(yè)的地址查詢數(shù)據(jù)（通常情況下來(lái)說(shuō)是的，但如果索引覆蓋的話實(shí)際上是不用回表的）。
3. 非聚簇索引的優(yōu)點(diǎn)是，對(duì)數(shù)據(jù)進(jìn)行修改或刪除操作時(shí)不需要更新索引樹，減少了系統(tǒng)的開(kāi)銷。
4. 非聚簇索引通常用于數(shù)據(jù)庫(kù)系統(tǒng)中，主要用于提高數(shù)據(jù)更新和刪除操作的效率。

聚簇索引與非聚簇索引b+樹實(shí)現(xiàn)有什么區(qū)別？

結(jié)合“聚簇索引與非聚集索引的特點(diǎn)是什么？”加上下圖就明白了

一個(gè)表中可以有多個(gè)（非）聚簇索引嗎？

可以，這題容易混淆聚簇和非聚簇，聚簇只能有一個(gè)，但是非聚簇可以有很多，因?yàn)榫鄞厥呛蛿?shù)據(jù)存放在一起的，但是非聚簇是單獨(dú)的。（同時(shí)這題可以結(jié)合上面兩個(gè)問(wèn)題回答）

非聚簇索引為什么不存數(shù)據(jù)地址值而存儲(chǔ)主鍵？

我們知道在MyISAM引擎中是沒(méi)有聚簇索引，都是存的輔助索引。但是和InnoDB不同的是存儲(chǔ)的，它是存儲(chǔ)索引值和數(shù)據(jù)地址，而我們InnoDB中存儲(chǔ)的是主鍵ID。

我們要記住知道一個(gè)點(diǎn)，數(shù)據(jù)是會(huì)不斷變動(dòng)的，那么它的一個(gè)地址也是會(huì)跟著不斷變動(dòng)，如果直接存儲(chǔ)地址，下次找到的數(shù)據(jù)可能就不是原來(lái)的數(shù)據(jù)了。如果要解決這個(gè)問(wèn)題的話，成本是非常高的。每次數(shù)據(jù)變動(dòng)都需要進(jìn)行調(diào)整。

一個(gè)b+樹中大概能存放多少條索引記錄？

什么是Hash索引？

哈希索引（hash index）基于哈希表實(shí)現(xiàn)。哈希索引通過(guò)Hash算法將數(shù)據(jù)庫(kù)的索引列數(shù)據(jù)轉(zhuǎn)換成定長(zhǎng)的哈希碼作為key，將這條數(shù)據(jù)的行的地址作為value一并存入Hash表的對(duì)應(yīng)位置。

在MySQL中，只有Memeory引擎顯式的支持哈希索引，這也是Memory引擎表的默認(rèn)索引結(jié)構(gòu)，Memeory同時(shí)也支持B-Tree索引。并且，Memory引擎支持非唯一哈希索引，如果多個(gè)列的哈希值相同（或者發(fā)生了Hash碰撞），索引會(huì)在對(duì)應(yīng)Hash鍵下以鏈表形式存儲(chǔ)多個(gè)記錄地址。

哈希索引還有如下特點(diǎn)：

• 哈希索引不支持部分索引列的匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來(lái)計(jì)算哈希值的。例如，在數(shù)據(jù)列（A，B）上建立哈希索引，如果查詢只有數(shù)據(jù)列A，則無(wú)法使用該索引。
• 哈希索引具有哈希表的特性，因此只有精確匹配所有列的查詢對(duì)于哈希索引才有效，比如=、<>、IN(，因?yàn)閿?shù)據(jù)的存儲(chǔ)是無(wú)序的)，且無(wú)法使用任何范圍查詢。
• 因?yàn)閿?shù)據(jù)的存儲(chǔ)是無(wú)序的，哈希索引還無(wú)法用于排序。
• 對(duì)于精確查詢，則哈希索引效率很高，時(shí)間復(fù)雜度為O(1)，除非有很多哈希沖突（不同的索引列有相同的哈希值），如果發(fā)生哈希沖突，則存儲(chǔ)引擎必須遍歷鏈表中的所有數(shù)據(jù)指針，逐行比較，直到找到所有符合條件的行。哈希沖突越多，代價(jià)就越大！

InnoDB到底支不支持哈希索引？

對(duì)于InnoDB的哈希索引，確切的應(yīng)該這么說(shuō)：

• InnoDB用戶無(wú)法手動(dòng)創(chuàng)建哈希索引，這一層上說(shuō)，InnoDB確實(shí)不支持哈希索引;
• InnoDB會(huì)自調(diào)優(yōu)(self-tuning)，如果判定建立自適應(yīng)哈希索引(Adaptive Hash Index, AHI)，能夠提升查詢效率，InnoDB自己會(huì)建立相關(guān)哈希索引，這一層上說(shuō)，InnoDB又是支持哈希索引的;

那什么是自適應(yīng)哈希索引(Adaptive Hash Index, AHI)呢?

1、自適應(yīng)即我們不需要自己處理，當(dāng)InnoDB引擎根據(jù)查詢統(tǒng)計(jì)發(fā)現(xiàn)某一查詢滿足hash索引的數(shù)據(jù)結(jié)構(gòu)特點(diǎn)，就會(huì)給其建立一個(gè)hash索引；

2、hash索引底層的數(shù)據(jù)結(jié)構(gòu)是散列表（Hash表），其數(shù)據(jù)特點(diǎn)就是比較適合在內(nèi)存中使用，自適應(yīng)Hash索引存在于InnoDB架構(gòu)中的緩存中（不存在于磁盤架構(gòu)中）.

什么是索引下推？

索引下推（INDEX CONDITION PUSHDOWN，簡(jiǎn)稱 ICP）是在 MySQL 5.6 針對(duì)掃描二級(jí)索引的一項(xiàng)優(yōu)化改進(jìn)?？偟膩?lái)說(shuō)是通過(guò)把索引過(guò)濾條件下推到存儲(chǔ)引擎，來(lái)減少 MySQL 存儲(chǔ)引擎訪問(wèn)基表的次數(shù)以及 MySQL 服務(wù)層訪問(wèn)存儲(chǔ)引擎的次數(shù)。ICP 適用于 MYISAM 和 INNODB，本篇的內(nèi)容只基于 INNODB。

在講這個(gè)技術(shù)之前你得對(duì)mysql架構(gòu)有一個(gè)簡(jiǎn)單的認(rèn)識(shí)，見(jiàn)下圖

• MySQL 服務(wù)層：也就是 SERVER 層，用來(lái)解析 SQL 的語(yǔ)法、語(yǔ)義、生成查詢計(jì)劃、接管從 MySQL 存儲(chǔ)引擎層上推的數(shù)據(jù)進(jìn)行二次過(guò)濾等等。
• MySQL 存儲(chǔ)引擎層：按照 MySQL 服務(wù)層下發(fā)的請(qǐng)求，通過(guò)索引或者全表掃描等方式把數(shù)據(jù)上傳到 MySQL 服務(wù)層。
• MySQL 索引掃描：根據(jù)指定索引過(guò)濾條件，遍歷索引找到索引鍵對(duì)應(yīng)的主鍵值后回表過(guò)濾剩余過(guò)濾條件。
• MySQL 索引過(guò)濾：通過(guò)索引掃描并且基于索引進(jìn)行二次條件過(guò)濾后再回表。
• 使用索引下推實(shí)現(xiàn)

索引下推的使用條件

• ICP目標(biāo)是減少全行記錄讀取，從而減少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表數(shù)據(jù)，也就不存在下推一說(shuō)。
• 只能用于range、 ref、 eq_ref、ref_or_null訪問(wèn)方法；
• where 條件中是用 and 而非 or 的時(shí)候。
• ICP適用于分區(qū)表。
• ICP不支持基于虛擬列上建立的索引，比如說(shuō)函數(shù)索引
• ICP不支持引用子查詢作為條件。
• ICP不支持存儲(chǔ)函數(shù)作為條件，因?yàn)榇鎯?chǔ)引擎無(wú)法調(diào)用存儲(chǔ)函數(shù)。

索引下推相關(guān)語(yǔ)句

# 查看索引下推是否開(kāi)啟
select @@optimizer_switch
# 開(kāi)啟索引下推
set optimizer_switch="index_condition_pushdown=on";
# 關(guān)閉索引下推
set optimizer_switch="index_condition_pushdown=off";

什么是唯一索引？

講起來(lái)非常簡(jiǎn)單，其實(shí)和 "普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。 可以是單列唯一索引，也可以是聯(lián)合唯一索引。

• 最大的所用就是確保寫入數(shù)據(jù)庫(kù)的數(shù)據(jù)是唯一值。

什么時(shí)候應(yīng)該使用唯一索引呢？

我們前面講了唯一索引最大的好處就是能保證唯一性?？此茮](méi)什么太大的價(jià)值，可能就會(huì)有同學(xué)說(shuō)，我業(yè)務(wù)層做一個(gè)重復(fù)檢查不就好了。問(wèn)題就在這個(gè)地方，“業(yè)務(wù)是無(wú)法確保唯一性的”，除非你說(shuō)你的代碼沒(méi)有BUG。很多時(shí)候業(yè)務(wù)場(chǎng)景需要保證唯一性，如果不在數(shù)據(jù)庫(kù)加限制的話，總有一天會(huì)出現(xiàn)臟數(shù)據(jù)。

那又有同學(xué)就說(shuō)了，既然你不想重復(fù)你可以使用主鍵索引。這個(gè)回答也很有意思。

• 我們確實(shí)可以通過(guò)主鍵索引來(lái)保證唯一，但是，如果你的數(shù)據(jù)不能保證有序插入。比如說(shuō)身份證字段，你如果用身份證字段作為主鍵的話，會(huì)導(dǎo)致查詢效率降低。
• 唯一索引還有一個(gè)好處就是可以為空，真實(shí)的業(yè)務(wù)場(chǎng)景肯定是可以保證身份證為空的，如果沒(méi)有綁定身份證就不讓注冊(cè)好像也有點(diǎn)說(shuō)不過(guò)去。

聚簇索引的原理就不在這里細(xì)講了，會(huì)有一個(gè)單獨(dú)的章節(jié)來(lái)介紹。

唯一索引是否會(huì)影響性能呢？

我們通過(guò)和普通索引來(lái)做一個(gè)對(duì)比，有查詢和插入兩個(gè)場(chǎng)景。

首先第一個(gè)數(shù)據(jù)查詢，一般情況下來(lái)說(shuō)索引是通過(guò)B+樹從根節(jié)點(diǎn)開(kāi)始層序遍歷到葉子結(jié)點(diǎn)，數(shù)據(jù)頁(yè)內(nèi)部通過(guò)二分搜索。

• 普通索引 查到滿足條件的第一條記錄，繼續(xù)查找下一條記錄，直到找到不滿足條件的記錄
• 唯一索引 查到第一個(gè)滿足條件的記錄，就停止搜索。

InnoDB 它是以數(shù)據(jù)頁(yè)為單位進(jìn)行讀寫的，我們讀一條記錄，并不是從磁盤加載一條記錄，而是以頁(yè)為單位整體讀到內(nèi)存里面來(lái)的。

普通索引比唯一索引就多了一次查找和判斷下一條記錄的操作，也就是一次指針尋找數(shù)據(jù)和一次計(jì)算。當(dāng)然還有一種特殊情況，讀取到的這條數(shù)據(jù)正好是數(shù)據(jù)頁(yè)的最后一條，但是這種概率也是非常低，幾乎可以忽略不計(jì)。

整體看下來(lái)看上去性能差距并不大對(duì)吧。

來(lái)看第二個(gè)更新的性能，我們按照上面圖上的例子在2和6之間插入一個(gè)3。

在內(nèi)存中

• 普通索引 找到2和6之間的位置 →插入值→ 結(jié)束
• 唯一索引 找到2和6之間的位置 →**當(dāng)判斷有沒(méi)有沖突**→ 插入值→ 結(jié)束

不在內(nèi)存中

• 普通索引 將更新記錄在change buffer → 結(jié)束
• 唯一索引 將數(shù)據(jù)頁(yè)讀入內(nèi)存→當(dāng)判斷到?jīng)]有沖突→插入值→結(jié)束

數(shù)據(jù)讀取到內(nèi)存涉及了隨機(jī)IO訪問(wèn)，這是在數(shù)據(jù)庫(kù)里面成本最高的操作之一，而change buffer 就可以減少這種隨機(jī)磁盤訪問(wèn)，所以性能提示比較明顯。所以在這一塊來(lái)說(shuō)，如果兩者在業(yè)務(wù)場(chǎng)景下都能滿足時(shí)可以優(yōu)先考慮使用普通索引。

什么是聯(lián)合索引，組合索引，復(fù)合索引？

我們?cè)谒饕仡櫟臅r(shí)候和大家對(duì)索引做了一個(gè)分類對(duì)吧，按照字段個(gè)數(shù)來(lái)分的話，就分為了單列索引和組合索引對(duì)吧。那么他們之間的特點(diǎn)是什么呢？我們來(lái)看

• 單列索引 一個(gè)索引只包含了一個(gè)列，一個(gè)表里面可以有多個(gè)單列索引，但是這不叫組合索引。
• 組合索引（聯(lián)合索引 & 復(fù)合索引）一個(gè)索引包含多個(gè)列。

看上去感覺(jué)這組合索引并沒(méi)有太大作用是吧，我一個(gè)列已經(jīng)有一個(gè)索引了，我還要這組合索引干嘛？

真相往往不那么簡(jiǎn)單，首先我們得承認(rèn)我們的業(yè)務(wù)千變?nèi)f化，我們的查詢語(yǔ)句條件肯定是非常多的。

• 高效率 如果說(shuō)只有單列索引，那就會(huì)涉及多次二級(jí)索引樹查找，再加上回表，性能相對(duì)于聯(lián)合索引來(lái)說(shuō)是比較低的。
• 減少開(kāi)銷 我們要記得創(chuàng)建索引是存在空間開(kāi)銷的，對(duì)于大數(shù)據(jù)量的表，使用聯(lián)合索引會(huì)降低空間開(kāi)銷。
• 索引覆蓋 如果組合索引索引值已經(jīng)滿足了我們的查詢條件，那么就不會(huì)進(jìn)行回表，直接返回。

但是我們按照我們的查詢條件去創(chuàng)建一個(gè)聯(lián)合索引的話，就避免了上面的問(wèn)題。那么聯(lián)合索引是怎么工作的呢？

這里涉及到了一個(gè)重點(diǎn)，叫做最左前綴，簡(jiǎn)單理解就是只會(huì)從最左邊開(kāi)始組合，組合索引的第一個(gè)字段必須出現(xiàn)在查詢組句中，還不能跳躍，只有這樣才能讓索引生效，比如說(shuō)我查詢條件里面有組合索引里面的第二個(gè)字段，那么也是不會(huì)走組合索引的。舉個(gè)例子

// 假設(shè)給username，age創(chuàng)建了組合索引

// 這兩種情況是會(huì)走索引的
select username,age from user where username = '張三' and age = 18;
select * from user where username = '張三';

// 這種是不會(huì)走索引的
select * from user where age = 18;
select * from user where city = '北京' and age = 18;

復(fù)合索引創(chuàng)建時(shí)字段順序不一樣使用效果一樣嗎？

// 特殊情況，這種也是會(huì)走索引的，雖然我的age在前面，username在后面。
// 剛剛不是手最左前綴匹配嗎，為什么放到第二位也可以呢？
// 雖說(shuō)順序不一致，但是在SQL執(zhí)行過(guò)程中，根據(jù)查詢條件命中索引，
// 無(wú)論我username在不在前面，都會(huì)按照username去進(jìn)行索引查找。
select * from user where age = 18 and username = '張三';

使用Order By時(shí)能否通過(guò)索引排序？

我們知道在很多場(chǎng)景下會(huì)導(dǎo)致索引失效，比如說(shuō)沒(méi)有遵循B+樹的最左匹配原則，但是也有一些情況是遵循了最左匹配原則但是還是沒(méi)有走索引，這里我們使用order by進(jìn)行排序的時(shí)候就有不走索引的情況，那么帶大家來(lái)分析一下

drop table if exists `user`;
drop table if exists `user_example`;
create table `user`(
    `id` int primary key comment '主鍵ID',
    `card_id` int comment '身份證',
    `nickname` varchar(10) comment '昵稱',
    `age` int not null comment '年齡',
    key  `card_id` (`card_id`)
) engine=InnoDB default charset=utf8mb4;

// 這里我們明明對(duì)card_id建好了單列索引，那為什么不走索引呢？
select * from `user` order by card_id

• 如果索引覆蓋是可以走索引的
• 如果帶上索引條件是可以走索引的

通過(guò)索引排序內(nèi)部流程是什么呢？

explain select nickname,card_id,age from user order by card_id;

我們?cè)诹私鈓ysql底層是怎么排序的之前，我們先來(lái)了解一下一個(gè)概念 sort buffer .

首先mysql會(huì)為每一個(gè)線程都分配一個(gè)固定大小的sort buffer 用于排序。它是一個(gè)具有邏輯概念的內(nèi)存區(qū)域，我們可以通過(guò)sort_buffer_size參數(shù)來(lái)控制，默認(rèn)值是256kb 。

// 輸入查看最，小可以設(shè)置為 32K，最大可以設(shè)置為 4G。
show variables like 'sort_buffer_size';

由于sort buffer 大小是一個(gè)固定的，但是我們待排序的數(shù)據(jù)量它不是，所以根據(jù)它們之間的一個(gè)差值呢，就分為了內(nèi)部排序和外部排序

• 當(dāng)待排序的數(shù)據(jù)量小于等于sort buffer 時(shí)，那我們的sort buffer就能夠容納，MySQL就可以直接在內(nèi)存里面排序就行了，內(nèi)部排序使用的排序算法是快排
• 當(dāng)待排序的數(shù)據(jù)量大于sort buffer 時(shí)，那我們的sort buffer 就不夠用了對(duì)吧。這個(gè)時(shí)候MySQL就得要借助外部文件來(lái)進(jìn)行排序了。將待排序數(shù)據(jù)拆成多個(gè)小文件，對(duì)各個(gè)小文件進(jìn)行排序，最后再匯總成一個(gè)有序的文件，外部排序使用的算法時(shí)歸并排序

我們來(lái)聊聊row_id排序

和大家說(shuō)一個(gè)這個(gè)參數(shù)max_length_for_sort_data ，在我們MySQL中專門控制用戶排序的行數(shù)據(jù)長(zhǎng)度參數(shù)。默認(rèn)是4096，也就是說(shuō)如果超過(guò)了這個(gè)長(zhǎng)度MySQL就會(huì)自動(dòng)升級(jí)成row_id算法。

// 默認(rèn)max_length_for_sort_data的大小為4096字節(jié)
show variables like 'max_length_for_sort_data';

row_id排序的思想就是把不需要的數(shù)據(jù)不放到sort_buffer中，讓sort_buffer中只存放需要排序的字段。

舉個(gè)例子：

explain select nickname,card_id,age from user order by card_id;

我們前面說(shuō)到了sort buffer，在sort buffer里面進(jìn)行排序的數(shù)據(jù)是我們select的全部字段，所以當(dāng)我們查詢的字段越多，那么sort buffer能容納的數(shù)據(jù)量也就越小。而通過(guò)row_id排序就只會(huì)存放row_id 字段和排序相關(guān)的字段。其余的字段等排序完成之后通過(guò)主鍵ID進(jìn)行回表拿。

group by 分組和 order by 在索引使用上有什么不同嗎？

沒(méi)什么太大的差異group by實(shí)際是先進(jìn)行排序，再進(jìn)行分組。所以遵循order by的索引機(jī)制。