阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL的show index 选择率

209次阅读
没有评论

共计 4937 个字符,预计需要花费 13 分钟才能阅读完成。

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 越好的缘故了。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4937字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中