怎样理解MySQL索引cardinalit
发布时间：2022-01-12 11:03:39 所属栏目：MySql教程 来源：互联网
导读：本篇内容主要讲解如何理解MySQL索引cardinalit，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习如何理解MySQL索引cardinalit吧! 查看一个表的索引： mysql show index from rank_item; +-----------+----------
|
本篇内容主要讲解“如何理解MySQL索引cardinalit”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何理解MySQL索引cardinalit”吧! 查看一个表的索引： 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表中用户，一般来说很少重复； 到此，相信大家对“如何理解MySQL索引cardinalit”有了更深的了解，不妨来实际操作一番吧！ （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐