共計 9449 個字符，預計需要花費 24 分鐘才能閱讀完成。

這篇文章主要介紹 MySQL 中索引與優化的示例分析，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

索引與優化

1、選擇索引的數據類型

MySQL 支持很多數據類型，選擇合適的數據類型存儲數據對性能有很大的影響。通常來說，可以遵循以下一些指導原則：

(1)越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和 CPU 緩存中都需要更少的空間，處理起來更快。

(2)簡單的數據類型更好：整型數據比起字符，處理開銷更小，因為字符串的比較更復雜。在 MySQL 中，應該用內置的日期和時間數據類型，而不是用字符串來存儲時間；以及用整型數據類型存儲 IP 地址。

(3)盡量避免 NULL：應該指定列為 NOT NULL，除非你想存儲 NULL。在 MySQL 中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用 0、一個特殊的值或者一個空串代替空值。

1.1、選擇標識符

選擇合適的標識符是非常重要的。選擇時不僅應該考慮存儲類型，而且應該考慮 MySQL 是怎樣進行運算和比較的。一旦選定數據類型，應該保證所有相關的表都使用相同的數據類型。

(1)    整型：通常是作為標識符的最好選擇，因為可以更快的處理，而且可以設置為 AUTO_INCREMENT。

(2)    字符串：盡量避免使用字符串作為標識符，它們消耗更好的空間，處理起來也較慢。而且，通常來說，字符串都是隨機的，所以它們在索引中的位置也是隨機的，這會導致頁面分裂、隨機訪問磁盤，聚簇索引分裂（對于使用聚簇索引的存儲引擎）。

2、索引入門
對于任何 DBMS，索引都是進行優化的最主要的因素。對于少量的數據，沒有合適的索引影響不是很大，但是，當隨著數據量的增加，性能會急劇下降。
如果對多列進行索引 (組合索引)，列的順序非常重要，MySQL 僅能對索引最左邊的前綴進行有效的查找。例如：
假 設存在組合索引 it1c1c2(c1,c2)，查詢語句 select * from t1 where c1=1 and c2= 2 能夠使用該索引。查詢語句 select * from t1 where c1= 1 也能夠使用該索引。但是，查詢語句 select * from t1 where c2= 2 不能夠使用該索引，因為沒有組合索引的引導列，即，要想使用 c2 列進行查找，必需出現 c1 等于某值。

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

2.1.1、B-Tree 索引
假設有如下一個表：

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 列。其結構大致如下：

 

  索引存儲的值按索引列中的順序排列。可以利用 B -Tree 索引進行全關鍵字、關鍵字范圍和關鍵字前綴查詢，當然，如果想使用索引，你必須保證按索引的最左邊前綴 (leftmost prefix of the index) 來進行查詢。
(1)匹配全值 (Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于 1960-01-01 的 Cuba Allen。
(2) 匹配最左前綴 (Match a leftmost prefix)：你可以利用索引查找 last name 為 Allen 的人，僅僅使用索引中的第 1 列。
(3) 匹配列前綴 (Match a column prefix)：例如，你可以利用索引查找 last name 以 J 開始的人，這僅僅使用索引中的第 1 列。
(4) 匹配值的范圍查詢 (Match a range of values)：可以利用索引查找 last name 在 Allen 和 Barrymore 之間的人，僅僅使用索引中第 1 列。
(5) 匹配部分精確而其它部分進行范圍匹配 (Match one part exactly and match a range on another part)：可以利用索引查找 last name 為 Allen，而 first name 以字母 K 開始的人。
(6) 僅對索引進行查詢 (Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。
由于 B - 樹中的節點都是順序存儲的，所以可以利用索引進行查找(找某些值)，也可以對查詢結果進行 ORDER BY。當然，使用 B -tree 索引有以下一些限制：
(1) 查詢必須從索引的最左邊的列開始。關于這點已經提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳過某一索引列。例如，你不能利用索引查找 last name 為 Smith 且出生于某一天的人。
(3) 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為 WHERE last_name= Smith AND first_name LIKE J% AND dob= 1976-12-23，則該查詢只會使用索引中的前兩列，因為 LIKE 是范圍查詢。

2.1.2、Hash 索引
MySQL 中，只有 Memory 存儲引擎顯示支持 hash 索引，是 Memory 表的默認索引類型，盡管 Memory 表也可以使用 B -Tree 索引。Memory 存儲 引擎支持非唯一 hash 索引，這在數據庫領域是罕見的，如果多個值有相同的 hash code，索引把它們的行指針用鏈表保存到同一個 hash 表項中。
假設創建如下一個表：
CREATE TABLE testhash (
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  KEY USING HASH(fname)
) ENGINE=MEMORY;
包含的數據如下：

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

f(Arjen) = 2323

f(Baron) = 7437

f(Peter) = 8784

f(Vadim) = 2458

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

 

 Slots 是有序的，但是記錄不是有序的。當你執行
mysql SELECT lname FROM testhash WHERE fname= Peter
MySQL 會計算’Peter’的 hash 值，然后通過它來查詢索引的行指針。因為 f(Peter) = 8784，MySQL 會在索引中查找 8784，得到指向記錄 3 的指針。
因為索引自己僅僅存儲很短的值，所以，索引非常緊湊。Hash 值不取決于列的數據類型，一個 TINYINT 列的索引與一個長字符串列的索引一樣大。
 
Hash 索引有以下一些限制：
(1)由于索引僅包含 hash code 和記錄指針，所以，MySQL 不能通過使用索引避免讀取記錄。但是訪問內存中的記錄是非常迅速的，不會對性造成太大的影響。
(2)不能使用 hash 索引排序。
(3)Hash 索引不支持鍵的部分匹配，因為是通過整個索引值來計算 hash 值的。
(4)Hash 索引只支持等值比較，例如使用 =，IN()和 =。對于 WHERE price 100 并不能加速查詢。

2.1.3、空間 (R-Tree) 索引
MyISAM 支持空間索引，主要用于地理空間數據類型，例如 GEOMETRY。

2.1.4、全文 (Full-text) 索引
全文索引是 MyISAM 的一個特殊索引類型，主要用于全文檢索。

3、高性能的索引策略
3.1、聚簇索引(Clustered Indexes)
聚 簇索引保證關鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統進行大量的移動操作），且一個表只 能有一個聚簇索引。因為由存儲引擎實現索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有 solidDB 和 InnoDB 支持。
聚簇索引的結構大致如下：

  注：葉子頁面包含完整的元組，而內節點頁面僅包含索引的列(索引的列為整型)。一些 DBMS 允許用戶指定聚簇索引，但是 MySQL 的存儲引擎到目前為止都不支 持。InnoDB 對主鍵建立聚簇索引。如果你不指定主鍵，InnoDB 會用一個具有唯一且非空值的索引來代替。如果不存在這樣的索引，InnoDB 會定義 一個隱藏的主鍵，然后對其建立聚簇索引。一般來說，DBMS 都會以聚簇索引的形式來存儲實際的數據，它是其它二級索引的基礎。

3.1.1、InnoDB 和 MyISAM 的數據布局的比較
為了更加理解聚簇索引和非聚簇索引，或者 primary 索引和 second 索引(MyISAM 不支持聚簇索引)，來比較一下 InnoDB 和 MyISAM 的數據布局，對于如下表：

CREATE TABLE layout_test (

 col1 int NOT NULL,

 col2 int NOT NULL,

 PRIMARY KEY(col1),

 KEY(col2)

);

  假設主鍵的值位于 1 —10,000 之間，且按隨機順序插入，然后用 OPTIMIZE TABLE 進行優化。col2 隨機賦予 1 —100 之間的值，所以會存在許多重復的值。
(1)   MyISAM 的數據布局
其布局十分簡單，MyISAM 按照插入的順序在磁盤上存儲數據，如下：

  注：左邊為行號 (row number)，從 0 開始。因為元組的大小固定，所以 MyISAM 可以很容易的從表的開始位置找到某一字節的位置。
據些建立的 primary key 的索引結構大致如下：

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

  實際上，在 MyISAM 中，primary key 和其它索引沒有什么區別。Primary key 僅僅只是一個叫做 PRIMARY 的唯一，非空的索引而已。

(2)   InnoDB 的數據布局
InnoDB 按聚簇索引的形式存儲數據，所以它的數據布局有著很大的不同。它存儲表的結構大致如下：

  注：聚簇索引中的每個葉子節點包含 primary key 的值，事務 ID 和回滾指針(rollback pointer)——用于事務和 MVCC，和余下的列(如 col2)。

相 對于 MyISAM，二級索引與聚簇索引有很大的不同。InnoDB 的二級索引的葉子包含 primary key 的值，而不是行指針(row pointers)，這減小了移動數據或者數據頁面分裂時維護二級索引的開銷，因為 InnoDB 不需要更新索引的行指針。其結構大致如下：

  聚簇索引和非聚簇索引表的對比：

 

 3.1.2、按 primary key 的順序插入行(InnoDB)

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

 3.2、覆蓋索引 (Covering Indexes)
如果索引包含滿足查詢的所有數據，就稱為覆蓋索引。覆蓋索引是一種非常強大的工具，能大大提高查詢性能。只需要讀取索引而不用讀取數據有以下一些優點：
(1)索引項通常比記錄要小，所以 MySQL 訪問更少的數據；
(2)索引都按值的大小順序存儲，相對于隨機訪問記錄，需要更少的 I /O；
(3)大多數據引擎能更好的緩存索引。比如 MyISAM 只緩存索引。
(4)覆蓋索引對于 InnoDB 表尤其有用，因為 InnoDB 使用聚集索引組織數據，如果二級索引中包含查詢所需的數據，就不再需要在聚集索引中查找了。
覆蓋索引不能是任何索引，只有 B -TREE 索引存儲相應的值。而且不同的存儲引擎實現覆蓋索引的方式都不同，并不是所有存儲引擎都支持覆蓋索引 (Memory 和 Falcon 就不支持)。
對 于索引覆蓋查詢(index-covered query)，使用 EXPLAIN 時，可以在 Extra 一列中看到“Using index”。例如，在 sakila 的 inventory 表中，有一個組合索引(store_id,film_id)，對于只需要訪問這兩列的查 詢，MySQL 就可以使用索引，如下：

mysql EXPLAIN SELECT store_id, film_id FROM sakila.inventory/G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: inventory

 type: index

possible_keys: NULL

 key: idx_store_id_film_id

 key_len: 3

 ref: NULL

 rows: 5007

 Extra: Using index

1 row in set (0.17 sec)

在 大多數引擎中，只有當查詢語句所訪問的列是索引的一部分時，索引才會覆蓋。但是，InnoDB 不限于此，InnoDB 的二級索引在葉子節點中存儲了 primary key 的值。因此，sakila.actor 表使用 InnoDB，而且對于是 last_name 上有索引，所以，索引能覆蓋那些訪問 actor_id 的查 詢，如：

mysql EXPLAIN SELECT actor_id, last_name

 – FROM sakila.actor WHERE last_name = HOPPER /G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: actor

 type: ref

possible_keys: idx_actor_last_name

 key: idx_actor_last_name

 key_len: 137

 ref: const

 rows: 2

 Extra: Using where; Using index

3.3、利用索引進行排序
MySQL 中，有兩種方式生成有序結果集：一是使用 filesort，二是按索引順序掃描。利用索引進行排序操作是非常快的，而且可以利用同一索引同時進行查找和排 序操作。當索引的順序與 ORDER BY 中的列順序相同且所有的列是同一方向(全部升序或者全部降序) 時，可以使用索引來排序。如果查詢是連接多個表，僅當 ORDER BY 中的所有列都是第一個表的列時才會使用索引。其它情況都會使用 filesort。

create table actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL DEFAULT ,

password  varchar(16) NOT NULL DEFAULT ,

PRIMARY KEY(actor_id),

 KEY  (name)

) ENGINE=InnoDB

insert into actor(name,password) values(cat01 , 1234567

insert into actor(name,password) values(cat02 , 1234567

insert into actor(name,password) values(ddddd , 1234567

insert into actor(name,password) values(aaaaa , 1234567

mysql explain select actor_id from actor order by actor_id /G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: actor

 type: index

possible_keys: NULL

 key: PRIMARY

 key_len: 4

 ref: NULL

 rows: 4

 Extra: Using index

1 row in set (0.00 sec)

 

mysql explain select actor_id from actor order by password /G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: actor

 type: ALL

possible_keys: NULL

 key: NULL

 key_len: NULL

 ref: NULL

 rows: 4

 Extra: Using filesort

1 row in set (0.00 sec)

 

mysql explain select actor_id from actor order by name /G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: actor

 type: index

possible_keys: NULL

 key: name

 key_len: 18

 ref: NULL

 rows: 4

 Extra: Using index

1 row in set (0.00 sec)

  當 MySQL 不能使用索引進行排序時，就會利用自己的排序算法 (快速排序算法) 在內存 (sort buffer) 中對數據進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集（實際上就是 外排序）。對于 filesort，MySQL 有兩種排序算法。
(1)兩遍掃描算法 (Two passes)
實現方式是先將須要排序的字段和可以直接定位到相關行數據的指針信息取出，然后在設定的內存（通過參數 sort_buffer_size 設定）中進行排序，完成排序之后再次通過行指針信息取出所需的 Columns。
注：該算法是 4.1 之前采用的算法，它需要兩次訪問數據，尤其是第二次讀取操作會導致大量的隨機 I / O 操作。另一方面，內存開銷較小。
(3)    一次掃描算法 (single pass)
該算法一次性將所需的 Columns 全部取出，在內存中排序后直接將結果輸出。
注：從 MySQL 4.1 版本開始使用該算法。它減少了 I / O 的次數，效率較高，但是內存開銷也較大。如果我們將并不需要的 Columns 也取出來，就會極大地浪費排序過程所需要 的內存。在 MySQL 4.1 之后的版本中，可以通過設置 max_length_for_sort_data 參數來控制 MySQL 選擇第一種排序算法還是第二種。當取出的所有大字段總大小大于 max_length_for_sort_data 的設置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

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

3.4、索引與加鎖
索引對于 InnoDB 非 常重要，因為它可以讓查詢鎖更少的元組。這點十分重要，因為 MySQL 5.0 中，InnoDB 直到事務提交時才會解鎖。有兩個方面的原因：首先，即使 InnoDB 行級鎖的開銷非常高效，內存開銷也較小，但不管怎么樣，還是存 在開銷。其次，對不需要的元組的加鎖，會增加鎖的開銷，降低并發性。
InnoDB 僅對需要訪問的元組加鎖，而索引能夠減少 InnoDB 訪問的元組 數。但是，只有在存儲引擎層過濾掉那些不需要的數據才能達到這種目的。一旦索引不允許 InnoDB 那樣做（即達不到過濾的目的），MySQL 服務器只能對 InnoDB 返回的數據進行 WHERE 操作，此時，已經無法避免對那些元組加鎖了：InnoDB 已經鎖住那些元組，服務器無法解鎖了。
來看個例子：

create table actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL DEFAULT ,

password  varchar(16) NOT NULL DEFAULT ,

PRIMARY KEY(actor_id),

 KEY  (name)

) ENGINE=InnoDB

insert into actor(name,password) values(cat01 , 1234567

insert into actor(name,password) values(cat02 , 1234567

insert into actor(name,password) values(ddddd , 1234567

insert into actor(name,password) values(aaaaa , 1234567

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id 4

AND actor_id 1 FOR UPDATE;

  該查詢僅僅返回 2 —3 的數據，實際已經對 1 —3 的數據加上排它鎖了。InnoDB 鎖住元組 1 是因為 MySQL 的查詢計劃僅使用索引進行范圍查詢（而沒有進行過濾操作，WHERE 中第二個條件已經無法使用索引了）：

mysql EXPLAIN SELECT actor_id FROM test.actor

 – WHERE actor_id 4 AND actor_id 1 FOR UPDATE /G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: actor

 type: index

possible_keys: PRIMARY

 key: PRIMARY

 key_len: 4

 ref: NULL

 rows: 4

 Extra: Using where; Using index

1 row in set (0.00 sec)

 

mysql

  表明存儲引擎從索引的起始處開始，獲取所有的行，直到 actor_id 4 為假，服務器無法告訴 InnoDB 去掉元組 1。
為了證明 row 1 已經被鎖住，我們另外建一個連接，執行如下操作：

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

  該查詢會被掛起，直到第一個連接的事務提交釋放鎖時，才會執行（這種行為對于基于語句的復制 (statement-based replication) 是必要的）。

如上所示，當使用索引時，InnoDB 會鎖住它不需要的元組。更糟糕的是，如果查詢不能使用索引，MySQL 會進行全表掃描，并鎖住每一個元組，不管是否真正需要。

以上是“MySQL 中索引與優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！