MySQL大数据统计和元数据统计是数据库管理和优化中的重要环节，通过这些统计信息，可以更好地了解数据库的运行状态、数据分布以及性能瓶颈，下面将详细介绍MySQL中的一些关键统计信息及其用途，并通过表格形式展示相关命令和结果。
一、统计信息的收集方法
1. 自动收集统计信息
MySQL提供了自动收集统计信息的功能，可以通过设置合适的参数开启自动收集统计信息的功能，在MySQL 8.0版本之后，默认开启了InnoDB存储引擎的自动统计信息收集功能，MySQL会在后台自动收集表的统计信息，包括表的大小、行数、索引使用情况等。
2. 手动收集统计信息
除了自动收集外，还可以手动收集数据库统计信息，MySQL提供了多种方式来手动收集统计信息，如使用ANALYZE TABLE命令、使用OPTIMIZE TABLE命令和使用mysqldumpslow工具等。
ANALYZE TABLE命令：用于收集表的统计信息，该命令将分析表的索引和数据分布情况，并更新相关统计信息。
OPTIMIZE TABLE命令：用于优化表，并同时收集相关的统计信息，该命令会重建表，并重新计算统计信息。
mysqldumpslow工具：用于分析MySQL的慢查询日志，并生成慢查询报告，这些慢查询报告可以用于分析和优化查询性能，同时还包含了查询的统计信息。
二、统计信息的具体用途
| 序号 | 统计信息类型 | 描述 | SQL命令示例 |
| 1 | 没有使用索引的表 | 列出所有未使用索引的表 | SELECT t.TABLE_SCHEMA,t.TABLE_NAME FROM information_schema.TABLES AS t LEFT JOIN (SELECT DISTINCT table_schema, table_name FROM information_schema.KEY_COLUMN_USAGE) AS kt ON kt.table_schema = t.table_schema AND kt.table_name = t.table_name WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'test') AND kt.table_name IS NULL; |
| 2 | 查看存储过程和函数 | 列出所有存储过程和函数 | SELECT routine_name, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.routines; |
| 3 | 查看使用MyISAM的表 | 列出所有使用MyISAM存储引擎的表 | SELECT table_schema, table_name FROM information_schema.TABLES WHERE ENGINE = 'MyISAM' AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql'); |
| 4 | 查看没有使用主键的表 | 列出所有没有使用主键的表 | SELECT t.table_schema, t.table_name, t.ENGINE, IF(ISNULL(c.constraint_name), 'NOPK', '') AS nopk, IF(s.index_type = 'FULLTEXT', 'FULLTEXT', '') AS ftidx, IF(s.index_type = 'SPATIAL', 'SPATIAL', '') AS gisidx FROM information_schema.TABLES AS t LEFT JOIN information_schema.key_column_usage AS c ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name AND c.constraint_name = 'PRIMARY') LEFT JOIN information_schema.statistics AS s ON (t.table_schema = s.table_schema AND t.table_name = s.table_name AND s.index_type IN ('FULLTEXT', 'SPATIAL')) WHERE t.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') AND t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL ORDER BY t.table_schema, t.table_name; |
| 5 | 行数据前10统计的表 | 列出行数据最多的前10个表 | SELECT table_schema, table_name, table_rows, data_length, index_length, CONCAT(ROUND((data_length + index_length) / (1024 * 1024), 2), 'M') AS 'Total', CONCAT(ROUND(DATA_FREE / (1024 * 1024), 2), 'M') AS free FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'test') ORDER BY table_rows DESC LIMIT 10; |
| 6 | 查看主外键约束 | 列出所有的主外键约束 | SELECT C.TABLE_SCHEMA, C.REFERENCED_TABLE_NAME, C.REFERENCED_COLUMN_NAME, C.TABLE_NAME, C.COLUMN_NAME, C.CONSTRAINT_NAME, T.TABLE_COMMENT, R.UPDATE_RULE, R.DELETE_RULE FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = C.TABLE_NAME JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME WHERE C.REFERENCED_TABLE_NAME IS NOT NULL; |
| 7 | 查看触发器 | 列出所有的触发器 | SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT FROM information_schema.TRIGGERS; |
| 8 | 查看视图表 | 列出所有的视图表 | SELECT table_schema, table_name, definer, VIEW_DEFINITION, COLLATION_CONNECTION FROM information_schema.views WHERE table_schema NOT IN ('sys') GROUP BY table_name, table_schema, VIEW_DEFINITION; |
三、FAQs
Q1: 如何快速统计表中的数据量？
A1: 如果需要快速统计表中的数据量，可以使用以下SQL语句：
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
这种方法的响应速度通常非常快，但需要注意，TABLE_ROWS是一个估算值，可能与实际值有偏差。
Q2: 如何手动更新表的统计信息？
A2: 可以使用ANALYZE TABLE命令手动更新表的统计信息，
ANALYZE TABLE your_table_name;
这个命令会分析表的索引和数据分布情况，并更新相关统计信息，建议在业务低峰时执行此命令。
小编有话说
通过合理利用MySQL提供的统计信息，开发者可以更好地了解数据库的运行状态和数据分布情况，从而做出更优化的决策，无论是自动收集还是手动收集统计信息，都有其适用的场景和方法，希望本文能为你提供有价值的参考，让你在数据库管理和优化的道路上更加得心应手。
原创文章，作者：未希，如若转载，请注明出处：https://www.kdun.com/ask/1463431.html
本网站发布或转载的文章及图片均来自网络，其原创性以及文中表达的观点和判断不代表本网站。如有问题，请联系客服处理。
发表回复