[.com原稿]QueryProfiling，查询分析技术，是MySQL数据库提供的一种诊断SQL性能的方法，也被认为是分析数据库整体性能的有效技术。图片来自Pexels。开启Profiling后，用户可以查看当前会话中SQL执行耗时、系统时间、CPU用户时间的分布情况，以及流程中涉及到的关键函数在源代码文件中的位置。由于单个大中型应用在单位时间内可以完成多次查询，因此QueryProfiling是数据库优化调整的重要组成部分。问题。在实际工作场景中，如果不使用可靠的查询分析技术，相关技术人员往往很难定位到数据库中出现性能瓶颈和性能不佳问题的根源。作为MySQL的一个分支，MariaDBServer内置的工具为我们提供了queryprofiling相关的queryprofiling技术。我们以MariaDBServer内置的两个工具SlowQueryLog（慢查询日志）和PerformanceSchema（性能策略模型）为例，深入探讨查询分析技术的价值。MariaDB与MySQL首先让我们回顾一下MariaDB和MySQL这两个产品之间的亲缘关系。早在2010年Oracle宣布收购Sun的那一天，MySQL之父Michael“Monty”Widenius就衍生出MySQL并推出了MariaDB，此后吸引了大批MySQL开发者为其效力。如今，MariaDB已经成为MySQL中增长最快的分支。与MySQL本身相比，它具有更丰富的功能和更优越的性能。MariaDB并不是一个孤立的分支，它是基于对应的MySQL版本而存在的。比如MariaDB5.1.53是在MySQL5.1.53的基础上，修复了之前的bug，增加了存储引擎、新功能等，并在性能上做了相应的提升。SlowQueryLog（慢查询日志）MariaDB和MySQL都有SlowQueryLog（慢查询日志）的功能。此日志包含被认为非常缓慢且可能有问题的查询。这里的“慢”查询定义为运行时间比[long_query_time]全局系统变量的值（默认为10秒）更长的查询语句。值得一提的是，文件记录允许“微秒”，而表记录不允许，所以这里的时间单位是“秒”。通过全局系统变量配置慢查询日志除了上面提到的[long_query_time]全局系统变量外，还有一些其他的变量用来决定SlowQueryLog（慢查询日志）的行为状态。默认情况下，慢查询日志是禁用的，要启用它，您需要将[slow_query_log]系统变量值设置为1。此外，“log_output”服务器系统变量决定了输出的写入方式，该变量值也可以设置为禁用。默认情况下，日志允许写入文件，但也可以写入表。[log_output]服务器系统变量的有效值为[TABLE]、“FILE”或“NONE”。文件的默认名称是[host_name-slow.log]，也可以通过[–slow_query_log_file=file_name]选项设置，这里使用的表是MySQL系统数据库中的[slow_log]表。建议这些变量最好设置在“my.cnf”或“mariadb.cnf”配置文件中，这些配置文件通常存放在Linux的“/etc/mysql/”目录下。如果是Windows系统，存放在Windows系统目录下（一般是C:\Windows\System）。在配置文件中进行以下设置：启用慢查询日志：slow_query_log=1设置以秒/微秒为单位定义慢查询的时间：long_query_time=5提供慢查询日志文件的名称：slow_query_log_file=/var/log/mysql/slow-query.log需要记录不使用索引的查询语句：log_queries_not_using_indexes以上设置会在服务器重启后生效。查看慢查询日志（slowquerylog）已经写入文件的慢查询日志可以用任何文本编辑器打开查看。下面是一个慢查询日志的例子：通过文本编辑器查看慢查询日志看似很方便，但是随着日志内容（数据量）的增长，很有可能会丢失内容。即显示不完整。这是因为文本编辑器本身无法承受越来越大的日志容量，导致日志中的部分内容存在漏解析的风险。为了避免这种情况，MariaDB为我们提供了mysqldumpslow工具，可以通过汇总信息来简化流程，从而更可靠有效地展示日志内容。“mysqldumpslow”可执行文件与MariaDB捆绑在一起，因此只需将日志路径传递给它即可通过命令行显示。从下面的demo可以看出，“mysqldumpslow”呈现的日志内容更具可读性，也支持分组显示。“mysqldumpslow”命令可以通过指定不同的参数来自定义输出格式。以下示例将显示按平均查询时间排序的前5个查询：[mysqldumpslow-t5-sat/var/log/mysql/localhost-slow.log]slow_loglist如果您不熟悉上面日志中显示的内容日志，可以借助slow_log表来帮助理解。下面是日志中各个字段的详细说明：下图是slow_log表SELECTALL的示例结果：也可以通过slow_log表模拟linux的“tail-100log-slow.log”命令来列出最近的Query记录（最近100条查询），如下图：为了方便以后频繁调用，我们也可以创建一个存储过程（如SHOW_LATEST_SLOW_QUERIES），以及要显示的查询数量可以通过输入参数传递给这个存储过程。这样，当我们需要列出指定数量的查询记录时，就不需要每次都重复同一条SELECT语句。测试SlowQueryLog（慢查询日志）为了在生产环境中更有效的获取我们想要的慢查询日志信息，通常，我们需要进行一些设置，比如指定哪些查询必须写入SlowQueryLog（慢查询日志）查询日志）。如上所述，当启用日志记录时，那些运行时间比[long_query_time]全局系统变量的值更长的查询将被记录在慢查询日志或slow_log表中，具体取决于log_output变量的值。除了指定[long_query_time]时间，我们还可以根据不同的需求，通过select语句指定相应的变量时间。这个操作需要和sleep()函数配合使用，它（根据传入的duration参数值N）会暂停当前查询N秒，然后返回0，如果sleep()函数被中断，它将返回1。如下所示，假设没有为[long_query_time]全局系统变量指定值，默认值为10秒。因此，[SELECTSLEEP(11);]这条select语句会记录在慢日志中：通过PerformanceSchema进行查询分析我们可以通过另一个服务器性能工具PerformanceSchema来监控服务器性能。PerformanceSchema在MariaDB5.5中引入，并作为存储引擎实现；因此，PerformanceSchema可以在MariaDB的存储引擎列表中找到。图中的“PerformanceSchema”功能默认是关闭的，我们可以通过以下设置来一一启用：①在my.cnf或my.ini文件的[mysqld]部分添加如下行：performance_schema=onNotes是的，“性能模式”不能在运行时激活，它必须在服务器启动时通过配置文件进行设置。PerformanceSchema存储引擎包含一个名为performance_schema的数据库，该数据库又由许多表组成，可以使用常规SQL语句查询各种性能信息。②消费者数据设置为了收集数据，我们需要设置收集哪些消费者触发的数据。这些设置可以在服务器启动时或运行时进行。使用以下语句在运行时设置和检测所需数据：UPDATEperformance_schema.setup_consumersSETENABLED='YES';UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES';启用/禁用WHERENAME对应的查询语句，通过设置ENABLED设置为“NO”来禁用检测。以下内容将为配置文件中的所有阶段启用所有检测：通过更新setup_instruments表确保语句和阶段检测已启用：UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES'WHERENAMELIKE'%statement/%';UPDATEperformance_schema。setup_instrumentsSETENABLED='YES',TIMED='YES'WHERENAMELIKE'%stage/%';启用events_statements_*和events_stages_*用户：UPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAMELIKE'%events_statements_%';UPDATEREperformance_schema.setup_consumersSETENABLE'%events_stages_%';一旦您缩小了感兴趣的区域，有两种方法可以对其进行监控：在摘要视图中查看原始数据，以全面了解实例使用情况。快照数据并计算随时间变化的增量，以了解事件的变化率。我们以查看原始汇总数据为例：运行待分析的语句：通过查询“events_statements_history_long”表确定该语句的EVENT_ID。此步骤类似于运行SHOWPROFILES来识别Query_ID。以下查询将产生类似于SHOWPROFILES的输出：查询“events_stages_history_long”表以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个NESTING_EVENT_ID列，其中包含父语句的EVENT_ID。Monyog监控工具现在我们知道SlowQueryLog（慢查询日志）专门用来记录运行时间过长被认为有问题的查询语句，即运行时间超过long_query_time全局系统变量值的查询语句。另一方面，PerformanceSchema是一个存储引擎，可用于在摘要视图中查看原始数据和过程中随时间变化的性能状态。这两种工具都有自己的优势。例如，SlowQueryLog易于使用，可以使用任何文本编辑器查看。PerformanceSchema允许我们使用常规的SQL语句来查询一系列表以获得各种性能信息。但是，两者都不可避免地会产生大量的数据信息，造成冗余，增加各自数据处理的负担。幸运的是，Monyog监控工具可以有效地为我们缓解这个问题。不仅如此，作为一款专业的监控工具，它还能给我们带来巨大的价值。Monyog是一款优秀的MySQL监控工具，可以实时监控MySQL服务器，查看MySQL服务器的运行状态，支持查询分析功能，帮助用户掌握服务器的运行状态，查看图表详细查询任意时间点绘制的信息图表。Monyog不仅是一个实时监控工具，还具备RDSOS和基于文件的日志监控能力，包括常规查询、慢查询和错误日志在一个视图中，还可以通过查看CPU利用率等RDSOS指标CloudWatchAPI、RAM利用率等。因为在MariaDB中，慢查询日志默认是禁用的。必须将slow_query_log全局系统变量设置为1才能启用它。还有一些其他系统变量的应用程序设置，例如：以秒/微秒为单位设置定义慢查询时间是否写入文件或表提供慢查询日志文件的名称来记录不使用的查询Monyog中的SlowQueryLog（慢查询日志）设置中的indexes可以直接通过“ServerSettingsdialog”对话框的“ADVANCED”选项卡配置以上所有设置。步骤如下：单击服务器图标单击服务器摘要框上的省略号将弹出“编辑服务器”窗口单击高级单击MySQL查询日志项MySQL查询日志项的高级选项卡包含普通查询的设置，缓慢的查询和错误日志。ServerSettings对话框（服务器设置对话框）允许我们将SlowQueryLog（慢查询日志）的设置应用到当前服务器，或者与当前服务器具有相同标签的其他服务器。最后点击“Save”按钮关闭对话框并保存SlowQueryLog（慢查询日志）设置。Monyog监控图表①DashboardMetricsDBA可以通过Dashboard显示的一组图表轻松了解所有MySQL服务器的安全性、可用性和性能状态。Monyog自带默认的Dashboard“Performancemetrics”性能指标，DBA也可以为一台或多台服务器指定数据库和操作系统指标，并创建一套自己的专属图表。例如，查询性能指标包括“QueriesExecuted”、“Statements”和“QueryCacheEfficiency”。Dashboard上显示的所有图表都可以导出为PDF/JPG/PNG格式：②查看MySQL日志详细信息MonyogMonitors页面显示了服务器参数和指标的详细显示。点击“MONITORGROUP”下的“MySQLLogs”项，会显示被监控服务器对应的“常规查询”、“慢查询”（红框标注）和错误日志的详细信息。SlowQuery慢查询信息包括：慢日志——是否启用？（是/否）最慢的查询执行时间，以秒为单位。慢查询的数量是否记录了那些不使用索引的查询？(Yes/No)③Trend与原始数据相比，价值图用图形来展示大量的数据以及数据不同部分之间的相关性，更简洁、易懂、可读性更强。趋势值图是一种用于显示数据随时间变化趋势的图表。由于数据波动，单点测量可能不准确，从而产生误差。因此，展示数据随时间变化的趋势，可以让我们更有效地获取实际绩效，并根据既定目标，有针对性地监控实际绩效状态。下图是主服务器的趋势图示例：上图中的“SERVERS”图例列出了SQL日志中的所有服务器。每个服务器图例都有自己的颜色，以便在图中轻松识别。由于当前图中只展示了主服务器的趋势数据，其他对应服务器的趋势值没有出现在图中，所以是灰色的。通过点击服务器图例，可以自由切换显示的服务器数据趋势。④显示特定时间范围内的趋势值在上面的趋势图中，只显示了某个时间段内某台服务器上的所有趋势数据。在MonyogProfessional、Enterprise和Ultimate版本中，我们还可以通过TIMEFRAME下拉列表中的“History”选项指定具体的时间范围。可选择的时间范围包含多个时间区间，如“今天”、“昨天”、“最近2天”，也可以自定义范围，设置起止字段；单击任何自定义范围字段将显示日历，用于选择确切的日期时间。下图是特定时间范围内各服务器慢查询数量的趋势值：⑤查询分析器在“查询分析器”选项卡中，选择需要的MySQL服务器和需要分析的日志类型（包括慢查询日志）),单击分析按钮开始分析。几秒钟后，分析结果显示如下，页面上半部分包含基于总时间的“TopQueries”，而下半部分显示使用结果分页的所有查询：The“TopQueries”basedonTotal时间部分显示最热门的查询，以便最慢的查询可以显示在顶部。包括：查询语句。COUNT：这条语句在日志中出现了多少次。总时间：执行查询所需的时间，格式为hh:mm:ss:ms。AverageLatency：平均查询执行时间，格式为hh:mm:ss:ms。USER@HOST：执行查询的用户和主机。每个语句都显示为查询数据顶部的条形图，因此每个查询都有唯一的颜色。每个查询的总执行时间从左到右显示，最慢的在最左边。条形图对于快速评估每个慢速查询语句之间的比较很有用。在上图中，我们可以看到最慢的查询比所有其他慢查询时间的总和慢几个数量级。点击某一行会显示对应慢查询的详细信息，比如第一次和最后一次执行查询的时间点，查询耗费的最大时间：⑥过滤器设置“Query”部分Query查询面板为我们提供了更完整的查询列表分析，除了可以通过分页导航遍历所有查询外，还可以自定义过滤条件，将显示列表的内容缩小到我们感兴趣的范围内。过滤条件有四个选项：Containing：包含。不含：不含。Matchingregex：匹配正则表达式。不匹配正则表达式：不匹配正则表达式。例如，过滤以限制结果以匹配正则表达式“sakila*”语句：通过单击标题，按任何列排序，箭头显示排序顺序（即升序、降序）：通过单击标题，按任何列排序，箭头显示排序顺序（升序、降序）：⑦导出为CSV点击查询面板上的“导出为CSV”，将查询数据保存为“.csv”文件：保存的CSV文件可以用EXCEL导出打开预览：汇总查询分析是分析数据库整体性能的有效技术。在本文中，该技术使用了MariaDB服务器的内置工具：SlowQueryLog和PerformanceSchema。慢查询日志通过设置long_query_time全局系统变量的值来跟踪记录运行超时和出现问题的查询语句。性能模式是一个存储引擎。Performance_schema数据库由多个表组成。我们可以使用常规的SQL语句来查询这些表，以获得更广泛的性能信息。但是，以上两个工具都会产生大量的数据，工作繁琐。Monyog的引入可以为我们解决这样的问题。使用Monyog监控MariaDB慢查询日志和性能模式是最有效的方法之一。作者：罗小罗简介：英国计算机专业TOP10，计算机科学与技术硕士，曾就职于汇丰银行、摩根大通、惠普、交通银行、阿里等国内外知名企业。涉及的项目领域主要包括：互联网金融、电子商务、教育、医疗等。目前就职于某世界500强企业，作为测试开发团队的负责人，带领团队搭建并持续优化自动化测试框架，开发自动化测试辅助工具；擅长领域：单元/接口/性能/安全/自动化测试/CD/CI/DevOps；个人持续研究领域：自动化测试模型/数据分析/算法/机器学习等。编辑：陶家龙合作网站请注明原作者及来源为.com]
