MySQL之索引

1.關于查詢緩存

直接看2個例子：

<a href="http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1_aQWzaqAAC-tRq76Ak323.jpg" target="_blank"></a>

<a href="http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE2AWhEA05AACPYb6eaGk941.jpg" target="_blank"></a>

可以發現，第一次執行SQL的結果會緩存起來，第二次執行同樣的SQL的時候，會快很多。

那能不能在第一次執行的時候，就非常快？

物美價廉：使用索引。

2.關于索引

主鍵索引

注意，當一個表的字段為primary key的時候，會自動成為主鍵索引的。

<a href="http://s3.51cto.com/wyfs02/M02/4C/C1/wKiom1RE2TXx5zXFAAFqbv-ZYpw749.jpg" target="_blank"></a>

【利用show index(es)/keys from table 可以檢視一張表上的索引】

在索引列上進行查詢一般而言是非常快的，例如：

<a href="http://s3.51cto.com/wyfs02/M00/4C/C1/wKiom1RE2azzoU5NAABSMFWEW-w066.jpg" target="_blank"></a>

注意這張表是非常大的（+300W），但是查找一條記錄的時間幾乎為0.

普通索引

就是在一個普通列上進行建立。

全文索引

比如要在一片文章中進行關鍵詞搜尋。适用于myisam引擎類型的表。

建立全文索引方法：

fulltext(col1,col2,...)

要想适用全文索引的話，不可以使用select * from user where name like '%keyword%'的方式。

使用方法： match(col1,col2,...) against 'keyword'

在實際開發中，我們經常需要知道一條SELECT語句到底是否使用了索引，MYSQL會怎樣執行，我們可以使用EXPLAIN指令來檢視，進而幫助我們優化SQL結構。舉例如下：

<a href="http://s3.51cto.com/wyfs02/M01/51/74/wKiom1RjYNvSP4zYAACH9NYl2a0938.jpg" target="_blank"></a>

重點注意一下：

id              MYSQL查詢序列号

select_type     有simple/union/subquery...

type            掃描的方式  【ALL代表全表掃描,system表僅有一行,const最多有一個比對行】

possible_keys 可能使用到的索引

key           實際上使用的索引

key_len       使用索引的長度。這在複合索引，也就是多列索引中用于判斷哪些索引有效使用，哪些索引沒有被使用【最左字首原理進行調整使用索引的政策】

rows            表示估算出來的結果集行數

extra            SQL語句的額外資訊【using where 表示不用讀取表中所有資訊，僅通過索引就可以擷取所需資料、using temporary 一些group by order by 的操作要使用臨時表】

通過EXPLAIN指令的幫助，我們可以來調整SQL，調整完SQL後，其實我們可以利用:

show profiles;

來分析調整前後SQL的執行時間

3.索引原理分析

mysql對索引的定義為：

幫助MYSQL高效擷取資料的一種資料結構。

行，表，檔案

<a href="http://s3.51cto.com/wyfs02/M01/4D/F4/wKioL1Rdfr3QNdE1AAE_RU-UYP8119.jpg" target="_blank"></a>

分析：

A.行存儲在檔案中。

B.行，表都是資料庫的概念，作業系統并不知道這些邏輯。

C.MYSQL的MYISAM存儲引擎會将一個表的資料存放至一個檔案，而在預設情況下INNODB，

則會不同表的資料都會存儲至一個檔案。

D.每個檔案可以劃分為頁。比如上圖，就劃分了5個頁。

E.要添加資料時，MYSQL會在最後一頁的最後一行插入資料，如果最後一頁已經滿了的話，

就需要新生成一頁。

F.如上圖，每個頁裡面都有空隙，那麼這是怎麼造成的呢？

根據E，肯定不是添加資料導緻的。而是删除資料導緻的。也就是說，删除導緻了頁的碎片。

那麼為什麼MYSQL不在添加的時候自動去填充這種空隙呢？如果一張表很大，

MYSQL每次添加資料都需要從上到下去找空隙，那麼這就會有很大的延時。

G.一個頁能有多少行？

比如一個頁的大小為4KB，一行記錄占用90位元組的話，那麼4*1024/90=45，也就是說

大概一頁可以放置45條資料。頁的大小是和作業系統和資料庫相關的。

H.頁是I/O的基本機關。

資料庫不可能訓示作業系統去拿到某些記錄或者拿到幾個位元組的資料，隻能告訴去加載

哪些頁至記憶體，然後資料庫在頁中找到想要的資料。也就是說，一條記錄應該由  

頁标示 + 行标示  共同構成這條記錄的唯一标示。

索引是如何工作的？

如果一張表很大，有幾百萬行，每次都使用順序逐行查找的話，勢必效率低下。

而索引，這種資料結構，提供了查找表的另外一種方式。

看一個簡化版的MYSQL索引：

【建構在上面圖中表的no列】

<a href="http://s3.51cto.com/wyfs02/M00/4D/F5/wKiom1RdhW_TZFceAAFQ4WQgjnA855.jpg" target="_blank"></a>

A.索引的資料結構表現形式為一個多節點的樹

B.有一個根節點

C.節點内的值是有序的

D.可以看到根節點的44的P1指向的節點的值都小于等于44.其實其他的節點

都是這樣的。

E.節點中的指針PX，要麼指向下一個節點，如P3，要麼指向表中的記錄，如P6.

對于P6這種指向表中記錄的節點即為葉子

F.葉子是連結的，也就是說一個葉子指向下一個葉子

G.其實P6相當于  頁标示 + 行标示

索引查找舉例分析：

案例一：利用索引查找no=39的所有行

第一步，從根節點開始，此時根成為活動節點

第二步，判斷活動節點是不是葉子，如果不是葉子，繼續  第三步  ； 否則 繼續 

        第四步

第三步，由于39&lt;44，是以P1所指向的節點成為活動節點，繼續  第二步

第四步，在活動節點中查找值，取出指針，也就是 這個值所在頁的标示

第五步，資料庫訓示作業系統加載這些頁，然後再在這些頁中找到no=39的行

可見，查找特定的值的所有行，MYSQL利用索引，并不需要浏覽所有行，可以快速找到。

案例二：擷取按照no排序的所有行

直接查找葉子，先擷取P6的所有頁，然後是P7的所有頁。

由于葉子後面還有葉子，是以下一個葉子繼續上面的過程。

no=2的時候，需要取第二頁

no=6的時候，需要取第一頁

no=7的時候，需要取第三頁

no=8的時候，需要取第四頁

no=27的時候，需要取第二頁

...

可以發現，由于檔案中的行是沒有順序的，那麼導緻有些頁必須多次擷取，這會增加

處理時間。（記憶體中的頁的個數是有限的，很可能有些頁已經不在記憶體中，是以必須

重新從硬碟擷取頁）

為了加快這一過程，就必須檔案中的行是有序存放的，那麼就可能每個頁隻需要擷取

一次，比如no=2在X頁中，那麼no=6就不需要再次擷取頁了，因為正确的頁已經存在記憶體

中了，MYSQL是了解這一點的。

【上面的就是聚集索引的概念】

4.索引小結

insert,update,delete的時候，MYSQL是需要維護索引樹

上面圖中的葉子的指針可能指向一行，也可能指向多行

如果no是主鍵，那麼顯然一個no隻能有一條記錄，那麼指針所指向的隻有一條記錄。

如果我們在name上建立索引，由于name并非唯一的，那麼指針會指向多個。

索引的節點就像是表中的行，是需要占用實體空間的

本文轉自zfz_linux_boy 51CTO部落格，原文連結：http://blog.51cto.com/zhangfengzhe/1575893，如需轉載請自行聯系原作者