MySQL：性能优化之 Performance Schema

一、简介

Performance Schema 具有以下特征：

• 从MySQL5.6开始，Performance Schema 默认打开
• MySQLPerformance Schema是一种用于在底层监视MySQL服务器执行的功能。Performance Schema具有以下特征：
• MySQL Performance Schema 用于监视MySQL服务器，且运行时消耗很少的性能。Performance Schema 收集数据库服务器性能参数，并且表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
• Performance Schema 提供了一种在服务器运行时检查服务器的内部执行的方法。它使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现。性能模式主要关注性能数据。这与用于检查元数据的INFORMATION_SCHEMA不同。
• Performance Schema 事件特定于MySQL服务器的给定实例。 Performance Schema 表被视为本地服务器，并且对其进行的更改不会被复制或写入二进制日志。
• Performance Schema 中的表是内存表，不使用磁盘存储，在 datadir 的 performance_schema 目录下，只有.frm表结构文件，没有数据文件。表内容在服务器启动时重新填充，并在服务器关闭时丢弃。

二、配置performance_schema

• 开启：在配置文件中添加performance_schema=on
• 配置
  
  • setup_instruments表：setup_instruments表指定了对哪些项进行监控
  • setup_consumers 表：控制是否将监控到的结果进行记录

三、示例

查看某个时刻的等待事件

--查询 events_waits_current表。它记录了每个thread最近的监控信息。
SELECT * FROM events_waits_current

查询没有使用到索引或者索引效率低下的语句

SELECT 
    OBJECT_SCHEMA,  
    THREAD_ID TID, 
    SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, 
    ROWS_SENT RS, ROWS_EXAMINED RE,
    CREATED_TMP_TABLES,
    NO_INDEX_USED,
    NO_GOOD_INDEX_USED 
FROM performance_schema.events_statements_history 
WHERE 
    (NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1) 
    AND sql_text  NOT LIKE '%performance_schema%'

查看哪些索引没有被使用过

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE 
    INDEX_NAME IS NOT NULL
    AND COUNT_STAR = 0
    AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

查看SQL语句在哪个阶段消耗最大

SELECT 
    eshl.event_name, 
    sql_text, 
    eshl.timer_wait/1000000000000 w_s
FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
    ON (eshl.nesting_event_id = esthl.event_id)
WHERE eshl.timer_wait > 1*10000000000

四、参考资料

MySQL 5.7 Reference Manual: Chapter 25 MySQL Performance Schema
Performance Schema使用简介(一)