结构

  • 客户端 : 发送连接请求,SQL请求
  • MySQL-server:

连接器: 提供连接服务,权限认证
分析器: 把SQL语句切分,语法分析,词法分析,转换成AST(抽象语法树)
优化器: 优化SQL语句,规定执行流程
两种优化方式,RBO(基于规则的优化)和CBO(基于成本的优化),更多的使用CBO
可以查看SQL语句的执行计划,采用对应的优化点,加快查询
执行器: SQL语句的实际执行组件,和存储引擎挂钩,查询数据
MySQL8之前还有一层缓存,8之后去掉了;因为数据经常变化,命中率不高

可以帮助我们分析SQL的执行情况
进入MySQL客户端后,输入命令: set profiling=1; 开启

然后执行SQL后,就可以通过show profiles; 查看了

show profiles; 简单的查看一个列表,会带着QueryId
show profile; 查看最近一条SQL的各个步骤的详情
show profile [type] for query queryId; 查看某条SQL的各个步骤的详情,可以指定type
MySQL(一)MySQL结构介绍,性能监控 - 图1

  1. The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.19.1, “Query Profiling Using Performance Schema”.
  2. 现在版本还能继续用,将来版本很可能会被删除的;用Performance Schema替代
  3. —

Performance Schema

https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
默认开启,通过show databases; 就可以看到performance_schema库,数据不会持久化
它比show profiles功能强大很多,但使用起来也复杂很多;一般我们作为开发人员,show profiles就够了…
performance_schema 数据库中的表使用performance_schema存储引擎

两个基本概念：

  1. instruments: 生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。
  2. consumers:消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。

    简单配置与使用

    数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异)。

select * from setup_instruments; 可以看到很多NO

  1. --打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项
  2. UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';
  4. --打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项
  5. UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';
  7. --当配置完成之后可以查看当前server正在做什么，可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件
  8. select * from events_waits_current\G
  9. *************************** 1. row ***************************
  10.             THREAD_ID: 11
  11.              EVENT_ID: 570
  12.          END_EVENT_ID: 570
  13.            EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
  14.                SOURCE: 
  15.           TIMER_START: 4508505105239280
  16.             TIMER_END: 4508505105270160
  17.            TIMER_WAIT: 30880
  18.                 SPINS: NULL
  19.         OBJECT_SCHEMA: NULL
  20.           OBJECT_NAME: NULL
  21.            INDEX_NAME: NULL
  22.           OBJECT_TYPE: NULL
  23. OBJECT_INSTANCE_BEGIN: 67918392
  24.      NESTING_EVENT_ID: NULL
  25.    NESTING_EVENT_TYPE: NULL
  26.             OPERATION: lock
  27.       NUMBER_OF_BYTES: NULL
  28.                 FLAGS: NULL
  29. /*该信息表示线程id为11的线程正在等待buf_dblwr_mutex锁，等待事件为30880
  30. 属性说明：
  31.     id:事件来自哪个线程，事件编号是多少
  32.     event_name:表示检测到的具体的内容
  33.     source:表示这个检测代码在哪个源文件中以及行号
  34.     timer_start:表示该事件的开始时间
  35.     timer_end:表示该事件的结束时间
  36.     timer_wait:表示该事件总的花费时间
  37. 注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息
  38. */
  40. /*
  41. _history表中记录每个线程应该执行完成的事件信息，但每个线程的事件信息只会记录10条，再多就会被覆盖，*_history_long表中记录所有线程的事件信息，但总记录数量是10000，超过就会被覆盖掉
  42. */
  43. select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;
  45. /*
  46. summary表提供所有事件的汇总信息，该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来）
  47. */
  48. SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name  ORDER BY COUNT_STAR DESC LIMIT 10;
  50. /*
  51. instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录，例如，file_instances表列出了文件I/O操作及其关联文件名
  52. */
  53. select * from file_instances limit 20;

performance_schema实践操作

基本了解了表的相关信息之后，可以通过这些表进行实际的查询操作来进行实际的分析。

  1. --1、哪类的SQL执行最多？
  2. SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  3. --2、哪类SQL的平均响应时间最多？
  4. SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  5. --3、哪类SQL排序记录数最多？
  6. SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  7. --4、哪类SQL扫描记录数最多？
  8. SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  9. --5、哪类SQL使用临时表最多？
  10. SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  11. --6、哪类SQL返回结果集最多？
  12. SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  13. --7、哪个表物理IO最多？
  14. SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
  15. --8、哪个表逻辑IO最多？
  16. SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
  17. --9、哪个索引访问最多？
  18. SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
  19. --10、哪个索引从来没有用过？
  20. 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;
  21. --11、哪个等待事件消耗时间最多？
  22. SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
  23. --12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
  24. SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
  25. --12-2、查看每个阶段的时间消耗
  26. SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
  27. --12-3、查看每个阶段的锁等待情况
  28. SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

show processlist

官网SQL Statement下面
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
查看MySQL-server建立的连接情况
MySQL(一)MySQL结构介绍,性能监控 - 图2

连接池

工程中我们一般会使用数据库连接池,常见的有DOCP,C3P0,阿里的Druid
druid监控功能很方便
https://github.com/alibaba/druid/wiki/Druid%E8%BF%9E%E6%8E%A5%E6%B1%A0%E4%BB%8B%E7%BB%8D
MySQL(一)MySQL结构介绍,性能监控 - 图3