[MySQL]ANALYZE TABLE 更新索引基数
2023-02-18 15:41:18 时间
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)
相关文章
- <一>对象使用过程中背后调用了哪些方法
- <十>C++的四种类型转换
- <十>关于菱形继承
- <九>理解虚继承和虚基类
- <七>理解多态
- <八>理解抽象类
- <六>关于虚函数和动态绑定
- <五>关于虚析构函数
- <四>虚函数 静态绑定 动态绑定
- <三>关于重载 隐藏 覆盖
- <二>派生类的构造过程
- <一>继承的基本意义
- <八>通过new和delete重载实现对象池的应用
- <七>深入理解new和delete的原理
- <四>MyVector中加入迭代器功能
- <三>自己实现string,加入迭代器功能
- <二>自己实现简单的string
- <一>关于运算符重载
- <四>理解空间配置器allocator, 优化STL 中的Vector
- <三>使用类模板实现STL Vector