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;
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | 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;
- +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
- +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | t_car | 0 | PRIMARY | 1 | id | A | 20108 | NULL | NULL | | BTREE | | |
- | t_car | 1 | idx_mem_id | 1 | mem_id | A | 20108 | NULL | NULL | | BTREE | | |
- | t_car | 1 | idx_status | 1 | status | A | 10054 | NULL | NULL | YES | BTREE | | |
- | t_car | 1 | idx_dept_no | 1 | dept_no | A | 20108 | NULL | NULL | | 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 越好的缘故了.
来源: http://www.linuxidc.com/Linux/2019-07/159482.htm