information_schema.tables基础应用

information_schema

虚拟库

创建视图:
1
2
3
4
5
CREATE VIEW test AS SELECT world.city.`Name`,world.country.`Code`,world.city.Population 
FROM world.city
JOIN world.country
ON world.city.CountryCode = world.country.`Code`
WHERE world.city.Population<100;
调用视图:
1
SELECT * FROM test;

元数据?

—-> “基表”(无法直接查询和修改的)
—-> DDL 进行元数据修改
—-> show ,desc(show),information_schema(全局类的统计和查询)

information_schema

1
2
3
4
5
6
7
8
DESC information_schema.tables;
重要列:
TABLE_SCHEMA #表所在的库
TABLE_NAME #表名
ENGINE #表的存储引擎
TABLE_ROWS #表的行数
AVG_ROW_LENGTH #平均行长度
INDEX_LENGTH #索引的长度

要查询整个数据库下,所有库和对应的表名。
1
2
SELECT table_schema,table_name
FROM information_schema.tables;

查询world和school,下所有表
1
2
3
4
5
6
7
8
9
10
11
12
SELECT table_schema,GROUP_CONCAT(table_name) 
FROM information_schema.tables
WHERE table_schema='world' OR table_schema='school'
GROUP BY table_schema;
改写:
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_schema='world'
UNION ALL
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_schema='school';

统计一下每个库下的表的个数。
1
2
3
SELECT table_schema,COUNT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

查询整个数据库中所有的库对应的表名,每个库显示成一行
1
2
3
SELECT table_schema,GROUP_CONCAT(table_name) 
FROM information_schema.tables
GROUP BY table_schema;

统计每表数据用量

公式:
每张表的数据量=AVG_ROW_LENGTH(平均行长度)*TABLE_ROWS(行数)+INDEX_LENGTH(引索长度) SELECT

1
2
3
4
5
6
select TABLE_SCHEMA,table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS mb
from information_schema.tables
WHERE TABLE_SCHEMA NOT IN('sys','performance','information_schema')
ORDER BY mb DESC;

SELECT AVG_ROW_LENGTH,TABLE_ROWS,INDEX_LENGTH FROM information_schema.tables;

统计一下每个库的真实数据量
1
2
3
4
5
SELECT TABLE_SCHEMA,count(table_name),SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;

// 统计出每表数据用量,更具库名进行站队,计算每库下表的个数以及库下的每表数据量之和。

总数据量
1
2
3
(平均行长度所有列之和*平均行数所有列之和+引索长度所有列之和)
SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.tables;

CONCAT()拼接函数示例:
1
2
SELECT CONCAT(USER,"@",HOST)
FROM mysql.user;

生产需求:单库单表备份
1
2
3
mysqldump -uroot -p123  world city >/tmp/world_city.sql
--- 模仿以上命令,对整个数据库下的1000张表进行单独备份,
--- 排除sys(5.6没有),performance,information_schema

批量分表备份:

1:生成拼接语句

1
2
3
4
SELECT CONCAT("mysqldump -uroot -p123  ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")  
FROM information_schema.tables
WHERE table_schema NOT IN('sys','performance','information_schema') #排除库
INTO OUTFILE '/tmp/bak.sh'; #<导出脚本

2:解除mysql导出限制

1
2
vim /etc/my.cnf 
secure-file-priv=/tmp

3:重启数据库已生效配置
/etc/init.d/mysqld restart


批量执行语句

例子:模仿以下语句,批量实现world下所有表的操作语句生成
alter table world.city discard tablespace;

1
2
3
4
select concat("alter table ",table_schema,".",table_name,"discard tablespace;") 
from information_schema.tables
where table_schema='world'
into outfile '/tmp/discard.sql';