MySQL--SQL语句优化--步骤

本是古典何须时尚 2023-07-22 04:57 77阅读 0赞

首发网址：[MySQL--SQL语句优化--步骤\_IT利刃出鞘的博客-CSDN博客][MySQL--SQL_--_IT_-CSDN]

# 其他网址 #

[SQL优化（一）、sql优化一般步骤\_数据库\_小浪与艳花-CSDN博客][SQL_sql_-CSDN]

# 概要步骤 #

sql优化一般步骤概要：

1.  通过 show status 命令了解各种sql的执行频率
2.  定位执行效率较低的sql语句
3.  通过explain分析低效sql的执行计划
4.  通过 show profile 分析sql
5.  通过trace分析 优化器 如何选择执行计划
6.  确定问题并采取相应的优化措施

# show status 了解sql的执行频率 #

mysql客户端连接成功后，通过show\[session|global\] status命令，可以查看服务器的状态，如果不加默认为session（session级为当前连接的统计结果，global级为自数据库上次启动到现在的统计结果）。  
示例: show status like 'Com\_%';

![70][]

com\_xx表示每个xx语句的执行次数，通常着重看curd操作次数（这些参数适用于所有存储引擎）；

Com\_insert：执行select操作的次数，一次查询只累加1；  
Com\_select：执行insert操作的次数，批量插入的insert操作只累加一次；  
Com\_delete：执行update操作次数，提交和回滚均会累加；  
Com\_update：执行delete操作次数。

针对innodb存储引擎的表操作，累加的算法。比如：show status like 'Innodb\_rows\_%';

![70 1][]

Innodb\_rows\_deleted ：执行delete操作删除的行数；  
Innodb\_rows\_inserted：执行insert操作插入的行数；  
Innodb\_rows\_read ：select查询返回的函数；  
Innodb\_rows\_updated： update操作更新的函数。  
通过截图，可以了解到当前数据库是以插入和查询为主，以及各种类型操作的执行比例。  
对于事务型的应用，通过com\_commit 和com\_rollback可以了解事务提交和回滚的情况，对于回滚特别频繁的操作，可能意味着代码编写有问题。

以下操作用户了解数据库的基本情况：  
show status like ‘connections’;-- 试图连接mysql数据库的次数  
show status like ‘uptime’;-- 服务器工作时间  
show status like ‘slow\_queries’; – 慢查询的次数

# 定位执行效率较低的sql语句 #

通过**慢查询日志**定位哪些sql执行效率低下，用–log-slow-queries\[=file\_name\]选项启动时，mysqld写一个包含所有执行时间超过long\_query\_time 秒的sql语句的日志文件，具体可参看后面的日志管理部分。

慢查询日志在查询结束以后才记录，所以在应用反正执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用**show processlist**命令查看当前mysql在进行的线程，包括线程的状态，是否锁表等，可以实时查看sql的执行情况，同时对一些锁表操作进行优化。

# 通过explain分析低效sql的执行计划 #

追查到效率低的sql后，可以通过explain或desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序，eg:统计某个email为租赁电影拷贝所支付的总金额，需要关联客户表customer和付款表payment,并且对付款金额amount字段做求和操作，相应sql的执行计划如下：

explain select sum(amount) from customer a ,payment b where 1=1 and a.customer_id=b.customer_id and email ='JANE.BENNETT@SAKILACUSTOMER.ORG'\G

# 通过 show profile 分析sql #

show profile可以在sql优化时告诉我们时间主要浪费在哪了。从5.0.37版本开始增加了对show profiles 和show profile的语句支持。通过having\_profiling参数，可以看到sql是否支持profile。

mysql> select @@have_profiling;
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    1 row in set, 1 warning (0.00 sec)

默认profiling是关闭的，可以通过set语句在session级别开启profiling:

mysql>  select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    1 row in set, 1 warning (0.00 sec)

可以直接set其值为1

mysql> set profiling=1;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

通过profile我们可以大致了解sql执行的过程。例如myisam表有表元数据的缓存（比如行数，即count(\*)值），那么对一个myisam表的count(\*)是不需要消耗太多资源的，而对于innodb来说，就没有这种元数据缓存，count(\*)执行的较慢。下面来做个试验验证下。  
首先，在一个innodb引擎的付款表payment上，执行一个count(\*)查询：

mysql> select count(*) from payment;
    +----------+
    | count(*) |
    +----------+
    |    16049 |
    +----------+
    1 row in set (0.00 sec)

执行完毕后，通过show profiles语句，看到当前sql的query id为1：

通过show profile for query 1;语句能够看到执行过程中线程的每个状态和消耗的时间：

mysql> show profile for query 1;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000082 |
    | checking permissions | 0.000008 |
    | Opening tables       | 0.000022 |
    | init                 | 0.000017 |
    | System lock          | 0.000008 |
    | optimizing           | 0.000006 |
    | statistics           | 0.000014 |
    | preparing            | 0.000012 |
    | executing            | 0.000003 |
    | Sending data         | 0.003496 |
    | end                  | 0.000007 |
    | query end            | 0.000006 |
    | closing tables       | 0.000010 |
    | freeing items        | 0.000011 |
    | cleaning up          | 0.000025 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)

状态值为sending data表示mysql线程开始访问数据行并把结果返回给客户端之前，mysql线程要做大良的磁盘读取操作，所以会导致在整个查询中耗时最长的状态。  
通过执行show profile for query,可以看到在执行count(\*)操作的过程中，时间耗费在状态值为sending data 上，通过查询 information\_schema.profiling表并按照时间做个desc排序：

mysql> select state,sum(duration) as total_r,round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=@query_id),2) as pct_r,count(*) as calls,sum(duration)/count(*) as "R/Call" from information_schema.profiling where query_id=@query_id group by state order by  total_r desc;
    +----------------------+----------+-------+-------+--------------+
    | state                | total_r  | pct_r | calls | R/Call       |
    +----------------------+----------+-------+-------+--------------+
    | Sending data         | 0.003496 | 93.80 |     1 | 0.0034960000 |
    | starting             | 0.000082 |  2.20 |     1 | 0.0000820000 |
    | cleaning up          | 0.000025 |  0.67 |     1 | 0.0000250000 |
    | Opening tables       | 0.000022 |  0.59 |     1 | 0.0000220000 |
    | init                 | 0.000017 |  0.46 |     1 | 0.0000170000 |
    | statistics           | 0.000014 |  0.38 |     1 | 0.0000140000 |
    | preparing            | 0.000012 |  0.32 |     1 | 0.0000120000 |
    | freeing items        | 0.000011 |  0.30 |     1 | 0.0000110000 |
    | closing tables       | 0.000010 |  0.27 |     1 | 0.0000100000 |
    | System lock          | 0.000008 |  0.21 |     1 | 0.0000080000 |
    | checking permissions | 0.000008 |  0.21 |     1 | 0.0000080000 |
    | end                  | 0.000007 |  0.19 |     1 | 0.0000070000 |
    | query end            | 0.000006 |  0.16 |     1 | 0.0000060000 |
    | optimizing           | 0.000006 |  0.16 |     1 | 0.0000060000 |
    | executing            | 0.000003 |  0.08 |     1 | 0.0000030000 |
    +----------------------+----------+-------+-------+--------------+
    15 rows in set (0.00 sec)

获取到最消耗时间的线程状态后，mysql进一步选择all,cpu,block io,context switch,page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间，例如，选择查看cpu的耗费时间：

mysql> show profile cpu for query 1;
    +----------------------+----------+----------+------------+
    | Status               | Duration | CPU_user | CPU_system |
    +----------------------+----------+----------+------------+
    | starting             | 0.000065 | 0.000027 |   0.000028 |
    | checking permissions | 0.000007 | 0.000003 |   0.000004 |
    | Opening tables       | 0.000021 | 0.000011 |   0.000011 |
    | init                 | 0.000014 | 0.000007 |   0.000007 |
    | System lock          | 0.000009 | 0.000004 |   0.000005 |
    | optimizing           | 0.000007 | 0.000003 |   0.000003 |
    | statistics           | 0.000013 | 0.000007 |   0.000007 |
    | preparing            | 0.000012 | 0.000005 |   0.000006 |
    | executing            | 0.000003 | 0.000002 |   0.000002 |
    | Sending data         | 0.003683 | 0.003684 |   0.000000 |
    | end                  | 0.000007 | 0.000006 |   0.000000 |
    | query end            | 0.000006 | 0.000006 |   0.000000 |
    | closing tables       | 0.000009 | 0.000009 |   0.000000 |
    | freeing items        | 0.000010 | 0.000010 |   0.000000 |
    | cleaning up          | 0.000014 | 0.000014 |   0.000000 |
    +----------------------+----------+----------+------------+
    15 rows in set, 1 warning (0.00 sec)

可想而知，sending data状态值大的原因为时间主要耗费在cpu的操作上了，对比myisam表的count(\*)操作，也创建一个同样表结构的myisam表，数据量也完全一致。

mysql> create table payment_myisam like payment;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> alter table payment_myisam engine=myisam;
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> insert into payment_myisam select * from payment;
    Query OK, 16049 rows affected (0.08 sec)
    Records: 16049  Duplicates: 0  Warnings: 0

执行count(\*),显示profile:

mysql> select count(*) from payment_myisam;
    +----------+
    | count(*) |
    +----------+
    |    16049 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> show profiles;
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------+
    | Query_ID | Duration   | Query                                                                                                                                |
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------+
    |        1 | 0.00016550 | select @@have_profiling                                                                                                              |
    |        2 | 0.00387925 | select count(*) from payment                                                                                                         |
    |        3 | 0.00005900 | show profile context switch for query 2                                                                                              |
    |        4 | 0.00007175 | create table payment_myisam lkike payment                                                                                            |
    |        5 | 0.04542150 | create table payment_myisam like payment                                                                                             |
    |        6 | 0.03682200 | alter table payment_myisam engine=myisam                                                                                             |
    |        7 | 0.08007525 | insert into payment_myisam select * from payment                                                                                     |
    |        8 | 0.00005425 | mysql> create table payment_myisam like payment                                                                                      |
    |        9 | 0.00005275 | Query OK, 0 rows affected (0.04 sec)
    
    mysql> alter table payment_myisam engine=myisam                                                |
    |       10 | 0.00005300 | Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> insert into payment_myisam select * from payment |
    |       11 | 0.00006350 | Query OK, 16049 rows affected (0.08 sec)
    Records: 16049  Duplicates: 0  Warnings: 0                                                  |
    |       12 | 0.00016450 | select count(*) from payment_myisam                                                                                                  |
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------+
    12 rows in set, 1 warning (0.00 sec)
    
    mysql> show profile for query 12;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000063 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000020 |
    | init                 | 0.000014 |
    | System lock          | 0.000009 |
    | optimizing           | 0.000008 |
    | executing            | 0.000010 |
    | end                  | 0.000004 |
    | query end            | 0.000004 |
    | closing tables       | 0.000009 |
    | freeing items        | 0.000008 |
    | cleaning up          | 0.000012 |
    +----------------------+----------+
    12 rows in set, 1 warning (0.00 sec)

由上面两种引擎所建表的count(\*)操作后，执行profile可看出，innodb引擎的表由sending data状态，存在访问数据即磁盘读取的过程，时间主要耗费在cpu上，而myisam引擎的表在executing之后就结束查询，意味着不需要去访问数据。  
如果对mysql源码感兴趣，可以通过show profile source for query查看sql解析执行过程中的每个步骤对应的源码文件、函数名称、具体源文件行数：

# 通过trace分析优化器如何选择执行计划 #

mysql5.6是通过trace文件进一步告诉我们优化器是如何选择执行计划的。（即mysql5.6提供了对sql的跟踪trace文件，从而得知优化器为何选择a执行计划而不选择b执行计划，有助于我们理解优化器的行为。）

首先打开trace,设置格式为json,设置trace最大能够使用内存的大小，避免解析过程中因为默认内存过小不能完全显示：

mysql> set optimizer_trace="enabled=on" ,end_markers_in_json=on;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set optimizer_trace_max_mem_size=1000000;
    Query OK, 0 rows affected (0.00 sec)

然后执行下想做追踪的sql语句，eg:租赁表中rental的库存编号inventory\_id为4466的电影拷贝 ，出租日期rental\_date 在2005-05-25 4:00:00~5:00:00范围内的出租记录，最后通过检查语句select \* from information\_schema.optimizer\_trace就可以知道mysql如何执行sql的。

mysql> select rental_id from rental where 1=1 and rental_date>='2005-05-25 04:00:00' and rental_date <='2005-05-25 05:00:00' and inventory_id=4466;
    +-----------+
    | rental_id |
    +-----------+
    |        39 |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> select * from information_schema.optimizer_trace\G
    *************************** 1. row ***************************
                                QUERY: select rental_id from rental where 1=1 and rental_date>='2005-05-25 04:00:00' and rental_date <='2005-05-25 05:00:00' and inventory_id=4466
                                TRACE: {
      "steps": [
        {
          "join_preparation": {
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `rental`.`rental_id` AS `rental_id` from `rental` where ((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": {
            "select#": 1,
            "steps": [
              {
                "condition_processing": {
                  "condition": "WHERE",
                  "original_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))",
                  "steps": [
                    {
                      "transformation": "equality_propagation",
                      "resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
                    },
                    {
                      "transformation": "constant_propagation",
                      "resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
                    },
                    {
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "((`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
                    }
                  ] /* steps */
                } /* condition_processing */
              },
              {
                "table_dependencies": [
                  {
                    "table": "`rental`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ] /* depends_on_map_bits */
                  }
                ] /* table_dependencies */
              },
              {
                "ref_optimizer_key_uses": [
                  {
                    "table": "`rental`",
                    "field": "inventory_id",
                    "equals": "4466",
                    "null_rejecting": false
                  }
                ] /* ref_optimizer_key_uses */
              },
              {
                "rows_estimation": [
                  {
                    "table": "`rental`",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 16008,
                        "cost": 3300.7
                      } /* table_scan */,
                      "potential_range_indices": [
                        {
                          "index": "PRIMARY",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "rental_date",
                          "usable": true,
                          "key_parts": [
                            "rental_date",
                            "inventory_id",
                            "customer_id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_fk_inventory_id",
                          "usable": true,
                          "key_parts": [
                            "inventory_id",
                            "rental_id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_fk_customer_id",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_fk_staff_id",
                          "usable": false,
                          "cause": "not_applicable"
                        }
                      ] /* potential_range_indices */,
                      "best_covering_index_scan": {
                        "index": "rental_date",
                        "cost": 3229.9,
                        "chosen": true
                      } /* best_covering_index_scan */,
                      "setup_range_conditions": [
                      ] /* setup_range_conditions */,
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_group_by_or_distinct"
                      } /* group_index_range */,
                      "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                          {
                            "index": "rental_date",
                            "ranges": [
                              "2005-05-25 04:00:00 <= rental_date <= 2005-05-25 05:00:00"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": true,
                            "rows": 10,
                            "cost": 3.0254,
                            "chosen": true
                          },
                          {
                            "index": "idx_fk_inventory_id",
                            "ranges": [
                              "4466 <= inventory_id <= 4466"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": true,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 5,
                            "cost": 7.01,
                            "chosen": false,
                            "cause": "cost"
                          }
                        ] /* range_scan_alternatives */,
                        "analyzing_roworder_intersect": {
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        } /* analyzing_roworder_intersect */
                      } /* analyzing_range_alternatives */,
                      "chosen_range_access_summary": {
                        "range_access_plan": {
                          "type": "range_scan",
                          "index": "rental_date",
                          "rows": 10,
                          "ranges": [
                            "2005-05-25 04:00:00 <= rental_date <= 2005-05-25 05:00:00"
                          ] /* ranges */
                        } /* range_access_plan */,
                        "rows_for_plan": 10,
                        "cost_for_plan": 3.0254,
                        "chosen": true
                      } /* chosen_range_access_summary */
                    } /* range_analysis */
                  }
                ] /* rows_estimation */
              },
              {
                "considered_execution_plans": [
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`rental`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "idx_fk_inventory_id",
                          "rows": 5,
                          "cost": 6,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "rows": 5,
                          "cost": 5.0254,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 5.0254,
                    "rows_for_plan": 5,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                "attaching_conditions_to_tables": {
                  "original_condition": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))",
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [
                    {
                      "table": "`rental`",
                      "attached": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))"
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                "refine_plan": [
                  {
                    "table": "`rental`",
                    "access_type": "range"
                  }
                ] /* refine_plan */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": {
            "select#": 1,
            "steps": [
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
              INSUFFICIENT_PRIVILEGES: 0
    1 row in set (0.00 sec)

# 确定问题并采取相应的优化措施 #

经过以上操作，基本可以定位问题出现的原因。此时可以根据实际情况采取相应措施，通过优化来提高执行的效率。比如在第三点提到的低效sql的执行计划，已经确认是对客户表customer的权标扫描导致效率不理想，我们可以通过对email创建索引来提高效率。

mysql> create index idx_email on customer(email);
    Query OK, 0 rows affected, 1 warning (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 1
    
    mysql> explain select sum(amount) from customer a ,payment b where 1=1 and a.customer_id=b.customer_id and email ='JANE.BENNETT@SAKILACUSTOMER.ORG'\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: a
             type: const
    possible_keys: PRIMARY,uk_email,idex_email,idx_email
              key: uk_email
          key_len: 153
              ref: const
             rows: 1
            Extra: Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: b
             type: ref
    possible_keys: idx_fk_customer_id
              key: idx_fk_customer_id
          key_len: 2
              ref: const
             rows: 28
            Extra: NULL
    2 rows in set (0.00 sec)

对比第3点，可以看出建立索引对customer表需要扫描的行数rows变少,性能有所提升（根据类型判断性能：由all变为const）,可见索引的使用可以大大提高数据库的访问速度，尤其表越大效率越明显。

[MySQL--SQL_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/105314555
[SQL_sql_-CSDN]: https://blog.csdn.net/qq_17033579/article/details/82950096
[70]: /images/20230528/b4de6747422b41c58b82a00518fd5164.png
[70 1]: /images/20230528/fe7b1c7cfcc7459fa7e60acdeebbb8e2.png