mysql索引基数概念与用法示例

本文实例讲述了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数据库计有所帮助。