ITPub博客

首页 > 数据库 > MySQL > MySQL索引之cardinality

MySQL索引之cardinality

原创 MySQL 作者:StevenBeijing 时间:2020-08-06 12:03:47 0 删除 编辑

查看一个表的索引:

mysql> show index from rank_item;
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rank_item |          0 | PRIMARY            |            1 | id          | A         |     5665508 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_city_category  |            1 | city        | A         |        2713 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_city_category  |            2 | category    | A         |        3798 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_artisan_id     |            1 | artisan_id  | A         |       33916 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | index_weight       |            1 | weight      | A         |       11680 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | product_id_plan_id |            1 | product_id  | A         |     1480432 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | product_id_plan_id |            2 | plan_id     | A         |     5590288 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            1 | category    | A         |        3170 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            2 | city        | A         |       11417 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            3 | artisan_id  | A         |       46514 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            1 | category    | A         |        3187 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            2 | city        | A         |       10869 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            3 | plan_id     | A         |       17403 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            4 | weight      | A         |      659306 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      上面有一个属性Cardinality,可以通过观察它来评估索引是否合理。它会估计索引中不重复记录,如果这个相对值很小,可能就要评估索引是否有意义。

       查看表的总行数:

mysql> select count(*) as total from rank_item;
+---------+
| total   |
+---------+
| 5581872 |
+---------+

观察以下信息:

id列:Cardinality/total=5608506/5581872=1.005

city列: Cardinality/total=2713/ 5581872=0.0000486

category列: Cardinality/total=3170/ 5581872=0.0000568

        列id由于是主键,通过cardinality估算出来的值/总数接近于1;而另外2个索引列,估算出来的值/总数都趋近于0。 估算出来的值/总数=占比,我们称占比为相对值。

通过上面表格做一个大胆推测,查询id列是很快,查询另外2列是很慢;现在我们看下相应的执行计划。

mysql> explain select * from rank_item where id=2419;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | rank_item | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from rank_item where city=4967;
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys     | key               | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | rank_item | NULL       | ref  | idx_city_category | idx_city_category | 4       | const | 556680 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.04 sec)

      但是发现都会走索引,而且ref都是const。难道是cardinality不准?是的,因为它是一个预估值!

cardinality是怎么预估的?

      上面提到cardinality是索引中不重复记录的预估值,那么它是怎么实现的呢?由于Mysql的B+索引在每个存储引擎中实现的都不一样,所以cardinality干脆放到存储引擎层面实现的!

对于innodb来说,达到以下2点就会重新计算cardinality

  • 如果表中1/16的数据发生变化 

  • 如果stat_modified_counter>200 000 0000


       这是为什么呢?因为真实环境中,索引的更新可能非常频繁,比如一个表中数据的插入,更新,删除等,每次都去统计cardinality会带来很大的负担;另外如果是一个大表,统计一次可能非常耗时。基于此,采用基于上面2个条件的"抽样"统计的方式。

那上面2种有什么区别呢?

       如果表中1/16数据发生变化则会更新;第2种情况比较特别,如果某一千数据频繁更新,但是数据并没有增加,则第一种无法适用,所以设置stat_modified_counter为发生变化的次数;如果次数达到200 000 0000,也会更新统计值。

那具体是如何采样统计的呢?

  • 获取B+树叶子节点的数据,记为A
  • 随机获得B+树索引中8个叶子节点。统计每个页不同记录的个数,分别记为P1,P2...P8
  • 计算cardinality = (P1+P2+...P8)A/8

从而得出索引中不同记录的数量。从上面可以发现,有2个问题

1、由于是随机采样的方式,所以会出现,连续2次统计,数量都不同。只有在表数据非常少,叶子节点不多于8个时,每次采样都是取到相同的页,统计值才会相同。
2、由于统计值是基于上面2个条件去更新的,可能出现系统运行了一段时间之后,数据发生了很大变化,统计值偏差比较大了,那么索引的效率会下降。

那对于问题2,该怎么处理呢?

手动更新统计值

       如果系统运行一段时间之后,我们可以通过执行下面的sql,重新计算cardinality值。

analyze table tablename;

不过,如果表很大,重新统计可能会非常耗时间,建议对于核心表,在非高峰时段操作

选择性

       现在又回到前面的例子,我们通过观察执行计划发现,不论cardinality大小,相对值大小,发现还是会走索引,那为什么要说对于相对值非常小的不建议建索引呢?这就涉及到一个选择性的问题

       比如有一个用户表,有一列性别sex,现在要查询所以性别为male的用户(假定只有男人-male,女人-female,没有其它不明性别),可能的sql:

select * from user where sex = 'M';

           对于这个sql,虽然sex上有索引,但是执行的时候,读取的数据可能会超过一半,甚至在极端情况下(比如程序员的网站),大部分数据都需要读取,所以还是会走全表扫描,这种数据称为低选择性。反之,如果是高选择性的,建议建索引 ,比如user表中用户,一般来说很少重复;










来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2709686/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-02-10

  • 博文量
    284
  • 访问量
    247583