Mysql中INFORMATION_SCHEMA虚拟库使用

虚拟库字段讲解

#查看INFORMATION_SCHEMA的表信息
DESC information_schema.tables;
重要列：
TABLE_SCHEMA #表所在的库
TABLE_NAME #表名
ENGINE #表的存储引擎
TABLE_ROWS #表的行数
DATA_LENGTH #表数据行占用的字节数
AVG_ROW_LENGTH #平均行长度
INDEX_LENGTH #索引的长度

案例

例1：查询mysql库中有哪些表
方法一：从硬盘上查找
show tables from mysql;

方法二：从内存中查找
select table_name from information_schema.tables where table_schema='mysql';

例2：统计mysql库的表数量
select count(table_name) from information_schema.tables where table_schema='mysql';

例3：统计当前数据库服务器每个库的表数量
select 
    table_schema as 库名,
    count(table_name) as 表数量
from 
    information_schema.tables 
group by 
    table_schema;

例4：统计当前数据库服务器库的数量
select 
    count(distinct table_schema) as 库数量
from 
    information_schema.tables;

注意事项：
1.在企业应用中，应排除系统库，需要在where条件中增加如下下配置
where table_schema not in ('mysql','sys','information_schema','performance_schema')
2.要解决交互问题，能直接通过shell命令来查询出对应的结果
mysql -e "select count(table_name) from information_schema.tables where table_schema='mysql';"

例5：统计world库每张表的行数
方法一： 
select count(*) from world.city;
select count(1) from world.city;

方法二：
select table_name,table_rows from information_schema.tables where table_schema='world';


例6：统计world库每张表的大小
表=索引+数据
注释：
AVG_ROW_LENGTH  表的平均行长度，单位是字节
TABLE_ROWS      表的行数
INDEX_LENGTH    索引的长度，单位是字节

AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH 

select 
	table_name as 表名,
	FORMAT((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024,0) as '大小(KB)'
from 
	information_schema.tables 
where 
	table_schema='world';

例7：统计每个业务库的大小

select 
	table_schema as 库名,
	FORMAT(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024,0) as '大小(KB)'
from 
	information_schema.tables
where 
	table_schema not in ('sys','information_schema','performance_schema')
group by 
	table_schema;

例8：统计当前数据库总数据

select 
	FORMAT(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024,0) as '大小(KB)'
from 
	information_schema.tables;

concat拼接函数

案例环境：单库单表备份
单库单表备份的命令如下：
mysqldump world city >/tmp/world_city.sql

如果库名和表名都非常多，那就导致这个操作重复次数多，还需要手动填写库名和表名

使用information_schema虚拟库配合concat函数，可以实现快速生成单表备份的指令

具体步骤：
1.先修改配置文件，让mysql支持可以输出结果到本地磁盘上
vim /etc/my.cnf
[mysqld]
...
secure-file-priv=/tmp

2.改完保存后，重启mysqld服务，让配置生效
systemctl restart mysqld 

3.登入mysql，来拼接备份指令，并导出到本地脚本文件中
select concat("mysqldump ",table_schema," ",table_name," > /tmp/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema') into outfile '/tmp/mysql_bak.sh';

4.运行该脚本，实现备份
sh /tmp/mysql_bak.sh