本文實例講述了mysql索引基數概念與用法。分享給大家供大家參考，具體如下：

Cardinality（索引基數）是mysql索引很重要的一個概念

索引基數是數據列所包含的不同值的數量。例如，某個數據列包含值1、2、3、4、5、1，那么它的基數就是5。索引的基數相對于數據表行數較高（也就是說，列中包含很多不同的值，重復的值很少）的時候，它的工作效果最好。如果某數據列含有很多不同的年齡，索引會很快地分辨數據行。如果某個數據列用于記錄性別（只有”M”和”F”兩種值），那么索引的用處就不大。如果值出現的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數據行。在這些情況下，最好根本不要使用索引，因為查詢優化器發現某個值出現在表的數據行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是”30%”

另一個概念叫做索引選擇性

索引選擇性=索引基數/數據總數，基數可以通過“show index from 表名”查看。
越接近1就越有可能利用索引，也可以理解1為百分百
高索引選擇性的好處就是mysql查找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1。

這里是我自己的一張表，數據量并不高，只是為了測驗索引選擇性

select * from articles
id 標題 姓名
15 Title 0 大熊
16 Title 1 大熊
17 Title 2 大熊
18 Title 3 大熊
19 Title 4 大熊
20 Title 5 大熊
21 Title 6 大熊
22 Title 7 大熊
23 Title 8 二熊
24 Title 9 二熊

id為默認整型自增主鍵

現在增加author列為索引并查看articles表的所有索引

ALTER TABLE `articles` ADD INDEX (`author`)
SHOW INDEX FROM articles
table non_unique key_name seq_in_index column_name collation cardinality sub_part packed null index_type
articles 0 PRIMARY 1 id A 10 NULL NULL  BTREE
articles 1 author 1 author A 2 NULL NULL  BTREE

說下各個字段的意義

• table 表名
• non_unique 如果索引不能包括重復詞，則為0。如果可以，則為1。
• key_name 索引名
• seq_in_index 索引中的列序列號，從1開始
• column_name 列名
• collation 列以什么方式存儲在索引中。在MySQLSHOW INDEX語法中，有值'A'（升序）或NULL（無分類）
• cardinality 索引基數
• sub_part 如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為NULL。
• packed 指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。
• null 如果列含有NULL，則含有YES。如果沒有，則該列含有NO。
• index_type 所用索引存儲方法（BTREE, FULLTEXT, HASH, RTREE）

可以看到，表articles已經有兩個索引了

id索引不包含重復詞 為主鍵 列名id 升序排列 索引基數為10 沒有部分索引 沒有壓縮 不含null 存儲方式為btree

author索引包含重復詞 索引名author 列名author 升序排列 索引基數為2 沒有部分索引 沒有壓縮 不含null 存儲方式為btree

根據索引選擇性的算法，id索引選擇性為10/10 =1 author索引選擇性為2/10=0.2， 下面咱們測試一下，使用explain看一下語句分析

explain select * from articles where id = 15
id select_type table partitions type possible_keys key key_len ref rows filtered extra
1 SIMPLE articles NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL

這邊再說一下explain這個命令

explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。說一下每個字段的意義

1.id SELECT識別符。這是SELECT查詢序列號。這個不重要,查詢序號即為sql語句執行的順序

2.select_type select類型

2.1.SIMPLE 進行不需要Union操作或不含子查詢的簡單select查詢時，響應查詢語句的select_type 即為simple，無論查詢語句是多么復雜，執行計劃中select_type為simple的單位查詢一定只有一個
2.2.PRIMARY 一個需要Union操作或含子查詢的select查詢執行計劃中，位于最外層的select_type即為primary。與simple一樣，select_type為primary的單位select查詢也只存在1個
2.3.union 由union操作聯合而成的單位select查詢中，除第一個外，第二個以后的所有單位select查詢的select_type都為union。union的第一個單位select的select_type不是union，而是DERIVED。它是一個臨時表，用于存儲聯合（Union）后的查詢結果
2.4.DEPENDENT UNION dependent 與UNION select_type一樣，dependent union出現在union或union all 形成的集合查詢中。此處的dependent表示union或union all聯合而成的單位查詢受外部影響
2.5.union result union result為包含union結果的數據表

3.table 表名

4.type 連接類型，有多個參數，先從最佳類型到最差類型介紹 也是本篇的重點

4.1 const，表最多有一個匹配行，const用于比較primary key 或者unique索引。因為只匹配一行數據，所以很快，也可以理解為最優化的索引，常數查找
4.2 eq_ref 對于eq_ref的解釋，mysql手冊是這樣說的:”對于每個來自于前面的表的行組合，從該表中讀取一行。除了const類型，這可能是最好的聯接類型”
4.3 ref 對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基于關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的
4.4 ref_or_null 該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化
4.5 index_merge 該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素
4.6 unique_subquery
4.7 index_subquery
4.8 range 給定范圍內的檢索，使用一個索引來檢查行
4.9 index 該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
4.10 ALL 對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數值或列值被檢索出

5.possible_keys 提示使用哪個索引會在該表中找到行，不太重要

6.keys 指明MYSQL查詢使用的索引

7.key_len MYSQL使用的索引長度

8.ref 顯示使用哪個列或常數與key一起從表中選擇行

9.rows 顯示MYSQL執行查詢的行數，數值越大越不好，說明沒有用好索引

10.Extra 該列包含MySQL解決查詢的詳細信息

可以看到 id查詢 使用了id索引 簡單查詢 查詢類型為最優的常量查詢
然后咱們換一個查詢試試，這次使用作者索引

explain select * from articles where author = "大熊"
1 SIMPLE articles NULL ALL author NULL NULL NULL 10 80.00 Using where

可以很清楚的看到 使用了author索引 簡單查詢 查詢類型為最差的全表掃描
先不急著解釋，還是這個語句，咱們換成二熊

explain select * from articles where author = "二熊"
1 SIMPLE articles NULL ref author author 1022 const 2 100.00 NULL

可以看到這次的查詢類型為ref

也就是說，因為作者為大熊的數據行數，超過了總數據的百分之30 所以mysql認為全表掃描比使用索引更快
這就是索引基數和索引選擇性這個概念的意義，所以在建立索引的時候，要注意要把索引建立在索引基數高的列上

更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》

希望本文所述對大家MySQL數據庫計有所幫助。