[MySQL]ANALYZE TABLE 更新索引基数
[MySQL]ANALYZE TABLE 更新索引基数
唯一Chat
发布于 2020-03-19 14:28:51
发布于 2020-03-19 14:28:51
MySQL使用存储的键分布基数来确定表连接顺序 在决定对查询中的特定表使用哪些索引时，也会使用使用键分布基数
ANALYZE TABLE 表名 可以更新表的索引基数，使其更接近非重复的记录数，记录数可以使用show index from 表 来查询cardinality字段
mysql> show index from index_test;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| index_test | 1 | score_index | 1 | score | A | 2 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 2 | gid | A | 3 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 3 | age | A | 6 | NULL | NULL | | BTREE | | |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> select * from index_test;
+----+------------+-----+-----+-------+
| id | name | gid | age | score |
+----+------------+-----+-----+-------+
| 1 | taoshihan | 2 | 0 | 0 |
| 2 | taoshihan1 | 2 | 0 | 0 |
| 3 | taoshihan2 | 3 | 10 | 10 |
| 4 | taoshihan | 2 | 1 | 0 |
| 5 | taoshihan | 2 | 2 | 0 |
| 6 | taoshihan | 2 | 3 | 0 |
+----+------------+-----+-----+-------+
6 rows in set (0.03 sec)
mysql> ANALYZE TABLE index_test;
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| my_test.index_test | analyze | status | OK |
+--------------------+---------+----------+----------+
1 row in set (0.13 sec)
mysql> show index from index_test;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test | 0 | PRIMARY | 1 | id | A | 6这里变了 | NULL | NULL | | BTREE | | |
| index_test | 1 | score_index | 1 | score | A | 2 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 2 | gid | A | 3 | NULL | NULL | | BTREE | | |
| index_test | 1 | name_gid_age_index | 3 | age | A | 6 | NULL | NULL | | BTREE | | |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.07 sec)本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2020-02-27 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。