mysql 索引及索引建立原則

是什麼

　　索引用於快速的查詢某些特殊列的某些行。如果沒有索引， MySQL 必須從第一行開始，然後通過搜尋整個表來查詢有關的行。表越大，查詢的成本越大。如果表有了索引的話，那麼 MySQL 可以很快的確定資料的位置，而不用查詢整個表格。這比順序的讀取每一行要快的多。索引就像我們查字典時的目錄一樣，我們通過查詢字典的目錄，可以定位到某一行資料。

　　大多數的 MySQL 的索引（主鍵索引，唯一索引，普通索引，全文索引）都是 B-trees  結構。例外的情況有：在空間資料類型使用 R-trees 結構。儲存引擎為 MEMORY 的資料庫，也可以支援雜湊索引。InnoDB 儲存引擎的全文索引使用反向列表結構。

使用情境

　　MySQL 會使用到索引的情境如下：

　　1.根據一個條件快速的匹配到對應的行。

　　2.縮小查詢影響行數。如果一個查詢欄位有多個索引，MySQL 通常選擇使用影響行數最小的索引（選擇性最高的索引）。索引的選擇性的計算 select count(distinct name) / count(*) from table; 

　　3.對於複合式索引，索引左邊的列可以用索引首碼最佳化器來查詢資料。例如，你有個三列的複合式索引（col1,col2,col3） ，那麼你可以使用索引查詢（col1）,(col1,col2),(col1,col2,col3)這三種組合的資料。有關於複合式索引，詳細請看另外一篇部落格 MySQL 複合式索引

　　4.當和其他表進行連表查詢的時候，如果進行判斷的列的資料類型和大小相同，那麼再這兩個列上使用索引，可以讓判斷更加效率。例如：在如下查詢中，給tb1.name和tb2.name添加索引會提升查詢效率。 SELECT * FROM tb1, tb2 WHERE tb1. name = tb2. name 

　　在這裡，VARCHAR 與 CHAR 被認為是相同的類型。需要注意的是，如果要讓索引生效，不僅需要類型一致，大小也必須一致。例如，VARCHAR(10) 和 CHAR(10) 大小相同可以使用索引，但 VARCHAR(10) 與 CHAR(15)就無法使用索引。

　　5.尋找索引列的 MIN() 或 MAX()值。

　　6.通過索引列進行排序或分組，或者複合式索引的左首碼進行排序或分組。

　　7.查詢索引列的內容。（如果只需要返回索引列的值，那麼不需要查詢資料行，直接從記憶體中讀取檢索值。這種情況稱為覆蓋索引）例如： SELECT key_part FROM table WHERE key_part=1 

　　對於小型表或報表查詢處理大多數或所有行的大型表的查詢，索引不太重要。當查詢需要訪問大多數行時，順序讀取比通過索引更快。順序讀取可以最大限度地減少磁碟搜尋，即使查詢不需要所有行也是如此。只有資料較大，並且需要訪問其中一部分資料的時候，索引才會顯得比較重要。

 

怎麼用查看索引

　　 SHOW INDEX FROM table 

　　運行後，顯示結果如下：

　　

　　其中，各個欄位的含義：

　　table： 表的名稱

　　Non_unique： 索引是否可以重複。不可以重複則為0；可以重複則為1。

　　Key_name： 索引名稱。建立的時候，可以選擇輸入，不輸入 MySQL 自動產生。如果索引是主鍵，則名稱始終為 PRIMARY。

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

　　Column_name： 索引涉及到的列的名稱。

　　Collation：列如何在索引中排序。這可以具有值 A（ascending 升序），D （ descending 降序）或NULL（未排序）。

　　Cardinality： 索引中唯一值的數量（不是即時更新的準確資料）。

　　Sub_part： 索引前置長度。如果使用欄位的部分字元作為索引，那麼顯示索引字元數量。如果使用整個欄位都被索引，那麼為 NULL。

　　Packed： key的打包方式，NULL 表示不打包。

　　Null： 索引列包含 NULL 或者 ‘’ 的時候，會是 YES。

　　Index_type： 索引類型。（BTREE， FULLTEXT，HASH， RTREE）之一。

　　Comment： 未在當前列中描述的索引資訊，例如 disabled 索引是否已禁用。

　　Index_comment： 在建立索引時提供的注釋。

　　Visible： 索引是否對最佳化程式可見（有的版本會出現該資訊）。

添加索引

　　 CREATE INDEX index_name ON table_name (key_part,...) 

　　 ALTER TABLE t1 ADD INDEX index_name (key_part) 

　　通常，在建立表時建立索引。對於InnoDB儲存引擎的表。其中主鍵確定資料的物理布局，可以向現有表中添加索引。key_part 表示組成索引的列的列名，如果是多個列名，那麼將產生一個複合式索引。在 key_part 參數後可以添加 ASC 或者 DESC 去指定索引按照正序排列還是倒序排列。

　　關於建立索引需要注意的是：

複合式索引

　　複合式索引是一個由多個列組成的索引。舉例說明：例如在表 address 中有三個欄位，分別為 Provincial 省 city 市 county 縣 在建表的時候，用這三個欄位組成一個複合式索引。代碼如下：

1 CREATE TABLE address (2     provincial VARCHAR (10),3     city VARCHAR (10),4     county VARCHAR (10),5     INDEX (provincial, city, county)6 )

CREATE TABLE address

 

　　這裡的索引是這樣建立的：首先按照省排序，然後，再根據同一個省的內容，按照市進行排序，最後，按照縣去排序。即，首先按照第一列進行索引排序，如果第一列內容一致，那麼按照第二列進行排序，以此類推。

 

首碼索引

　　如果將字串的列作為索引，可以建立首碼索引。一般情況下某個首碼的選擇性也是足夠高的，足以滿足查詢效能。對於BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用首碼索引。首碼索引以位元組為單位。首碼索引支援的長度取決於儲存引擎。例如，對於InnoDB 使用 REDUNDANT 或 COMPACT 行格式的表， 首碼最長可達767位元組。對於InnoDB使用DYNAMIC 或 COMPRESSED 行格式的表， 前置長度限制為3072位元組 。對於MyISAM表，前置長度限制為1000個位元組。

　　如果指定的索引首碼超過最大列資料類型大小，對於非唯一索引，如果啟用了嚴格的SQL模式，建立會發生錯誤。如果未啟用嚴格SQL模式，索引長度減少到最大列資料類型大小，併產生警告。

　　建立首碼索引的長度，取決於索引的選擇性。詳見另外一篇部落格：索引選擇性

　　建立首碼索引文法如下(這裡的10 表示截取前10個字元)：

　　 CREATE INDEX key_part_name ON table_name (key_part(10)); 

　　 ALTER TABLE table_name ADD INDEX index_name (key_part(10)) 

　　首碼索引可以兼顧索引大小和查詢速度。可以利用相對更小的空間，用更快的速度，查出資料。但是它也有缺點：首碼索引無法用於 ORDER BY 和 GROUP BY 操作，也不能用於索引覆蓋。

方法索引

　　這裡的索引類型英文名稱為：Functional Key Parts 這裡作者並不清楚官方的翻譯名稱為啥，只是根據索引的方式進行翻譯。如果不對，歡迎大神指正。

　　這個索引類似於兩個首碼索引的拼接。直接舉個例子就明白了：在 t1 表中有兩個列，col1 和 col2 我要建立一個包含完整的 col1 列和 col2 列的前10個位元組組成一個複合式索引。代碼如下：

CREATE TABLE t1 (  col1 VARCHAR(10),  col2 VARCHAR(20),  INDEX (col1, col2(10)));

Functional Key Parts

 

　　在 MySQL 8.0.13版本及更高版本中，MySQL 支援運算式進行索引。這裡，需要將運算運算式寫在括弧內進行縮印的聲明。例如：

 1 -- 方法索引 2 CREATE TABLE t1 ( 3     col1 INT, 4     col2 INT, 5     INDEX func_index ((ABS(col1))) 6 ); 7  8 CREATE INDEX idx1 ON t1 ((col1 + col2)); 9 10 CREATE INDEX idx2 ON t1 (11     (col1 + col2),12     (col1 - col2),13     col114 );15 16 ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

Functional Key Parts　　唯一索引

　　通過 UNIQUE 建立的索引。索引列的內容非null值的時候必須是唯一的，null值可以不唯一。如果添加重複值，則會發生錯誤。如果在建立唯一索引的時候指定首碼值，那麼首碼必須是唯一的。建立文法： CREATE UNIQUE INDEX unique_index_name ON table_name (key_part) 

　　全文索引

　　全文索引，顧名思義，支援全文檢索索引的索引。僅支援 Innodb 和 MyISAM 兩種儲存引擎。並且只能包括 CHAR, VARCHAR 和 TEXT 列，索引始終發生在整個列上，不支援首碼索引。（即使寫了也沒用）可以對欄位進行全文檢索索引。對於資料量比較大的資料集，先將資料載入到沒有資料的表中，然後再添加索引，效率要比把資料直接向有索引的表中添加高。

　　空間索引

　　空間索引是為空白間搜尋提供一種合適的資料結構，以提高搜尋速度。對於空間索引，作者研究並不多，這裡大概介紹下空間索引的用處。等以後研究深刻後，再補上這部分內容。首先，空間索引是幹什麼用的？舉例：當我們需要按照某個點，查詢附近的50米內都有哪些客戶存在。對於這個需求，可能的解法如下：

　　1.我們可以根據使用者的經緯度，去計算每個人跟我們的當前點的距離，然後跟50米去作對比。這在客戶資料量少的時候，還可以這麼做，資料量大的時候，將會特別的浪費效能。

　　2.先畫一個方框，把50米範圍的資料的經緯度畫出來，通過經緯度的值進行篩選後，得到一個正方形的地區，然後再進行計算。這時候，會少很多計算，但依然不是最優方案。

　　3.使用空間索引。將空間按照一定規則劃分為不同的地區，在檢索的時候，根據設計的地區，取出相應的資料。空間索引結構圖如下（圖片來自知乎）：

　　

 

刪除索引

　　刪除索引沒啥好說的，語句如下：

1 DROP INDEX index_name ON talbe_name2 ALTER TABLE table_name DROP INDEX index_name

DROP INDEX

 

　　如果索引所在的列刪除，那麼該列對應的索引也會自動刪除。

 

索引最佳化　　主鍵最佳化

　　表的主鍵是唯一且非空的索引，在使用InnoDB儲存引擎的時候，表資料直接掛載在主鍵的葉子節點上，是查詢速度最快的索引。

　　如果表的內容很多，並且很重要。但是沒有明顯的列和列的集合作為主鍵的話，可以單獨建立一個自動成長的值作為主鍵。當使用外鏈查詢的時候，這個id可以作為指向內容的指標。

　　外鍵最佳化

　　如果你的表有很多列，你可以將查詢頻率比較低的列拆分到其他表格，並通過複製id的方式讓它們與主表關聯。這樣，每個小表都會有個主鍵來快速尋找其他資料。在查詢的時候，就可以僅查詢自己需要的列集。這時，查詢會執行較少的 I/O 並且佔用較少的記憶體。整體原則是：為了提高效能，儘可能少的從磁碟讀取資料。這就是拆表的原則。