索引原理

索引的優(yōu)缺點

優(yōu)點
索引大大減小了服務器需要掃描的數(shù)據(jù)量
索引可以幫助服務器避免排序和臨時表
索引可以將隨機IO變成順序IO
索引對于InnoDB（對索引支持行級鎖）非常重要，因為它可以讓查詢鎖更少的元組。在MySQL5.1和更新的版本中，InnoDB可以在服務器端過濾掉行后就釋放鎖，但在早期的MySQL版本中，InnoDB直到事務提交時才會解鎖。對不需要的元組的加鎖，會增加鎖的開銷，降低并發(fā)性。 InnoDB僅對需要訪問的元組加鎖，而索引能夠減少InnoDB訪問的元組數(shù)。但是只有在存儲引擎層過濾掉那些不需要的數(shù)據(jù)才能達到這種目的。一旦索引不允許InnoDB那樣做（即索引達不到過濾的目的），MySQL服務器只能對InnoDB返回的數(shù)據(jù)進行WHERE操作，此時，已經(jīng)無法避免對那些元組加鎖了。如果查詢不能使用索引，MySQL會進行全表掃描，并鎖住每一個元組，不管是否真正需要。
關于InnoDB、索引和鎖：InnoDB在二級索引上使用共享鎖（讀鎖），但訪問主鍵索引需要排他鎖（寫鎖）
缺點
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存索引文件。
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。
如果某個數(shù)據(jù)列包含許多重復的內(nèi)容，為它建立索引就沒有太大的實際效果。
對于非常小的表，大部分情況下簡單的全表掃描更高效；
索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。

因此應該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。

MySQL里同一個數(shù)據(jù)表里的索引總數(shù)限制為16個。

索引存儲類型

InnoDB使用的是B+Tree。
B+Tree：每一個葉子節(jié)點都包含指向下一個葉子節(jié)點的指針，從而方便葉子節(jié)點的范圍遍歷。
B-Tree通常意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同，很適合查找范圍數(shù)據(jù)。
B-Tree可以對<，<=，=，>，>=，BETWEEN，IN，以及不以通配符開始的LIKE使用索引。

索引查詢

可以利用B-Tree索引進行全關鍵字、關鍵字范圍和關鍵字前綴查詢，但必須保證按索引的最左邊前綴(leftmost prefix of the index)來進行查詢。

image

假設有如下一個表：

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob date not null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

其組合索引包含表中每一行的last_name、first_name和dob列。其結構大致如下：

image

按索引的最左邊前綴(leftmost prefix of the index)來進行查詢：

1. 查詢必須從索引的最左邊的列開始，否則無法使用索引。例如，你不能直接利用索引查找在某一天出生的人。

2. 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。

3. 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。

4. 匹配全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。

5. 匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。

6. 匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。

7. 匹配值的范圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。

8. 匹配部分精確而其它部分進行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。

9. 僅對索引進行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要再多一次I/O回讀元組。(覆蓋索引：索引的葉子節(jié)點中已經(jīng)包含要查詢的數(shù)據(jù)，那么就沒有必要再回表查詢了，如果索引包含滿足查詢的所有數(shù)據(jù)，就稱為覆蓋索引。)

索引排序

也可以利用B-Tree索引進行索引排序（對查詢結果進行ORDER BY），必須保證ORDER BY按索引的最左邊前綴(leftmost prefix of the index)來進行。

MySQL中，有兩種方式生成有序結果集：

• 使用filesort
• 按索引順序掃描

如果explain出來的type列的值為“index”，則說明MYSQL使用了索引掃描來做排序。

按索引順序掃描：

可以利用同一索引同時進行查找和排序操作：

• 當索引的順序與ORDER BY中的列順序相同，且所有的列是同一方向（全部升序或者全部降序）時，可以使用索引來排序。
• ORDER BY子句和查詢型子句的限制是一樣的：需要滿足索引的最左前綴的要求，有一種情況下ORDER BY子句可以不滿足索引的最左前綴要求，那就是前導列為常量時：WHERE子句或者JOIN子句中對前導列指定了常量。

image

• 如果查詢是連接多個表，僅當ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort文件排序。

使用filesort：

當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進行排序；

如果內(nèi)存裝載不下，它會將磁盤上的數(shù)據(jù)進行分塊，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結果集（實際上就是外排序，使用臨時表）。

對于filesort，MySQL有兩種排序算法：

• 兩次掃描算法(Two passes)

先將需要排序的字段和可以直接定位到相關行數(shù)據(jù)的指針信息取出，然后在設定的內(nèi)存（通過參數(shù)sort_buffer_size設定）中進行排序，完成排序之后再次通過行指針信息取出所需的Columns。

該算法是MySQL4.1之前采用的算法，它需要兩次訪問數(shù)據(jù)，尤其是第二次讀取操作會導致大量的隨機I/O操作。另一方面，內(nèi)存開銷較小。

• 一次掃描算法(single pass)

該算法一次性將所需的Columns全部取出，在內(nèi)存中排序后直接將結果輸出。

從MySQL4.1版本開始使用該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開銷也較大。如果我們將并不需要的Columns也取出來，就會極大地浪費排序過程所需要的內(nèi)存。

在 MySQL 4.1 之后的版本中，可以通過設置 max_length_for_sort_data 參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種：當取出的所有大字段總大小大于 max_length_for_sort_data 的設置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。

當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然后進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連接完成之后進行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。

為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

聚簇索引(cluster index)

一個表只能有一個聚簇索引。

目前，只有solidDB和InnoDB支持聚簇索引，MyISAM不支持聚簇索引。一些DBMS允許用戶指定聚簇索引，但是MySQL的存儲引擎到目前為止都不支持。

InnoDB的聚簇索引：

1. InnoDB對主鍵建立聚簇索引。
2. 如果你不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替。
3. 如果不存在這樣的索引，InnoDB會定義一個隱藏的主鍵，然后對其建立聚簇索引。

InnoDB默認使用聚簇索引來組織數(shù)據(jù)，如果你用InnoDB，而且不需要特殊的聚簇索引，一個好的做法就是使用代理主鍵(surrogate key)——獨立于你的應用中的數(shù)據(jù)。最簡單的做法就是使用一個AUTO_INCREMENT的列，這會保證記錄按照順序插入，而且能提高使用primary key進行連接的查詢的性能。應該盡量避免隨機的聚簇主鍵，例如字符串主鍵就是一個不好的選擇，它使得插入操作變得隨機。

一般來說，DBMS都會以聚簇索引的形式來存儲實際的數(shù)據(jù)，它是其它二級索引的基礎：

• 聚簇索引（primary索引）：主鍵索引
• 非聚簇索引（second索引）：二級索引

聚簇索引結構：

聚簇索引的結構大致如下：

• 聚簇索引：節(jié)點頁只包含了索引列，葉子頁包含了行的全部數(shù)據(jù)。聚簇索引“就是表”，因此可以不需要獨立的行存儲。

聚簇索引保證關鍵字的值相近的元組存儲的物理位置也相近（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統(tǒng)進行大量的移動操作）。

image

• 二級索引：葉子節(jié)點保存的不是指行的物理位置的指針，而是行的主鍵值。

這意味著通過二級索引查找行，存儲引擎需要：1、找到二級索引的葉子節(jié)點獲取對應的主鍵值，2、根據(jù)這個主鍵值去聚簇索引中查找到對應的行。這里需要兩次B-Tree查找而不是一次。

覆蓋索引對于InnoDB表尤其有用，因為InnoDB使用聚簇索引組織數(shù)據(jù)，如果二級索引中包含查詢所需的數(shù)據(jù)，就不再需要在聚集索引中查找了。

聚簇索引（InnoDB）和二級索引（MyISAM）數(shù)據(jù)布局比較：

CREATE TABLE layout_test (
  col1 int NOT NULL,
  col2 int NOT NULL,
  PRIMARY KEY(col1),
  KEY(col2)
);

• MyISAM

MyISAM按照插入的順序在磁盤上存儲數(shù)據(jù)：

左邊為行號(row number)，從0開始。因為元組的大小固定，所以MyISAM可以很容易的從表的開始位置找到某一字節(jié)的位置。

image

MyISAM建立的索引結構大致如下：

col1主鍵索引：

MyISAM不支持聚簇索引，索引中每一個葉子節(jié)點僅僅包含行號(row number)，且葉子節(jié)點按照col1的順序存儲。

image

col2非主鍵索引：

在MyISAM中，primary key和其它索引沒有什么區(qū)別。Primary key僅僅只是一個叫做PRIMARY的唯一，非空的索引而已，葉子節(jié)點按照col2的順序存儲。

image

• InnoDB

col1主鍵索引，即聚簇索引：

聚簇索引中的每個葉子節(jié)點包含主鍵的值，事務ID，用于事務和MVCC的回滾指針，和余下的列(如col2)。

image

col2非主鍵索引，即二級索引：

InnoDB的二級索引的葉子包含主鍵的值，而不是行指針(row pointers)，這樣的策略減小了移動數(shù)據(jù)或者數(shù)據(jù)頁面分裂時維護二級索引的開銷，因為InnoDB不需要更新索引的行指針。

image

• 聚簇索引+二級索引表 與 非聚簇索引表 的對比

image

Hash索引

哈希索引基于哈希表實現(xiàn)，只有精確索引所有列的查詢才有效。

對于每一行數(shù)據(jù)，存儲引擎都會對所有的索引列計算一個哈希碼。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數(shù)據(jù)的指針。

MySQL中，只有Memory存儲引擎顯示支持hash索引，是Memory表的默認索引類型，盡管Memory表也可以使用B-Tree索引。

Memory存儲引擎支持非唯一hash索引，這在數(shù)據(jù)庫領域是罕見的：如果多個值有相同的hash code，索引把它們的行指針用鏈表保存到同一個hash表項中。

假設創(chuàng)建如下一個表：

CREATE TABLE testhash (
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  KEY USING HASH(fname)
) ENGINE=MEMORY;

包含的數(shù)據(jù)如下：

image

假設索引使用hash函數(shù)f( )，如下：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

此時，索引的結構大概如下：

image

哈希索引中存儲的是：哈希值+數(shù)據(jù)行指針

當你執(zhí)行 SELECT lname FROM testhash WHERE fname='Peter'; MySQL會計算’Peter’的hash值，然后通過它來查詢索引的行指針。因為f('Peter') = 8784，MySQL會在索引中查找8784，得到指向記錄3的指針。

Hash索引有以下一些限制：

• 由于索引僅包含hash code和記錄指針，所以，MySQL不能通過使用索引避免讀取記錄，即每次使用哈希索引查詢到記錄指針后都要回讀元祖查取數(shù)據(jù)。
• 不能使用hash索引排序。
• Hash索引不支持鍵的部分匹配，因為是通過整個索引值來計算hash值的。
• Hash索引只支持等值比較，例如使用=，IN( )和<=>。對于WHERE price>100并不能加速查詢。
• 訪問Hash索引的速度非常快，除非有很多哈希沖突（不同的索引列值卻有相同的哈希值）。當出現(xiàn)哈希沖突的時候，存儲引擎必須遍歷鏈表中所有的行指針，逐行進行比較，直到找到所有符合條件的行。
• 如果哈希沖突很多的話，一些索引維護操作的代價也會很高。當從表中刪除一行時，存儲引擎要遍歷對應哈希值的鏈表中的每一行，找到并刪除對應行的引用，沖突越多，代價越大。

InnoDB引擎有一個特殊的功能叫做“自適應哈希索引”，由Mysql自動管理，不需要DBA人為干預。默認情況下為開啟，我們可以通過參數(shù)innodb_adaptive_hash_index來禁用此特性。

當InnoDB注意到某些索引值被使用得非常頻繁時，它會在內(nèi)存中基于緩沖池中的B+ Tree索引上再創(chuàng)建一個哈希索引，這樣就上B-Tree索引也具有哈希索引的一些優(yōu)點，比如快速的哈希查找。

• 只能用于等值比較，例如=， <=>，in ；
• 無法用于排序

InnoDB官方文檔顯示，啟用自適應哈希索引后，讀和寫性能可以提高2倍，對于輔助索引的連接操作，性能可以提高5倍

空間(R-Tree)索引

MyISAM支持空間索引，主要用于地理空間數(shù)據(jù)類型，例如GEOMETRY。

全文(Full-text)索引

全文索引是MyISAM的一個特殊索引類型，它查找的是文本中的關鍵詞，主要用于全文檢索。

MySQL InnoDB從5.6開始已經(jīng)支持全文索引，但InnoDB內(nèi)部并不支持中文、日文等，因為這些語言沒有分隔符?？梢允褂貌寮o助實現(xiàn)中文、日文等的全文索引。詳見：12.9.5 Full-Text Restrictions

索引使用

MySQL建立索引類型

• 單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。
• 組合索引，即一個索包含多個列。

索引是在存儲引擎中實現(xiàn)的，而不是在服務器層中實現(xiàn)的。所以，每種存儲引擎的索引都不一定完全相同，并不是所有的存儲引擎都支持所有的索引類型。

普通索引

這是最基本的索引，它沒有任何限制。普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對數(shù)據(jù)的訪問速度。因此，應該只為那些最經(jīng)常出現(xiàn)在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的數(shù)據(jù)列創(chuàng)建索引。

它有以下幾種創(chuàng)建方式：

• 創(chuàng)建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

• 修改表結構

ALTER mytable ADD INDEX [indexName] ON (username(length))

• 創(chuàng)建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

• 刪除索引的語法：

DROP INDEX [indexName] ON mytable;

唯一索引

它與前面的普通索引類似，不同的就是：普通索引允許被索引的數(shù)據(jù)列包含重復的值。而唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

它有以下幾種創(chuàng)建方式：

• 創(chuàng)建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

• 修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

• 創(chuàng)建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

主鍵索引

它是一種特殊的唯一索引，不允許有空值。一個表只能有一個主鍵。

一般是在建表的時候同時創(chuàng)建主鍵索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 當然也可以用 ALTER 命令。

與之類似的，外鍵索引

如果為某個外鍵字段定義了一個外鍵約束條件，MySQL就會定義一個內(nèi)部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

組合索引

為了形象地對比單列索引和組合索引，為表添加多個字段：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表時，usernname長度為 16，這里用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

建立這樣的組合索引，其實是相當于分別建立了下面三組組合索引：

usernname,city,age

usernname,city

usernname

為什么沒有 city，age這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引。下面的幾個SQL就會用到這個組合索引：

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"

SELECT * FROM mytable WHREE username="admin"

而下面幾個則不會用到：

SELECT * FROM mytable WHREE age=20 AND city="鄭州"

SELECT * FROM mytable WHREE city="鄭州"

如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。因為雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立索引的時機

一般來說，在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此，因為MySQL的B-Tree只對<，<=，=，>，>=，BETWEEN，IN，以及不以通配符開始的LIKE才會使用索引。

例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州'

此時就需要對city和age建立索引，由于mytable表的userame也出現(xiàn)在了JOIN子句中，也有對它建立索引的必要。

正確使用索引

使用（B-Tree）索引時，有以下一些技巧和注意事項：

索引設計：

• 索引字段盡量使用數(shù)字型（簡單的數(shù)據(jù)類型）

若只含數(shù)值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了

• 盡量不要讓字段的默認值為NULL

在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復雜。

索引不會包含有NULL值的列，只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。

所以我們在數(shù)據(jù)庫設計時盡量不要讓字段的默認值為NULL，應該指定列為NOT NULL，除非你想存儲NULL。你應該用0、一個特殊的值或者一個空串代替空值。

• 前綴索引和索引選擇性

對串列進行索引，如果可能應該指定一個前綴長度。

對于BLOB、TEXT或者很長的VARCHAR類型的列，必須使用前綴索引，因為MYSQL不允許索引這些列的完整長度。

前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點：MySQL無法使用前綴索引做order by和group by，也無法使用前綴索引做覆蓋掃描。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。

例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。

短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。在絕大多數(shù)應用里，數(shù)據(jù)庫中的字符串數(shù)據(jù)大都以各種各樣的名字為主，把索引的長度設置為10~15個字符已經(jīng)足以把搜索范圍縮小到很少的幾條數(shù)據(jù)記錄了。

通?？梢运饕_始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。

索引的選擇性是指，不重復的索引值（基數(shù)）和數(shù)據(jù)表中的記錄總數(shù)的比值。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MYSQL在查找時過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

決竅在于要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長（以便節(jié)約空間）。前綴應該足夠長，以使得前綴索引的選擇性接近于索引整個列。換句話說，前綴的“基數(shù)”應該接近于完整列的“基數(shù)”。

為了決定前綴的合適長度，需要找到最常見的值的列表，然后和最常見的前綴列表進行比較。例如以下查詢：

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as perf from sakila.city_demo group by city order by cnt desc limit 10;

直到這個前綴的選擇性接近完整列的選擇性。

計算合適的前綴長度的另一個方法就是計算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性，如下：

select count(distinct city)/count(*) from sakila.city_demo;

select count(distinct left(city,7))/count(*) from sakila.city_demo;

• 使用唯一索引

考慮某列中值的分布。索引的列的基數(shù)越大，索引的效果越好。

例如，存放出生日期的列具有不同值，很容易區(qū)分各行。而用來記錄性別的列，只含有“ M” 和“F”，則對此列進行索引沒有多大用處，因為不管搜索哪個值，都會得出大約一半的行。

• 使用組合索引代替多個列索引

一個多列索引（組合索引）與多個列索引MySQL在解析執(zhí)行上是不一樣的，如果在explain中看到有索引合并（即MySQL為多個列索引合并優(yōu)化），應該好好檢查一下查詢的表和結構是不是已經(jīng)最優(yōu)。

• 注意重復/冗余的索引、不使用的索引

MySQL允許在相同的列上創(chuàng)建多個索引，無論是有意還是無意的。大多數(shù)情況下不需要使用冗余索引。

對于重復/冗余、不使用的索引：可以直接刪除這些索引。因為這些索引需要占用物理空間，并且也會影響更新表的性能。

索引使用：

• 如果對大的文本進行搜索，使用全文索引而不要用使用 like ‘%…%’
• like語句不要以通配符開頭

對于LIKE：在以通配符%和_開頭作查詢時，MySQL不會使用索引。like操作一般在全文索引中會用到（InnoDB數(shù)據(jù)表不支持全文索引）。

例如下句會使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不會使用：

SELECT * FROM mytable WHEREt Name like'%admin'

• 不要在列上進行運算

索引列不能是表達式的一部分，也不是是函數(shù)的參數(shù)。

例如以下兩個查詢無法使用索引：

1）表達式： select actor_id from sakila.actor where actor_id+1=5;

2）函數(shù)參數(shù)：select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

• 盡量不要使用NOT IN、<>、!= 操作

應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

對于not in，可以用not exists或者（外聯(lián)結+判斷為空）來代替；很多時候用 exists 代替 in 是一個好的選擇： select num from a where num in(select num from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)

對于<>，用其它相同功能的操作運算代替，如a<>0 改為 a>0 or a<0

• or條件

用 or 分割開的條件， 如果 or 前的條件中的列有索引， 而后面的列中沒有索引， 那么涉及到的索引都不會被用到。

應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如： 假設num1有索引，num2沒有索引，查詢語句select id from t where num1=10 or num2=20會放棄使用索引，可以改為這樣查詢： select id from t where num1=10 union all select id from t where num2=20，這樣雖然num2沒有使用索引，但至少num1會使用索引，提高效率。

• 組合索引的使用要遵守“最左前綴”原則'

組合索引：當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是最好的。

例子：

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  birthday date not null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, birthday)
);

1. 查詢必須從索引的最左邊的列開始，否則無法使用索引。例如，你不能直接利用索引查找在某一天出生的人。
2. 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。
3. 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。

• 使用索引排序時，ORDER BY也要遵守“最左前綴”原則

1. 當索引的順序與ORDER BY中的列順序相同，且所有的列是同一方向（全部升序或者全部降序）時，可以使用索引來排序。
2. ORDER BY子句和查詢型子句的限制是一樣的：需要滿足索引的最左前綴的要求，有一種情況下ORDER BY子句可以不滿足索引的最左前綴要求，那就是前導列為常量時：WHERE子句或者JOIN子句中對前導列指定了常量。
3. 如果查詢是連接多個表，僅當ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort文件排序。

image

• 如果列類型是字符串，那么一定記得在 where 條件中把字符常量值用引號引起來，否則的話即便這個列上有索引，MySQL 也不會用到的，因為MySQL 默認把輸入的常量值進行轉換以后才進行檢索。 例如：

image

image

• 任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段
• 如果 MySQL 估計使用索引比全表掃描更慢，則不使用索引。當索引列有大量數(shù)據(jù)重復時,查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

索引性能測試與索引優(yōu)化

只有當數(shù)據(jù)庫里已經(jīng)有了足夠多的測試數(shù)據(jù)時，它的性能測試結果才有實際參考價值。如果在測試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里，這將使后續(xù)的查詢命令都執(zhí)行得非?？臁还苡袥]有使用索引。只有當數(shù)據(jù)庫里的記錄超過了1000條、數(shù)據(jù)總量也超過了 MySQL服務器上的內(nèi)存總量時，數(shù)據(jù)庫的性能測試結果才有意義。

在不確定應該在哪些數(shù)據(jù)列上創(chuàng)建索引的時候，人們從EXPLAIN SELECT命令那里往往可以獲得一些幫助。這其實只是簡單地給一條普通的SELECT命令加一個EXPLAIN關鍵字作為前綴而已。有了這個關鍵字，MySQL將不是去執(zhí)行那條SELECT命令，而是去對它進行分析。MySQL將以表格的形式把查詢的執(zhí)行過程和用到的索引(如果有的話)等信息列出來。

查看索引使用情況：

• 如果索引正在工作，Handler_read_key 的值將很高，這個值代表了一個行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因為索引并不經(jīng)常使用。
• Handler_read_rnd_next 的值高則意味著查詢運行低效，并且應該建立索引補救。這個值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)。如果正進行大量的表掃描， Handler_read_rnd_next 的值較高，則通常說明表索引不正確或寫入的查詢沒有利用索引。

具體如下：

image

從上面的例子中可以看出，目前使用的 MySQL 數(shù)據(jù)庫的索引情況并不理想。