【MySQL数据库】第三章解读：服务器性能剖析（上）

前言：

保持空杯精神，使用性能剖析，专注于测量服务器的时间花费在哪里，思考1、如何确认服务器是否达到了性能最佳状态，2、某条语句为什么不够快，诊断被用户描述为“停顿、堆积、卡死”的某些间歇性疑难故障；

接下来将介绍一些工具、技巧优化整机性能、优化单条语句执行速度，诊断 解决那些很难观察到的问题，展示如何测量系统并生成剖析报告、如何分析系统的堆栈；3.1简介性能：为完成某件任务所需要的时间度量，in other words 性能即响应时间

吞吐量：单位时间内的查询数据（性能定义的倒数）

第一步：弄清楚时间都去哪了，在哪消耗了时间

如果通测量没有找到答案，测量方式错了或不够完善，只测量需要优化的活动

不要在错误的时间启动或停止测试，测量的是聚合后的信息而不是目标活动本身；需要定位和优化子任务

原则：无法测量便无法有效地优化3.1.1通过性能剖析进行优化性能剖析：测量、分析时间花费在哪里的主要方法

1、测量任务所花费的时间；2、对结果统计、排序（重要前排）

可将相似任务分组汇总，通过性能剖析报告获需要的结果；报告会列出all任务，每行记录一个任务：

任务名、执行时间、消耗时间、平均执行时间，执行占全部时间的百分比；按照任务的消耗时间降序排序；性能剖析类型：基于执行时间的分析：什么任务的执行时间最长

基于等待的分析：判断任务在什么地方呗阻塞的时间最长3.1.2理解性能剖析性能剖析中缺失但是重要的信息：

1、值得优化的查询

占总响应时间比重很小的查询不值得优化；成本大于收益、停止优化

2、异常情况

没有显式要优化的也要优化，如执行次数少但每次都特别慢的任务

3、未知的未知

丢失时间：任务总时间与实际测量到的时间的差，即使没有发现也要注意这类问题存在的可能性

4、被掩藏的细节

无法显示all响应时间的分布，更多信息、直方图、百分比、标准差、偏差指数

5、无法再更高层次的堆栈中进行交互式 分析3.2对应用程序进行性能剖析：自上而下性能瓶颈的影响因素：

1、外部资源，调用外部web服务或搜索引擎

2、应用需要处理大量数据，分析一个超大的xml文件

3、循环中执行昂贵的操作：滥用正则

4、使用低效的算法：暴力搜索算法

建议：新的项目中应考虑包含性能剖析的代码3.2.1测量PHP应用程序：空3.3剖析MySQL查询3.3.1剖析服务器负载铺获MySQL查询到日志文件：

1、慢查询日志：开销低、精度高，大的磁盘空间，长期开启 注意部署日志轮转工具，只在收集负载样本期间开启即可，5.1后微秒级别；

2、通用日志，查询请求到服务器时进行记录，不包含响应时间和执行计划分析查询日志自顶向下，先生成剖析报告（pt-query-digest），查看特别关注的部分3.3.2剖析单条查询思考为什么花费这么长时间、如何去优化使用SHOW PROFILE:MySQL5.1后查看： show variables like "%pro%";【源】

默认禁用，开启：set profiling=1；然后在服务器执行语句（关闭 set profiling=off;）

语法：SHOW PROFILE [type [, type] ... ][FOR QUERY n][LIMIT row_count [OFFSET offset]]type:ALL--显示所有的开销信息| BLOCK IO--显示块IO相关开销| CONTEXT SWITCHES--上下文切换相关开销| CPU--显示CPU相关开销信息| IPC--显示发送和接收相关开销信息| MEMORY--显示内存相关开销信息| PAGE FAULTS--显示页面错误相关开销信息| SOURCE--显示和Source_function，Source_file，Source_line相关的开销信息| SWAPS--显示交换次数相关开销的信息实质是这些开销信息被记录到information_schema.profiling表show profiles；查看show profile for query 2; 获取指定查询的开销（第二条查询开销明细）show profile cpu for query 2 ;查看特定部分的开销，如下为CPU部分的开销 show profile block io,cpu for query 2;同时查看不同资源开销使用SHOW STATUS:计数器全局show global status、基于某个连接会话级别，作用域要注意

计数器显示活动的频繁程度，常用：句柄计数器、临时文件、表计数器

会创建临时表，通过句柄操作（引用、指针？）访问此临时表，影响show status结果中对应的数字使用慢查询日志：【源】【源】将MySQL中响应时间超过阈值long_query_time的语句记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件），默认是10s，需要手动开启

查看：

（1）slow_query_log的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。

（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。

（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。

（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启，开启后使用full index scan的sql也会被记录到慢查询日志。//上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf查看输出格式：文件？表show variables like ‘%log_output%’;开启通用日志查询： set global general_log=on;关闭通用日志查询： set globalgeneral_log=off;设置通用日志输出为表方式： set globallog_output=’TABLE’;设置通用日志输出为文件方式： set globallog_output=’FILE’;设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;查询慢查询语句的个数：show global status like ‘%slow%’;日志部分内容简介：哪条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。利用自带的慢查询日志分析工具：mysqldumpslowperl mysqldumpslows ct 10 slow-query.log

-s 表示按何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；-t 表示top的意思，后面跟着的数据表示返回前面多少条；-g 后面可以写正则表达式匹配，大小写不敏感。使用Performance Schema:【源】【源】监视MySQL服务器，收集性能参数，且表的存储引擎PERFORMANCE_SCHEMA，低耗能

本地服务器，表是内存表，表内容在服务器启动时重新填充，关闭时丢弃，更改不会被复制或写入二进制日志

特性：

性能方案配置可被动态的执行SQL修改，立即影响到数据收集

监控服务事件：事件是服务做并被感知到的任何事，时间信息可被收集

数据库性能方案，提供对运行时数据库服务进行内部检查的方式，关注性能数据

特定于一个数据库服务，数据库表关联到数据服务，修改不会被备份也不写进二进制日志

存储引擎用“感知点”收集事件数据，且存储在performance_schema数据库，可通过select语句进行查询补充：数据库初始安装有三个基本库mysql

包含权限配置，事件，存储引擎状态，主从信息，日志，时区信息，用户权限配置等

information_schema

对数据库元数据的抽象分析，由此提供了SQL语句方式来查询数据库运行时状态，每次对information_schema的查询都产生对metadata的互斥访问，影响其他数据库的访问性能。

performance_schema

内存型数据库，使用performance_schema 存储引擎，通过事件机制将mysql服务的运行时状态采集并存储在performace_schema数据库。注意，两个单词之间用下划线连接时，表示performance_schema是一个数据库；用空格分开时，表示一个数据库性能方案，也表示一个存储引擎。

相关文章：

【MySQL数据库】第三章解读：服务器性能剖析 （下）

【MySQL数据库】第二章解读：MySQL基准测试以上就是【MySQL数据库】第三章解读：服务器性能剖析（上）的详细内容，更多请关注小潘博客其它相关文章！