^-^
MySQL元数据
  1. 元数据概述
    1. 什么是元数据
  2. 元数据查询
    1. 通过视图查询
    2. 通过show命令查询

元数据概述

什么是元数据

在数据库中存储数据的基本逻辑单元是表，我们通常可以往表中插入数据，修改数据，删除数据和查询数据。一张表中包含了数据字典，数据行记录，索引，数据库状态，权限，日志等信息。

相关概念

  • 数据行记录: 存储的真实数据
  • 索引: 可以理解为一本书的目录，用于加快查询的
  • 数据字典: 表中定义的字段信息
  • 数据库状态: 也叫元数据信息，它存储了当前数据库实例的一些状态信息，用于运行MySQL实例本身的配置信息。存储元数据信息的库都是MySQL内置的数据库，如:”information_schema，mysql，performance_schema，sys”
  • 权限: 存储MySQL用户的权限相关的表信息。对应存储权限的表基本上都在mysql数据库中，比如:”columns_priv”,”db”,”tables_priv”,”user”等等都是跟权限有关的表
  • 日志: 存储MySQL日志信息，对应的有专门的日志文件，比如有用于存储MySQL错误日志的文件，也有对应二进制日志的文件，后面在主从复制会提及这些内容。

除了数据行记录和索引外，其他的都可以理解为和元数据相关的信息，当我们使用SHOW DATABASES;语句查看现有的数据库信息时，会发现有information_schema这样的一个数据库， 每次数据库启动，会自动在内存中生成information_schema数据库，该数据库用于存储查询数据库的元数据信息的视图。

元数据查询视图

所谓的元数据查询视图，就可以理解为SELECT语句的执行方法，不保存数据本身。 在information_schema数据库的tables表中，保存了查询元数据表的视图表，其表结构如下所示：

image-20240416232155705

序号 字段信息 解释说明
01 TABLE_SCHEMA 表示数据表所属库的名称信息
02 TABLE_NAME 表示数据库中所有数据表名称
03 ENGINE 表示数据库服务中的引擎信息
04 TABLE_ROWS 表示数据库相应数据表的行数
05 AVG_ROW_LENGTH 表示数据表中每行的平均长度
06 INDEX_LENGTH 表示数据表中索引信息的长度
07 DATA_FREE 表示数据库服务碎片数量信息
08 CREATE_TIME 表示数据表创建的时间戳信息
09 UPDATE_TIME 表示数据表修改的时间戳信息
10 TABLE_COMMENT 表示数据表对应所有注释信息

元数据查询

通过视图查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 切换进入information_schema数据库中查看表信息，此时看到的所有表信息，其实都是视图信息
use information_schema;
show tables;

# 统计获取每个数据库中数据表的总数，以及表名称
select table_schema,count(*),group_concat(table_name) from information_schema.tables group by table_schema;

# 获取除了系统数据库之外的数据库中，数据表的总数，以及表名称
select table_schema,count(*),group_concat(table_name) from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') group by table_schema;

# 获取每个数据库数据占用磁盘空间
select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024 from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') group by table_schema;

# 获取具有碎片信息的表，碎片信息过多会导致索引信息失效，出现统计信息不真实的情况
select table_schema,table_name,data_free from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;

# 处理具有碎片信息的表，其实就是把表的存储引擎改为InnoDB，也可以对已经是innodb存储引擎的表做此操作
select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;

# 获取数据库中非innodb表信息
select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and engine!='innodb';


# 修改数据库中非innodb表信息替换成innodb,并将信息输出到文件中
select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and  engine!='innodb' into outfile '/tmp/alter.sql'; 

# 注意，如果遇到报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，做如下修改：
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
# 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务

通过show命令查询

使用show命令虽然可以快速得到相应的数据库元数据信息，但是查询功能过于单一，想查询全面信息，就需要执行多条语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# 常用SQL语句的show命令查看元数据信息
mysql> show databases;
-- 查询数据库服务中的所有数据库信息（数据库名称-元数据）

mysql> show tables;
mysql> show tables from mysql;
-- 查询数据库服务中的相应数据表信息（数据表名称-元数据）

mysql> show create database <库名>;
-- 查询数据库服务中的建库语句信息 （建库语句参数-元数据 建库语句就是DDL语句，定义建立数据库的属性信息）

mysql> show create table <表名>;
-- 查询数据库服务中的建表语句信息 （建表语句参数-元数据 建表语句就是DDL语句，定义建立数据表的属性信息）

mysql> desc <表名>;
mysql> show columns from <表名>;
-- 查询数据库服务中的数据表的结构（数据表的列定义信息-元数据）

mysql> show table status from <库名>;
-- 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据）
mysql> show table status from world like 'city' \G
*************************** 1. row ***************************
           Name: city                 -- 数据表名称信息
         Engine: InnoDB           -- 使用的数据库引擎信息
        Version: 10
     Row_format: Dynamic
           Rows: 4046                -- 数据表的行数信息
 Avg_row_length: 101         -- 平均行长度
    Data_length: 409600       
Max_data_length: 0
   Index_length: 114688      -- 索引长度信息
      Data_free: 0
 Auto_increment: 4080       -- 自增列的值计数
    Create_time: 2022-11-04 09:13:27   -- 数据表创建时间
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci      -- 校对规则信息
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-- 查看数据库服务中的具体数据库表的状态信息（属于单库或单表查询）

mysql> show index from world.city;
-- 查询数据库服务中的相应数据表的索引情况（了解即可）

mysql> show grants for root@'localhost';
-- 查询数据库服务中的用户权限属性配置信息

mysql> show [full] processlist;
-- 查询数据库服务的系统状态信息，表示当前数据库的所有连接情况

mysql> show variables; 
mysql> show variables like '%xx%';
-- 查询数据库服务的所有配置信息

mysql> show status;
mysql> show status like '%lock%'; 
-- 查询数据库服务的系统整体状态，表示当前数据库服务运行的即时状态情况

mysql> show binary logs;
-- 查询数据库服务的所有二进制日志信息（binlog日志）
mysql> show master status;
-- 查询数据库服务正在使用的二进制日志
mysql> show binlog events in 'binlog.000009';
-- 查询数据库服务具体二进制日志内容事件信息

mysql> show engine innodb status \G
-- 查询数据库服务存储引擎相关信息

mysql> show slave hosts;
-- 在数据库服务主库查看从库信息
mysql> show slave status;
-- 查询数据库服务主从状态信息
2024-03-16 该篇文章被 邓胖胖 打上标签: MySQL 归为分类: 学习笔记