数据库调优执行计划分析指南,带你从入门到精通
- 慢查询⽇志
- 参数与默认值
- 慢查询日志的启用
- 修改配置文件
- 全局变量设置
- 参数解释
- `log_output = 'FILE/TABLE'`
- `slow_query_log = ON`
- `long_query_time = 0.001`
- 分析慢查询⽇志表
- slow_log表的基本字段
- 分析慢查询⽇志⽂件
- 返回记录集最多10条SQL
- 按照查询时间排序,包含right join的10条SQL
- 分析慢查询⽇志⽂件
- format_name
- explain_type
- explainable_stmt
- 结果输出展示
- id-语句(唯⼀标识)
- select_type(查询类型)
- table(表名)
- partitions(分区)
- type(连接类型)
- 从最优到次优的排序
- possible_keys
- key
- key_len(索引占用的空间度量)
- ref(字段与常量关系)
- rows(预期扫描行数)
- filtered(数据匹配率)
- Extra(探索深入Extra组件的奥秘待揭)
慢查询⽇志
慢查询日志(Slow Query Log)作为MySQL数据库管理系统的一项核心内置功能,其设计初衷在于监控并捕获执行时间超出预设阈值的SQL查询语句。这一机制不仅对于性能调优至关重要,还是诊断数据库瓶颈、优化查询性能不可或缺的工具。
通过启用慢查询日志,数据库管理员能够系统地追踪那些执行效率低下、耗时过长的SQL语句。这些查询往往是数据库性能问题的源头,比如由于查询条件不合理、索引缺失或失效、数据表结构设计不当等原因导致的。一旦识别出这些慢查询,管理员便能针对性地采取措施,如优化查询语句、调整索引策略、重构数据库架构等,从而显著提升数据库的整体响应速度和吞吐量。
参数与默认值
参数 | 作用 | 默认值 |
---|---|---|
log_output | 日志输出到哪儿,默认FILE,表示文件;设置成TABLE,则将日志记录到sql>mysql.slow_log中。也可设置多种格式,比如FILE,TABLE | FILE |
long_query_time | 执行时间超过这么久才记录到慢查询日志,单位秒,可使用小数表示小于秒的时间 | 10 |
log_queries_not_using_indexes | 是否要将未使用索引的SQL记录到慢查询日志中,此配置会无视long_query_time的配置。生产环境建议关闭;开发环境建议开启。 | OFF |
log_throttle_queries_not_using_indexes | 如果log_queries_not_using_indexes打开,则该参数将限制每分钟写入的、未使用索引的SQL数量。 | 0 |
min_examined_row_limit | 扫描行数至少达到这么多才记录到慢查询日志 | 0 |
log_slow_admin_statements | 是否要记录管理语句,默认关闭。管理语句包括ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE. | OFF |
slow_query_log_file | 指定慢查询日志文件路径 | path路径 |
log_slow_slave_statements | 该参数在从库上设置,决定是否记录在复制过程中超过long_query_time的SQL。如果binlog格式是row,则该参数无效 | OFF |
log_slow_extra | 当log_output=FILE时,是否要记录额外信息(MySQL 8.0.14开始提供),对log_output=TABLE的结果无影响。 | OFF |
慢查询日志的启用
慢查询日志的启用与配置相当灵活,管理员可以根据实际需要设置阈值时间(即定义何为“慢查询”),还可以指定日志文件的存储位置、大小限制及滚动策略等。这种灵活性确保了慢查询日志既不会因记录过多无关紧要的查询而占用过多磁盘空间,也不会因错过关键的性能问题而失去其价值。
修改配置文件
为了启用并配置慢查询日志等关键参数,编辑MySQL的配置文件my.cnf
,文件中的[sql>mysqld]
部分,并在此段落内精确地添加上述提及的各项参数。
[sql>mysqld]
# ...
log_output = 'FILE,TABLE';
slow_query_log = ON
long_query_time = 0.001
接下来,为了使所做的配置更改生效,您需要执行MySQL服务的重启操作:
service sql>mysqld restart
全局变量设置
此方法具备即时生效的便捷性,无需经过繁琐的重启流程即可让配置变动立即得到应用。然而,当系统或MySQL服务发生重启时,这些临时性的配置更改将不会得到持久化保存,从而导致配置信息的丢失。
set global log_output = 'FILE/TABLE';
set global slow_query_log = 'ON';
set global long_query_time = 0.001;
参数解释
log_output = 'FILE/TABLE'
log_output
用于指定慢查询日志的输出方式。当设置为'FILE,TABLE'
时,MySQL 会同时将慢查询日志记录到文件中和MySQL数据库的一个特殊表(通常是sql>mysql.slow_log
)中。
- TABLE:通过查询数据库表来快速检索和分析慢查询日志。
- FILE:保留了传统的文件日志记录方式,便于使用外部工具或脚本进行处理。
slow_query_log = ON
slow_query_log
参数用于启用或禁用慢查询日志功能。当设置为 ON
时,MySQL 会开始记录执行时间超过 long_query_time
阈值的查询语句。这对于识别和优化数据库中的性能瓶颈非常有用。
默认情况下,该参数可能设置为
OFF
,表示慢查询日志功能被禁用。
long_query_time = 0.001
long_query_time
参数定义了哪些查询被认为是“慢查询”,即它们的执行时间超过了这个阈值(以秒为单位)。上面的案例中long_query_time
被设置为 0.001
,意味着任何执行时间超过0.001秒的查询都将被记录到慢查询日志中。
注意,将
long_query_time
设置得过低可能会导致大量的查询被记录到慢查询日志中,从而增加了日志文件的体积和处理难度。
分析慢查询⽇志表
在log_output
被设定为TABLE
模式时,我们可以直接采用以下精心设计的语句来进行深入的数据分析:
sql">select * from `sql>mysql`.slow_log
SQL是以数据表的形式直接访问和操作日志输出,极大地增强了数据处理的灵活性和直观性。具体地,通过执行下述定制化语句,我们能够高效地提取、筛选、汇总及解析日志数据中的关键信息。
slow_log表的基本字段
字段名称 | 描述 |
---|---|
start_time | 查询开始的时间 |
user@host | 执行查询的用户和主机 |
query_time | 查询执行所花费的时间,以秒为单位 |
lock_time | 查询在等待锁时花费的时间,以秒为单位(如果查询被锁定) |
rows_sent | 查询返回给客户端的行数 |
rows_examined | MySQL服务器在执行查询期间检查的行数 |
db | 执行查询时使用的数据库名 |
query | 实际执行的SQL查询语句 |
thread_id | 执行查询的线程ID |
tmp_tables | 查询执行期间创建的临时表的数量 |
tmp_disk_tables | 查询执行期间在磁盘上创建的临时表的数量 |
tmp_table_sizes | 查询执行期间创建的临时表的大小(MySQL 5.6.6及以后版本) |
full_scan | 指示查询是否进行了全表扫描(MySQL 5.6.3及以后版本) |
file_sort | 指示查询是否使用了文件排序(MySQL 5.6.3及以后版本) |
file_sort_mb | 排序操作使用的内存量,以MB为单位(MySQL 5.6.3及以后版本) |
merged_passes | 排序操作合并的次数(MySQL 5.6.3及以后版本) |
分析慢查询⽇志⽂件
当log_output = FILE时,可使⽤相关的工具和接口来分析和处理慢查询日志,如sql>mysqldumpslow工具可以帮助管理员快速汇总和排序慢查询日志中的信息,便于发现最耗时的查询和最常见的性能问题。
sql>mysqldumpslow --help
Usage: sql>mysqldumpslow [ OPTS... ] [ LOGS... ]
总结MySQL慢查询日志。可选项包括
选项 | 描述 |
---|---|
--verbose 或 -v | 展示更详细的信息 |
--debug 或 -d | 开启调试模式 |
--help | 将此文本写入标准输出(即显示帮助信息) |
-s ORDER | 以哪种方式排序,默认at 。可选项包括:- al : 平均锁定时间- ar : 平均返回记录数- at : 平均查询时间- c : 访问计数- l : 锁定时间- r : 返回记录- t : 查询时间 |
-r | 将-s 的排序结果倒序 |
-t NUM | 展示最前面的NUM 条记录,即top n的意思 |
-a | 不将数字展示成N(可能指不以千分位分隔符显示),将字符串展示成’S’(具体行为可能依工具而异) |
-n NUM | 抽象名称中的数字,只展示名称中至少有NUM 位数字的部分 |
-g PATTERN | 后边可以写一个正则表达式,只有符合正则表达式的行会被展示 |
-h HOSTNAME | 慢查询日志以主机名-slow.log 的格式命名,-h 可指定读取指定主机名的慢查询日志,默认情况下是* ,读取所有的慢查询日志 |
-i NAME | MySQL Server的实例名称(如果使用了sql>mysql.server启动脚本的话) |
-l | 不将锁定时间从总时间中减去 |
返回记录集最多10条SQL
sql>mysqldumpslow -s r -t 10 /var/lib/sql>mysql/xxxx-slow.log
按照查询时间排序,包含right join的10条SQL
sql>mysqldumpslow -s t -t 10 -g “right join” /var/lib/sql>mysql/xxxx-slow.log
分析慢查询⽇志⽂件
explain
语句在数据库管理和优化中扮演着至关重要的角色,它提供了一种深入洞察SQL查询执行过程的方式,帮助开发者、数据库管理员及分析师理解查询的执行计划,从而识别潜在的瓶颈,优化查询性能,格式如下:
sql">{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}[explain_type]{explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement
format_name
format_name的取值应当严格限定在以下列出的范围内进行选取,这些范围不仅覆盖了常见的格式命名约定。
format_name: {TRADITIONAL | JSON | TREE
}
explain_type
explain_type的取值应当严格限定在以下列出的范围内进行选取,这些范围不仅覆盖了常见的格式命名约定。
sql">explain_type: {FORMAT = format_name
}
explainable_stmt
explainable_stmt的取值应当严格限定在以下列出的范围内进行选取,这些范围不仅覆盖了常见的格式命名约定。
explainable_stmt: {SELECT statement| TABLE statement| DELETE statement| INSERT statement| REPLACE statement| UPDATE statement
}
结果输出展示
字段 | format=json 时的名称 | 含义 |
---|---|---|
id | select_id | 该语句的唯一标识 |
select_type | 实际查询类型,如SIMPLE, PRIMARY等 | 查询类型 |
table | table_name | 表名 |
partitions | partitions | 匹配的分区 |
type | access_type | 联接类型(如ALL, index, range, ref等) |
possible_keys | possible_keys | 可能的索引选择 |
key | key | 实际选择的索引 |
key_len | key_length | 索引的长度 |
ref | ref | 索引的哪一列被引用了 |
rows | rows | 估计要扫描的行数 |
filtered | filtered | 表示符合查询条件的数据百分比 |
Extra | 没有(但在实际JSON输出中会有额外信息) | 附加信息 |
id-语句(唯⼀标识)
在通过explain语句分析查询执行计划时,若结果集中包含了多个ID值,则这些ID值遵循一个明确的执行优先级原则:数值较大的ID对应的操作将先于数值较小的ID执行。而对于那些共享相同ID值的行而言,它们之间的执行顺序则是严格遵循从上至下的自然顺序,即先列出的行将先于后续行执行。
select_type(查询类型)
- SIMPLE:表示最简单的查询,不涉及任何子查询或UNION操作,通常是对单个表的直接访问。
- PRIMARY:在包含UNION或子查询的查询中,PRIMARY代表最外层的查询块,即那些不是由UNION或子查询直接生成的查询部分。
- UNION:当查询中使用了UNION操作来合并多个SELECT语句的结果时,UNION类型用于标识那些通过UNION合并的查询块。
- SUBQUERY:在SELECT或WHERE子句中出现的子查询,被标记为SUBQUERY类型。这些子查询可能是嵌套在其他查询中的,用于提供额外的条件或数据。
- DERIVED:从子查询中派生的临时表(也称为派生表)。当MySQL需要对子查询的结果进行多次访问时,可能会将其转换为派生表以提高效率。
- UNION RESULT:表示UNION操作的结果集。这种类型的查询块是多个UNION成员查询执行完毕后,用于合并和去重最终结果的中间步骤。
- DEPENDENT SUBQUERY:与主查询相关的子查询,即子查询中引用了主查询中的列。这种子查询的执行可能依赖于外部查询的每一行结果。
- UNCACHEABLE SUBQUERY:表示一个无法被缓存优化的子查询。这通常是因为子查询中包含了依赖于外部查询结果或当前时间的表达式。
- MATERIALIZED:物化子查询的结果。在某些情况下,为了优化查询性能,MySQL可能会选择将子查询的结果物化(即存储在临时表中),以便后续多次快速访问。
table(表名)
在SQL查询的上下文中,用于标识当前行正在访问的数据表时,通常会直接显示表的名称。若该表在查询中被赋予了别名(Alias),则为了提升查询的可读性和简洁性,系统或查询结果将优先展示这个别名,而非表的原始名称。这一做法不仅简化了对复杂查询的理解,还使得在多个表连接(JOIN)操作中能够更清晰地追踪数据流的来源。
partitions(分区)
当执行一个查询操作时,如果该查询是针对一个已分区的表进行的,那么系统将会标识并返回当前查询所匹配到的记录所属的分区信息。
分区是数据库管理中的一种高级特性,它允许将表中的数据物理上分割成多个部分,每个部分可以独立存储、管理和查询,从而优化查询性能和数据管理效率。
type(连接类型)
连接类型(Join Types)在数据库查询中扮演着至关重要的角色,它们决定了如何结合来自不同表的数据。这些连接类型在性能上存在着显著差异,
- 内连接(INNER JOIN):性能上通常较为优越,因为它仅返回两个或多个表中匹配的行。当表之间存在明确且直接的关联时,内连接是高效获取所需数据的方式。
- 索引连接(Indexed Join):虽然不是一个直接的连接类型名称,但利用索引进行的连接操作(如哈希连接、嵌套循环连接中的索引查找)可以显著提升查询性能。通过减少需要扫描的数据量,索引连接能够加快连接操作的速度。
- 使用索引的嵌套循环连接(Indexed Nested-Loop Join):当参与连接的表之一较小,且该表上有合适的索引时,这种连接类型尤为高效。它通过在外层循环中遍历较大的表,而在内层循环中利用索引快速查找匹配项来实现。
- 哈希连接(Hash Join):适用于大数据量连接的场景,它通过构建哈希表来快速匹配连接键,从而加快连接速度。哈希连接在处理大量数据时通常比嵌套循环连接更高效。
- 归并连接(Merge Join):当两个表都已经根据连接键排序时,归并连接是一种非常高效的连接方法。它通过同时遍历两个表并比较连接键来找到匹配的行,类似于归并排序中的合并步骤。
- 全表扫描连接(Full Table Scan Join):性能上相对较差,因为它需要对至少一个表执行全表扫描以查找匹配的行。这种连接类型在表未建立索引或索引不适用于查询时较为常见。
- 笛卡尔积(Cartesian Join):也称为交叉连接,是所有连接类型中性能最差的一种,因为它不基于任何连接条件,而是简单地将第一个表的每一行与第二个表的每一行组合起来。这通常会导致巨大的结果集和极低的查询效率,除非在特定场景下故意使用。
从最优到次优的排序
-
system:此表结构独特,仅包含单一行数据,其性质类似于系统级表格。"system"标识符在此处扮演了一个恒定类型(const type)的独特角色。
-
const:当执行涉及主键或唯一索引的精确匹配查询时,系统将高效地筛选,确保结果集不会超出单条记录。此类常量(const)查询展现出了惊人的速度优势,归功于其仅需一次访问数据库即可完成数据检索的特性。
-
eq_ref:当使⽤了索引的全部组成部分,并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使⽤该类型,性能
仅次于system及const。sql">-- 多表关联查询,单⾏匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; -- 多表关联查询,联合索引,多⾏匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
-
ref:在ref查询模式下,系统仅在遵循索引的最左侧优先原则,或所使用索引既非主键亦非唯一的情况下,方会触发这一机制。尤其当索引能高效过滤,仅返回有限行数时,其性能表现尤为出色。
sql">-- 根据索引(⾮主键,⾮唯⼀索引),匹配到多⾏ SELECT * FROM ref_table WHERE key_column=expr; -- 多表关联查询,单个索引,多⾏匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; -- 多表关联查询,联合索引,多⾏匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
-
ref_or_null:在ref_or_null查询类型中,其行为与ref模式相仿,然而MySQL数据库会进一步识别并纳入包含NULL值的行进行处理。这一特性在子查询解析场景中颇为常见,有效提升了复杂查询的灵活性。
sql">SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
-
index_merge:这种类型体现了索引合并优化的应用,意味着在单一查询中,系统有效地结合了多个索引的信息,以提升查询效率和准确性。
-
unique_subquery:此查询类型与eq_ref模式相近,不同之处在于它采用了IN操作符,并要求子查询基于主键或唯一索引进行。
sql">SELECT * FROM ref_table WHERE key_column IN (SELECT primary_key FROM single_table WHERE some_expr)
-
index_subquery:与unique_subquery模式相仿,本类型同样聚焦于子查询的处理,但其区别在于采用的是非唯一性索引,为查询提供了更广泛的适用范围。
sql">SELECT * FROM ref_table WHERE key_column IN (SELECT key_column FROM single_table WHERE some_expr)
-
range:范围扫描,表示检索了指定范围的行,主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
sql">SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
-
index:全索引扫描,和ALL类似,只不过index是全盘扫描了索引的数据。当查询仅使⽤索引中的⼀部分列时,可
使⽤此类型。有两种场景会触发:- 如果索引是查询的覆盖索引,并且索引查询的数据就可以满⾜查询中所需的所有数据,则只扫描索引树。此时,explain的Extra 列的结果是Using index。index通常⽐ALL快,因为索引的⼤⼩通常⼩于表数据。
- 按索引的顺序来查找数据⾏,执⾏了全表扫描。此时,explain的Extra列的结果不会出现Uses index。
-
ALL:全表扫描,性能最差。
possible_keys
这一列的数据展示了在查询执行初期可利用的索引信息。这些索引在优化过程的起始阶段生成,旨在提供多种路径以加速数据检索。
注意,并非所有列出的索引都能在后续的优化流程中发挥实质性作用。某些索引可能因查询特性、数据分布或系统配置等因素,对提升性能贡献有限。
key
表示MySQL实际选择的索引,基于性能考量与资源优化,所选定的具体索引方案。它反映了MySQL智能决策机制的核心,确保了数据检索的高效与精准。
key_len(索引占用的空间度量)
key_len揭示了索引在存储层面的实际开销,具体体现为使用的字节数。字段的空值属性对索引大小有着微妙但重要的影响。当字段支持NULL值时,为了记录这一额外状态,每条索引记录会消耗比非空字段多一个字节的空间。
ref(字段与常量关系)
表示将哪个字段或常量和key列所使⽤的字段进⾏⽐较,与key列字段进行对比的具体对象——无论是某个字段值还是预定义的常量。当ref涉及函数调用时,对比的依据变为该函数的计算结果。
rows(预期扫描行数)
这一数值代表了MySQL系统基于统计信息预测的,为完成查询所需扫描的记录数量。直观上,较低的估计值预示着更高效的查询执行,意味着数据库引擎能以更少的磁盘I/O操作和内存消耗,快速定位并返回目标数据。因此,追求最小化预期扫描行数,成为了优化查询性能、提升响应速度的重要策略之一
filtered(数据匹配率)
这一指标生动诠释了查询条件下数据集的精炼程度,其上限为100%,象征着完全匹配的理想状态。尤为重要的是,通过将预计扫描的行数(rows)与筛选比率(filtered)相乘,我们能够精确预估出与后续表进行联接操作的有效行数。
例如,当rows设定为1000,而filtered比例为50%时,意味着仅500行数据将参与后续的联接处理,显著降低了数据处理的复杂度,提升了整体查询效率。
Extra(探索深入Extra组件的奥秘待揭)
鉴于Extra部分蕴含的信息繁复且应用场景广泛,我们计划在后续篇章中专门对其进行详尽剖析。后续,我们将聚焦于Extra字段的基础取值解读,以及如何借助这一模块洞察查询执行的隐含细节。