MySQL為數據表建立索引的原則詳解

Posted on by

面試題：

  • 索引是什麼？
  • 索引的優點？
  • 索引的缺點？
  • 在建立索引的時候都有哪些需要考慮的因素呢?
  • 為數據表建立索引的原則有哪些？
  • 什麼是索引覆蓋？
  • 非聚簇索引一定會回表查詢嗎?

1. 索引是什麼？

索引是幫助我們實現快速查詢數據庫的數據結構。
在很多時候，表中存放的記錄非常的多，需要用到好多的數據頁來存放這些記錄，在很多頁中查找記錄可以分為2個步驟：

1、定位到記錄所在的頁

2、從所在頁內查找相應的記錄

在沒有索引的情況下，無論是根據主鍵列還是其他列的值進行查找，由於我們不能快速的定位到記錄所在的頁，所以隻能從第一頁沿著雙向鏈表一直往下找，因為需要遍歷所有的數據頁，這種方式超級消耗性能。因此人們期望一種能高效完成搜索的方法，因此就出現瞭索引，索引就是一種數據結構。

2. 索引的優點?

索引可以讓服務器快速的定位到表的指定位置 （索引大大減少瞭服務器需要需要掃描的數據量）。最常見的B-Tree索引，按照順序存儲數據，所以mysql可以來做order by和group by操作（索引可以幫助服務器避免排序和臨時表）。因為數據是有序的，所以B-Tree也就會將相關的列值都存儲在一起（索引可以將隨機IO變為順序IO）。因為索引中存儲瞭實際的列值，所以某些查詢值使用索引就能完成全部查詢。因此，總結下來的索引的優點為：

1、索引大大減少瞭服務器需要需要掃描的數據量；

2、索引可以幫助服務器避免排序和臨時表；

3、索引可以將隨機IO變為順序IO;

3. 索引的缺點?

1、空間上的代價：

建立索引需要占用物理空間

InnoDB存儲引擎默認使用的B+樹索引，每建立一個索引，都要為它建立一顆B+樹，每一顆B+樹的每一個節點都是一個數據頁，一個數據頁默認會占用16KB的存儲空間，而一顆很大的B+樹由許多數據頁組成，這將占用很大的一片存儲空間。

2、時間上的代價：

因為每次對表記錄進行增刪改，需要進行動態維護索引，導致增刪改時間變長

每當對表中的數據進行增刪改查操作時，都需要修改各個B+樹索引，B+樹中的每層節點都按照索引列的值從小到大的順序組成瞭雙向鏈表，無論是葉子節點中的記錄還是非葉子節點中的記錄都按照索引列的值從小到大的順序排成瞭單向鏈表。而增刪改查操作可能會對節點和記錄的排序造成破壞，所以存儲引擎需要額外的時間進行頁面分裂、頁面回收等操作，以維護節點和記錄的順序。

在執行查詢語句前，首先要生成一個執行計劃，一般情況下，一條查詢語句在執行過程中最多使用一個二級索引，因此，在生成執行計劃時需要計算使用不同索引執行查詢時所需的成本，最後選取成本最小的那個索引執行查詢，如果建立瞭太多的索引，可能會導致成本分析耗時太多，從而影響查詢語句的執行性能。

4. 在建立索引的時候,都有哪些需要考慮的因素呢?

在創建和使用索引是應該註意下列事項：

  • 隻為用於搜索、排序、分組的列創建索引；
  • 當列中不重復值的個數在總記錄條數中的占比很大時，才為列建立索引；
  • 索引列的類型盡量小；
  • 可以隻為索引列前綴創建索引，以減少蘇嘔心占用的存儲空間；
  • 盡量使用覆蓋索引進行查詢，以畢淼回表操作帶來的性能損耗；
  • 讓索引列以列名的形式單獨出現在搜索條件中；
  • 為瞭盡可能少的讓聚簇索引發生頁面分裂的情況，建議讓主鍵擁有anto_increment屬性;
  • 定位並刪除表中的冗餘和重復索引；

建立一張數據庫表：

create table single_table(
	id int not auto_increment, 
	key1 varchar(100),         
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
    common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二級索引
	unique key uk_key2(key2), # 二級索引，而且該索引是唯一二級索引
	key idx_key3(key3),       # 二級索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二級索引，也是聯合索引
)Engine=InnoDB CHARSET=utf8;

1、隻為用於搜索、排序、分組的列創建索引

我們隻為出現在where子句中的列、order by或group by子句中的列、連接子句中的連接列創建索引。僅出現在查詢列表中的列就沒有必要建立索引瞭。

比如下面的查詢語句：

select common_field,key_part3 from single_table where key1='a';

我們隻需要為出現在where子句中的key1建立索引就可以瞭，而查詢列表中的common_field、key_part3這兩個列就沒有必要建立索引瞭。

2、索引列的類型盡量小

在定義表結構時，要顯式的指定列的類型。以整數類型為例，有tingint、mediumint、int、bigint這幾種，他們占用的存儲空間的大小依次遞增，他們能表示的整數范圍當然也是依次遞增。如果想要對某個整數類型的列建立索引，在表示的整數范圍允許的情況下，盡量讓索引列使用較小的類型，比如使用int就不要用bigint，因為數據類型越小，索引占用的存儲空間就越少，在一個數據頁內就可以存放更多的記錄，磁盤IO帶來的性能損耗也就越小，讀寫效率也就越高。

這個建議對表的主鍵來說更加適用，因為不僅聚簇索引會存儲主鍵值，所有的二級索引的節點都會存儲一份記錄的主鍵值，如果主鍵使用更小的數據類型，也就意味著能節省更多的存儲空間。

3、為列前綴建立索引

一個字符串其實是由若幹個字符組成的，假如字符串很長，那麼存儲這個字符串就會占用很多的存儲空間。在為這個字符串所在的列建立索引時，就需要在對應的B+樹中，把列的完整字符串存儲起來，字符串越長，在索引中占用的存儲空間越大。

索引列的字符串前綴其實也是排好序的，所以索引的設計人員設計瞭一個方案，即隻將字符串的前幾個字符存放到索引中，也就是說二級索引的記錄中隻保留字符串的前幾個字符。

比如可以這樣修改idx_key1索引，讓索引中隻保留字符串的前10個字符：

alert table single_table drop index idx_key1;
alert table single_table add index idx_key1(key1(10));

然後再執行下面的查詢語句：

select * from single_table where key1='abcdefghijklmn';

由於在idx_key1的二級索引記錄中隻保留字符串的前10個字符，所以我們隻能定位到前綴為‘abcdefghij'的二級索引記錄，在掃描這些二級索引記錄時再判斷它們是否滿足key1='abcdefghijklmn'條件，當列中存儲的字符串包含的字符較多時，這種為列前綴建立索引的方式可以明顯減少索引大小。

不過，在隻對前綴建立索引的情況下，下面這個查詢語句就不能使用索引來完成排序需求瞭：

select * from single_table order by key1 limit 10;

因為二級索引idx_key1中不包含完整的key1列信息，所以在僅使用idx_key1索引執行查詢時，無法對key1列前10個字符相同但其餘字符不同的記錄進行排序，也就是說，隻為列前綴建立索引的方式無法支持使用索引進行排序的需求。

4、覆蓋索引

為瞭徹底告別回表操作帶來的性能損耗，建議最好在查詢列表中隻包含索引列，比如這個查詢語句：

select key1,id from single_table where key1>'a' and key1<'c';

由於我們隻查詢key1列和id列的值，所以在使用idx_key1索引來掃描('a','c')區間中的二級索引時，可以直接從獲取到的二級索引記錄中讀出key1列和id列的值，而不需要再通過id值到聚簇索引中執行回表操作瞭，這樣就省去瞭回表操作帶來的性能損耗。

我們把這種索引中已經包含瞭所有需要讀取的列的查詢方式稱為覆蓋索引。如果索引的葉子節點中已經包含要查詢的數據，那麼還有必要再回表查詢呢？如果一個索引包含所有需要查詢的字段的值，就稱為覆蓋索引。

排序操作也優先使用覆蓋索引進行查詢，比如下面這個查詢語句：

select key1 from single_table order by key1;

雖然這個查詢語句中沒有limit子句，但是由於可以采用覆蓋索引，所以查詢優化器會直接使用idx_key1索引進行排序，而不需要執行回表操作。

當然，如果業務需要查詢索引列以外的列，還是要以保證業務需求為重，如無必要，最好把業務中需要的列放在查詢列表中，而不是以簡單的*替代。

5、讓索引列以列名的形式在搜索條件中單獨出現

在下面這兩個查詢語句中，搜索條件中的語義是一樣的：

select * from single_table where key2*2<4;
select * from single_table where key2<4/2;

在第一個查詢語句的搜索條件中，key2列並不是以單獨列名的形式出現的，而是以key2*2這樣的形式表現的，MySQL並不會嘗試簡化key2*2<4表達式，而是直接認為這個搜索條件不能形成合適的掃描區間來減少需要掃描的記錄數量，所以該查詢語句隻能以全表掃描的方式來執行。

在第二個查詢語句的搜索條件中，key2列並是以單獨列名的形式出現的，MySQL可以分析出key2<2，這樣可以減少需要掃描的記錄數量。所以MySQL可能使用uk_key2來執行查詢。

所以，如果想讓某個查詢使用索引來執行，請讓索引列以列名的形式單獨出現在搜索條件中。

6、新插入記錄時主鍵大小對效率的影響

我們知道，對於一個使用InnoDB存儲引擎的表來說，在沒有顯式創建索引時，表中的數據實際上存儲在聚簇索引的葉子節點中，而且B+樹的每一層數據頁以及頁面中的記錄都是按照主鍵值從小到大的順序排序的，如果新插入記錄的主鍵值是依次遞增的話，每插滿一個數據頁就換到下一個數據頁繼續插入，如果新插入記錄的主鍵值忽大忽小，就比較麻煩瞭。

假設某個數據頁存儲的聚簇索引記錄已經滿瞭，它存儲的主鍵值在1~100之間，此時如果再插入一提哦啊主鍵值為9的記錄，因為這個數據頁已經滿瞭，新紀錄應該插入到哪裡呢？

我們需要把當前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新創建的一些頁中，頁分裂意味著性能損耗，所以如果想盡量避免這種無謂的性能損耗，最好讓插入記錄的主鍵值依次遞增。即讓主鍵擁有auto_increment屬性，MySQl會自動為新插入的記錄生成遞增的主鍵值。

7、冗餘和重復索引

針對single_table表，可以單獨針對key_part1列建立一個idx_key_part1索引：

alert table single_table and index idx_key_part1(key_part1);

而此時我們已經有瞭一個針對key_part1、key_part2、key_part3列建立的聯合索引idx_key_par。idx_key_part索引的二級索引記錄本身就是按照key_part1列的值排序的，此時再單獨為key_part1列建立一個索引其實是沒必要的，我們可以把整個新建的idx_key_part1索引看作一個冗餘索引，該冗餘索引是沒有必要的。

有時，我們可能會對同一個列建立多個索引，比如下面兩個添加索引的語句：

alert table single_table add unique key uk_id(id);
alert table single_table add index idx_id(id);

我們針對id列又建立瞭一個唯一二級索引uk_id，還建立瞭一個普通二級索引idx_id，可是id列本身就是single_table表的主鍵，InnoDB自動為該列建立瞭聚簇索引，此時uk_id和idx_id就是重復的，這種重復索引應該避免。

總結

本篇文章就到這裡瞭，希望能夠給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容!   

推薦閱讀：