mysql索引基數(shù)概念與用法示例

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

Cardinality（索引基數(shù)）是mysql索引很重要的一個(gè)概念

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

另一個(gè)概念叫做索引選擇性

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

這里是我自己的一張表，數(shù)據(jù)量并不高，只是為了測驗(yàn)索引選擇性

select * from articles
id 標(biāo)題 姓名
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為默認(rèn)整型自增主鍵

現(xiàn)在增加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

說下各個(gè)字段的意義

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

可以看到，表articles已經(jīng)有兩個(gè)索引了

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

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

根據(jù)索引選擇性的算法，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這個(gè)命令

explain顯示了mysql如何使用索引來處理select語句以及連接表?？梢詭椭x擇更好的索引和寫出更優(yōu)化的查詢語句。說一下每個(gè)字段的意義

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

2.select_type select類型

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

3.table 表名

4.type 連接類型，有多個(gè)參數(shù)，先從最佳類型到最差類型介紹 也是本篇的重點(diǎn)

4.1 const，表最多有一個(gè)匹配行，const用于比較primary key 或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快，也可以理解為最優(yōu)化的索引，常數(shù)查找
4.2 eq_ref 對于eq_ref的解釋，mysql手冊是這樣說的:”對于每個(gè)來自于前面的表的行組合，從該表中讀取一行。除了const類型，這可能是最好的聯(lián)接類型”
4.3 ref 對于每個(gè)來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯(lián)接類型是不錯(cuò)的
4.4 ref_or_null 該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化
4.5 index_merge 該聯(lián)接類型表示使用了索引合并優(yōu)化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關(guān)鍵元素
4.6 unique_subquery
4.7 index_subquery
4.8 range 給定范圍內(nèi)的檢索，使用一個(gè)索引來檢查行
4.9 index 該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
4.10 ALL 對于每個(gè)來自于先前的表的行組合，進(jìn)行完整的表掃描。如果表是第一個(gè)沒標(biāo)記const的表，這通常不好，并且通常在它情況下很差。通?？梢栽黾痈嗟乃饕灰褂肁LL，使得行能基于前面的表中的常數(shù)值或列值被檢索出

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

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

7.key_len MYSQL使用的索引長度

8.ref 顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行

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

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

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

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

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

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

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

也就是說，因?yàn)樽髡邽榇笮艿臄?shù)據(jù)行數(shù)，超過了總數(shù)據(jù)的百分之30 所以mysql認(rèn)為全表掃描比使用索引更快
這就是索引基數(shù)和索引選擇性這個(gè)概念的意義，所以在建立索引的時(shí)候，要注意要把索引建立在索引基數(shù)高的列上

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

希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。