MySQL 5.7 ANALYZE TABLE分析索引的统计信息

╰半夏微凉° 2024-04-18 00:10 66阅读 0赞

在生产环境中，索引的更新操作可能非常频繁。如果再每次索引发生更新操作时就对其进行Cardinality的统计，那么将会对数据库带来很大的负担。另外需要考虑的是，如果一张表的数据量非常大，比如一张表有50GB的数据，  
那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境的应用中也是不能接受的。因此，数据库对于Cardinality的统计都是通过采样(sample)的方法来完成的。  
在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时都去更新Cardinality的信息，  
这会增加数据库系统的负荷，同事对大表进行统计时，时间上也不允许。  
因此InnoDB存储引擎对于更新Cardinality信息的策略为：  
表中1/16的数据已发生变化  
stat\_modified\_counter > 2 000 000 000  
  
ANALYZE TABLE语句分析并存储表中索引的分布情况。对于InnoDB、MyISAM存储引擎，在分析中，表会被加只读锁。这个语句适用于InnoDB、NDB和MyISAM表。对于MyISAM表，这个语句等同于myisamchk --analyze。这个语句对  
 视图无效。  
  
 在执行非constant类型的表连接时，MySQL使用存储的索引分布信息来决定表的关联顺序。另外，索引的分布情况也可以用来决定在查询中使用哪个表的特定索引。  
  
 执行这个语句需要对指定表的SELECT和INSERT权限。  
  
 对于分区表，需要执行ALTER TABLE ... ANALYZE PARTITION语句。  
  
 在MySQL 5.7.1，在执行ANALYZE TABLE语句之前，需要将gtid\_next参数设为AUTOMATIC。这个限制在MySQL 5.7.2版本以后被修复。  
  
 可以使用SHOW INDEX语句来显示索引的分布情况。  
  
 默认，MySQL服务会将ANALYZE TABLE语句写到二进制日志中，并传输到复制环境中的slave节点。想关闭这个日志记录，可以在语句中加上NO\_WRITE\_TO\_BINLOG或LOCAL。  
  
 \--分析非分区表的索引分布情况  
  
 mysql> show index from dept;  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)  
  
 mysql> show keys from dept;  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)  
  
 mysql> analyze table  dept;  
 \+-----------+---------+----------+----------+  
 | Table     | Op      | Msg\_type | Msg\_text |  
 \+-----------+---------+----------+----------+  
 | fire.dept | analyze | status   | OK       |  
 \+-----------+---------+----------+----------+  
 1 row in set (0.03 sec)  
  
 mysql> show index from dept;  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)  
  
 \--分析分区表的索引分布情况  
  
 mysql> select table\_schema,table\_name,partition\_name from  information\_schema.partitions where table\_name = 'trb1';  
 \+--------------+------------+----------------+  
 | table\_schema | table\_name | partition\_name |  
 \+--------------+------------+----------------+  
 | fire         | trb1       | p0             |  
 | fire         | trb1       | p1             |  
 | fire         | trb1       | p2             |  
 | fire         | trb1       | p3             |  
 \+--------------+------------+----------------+  
 4 rows in set (0.00 sec)  
  
 mysql> show index from trb1;  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | trb1  |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)  
  
 mysql> alter table trb1 analyze partition p0;  
 \+-----------+---------+----------+----------+  
 | Table     | Op      | Msg\_type | Msg\_text |  
 \+-----------+---------+----------+----------+  
 | fire.trb1 | analyze | status   | OK       |  
 \+-----------+---------+----------+----------+  
 1 row in set (0.03 sec)  
  
 mysql> alter table trb1 analyze partition p1;  
 \+-----------+---------+----------+----------+  
 | Table     | Op      | Msg\_type | Msg\_text |  
 \+-----------+---------+----------+----------+  
 | fire.trb1 | analyze | status   | OK       |  
 \+-----------+---------+----------+----------+  
 1 row in set (0.00 sec)  
  
 mysql> alter table trb1 analyze partition p2;  
 \+-----------+---------+----------+----------+  
 | Table     | Op      | Msg\_type | Msg\_text |  
 \+-----------+---------+----------+----------+  
 | fire.trb1 | analyze | status   | OK       |  
 \+-----------+---------+----------+----------+  
 1 row in set (0.01 sec)  
  
 mysql> alter table trb1 analyze partition p3;  
 \+-----------+---------+----------+----------+  
 | Table     | Op      | Msg\_type | Msg\_text |  
 \+-----------+---------+----------+----------+  
 | fire.trb1 | analyze | status   | OK       |  
 \+-----------+---------+----------+----------+  
 1 row in set (0.00 sec)  
  
 mysql> show index from trb1;  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | trb1  |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)

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

转载于:http://blog.itpub.net/26506993/viewspace-2115026/

发表评论取消回复

表情：

评论列表（有 0 条评论，66人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 5.7 ANALYZE TABLE分析索引的统计信息

生产环境中，索引的更新操作可能非常频繁。如果再每次索引发生更新操作时就对其进行Cardinality的统计，那么将会对数据库带来很大的负担。另外需要考虑的是，如果一张表的...

╰半夏微凉°/ 2024年04月18日 00:10/ 0 赞/ 67 阅读

相关 mysql统计信息_概述MySQL统计信息

MySQL执行SQL会经过SQL解析和查询优化的过程，解析器将SQL分解成数据结构并传递到后续步骤，查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如

深碍√TFBOYSˉ_/ 2023年01月11日 03:54/ 0 赞/ 160 阅读

相关 MySQL之ANALYZE TABLE

> 关键词：索引分布 cardinality -------------------- 语法：ANALYZE TABLE 表名; 作用：执行ANALYZE T

红太狼/ 2022年12月19日 00:53/ 0 赞/ 392 阅读

相关 mysql 收集表的统计信息

mysql 收集表的统计信息比较个性查询 information\_schema.tables select from information_

清疚/ 2022年12月09日 02:24/ 0 赞/ 172 阅读

相关 mysql 收集表的统计信息

mysql 收集表的统计信息比较个性查询 information\_schema.tables select from information_

不念不忘少年蓝@/ 2022年11月27日 00:42/ 0 赞/ 203 阅读

相关 mysql analyze_技术分享 | MySQL EXPLAIN ANALYZE

作者：Norvald H. Ryeng 译：徐轶韬本文转载自公众号：MySQL解决方案工程师 ![b901c9f3b9c7773ddcd44132142c7252.pn

ゝ一世哀愁。/ 2022年10月25日 00:56/ 0 赞/ 188 阅读

相关 oracle收集统计信息之analyze

oracle收集统计信息之analyze 1.analyze 收集表，索引的统计信息，现在oracle不推荐用analyze收集统计信息收集表的统计信息Ana

浅浅的花香味﹌/ 2022年04月10日 08:55/ 0 赞/ 717 阅读

相关 MySQL ANALYZE Optimize Check Table使用详解

[MYSQL 分析表、检查表和优化表][MYSQL] 1. 对表进行优化 ( 优化表主要作用是消除删除或者更新造成的空间浪费) 2. 对表进行分析（分析关键字的分布,

你的名字/ 2022年04月04日 14:12/ 1 赞/ 407 阅读

相关 SQL优化之统计信息和索引

大部分情况下的sql优化的场景，都是发生在我们想让我们的sql语句运行的更快一点，这个时候我们往往会采取一些“措施”调整sql的执行路径以便它们能更快的运行结束。刚入dba

ゝ一世哀愁。/ 2022年04月04日 11:44/ 0 赞/ 220 阅读

相关使用analyze命令收集统计信息

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][] 1、以计算[模式][Link 1][收集表][Link

左手的ㄟ右手/ 2021年09月20日 04:48/ 0 赞/ 321 阅读