参考文献

  • 千金良方: MySQL性能优化金字塔法则

sys系统库使用基础环境

  • 因为sys系统库提供了一些代替直接访问performance_schema的视图,所以必须启用performance_schema(将performance_schema系统参数设置为ON),sys系统库的大部分功能才能正常使用

  • 要完全访问sys系统库,用户必须具有以下权限.

    • 对所有sys表和视图具有SELECT权限.
    • 对所有sys存储过程和函数具有EXECUTE权限.
    • 对sys_config表具有INSERT、UPDATE权限.
    • 对某些特定的sys系统库存储过程和函数需要额外权限,如ps_setup_save()存储过程,需要与临时表相关的权限.

  • 还要有与被sys系统库执行访问的对象相关的权限.

    • 任何被sys系统库访问的performance_schema表都需要有SELECT权限,如果要使用sys系统库对performance_schema相关表执行更新,则需要有performance_schema相关表的UPDATE权限.
    • INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表的PROCESS权限.

  • 如果要充分使用sys系统库的功能,则必须启用某些performance_schema的instruments和consumers.

    • 所有的wait instruments.
    • 所有的stage instruments.
    • 所有的statement instruments.
    • 对于所启用的类型事件的instruments,还需要启用对应类型的consumers(xxx_current和xxx_history_long).要了解某存储过程具体做了什么事情,可以通过show create procedure procedure_name;语句查看.

  • 可以使用sys系统库本身来启用所有需要的instruments和consumers(而不需要手动使用DML语句来启用).

    • 启用所有的wait instruments: CALL sys.ps_setup_enable_instrument('wait');.
    • 启用所有的stage instruments: CALL sys.ps_setup_enable_instrument('stage');.
    • 启用所有的statement instruments: CALL sys.ps_setup_enable_instrument('statement');.
    • 启用所有事件类型的current表: CALL sys.ps_setup_enable_consumer('current');.
    • 启用所有事件类型的history_long表: CALL sys.ps_setup_enable_consumer('history_long');
    • 如果启用了一些默认配置之外的配置,则可以使用存储过程CALL sys.ps_setup_reset_to_default(TRUE);快速恢复到performance_schema的默认配置.

sys库使用

  • 在sys系统库下包含很多视图,它们以各种方式对performance_schema表进行聚合计算展示.这些视图大部分是成对出现的,两个视图名称相同,但有一个视图是带x$前缀的,例如: host_summary_by_file_io和x$host_summary_by_file_io,代表按照主机进行汇总统计的文件I/O性能数据,两个视图访问的数据源是相同的,但是在创建视图的语句中,不带x$前缀的视图显示的是相关数值经过单位换算后的数据(单位是毫秒、秒、分钟、小时、天等),带x$前缀的视图显示的是原始的数据(单位是皮秒).

示例

1
2
3
# 查看当前正在执行的语句进度信息
mysql > select * from session where conn_id!=connection_id() and trx_state='ACTIVE'\G
mysql > select * from session where conn_id!=connection_id() and trx_state='COMMITTED'\G

查看慢SQL语句慢在哪里

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root@192.168.11.216 [sys] 15:01:16>call sys.ps_setup_enable_instrument('wait');
+-----------------------+
| summary               |
+-----------------------+
| Enabled 9 instruments |
+-----------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

root@192.168.11.216 [sys] 15:02:07>call sys.ps_setup_enable_consumer('wait');
+--------------------+
| summary            |
+--------------------+
| Enabled 1 consumer |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@192.168.11.216 [sys] 15:02:17>select * from session where command ='query' and conn_id !=connection_id()\G

查看是否有事务锁等待

1
root@192.168.11.216 [sys] 15:00:48> select * from innodb_lock_waits\G
字段名 含义
wait_started 发生等待的开始时间
wait_age 线程已经等待了多久,该值是一个时间格式值
wait_age_secs 线程已经等待了几秒钟,该值是一个纯整型值.
locked_table 锁等待的表名.此字段值格式为:schema_name.table_name
locked_index 锁等待的索引名称
locked_type 锁等待的锁类型
waiting_trx_id 等待的事务 ID
waiting_trx_started 发生锁等待的事务开始时间
waiting_trx_age 发生锁等待的事务的等待时间,该值是一个时间格式值
waiting_trx_rows_locked 发生锁等待的事务已经锁定的行数(如果是复合事务会累积计算)
waiting_trx_rows_modified 发生锁等待的事务已经修改的行数(如果是复合事务会累积计算)
waiting_pid 发生锁等待的事务的 processlist_id
waiting_query 发生锁等待的事务的 SQL 语句文本
waiting_lock_id 发生锁等待的锁 ID
waiting_lock_mode 发生锁等待的锁模式
blocking_trx_id 持有锁的事务 ID
blocking_pid 持有锁的事务的 processlist_id
blocking_query 持有锁的事务的 SQL 语句文本
blocking_lock_id 持有锁的锁 ID
blocking_lock_mode 持有锁的锁模式
blocking_trx_started 持有锁的事务的开始时间
blocking_trx_age 持有锁的事务已经等待了多长时间,该值是时间格式值
blocking_trx_rows_locked 持有锁的事务已经锁定的行数
blocking_trx_rows_modified 持有锁的事务已经修改的行数
sql_kill_blocking_query 执行 KILL 语句来杀死持有锁的查询语句(而不是整个会话).该字段是 MySQL 5.7.9 中新增的
sql_kill_blocking_connection 执行 KILL 语句以杀死持有锁的整个会话.该字段是 MySQL 5.7.9 中新增的

查看是否有MDL锁等待

  • 通过schema_table_lock_waits视图可以查看当前链接线程的MDL锁等待信息,显示哪些会话被MDL锁阻塞,是谁阻塞了这些会话,数据来源:performance_schema下的threads、metadata_locks、events_statements_current表.
1
2
3
4
5
6
7
8
9
10
11
12
13
# 首先开启mdl相关的instrument
root@192.168.11.216 [sys] 15:13:02>call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
+-----------------------+
| summary               |
+-----------------------+
| Enabled 1 instruments |
+-----------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@192.168.11.216 [sys] 15:23:28>select * from schema_table_lock_waits\G
Empty set (0.01 sec)
字段名 含义
object_schema 被锁定表所在的数据库
object_name 被锁定表的名称
waiting_thread_id 等待锁的线程 ID
waiting_pid 等待锁的进程 ID
waiting_account 等待锁的用户账户
waiting_lock_type 等待的锁类型(如:TABLE LOCK、ROW LOCK)
waiting_lock_duration 等待锁的持续时间类型(如:TRANSACTION、EXPLICIT)
waiting_query 导致等待的 SQL 查询文本
waiting_query_secs 导致等待的 SQL 查询的执行时间(秒)
waiting_query_rows_affected 导致等待的 SQL 查询影响的行数
waiting_query_rows_examined 导致等待的 SQL 查询检查的行数
blocking_thread_id 持有锁的线程 ID
blocking_pid 持有锁的进程 ID
blocking_account 持有锁的用户账户
blocking_lock_type 持有锁的锁类型(如:TABLE LOCK、ROW LOCK)
blocking_lock_duration 持有锁的持续时间类型(如:TRANSACTION、EXPLICIT)
sql_kill_blocking_query 用于终止持有锁的查询的 SQL 命令
sql_kill_blocking_connection 用于终止持有锁的连接的 SQL 命令

查看InnoDB缓冲池中的热点数据有哪些

  • 使用innodb_buffer_stats_by_schema视图可以按照schema分组查询InnoDB缓冲池的统计信息,默认按照已分配的buffer size(缓冲区大小)降序排列(allocated字段).数据来源:information_schema.innodb_buffer_page

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    root@192.168.11.216 [sys] 15:25:50>select * from innodb_buffer_stats_by_schema;
    +---------------+-----------+------------+-------+--------------+-----------+-------------+
    | object_schema | allocated | data       | pages | pages_hashed | pages_old | rows_cached |
    +---------------+-----------+------------+-------+--------------+-----------+-------------+
    | sbtest        | 71.97 MiB | 59.50 MiB  |  4606 |         1649 |      2228 |       96081 |
    | athena        | 13.58 MiB | 10.61 MiB  |   869 |          104 |       146 |        2692 |
    | mysql         | 2.22 MiB  | 1.38 MiB   |   142 |           89 |        53 |        1347 |
    | sys           | 16.00 KiB |  338 bytes |     1 |            0 |         0 |           6 |
    +---------------+-----------+------------+-------+--------------+-----------+-------------+
    4 rows in set (0.28 sec)

查看冗余索引

  • 使用schema_redundant_indexes视图可以查看重复或冗余索引,数据来源:sys.x$schema_flattened_keys,该数据来源视图被称作schema_redundant_indexes视图的辅助视图.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    root@192.168.11.216 [sys] 15:29:55>select * from schema_redundant_indexes limit 1\G
    *************************** 1. row ***************************
                  table_schema: dbt3
                    table_name: lineitem
          redundant_index_name: i_l_orderkey
       redundant_index_columns: l_orderkey
    redundant_index_non_unique: 1
           dominant_index_name: PRIMARY
        dominant_index_columns: l_orderkey,l_linenumber
     dominant_index_non_unique: 0
                subpart_exists: 0
                sql_drop_index: ALTER TABLE `dbt3`.`lineitem` DROP INDEX `i_l_orderkey`
    1 row in set (0.02 sec)

查看未使用的索引

  • 使用schema_unused_indexes视图可以查看不活跃的索引(没有任何事件发生的索引,表示该索引从未使用过),在默认情况下按照schema名称和表名进行排序.数据来源:performance_schema.table_io_waits_summary_by_index_usage,该视图在服务器启动并运行足够长的时间后,所查询出的数据才比较实用；否则,使用该视图查询出的数据可能并不十分可靠,因为统计的数据可能并不精确,有一部分业务查询逻辑可能还来不及查询.

    1
    2
    3
    4
    5
    6
    root@192.168.11.216 [sys] 15:30:21>select * from schema_unused_indexes limit 1\G
    *************************** 1. row ***************************
    object_schema: athena
      object_name: dicom_patient_info
       index_name: key_patient_name
    1 row in set (0.09 sec)

查询表的增、删、改、查数据量和I/O耗时统计信息

  • 使用schema_table_statistics_with_buffer视图可以查询表的增、删、改、查数据量,I/O耗时,以及在InnoDB缓冲池中占用情况等统计信息,在默认情况下按照增、删、改、查操作的总表I/O延迟时间(执行时间,也可以理解为存在最多I/O争用的表)降序排列.数据来源:performance_schema.table_io_waits_summary_by_table、sys.x$ps_schema_table_statistics_ io、sys.x$innodb_buffer_stats_by_table.另外,该视图在内部使用了辅助视图sys.x$ps_ schema_table_statistics_io

    1
    root@192.168.11.216 [sys] 15:31:31>select * from schema_table_statistics_with_buffer limit 1\G

查看MySQL磁盘文件产生的磁盘流量与读写比例

  • 使用io_global_by_file_by_bytes视图可以按照文件路径+名称分组(磁盘文件名)查看全局的I/O读写字节数、读写文件I/O事件数量统计信息,在默认情况下按照总I/O读写字节数进行降序排列.数据来源:performance_schema.file_summary_by_instance

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    root@192.168.11.216 [sys] 15:33:06>select * from io_global_by_file_by_bytes limit 1\G
    *************************** 1. row ***************************
             file: @@datadir/#ib_16384_1.dblwr
       count_read: 1
       total_read: 8.19 MiB
         avg_read: 8.19 MiB
      count_write: 577344
    total_written: 8.81 GiB
        avg_write: 16.00 KiB
            total: 8.82 GiB
        write_pct: 99.91
    1 row in set (0.04 sec)

查看哪些语句使用了全表扫描

  • 使用statements_with_full_table_scans视图可以查看全表扫描或者没有使用最优索引的语句(经过标准化转换的语句文本),在默认情况下按照全表扫描次数与语句总次数百分比和语句总延迟时间(执行时间)降序排列.数据来源:performance_schema.events_statements_ summary_by_digest

    1
    root@192.168.11.216 [sys] 15:34:07>select * from statements_with_full_table_scans limit 1\G

查看哪些语句使用了文件排序

  • 使用statements_with_sorting视图可以查看执行了文件排序的语句,在默认情况下按照语句总延迟时间(执行时间)降序排列.数据来源:performance_schema.events_statements_ summary_by_digest

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    root@192.168.11.216 [sys] 15:35:07>select * from statements_with_sorting limit 1\G
    *************************** 1. row ***************************
                query: SELECT `user_id` , `username`  ... ? ORDER BY `created_time` DESC
                   db: athena
           exec_count: 143
        total_latency: 412.21 ms
    sort_merge_passes: 0
      avg_sort_merges: 0
    sorts_using_scans: 143
     sort_using_range: 0
          rows_sorted: 3352
      avg_rows_sorted: 23
           first_seen: 2024-06-28 09:45:27.905146
            last_seen: 2024-06-28 14:46:46.075047
               digest: d155d6b92e1cc530703a86fd6548e5640fbc9319734729e943a2a6f6ac982e80
    1 row in set (0.12 sec)

查看哪些语句使用了临时表

  • 使用statements_with_temp_tables视图可以查看使用了临时表的语句,在默认情况下按照磁盘临时表数量和内存临时表数量进行降序排列.数据来源:performance_schema.events_ statements_summary_by_digest.

    1
    root@192.168.11.216 [sys] 15:40:06>select * from statements_with_temp_tables limit 1\G