mysql 定时任务 性能影响_MySQL慢查询性能优化

思想

所有任务的响应时间上的性能优化，基本上都可以通过以下步骤完成:

将任务划分为多个子任务

性能剖析: 测量和分析时间花费在哪里

测量子任务所花费的时间

对结果进行统计和排序

将重要的任务排在前面

原因: 推断系统运行的方式，找出慢的原因

优化: 针对该原因进行改良

工具

Explain

OptimizerTrace

Profiling

示例数据和语句

Create Table: CREATE TABLE `record` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL COMMENT '用户',

`purse_type` int(8) NOT NULL DEFAULT '0' COMMENT '货币类型',

`order_id` varchar(32) NOT NULL DEFAULT '' COMMENT '订单id',

`order_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '操作类型',

`profit` int(11) NOT NULL DEFAULT '0' COMMENT '金额',

`source` varchar(32) NOT NULL DEFAULT '' COMMENT '业务来源',

`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态',

`excuse` varchar(32) NOT NULL DEFAULT '' COMMENT '描述',

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_purse_type_order_id` (`purse_type`,`order_id`),

KEY `index_uid_purse_order_type_status_create` (`uid`,`purse_type`,`order_type`,`status`,`create_time`),

KEY `ix_create_time` (`create_time`)

) ENGINE=InnoDB AUTO_INCREMENT=7341596 DEFAULT CHARSET=utf8mb4

语句：select sum(profit) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) AND source in (1016)

步骤

我们通过两个维度对查询进行分析：逻辑维度和微观维度，来定位性能原因

假设我们已经通过trace确认了就是数据库慢，记录要分析的数据库表结构和语句

找出慢查询语句：慢查询日志或者trace系统

分析慢查询日志的执行计划

通过explain语句的输出判断分析的执行计划是否符合预期

通过optimizer_trace语句分析执行计划

在逻辑维度上

我们按照执行计划将查询划分成多个子任务，构造连续子任务的语句，启动profiling，依次执行，记录时间，注意Buffer对执行时间的影响

作表：记录子任务和耗时以及关键指标，关键指标因任务而异

在微观维度上

通过profiling得到语句在数据库内部执行的情况

分析结果，定位原因，制定优化方案

案例

分析任务

数据库：

Create Table: CREATE TABLE `record` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL COMMENT '用户',

`purse_type` int(8) NOT NULL DEFAULT '0' COMMENT '货币类型',

`order_id` varchar(32) NOT NULL DEFAULT '' COMMENT '订单id',

`order_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '操作类型',

`profit` int(11) NOT NULL DEFAULT '0' COMMENT '金额',

`source` varchar(32) NOT NULL DEFAULT '' COMMENT '业务来源',

`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态',

`excuse` varchar(32) NOT NULL DEFAULT '' COMMENT '描述',

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_purse_type_order_id` (`purse_type`,`order_id`),

KEY `index_uid_purse_order_type_status_create` (`uid`,`purse_type`,`order_type`,`status`,`create_time`),

KEY `ix_create_time` (`create_time`)

) ENGINE=InnoDB AUTO_INCREMENT=7341596 DEFAULT CHARSET=utf8mb4

语句：select sum(profit) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) AND source in (1016)

执行计划

id: 1

select_type: SIMPLE

table: record

partitions: NULL

type: ref

possible_keys: uniq_purse_type_order_id,index_uid_purse_order_type_status_create

key: index_uid_purse_order_type_status_create

key_len: 8

ref: const,const

rows: 5230

filtered: 3.00

Extra: Using index condition; Using where

逻辑任务划分、测量及绘表

使用index_uid_purse_order_type_status_create索引中的(uid,puser_type)进行扫描

通过索引条件下推过滤掉status字段不合格的记录

回表查询，过滤掉source不合格的记录

sum出结果

T1: select count(1) as total from record where uid=1800138860 AND purse_type=1;

T2: select count(1) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100);

T3: select count(1) as total from record where uid=1800138860 AND - purse_type=1 AND status in (10,20,100) AND source in (1016)

T4: select sum(profit) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) AND source in (1016);

+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------+

| 1 | 0.00993100 | select sum(profit) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) AND source in (1016) |

| 2 | 0.00958900 | select count(1) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) AND source in (1016) |

| 3 | 0.00187000 | select count(1) as total from record where uid=1800138860 AND purse_type=1 AND status in (10,20,100) |

| 4 | 0.00153425 | select count(1) as total from record where uid=1800138860 AND purse_type=1 |

+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------+

子任务

耗时

关键指标

索引扫

1.5ms

rows=5230

索引过滤

0.3ms

rows=5229

回表

7.7ms

rows=365

sum

0.4ms

rows=265

微观执行情况

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+

| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+

| starting | 0.000066 | 0.001000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |

| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 852 |

| Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5819 |

| init | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 |

| System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 338 |

| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |

| statistics | 0.000111 | 0.000000 | 0.000000 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 378 |

| preparing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 486 |

| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |

| Sending data | 0.009601 | 0.011998 | 0.002000 | 152 | 3 | 0 | 64 | 0 | 0 | 0 | 3 | 0 | exec | sql_executor.cc | 202 |

| end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |

| query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5385 |

| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5440 |

| freeing items | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6053 |

| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2090 |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+

定位优化

定位原因：通过逻辑维度可以看到，主要时间消耗在回表上，通过对回表关键指标的分析，可以知道：5229行数据产生了大量的随机IO，导致回表子任务慢，所以优化的方向自然是优化索引，减少回表行数。另外，通过对explain和optimizer_trace产生的执行计划进行分析，也可以验证这结论，另外，status和purse_type字段区分度降低。通过微观维度可以看到，在sending data阶段产生了64次随机IO。

优化方案

给索引(uid,purse_type,order_type,status,create)增加source，可以使得回表行数从5229减少为365，可以减少回表

给索引(uid,purse_type,order_type,status,create)增加source和profit，可以通过索引覆盖不再回表

增加新索引(uid,source)，通过(uid=1800138860 AND source in (1016))可以知道，回表行数为356行较少且索引较短，原有索引区分度较低，无其他用途可删除。

附录：Optimizer_Trace字段

join_prepare: 准备阶段，负责基本的语句重写

join_optimization: 优化阶段

condition_processing: where/on/having条件语句转化优化

substitute_generated_columns: 替换虚拟生成列

table_dependencies: 表之间的依赖关系

ref_optimizer_key_uses: 所有可能用于ref类型的字段，如果使用了组合索引的多个部分则会在ref_optimizer_key_uses下列出多个字段

rows_estimation: 用于估算需要扫描的记录数

table_scan: 全表扫描的代价

potential_range_indexes: 列出表中所有的索引并分析其是否可用

setup_range_conditions: 如果有可下推的条件，则带条件考虑范围查询

group_index_range: 当使用了GROUP BY或DISTINCT时，是否有合适的索引可用

analyzing_range_alternatives: 分析各个索引

range_scan_alternatives: range扫描分析的使用成本和属性

analyzing_roworder_intersect: 分析是否使用了索引合并(index merge)

chosen_range_access_summary: 汇总前一阶段的中间结果确认表访问最后的方案

range_access_plan: range扫描表时最终选择的执行计划

considered_execution_plans: 确定整个语句的最终执行计划

best_access_path: 最佳的访问路径

condition_filtering_pct: 在server中应用where后的比例，估值

attaching_conditions_to_tables:

attached_conditions_computation: 最终的扫描行数

attached_conditions_summary: 最终的执行计划

finalizing_table_conditions: 最终的、优化后的表条件

refine_plan: 改善执行计划

join_execution: 执行过程