MySQL运维与监控之：`MySQL`的`I_S`（`Information Schema`）：其在数据库元数据查询中的应用。

MySQL运维与监控之：MySQL的I_S（Information Schema）：数据库元数据查询的利器

大家好，今天我们来聊聊MySQL运维和监控中一个非常重要的工具——Information Schema，简称I_S。I_S是MySQL数据库中一个特殊的数据库，它存储了关于MySQL服务器的元数据，例如数据库、表、列、索引、权限等等的信息。我们可以通过标准的SQL语句查询I_S，从而获取这些元数据，进而实现数据库的管理、监控、诊断和优化等功能。

1. 什么是Information Schema？

简单来说，I_S就像MySQL的“字典”或“说明书”。它提供了一个接口，让我们能够以编程的方式访问数据库的内部信息。与直接读取MySQL的系统文件不同，I_S提供了一种更安全、更标准的方式来获取元数据。

2. I_S的核心表

I_S包含许多表，每个表都存储了特定类型的元数据。以下是一些最常用的表：

表名	描述
SCHEMATA	存储关于数据库的信息，例如数据库名、字符集等。
TABLES	存储关于表的信息，例如表名、数据库名、表类型、创建时间等。
COLUMNS	存储关于表中列的信息，例如列名、数据类型、是否允许为空、默认值等。
STATISTICS	存储关于表索引的信息，例如索引名、索引类型、索引列等。
TABLE_CONSTRAINTS	存储关于表约束的信息，例如主键、外键、唯一约束等。
KEY_COLUMN_USAGE	存储关于表外键的信息，包括外键约束的列和引用的列。
PROCESSLIST	存储当前MySQL服务器上正在运行的线程的信息，例如线程ID、用户、主机、状态、执行的SQL语句等。
GLOBAL_STATUS	存储MySQL服务器的全局状态变量，例如连接数、查询数、缓存命中率等。
GLOBAL_VARIABLES	存储MySQL服务器的全局变量，例如字符集、缓存大小等。
EVENTS	存储关于计划任务的信息，例如任务名、执行时间、状态等。
TRIGGERS	存储关于触发器的信息，例如触发器名、关联的表、触发事件等。
ROUTINES	存储关于存储过程和函数的信息，例如名称、参数、返回类型等。
USER_PRIVILEGES	存储用户权限信息，包括用户对哪些数据库、表拥有哪些权限。
SCHEMA_PRIVILEGES	存储用户对数据库的权限信息。
TABLE_PRIVILEGES	存储用户对表的权限信息。

3. 使用I_S进行数据库管理

I_S在数据库管理中有很多应用场景，例如：

• 查看数据库列表：

  SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

• 查看指定数据库中的表列表：

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

• 查看表的列信息：

  SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

• 查找没有主键的表：

  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME NOT IN (
      SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND TABLE_SCHEMA = 'your_database_name'
  );

• 查找包含特定列的表：

  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'your_database_name'
  AND COLUMN_NAME = 'your_column_name';

• 检查表的大小：

  SELECT
      TABLE_NAME,
      ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS "Size in MB"
  FROM
      information_schema.TABLES
  WHERE
      TABLE_SCHEMA = 'your_database_name'
  ORDER BY
      (DATA_LENGTH + INDEX_LENGTH) DESC;

4. 使用I_S进行数据库监控

I_S在数据库监控中也发挥着重要作用，例如：

• 监控当前连接数：

  SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected';

• 监控慢查询数：

  SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Slow_queries';

• 监控查询缓存命中率：

  SELECT
      (SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') /
      (SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_queries') * 100 AS 'Query Cache Hit Rate';

• 查找长时间运行的查询：

  SELECT
      ID,
      USER,
      HOST,
      DB,
      COMMAND,
      TIME,
      STATE,
      INFO
  FROM
      INFORMATION_SCHEMA.PROCESSLIST
  WHERE
      COMMAND != 'Sleep'
      AND TIME > 60; -- 超过60秒的查询

• 监控InnoDB缓冲池命中率：

  SELECT
      ( 1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests ) * 100 AS 'InnoDB Buffer Pool Hit Rate'
  FROM
      GLOBAL_STATUS
  WHERE
      VARIABLE_NAME LIKE 'Innodb_buffer_pool_read%'

5. 使用I_S进行数据库诊断和优化

I_S还可以帮助我们诊断数据库问题，并进行优化，例如：

• 查找重复索引：

  SELECT
      TABLE_NAME,
      INDEX_NAME,
      COUNT(*) AS duplicate_count
  FROM
      INFORMATION_SCHEMA.STATISTICS
  WHERE
      TABLE_SCHEMA = 'your_database_name'
  GROUP BY
      TABLE_NAME,
      INDEX_NAME
  HAVING
      duplicate_count > 1;

• 查找没有被使用的索引： (这个比较复杂，需要结合performance schema，这里只展示思路)

  虽然I_S本身不直接提供未使用索引的信息，但可以结合Performance Schema来分析。Performance Schema记录了索引的使用情况。 通常的做法是，启用Performance Schema的 index_statistics 收集索引使用数据，然后查询 performance_schema.table_io_waits_summary_by_index_usage 表，找出 count_star 接近于0的索引。 这个过程比较复杂，需要一定的配置和分析。

• 分析表结构，找出可以优化的列类型：

  例如，可以将VARCHAR列改为ENUM或SET类型，或者将INT改为TINYINT，以节省空间。可以通过查询INFORMATION_SCHEMA.COLUMNS表，分析列的实际存储值，判断是否可以进行优化。

• 识别潜在的锁冲突：

  通过监控INFORMATION_SCHEMA.PROCESSLIST，可以观察到哪些查询处于Waiting for table metadata lock状态，从而识别潜在的锁冲突。

6. I_S的性能考虑

虽然I_S非常有用，但在高并发环境下频繁查询I_S可能会对性能产生一定影响。因为I_S的数据是动态生成的，每次查询都需要访问MySQL服务器的内部数据结构。

因此，在使用I_S时需要注意以下几点：

• 尽量避免在高并发环境下频繁查询I_S。 可以考虑将I_S的数据缓存到应用程序中，或者使用专门的监控工具来收集数据。
• 只查询需要的列，避免查询不必要的列。
• 使用合适的WHERE条件，缩小查询范围。
• 考虑使用Performance Schema来替代I_S。 Performance Schema提供了更详细的性能数据，并且对性能的影响更小。但是，Performance Schema的配置和使用也更复杂。

7. I_S的权限控制

访问I_S需要SELECT权限。默认情况下，所有用户都可以访问I_S，但只能看到自己有权限访问的数据库和表的信息。

可以通过GRANT和REVOKE语句来控制用户对I_S的访问权限。例如，可以禁止用户访问INFORMATION_SCHEMA.PROCESSLIST表，以保护数据库的安全性。

REVOKE SELECT ON INFORMATION_SCHEMA.PROCESSLIST FROM 'your_user'@'your_host';

8. I_S的局限性

虽然I_S提供了丰富的元数据信息，但也存在一些局限性：

• 并非所有信息都实时更新。 例如，某些统计信息可能存在一定的延迟。
• 不同MySQL版本之间，I_S的结构可能会有所差异。 因此，在使用I_S时需要注意兼容性问题。
• 某些信息只能通过其他方式获取。 例如，查看binlog的位置信息，需要使用SHOW MASTER STATUS命令。

9. 结合实例的更复杂查询

下面展示几个更复杂一些的，结合多个表进行查询的例子：

• 查找所有外键约束的详细信息，包括外键名、外键所在的表和列，以及引用的表和列：

  SELECT
      tc.CONSTRAINT_NAME,
      tc.TABLE_NAME,
      kcu.COLUMN_NAME,
      rc.REFERENCED_TABLE_NAME,
      kcu.REFERENCED_COLUMN_NAME
  FROM
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
  JOIN
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
  ON
      tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
      AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  JOIN
      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
  ON
      tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
      AND tc.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
  WHERE
      tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND tc.TABLE_SCHEMA = 'your_database_name';

• 查找所有使用了ENUM或SET类型的列，并显示其允许的值：

  SELECT
      TABLE_NAME,
      COLUMN_NAME,
      COLUMN_TYPE
  FROM
      INFORMATION_SCHEMA.COLUMNS
  WHERE
      TABLE_SCHEMA = 'your_database_name'
      AND (DATA_TYPE = 'enum' OR DATA_TYPE = 'set');

• 找到所有具有相同列定义（数据类型、长度、是否允许为空）的列，这可能暗示着设计上的冗余：

  SELECT
      c1.TABLE_NAME AS Table1,
      c1.COLUMN_NAME AS Column1,
      c2.TABLE_NAME AS Table2,
      c2.COLUMN_NAME AS Column2
  FROM
      INFORMATION_SCHEMA.COLUMNS AS c1
  JOIN
      INFORMATION_SCHEMA.COLUMNS AS c2
  ON
      c1.DATA_TYPE = c2.DATA_TYPE
      AND c1.COLUMN_TYPE = c2.COLUMN_TYPE
      AND c1.IS_NULLABLE = c2.IS_NULLABLE
      AND c1.TABLE_SCHEMA = c2.TABLE_SCHEMA
      AND c1.TABLE_NAME != c2.TABLE_NAME -- 排除同一张表
  WHERE
      c1.TABLE_SCHEMA = 'your_database_name';

10. 使用编程语言访问I_S

除了直接在MySQL客户端中使用SQL语句查询I_S，还可以使用各种编程语言（例如Python、Java、PHP）来访问I_S。

下面是一个Python的例子：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="your_user",
  password="your_password",
  database="information_schema"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT SCHEMA_NAME FROM SCHEMATA")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

结语

I_S是MySQL运维和监控的利器，掌握I_S的使用方法，可以帮助我们更好地管理、监控、诊断和优化MySQL数据库。希望今天的分享对大家有所帮助。

I_S是元数据宝库，善用能解决诸多问题
I_S提供了强大的元数据查询能力，是MySQL运维和监控的基石。了解其表结构和使用方法，能有效提升数据库管理效率。

性能需权衡，合理使用是关键
虽然I_S非常有用，但过度使用会影响性能。需要根据实际情况，权衡性能和信息获取的需求，选择合适的查询方式。

持续学习，不断探索I_S的更多应用
I_S的功能远不止本文介绍的这些。通过不断学习和实践，可以发现更多I_S的应用场景，提升数据库管理水平。