information_schema.tables
表的统计信息,初步判断表的数据行大小。
select table_schema,table_name,table_type,table_rows from information_schema.tables where table_schema='mysql'; TABLE_SCHEMA TABLE_NAME TABLE_TYPE TABLE_ROWS ------------ ------------------------- ---------- ------------ mysql columns_priv BASE TABLE 0 mysql component BASE TABLE 0 mysql db BASE TABLE 2 mysql default_roles BASE TABLE 0 mysql engine_cost BASE TABLE 2 mysql func BASE TABLE 0 mysql general_log BASE TABLE 2 mysql global_grants BASE TABLE 28 mysql gtid_executed BASE TABLE 0 mysql help_category BASE TABLE 44 mysql help_keyword BASE TABLE 738 mysql help_relation BASE TABLE 1837 mysql help_topic BASE TABLE 835 mysql innodb_index_stats BASE TABLE 507 mysql innodb_table_stats BASE TABLE 50 mysql password_history BASE TABLE 0 mysql plugin BASE TABLE 0 mysql procs_priv BASE TABLE 0 mysql proxies_priv BASE TABLE 1 mysql role_edges BASE TABLE 0 mysql server_cost BASE TABLE 6 mysql servers BASE TABLE 0 mysql slave_master_info BASE TABLE 0 mysql slave_relay_log_info BASE TABLE 0 mysql slave_worker_info BASE TABLE 0 mysql slow_log BASE TABLE 2 mysql tables_priv BASE TABLE 2 mysql time_zone BASE TABLE 0 mysql time_zone_leap_second BASE TABLE 0 mysql time_zone_name BASE TABLE 0 mysql time_zone_transition BASE TABLE 0 mysql time_zone_transition_type BASE TABLE 0 mysql user BASE TABLE 4
对于非事务性表,table_rows
这个值是精确的,对于事务性引擎,这个值通常是估算的。例如 MyISAM,存储精确的数目。对于其它存储引擎,比如 InnoDB ,本值是一个大约的数,与实际值相差可达 40 到 50% 。在这些情况下,使用 SELECT COUNT(*)
来获得准确的数目。对于在 information_schema
数据库中的表, Rows 值为 NULL 。
可以使用如下的 SQL 语句来批量统计数据库中的表的行数:
SELECT CONCAT( 'SELECT "', TABLE_NAME, '", COUNT(*) FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ' UNION ALL' ) EXEC_SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql';
把生成的 SQL 语句拷贝出来,并去掉最后的一个 “ UNION ALL
” 就可以执行了。产生的示例 SQL 如下所示:
SELECT "columns_priv", COUNT(*) FROM mysql.columns_priv UNION ALL SELECT "component", COUNT(*) FROM mysql.component UNION ALL SELECT "db", COUNT(*) FROM mysql.db UNION ALL SELECT "default_roles", COUNT(*) FROM mysql.default_roles UNION ALL SELECT "engine_cost", COUNT(*) FROM mysql.engine_cost UNION ALL SELECT "func", COUNT(*) FROM mysql.func UNION ALL SELECT "general_log", COUNT(*) FROM mysql.general_log UNION ALL SELECT "global_grants", COUNT(*) FROM mysql.global_grants UNION ALL SELECT "gtid_executed", COUNT(*) FROM mysql.gtid_executed UNION ALL SELECT "help_category", COUNT(*) FROM mysql.help_category UNION ALL SELECT "help_keyword", COUNT(*) FROM mysql.help_keyword UNION ALL SELECT "help_relation", COUNT(*) FROM mysql.help_relation UNION ALL SELECT "help_topic", COUNT(*) FROM mysql.help_topic UNION ALL SELECT "innodb_index_stats", COUNT(*) FROM mysql.innodb_index_stats UNION ALL SELECT "innodb_table_stats", COUNT(*) FROM mysql.innodb_table_stats UNION ALL SELECT "password_history", COUNT(*) FROM mysql.password_history UNION ALL SELECT "plugin", COUNT(*) FROM mysql.plugin UNION ALL SELECT "procs_priv", COUNT(*) FROM mysql.procs_priv UNION ALL SELECT "proxies_priv", COUNT(*) FROM mysql.proxies_priv UNION ALL SELECT "role_edges", COUNT(*) FROM mysql.role_edges UNION ALL SELECT "server_cost", COUNT(*) FROM mysql.server_cost UNION ALL SELECT "servers", COUNT(*) FROM mysql.servers UNION ALL SELECT "slave_master_info", COUNT(*) FROM mysql.slave_master_info UNION ALL SELECT "slave_relay_log_info", COUNT(*) FROM mysql.slave_relay_log_info UNION ALL SELECT "slave_worker_info", COUNT(*) FROM mysql.slave_worker_info UNION ALL SELECT "slow_log", COUNT(*) FROM mysql.slow_log UNION ALL SELECT "tables_priv", COUNT(*) FROM mysql.tables_priv UNION ALL SELECT "time_zone", COUNT(*) FROM mysql.time_zone UNION ALL SELECT "time_zone_leap_second", COUNT(*) FROM mysql.time_zone_leap_second UNION ALL SELECT "time_zone_name", COUNT(*) FROM mysql.time_zone_name UNION ALL SELECT "time_zone_transition", COUNT(*) FROM mysql.time_zone_transition UNION ALL SELECT "time_zone_transition_type", COUNT(*) FROM mysql.time_zone_transition_type UNION ALL SELECT "user", COUNT(*) FROM mysql.user columns_priv COUNT(*) ------------------------- ---------- columns_priv 0 component 0 db 2 default_roles 0 engine_cost 2 func 0 general_log 0 global_grants 28 gtid_executed 0 help_category 44 help_keyword 711 help_relation 1635 help_topic 682 innodb_index_stats 490 innodb_table_stats 56 password_history 0 plugin 0 procs_priv 0 proxies_priv 1 role_edges 0 server_cost 6 servers 0 slave_master_info 0 slave_relay_log_info 0 slave_worker_info 0 slow_log 0 tables_priv 2 time_zone 0 time_zone_leap_second 0 time_zone_name 0 time_zone_transition 0 time_zone_transition_type 0 user 4
USE information_schema; SELECT table_name,table_rows FROM TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY table_rows DESC; TABLE_NAME TABLE_ROWS ------------------------- ------------ help_relation 1837 help_topic 835 help_keyword 738 innodb_index_stats 507 innodb_table_stats 50 help_category 44 global_grants 28 server_cost 6 user 4 general_log 2 slow_log 2 db 2 tables_priv 2 engine_cost 2 proxies_priv 1 slave_master_info 0 time_zone_transition 0 password_history 0 slave_relay_log_info 0 time_zone_transition_type 0 columns_priv 0 gtid_executed 0 plugin 0 slave_worker_info 0 component 0 procs_priv 0 default_roles 0 role_edges 0 time_zone 0 time_zone_leap_second 0 func 0 servers 0 time_zone_name 0