GVKun编程网logo

MYSQL 的 information_schema 数据库中你可以得到的信息！！！（mysql显示数据库信息）

1

对于MYSQL的information_schema数据库中你可以得到的信息！！！感兴趣的读者，本文将会是一篇不错的选择，我们将详细介绍mysql显示数据库信息，并为您提供关于ApacheHiveMe

对于MYSQL 的 information_schema 数据库中你可以得到的信息！！！感兴趣的读者，本文将会是一篇不错的选择，我们将详细介绍mysql显示数据库信息，并为您提供关于Apache Hive Metastore - information_schema、information_schema 表的一些查询、information_schema.COLUMNS、information_schema.TABLE_STATISTICS的有用信息。

本文目录一览：

MYSQL 的 information_schema 数据库中你可以得到的信息！！！（mysql显示数据库信息）

MYSQL 的 information_schema 数据库中你可以得到的信息！！！（mysql显示数据库信息）

 

1、COLUMNS 记录了所有表字段的一些基本信息，例如权限信息等。

 

2:TABLES : 使用该表可以查询每一个表的详细信息，例如数据占用空间大小、索引大小以及表的更新时间以及表的行数等

 

3：视图 可以查看视图的所在的数据库，表以及该视图的定义

 

4：TABLE_PRIVILEGES 可以查看表权限

 

 5:STATISTICS 可以查看一些表的相信信息

 

6：PROCESSLIST  查看数据库的线程信息，有多少连接，连接访问的是那些数据库

 

7: PROFILING 一些性能指标，数据库查询调优涉及该表，该表目前不是很了解，有时间研究一下，

使用该表需要开启：set profiling=on;

 

 

Apache Hive Metastore - information_schema

Apache Hive Metastore - information_schema

如何解决Apache Hive Metastore - information_schema

我对 Hive 还很陌生。我想知道是否可以使用带有 Hive 元存储数据的 information_schema 数据库？例如，我创建 Hive 元存储并在执行“show database”时返回的数据库，这些也可以添加到 information_schema.schemata 表中吗？我发现 Hive metastore 信息保存在 Hive 数据库中，我可以将其更改为 information_schema 吗？

一些帖子，如下所示，暗示可以使用 information_schema，但是，我找不到关于如何实现这一点的任何好的资源。

https://issues.apache.org/jira/browse/HIVE-16941

*** 更新 *** 我们还没有决定使用哪个版本或供应商。我们正在考虑使用开源版本，但是，如果供应商提供此版本（而不是开源版本），我们会考虑使用供应商，因为这对我们来说是一个重要问题。

information_schema 表的一些查询

information_schema 表的一些查询

OSC 请你来轰趴啦！1028 苏州源创会，一起寻宝 AI 时代

找出 xnc 数据库中，列的名字是 origin 的地方。

SELECT
*
FROM
information_schema.`COLUMNS` c
WHERE
c.TABLE_SCHEMA = ''xnc''
AND c.COLUMN_NAME = ''origin''

找出 xnc 数据库中，引用 user 表的 id 作为外键的地方。

SELECT
*
FROM
information_schema.KEY_COLUMN_USAGE c
WHERE
c.REFERENCED_TABLE_SCHEMA = ''xnc''
AND c.REFERENCED_TABLE_NAME = ''user''
AND c.REFERENCED_COLUMN_NAME = ''id''

 

information_schema.COLUMNS

information_schema.COLUMNS

MySQL的information_schema库中有个COLUMNS表，里面记录了mysql所有库中所有表的字段信息，该表信息如下：

COLUMNS表的每一条记录都对应了数据库中某个表的某个字段，该表记录了如下信息：

TABLE_CATALOG

MySQL官方文档中说，这个字段值永远是def，但没写这个字段是干嘛用的。

网上有把这个叫表限定符的，有叫登记目录的。作用疑似是和其他种类的数据库做区分。

TABLE_SCHEMA

表格所属的库。

TABLE_NAME

表名

COLUMN_NAME

字段名

ORDINAL_POSITION

字段标识。

其实就是字段编号，从1开始往后排。

COLUMN_DEFAULT

字段默认值。

IS_NULLABLE

字段是否可以是NULL。

该列记录的值是YES或者NO。

DATA_TYPE

数据类型。

里面的值是字符串，比如varchar，float，int。

CHARACTER_MAXIMUM_LENGTH

字段的最大字符数。

假如字段设置为varchar(50)，那么这一列记录的值就是50。

该列只适用于二进制数据，字符，文本，图像数据。其他类型数据比如int，float，datetime等，在该列显示为NULL。

CHARACTER_OCTET_LENGTH

字段的最大字节数。

和最大字符数一样，只适用于二进制数据，字符，文本，图像数据，其他类型显示为NULL。

和最大字符数的数值有比例关系，和字符集有关。比如UTF8类型的表，最大字节数就是最大字符数的3倍。

NUMERIC_PRECISION

数字精度。

适用于各种数字类型比如int，float之类的。

如果字段设置为int(10)，那么在该列保存的数值是9，少一位，还没有研究原因。

如果字段设置为float(10,3)，那么在该列报错的数值是10。

非数字类型显示为在该列NULL。

NUMERIC_SCALE

小数位数。

和数字精度一样，适用于各种数字类型比如int，float之类。

如果字段设置为int(10)，那么在该列保存的数值是0，代表没有小数。

如果字段设置为float(10,3)，那么在该列报错的数值是3。

非数字类型显示为在该列NULL。

DATETIME_PRECISION

datetime类型和SQL-92interval类型数据库的子类型代码。

我本地datetime类型的字段在该列显示为0。

其他类型显示为NULL。

CHARACTER_SET_NAME

字段字符集名称。比如utf8。

COLLATION_NAME

字符集排序规则。

比如utf8_general_ci，是不区分大小写一种排序规则。utf8_general_cs，是区分大小写的排序规则。

COLUMN_TYPE

字段类型。比如float(9,3)，varchar(50)。

COLUMN_KEY

索引类型。

可包含的值有PRI，代表主键，UNI，代表唯一键，MUL，可重复。

EXTRA

其他信息。

比如主键的auto_increment。

PRIVILEGES

权限

多个权限用逗号隔开，比如 select,insert,update,references

COLUMN_COMMENT

字段注释

GENERATION_EXPRESSION

组合字段的公式。

information_schema.TABLE_STATISTICS

information_schema.TABLE_STATISTICS

version: 5.7.21 Percona Server

>CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 
>select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
 
>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       118 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)
 
>select * from t1 limit 1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
+----+------+
1 row in set (0.00 sec)
 
>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       119 |           17 |

### 本以为是读出来几行 ROWS_READ 就加几个，没成想不是这样的，请看下面的例子

>CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

>select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        87 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 order by a limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        91 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

 

### order by 没走索引，最后走的 filesort， 表里面有 4 条数据，最后 ROWS_READ 是加的 4。（应该是 order by 了几条，加的就是几个），可以对比一下下面的例子

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        95 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 where id >2 order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 where id >2 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

# 可以看到ROWS_READ 加的是2，因为已经通过索引把数据过滤剩两个了。
>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        97 |           16 |
+--------------+------------+-----------+--------------+

 

### 假如 where 没有用上索引，再 order by 的话还是 4 个

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        91 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>select * from t1 where a<10 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.00 sec)

>explain select * from t1 where a<10 order by a limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        95 |           16 |
+--------------+------------+-----------+--------------+

 

### 但是如果条件里面没有 order 并且没有 filesout，limit 几个就加几个

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        97 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

> select * from t1 limit 1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        98 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

 

### 但是 where 条件里面有主键和非索引，order by 非索引的话 也是 limit 几就是几

# 先插入一行数据
>insert into t1 values(5,3);
Query OK, 1 row affected (0.01 sec)

>select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    3 |
+----+------+
5 rows in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       104 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>explain select * from t1 where id >2 and a=3 order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    20.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 where id >2 and a=3 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       105 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

 

### 下面这个我没想明白是为什么

a>3 已经过滤剩两行了，为啥还是加 3 呢？？？

难道是有 filesort 的话就是扫描多少行 ROWS_READ 就加几吗？？？

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       115 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>explain select * from t1 where id >2 and a>3  order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

>select * from t1 where id >2 and a>3  order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  4 |    4 |
+----+------+
1 row in set (0.01 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       118 |           17 |
+--------------+------------+-----------+--------------+

 

我们今天的关于MYSQL 的 information_schema 数据库中你可以得到的信息！！！和mysql显示数据库信息的分享就到这里，谢谢您的阅读，如果想了解更多关于Apache Hive Metastore - information_schema、information_schema 表的一些查询、information_schema.COLUMNS、information_schema.TABLE_STATISTICS的相关信息，可以在本站进行搜索。

本文标签：

上一篇Mysql 提示 you need the SUPER privilege for this operation 的解决办法

下一篇mysql 开源 ~ canal+otter系列(1)（mysql开源工具）

相关文章