1. 首頁 > 生活百科 > >

「MySQL高級篇」MySQL索引原理，設計原則( 四 )

生活百科


  1. 按樓梯從低到高，無出現跳躍的情況--此時符合最左前綴原則，索引不會失效

「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
  1. 出現跳躍的情況
  • 直接第一層name都不走，當然都失效

「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
  • 走了第一層，但是后續直接第三層，只有出現跳躍情況前的不會失效（此處就只有name成功）

「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
  • 同時 ， 這個順序并不是由我們where中的排列順序決定，比如：
    • where name='小米科技' and status='1' and address='北京市'
    • where status='1' and name='小米科技' and address='北京市'
這兩個盡管where中字段的順序不一樣 ， 第二個看起來越級了，但實際上效果是一樣的
其實是因為我們MySQL有一個Optimizer（查詢優化器），查詢優化器會將SQL進行優化，選擇最優的查詢計劃來執行 。
  • 關于這個查詢優化器，后續文章我們也會談談MySQL的邏輯架構與存儲引擎
索引設計原則針對表
  1. 查詢頻次高，且數據量多的表
針對字段
  1. 最好從where子句的條件中提取 ， 如果where子句中的組合比較多，那么應當挑選最常用、過濾效果最好的列的組合 。
其他原則
  1. 最好用唯一索引，區分度越高，使用索引的效率越高
  2. 不是越多越好，維護也需要時間和空間代價，建議單張表索引不超過 5 個
因為 MySQL 優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引來進行評估 ， 以生成出一個最好的執行計劃，如果同時有很多個索引都可以用于查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢性能 。
比如：
我們創建了三個單列索引，name ， status，address
當我們where中根據status和address兩個字段來查詢時，數據庫只會選擇最優的一個索引，不會所有單列索引都使用 。最優的索引：具體是指所查詢表中 ， 辨識度最高(所占比例最少)的索引列，比如此處address中有一個辨識度很高的 '西安市'數據；
「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
?
  1. 使用短索引,索引創建之后也是使用硬盤來存儲的 ， 因此提升索引訪問的I/O效率，也可以提升總體的訪問效率 。假如構成索引的字段總長度比較短，那么在給定大小的存儲塊內可以存儲更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率 。
  2. 利用最左前綴 ， 比如有N個字段，我們不一定需要創建N個索引，可以用復合索引
也就是說 ， 我們盡量創建復合索引，而不是單列索引
創建復合索引: CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);就相當于 對name 創建索引 ; 對name , email 創建了索引 ; 對name , email, status 創建了索引 ;舉個栗子假設我們有這么一個表，id為主鍵，沒有創建索引：
CREATE TABLE `tuser` (`id` int(11) NOT NULL,`name` varchar(32) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),) ENGINE=InnoDB如果要在此處建立復合索引，我們要遵循什么原則呢？?
通過調整順序，可以少維護一個索引
  • 比如我們的業務需求里邊 ， 有如下兩種查詢方式：
    1. 根據name查詢
    2. 根據name和age查詢
如果我們建立索引（age,name），由于最左前綴原則 ， 我們這個索引能實現的是根據age ， 根據age和name查詢，并不能單純根據name查詢（因為跳躍了），為了實現我們的需求，我們還得再建立一個name索引；?
而如果我們通過調整順序，改成（name，age），就能實現我們的需求了，無需再維護一個name索引 ， 這就是通過調整順序，可以少維護一個索引 。
考慮空間->短索引
  • 比如我們的業務需求里邊，有以下兩種查詢方式：
    1. 根據name查詢
    2. 根據age查詢
    3. 根據name和age查詢
我們有兩種方案：
  1. 建立聯合索引(name,age)，建立單列索引：age索引 。
  2. 建立聯合索引(age,name)，建立單列索引：name索引 。
?
這兩種方案都能實現我們的需求 ， 這個時候我們就要考慮空間了，name字段是比age字段大的 ， 顯然方案1所耗費的空間是更小的，所以我們更傾向于方案1 。

推薦閱讀