使用sys数据库分析 MySQL

使用sys数据库分析 MySQL的的运行情况

目录

开启performance_schema

找到配置文件

编辑配置文件

重启 MySQL 服务

验证是否启用

sys数据库概述

常用查询

查看每个数据库连接消耗资源

查看用户连接MySQL时消耗资源

查看当前MySQL总分配内存资源

查看MySQL中存在的冗余索引

查看MySQL中未使用到的索引

总结

---

MySQL的sys数据库中除了sys_config数据表外，基本上以视图的形式展示performance_schema中的其他数据。

因此，要想使用sys数据库分析MySQL，就需要先在my.cnf或者my.ini文件中开启performance_schema的配置。

开启performance_schema

以下是在配置文件中启用 performance_schema 的步骤：

找到配置文件

Linux/macOS 系统：通常是 /etc/my.cnf 或 /etc/mysql/my.cnf

Windows 系统：MySQL/my.ini

编辑配置文件

在 [mysqld] 部分添加或修改以下配置：

[mysqld]
performance_schema = ON

重启 MySQL 服务

Linux：sudo systemctl restart mysql 或 sudo service mysql restart

Windows：在服务管理器中重启 "MySQL" 服务，或使用命令 net stop mysql && net start mysql

验证是否启用

登录 MySQL 命令行后，执行以下命令检查：

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

sys数据库概述

sys数据库中包含1个数据表、100个视图、48个存储过程和函数。

其中视图总体上可以分为两类：

以正常字母开始的视图共52个，能够显示格式化后的数据，适合开发人员或数据库维护人员阅读。

以“x$”开始的视图共48个，只显示未经处理的原始数据，适合使用工具采集数据。

常用查询

查看每个数据库连接消耗资源

mysql>  SELECT * FROM sys.host_summary \G
*************************** 1. row ***************************
                  host: localhost
            statements: 15
     statement_latency: 23.01 ms
 statement_avg_latency: 1.53 ms
           table_scans: 4
              file_ios: 35
       file_io_latency: 9.95 ms
   current_connections: 1
     total_connections: 3
          unique_users: 1
        current_memory: 1.02 MiB
total_memory_allocated: 22.84 MiB
1 row in set (0.01 sec)

mysql>  SELECT * FROM sys.host_summary \G
*************************** 1. row ***************************
                  host: localhost
            statements: 52
     statement_latency: 33.14 ms
 statement_avg_latency: 637.24 us
           table_scans: 5
              file_ios: 35
       file_io_latency: 9.95 ms
   current_connections: 1
     total_connections: 3
          unique_users: 1
        current_memory: 920.56 KiB
total_memory_allocated: 27.50 MiB
1 row in set (0.00 sec)

mysql>  SELECT * FROM sys.host_summary \G
*************************** 1. row ***************************
                  host: localhost
            statements: 91
     statement_latency: 36.34 ms
 statement_avg_latency: 399.29 us
           table_scans: 6
              file_ios: 35
       file_io_latency: 9.95 ms
   current_connections: 1
     total_connections: 3
          unique_users: 1
        current_memory: 920.59 KiB
total_memory_allocated: 32.18 MiB
1 row in set (0.00 sec)

查看用户连接MySQL时消耗资源

mysql> SELECT * FROM sys.user_summary \G
*************************** 1. row ***************************
                  user: root
            statements: 130
     statement_latency: 39.35 ms
 statement_avg_latency: 302.68 us
           table_scans: 7
              file_ios: 38
       file_io_latency: 11.95 ms
   current_connections: 1
     total_connections: 3
          unique_hosts: 1
        current_memory: 1.04 MiB
total_memory_allocated: 38.44 MiB
*************************** 2. row ***************************
                  user: background
            statements: 1
     statement_latency: 150.90 us
 statement_avg_latency: 150.90 us
           table_scans: 0
              file_ios: 6805
       file_io_latency: 341.64 ms
   current_connections: 41
     total_connections: 47
          unique_hosts: 0
        current_memory: 42.34 MiB
total_memory_allocated: 395.75 MiB
2 rows in set (0.01 sec)

查看当前MySQL总分配内存资源

mysql> SELECT * FROM sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 344.69 MiB      |
+-----------------+
1 row in set (0.00 sec)

当前MySQL总共分配了344.69 MiB的内存资源。

查看MySQL中存在的冗余索引

mysql>  SELECT * FROM sys.schema_redundant_indexes \G
*************************** 1. row ***************************
              table_schema: books.com
                table_name: fa_auth_group_access
      redundant_index_name: uid
   redundant_index_columns: uid
redundant_index_non_unique: 1
       dominant_index_name: uid_group_id
    dominant_index_columns: uid,group_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `books.com`.`fa_auth_group_access` DROP INDEX `uid`
*************************** 2. row ***************************
              table_schema: cpoem
                table_name: album_0
      redundant_index_name: id
   redundant_index_columns: id
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `cpoem`.`album_0` DROP INDEX `id`
*************************** 3. row ***************************
              table_schema: cpoem
                table_name: album_1
      redundant_index_name: id
   redundant_index_columns: id
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `cpoem`.`album_1` DROP INDEX `id`
*************************** 4. row ***************************
              table_schema: cpoem
                table_name: album_2
      redundant_index_name: id
   redundant_index_columns: id
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `cpoem`.`album_2` DROP INDEX `id`
######################## 省略n行 ##############################
249 rows in set (0.11 sec)

结果会显示出当前MySQL中所有的冗余索引。

查看MySQL中未使用到的索引

mysql> SELECT * FROM sys.schema_unused_indexes \G
Empty set, 3 warnings (0.00 sec)

注意：这里简单列举了几个常用的sys数据库的查询操作，更多可到MySQL官网进一步学习sys数据库的使用。地址为https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html

总结

sys数据库比performance_schema数据库中的数据更加友好，

可以直接读取sys数据库中的数据来了解MySQL的运行情况。