mysql索引基数概念与用法示例
本文实例讲述了mysql索引基数概念与用法。分享给大家供大家参考，具体如下：
Cardinality（索引基数）是mysql索引很重要的一个概念
索引基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、2、3、4、5、1，那么它的基数就是5。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有”M”和”F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”
另一个概念叫做索引选择性
索引选择性=索引基数/数据总数，基数可以通过“showindexfrom表名”查看。
越接近1就越有可能利用索引，也可以理解1为百分百
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。
这里是我自己的一张表，数据量并不高，只是为了测验索引选择性
select*fromarticles id标题姓名 15Title0大熊 16Title1大熊 17Title2大熊 18Title3大熊 19Title4大熊 20Title5大熊 21Title6大熊 22Title7大熊 23Title8二熊 24Title9二熊
id为默认整型自增主键
现在增加author列为索引并查看articles表的所有索引
ALTERTABLE`articles`ADDINDEX(`author`) SHOWINDEXFROMarticles tablenon_uniquekey_nameseq_in_indexcolumn_namecollationcardinalitysub_partpackednullindex_type articles0PRIMARY1idA10NULLNULLBTREE articles1author1authorA2NULLNULLBTREE
说下各个字段的意义
- table表名
- non_unique如果索引不能包括重复词，则为0。如果可以，则为1。
- key_name索引名
- seq_in_index索引中的列序列号，从1开始
- column_name列名
- collation列以什么方式存储在索引中。在MySQLSHOWINDEX语法中，有值'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=1author索引选择性为2/10=0.2，下面咱们测试一下，使用explain看一下语句分析
explainselect*fromarticleswhereid=15 idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredextra 1SIMPLEarticlesNULLconstPRIMARYPRIMARY4const1100.00NULL
这边再说一下explain这个命令
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。说一下每个字段的意义
1.idSELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序
2.select_typeselect类型
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.DEPENDENTUNIONdependent与UNIONselect_type一样，dependentunion出现在union或unionall形成的集合查询中。此处的dependent表示union或unionall联合而成的单位查询受外部影响
2.5.unionresultunionresult为包含union结果的数据表
3.table表名
4.type连接类型，有多个参数，先从最佳类型到最差类型介绍也是本篇的重点
4.2eq_ref对于eq_ref的解释，mysql手册是这样说的:”对于每个来自于前面的表的行组合，从该表中读取一行。除了const类型，这可能是最好的联接类型”
4.3ref对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARYKEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的
4.4ref_or_null该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化
4.5index_merge该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素
4.6unique_subquery
4.7index_subquery
4.8range给定范围内的检索，使用一个索引来检查行
4.9index该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
4.10ALL对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出
5.possible_keys提示使用哪个索引会在该表中找到行，不太重要
6.keys指明MYSQL查询使用的索引
7.key_lenMYSQL使用的索引长度
8.ref显示使用哪个列或常数与key一起从表中选择行
9.rows显示MYSQL执行查询的行数，数值越大越不好，说明没有用好索引
10.Extra该列包含MySQL解决查询的详细信息
可以看到id查询使用了id索引简单查询查询类型为最优的常量查询
然后咱们换一个查询试试，这次使用作者索引
explainselect*fromarticleswhereauthor="大熊" 1SIMPLEarticlesNULLALLauthorNULLNULLNULL1080.00Usingwhere
可以很清楚的看到使用了author索引简单查询查询类型为最差的全表扫描
先不急着解释，还是这个语句，咱们换成二熊
explainselect*fromarticleswhereauthor="二熊" 1SIMPLEarticlesNULLrefauthorauthor1022const2100.00NULL
可以看到这次的查询类型为ref
也就是说，因为作者为大熊的数据行数，超过了总数据的百分之30所以mysql认为全表扫描比使用索引更快
这就是索引基数和索引选择性这个概念的意义，所以在建立索引的时候，要注意要把索引建立在索引基数高的列上
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。