MySQL的show index 选择率(2)

并不是在所有的查询条件中出现的列都需要添加索引,对于什么时候添加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列上不适合建立索引。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/e25e214872a52c258b03180c4192b3b6.html