MySQL之索引

標籤：mysql 索引

1.關於查詢快取

直接看2個例子： 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1_aQWzaqAAC-tRq76Ak323.jpg" title="mysql13.png" alt="wKioL1RE1_aQWzaqAAC-tRq76Ak323.jpg" /> 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE2AWhEA05AACPYb6eaGk941.jpg" title="mysql14.png" alt="wKioL1RE2AWhEA05AACPYb6eaGk941.jpg" /> 可以發現，第一次執行SQL的結果會緩衝起來，第二次執行同樣的SQL的時候，會快很多。 那能不能在第一次執行的時候，就非常快？ 物美價廉：使用索引。

2.關於索引

主鍵索引 注意，當一個表的欄位為primary key的時候，會自動成為主鍵索引的。 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/C1/wKiom1RE2TXx5zXFAAFqbv-ZYpw749.jpg" title="mysql15.png" alt="wKiom1RE2TXx5zXFAAFqbv-ZYpw749.jpg" /> 【利用show index(es)/keys from table 可以查看一張表上的索引】 在索引列上進行查詢一般而言是非常快的，例如： 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/C1/wKiom1RE2azzoU5NAABSMFWEW-w066.jpg" title="mysql16.png" alt="wKiom1RE2azzoU5NAABSMFWEW-w066.jpg" /> 注意這張表是非常大的（+300W），但是尋找一條記錄的時間幾乎為0. 普通索引 就是在一個普通列上進行建立。 全文索引 比如要在一片文章中進行關鍵詞搜尋。適用於myisam引擎類型的表。 建立全文索引方法： fulltext(col1,col2,...) 要想適用全文索引的話，不可以使用select * from user where name like ‘%keyword%‘的方式。 使用方法： match(col1,col2,...) against ‘keyword‘ 在實際開發中，我們經常需要知道一條SELECT語句到底是否使用了索引，MYSQL會怎樣執行，我們可以使用EXPLAIN命令來查看，從而協助我們最佳化SQL結構。舉例如下： 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/51/74/wKiom1RjYNvSP4zYAACH9NYl2a0938.jpg" title="index.png" alt="wKiom1RjYNvSP4zYAACH9NYl2a0938.jpg" /> 重點注意一下： 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高效擷取資料的一種資料結構。 行，表，檔案 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4D/F4/wKioL1Rdfr3QNdE1AAE_RU-UYP8119.jpg" title="index01.png" alt="wKioL1Rdfr3QNdE1AAE_RU-UYP8119.jpg" /> 分析： 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列】 650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4D/F5/wKiom1RdhW_TZFceAAFQ4WQgjnA855.jpg" title="index05.png" alt="wKiom1RdhW_TZFceAAFQ4WQgjnA855.jpg" /> A.索引的資料結構表現形式為一個多節點的樹 B.有一個根節點 C.節點內的值是有序的 D.可以看到根節點的44的P1指向的節點的值都小於等於44.其實其他的節點 都是這樣的。 E.節點中的指標PX，要麼指向下一個節點，如P3，要麼指向表中的記錄，如P6. 對於P6這種指向表中記錄的節點即為葉子 F.葉子是連結的，也就是說一個葉子指向下一個葉子 G.其實P6相當於  頁標示 + 行標示 索引尋找舉例分析： 案例一：利用索引尋找no=39的所有行 第一步，從根節點開始，此時根成為活動節點 第二步，判斷活動節點是不是葉子，如果不是葉子，繼續  第三步  ； 否則 繼續          第四步 第三步，由於39<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並非唯一的，那麼指標會指向多個。 索引的節點就像是表中的行，是需要佔用物理空間的

本文出自 “我想超越自我” 部落格，請務必保留此出處http://zhangfengzhe.blog.51cto.com/8855103/1575893

MySQL之索引