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

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

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

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

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

這里是我自己的一張表，數(shù)據(jù)量并不高，只是為了測(cè)驗(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

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

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

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

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

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

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

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

這邊再說(shuō)一下explain這個(gè)命令

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

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

2.select_type select類型

2.1.SIMPLE 進(jìn)行不需要Union操作或不含子查詢的簡(jiǎn)單select查詢時(shí)，響應(yīng)查詢語(yǔ)句的select_type 即為simple，無(wú)論查詢語(yǔ)句是多么復(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í)表，用于存儲(chǔ)聯(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 對(duì)于eq_ref的解釋，mysql手冊(cè)是這樣說(shuō)的:”對(duì)于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行。除了const類型，這可能是最好的聯(lián)接類型”
4.3 ref 對(duì)于每個(gè)來(lái)自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說(shuō)，如果聯(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包含了使用的索引的最長(zhǎng)的關(guān)鍵元素
4.6 unique_subquery
4.7 index_subquery
4.8 range 給定范圍內(nèi)的檢索，使用一個(gè)索引來(lái)檢查行
4.9 index 該聯(lián)接類型與ALL相同，除了只有索引樹(shù)被掃描。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。（也就是說(shuō)雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
4.10 ALL 對(duì)于每個(gè)來(lái)自于先前的表的行組合，進(jìn)行完整的表掃描。如果表是第一個(gè)沒(méi)標(biāo)記const的表，這通常不好，并且通常在它情況下很差。通?？梢栽黾痈嗟乃饕灰褂肁LL，使得行能基于前面的表中的常數(shù)值或列值被檢索出

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

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

7.key_len MYSQL使用的索引長(zhǎng)度

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

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

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

可以看到 id查詢 使用了id索引 簡(jiǎn)單查詢 查詢類型為最優(yōu)的常量查詢
然后咱們換一個(gè)查詢?cè)囋嚕@次使用作者索引

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

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

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

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

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

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

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

國(guó)外服務(wù)器租用

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。