如何发现快速发现分析生产问题SQL

Performance Schema介绍
Performance Schema提供了有关MySQL服务器内部运行的操作上的底层指标。为了解释清楚Performance Schema的工作机制，先介绍两个概念。
第一个概念是程序插桩（instrument）。程序插桩在MySQL代码中插入探测代码，以获取我们想了解的信息。例如，如果想收集关于元数据锁的使用情况，需要启用wait/lock/meta-data/sql/mdl这个插桩。
第二个概念是消费者表（consumer），指的是存储关于程序插桩代码信息的表。如果我们为查询模块添加插桩，相应的消费者表将记录诸如执行总数、未使用索引的次数、花费的时间等信息。大多数人都将消费者表与Performance Schema紧密联系在一起。

当应用程序用户连接到MySQL并执行被测量的插桩指令时，performance_schema将每个检查的调用封装到两个宏中，然后将结果记录在相应的消费者表中。这里的要点是，启用插桩会调用额外的代码，这意味着插桩会消耗CPU资源。
插桩元件
在performance_schema中，setup_instruments表包含所有支持的插桩的列表。所有插桩的名称都由用斜杠分隔的部件组成。下面的例子展示了插桩的命名规则：
● statement/sql/select
● wait/synch/mutex/innodb/autoinc_mutex
插桩名称的最左边部分表示插桩的类型。因此，statement表示插桩类型是statement，wait表示插桩类型是wait，以此类推。
名称字段中的其余部分从左至右依次表示从通用到特定的子系统。select是sql子系统的一部分，属于statement类型。或者autoinc_mutex属于innodb，它是更通用的插桩类mutex的一部分，而mutex又是更通用的插桩类型wait的sync插桩的一部分。
大多数插桩名称是自描述型的。与示例中一样，statement/sql/select是一个SELECT查询，wait/synch/mutex/innodb/autoinc_mutex是InnoDB在自增列上设置的一个互斥体。setup_instruments表中还有一个DOCUMENTATION列，其中包含更多详细信息：

SELECT * FROM performance_schema.setup_instruments WHERE DOCUMENTATION IS NOT NULL

performance_schema的消费者表

存放事件的表名包含如下结尾：
● _current 当前服务器上进行中的事件。
● _history 每个线程最近完成的10个事件。
● _history_long 从全局来看，每个线程最近完成的10000个事件。注：_history和history_long表的大小是可配置的。
● events_waits：底层服务器等待，例如获取互斥对象。
● events_statements_* SQL查询语句。
● events_stages_* 配置文件信息，例如创建临时表或发送数据。
● events_transactions_* 事务相关。
● memory_summary_by_thread_by_event_name表保存了用户连接或任何后台线程的每个MySQL线程的聚合内存使用情况。
SQL的分析过程
重点关注的插桩元件

原始SQL

select film_id,film.description from sakila.film inner join (select film_id from sakila.film order by title limit 50,5) as lim using(film_id)

执行结果

51	A Insightful Panorama of a Forensic Psychologist And a Mad Cow who must Build a Mad Scientist in The First Manned Space Station
52	A Thrilling Documentary of a Composer And a Monkey who must Find a Feminist in California
53	A Epic Drama of a Madman And a Cat who must Face a A Shark in An Abandoned Amusement Park
54	A Awe-Inspiring Drama of a Car And a Pastry Chef who must Chase a Crocodile in The First Manned Space Station
55	A Awe-Inspiring Story of a Feminist And a Cat who must Conquer a Dog in A Monastery

查看运行过程

select * from `performance_schema`.events_statements_history where sql_text like 'select film_id%'\G

运行结果

mysql> select * from `performance_schema`.events_statements_history where sql_text like 'select film_id%'\G
*************************** 1. row ***************************
              THREAD_ID: 124
               EVENT_ID: 43
           END_EVENT_ID: 44
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:95
            TIMER_START: 2680897723222415000
              TIMER_END: 2680897723733954000
             TIMER_WAIT: 511539000
              LOCK_TIME: 224000000
               SQL_TEXT: select film_id,film.description from sakila.film inner join (select film_id from sakila.film order by title limit 50,5) as lim using(film_id)
                 DIGEST: f65da42ebf50607dfda40f3b31304775b671fb71122f0860f312ff1d330de99f
            DIGEST_TEXT: SELECT `film_id` , `film` . `description` FROM `sakila` . `film` INNER JOIN ( SELECT `film_id` FROM `sakila` . `film` ORDER BY `title` LIMIT ?, ... ) AS `lim` USING ( `film_id` )
         CURRENT_SCHEMA: sakila
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 5
          ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 2
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 1311

要找出哪些语句需要优化，可以选择上述列中的任何一列，并将其与0进行比较。例如，要找到所有没有使用合适索引的查询，可运行以下命令：

SELECT * 
FROM
	`performance_schema`.events_statements_history_long
WHERE
	NO_INDEX_USED > 0 
	OR NO_GOOD_INDEX_USED > 0

要查询所有创建了临时表的查询，可运行：

SELECT * 
FROM
	`performance_schema`.events_statements_history_long
WHERE
	CREATED_TMP_DISK_TABLES > 0 
	OR CREATED_TMP_TABLES > 0 

可以使用这些列值单独显示潜在的问题。例如，要查找所有返回错误的查询，可以使用条件WHERE errors > 0；要找到所有执行时间超过5秒的查询，可以使用条件WHERE TIMER_WAIT > 5000000000，等等。
挖掘可能存在问题的SQL

SELECT
	* 
FROM
	`performance_schema`.events_statements_history_long
WHERE
	ROWS_EXAMINED > ROWS_SENT 
	OR ROWS_EXAMINED > ROWS_AFFECTED 
	OR ERRORS > 0 
	OR CREATED_TMP_DISK_TABLES > 0 
	OR CREATED_TMP_TABLES > 0 
	OR SELECT_FULL_JOIN > 0 
	OR SELECT_FULL_RANGE_JOIN > 0 
	OR SELECT_RANGE > 0 
	OR SELECT_RANGE_CHECK > 0 
	OR SELECT_SCAN > 0 
	OR SORT_MERGE_PASSES > 0 
	OR SORT_RANGE > 0 
	OR SORT_ROWS > 0 
	OR SORT_SCAN > 0 
	OR NO_INDEX_USED > 0 
	OR NO_GOOD_INDEX_USED > 0