《Mysql學習深入理解MySQL索引與優化》要點：
本文介紹了Mysql學習深入理解MySQL索引與優化，希望對您有用。如果有疑問，可以聯系我們。

本節內容：
mysql索引深入探討

索引對查詢的速度有著至關重要的影響,理解索引也是進行數據庫性能調優的起點.考慮如下情況,假設數據庫中一個表有10^6條記錄,DBMS的頁面大小為4K,并存儲100條記錄.

如果沒有索引,查詢將對整個表進行掃描,最壞的情況下,如果所有數據頁都不在內存,必要讀取10^4個頁面,如果這10^4個頁面在磁盤上隨機分布,必要進行10^4次I/O,假設磁盤每次I/O時間為10ms(忽略數據傳輸時間),則總共必要100s(但實際上要好很多很多).

如果對之建立B-Tree索引,則只必要進行log100(10^6)=3次頁面讀取,最壞情況下耗時30ms.這就是索引帶來的效果,很多時候,當你的應用程序進行SQL查詢速度很慢時,應該想想是否可以建索引.MYSQL學習

第二章、mysql索引與索引優化MYSQL學習

1、選擇索引的數據類型MYSQL學習

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

(1)越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和CPU緩存中都需要更少的空間,處理起來更快.
(2)簡單的數據類型更好：整型數據比起字符,處理開銷更小,因為字符串的比較更復雜.在MySQL中,應該用內置的日期和時間數據類型,而不是用字符串來存儲時間；以及用整型數據類型存儲IP地址.
(3)盡量避免NULL：應該指定列為NOT NULL,除非你想存儲NULL.在MySQL中,含有空值的列很難進行查詢優化,因為它們使得索引、索引的統計信息以及比較運算更加復雜.你應該用0、一個特殊的值或者一個空串代替空值.

1.1、選擇標識符
選擇合適的標識符是非常重要的.選擇時不僅應該考慮存儲類型,而且應該考慮MySQL是怎樣進行運算和比較的.一旦選定數據類型,應該保證所有相關的表都使用相同的數據類型.
(1)?? ?整型：通常是作為標識符的最好選擇,因為可以更快的處理,而且可以設置為AUTO_INCREMENT.

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

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索引
假設有如下一個表：
?MYSQL學習

CREATE TABLE People (MYSQL學習

?? last_name?varchar(50)??? not null,MYSQL學習

?? first_name varchar(50)??? not null,MYSQL學習

?? dob??????? date?????????? not null,MYSQL學習

?? gender???? enum('m', 'f') not null,MYSQL學習

?? key(last_name, first_name, dob)MYSQL學習

);MYSQL學習

?其索引包括表中每一行的last_name、first_name和dob列.其結構大致如下：MYSQL學習

?MYSQL學習

?索引存儲的值按索引列中的順序排列.可以利用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是范圍查詢.

?MYSQL學習

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;
包括的數據如下：

?MYSQL學習

假設索引使用hash函數f( ),如下：MYSQL學習

f('Arjen') = 2323MYSQL學習

f('Baron') = 7437MYSQL學習

f('Peter') = 8784MYSQL學習

f('Vadim') = 2458MYSQL學習

此時,索引的結構大概如下：MYSQL學習

?MYSQL學習

?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的一個特殊索引類型,主要用于全文檢索.

?MYSQL學習

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

?MYSQL學習

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

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

?MYSQL學習

CREATE TABLE layout_test (MYSQL學習

?? col1 int NOT NULL,MYSQL學習

?? col2 int NOT NULL,MYSQL學習

?? PRIMARY KEY(col1),MYSQL學習

?? KEY(col2)MYSQL學習

);MYSQL學習

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

?MYSQL學習

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

?MYSQL學習

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

MYSQL學習

?實際上,在MyISAM中,primary key和其它索引沒有什么區別.Primary key僅僅只是一個叫做PRIMARY的唯一,非空的索引罷了.

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

MYSQL學習

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

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

MYSQL學習

?聚簇索引和非聚簇索引表的對比：MYSQL學習

?MYSQL學習

?MYSQL學習

?3.1.2、按primary key的順序插入行(InnoDB)MYSQL學習

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

??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學習

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventoryGMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: inventoryMYSQL學習

???????? type: indexMYSQL學習

possible_keys: NULLMYSQL學習

????????? key: idx_store_id_film_idMYSQL學習

????? key_len: 3MYSQL學習

????????? ref: NULLMYSQL學習

???????? rows: 5007MYSQL學習

??????? Extra: Using indexMYSQL學習

1 row in set (0.17 sec)MYSQL學習

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

mysql> EXPLAIN SELECT actor_id, last_nameMYSQL學習

??? -> FROM sakila.actor WHERE last_name = 'HOPPER'GMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: actorMYSQL學習

???????? type: refMYSQL學習

possible_keys: idx_actor_last_nameMYSQL學習

????????? key: idx_actor_last_nameMYSQL學習

????? key_len: 137MYSQL學習

????????? ref: constMYSQL學習

???????? rows: 2MYSQL學習

??????? Extra: Using where; Using indexMYSQL學習

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

create table actor(MYSQL學習

actor_id?int unsigned NOT NULL AUTO_INCREMENT,MYSQL學習

name????? varchar(16) NOT NULL DEFAULT '',MYSQL學習

password??????? varchar(16) NOT NULL DEFAULT '',MYSQL學習

PRIMARY KEY(actor_id),MYSQL學習

?KEY???? (name)MYSQL學習

) ENGINE=InnoDBMYSQL學習

insert into actor(name,password) values('cat01','1234567');MYSQL學習

insert into actor(name,password) values('cat02','1234567');MYSQL學習

insert into actor(name,password) values('ddddd','1234567');MYSQL學習

insert into actor(name,password) values('aaaaa','1234567');MYSQL學習

?MYSQL學習

mysql> explain select actor_id from actor order by actor_id GMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: actorMYSQL學習

???????? type: indexMYSQL學習

possible_keys: NULLMYSQL學習

????????? key: PRIMARYMYSQL學習

????? key_len: 4MYSQL學習

????????? ref: NULLMYSQL學習

???????? rows: 4MYSQL學習

??????? Extra: Using indexMYSQL學習

1 row in set (0.00 sec)MYSQL學習

?MYSQL學習

mysql> explain select actor_id from actor order by password GMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: actorMYSQL學習

???????? type: ALLMYSQL學習

possible_keys: NULLMYSQL學習

????????? key: NULLMYSQL學習

????? key_len: NULLMYSQL學習

????????? ref: NULLMYSQL學習

???????? rows: 4MYSQL學習

??????? Extra: Using filesortMYSQL學習

1 row in set (0.00 sec)MYSQL學習

?MYSQL學習

mysql> explain select actor_id from actor order by name GMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: actorMYSQL學習

???????? type: indexMYSQL學習

possible_keys: NULLMYSQL學習

????????? key: nameMYSQL學習

????? key_len: 18MYSQL學習

????????? ref: NULLMYSQL學習

???????? rows: 4MYSQL學習

??????? Extra: Using indexMYSQL學習

1 row in set (0.00 sec)MYSQL學習

?當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”.MYSQL學習

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

create table actor(MYSQL學習

actor_id?int unsigned NOT NULL AUTO_INCREMENT,MYSQL學習

name????? varchar(16) NOT NULL DEFAULT '',MYSQL學習

password??????? varchar(16) NOT NULL DEFAULT '',MYSQL學習

PRIMARY KEY(actor_id),MYSQL學習

?KEY???? (name)MYSQL學習

) ENGINE=InnoDBMYSQL學習

insert into actor(name,password) values('cat01','1234567');MYSQL學習

insert into actor(name,password) values('cat02','1234567');MYSQL學習

insert into actor(name,password) values('ddddd','1234567');MYSQL學習

insert into actor(name,password) values('aaaaa','1234567');MYSQL學習

SET AUTOCOMMIT=0;MYSQL學習

BEGIN;MYSQL學習

SELECT actor_id FROM actor WHERE actor_id < 4MYSQL學習

AND actor_id <> 1 FOR UPDATE;MYSQL學習

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

?MYSQL學習

mysql> EXPLAIN SELECT actor_id FROM test.actorMYSQL學習

??? -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE GMYSQL學習

*************************** 1. row ***************************MYSQL學習

?????????? id: 1MYSQL學習

?select_type: SIMPLEMYSQL學習

??????? table: actorMYSQL學習

???????? type: indexMYSQL學習

possible_keys: PRIMARYMYSQL學習

????????? key: PRIMARYMYSQL學習

????? key_len: 4MYSQL學習

????????? ref: NULLMYSQL學習

???????? rows: 4MYSQL學習

??????? Extra: Using where; Using indexMYSQL學習

1 row in set (0.00 sec)MYSQL學習

?MYSQL學習

mysql>MYSQL學習

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

SET AUTOCOMMIT=0;MYSQL學習

BEGIN;MYSQL學習

SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;MYSQL學習

??該查詢會被掛起,直到第一個連接的事務提交釋放鎖時,才會執行(這種行為對于基于語句的復制(statement-based replication)是需要的).
如上所示,當使用索引時,InnoDB會鎖住它不需要的元組.更糟糕的是,如果查詢不能使用索引,MySQL會進行全表掃描,并鎖住每一個元組,不管是否真正需要.

分享:Mysql索引優化的技巧
深入理解Mysql的列索引和多列索引
了解 MySQL 主鍵與索引的聯系與區別
mysql性能優化之索引優化MYSQL學習

維易PHP培訓學院每天發布《Mysql學習深入理解MySQL索引與優化》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11577.html