MySQL慢查询性能优化

作者: 一剑光寒十九洲 | 来源:发表于2021-02-23 17:03 被阅读0次

思想

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

将任务划分为多个子任务
性能剖析: 测量和分析时间花费在哪里
- 测量子任务所花费的时间
- 对结果进行统计和排序
- 将重要的任务排在前面
原因: 推断系统运行的方式，找出慢的原因
优化: 针对该原因进行改良

工具

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: 执行过程

MySQL慢查询性能优化

思想

工具

示例数据和语句

步骤

案例

分析任务

执行计划

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

微观执行情况

定位优化

优化方案

附录：Optimizer_Trace字段

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读