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