來(lái)自：CSDN，作者：topEngineerray
鏈接：https://blog.csdn.net/topdeveloperr/article/details/88742503
人類(lèi)存儲(chǔ)信息的發(fā)展歷程大致經(jīng)歷如下：
由于是個(gè)人憑著自己理解總結(jié)的，因此可能不一定精確，但是毋庸置疑的是，在當(dāng)代，各大公司機(jī)構(gòu)部門(mén)的數(shù)據(jù)都是維護(hù)在數(shù)據(jù)庫(kù)當(dāng)中的。
數(shù)據(jù)庫(kù)作為數(shù)據(jù)存儲(chǔ)介質(zhì)發(fā)展的最新產(chǎn)物，必然是具有許多優(yōu)點(diǎn)的，其中一個(gè)很大的優(yōu)點(diǎn)就是存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)訪問(wèn)速度非常快。
數(shù)據(jù)庫(kù)訪問(wèn)速度快的一個(gè)很重要的原因就在于索引index的作用。也就是這篇文章的主要想介紹的內(nèi)容，為什么索引可以讓數(shù)據(jù)庫(kù)查詢(xún)變快？
在理解索引這個(gè)概念之前，我們需要先了解一下計(jì)算機(jī)存儲(chǔ)方面的基本知識(shí)。
我們知道數(shù)據(jù)持久化之后存在了數(shù)據(jù)庫(kù)里,那么我現(xiàn)在的問(wèn)題是數(shù)據(jù)庫(kù)將數(shù)據(jù)存在了哪里？答案顯然是存在了計(jì)算機(jī)的存儲(chǔ)設(shè)備上。就個(gè)人電腦而言，數(shù)據(jù)被存在了我們的電腦存儲(chǔ)設(shè)備上。
計(jì)算機(jī)的存儲(chǔ)設(shè)備有很多種，其中速度越快的越貴，因此容量也往往越小例如我們的RAM隨機(jī)存儲(chǔ)器，也就是大家平時(shí)說(shuō)的內(nèi)存條，速度慢的就相對(duì)便宜例如我們的硬盤(pán)。而我們的數(shù)據(jù)往往都是被存在最慢的存儲(chǔ)設(shè)備硬盤(pán)上的，因?yàn)榇嬖诋?dāng)中的數(shù)據(jù)在斷電之后依然存在。
計(jì)算機(jī)的存儲(chǔ)介質(zhì)有多種，例如硬盤(pán)，例如告訴緩存，不同的存儲(chǔ)介質(zhì)的數(shù)據(jù)讀取速度是不一樣的。例如，像RAM這樣的易失性存儲(chǔ)設(shè)備的讀寫(xiě)操作就非?？?，訪問(wèn)其中的數(shù)據(jù)幾乎沒(méi)有延遲性。
由于這個(gè)原因，計(jì)算機(jī)操作系統(tǒng)的設(shè)計(jì)是這樣的：數(shù)據(jù)永遠(yuǎn)不會(huì)直接從硬盤(pán)等機(jī)械設(shè)備中取出，而是首先從硬盤(pán)轉(zhuǎn)移到更快的存儲(chǔ)設(shè)備，例如RAM，從RAM當(dāng)中應(yīng)用程序直接按需獲取數(shù)據(jù)。
計(jì)算機(jī)內(nèi)部的機(jī)械硬盤(pán)是下面這樣的：
在一個(gè)典型的硬盤(pán)驅(qū)動(dòng)器中可以有很多個(gè)盤(pán)片，“盤(pán)片”在外觀上非常類(lèi)似于一個(gè)光盤(pán)（但具有很高的存儲(chǔ)容量）。盤(pán)片又被磁道分條，同時(shí)一個(gè)盤(pán)片又可以分為扇區(qū)。
要獲取數(shù)據(jù)，“盤(pán)片”需要由主軸進(jìn)行旋轉(zhuǎn)。大多數(shù)硬盤(pán)供應(yīng)商都提到了主軸旋轉(zhuǎn)的速度，例如，7200轉(zhuǎn)/分和15000轉(zhuǎn)/分。磁盤(pán)中的數(shù)據(jù)總是以扇區(qū)的固定大小倍數(shù)表示。因此，如果要從硬盤(pán)訪問(wèn)數(shù)據(jù)，需要執(zhí)行以下步驟，這也是性能開(kāi)銷(xiāo)的主要來(lái)源。
如果數(shù)據(jù)恰好分布在連續(xù)扇區(qū)上，那么它將提高獲取數(shù)據(jù)的性能。因?yàn)橹鬏S和磁頭本身不需要移動(dòng)/旋轉(zhuǎn)，也就沒(méi)有太多開(kāi)銷(xiāo)，但是大多數(shù)時(shí)候這種開(kāi)銷(xiāo)是存在的。
由于存在這種開(kāi)銷(xiāo)，我們不能直接從硬盤(pán)獲取數(shù)據(jù)。RAM的存儲(chǔ)器高性能的背后的主要原因是它沒(méi)有像硬盤(pán)那樣的機(jī)械運(yùn)動(dòng)部件。但是盡管RAM的性能很高，但它當(dāng)中的數(shù)據(jù)卻不會(huì)用作永久存儲(chǔ)，斷電之后就會(huì)消失，重新啟動(dòng)之后就什么都沒(méi)有了，這是我們需要硬盤(pán)來(lái)進(jìn)行持久化的原因所在。數(shù)據(jù)庫(kù)中的數(shù)據(jù)毫無(wú)疑問(wèn)就是存放在硬盤(pán)當(dāng)中的，因此訪問(wèn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)不可避免的會(huì)經(jīng)歷磁盤(pán)操作的開(kāi)銷(xiāo)。
知道上述知識(shí)后，索引就更容易理解了。
舉個(gè)例子，想象一下，現(xiàn)在有一本500頁(yè)厚包含幾十萬(wàn)字的字典，同時(shí)里面的字是無(wú)序排列的，現(xiàn)在我需要你從中找出某幾個(gè)字出來(lái)同時(shí)不允許查看目錄。毫無(wú)疑問(wèn)，我們只能一頁(yè)一頁(yè)的翻，這是非人類(lèi)能接受的工作，我們必然想的是先看目錄，找到相關(guān)的字或者偏旁，然后去對(duì)應(yīng)的地方查找文字，這樣效率就大大提高了。目錄事實(shí)上就是一種索引，其思想一脈相承。
數(shù)據(jù)庫(kù)的索引類(lèi)似于書(shū)中的這個(gè)目錄。索引會(huì)幫助我們快速檢索數(shù)據(jù)庫(kù)，查詢(xún)不需要通過(guò)整個(gè)表來(lái)獲取數(shù)據(jù)，而是從索引中找到數(shù)據(jù)塊。以一張數(shù)據(jù)庫(kù)表為例：
上表是一張真實(shí)的數(shù)據(jù)庫(kù)表，其中每一行是一條記錄，每條記錄都有字段。假設(shè)上面的數(shù)據(jù)庫(kù)是一個(gè)有10萬(wàn)條記錄的大數(shù)據(jù)庫(kù)?，F(xiàn)在，我們想從10萬(wàn)條記錄中搜索一些內(nèi)容，那么挨著一個(gè)一個(gè)搜索無(wú)疑將花費(fèi)很長(zhǎng)的時(shí)間，這個(gè)時(shí)候我們?cè)跀?shù)據(jù)結(jié)構(gòu)與算法里學(xué)的二分查找法就派上了用場(chǎng)。
使用二分查找法，需要將數(shù)據(jù)先排序，但是其查詢(xún)效率將大大提高。
例子如下:
假設(shè)我們?cè)谏厦娴臄?shù)據(jù)庫(kù)中使用的是固定長(zhǎng)度的記錄,固定塊記錄大小為205個(gè)字節(jié)， 默認(rèn)塊大小是1024字節(jié)。則：
固定記錄大小=204字節(jié)，塊大小=1024字節(jié)
所以每個(gè)數(shù)據(jù)塊的記錄數(shù)=1024/204=5條記錄，10萬(wàn)條記錄就是2萬(wàn)個(gè)塊
不使用任何算法，我們要查詢(xún)100000條記錄中的某一條，，在最壞的情況下我們需要遍歷一遍2萬(wàn)block才能獲得全部100000條記錄。但如果進(jìn)行二分查找，則只需要進(jìn)行20000的對(duì)數(shù)基數(shù)2，即14.287712次即可。這意味著我們只需對(duì)排序后的值進(jìn)行14次搜索，就可以使用二分查找到您感興趣的唯一值。
上圖是對(duì)一串?dāng)?shù)字生成的二叉查找樹(shù)。其時(shí)間復(fù)雜度為O(n)=O(log2N),即以2為底，n的對(duì)數(shù)。其中n為查找目標(biāo)群體的總數(shù)據(jù)量。
例如，假設(shè)N為8，則O(n) = O(2為底8的對(duì)數(shù)) = O(3).
遍歷方式，其時(shí)間復(fù)雜度為O(n)
在上述例子當(dāng)中，n就是10000。使用索引的時(shí)間復(fù)雜度為O(2為底10000的對(duì)數(shù)) 大約等于 13. 和O(10000)之間差大概800倍。
這個(gè)時(shí)候我們就能直接回答上述問(wèn)題了，建立了索引的數(shù)據(jù)，就是通過(guò)事先排好序，從而在查找時(shí)可以應(yīng)用二分查找來(lái)提高查詢(xún)效率。這也解釋了為什么索引應(yīng)當(dāng)盡可能的建立在主鍵這樣的字段上，因?yàn)橹麈I必須是唯一的，根據(jù)這樣的字段生成的二叉查找樹(shù)的效率無(wú)疑是最高的。
如果一個(gè)表中所有字段的索引很大，也會(huì)導(dǎo)致性能下降。想象一下，如果一個(gè)索引和一個(gè)表一樣長(zhǎng)，那么它將再次成為一個(gè)需要檢查的開(kāi)銷(xiāo)。這就好比字典的目錄非常詳細(xì)，但是其長(zhǎng)度已經(jīng)和所有的文字一樣長(zhǎng)，這個(gè)時(shí)候目錄本身的效率就大大下降了。
肯定是有的，索引可以提高查詢(xún)讀取性能，而它將降低寫(xiě)入性能。當(dāng)有索引時(shí)，如果更改一條記錄，或者在數(shù)據(jù)庫(kù)中插入一條新的記錄，它將執(zhí)行兩個(gè)寫(xiě)入操作（一個(gè)操作是寫(xiě)入記錄本身，另一個(gè)操作是將更新索引）。因此，在定義索引時(shí)，必須牢記以下幾點(diǎn)：
聚集索引clustered index也叫聚簇索引，它的定義是：聚集索引的表中數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。
例如:
結(jié)合上面的表格就很好理解了：數(shù)據(jù)行的物理順序與列值的順序相同，如果我們查詢(xún)id比較靠后的數(shù)據(jù)，那么這行數(shù)據(jù)的地址在磁盤(pán)中的物理地址也會(huì)比較靠后。聚集索引存儲(chǔ)記錄是物理上連續(xù)存在，而非聚集索引是邏輯上的連續(xù)，物理存儲(chǔ)并不連續(xù)。
為什么查詢(xún)更快呢？我們通過(guò)上面的分析知道了索引是通過(guò)二叉樹(shù)的數(shù)據(jù)結(jié)構(gòu)來(lái)描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過(guò)有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。
主鍵一般會(huì)默認(rèn)創(chuàng)建聚集索引。
在創(chuàng)建聚集索引之前，應(yīng)先了解您的數(shù)據(jù)是如何被訪問(wèn)的?？煽紤]將聚集索引用于：
包含大量非重復(fù)值的列。使用下列運(yùn)算符返回一個(gè)范圍值的查詢(xún)：BETWEEN、>、>=、< 和 <=。被連續(xù)訪問(wèn)的列。返回大型結(jié)果集的查詢(xún)。經(jīng)常被使用聯(lián)接或 GROUP BY 子句的查詢(xún)?cè)L問(wèn)的列；一般來(lái)說(shuō)，這些是外鍵列。對(duì) ORDER BY 或 GROUP BY 子句中指定的列進(jìn)行索引，可以使 SQL Server 不必對(duì)數(shù)據(jù)進(jìn)行排序，因?yàn)檫@些行已經(jīng)排序。這樣可以提高查詢(xún)性能。OLTP型的應(yīng)用程序，這些程序要求進(jìn)行非?？焖俚膯涡胁檎遥ㄒ话阃ㄟ^(guò)主鍵）。應(yīng)在主鍵上創(chuàng)建聚集索引。聚集索引不適用于：
頻繁更改的列 這將導(dǎo)致整行移動(dòng)，因?yàn)?SQL Server 必須按物理順序保留行中的數(shù)據(jù)值。這一點(diǎn)要特別注意，因?yàn)樵诖髷?shù)據(jù)量事務(wù)處理系統(tǒng)中數(shù)據(jù)是易失的
條件中用or，即使其中有條件帶索引，也不會(huì)使用索引查詢(xún)，這就是查詢(xún)盡量不要用or的原因，用in吧。
全表掃描往往發(fā)生在下面幾種情況:
不在索引列上做任何操作（計(jì)算，函數(shù)、自動(dòng)or手動(dòng)類(lèi)型轉(zhuǎn)換），這樣會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
存儲(chǔ)引擎不能使用索引中范圍條件右邊的列。這個(gè)是因?yàn)閍ge中查詢(xún)時(shí)范圍查詢(xún)了，pos列的索引就沒(méi)有生效了。
盡量使用覆蓋索引（只訪問(wèn)索引的查詢(xún)（索引列和查詢(xún)列一致）），減少select *。
對(duì)于MySQL而言：
聯(lián)系客服
