一：索引基礎

   1：什麼是索引:

   

在關聯式資料庫中，索引是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種儲存結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯指標清單。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

索引提供指向儲存在表的指定列中的資料值的指標，然後根據您指定的排序順序對這些指標排序。資料庫使用索引以找到特定值，然後順指標找到包含該值的行。這樣可以使對應於表的SQL語句執行得更快，可快速訪問資料庫表中的特定資訊。

當表中有大量記錄時，若要對錶進行查詢，第一種搜尋資訊方式是全表搜尋，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作；第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過儲存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

 

 2：索引的作用

在資料庫系統中建立索引主要有以下作用：

（1）快速取資料；

（2）保證資料記錄的唯一性；

（3）實現表與表之間的參照完整性；

（4）在使用ORDER by、group by子句進行資料檢索時，利用索引可以減少排序和分組的時間。

 

 3：優缺點

優點

1.大大加快資料的檢索速度;

2.建立唯一性索引，保證資料庫表中每一行資料的唯一性;

3.加速表和表之間的連線;

4.在使用分組和排序子句進行資料檢索時，可以顯著減少查詢中分組和排序的時間。

缺點

1.索引需要佔物理空間。

2.當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，降低了資料的維護速度。

 

4：表索引型別查詢和分析

show index from table 檢視錶索情況和分佈

Table
表的名稱。

Non_unique

改索引值是否能重複，0:不可以  1：可以

3.Key_name
索引的名稱。

主鍵索引 PRIMARY KEY

唯一索引 UNIQUE

普通索引 INDEX

組合索引 INDEX

全文索引 FULLTEXT

4.Seq_in_index
索引中的列序列號，從1開始。

5.Column_name
列名稱。

6.Collation
列以什麼方式儲存在索引中。在MySQL中，A表示索引以序升排列，null表示不排序。

7.Cardinality
索引中唯一值的數目的估計值。。基數越大，當進行聯合時，MySQL使用該索引的機會就越大。

8.Sub_part
如果列只是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為NULL。

9.Packed
指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。

10.Null
如果列含有NULL，則含有YES。如果沒有，則該列含有NO。

11.Index_type
用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

12.Comment
註釋

 

 

5：索引型別介紹

 

 

1:主鍵索引（聚簇索引） PRIMARY KEY

主鍵索引是一種特殊的唯一索引，規則是不允許有空值，必須唯一，而且一張表只能有一個主鍵。

建立方式:建立表的時候就可以指定

通過主鍵索引，可以直接定位需要檢索的id，該id對應的行資料就是該索引的葉子節點

 

非索引方式查詢資料，先通過索引數查詢到對應的ID，再通過ID去表查詢行資料，就是回表

 

2:唯一索引 UNIQUE

唯一索引的列值規則要是是唯一，可以為空

新增方式：ALTER TABLE tr_order ADD UNIQUE(colom);

這裡面有兩個定義可以瞭解 一下關於唯一約束和唯一索引，資料庫概論描述，唯一約束是為了保證資料庫的完整性，唯一索引是作為輔助查詢的工具，建立唯一約束時會自動建立唯一索引，實際使用上沒啥區別。

 

 

3:組合索引 INDEX

組合索引是一個索引包含多個列，可以避免回表查詢

組合索引是比較特殊的索引方式，上面通過yunshan，is_not_robot 這兩個欄位建立的組合索引，實際上是建立了兩組組合索引

分別是

yunshan 屬性的組合索引

yunshan，is_not_robot的組合索引

為什麼沒有is_not_robot的組合索引，這是因為mysql組合索引的最左匹配原則的結果。理解就是隻從最左面的開始組合。並不是只要包含這兩個列的查詢都會用到該組合索引

 

什麼是最左匹配原則：

假如現在有這麼一張表

 

此時我們將name作為索引建立，採用B+樹的結構

 

 

如果我們繼續模式查詢，以name = “張%”去查詢，由於在B+樹結構的索引中，索引項是按照索引定義裡面出現的欄位順序排序的，索引在查詢的時候，可以快速定位到 Iid為 1的小明，然後直接向右遍歷所有小開頭的人，直到條件不滿足為止。

也就是說，我們找到第一個滿足條件的人之後，直接向右遍歷就可以了，由於索引是有序的，所有滿足條件的人都會聚集在一起。而這種定位到最左邊，然後向右遍歷尋找，就是我們所說的最左字首原則。

 

 

 

 

4:全文索引（全文檢索） FULLTEXT

全文索引只要建立就不能修改，只能通過刪除重新新增，適合在對大量的文字資料檢索。速度比like快。

只有欄位的資料型別為 char、varchar、text 及其系列才可以建全文索引

可以為該欄位建一個

ALTER TABLE table_name ADD FULLTEXT (column)

 

 

show variables like '%ft%';

 

 

6：索引設計的原則

• 適合索引的列是出現在where子句中的列，或者連線子句中指定的列；
• 基數較小的類，索引效果較差，沒有必要在此列建立索引；
• 使用短索引，如果對長字串列進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間；
• 不要過度索引。索引需要額外的磁碟空間，並降低寫操作的效能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利於查詢即可。

 

 

二、索引優化實戰

 

檢視索引使用情況：

在MySQL裡，使用SHOW STATUS查詢伺服器狀態，語法一般來說如下：

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

    執行命令後會看到很多內容，其中有一部分是Handler_read_*，

它們顯示了資料庫處理SELECT查詢語句的狀態，對於除錯SQL語句有很大意義。

SHOW STATUS LIKE 'Handler_read%';

 

Handler_read_first：索引中第一條被讀的次數。如果較高，它表示伺服器正執行大量全索引掃描；例如，SELECT col1 FROM foo，假定col1有索引（這個值越低越好）

Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的效能改善不高，因為索引不經常使用（這個值越高越好）。

Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。

Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。

Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連線沒有正確使用鍵。這個值較高，意味著執行效率低，應該建立索引來補救。

Handler_read_rnd_next：在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。