MySQL 数据库表信息分析

原创已于 2024-10-27 18:12:53 修改 · 1.6k 阅读

27 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

于 2024-10-27 17:04:46 首次发布

数据库专栏收录该内容

10 篇文章

订阅专栏

查看有多少业务表

select count(*) from information_schema.tables where table_schema = ‘dk_shard’

查看有多少逻辑表

select distinct substring_index(table_name, ‘__’, 1) as logical_table_count
from information_schema.tables
where table_schema = ‘dk_shard’

查看表的统计信息

select * from information_schema.tables where table_schema = ‘dk_shard’

TABLE_CATALOG: 表所在的目录的名称。在大多数情况下，这个值是def。

TABLE_SCHEMA: 表所在的数据库的名称。在这个查询中，这个值将始终是dk_shard。

TABLE_NAME: 表的名称。这是dk_shard数据库中的每个表的名称。

TABLE_TYPE: 表的类型。这通常是普通表BASE TABLE，或者是视图VIEW。

ENGINE: 表使用的存储引擎，例如InnoDB、MyISAM、MEMORY等。

VERSION: 表的版本号，通常用于复制或恢复时。

ROW_FORMAT: 表的行格式，例如Dynamic、Fixed、Compressed等。

TABLE_ROWS: 表中的行数。这是一个估计值，并不总是准确的。

AVG_ROW_LENGTH: 表的平均行长度。

DATA_LENGTH: 表的数据长度（以字节为单位）。

MAX_DATA_LENGTH: 表可以容纳的最大数据长度。

INDEX_LENGTH: 表的索引长度（以字节为单位）。

DATA_FREE: 对于使用可变大小行格式的表，这是分配但未使用的空间。

AUTO_INCREMENT: 下一个AUTO_INCREMENT值。

CREATE_TIME: 表的创建时间。

UPDATE_TIME: 表最后更新时间。

CHECK_TIME: 表最后一次检查的时间。

TABLE_COLLATION: 表的字符集和校对规则。

CHECKSUM: 表的校验和。

CREATE_OPTIONS: 创建表时指定的其他选项。

TABLE_COMMENT: 表的注释。

表的校验和

校验和作用

表的校验和（Checksum）是数据库管理系统（DBMS）用于验证数据完整性的一个机制。

（1）检测数据损坏：校验和可以用来检测表数据是否因为硬件故障、软件错误或意外的系统行为而损坏。如果表的数据在写入磁盘后与原始数据不匹配，校验和将反映出这种差异。

（2）数据传输完整性：在数据从一台服务器传输到另一台服务器时，校验和可以确保数据在传输过程中没有被篡改或损坏。

（3）备份和恢复验证：在备份和恢复过程中，校验和可以帮助验证备份数据的完整性。

（4）比较数据一致性：在数据库复制或镜像环境中，可以通过比较主数据库和从数据库中表的校验和来验证数据是否一致。

（5）诊断和调试：当数据库出现问题时，校验和不匹配可以作为一个诊断工具，帮助数据库管理员定位问题所在。

（6）定期维护：数据库管理员可能会定期计算表的校验和作为维护任务的一部分，以确保数据的长期完整性。

校验和缺点

（1）性能开销：计算校验和可能会增加写入操作的开销，因为每次数据更改都需要更新校验和。

（2）存储空间：校验和需要占用一定的存储空间。

校验和工作原理

（1）计算校验和

数据块读取：DBMS 读取表中的数据块或页（取决于数据库的具体实现）。

哈希算法：对每个数据块应用哈希算法（如 MD5、SHA-1、SHA-256 等）来生成一个唯一的哈希值。哈希值是一个固定长度的字符串，代表了数据块的内容。

哈希值汇总：将所有数据块的哈希值汇总（例如，通过再次应用哈希算法或简单的求和）以生成一个最终的校验和值。

（2）存储校验和

计算出的校验和值被存储在数据库的系统表中，或者作为元数据与表相关联。

（3）校验过程

重新计算校验和：与初始计算过程相同，DBMS 重新读取表中的每个数据块并计算哈希值，然后汇总这些哈希值。

比较校验和：将重新计算出的校验和与之前存储的校验和值进行比较。

结果判断：

- 如果两个校验和值相同，则表的数据完整性没有问题。

- 如果校验和值不同，则表明表的数据可能已经损坏或被篡改。

具体示例

以 MySQL 的 MyISAM 存储引擎为例：

启用校验和：对于 MyISAM 表，ALTER TABLE your_table_name CHECKSUM=1;

计算校验和：CHECKSUM TABLE your_table_name;

查看校验和：校验和值存储在 `mysql` 数据库的 `tables` 表中，可以通过查询这个表来查看：SELECT checksum FROM mysql.tables WHERE table_name='your_table_name';

注意事项

校验和只能检测到数据块的更改，并不能防止数据损坏。

校验和的计算和验证可能会对数据库性能产生影响，尤其是在大型表上。

在某些数据库系统中，校验和可能不是实时计算的，而是在特定操作（如表关闭时）或在手动请求时才进行。

表的字符集

`utf8mb4和utf8`

utf8mb4_general_ci 和 utf8_general_ci 是 MySQL 数据库中用于排序和比较字符串的两种不同的字符集和校对规则。下面是它们之间的主要区别：

字符集差异

utf8mb4：
- 是 UTF-8 的一个扩展版本，能够存储任何有效的 UTF-8 字符。
- 它使用 1 到 4 个字节来表示一个字符。
- 支持 emoji 表情符号以及一些其他不常用的汉字和符号，这些在 utf8 字符集中是无法表示的。
utf8：
- 是 UTF-8 的一个早期版本，它只能使用 1 到 3 个字节来表示一个字符。
- 不能存储某些特殊的 Unicode 字符，如 emoji 表情符号。

校对规则差异

_general_ci：
- “ci” 代表 “case-insensitive”，即不区分大小写。
- 这种校对规则适用于大多数欧洲语言，它使用一种简单且快速的比较方法，但不完全遵循语言特定的排序规则。
虽然两个字符集都使用 _general_ci 校对规则，但是由于 utf8mb4 能够表示更多的字符，它的实现可能比 utf8 的 _general_ci 校对规则更加复杂，以处理更多的字符。

使用建议

由于 utf8mb4 支持更广泛的字符集，并且与官方的 UTF-8 标准完全兼容，因此通常建议使用 utf8mb4 而不是 utf8。
对于需要存储特殊字符（如 emoji）或者想要确保数据库能够处理任何 Unicode 字符的应用，utf8mb4 是更好的选择。
如果确定你的数据不会包含 utf8 无法表示的字符，并且性能是首要考虑的因素，那么 utf8 仍然可以使用，但这种情况越来越少见。

注意事项

当从 utf8 迁移到 utf8mb4 时，需要确保所有的数据库、表、连接和应用程序都使用新的字符集，以避免任何兼容性问题。
使用 utf8mb4 可能会增加一些存储空间的需求，因为某些字符需要更多的字节来存储。
在一些旧的 MySQL 版本中，utf8mb4 的性能可能不如 utf8，但随着数据库的更新和优化，这种差异已经大大减少。

表的行格式

表的行格式（ROW_FORMAT）定义了数据在磁盘上的物理存储方式。不同的行格式可能会影响性能、存储空间使用、以及某些操作（如压缩、备份、恢复）的效率。以下是几种常见的行格式及其特点：

1. Dynamic

动态行格式是 InnoDB 存储引擎的默认格式。
它可以存储变长列（例如 VARCHAR、VARBINARY、BLOB 和 TEXT）。
对于这些变长列，如果一行的数据超过了页的大小（通常是 16KB），则会使用额外的页来存储超出的数据。
这种格式可以更有效地使用存储空间，因为它只为实际存储的数据分配空间。

2. Fixed

固定行格式通常不是 InnoDB 存储引擎的默认选项。
在 Fixed 格式中，每行数据都占用相同大小的空间，无论实际数据大小如何。
这种格式通常用于 MyISAM 存储引擎，不适用于 InnoDB。
它可能会导致存储空间的浪费，尤其是对于含有大量变长列的表。

3. Compressed

压缩行格式用于减少磁盘空间的使用，通过压缩表中的数据。
它通常用于读多写少的大型表，因为压缩和解压数据会带来额外的 CPU 开销。
压缩格式可以显著减少存储需求，但可能会增加 CPU 的使用。

4. Redundant

冗余行格式是 InnoDB 存储引擎早期版本使用的格式。
它与 Dynamic 格式类似，但是存储结构不同，可能导致更多的存储空间浪费。
现在一般不推荐使用这种格式。

5. Compact

紧凑行格式是 InnoDB 在较新版本中的另一种格式。
它类似于 Dynamic 格式，但有一些细微的差别，例如在存储 NULL 值的方式上。
Compact 格式旨在减少存储空间的使用，同时保持良好的性能。

6. Barracuda

Barracuda 是一个行格式族的名称，它包括两种格式：Dynamic 和 Compressed。
它支持在 InnoDB 表中使用大 BLOB 和 TEXT 列的页外存储。

如何选择行格式？

选择哪种行格式取决于具体需求，以下是一些考虑因素：

存储空间：如果需要减少存储空间的使用，可以考虑 Compressed 或 Compact 格式。
性能：如果读写性能是主要关注点，Dynamic 或 Compact 可能是更好的选择。
兼容性：某些行格式可能不兼容旧版本的 MySQL 或 MariaDB。

在大多数情况下，使用默认的 Dynamic 行格式就足够了。如果有特殊需求，例如需要减少存储空间或者优化特定类型的查询性能，可能需要考虑使用其他行格式。

注意事项

在更改现有表的行格式之前，应该进行充分的测试，因为这一操作可能会影响性能和存储需求。
并非所有的 MySQL 版本都支持上述所有行格式。例如，早期的 MySQL 版本可能不支持 Compressed 或 Barracuda 格式。
在创建表时，可以通过 ROW_FORMAT 选项指定行格式，例如：CREATE TABLE my_table (...) ROW_FORMAT=DYNAMIC。

表的平均行长度

平均行长度计算

表的平均行长度（Average Row Length）指的是在数据库表中，每行数据平均占用的字节数。这个值可以帮助我们了解表的数据存储效率以及可能的空间优化机会。平均行长度可以通过以下方式计算：

总数据页数：表占用的所有数据页的总数。
总行数：表中的总行数。
平均行长度 = （总数据页数 * 每页的字节数）/ 总行数。

在 MySQL 中，可以通过 `INFORMATION_SCHEMA.TABLES` 视图或 `SHOW TABLE STATUS` 命令来查看表的平均行长度。例如：

SHOW TABLE STATUS LIKE 'your_table_name';

或者

SELECT AVG_ROW_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table_name';

这里的 `AVG_ROW_LENGTH` 就是平均行长度。

注意事项

平均行长度是一个动态变化的值，它会随着数据的插入、更新和删除而改变。
如果表中有大量的变长列（如 VARCHAR, TEXT, BLOB 等），平均行长度可能会因为存储的数据量不同而有较大的波动。
平均行长度仅表示数据行的平均大小，不包括索引等其他存储结构。

在实际应用中，了解平均行长度可以帮助进行以下优化：

如果平均行长度远小于页大小（通常是 16KB），可能意味着存储空间的使用效率不高，可以考虑重新设计表结构或者使用压缩行格式来优化。
如果平均行长度接近或超过页大小，可能需要检查是否有大量的大字段（如 TEXT, BLOB）并考虑是否可以优化数据存储方式，例如使用外部存储或分表策略。

Data Free

在 InnoDB 存储引擎中，`data_free` 是一个重要的状态指标，它表示表空间中尚未使用的空间大小（以字节为单位）。

较大值原因

表扩展：InnoDB 表空间可能会预分配一段空间用于未来的扩展。如果表预计会频繁插入数据，InnoDB 可能会提前预留空间以便快速写入。
删除操作：如果表中的大量数据被删除，InnoDB 可能不会立即回收这些空间。这些空间会变成 `data_free`，并且可以被后续的插入操作重用。
碎片整理：InnoDB 不像 MyISAM 那样自动进行碎片整理。删除操作可能会导致表空间出现碎片，`data_free` 值增加。
自动扩展的表空间：如果表空间设置为自动扩展（`autoextend`），当现有空间不足时，它会自动增加，而增加的空间可能不会立即被使用。
未压缩的 BLOB/CLOB 字段：如果表中包含大量的 BLOB 或 CLOB 字段，并且这些字段的数据被删除，那么这些字段原来占用的空间可能不会被立即回收。

处理较大值

优化表：使用 `OPTIMIZE TABLE table_name` 命令可以重新组织表并释放未使用的空间，减少 `data_free` 的值。但请注意，这个操作可能会锁定表，影响数据库性能。
重建表：如果你使用的是 MySQL 5.6 或更高版本，可以通过 `CREATE TABLE ... LIKE` 和 `INSERT INTO ... SELECT` 的方式重建表，这通常比 `OPTIMIZE TABLE` 更高效。
监控表的增长：监控表的插入和删除操作，了解表空间的使用情况，并根据需要调整策略。
调整自动扩展参数：如果表空间自动扩展，可以调整 `innodb_autoextend_increment` 和 `innodb_autoextend_max_size` 参数来控制扩展的行为。

需要注意的是，`data_free` 较大并不总是意味着有问题。如果数据库和表正在按预期运行，且性能没有受到影响，那么这可能只是 InnoDB 正常管理表空间的一部分。只有当 `data_free` 过大导致磁盘空间不足或者性能问题时，才需要考虑采取措施。

查看表大小

查看数据库表的大小

select table_name AS TableName,

round(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS SizeTable_GB

from information_schema.TABLES

where table_schema = 'dk_shard'

order by SizeTable_GB desc

数据库表的大小作用

性能监控：通过监控表的大小，可以了解数据库的增长趋势，预测未来的存储需求，及时调整数据库的存储策略。

性能优化：如果某个表的大小异常增长，可能会影响查询性能。通过监控表的大小，可以及时发现并优化性能瓶颈。

资源规划：表的大小可以帮助数据库管理员评估数据库的存储资源使用情况，合理规划存储空间和备份策略。

数据维护：对于一些大表，可能需要定期进行数据归档或清理，以保持数据库的高效运行。查看表大小有助于决定哪些表需要维护。

故障排查：在数据库出现性能问题时，查看表的大小可以帮助确定是否由于表数据量过大导致的性能问题。

应用优化：了解表的大小有助于开发人员优化应用逻辑，例如，合理设计索引、选择合适的数据类型等，以提高应用的整体性能。

库表信息分析总结
1. 表结构分析
1.1 检查数据类型和字段长度
目的：确保数据类型和字段长度符合实际数据需求，避免数据存储浪费或不足。
命令：`DESCRIBE table_name;`
问题：过大的字段长度或错误的数据类型可能导致性能下降。
1.2 检查索引使用
目的：确保索引被合理使用，提高查询效率。
命令：`SHOW INDEX FROM table_name;`
问题：缺少索引可能导致查询缓慢，过多或冗余的索引则可能影响写入性能。
1.3 检查主键和外键
目的：保证数据的一致性和完整性。
命令：`SHOW CREATE TABLE table_name;`
问题：缺少主键或外键可能导致数据完整性问题。

2. 性能分析
2.1 查询优化
目的：检查查询语句是否高效。
命令：`EXPLAIN SELECT * FROM table_name WHERE condition;`
问题：全表扫描、不使用索引的查询会导致性能问题。
2.2 索引效率
目的：检查索引的选择性和使用频率。
命令：`SHOW INDEX STATISTICS;`
问题：低选择性的索引可能不会带来性能提升。

3. 数据完整性分析
3.1 检查数据规范性
目的：确保数据符合规范化要求。
问题：不规范的数据可能导致更新异常、插入异常和删除异常。
3.2 检查数据一致性
目的：确保数据库中的数据逻辑上是一致的。
问题：不一致的数据可能导致错误的业务逻辑。

4. 数据冗余分析
4.1 检查重复数据
目的：查找重复的记录，这些记录可能是不必要的数据冗余。
命令：`SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;`
问题：重复数据会浪费存储空间，并可能导致数据不一致。

5. 空间和容量分析
5.1 检查数据表大小
目的：监控表的增长情况，避免超出存储限制。
命令：`SHOW TABLE STATUS LIKE 'table_name';`
问题：过大的表可能导致性能问题，需要考虑分表或归档旧数据。

6. 备份和恢复策略
6.1 检查备份频率和完整性
目的：确保数据安全，能够从灾难中恢复。
问题：不定期或不完整的备份可能导致数据丢失。

实施步骤
1. 收集信息：使用上述命令收集数据库表的相关信息。
2. 分析数据：根据收集的信息，分析潜在的问题点。
3. 制定计划：根据分析结果，制定优化和修复计划。
4. 执行优化：实施优化措施，如添加索引、修改字段类型、清理数据等。
5. 监控和调整：定期监控数据库性能，并根据实际情况调整优化策略。