MySQL 8.0如果只能优化一个SQL,选哪个?(译文)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 回答这个问题并不容易。像很多问题一样,最好的回答是“这要看情况...”! 但是,让我们试着给你所有必要的信息来提供最准确的答案。另外,仅仅修复一个SQL可能不够,查找一个有问题的SQL可能会找到多个有问题的SQL。

回答这个问题并不容易。像很多问题一样,最好的回答是“这要看情况...”!  但是,让我们试着给你所有必要的信息来提供最准确的答案。另外,仅仅修复一个SQL可能不够,查找一个有问题的SQL可能会找到多个有问题的SQL。

01

最慢的SQL


第一个要优化的候选项是执行时间最长的SQL。为了找到它,我们将使用sys模式与来自performance_schema的视图events_statements_summary_by_digest进行连接(参见:https://lefred.be/content/mysql-8-0-digest-query-samples-in-performance_schema/)。  让我们看看sys模式为我们提供了哪些与我们这项任务相关的内容:





> show tables like 'statements_with%';+---------------------------------------------+| Tables_in_sys (statements_with%)            |+---------------------------------------------+| statements_with_errors_or_warnings          || statements_with_full_table_scans            || statements_with_runtimes_in_95th_percentile || statements_with_sorting                     || statements_with_temp_tables                 |+---------------------------------------------+

然后,我们将使用statements_with_runtimes_in_95th_percentile来查找最慢的SQL语句。然而,我们将使用带有原始数据的视图版本(不是人类可读的格式),以便能够按照我们想要的方式对结果进行排序。sys模式视图的原始数据版本以x$开头:





SELECT schema_name, sys.format_time(total_latency) tot_lat,          exec_count, sys.format_time(total_latency/exec_count) latency_per_call,        query_sample_text   FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1  JOIN performance_schema.events_statements_summary_by_digest AS t2     ON t2.digest=t1.digest  WHERE schema_name NOT in ('performance_schema', 'sys') ORDER BY (total_latency/exec_count) desc LIMIT 1\G*************************** 1. row ***************************      schema_name: library          tot_lat: 857.29 ms       exec_count: 1 latency_per_call: 857.29 msquery_sample_text: INSERT INTO `books` (`doc`) VALUES ('{\"_id\": \"00005d44289d000000000000007d\", \"title\": \"lucky luke, tome 27 : l alibi\", \"isbn10\": \"2884710086\", \"isbn13\": \"978-2884710084\", \"langue\": \"français\", \"relié\": \"48 pages\", \"authors\": [\"Guylouis (Auteur)\", \"Morris (Illustrations)\"], \"editeur\": \"lucky comics (21 décembre 1999)\", \"collection\": \"lucky luke\", \"couverture\": \" data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDABQODxIPDRQSEBIXFRQYHjIhHhwcHj0sLiQySUBMS0dARkVQWnNiUFVtVkVGZIhlbXd7gYKBTmCNl4x9lnN+gXz/2wBDARUXFx4aHjshITt8U0ZTfHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHz/wAARCAEfANwDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBA...1 row in set (0.2838 sec)

优化这个语句很困难,因为它是一个简单的插入,并且只运行了一次。由于磁盘响应时间的原因,Insert可能会慢一些(写磁盘的方式是完全持久)。拥有太多的索引也会增加响应时间,这就是我建议您查看这两个sys模式视图的原因:

  • schema_redundant_indexes
  • schema_unused_indexes

由于有query_sample_text字段,我们可以使用explain来生成这个SQL的执行计划。

02

全表扫描的SQL


另外一类我建议需要优化的SQL是执行全表扫描的SQL:




SELECT schema_name, sum_rows_examined, (sum_rows_examined/exec_count) avg_rows_call,       sys.format_time(total_latency) tot_lat, exec_count,       sys.format_time(total_latency/exec_count) AS latency_per_call,       query_sample_text   FROM sys.x$statements_with_full_table_scans AS t1  JOIN performance_schema.events_statements_summary_by_digest AS t2     ON t2.digest=t1.digest  WHERE schema_name NOT in ('performance_schema', 'sys') ORDER BY (total_latency/exec_count) desc LIMIT 1\G*************************** 1. row ***************************       schema_name: wp_lefred sum_rows_examined: 268075     avg_rows_call: 3277.0419           tot_lat: 31.31 s        exec_count: 124  latency_per_call: 252.47 ms query_sample_text: SELECT count(*) as mytotal                 FROM wp_posts                 WHERE (post_content LIKE '%youtube.com/%'                    OR post_content LIKE '%youtu.be/%')                 AND post_status = 'publish' 1 row in set (0.0264 sec)

我们可以看到这个查询执行了124次,总执行时间为31.31秒,也就是说每次调用执行252.47毫秒。我们还可以看到,该查询检查了超过268k行,这意味着平均而言,这些全表扫描每个查询检查3277条记录。  这是一个非常值得优化的SQL。

03

使用临时表的SQL


创建临时表对于数据库应用来说也不是最优的,如果您有一些很慢的SQL,您应该已经在前面的查询中识别出它们。但是,如果您想专门寻找它们,sys 模式可以帮助您捕捉它们:




SELECT schema_name, sys.format_time(total_latency) tot_lat, exec_count,        sys.format_time(total_latency/exec_count) latency_per_call, query_sample_text   FROM sys.x$statements_with_temp_tables AS t1  JOIN  performance_schema.events_statements_summary_by_digest AS t2    ON t2.digest=t1.digest  WHERE schema_name NOT in ('performance_schema', 'sys') AND disk_tmp_tables=1ORDER BY 2 desc,(total_latency/exec_count) desc LIMIT 1\G

幸运的是,我的系统里没有。  

04

结束语


查询优化并不是DBA工作中最令人兴奋的部分……但却是必须要做的;-)。你现在已经有了一个简单的方法来找到从哪里开始,祝你好运!别忘了,如果你需要任何帮助,你可以随时加入MySQL社区Slack频道!(https://lefred.be/mysql-community-on-slack/

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
14天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
14天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
1月前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
190 11
|
19天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
SQL 关系型数据库 索引
SQL优化常用方法53
分离表和索引
1382 0
|
SQL
SQL优化常用方法51
使用显式的游标(CURSORs)
1155 0
|
SQL
SQL优化常用方法49
优化GROUP BY
1152 0
|
SQL 索引
SQL优化常用方法47
CBO下使用更具选择性的索引
1112 0
|
SQL 索引
SQL优化常用方法46
连接多个扫描
1206 0

推荐镜像

更多