SQL优化前置知识之优化前分析

在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在 MySQL 中优化 SQL 语句的方法。

当面对一个有 SQL 性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题 SQL 并尽快解决问题。

查看SQL执行频率

MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计结果和 global 级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值:

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 4     |
| Com_delete    | 4     |
| Com_insert    | 35    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 291   |
| Com_signal    | 0     |
| Com_update    | 11    |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

global:

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 1     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

下面这条语句则只会查看使用innodb引擎表的操作次数:

mysql> show status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 2     |
| Innodb_rows_inserted | 49    |
| Innodb_rows_read     | 382   |
| Innodb_rows_updated  | 17    |
+----------------------+-------+
4 rows in set (0.00 sec)
参数 含义
Com_select 执行 select 操作的次数,一次查询只累加 1。
Com_insert 执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。
Com_update 执行 UPDATE 操作的次数。
Com_delete 执行 DELETE 操作的次数。
Innodb_rows_read select 查询返回的行数。
Innodb_rows_inserted 执行 INSERT 操作插入的行数。
Innodb_rows_updated 执行 UPDATE 操作更新的行数。
Innodb_rows_deleted 执行 DELETE 操作删除的行数。

定位低效SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。这方面的配置见MySQL之日志配置全解
  • show processlist : 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。

show processlist的结果:

列名 描述
id 用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
user 显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
host 显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
db 显示这个进程目前连接的是哪个数据库
command 显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect)等
time 显示这个状态持续的时间,单位是秒
state 显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info 显示这个sql语句,是判断问题语句的一个重要依据

explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 :

explain  select * from tb_item where id = 1;

SQL优化前置知识之优化前分析
explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

SQL优化前置知识之优化前分析
字段 含义
id select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。
select_type 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等
table 输出结果集的表
type 表示表的连接类型,性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys 表示查询时,可能使用的索引
key 表示实际使用的索引
key_len 索引字段的长度
rows 扫描行的数量
extra 执行情况的说明和描述

准备三张如图所示的表:

SQL优化前置知识之优化前分析

explain 之 id

id 字段是 select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种 :

1) id相同表示加载表的顺序是从上到下。

mysql> explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+    
| id | select_type | table | partitions | type   | possible_keys               | key     | key_len | ref             | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | r     | NULL       | ALL    | PRIMARY                     | NULL    | NULL    | NULL            |    5 |   100.00 | NULL                                               |
|  1 | SIMPLE      | ur    | NULL       | ALL    | fk_ur_user_id,fk_ur_role_id | NULL    | NULL    | NULL            |    6 |    20.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY                     | PRIMARY | 98      | test.ur.user_id |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

2) id 不同id值越大,优先级越高,越先被执行。

mysql> EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_role    | NULL       | const | PRIMARY              | PRIMARY              | 98      | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user_role | NULL       | ref   | fk_ur_user_id        | fk_ur_user_id        | 99      | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | t_user    | NULL       | const | unique_user_username | unique_user_username | 137     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

3) id 有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。

explain 之 select_type

表示 SELECT 的类型,常见的取值,如下表所示:

select_type 含义
SIMPLE 简单的select查询,查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子查询,最外层查询标记为该标识
SUBQUERY 在SELECT 或 WHERE 列表中包含了子查询
DERIVED 在FROM 列表中包含的子查询,被标记为 DERIVED(衍生) MYSQL会递归执行这些子查询,把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后,则标记为UNION ; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为 : DERIVED
UNION RESULT 从UNION表获取结果的SELECT
mysql> explain select * from t_user where id = (select id from user_role where role_id = '9');
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_user    | NULL       | ALL  | PRIMARY       | NULL          | NULL    | NULL  |    6 |    16.67 | Using where |
|  2 | SUBQUERY    | user_role | NULL       | ref  | fk_ur_role_id | fk_ur_role_id | 99      | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 3 warnings (0.00 sec)
mysql> explain select * from t_user where id = '1' union select * from t_user where id = '2';
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_user     | NULL       | const | PRIMARY       | PRIMARY | 98      | const |    1 |   100.00 | NULL            |
|  2 | UNION        | t_user     | NULL       | const | PRIMARY       | PRIMARY | 98      | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

注: MySQL5.7中对Derived table做了一个新特性。该特性允许将符合条件的Derived table中的子表与父查询的表合并进行直接JOIN。体现为simple查询

explain 之 table

展示这一行的数据是关于哪一张表的

explain 之 type

type 显示的是访问类型,是较为重要的一个指标,可取值为:

type 含义
NULL MySQL不访问任何表,索引,直接返回结果
system 表只有一行记录(等于系统表),这是const类型的特例,一般不会出现
const 表示通过索引一次就找到了,const 用于比较primary key 或者 unique 索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL 就能将该查询转换为一个常量。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询,关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)
ref_or_null 类似ref,但是可以搜索值为NULL的行。
index_merge 表示使用了索引合并的优化方法。
range 只检索给定返回的行,使用一个索引来选择行。 where 之后出现 between , < , > , in 等操作。
index index 与 ALL的区别为 index 类型只是遍历了索引树, 通常比ALL 快, ALL 是遍历数据文件。
all 将遍历全表以找到匹配的行

结果值从最好到最坏以此是:

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

一般来说, 我们需要保证查询至少达到 range 级别, 最好达到ref 。

explain 之 key

possible_keys : 显示可能应用在这张表的索引, 一个或多个。

key : 实际使用的索引, 如果为NULL, 则没有使用索引。

key_len : 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。

explain 之 rows

扫描行的数量。

explain 之 extra

其他的额外的执行计划信息,在该列展示 。

extra 含义
using filesort 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取, 称为 “文件排序”, 效率低。
using temporary 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by; 效率低
using index 表示相应的select操作使用了覆盖索引, 避免访问表的数据行, 效率不错。

show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

(5.7版本后推荐使用performance_schema工具)

通过 have_profiling 参数,能够看到当前MySQL是否支持profile:

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

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

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

查看是否开启:

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

通过profile,我们能够更清楚地了解SQL执行的过程。

首先,我们可以执行一系列的操作,如:

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后,再执行show profiles 指令, 来查看SQL语句执行的耗时:

SQL优化前置知识之优化前分析

通过show profile for query query_id语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间:

SQL优化前置知识之优化前分析

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回个客户端。由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整各查询中耗时最长的状态。

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

SQL优化前置知识之优化前分析
字段 含义
Status sql 语句执行的状态
Duration sql 执行过程中每一个步骤的耗时
CPU_user 当前用户占有的cpu
CPU_system 系统占有的cpu

trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开trace ,设置格式为 JSON,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句 :

select * from tb_item where id < 4;

最后, 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 :

select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from tb_item where id < 4
TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS `num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS `status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS `createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where (`tb_item`.`id` < 4)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tb_item`.`id` < 4)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`tb_item`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`tb_item`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9816098,
                    "cost": 2.04e6
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "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": "PRIMARY",
                        "ranges": [
                          "id < 4"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 1.6154,
                        "chosen": true
                      }
                    ] /* 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": "PRIMARY",
                      "rows": 3,
                      "ranges": [
                        "id < 4"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.6154,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`tb_item`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 2.2154,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2154,
                "rows_for_plan": 3,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`tb_item`.`id` < 4)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tb_item`",
                  "attached": "(`tb_item`.`id` < 4)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`tb_item`",
                "access_type": "range"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/sql%e4%bc%98%e5%8c%96%e5%89%8d%e7%bd%ae%e7%9f%a5%e8%af%86%e4%b9%8b%e4%bc%98%e5%8c%96%e5%89%8d%e5%88%86%e6%9e%90/