MySQL的show index 选择率

文章由LinuxBoy分享于2019-07-24 11:07:32热评（399）

MySQL的show index 选择率

show index from tbl_name\G;

里面的每个字段信息各代表什么呢？

DROP TABLE IF EXISTS t;

CREATE TABLE t(

a int not null,

b varchar(2000) ,

c int not null,

d int,

e varchar(200),

primary key(a),

key idx_b(b),

key idx_c(c),

key idx_c_b(c,b),

unique key(d),

key idx_e(e(10))

)engine=innodb;

mysql>show index from t;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t | 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | | BTREE | | |

| t | 0 | d | 1 | d | A | 0 | NULL | NULL | YES | BTREE | | |

| t | 1 | idx_b | 1 | b | A | 0 | 191 | NULL | YES | BTREE | | |

| t | 1 | idx_c | 1 | c | A | 0 | NULL | NULL | | BTREE | | |

| t | 1 | idx_c_b | 1 | c | A | 0 | NULL | NULL | | BTREE | | |

| t | 1 | idx_c_b | 2 | b | A | 0 | 191 | NULL | YES | BTREE | | |

| t | 1 | idx_e | 1 | e | A | 0 | 10 | NULL | YES | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

7 rows in set (0.00 sec)

#说明

TABLE：索引所在的表名

Non_unique:非唯一的索引，必须要唯一，例如上面定义到主键a，unique d 都是显示是0

Key_name:索引的名字

Seq_in_index:索引中该列的位置，如idx_c_b 的联合索引

Column_name:索引列的名称

Collation:列是以什么方式存在在索引中索引中的，可以是A或是NULL，B+树索引总是A，即是排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了

因为Hash根据hash桶存放索引数据的，而不是对数据进行排序。

Cardinalilty:这个值非常关键，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1（为什么？怎么计算这个值？），下面会对这个字段进行详细的说明：

Sub_part:是否是列的部分索引，例如上面的idx_e就显示10，表示只对e列的前10个字符进行索引。如果索引整个列，则该字段为NULL。(idx_b,idx_c_b为什么只索引191个呢？)

Packed:关键字如何被压缩。若没有，则显示为NULL

Null:是否索引的列含有NULL值，例如看到的idx_b,就表示可以有NULL值，所以显示YES，而主键和定义了c列就不允许有NULL值

Index_type:索引的类型，InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。

Comment:注释

Index_comment:索引注释

////////////////////////////////////////

Cardinalilty:因为单词的意思为：基数、基准的意思

并不是在所有的查询条件中出现的列都需要添加索引，对于什么时候添加B+树索引，一般情况下，在访问表中很少的一部分数据时使用B+树索引才有意义。对于性别字段、地区字段、

类型字段，它们可取值的范围很小，成为低选择性。

e.g:

select * from stu where sex='F';

按性别进行查询时，可取值的范围一般只有'M','F'。因此上述得到结果可能是表50%的数据。这时添加索引完全没有必要。

相反，如果某个字段的取值范围比较广，几乎没有重复，即属于高选择性，则使用索引比较合适。

那怎么样看索引是否有高选择率呢？

一是通过show index结果中的列Cardinalilty来观察，此值表示索引中不重复记录数量的预估值（是通过采用来进行计算的），这个值不是一个精确值。Cardinalilty/table_row_counts尽可能的接近1

InnoDB存储引擎内部对更新Cardinalilty信息的策略为：

1.表中1/16的数据已发生变化就需要更新信息

2.stat_modified_counter>2 000 000 000 (20亿)

也是就是当计数器stat_modified_counter发生变化的次数大于20亿时，需要更新Cardinalilty信息。

第二种方法可以用SQL语句来进行计算是否是高选择率：

DROP TABLE IF EXISTS t_car;

CREATE TABLE t_car(

id BIGINT NOT NULL AUTO_INCREMENT ,

mem_id BIGINT NOT NULL,

status TINYINT(1),

dept_no INT NOT NULL,

PRIMARY KEY(id),

KEY idx_mem_id(mem_id),

KEY idx_status(status),

KEY idx_dept_no(dept_no)

)ENGINE=innodb;

insert into t_car values(NULL,1,1,101);

insert into t_car values(NULL,2,0,102);

insert into t_car values(NULL,3,1,103);

insert into t_car values(NULL,4,1,104);

insert into t_car values(NULL,5,0,105);

insert into t_car values(NULL,6,1,106);

insert into t_car values(NULL,7,1,107);

insert into t_car values(NULL,8,0,108);

insert into t_car values(NULL,9,1,109);

insert into t_car values(NULL,10,1,110);

insert into t_car

select NULL,id,status,dept_no from t_car; -- 多多执行几次

mysql>select count(*) from t_car;

+----------+

| count(*) |

+----------+

| 20480 |

+----------+

1 row in set (0.10 sec)

mysql>update t_car set mem_id=id;

Query OK, 20460 rows affected (3.43 sec)

Rows matched: 20480 Changed: 20460 Warnings: 0

mysql>show index from t_car;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t_car | 0 | PRIMARY | 1 | id | A | 20108 | NULL | NULL | | BTREE | | |

| t_car | 1 | idx_status | 1 | status | A | 10054 | NULL | NULL | YES | BTREE | | |

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

root@localhost[zjkj]:04:07:14>select count(distinct(id))/count(*) as id_select,count(distinct(status))/count(*) as status from t_car;

+-----------+--------+

| id_select | status |

+-----------+--------+

| 1.0000 | 0.0001 |

+-----------+--------+

1 row in set (0.16 sec)

#说明id列的选择率较高，适合建立索引，而status列选择性较低，因此status列上不适合建立索引。

这也是为什么Cardinality表的行数应尽可能接近1越好的缘故了。

推荐文章：

MySQL的show index 选择率