提供网站建设工具的品牌,如何自己开个网站平台,龙岩任做网站的哪几个比较好,北京旅游设计网站建设我们在日常开发中#xff0c;一定遇见过某些SQL执行较慢的情况#xff0c;我们俗称“慢SQL”#xff0c;如果你对系统的接口性能要求较高的话#xff0c;一定不会放过这种SQL#xff0c;肯定会想办法进行解决#xff0c;那么#xff0c;导致慢 SQL 出现的原因#xff0…我们在日常开发中一定遇见过某些SQL执行较慢的情况我们俗称“慢SQL”如果你对系统的接口性能要求较高的话一定不会放过这种SQL肯定会想办法进行解决那么导致慢 SQL 出现的原因究竟可能都有哪些呢 这是一道经典的面试题就此我们来研究一番下面我们就来好好看一下原因可能出在哪里。 本篇我们将从如下几个方面进行讨论 1.慢SQL捕获
2.执行计划分析
3.引擎参数配置分析 让我们就此开启本次慢SQL分析之旅Lets go ps: 本篇文章的讨论主要基于MySQL8.0数据库Oracle等其他数据库不在本篇讨论范围之列。 一、慢SQL捕获 追查应用服务的慢SQL首先需要追踪哪些SQL可能是慢SQL对于Java服务很多数据库中间件提供了慢SQL的追踪能力例如Alibaba Druid会将服务运行过程中的慢SQL打印到日志文件方便开发运维人员追查。 MySQL当然也提供了捕获慢查询的监控能力记录在MySQL中执行时间超过指定时间的SQL语句。 默认情况下MySQL并没有开启慢日志可以通过修改slow_query_log参数来打开慢日志。与慢日志相关的参数介绍如下 slow_query_log是否启用慢查询日志1表示开启0表示关闭默认为关闭。 slow_query_log_file指定慢查询日志位置及名称默认值为host_name-slow.log可指定绝对路径。 long_query_time慢查询执行时间阈值超过此时间会记录默认为10取值范围0~31536000单位为秒。 min_examined_row_limit对于查询扫描行数小于此参数的SQL将不会记录到慢查询日志中默认为0最大值(bit-64)为18446744073709551615。 log_output慢查询日志输出目标默认为FILE即输出到文件取值为TABLE、FILE、NONE。 log_timestamps主要是控制 error log、slow log、genera log 日志文件中的显示时区默认使用UTC时区取值为UTC、SYSTEM建议改为 SYSTEM系统时区。 log_queries_not_using_indexes是否记录所有未使用索引的查询语句默认为OFF。 log_slow_admin_statements慢速管理语句是否写入慢日志中管理语句包含 ALTER TABLE,ANALYZE TABLE, CHECK TABLE,CREATE INDEX, DROP INDEX,OPTIMIZE TABLE,REPAIR TABLE默认为OFF即不写入。 一般情况下我们只需开启慢日志记录配置下阈值时间其余参数可按默认配置。对于阈值时间可灵活调整比如说可以设置为 1s 或 3s 。 1.慢查询追踪配置方式 MySQL提供了两种配置慢查询参数的方式提供给开发者使用下面我们依次来看一下。 第一种将慢查询参数配置写入MySQL配置文件永久生效
# 慢查询日志相关配置可根据实际情况修改vim /etc/my.cnf [mysqld] slow_query_log 1slow_query_log_file /var/log/mysql/sql-slow.loglong_query_time 1log_timestamps SYSTEMlog_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# UserHost: wtopps[wtopps] localhost [] Id: 10831# Query_time: 1.609214 Lock_time: 0.003828 Rows_sent: 2050008 Rows_examined: 2150010SET timestamp1667381015;SELECT A.* FROM user A LEFT JOIN grade B ON A.id B.user_id; 对于每一组慢SQL日志内容格式如下 第一行记录的是该条SQL执行的时刻如果log_timestamps参数为UTC则改时间会显示UTC时区时间。 第二行记录的是执行该语句的用户和IP以及链接id。 第三行的几个字段含义如下 Query_time语句执行时间以秒为单位。 Lock_time获取锁的时间(以秒为单位)。 Rows_sent发送给Client端的行数。 Rows_examined服务器层检查的行数(不计算存储引擎内部的任何处理) 在8.0.14及以上版本可以打开log_slow_extra系统参数收集更多信息。 mysqlset global log_slow_extraon# Time: 2023-02-10T13:07:50.617272Z# UserHost: 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.617272ZSET timestamp1676034379;SELECT A.* FROM user A LEFT JOIN grade B ON A.id B.user_id; 可以看到当开启log_slow_extra参数后慢查询日志中出现了大量的额外信息其含义如下 Thread_id连接的标识ErrnoSQL错误号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产生的可能原因。 二、情况分析 1.为什么查询会慢 在尝试编写快速的查询之前需要清楚一点真正重要是响应时间。如果把查询看作是一个任务那么它由一系列子任务组成每个子任务都会消耗一定的时间。如果要优化查询实际上要优化其子任务要么消除其中一些子任务要么减少子任务的执行次数要么让子任务运行得更快。 MySQL在执行查询的时候有哪些子任务哪些子任务运行的速度很慢这里很难给出完整的列表通常来说查询的生命周期大致可以按照顺序来看 从客户端到服务器然后在服务器上进行解析生成执行计划执行并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理包括排序、分组等。 mysql执行过程 在完成这些任务的时候查询需要在不同的地方花费时间包括网络CPU计算生成统计信息和执行计划、锁等待互斥等待等操作尤其是向底层存储引擎检索数据的调用操作这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。 根据存储引擎不同可能还会产生大量的上下文切换以及系统调用。 在每一个消耗大量时间的查询案例中大概率会出现一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。 再次申明一点对于一个查询的全部生命周期上面列的并不完整。这里我们只是想说明了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。有了这些概念我们再一起来看看如何优化查询。 捕获具体的慢查询SQL后我们需要对可能导致慢查询的原因进行分析我们可以从如下几个角度对问题进行拆解 SQL执行计划分析 引擎参数配置分析 引擎参数配置分析网络物理机配置内存机器负载I/O 2.SQL执行计划分析 查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据但在实际业务场景中这并不常见。 大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询我们发现通过下面两个步骤来分析总是很有效 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行但有时候也可能是访问了太多的列。 确认MySQL服务器层是否在分析大量超过需要的数据行。 有些查询会请求超过实际需要的数据然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担并增加网络开销另外也会消耗应用服务器的CPU和内存资源。 因此合理的使用索引的重要性就凸显出来如果查询中的查询条件未命中索引字段MySQL引擎则只能对全量的数据进行检索再根据查询条件进行过滤筛选出目标的数据集这个过程是非常耗时且低效的。 接下来我们将逐步对SQL执行的过程进行分析拆解通过工具手段剖析慢查询的具体原因。 SQL分析三步走 3.explain执行计划分析 对于SQL执行过程分析最先登场的毫无疑问就是explain语句了explain是我们在日常开发最常使用的分析命令。其使用方式这里不再赘述一般来说95% 的慢查询问题只需要explain就可以解决了。 对于explain执行计划的分析我们需要关注最简单的衡量查询开销的两个点 扫描的行数 返回的行数 通过explain执行计划可以获得SQL在执行时预估的扫描行数以及返回行数的大概比例这在一定程度上能够说明该查询找到需要的数据的效率高不高。 但这个指标也并不是绝对的因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快内存中的行也比磁盘中的行的访问速度要快得多。 理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种情况并不多见。 例如在做一个多表的join关联查询时服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小一般在11和101之间不过有时候这个值也可能非常非常大。 比值越大则意味着查询效率越低因为引擎执行扫描出的大部分数据行都会被丢弃这也意味着需要执行更多的I/O操作因此尽可能降低扫描的行数对返回的行数的比率是我们对慢查询优化的关键所在。 一般MySQL能够使用如下三种方式应用where条件从好到坏依次为 在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的。 使用索引覆盖扫描在Extra列中出现了Using index来返回记录直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的但无须再回表查询记录。 从数据表中返回数据然后过滤不满足条件的记录在Extra列中出现Using Where。这在MySQL服务器层完成MySQL需要先从数据表读出记录然后过滤。 Extra中Using Index与Using WhereMySQL官方文档的解释如下 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) 查询是否使用了多个表(大于3张) 的join操作join表的数据量级如何是否使用了索引字段进行查询 对于多表join的复杂联合查询是可能产生慢SQL的重灾区join子表的顺序决定了扫描结果集会有多大需要结合explain进行分析判断 实际业务场景中也尽可能避免多表join操作需要在表设计阶段就做好冗余字段的考虑 where查询条件是否使用了分页查询分页深度是多大 limit10, offset100000MySQL在实际执行时会查询出100010条记录然后丢弃前100000条性能会极为糟糕 对于深分页查询优化当执行深度分页时可以带入主键ID作为查询条件执行下一页的查询时将上一页最大的主键ID作为条件id last_page_max_id 综上我们总结SQL执行时其扫描的行数决定了执行的效率而决定扫描行数的关键则是索引的命中情况与索引的质量。 Tips关于索引的一些小建议 唯一索引命名uk字段普通索引命名idx字段过长时可用首字母替代 尽量避免三张表以上的join对于多表join的情况可以视情况考虑将一个大查询拆分成多个子查询对结果集在业务层进行聚合处理。如必须要多表join的场景特别注意多连表查询的扫描行数问题以及索引的命中情况 varchar长字段建立索引需要指定索引长度根据文本区分度来决定长度 避免左模糊全模糊匹配 order by 字段放在索引最后列避免filesort 考虑利用覆盖索引来进行查询操作避免回表 性能优化目标需要为range级别以上最好是ref级别或者const最好 区分度高的列在索引最左边 避免字段类型不同造成的隐式转换导致索引失效。例如varchar和数字类型 根据大多数SQL来创建索引 对于运行较久的大表需要关注索引字段的区分度问题当索引值出现了严重倾斜时需要考虑优化拆分索引值。 4.PROFILE分析 通过使用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) 这个一个典型的效果较差的查询typeALL没有命中索引执行了全表扫描我们使用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执行耗时详情 从profile执行结果中我们可以清晰的看到一条SQL在每个执行阶段的耗时、CPU使用率、IO等指标帮助我们定位到慢查询具体执行耗时的阶段对于该条SQL执行过程中最耗时的部分是executing部分executing阶段包括了执行线程正在为SELECT读取和处理数据行并将数据发送到客户端。 因为在这个状态下发生的操作往往执行大量的磁盘读取所以它往往是在整个查询的生命周期中运行时间最长的一个阶段。 经过了对SQL语句进行explain与profile分析之后一个SQL为什么慢慢在哪里基本上可以定位出来了那么最后的手段主要是解决什么问题呢 我们将使用最终的分析工具OPTIMIZER_TRACE。 5.OPTIMIZER_TRACE分析 OPTIMIZER_TRACE是MySQL 5.6添加的新功能这个功能可以看到内部查询计划的TRACE信息也就是MySQL在执行过程中的具体决策细节从而可以知道MySQL是如何在众多索引中的挑选最合适的索引。 如果我们通过执行计划发现MySQL选择的索引并不符合我们的预期就需要根据OPTIMIZER_TRACE的信息来判断为什么会选择它是MySQL的配置原因还是SQL某些地方写的不好导致MySQL误判。 开启这个功能的方式如下
set session optimizer_traceenabledon; 在客户端执行一个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_usesrows_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_access_planrange扫描最终选择的执行计划。在该结构体中会给出执行计划的type使用的索引以及扫描行数。如果range_access_plan.type是index_roworder_intersect即index merge的话在该结构体下还会列intersect_of结构体给出index merge的具体信息。 rows_for_plan该执行计划的扫描行数 cost_for_plan该执行计划的执行代价 chosen是否选择该执行计划 OPTIMIZER_TRACE的核心是在跟踪记录TRACE的JSON树通过这棵树中的内容可以具体去分析优化器究竟做了什么事情进行了哪些选择是基于什么原因做的选择选择的结果及依据。这一系列都可以辅助验证我们的一些观点及优化更好的帮助我们对我们的数据库的实例进行调整。 由于OPTIMIZER_TRACE的内容非常复杂本文由于篇幅的关系无法在此对于每一个字段进行详细的解读感兴趣的读者可以参考MySQL官方文档对于OPTIMIZER_TRACE的解读。 三、引擎参数配置分析 上面的篇幅中我们针对SQL层面进行了可能导致慢查询的原因分析MySQL的数据最终都会存储在磁盘上因此操作系统的I/O情况也会影响MySQL的运行性能这一章节我们将从底层入手从操作系统I/O层面分析MySQL执行性能问题。 1.I/O性能分析 Linux系统查看 系统I/O情况可以使用iostat命令
[rootVM-16-14-centos ~]# iostat -x 1 -mLinux 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 %utilvda 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.07scd0 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其他指标的意义如下 r/s和w/s合并过后的读请求和写请求的每秒请求数可以当做IOPS来理解。 rMB/s和wMB/s磁盘的读写吞吐量。 rrqm/s和wrqm/s每秒合并的读请求和写请求数量。 r_await和w_await读请求和写请求的平均响应时间包含真正的处理时间和队列中的等待时间ms。 avgrq-sz平均每次设备I/O操作的数据大小 (扇区)。 avgqu-sz平均I/O队列长度。 await平均每次设备I/O操作的等待时间 (毫秒)。 scvtm计算出来的平均IO响应时间目前已经不准确不用再关注。 %util如果使用了RAID或者SSD则忽略这个指标仅在单块机械盘上准确。 一般来说评价一块IO设备忽略机械盘的情况没有评价的意义是否达到了高负载情况可以看这几个指标r/sw/srMB/swMB/sr_awaitw_awaitavgqu-sz。 2.MySQL I/O参数 MySQL涉及到IO相关的参数会比较多因此这里仅一部分经常用到的参数 innodb_io_capacity和innodb_io_capacity_max是最直接限制IOPS的指标大多数时候SSD 可以设置成16000或者更高的数值如果是云主机或者其他的共享存储设备则需要了解一下详细的IOPS上限再具体调整。 trx_commit和 sync_binlog这两个参数也放进来的原因是不同的参数组合对 IO的压力也会有区别。 通常的用法是双 1或者20二零参考官方文档的描述双 1 在每次提交事务的时候都会刷盘对IO的压力要高不少 20 则是滞后刷盘对IO的压力会较小因此写入QPS会高一些。 另外可以关注到一个细节innodb_io_capacity的描述对象是后台任务。这代表着 MySQL后台的 flushpurge操作会受到这个参数设置的限制。 吞吐量和IOPS一般情况下衡量IO系统性能最直观的指标并没有特别的提及主要原因还是判断起来很简单如果iostat的指标已经达到或者接近了实际硬件的指标比如达到了 75%那么根据业务量增长的情况及早规划硬件升级或者其他手段来分散读写压力。 常规的手段可以简单的遵循以下场景来酌情使用读多写少读写分离写多读少拆库拆表加缓存。 四、其他原因分析 1.网络抖动 目前的互联网应用服务数据库的部署与应用服务的部署不会在一台服务器上那么应用服务器与数据库服务器之间就需要通过网络通信一般情况来讲都会在同机房或同一个可用区进行部署以保证网络通信的质量。 但是难免可能会出现网络抖动的情况这种情况下对应用服务来讲可能会出现一条SQL执行较慢的情况但不是由于数据库原因导致的而是由于网络原因导致的。 发现偶现的SQL执行慢且SQL本身执行计划没有问题可以从网络的角度分析问题在服务所在的机器ping数据库服务器查看响应时间特别针对数据库服务器与业务服务器跨城市的情况如果没有拉通专线访问会很容易出现网络慢导致的问题。 2.单表数据量过大 上面我们提到了InnoDB的数据存储方式是面向主键索引进行数据存储的。InnoDB的数据表数量级超过几千万后性能会出现下降核心是由于BTree的数据结构导致的。
因此当你的数据表超过几千万量级的时候SQL执行即使全部命中的索引也有可能出现执行慢的情况。
