MySQL 최적화는 주로 논리 아키텍처의
핵심 계층(분석, 최적화 및 실행 단계)에서 시작됩니다.核心层-分析、优化、执行阶段着手。
实验过程中，我是用的MySQL版本是5.7，关于MySQL的快速安装，可参考 如何在CentOS7下快速安装MySQL5.7。
MySQL调优，我们先从性能监控开始，找点感觉。
show profile
show profile是MySQL的查询剖析工具。
mysql> show variables like '%profil%'; +------------------------+-------+| Variable_name | Value | +------------------------+-------+| have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+3 rows in set (0.00 sec)复制代码
可以看到此工具默认是
OFF（禁用）的，可以通过服务器变量在当前会话级别动态的修改
mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)复制代码
当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
我们来创建一个表来演示一下：
mysql> create database mytest; Query OK, 1 row affected (0.01 sec) mysql> use mytest; Database changed mysql> create table stu(id int(8), name varchar(20)); Query OK, 0 rows affected (0.07 sec) mysql> insert into stu(id, name) values(1, 'Jack Ma'); Query OK, 1 row affected (0.01 sec) mysql> insert into stu(id, name) values(1, 'Pony'); Query OK, 1 row affected (0.03 sec)复制代码
查询一下stu表：
mysql> select * from stu; +------+---------+| id | name | +------+---------+| 1 | Jack Ma | | 2 | Pony | +------+---------+2 rows in set (0.00 sec)复制代码
这个2 rows in set (0.00 sec)就是用命令行执行一个sql后给出的提示，而且还有执行时间0.00秒。
在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间
mysql> show profile; +----------------------+----------+| Status | Duration | +----------------------+----------+| starting | 0.000061 | | checking permissions | 0.000009 | | Opening tables | 0.000019 | | init | 0.000020 | | System lock | 0.000009 | | optimizing | 0.000005 | | statistics | 0.000014 | | preparing | 0.000011 | | executing | 0.000003 | | Sending data | 0.000121 | | end | 0.000012 | | query end | 0.000011 | | closing tables | 0.000011 | | freeing items | 0.000021 | | cleaning up | 0.000021 | +----------------------+----------+复制代码
这样我们可以清晰地看到，Starting、System lock系统锁、Sending data等等一系列操作分别用了多少时间。
通过上面的演示，我们可以看到，show profile一执行完就给我们返回每项的执行时间，并没有显示是针对哪条sql的，其实它就是列出了最近一条你执行的sql语句的执行时间。
那一定有可以看之前执行过的多条sql语句的情况吧？是的，show profiles
show profilesmysql> show profiles; +----------+------------+------------------------------------------------+| Query_ID | Duration | Query | +----------+------------+------------------------------------------------+| 1 | 0.02907350 | create table stu(id int(8), name varchar(20)) | | 2 | 0.00337800 | create database mytest | | 3 | 0.02786850 | SELECT DATABASE() | | 4 | 0.00065300 | show databases | | 5 | 0.00086700 | show tables | | 6 | 0.06554900 | create table stu(id int(8), name varchar(20)) | | 7 | 0.00079850 | insert into stu(1, 'Jack') | | 8 | 0.06901975 | show create table stu | | 9 | 0.00008800 | insert into stu(1, 'Jack') | | 10 | 0.00616000 | insert into stu(id, name) values(1, 'Jack Ma') | | 11 | 0.03119675 | insert into stu(id, name) values(1, 'Pony') | | 12 | 0.03124900 | update stu set id=2 where name='Pony' | | 13 | 0.00036975 | select * from stu | | 14 | 0.00034925 | select * from stu | +----------+------------+------------------------------------------------+14 rows in set, 1 warning (0.00 sec)复制代码
列出了执行过的sql语句执行时间，我们可以挑一个查询来单独看，比如我想看insert into stu(id, name) values(1, 'Pony')这条语句的情况，可以用下面的操作：
mysql> show profile for query 11; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000072 | | checking permissions | 0.000009 | | Opening tables | 0.000021 | | init | 0.000018 | | System lock | 0.000008 | | update | 0.000094 | | end | 0.000005 | | query end | 0.030815 | | closing tables | 0.000036 | | freeing items | 0.000081 | | cleaning up | 0.000039 | +----------------------+----------+11 rows in set, 1 warning (0.00 sec)复制代码
也就是说可以根据Query_ID结合show profile来看。
MySQL官网上，关于show profile语句：
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}复制代码show profile、for query n前面已经说过了，下面我列一下每个type是什么意思。
all显示所有性能信息
block io显示块io操作的次数
context switches显示上下文切换次数，被动和主动
cpu显示用户cpu时间、系统cpu时间
IPC显示发送和接受的消息数量
Memory暂未实现
page faults显示页错误数量
source显示源码中的函数名称与位置
swaps显示swap的次数
实操一下看看
mysql> show profiles; +----------+------------+-------------------------------+| Query_ID | Duration | Query | +----------+------------+-------------------------------+| 1 | 0.00009625 | show variable like '%profi%' | | 2 | 0.00373950 | show variables like '%profi%' | | 3 | 0.00025825 | select * from stu | +----------+------------+-------------------------------+3 rows in set, 1 warning (0.00 sec)复制代码
我们针对Query_ID为3的进行查看
allmysql> show profile all for query 3\G *************************** 1. row *************************** Status: starting Duration: 0.000070 CPU_user: 0.000000 CPU_system: 0.000064 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: NULL Source_file: NULL Source_line: NULL*************************** 2. row *************************** Status: checking permissions Duration: 0.000011 CPU_user: 0.000000 CPU_system: 0.000009 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: check_access Source_file: sql_authorization.cc Source_line: 809*************************** 3. row *************************** Status: Opening tables Duration: 0.000021 CPU_user: 0.000000 CPU_system: 0.000021 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: open_tables Source_file: sql_base.cc Source_line: 5793*************************** 4. row *************************** Status: init Duration: 0.000019 CPU_user: 0.000000 CPU_system: 0.000019 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: handle_query Source_file: sql_select.cc Source_line: 128*************************** 5. row *************************** Status: System lock Duration: 0.000009 CPU_user: 0.000000 CPU_system: 0.000009 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: mysql_lock_tables Source_file: lock.cc Source_line: 330*************************** 6. row *************************** Status: optimizing Duration: 0.000005 CPU_user: 0.000000 CPU_system: 0.000004 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: optimize Source_file: sql_optimizer.cc Source_line: 158*************************** 7. row *************************** Status: statistics Duration: 0.000014 CPU_user: 0.000000 CPU_system: 0.000014 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: optimize Source_file: sql_optimizer.cc Source_line: 374*************************** 8. row *************************** Status: preparing Duration: 0.000011 CPU_user: 0.000000 CPU_system: 0.000010 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: optimize Source_file: sql_optimizer.cc Source_line: 482*************************** 9. row *************************** Status: executing Duration: 0.000004 CPU_user: 0.000000 CPU_system: 0.000004 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: exec Source_file: sql_executor.cc Source_line: 126*************************** 10. row *************************** Status: Sending data Duration: 0.000050 CPU_user: 0.000000 CPU_system: 0.000050 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: exec Source_file: sql_executor.cc Source_line: 202*************************** 11. row *************************** Status: end Duration: 0.000005 CPU_user: 0.000000 CPU_system: 0.000005 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: handle_query Source_file: sql_select.cc Source_line: 206*************************** 12. row *************************** Status: query end Duration: 0.000008 CPU_user: 0.000000 CPU_system: 0.000008 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 4956*************************** 13. row *************************** Status: closing tables Duration: 0.000008 CPU_user: 0.000000 CPU_system: 0.000008 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 5009*************************** 14. row *************************** Status: freeing items Duration: 0.000013 CPU_user: 0.000000 CPU_system: 0.000013 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: mysql_parse Source_file: sql_parse.cc Source_line: 5622*************************** 15. row *************************** Status: cleaning up Duration: 0.000014 CPU_user: 0.000000 CPU_system: 0.000013 Context_voluntary: 0Context_involuntary: 0 Block_ops_in: 0 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: dispatch_command Source_file: sql_parse.cc Source_line: 193115 rows in set, 1 warning (0.00 sec)复制代码
查询最后的
G的目的是格式化显示一下查询结果。这里我们可以看到，
show profile all可以看到所有的性能信息。
block iomysql> show profile block io for query 3; +----------------------+----------+--------------+---------------+| Status | Duration | Block_ops_in | Block_ops_out | +----------------------+----------+--------------+---------------+| starting | 0.000070 | 0 | 0 | | checking permissions | 0.000011 | 0 | 0 | | Opening tables | 0.000021 | 0 | 0 | | init | 0.000019 | 0 | 0 | | System lock | 0.000009 | 0 | 0 | | optimizing | 0.000005 | 0 | 0 | | statistics | 0.000014 | 0 | 0 | | preparing | 0.000011 | 0 | 0 | | executing | 0.000004 | 0 | 0 | | Sending data | 0.000050 | 0 | 0 | | end | 0.000005 | 0 | 0 | | query end | 0.000008 | 0 | 0 | | closing tables | 0.000008 | 0 | 0 | | freeing items | 0.000013 | 0 | 0 | | cleaning up | 0.000014 | 0 | 0 | +----------------------+----------+--------------+---------------+15 rows in set, 1 warning (0.00 sec)复制代码
最后两列就表示块io操作的次数。
下面只把各个
type的语法列出来，演示效果不在此赘述了。
show profile context switches for query 3show profile cpu for query 3show profile ipc for query 3show profile page faults for query 3show profile source for query 3show profile swaps for query 3show profile用起来感觉很爽啊，他能帮我们看到sql语句各部分的执行时间，这对我们定位线上sql执行慢的问题很有帮助啊，but，官网上有这么一段话：
Note
The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead;
注意
不推荐使用SHOW PROFILE和SHOW PROFILES语句，并将在以后的MySQL版本中将其删除。
改用Performance Schema；
好吧，你说删除就删除。但是在MySQL删除show profile之前，我们还是可以用的，毕竟很香嘛。
既然后续版本会用Performance Schema
성능 모니터링부터 살펴보겠습니다. 🎜프로필 표시 사용프로필 표시는 MySQL용 쿼리 프로파일링 도구입니다. 🎜mysql> show variables like 'performance_schema'; +--------------------+-------+| Variable_name | Value | +--------------------+-------+| performance_schema | ON | +--------------------+-------+mysql> set performance_schema=0; ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable复制代码
OFF(비활성화)되어 있으며 서버 변수를 통해 현재 세션 수준에서 동적으로 수정할 수 있음을 알 수 있습니다🎜[mysqld] performance_schema=ON复制代码
mysql> use performance_schema; mysql> show tables;复制代码
stu 테이블 쿼리: 🎜mysql> show create table threads\G
*************************** 1. row *************************** Table: threadsCreate Table: CREATE TABLE `threads` ( `THREAD_ID` bigint(20) unsigned NOT NULL, `NAME` varchar(128) NOT NULL, `TYPE` varchar(10) NOT NULL, `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL, `PROCESSLIST_USER` varchar(32) DEFAULT NULL, `PROCESSLIST_HOST` varchar(60) DEFAULT NULL, `PROCESSLIST_DB` varchar(64) DEFAULT NULL, `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL, `PROCESSLIST_TIME` bigint(20) DEFAULT NULL, `PROCESSLIST_STATE` varchar(64) DEFAULT NULL, `PROCESSLIST_INFO` longtext, `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL, `ROLE` varchar(64) DEFAULT NULL, `INSTRUMENTED` enum('YES','NO') NOT NULL, `HISTORY` enum('YES','NO') NOT NULL, `CONNECTION_TYPE` varchar(16) DEFAULT NULL, `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf81 row in set (0.00 sec)复制代码2 행 세트(0.00초)는 프롬프트입니다. 명령줄을 사용하여 SQL을 실행한 후 주어지며 실행 시간은 0.00초입니다. 🎜🎜mysql의 명령줄 모드에서는 시간을 소수점 이하 두 자리까지만 표시할 수 있습니다. 다음 명령을 사용하면 특정 실행 시간을 볼 수 있습니다.🎜mysql> show create table setup_consumers;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| setup_consumers | CREATE TABLE `setup_consumers` ( `NAME` varchar(64) NOT NULL, `ENABLED` enum('YES','NO') NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)复制代码시작을 명확하게 볼 수 있습니다. , 시스템 잠금시스템 잠금, 데이터 전송 등과 같은 일련의 작업에 얼마나 시간이 걸렸나요? 🎜🎜위 데모를 통해 show profile이 각 항목이 실행되자마자 해당 항목의 실행 시간을 반환한다는 것을 알 수 있습니다. 실제로는 어떤 SQL에 대한 것인지 표시하지 않습니다. 가장 최근에 실행한 SQL 문의 실행 시간을 나열합니다. 🎜🎜 그러면 이전에 실행되었던 여러 개의 SQL문을 볼 수 있는 상황이 있겠죠? 예, 프로필 표시🎜프로필 표시--语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)show tables like '%statement%';--等待事件记录表，与语句事件类型的相关记录表类似：show tables like '%wait%';--阶段事件记录表，记录语句执行的阶段事件的表show tables like '%stage%';--事务事件记录表，记录事务相关的事件的表show tables like '%transaction%';--监控文件系统层调用的表show tables like '%file%';--监视内存使用的表show tables like '%memory%';--动态对performance_schema进行配置的配置表show tables like '%setup%';复制代码
insert into stu(id, name) value(1, 'Pony') 문을 보려면 다음 작업을 사용할 수 있습니다. 🎜mysql> show tables like '%transaction%'; +------------------------------------------------------+| Tables_in_performance_schema (%transaction%) | +------------------------------------------------------+| events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | +------------------------------------------------------+8 rows in set (0.00 sec)复制代码
쇼프로필과 결합된 Query_ID를 기준으로 볼 수 있다는 것입니다. 🎜🎜MySQL 공식 웹사이트, show profile 문과 관련하여: 🎜mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%'; Query OK, 267 rows affected (0.03 sec) Rows matched: 321 Changed: 267 Warnings: 0复制代码
show profile, for query n은 이전에 언급되었으므로 나열하겠습니다. 아래에서 각 유형이 무엇을 의미하는지 살펴보겠습니다. 🎜🎜🎜all 모든 성능 정보 표시 🎜🎜block io 블록 io 작업 수 표시 🎜🎜컨텍스트 스위치 컨텍스트 스위치 수 표시 , Passive 및 Active🎜🎜cpu 사용자 CPU 시간 및 시스템 CPU 시간을 표시합니다🎜🎜IPC 보내고 받은 메시지 수를 표시합니다🎜🎜메모리 아직 구현되지 않았습니다🎜 🎜페이지 폴트 페이지 폴트 수를 표시합니다 🎜🎜source 소스 코드에서 함수 이름과 위치를 표시합니다 🎜🎜스왑 스왑 횟수를 표시합니다 🎜🎜실전에서 확인해 보겠습니다🎜mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%'; Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0复制代码
Query_ID가 3🎜all인지 확인하겠습니다 li>该信息表示线程id为3的线程正在等待buf_dblwr_mutex锁，等待时间为69917。 属性说明： id:事件来自哪个线程，事件编号是多少 event_name:表示检测到的具体的内容 source:表示这个检测代码在哪个源文件中以及行号 timer_start:表示该事件的开始时间 timer_end:表示该事件的结束时间 timer_wait:表示该事件总的花费时间 注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息。复制代码
G의 목적은 쿼리 결과의 형식을 지정하고 표시하는 것입니다. 🎜🎜여기에서 show profile all이 모든 성과 정보를 볼 수 있음을 알 수 있습니다. 🎜block iomysql> mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21; +-----------+----------+------------------------------------------+------------+| thread_id | event_id | event_name | timer_wait | +-----------+----------+------------------------------------------+------------+| 3 | 4042 | wait/synch/mutex/innodb/buf_pool_mutex | 47785 | | 3 | 4043 | wait/synch/mutex/innodb/flush_list_mutex | 43761 | | 3 | 4044 | wait/synch/mutex/innodb/buf_pool_mutex | 58851 | | 3 | 4045 | wait/synch/mutex/innodb/buf_dblwr_mutex | 73438 | | 3 | 4046 | wait/synch/mutex/innodb/buf_pool_mutex | 222829 | | 3 | 4047 | wait/synch/mutex/innodb/buf_pool_mutex | 42755 | | 3 | 4048 | wait/synch/mutex/innodb/flush_list_mutex | 44767 | | 3 | 4049 | wait/synch/mutex/innodb/buf_pool_mutex | 59857 | | 3 | 4050 | wait/synch/mutex/innodb/buf_dblwr_mutex | 72432 | | 3 | 4041 | wait/synch/mutex/innodb/buf_pool_mutex | 243452 | | 17 | 2442 | wait/synch/mutex/innodb/sync_array_mutex | 74947 | | 17 | 2440 | wait/synch/mutex/innodb/sync_array_mutex | 82492 | | 17 | 2439 | wait/synch/mutex/innodb/sync_array_mutex | 458233 | | 17 | 2438 | wait/synch/mutex/innodb/log_sys_mutex | 239428 | | 17 | 2437 | wait/synch/mutex/innodb/sync_array_mutex | 85510 | | 17 | 2446 | wait/synch/mutex/innodb/sync_array_mutex | 84504 | | 17 | 2445 | wait/synch/mutex/innodb/sync_array_mutex | 77462 | | 17 | 2444 | wait/synch/mutex/innodb/log_sys_mutex | 238422 | | 17 | 2443 | wait/synch/mutex/innodb/sync_array_mutex | 83498 | | 17 | 2441 | wait/synch/mutex/innodb/log_sys_mutex | 229368 | | 19 | 7281 | wait/synch/mutex/innodb/flush_list_mutex | 43761 | +-----------+----------+------------------------------------------+------------+21 rows in set (0.00 sec)复制代码
타입의 구문만 나열되어 있으며 데모 효과에 대해서는 여기서 자세히 설명하지 않습니다. 🎜쿼리 3에 대한 프로필 컨텍스트 스위치 표시쿼리 3에 대한 프로필 CPU 표시쿼리 3에 대한 프로필 ipc 표시쿼리 3에 대한 프로필 페이지 오류 표시에 대한 프로필 소스 표시 쿼리 3쿼리 3에 대한 프로필 교환 표시프로필 표시사용하기 좋아요 , 이는 SQL 문 각 부분의 실행 시간을 확인하는 데 도움이 되며, 이는 온라인 SQL의 느린 실행 문제를 찾는 데 매우 도움이 됩니다. 하지만 공식 웹사이트에는 다음과 같은 문구가 있습니다. 🎜🎜🎜Note🎜 🎜SHOW PROFILE 및 SHOW PROFILES 문은 더 이상 사용되지 않으며 향후 MySQL 릴리스에서는 제거될 예정입니다. 🎜🎜성능 스키마로 변경;🎜🎜좋아요, 삭제라고 하면 삭제하세요. 하지만 MySQL이 show profile을 삭제하기 전에도 여전히 사용할 수 있습니다. 🎜🎜다음 버전에서는 성능 스키마를 사용할 예정이므로, 이것이 무엇인지 살펴보겠습니다! 🎜MySQL的performance schema用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。使用performance schema可以更加容易的监控MySQL。
在MySQL的5.7版本中，性能模式是默认开启的，如果想要显式的关闭的话需要修改配置文件，不能直接进行修改，会报错Variable 'performance_schema' is a read only variable。
mysql> show variables like 'performance_schema'; +--------------------+-------+| Variable_name | Value | +--------------------+-------+| performance_schema | ON | +--------------------+-------+mysql> set performance_schema=0; ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable复制代码
在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭
[mysqld] performance_schema=ON复制代码
看下performance_schema数据库有哪些功能表:
mysql> use performance_schema; mysql> show tables;复制代码
一共有87张表，分别存储着相关的信息。
可以通过show create table tablename来查看创建表的时候的表结构：
mysql> show create table threads\G
*************************** 1. row *************************** Table: threadsCreate Table: CREATE TABLE `threads` ( `THREAD_ID` bigint(20) unsigned NOT NULL, `NAME` varchar(128) NOT NULL, `TYPE` varchar(10) NOT NULL, `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL, `PROCESSLIST_USER` varchar(32) DEFAULT NULL, `PROCESSLIST_HOST` varchar(60) DEFAULT NULL, `PROCESSLIST_DB` varchar(64) DEFAULT NULL, `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL, `PROCESSLIST_TIME` bigint(20) DEFAULT NULL, `PROCESSLIST_STATE` varchar(64) DEFAULT NULL, `PROCESSLIST_INFO` longtext, `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL, `ROLE` varchar(64) DEFAULT NULL, `INSTRUMENTED` enum('YES','NO') NOT NULL, `HISTORY` enum('YES','NO') NOT NULL, `CONNECTION_TYPE` varchar(16) DEFAULT NULL, `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf81 row in set (0.00 sec)复制代码我一看到threads这个表名，还有THREAD_ID、PROCESSLIST_ID等等字段，就感觉这些比较重要啊有木有！
再来看一下setup_consumers这个表：
mysql> show create table setup_consumers;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| setup_consumers | CREATE TABLE `setup_consumers` ( `NAME` varchar(64) NOT NULL, `ENABLED` enum('YES','NO') NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)复制代码这个consumer总感觉似曾相识啊！
其实，想要搞明白后续的内容，我们需要理解两个基本概念：
performance_schema库下的表可以按照监视不同的纬度就行分组。
--语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)show tables like '%statement%';--等待事件记录表，与语句事件类型的相关记录表类似：show tables like '%wait%';--阶段事件记录表，记录语句执行的阶段事件的表show tables like '%stage%';--事务事件记录表，记录事务相关的事件的表show tables like '%transaction%';--监控文件系统层调用的表show tables like '%file%';--监视内存使用的表show tables like '%memory%';--动态对performance_schema进行配置的配置表show tables like '%setup%';复制代码
来，那一个事务相关的来瞅一眼：
mysql> show tables like '%transaction%'; +------------------------------------------------------+| Tables_in_performance_schema (%transaction%) | +------------------------------------------------------+| events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | +------------------------------------------------------+8 rows in set (0.00 sec)复制代码
数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件。
可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异)。
打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项
mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%'; Query OK, 267 rows affected (0.03 sec) Rows matched: 321 Changed: 267 Warnings: 0复制代码
打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项
mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%'; Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0复制代码
当配置完成之后可以查看当前server正在做什么。
events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件：
释义：
该信息表示线程id为3的线程正在等待buf_dblwr_mutex锁，等待时间为69917。 属性说明： id:事件来自哪个线程，事件编号是多少 event_name:表示检测到的具体的内容 source:表示这个检测代码在哪个源文件中以及行号 timer_start:表示该事件的开始时间 timer_end:表示该事件的结束时间 timer_wait:表示该事件总的花费时间 注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息。复制代码
_history表中记录每个线程应该执行完成的事件信息，但每个线程的事件信息只会记录10条，再多就会被覆盖，（下面的演示可以看到每个线程只有10条记录）。_history_long表中记录所有线程的事件信息，但总记录数量是10000，超过就会被覆盖掉mysql> mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21; +-----------+----------+------------------------------------------+------------+| thread_id | event_id | event_name | timer_wait | +-----------+----------+------------------------------------------+------------+| 3 | 4042 | wait/synch/mutex/innodb/buf_pool_mutex | 47785 | | 3 | 4043 | wait/synch/mutex/innodb/flush_list_mutex | 43761 | | 3 | 4044 | wait/synch/mutex/innodb/buf_pool_mutex | 58851 | | 3 | 4045 | wait/synch/mutex/innodb/buf_dblwr_mutex | 73438 | | 3 | 4046 | wait/synch/mutex/innodb/buf_pool_mutex | 222829 | | 3 | 4047 | wait/synch/mutex/innodb/buf_pool_mutex | 42755 | | 3 | 4048 | wait/synch/mutex/innodb/flush_list_mutex | 44767 | | 3 | 4049 | wait/synch/mutex/innodb/buf_pool_mutex | 59857 | | 3 | 4050 | wait/synch/mutex/innodb/buf_dblwr_mutex | 72432 | | 3 | 4041 | wait/synch/mutex/innodb/buf_pool_mutex | 243452 | | 17 | 2442 | wait/synch/mutex/innodb/sync_array_mutex | 74947 | | 17 | 2440 | wait/synch/mutex/innodb/sync_array_mutex | 82492 | | 17 | 2439 | wait/synch/mutex/innodb/sync_array_mutex | 458233 | | 17 | 2438 | wait/synch/mutex/innodb/log_sys_mutex | 239428 | | 17 | 2437 | wait/synch/mutex/innodb/sync_array_mutex | 85510 | | 17 | 2446 | wait/synch/mutex/innodb/sync_array_mutex | 84504 | | 17 | 2445 | wait/synch/mutex/innodb/sync_array_mutex | 77462 | | 17 | 2444 | wait/synch/mutex/innodb/log_sys_mutex | 238422 | | 17 | 2443 | wait/synch/mutex/innodb/sync_array_mutex | 83498 | | 17 | 2441 | wait/synch/mutex/innodb/log_sys_mutex | 229368 | | 19 | 7281 | wait/synch/mutex/innodb/flush_list_mutex | 43761 | +-----------+----------+------------------------------------------+------------+21 rows in set (0.00 sec)复制代码
summary表提供所有事件的汇总信息，该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来）
mysql> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10; +-----------------------------------------------+------------+| EVENT_NAME | COUNT_STAR | +-----------------------------------------------+------------+| wait/synch/mutex/innodb/buf_pool_mutex | 3357 | | wait/synch/mutex/innodb/log_sys_mutex | 3357 | | wait/synch/mutex/innodb/sync_array_mutex | 2241 | | wait/synch/mutex/innodb/flush_list_mutex | 2238 | | wait/io/file/sql/FRM | 1590 | | wait/synch/mutex/innodb/log_flush_order_mutex | 1119 | | wait/synch/mutex/innodb/log_sys_write_mutex | 1119 | | wait/synch/mutex/innodb/fil_system_mutex | 1119 | | wait/synch/mutex/innodb/dict_sys_mutex | 1119 | | wait/synch/mutex/innodb/buf_dblwr_mutex | 1119 | +-----------------------------------------------+------------+10 rows in set (0.10 sec)复制代码
instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录。例如，file_instances表列出了文件I/O操作及其关联文件名
mysql> select * from file_instances limit 10; +----------------------------------------+--------------------------------------+------------+| FILE_NAME | EVENT_NAME | OPEN_COUNT | +----------------------------------------+--------------------------------------+------------+| /usr/share/mysql/english/errmsg.sys | wait/io/file/sql/ERRMSG | 0 | | /usr/share/mysql/charsets/Index.xml | wait/io/file/mysys/charset | 0 | | /var/lib/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 | | /var/lib/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 | | /var/lib/mysql/mysql/engine_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 3 | +----------------------------------------+--------------------------------------+------------+10 rows in set (0.00 sec)复制代码
performance_schema_consumer_events_statements_current=TRUE#是否在mysql server启动时就开启events_statements_current表的记录功能(该表记录当前的语句事件信息)，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新setup_consumers配置表中的events_statements_current配置项，默认值为TRUEperformance_schema_consumer_events_statements_history=TRUE#与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUEperformance_schema_consumer_events_stages_history_long=FALSE#与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE除了statement(语句)事件之外，还支持：wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQL Server启动时一同启动，则通常需要写进my.cnf配置文件中 performance_schema_consumer_global_instrumentation=TRUE 是否在MySQL Server启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局对象计数统计和事件汇总统计信息表 ）的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新全局配置项 默认值为TRUEperformance_schema_consumer_statements_digest=TRUE是否在MySQL Server启动时就开启events_statements_summary_by_digest 表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新digest配置项 默认值为TRUEperformance_schema_consumer_thread_instrumentation=TRUE是否在MySQL Server启动时就开启 events_xxx_summary_by_yyy_by_event_name表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新线程配置项 默认值为TRUEperformance_schema_instrument[=name] 是否在MySQL Server启动时就启用某些采集器，由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持%号进行通配等，如下:# [=name]可以指定为具体的Instruments名称（但是这样如果有多个需要指定的时候，就需要使用该选项多次），也可以使用通配符，可以指定instruments相同的前缀+通配符，也可以使用%代表所有的instruments## 指定开启单个instruments--performance-schema-instrument= 'instrument_name=value'## 使用通配符指定开启多个instruments--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'## 开关所有的instruments--performance-schema-instrument= '%=ON'--performance-schema-instrument= '%=OFF'注意，这些启动选项要生效的前提是，需要设置performance_schema=ON。另外，这些启动选项虽然无法使用show variables语句查看，但我们可以通过setup_instruments和setup_consumers表查询这些选项指定的值。复制代码
show variables like '%performance_schema%'; --重要的属性解释 performance_schema=ON /* 控制performance_schema功能的开关，要使用MySQL的performance_schema，需要在mysqld启动时启用，以启用事件收集功能 该参数在5.7.x之前支持performance_schema的版本中默认关闭，5.7.x版本开始默认开启 注意：如果mysqld在初始化performance_schema时发现无法分配任何相关的内部缓冲区，则performance_schema将自动禁用，并将performance_schema设置为OFF */ performance_schema_digests_size=10000 /* 控制events_statements_summary_by_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance_schema会增加状态变量 */ performance_schema_events_statements_history_long_size=10000 /* 控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖 全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10000，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10000 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10000 */ performance_schema_events_statements_history_size=10 /* 控制events_statements_history表中单个线程（会话）的最大行数，该参数控制单个会话在events_statements_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖 全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10 除了statement(语句)事件之外，wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个参数分别进行存储限制配置，有兴趣的同学自行研究，这里不再赘述 */ performance_schema_max_digest_length=1024 /* 用于控制标准化形式的SQL语句文本在存入performance_schema时的限制长度，该变量与max_digest_length变量相关(max_digest_length变量含义请自行查阅相关资料) 全局变量，只读变量，默认值1024字节，整型值，取值范围0~1048576 */ performance_schema_max_sql_text_length=1024 /* 控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。 超出系统变量performance_schema_max_sql_text_length的部分将被丢弃，不会记录，一般情况下不需要调整该参数，除非被截断的部分与其他SQL比起来有很大差异 全局变量，只读变量，整型值，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入 降低系统变量performance_schema_max_sql_text_length值可以减少内存使用，但如果汇总的SQL中，被截断部分有较大差异，会导致没有办法再对这些有较大差异的SQL进行区分。 增加该系统变量值会增加内存使用，但对于汇总SQL来讲可以更精准地区分不同的部分。 */复制代码
基本了解了表的相关信息之后，可以通过这些表进行实际的查询操作来进行实际的分析。
--1、哪类的SQL执行最多？SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--2、哪类SQL的平均响应时间最多？SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--3、哪类SQL排序记录数最多？SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--4、哪类SQL扫描记录数最多？SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--5、哪类SQL使用临时表最多？SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--6、哪类SQL返回结果集最多？SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--7、哪个表物理IO最多？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--8、哪个表逻辑IO最多？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--9、哪个索引访问最多？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--10、哪个索引从来没有用过？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;--11、哪个等待事件消耗时间最多？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--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';--12-2、查看每个阶段的时间消耗SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;--12-3、查看每个阶段的锁等待情况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;复制代码
关于Performance Schema的东西，我们还可以参考官网继续进行详细的了解。
我们来思考一下，否可以做一个系统出来，通过查询Performance Schema的表的一些信息，并将其展示到web端，这样我们就可以进行可视化监控了？
使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。
mysql> show processlist; +----+------+-----------+--------------------+---------+------+----------+------------------+| Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------------------+---------+------+----------+------------------+| 6 | root | localhost | performance_schema | Query | 0 | starting | show processlist | +----+------+-----------+--------------------+---------+------+----------+------------------+1 row in set (0.00 sec)复制代码
属性说明：
idsession id
user操作的用户
host操作的主机
db操作的数据库
command当前状态
- sleep：线程正在等待客户端发送新的请求
- query：线程正在执行查询或正在将结果发送给客户端
- locked：在mysql的服务层，该线程正在等待表锁
- analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
- Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
- sorting result：线程正在对结果集进行排序
- sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据
info详细的sql语句
time相应命令执行时间
state命令执行状态
MySQL调优之性能监控：
show profile 使用show profile查询剖析工具，可以指定具体的typeperformance schema 使用performance schema来更加容易的监控mysqlshow processlist 使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征
更多相关免费学习推荐：mysql教程(视频)
위 내용은 MySQL 튜닝의 성능 모니터링에 대한 첫 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
