本文字数:17525字
预计阅读时间:44分钟
SQL
执行较慢的情况,我们俗称“慢SQL
”,如果你对系统的接口性能要求较高的话,一定不会放过这种SQL
,肯定会想办法进行解决,那么,导致慢 SQL 出现的原因,究竟可能都有哪些呢?1.慢SQL
捕获
2.执行计划分析
3.引擎参数配置分析
SQL
分析之旅,Let's go!ps: 本篇文章的讨论,主要基于MySQL8.0数据库,Oracle等其他数据库不在本篇讨论范围之列。
SQL
,首先需要追踪哪些SQL
可能是慢SQL
,对于Java
服务,很多数据库中间件提供了慢SQL
的追踪能力,例如Alibaba Druid
,会将服务运行过程中的慢SQL
打印到日志文件,方便开发运维人员追查。MySQL
当然也提供了捕获慢查询的监控能力,记录在MySQL
中执行时间超过指定时间的SQL
语句。MySQL
并没有开启慢日志,可以通过修改slow_query_log
参数来打开慢日志。与慢日志相关的参数介绍如下:host_name-slow.log
,可指定绝对路径。SQL
,将不会记录到慢查询日志中,默认为0,最大值(bit-64)为18446744073709551615。FILE
,即输出到文件,取值为TABLE
、FILE
、NONE
。error log
、slow log
、genera log
日志文件中的显示时区,默认使用UTC
时区,取值为UTC
、SYSTEM
,建议改为 SYSTEM
系统时区。OFF
。ALTER TABLE
,ANALYZE TABLE
, CHECK TABLE
,CREATE INDEX
, DROP INDEX
,OPTIMIZE TABLE
,REPAIR TABLE
,默认为OFF
即不写入。MySQL
提供了两种配置慢查询参数的方式,提供给开发者使用,下面我们依次来看一下。MySQL
配置文件,永久生效:# 慢查询日志相关配置,可根据实际情况修改
vim /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/sql-slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE
MySQL Server
中临时开启慢查询功能,当MySQL Server
重启时,配置修改则全部失效并恢复原状:SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/sql-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/VM-16-14-centos-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)
SQL
,来看下在慢日志中的体现。# Time: 2022-11-02T09:23:37.004885Z
# User@Host: wtopps[wtopps] @ localhost [] Id: 10831
# Query_time: 1.609214 Lock_time: 0.003828 Rows_sent: 2050008 Rows_examined: 2150010
SET timestamp=1667381015;
SELECT A.* FROM `user` A LEFT JOIN grade B ON A.`id` = B.`user_id`;
SQL
,日志内容格式如下:SQL
执行的时刻(如果log_timestamps
参数为UTC
,则改时间会显示UTC
时区时间)IP
以及链接id
Client
端的行数。log_slow_extra
系统参数,收集更多信息。mysql>set global log_slow_extra=on
# Time: 2023-02-10T13:07:50.617272Z
# User@Host: wtopps[wtopps] @ [111.197.236.164] Id: 19187
# Query_time: 91.511261 Lock_time: 0.000124 Rows_sent: 2050008 Rows_examined: 2150010 Thread_id: 19187 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 58061725 Read_first: 2 Read_last: 0 Read_key: 2 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 2150012 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2023-02-10T13:06:19.106011Z End: 2023-02-10T13:07:50.617272Z
SET timestamp=1676034379;
SELECT A.* FROM `user` A LEFT JOIN grade B ON A.`id` = B.`user_id`;
log_slow_extra
参数后,慢查询日志中出现了大量的额外信息,其含义如下:Thread_id:连接的标识;
Errno:SQL错误号,0表示没有错误;
Killed:语句终止的错误号,0表示正常终止;
Bytes_received/sent:收到和发送的字节数;
Read_first:Handler_read_first的值,代表读取索引中第一个条目的次数。反映查询全索引扫描的次数。
Read_last:读取索引最后一个key的次数;
Read_key:基于key读取行的请求数,较大说明使用正确的索引
Read_next:按顺序取下一行数据的次数,索引范围查找和索引扫描时该值会增大;
Read_prev:按顺序读取上一行的请求数,order by desc查询较优时该值较大;
Read_rnd:按固定位置读取行的请求数,大量的回表、没有索引的连接和对结果集排序时会增加;
Read_rnd_next:读取数据文件下一行的次数,大量表扫描、未创建或合理使用索引时会增加;
Sort_range_count:使用范围完成的排序次数;
Sort_rows:排序的行数;
Sort_scan_count:通过扫描表完成的排序次数;
Sort_merge_passes:排序算法合并的次数,如该值较大考虑增加sort_buffer_size的值
Created_tmp_disk_tables:创建内部磁盘临时表的数量;
Created_tmp_tables:创建内部临时表的数量;
Start/End:语句开始和结束时间
Tips: 在MariaDB中,可以开启log_slow_verbosity参数,可以更加详尽的打印出慢SQL的执行细节,该参数在MySQL8.0版本中并未支持,读者感兴趣可以自行查阅相关信息。
SQL
,接下来,则要具体分析慢SQL
产生的可能原因。MySQL
在执行查询的时候有哪些子任务,哪些子任务运行的速度很慢?这里很难给出完整的列表,通常来说,查询的生命周期大致可以按照顺序来看:CPU
计算,生成统计信息和执行计划、锁等待(互斥等待)等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU
操作和内存不足时导致的I/O
操作上消耗时间。SQL
后,我们需要对可能导致慢查询的原因进行分析,我们可以从如下几个角度,对问题进行拆解:SQL
执行计划分析I/O
)MySQL
服务器层是否在分析大量超过需要的数据行。MySQL
服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU
和内存资源。MySQL
引擎则只能对全量的数据进行检索,再根据查询条件进行过滤,筛选出目标的数据集,这个过程是非常耗时且低效的。SQL
执行的过程进行分析拆解,通过工具手段剖析慢查询的具体原因。SQL
执行过程分析,最先登场的毫无疑问就是explain
语句了,explain
是我们在日常开发最常使用的分析命令。其使用方式,这里不再赘述,一般来说,95% 的慢查询问题只需要explain
就可以解决了。explain
执行计划的分析,我们需要关注最简单的衡量查询开销的两个点:explain
执行计划可以获得SQL
在执行时预估的扫描行数以及返回行数的大概比例,这在一定程度上能够说明该查询找到需要的数据的效率高不高。join
关联查询时,服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小,一般在1:1和10:1之间,不过有时候这个值也可能非常非常大。MySQL
能够使用如下三种方式应用where
条件,从好到坏依次为:where
条件来过滤不匹配的记录。这是在存储引擎层完成的。Extra
列中出现了Using index
)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL
服务器层完成的,但无须再回表查询记录。Extra
列中出现Using Where
)。这在MySQL
服务器层完成,MySQL
需要先从数据表读出记录然后过滤。Extra中Using Index与Using Where,MySQL官方文档的解释如下: Using Index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. Using Where A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
explain
判断导致查询慢的原因,判断依据可以根据如下几点:where
查询条件中的字段,是否是索引字段,索引字段是否满足了最左匹配原则where
查询条件中是否对索引字段使用了函数处理where
查询条件中是否存在like %字段%
情况like%%
的全模糊匹配,会使得索引失效,如需使用like
,请使用like字段%
select *
的查询,该表的字段数量为多少select *
是效率低下的选择,实际业务中很少有情况会需要全部字段的情况,根据需要查询特定的字段是非常必要的where
查询条件中是否使用or
,如果使用了,or
的字段是否是主键或者索引字段or
与in
不存在性能差距,对于非索引字段,or
的性能会低于in
In many database servers, IN() is just a synonym for multiple OR clauses,
because the two are logically equivalent. Not so in MySQL,
which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list.
This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)
join
操作,join
表的数据量级如何,是否使用了索引字段进行查询join
的复杂联合查询,是可能产生慢SQL
的重灾区,join
子表的顺序决定了扫描结果集会有多大,需要结合explain
进行分析判断where
查询条件是否使用了分页查询,分页深度是多大limit10
, offset100000
,MySQL
在实际执行时,会查询出100010条记录,然后丢弃前100000条,性能会极为的糟糕id > last_page_max_id
SQL
执行时其扫描的行数决定了执行的效率,而决定扫描行数的关键,则是索引的命中情况与索引的质量。Tips:关于索引的一些小建议
1、唯一索引命名uk字段,普通索引命名idx字段,过长时可用首字母替代
2、尽量避免三张表以上的join,对于多表join的情况,可以视情况考虑将一个大查询拆分成多个子查询,对结果集在业务层进行聚合处理。如必须要多表join的场景,特别注意多连表查询的扫描行数问题以及索引的命中情况。
3、varchar长字段建立索引,需要指定索引长度,根据文本区分度来决定长度。
4、避免左模糊,全模糊匹配。
5、order by 字段放在索引最后列,避免filesort
6、考虑利用覆盖索引来进行查询操作,避免回表
7、性能优化目标,需要为range级别以上,最好是ref级别,或者const最好。
8、区分度高的列在索引最左边。
9、避免字段类型不同造成的隐式转换,导致索引失效。例如:varchar和数字类型
10、根据大多数SQL来创建索引。
11、对于运行较久的大表,需要关注索引字段的区分度问题,当索引值出现了严重倾斜时,需要考虑优化拆分索引值。
explain
分析SQL
的执行计划,我们可以看到SQL
执行过程中是否使用索引,使用了哪些索引,索引扫描的行数等,但MySQL
的慢查询,并不一定慢在有没有索引,SQL
的执行环节中任意一环出了问题都会表现为查询变慢,所以即使执行过程命中了索引,explain
的结果也很完美,但是还是慢,怎么办?profile
工具来帮忙了,这个命令可以详细的列出SQL
语句在每一个步骤消耗的时间,前提(缺点)是先执行一遍语句。PROFILE
默认是关闭的,所以需要在client
端先打开,操作如下:set session profiling = 1;
profile
的队列,保证想要查看的profile
结果还保存着,因此可以用如下操作来增加profile
的队列大小:set session profiling_history_size = 50;
profile
分析如何使用,我们先执行下面的SQL
:mysql> explain select * from user where name = '小六' and code = 300000003;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2043040 | 1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
type=ALL
,没有命中索引,执行了全表扫描,我们使用profile
分析一下各阶段的执行时间:mysql> show profiles;
+----------+------------+---------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------+
| 1 | 0.55695825 | select * from user where name = '小六' and code = 300000003 |
+----------+------------+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile block io,cpu,memory,source for query 1;
profile
执行结果中,我们可以清晰的看到一条SQL在每个执行阶段的耗时、CPU
使用率、IO
等指标,帮助我们定位到慢查询具体执行耗时的阶段,对于该条SQL
,执行过程中最耗时的部分是executing
部分,executing
阶段包括了执行线程正在为SELECT
读取和处理数据行,并将数据发送到客户端。因为在这个状态下发生的操作往往执行大量的磁盘读取,所以它往往是在整个查询的生命周期中运行时间最长的一个阶段。SQL
语句进行explain
与profile
分析之后,一个SQL
为什么慢,慢在哪里基本上可以定位出来了,那么最后的手段主要是解决什么问题呢?OPTIMIZER_TRACE
。OPTIMIZER_TRACE
是MySQL 5.6
添加的新功能,这个功能可以看到内部查询计划的TRACE
信息,也就是MySQL
在执行过程中的具体决策细节,从而可以知道MySQL
是如何在众多索引中的挑选最合适的索引。MySQL
选择的索引并不符合我们的预期,就需要根据OPTIMIZER_TRACE
的信息来判断为什么会选择它,是MySQL
的配置原因,还是SQL
某些地方写的不好导致MySQL
误判。set session optimizer_trace='enabled=on';
EXPLAIN
语句,生成一个执行计划,然后在information_chema.optimizer_trace
的表里面查找这一条语句对应的信息:mysql> select * from information_schema.optimizer_trace;
| explain select * from user where age = 21 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`code` AS `code` from `user` where (`user`.`age` = 21)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`user`.`age` = 21)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(21, `user`.`age`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(21, `user`.`age`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(21, `user`.`age`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`user`",
"field": "age",
"equals": "21",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`user`",
"range_analysis": {
"table_scan": {
"rows": 2043040,
"cost": 205676
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_age",
"usable": true,
"key_parts": [
"age",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_age",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_age",
"ranges": [
"21 <= age <= 21"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"in_memory": 0.788627,
"rows": 1,
"cost": 0.61,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_age",
"rows": 1,
"ranges": [
"21 <= age <= 21"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_age",
"rows": 1,
"cost": 0.35,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_age"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 0.35,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`user`.`age` = 21)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "(`user`.`age` = 21)"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`user`",
"original_table_condition": "(`user`.`age` = 21)",
"final_table_condition ": null
}
]
},
{
"refine_plan": [
{
"table": "`user`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
1 row in set (0.02 sec)
JSON
格式,所以推荐把结果转存到其他地方,然后用JSON
的转换工具来辅助查看,如果要看索引的选择情况,就重点关注这个JSON
的ref_optimizer_key_uses
,rows_estimation
及之后的部分,这里会展示索引选择相关的信息,截取一部分结果作为示例:{
"ref_optimizer_key_uses": [
{
"table": "`user`",
"field": "age",
"equals": "21",
"null_rejecting": true
}
]
}
......
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_age",
"rows": 1,
"ranges": [
"21 <= age <= 21"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
chosen_range_access_summary
部分的含义是在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary
阶段汇总前一阶段的中间结果确认最后的方案。range
扫描最终选择的执行计划。在该结构体中会给出执行计划的type
,使用的索引以及扫描行数。如果range_access_plan.type
是index_roworder_intersect
(即index merge
)的话,在该结构体下还会列intersect_of
结构体给出index merge
的具体信息。OPTIMIZER_TRACE
的核心是在跟踪记录TRACE
的JSON
树,通过这棵树中的内容可以具体去分析优化器究竟做了什么事情,进行了哪些选择,是基于什么原因做的选择,选择的结果及依据。这一系列都可以辅助验证我们的一些观点及优化,更好的帮助我们对我们的数据库的实例进行调整。OPTIMIZER_TRACE
的内容非常复杂,本文由于篇幅的关系,无法在此对于每一个字段进行详细的解读,感兴趣的读者,可以参考MySQL
官方文档对于OPTIMIZER_TRACE
的解读。SQL
层面进行了可能导致慢查询的原因分析,MySQL
的数据最终都会存储在磁盘上,因此操作系统的I/O
情况也会影响MySQL
的运行性能,这一章节我们将从底层入手,从操作系统I/O
层面分析MySQL
执行性能问题。Linux
系统查看 系统I/O
情况,可以使用iostat
命令:[root@VM-16-14-centos ~]# iostat -x 1 -m
Linux 3.10.0-1160.11.1.el7.x86_64 (VM-16-14-centos) 12/21/2022 _x86_64_ (1 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.82 0.00 0.67 0.07 0.00 98.44
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 1.67 0.04 2.49 0.00 0.02 17.55 0.01 2.34 3.74 2.31 0.29 0.07
scd0 0.00 0.00 0.00 0.00 0.00 0.00 7.10 0.00 0.89 0.89 0.00 0.88 0.00
avg-cpu
自然就是CPU
相关的指标,判断IO
问题时可以关注%iowait
,其他指标的意义如下:IOPS
来理解。ms
)。I/O
操作的数据大小 (扇区)。I/O
队列长度。I/O
操作的等待时间 (毫秒)。IO
响应时间,目前已经不准确,不用再关注。RAID
或者SSD
,则忽略这个指标,仅在单块机械盘上准确。IO
设备(忽略机械盘的情况,没有评价的意义)是否达到了高负载情况,可以看这几个指标:r/s
,w/s
,rMB/s
,wMB/s
,r_await
,w_await
,avgqu-sz
。MySQL
涉及到IO
相关的参数会比较多,因此这里仅一部分经常用到的参数:0:日志缓存区将每隔一秒写到日志文件中,并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。 1:每次事务提交时RDS for MySQL都会把日志缓存区的数据写入日志文件中,并且刷新到磁盘中,该模式为系统默认。 2:每次事务提交时RDS for MySQL都会把日志缓存区的数据写入日志文件中,但是并不会同时刷新到磁盘上。该模式下,MySQL会每秒执行一次刷新磁盘操作。 | ||
0 :存储引擎不进行binlog的刷新到磁盘,而由操作系统的文件系统控制缓存刷新。 1:每提交一次事务,存储引擎调用文件系统的sync操作进行一次缓存的刷新,这种方式最安全,但性能较低。 n:在每N次binlog日志文件写入后与磁盘同步,存储引擎调用文件系统的sync操作进行一次缓存的刷新。 |
innodb_io_capacity
和innodb_io_capacity_max
是最直接限制IOPS
的指标,大多数时候,SSD
可以设置成16000或者更高的数值,如果是云主机或者其他的共享存储设备,则需要了解一下详细的IOPS
上限再具体调整。trx_commit
和 sync_binlog
这两个参数也放进来的原因是不同的参数组合对 IO
的压力也会有区别。 IO
的压力要高不少;IO
的压力会较小,因此写入QPS
会高一些。innodb_io_capacity
的描述对象是:后台任务。这代表着 MySQL
后台的 flush
,purge
操作会受到这个参数设置的限制。IOPS
,一般情况下衡量IO
系统性能最直观的指标,并没有特别的提及,主要原因还是判断起来很简单:如果iostat
的指标已经达到或者接近了实际硬件的指标(比如达到了 75%),那么根据业务量增长的情况及早规划硬件升级或者其他的手段来分散读写压力。SQL
执行较慢的情况,但不是由于数据库原因导致的,而是由于网络原因导致的。SQL
执行慢,且SQL
本身执行计划没有问题,可以从网络的角度分析问题,在服务所在的机器ping
数据库服务器,查看响应时间,特别针对数据库服务器与业务服务器跨城市的情况,如果没有拉通专线访问,会很容易出现网络慢导致的问题。InnoDB
的数据存储方式是面向主键索引进行数据存储的。InnoDB
的数据表数量级超过几千万后,性能会出现下降,核心是由于B+Tree
的数据结构导致的。SQL
执行即使全部命中的索引,也有可能出现执行慢的情况。sharing-JDBC
、MyCat
等,可以根据业务的实际情况进行选择,这里就不过多的进行赘述。SQL
执行较慢可能有哪些原因导致的?进行了研究,总结一下,大部分的慢查询其实均由SQL
书写不当导致的,通过explain
命令结合实际业务场景分析,可以解决绝大多数的慢查询问题,对于一些疑难杂症SQL
,使用MySQL
强大的SQL
分析工具,也可以找出真正的问题原因。DBA
的分析手段,检查MySQL
运行性能情况,保证数据库服务的稳定运行。1.MySQL官方文档The Slow Query Log: