[MySQL]ANALYZE TABLE 更新索引基数
2023-02-18 15:41:37 时间
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)
相关文章
- 忙活了一年的开源社区,终于赶上了春节前的末班车!
- ChatGPT 会开源吗?
- 7 款殿堂级的开源 CMS(内容管理系统)
- 请收下这 10 个安全相关的开源项目
- MySQL 5.7 升级到 8.0
- 越折腾越好用的 3 款开源 APP
- 10 款更先进的开源命令行工具
- 对开源框架跃跃欲试,却在写的时候犯了难?
- 一大波开源小抄来袭
- 物联网?快来看 Arduino 上云啦
- 想做钢铁侠?听说很多大佬都是用它入门的
- 写给小白的开源编译器
- 支持中文!秒建 wiki 知识库的开源项目,构建私人知识网络
- 一款开源的文件搜索神器,终于不用记 find 命令了
- 用一个文件,实现迷你 Web 框架
- 一个文件的开源项目,开启你的开源之旅
- 3.6 万颗星!开源 Web 服务器后起之秀,自带免费 HTTPS 开箱即用
- 狠人!标星 3.4 万的项目说删就删,几行代码搞崩数万个开源项目
- 那些年的开源项目,你跑起来了吗?
- 重玩 40 年前的经典游戏小蜜蜂,这次通关了源码