MySQL优化(一）sql优化的一般步骤

103 阅读 0 评论 68 点赞

我是靠谱客的博主谨慎棒球，最近开发中收集的这篇文章主要介绍MySQL优化(一）sql优化的一般步骤，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

sql优化的一般步骤

1、通过show status 命令了解各种sql的执行频率

show [session|global] status 命令可以提供服务器状态信息，session表示当前连接，global 表示自数据库上次启动至今的统计结果，如果不写默认是session.

mysql> show status like 'Com_%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Com_admin_commands        | 0     |
| Com_assign_to_keycache    | 0     |
| Com_alter_db              | 0     |
| Com_alter_db_upgrade      | 0     |
| Com_alter_event           | 0     |
| Com_alter_function        | 0     |
| Com_alter_procedure       | 0     |
| Com_alter_server          | 0     |
| Com_alter_table           | 2     |
| Com_alter_tablespace      | 0     |
| Com_alter_user            | 0     |
| Com_analyze               | 0     |
| Com_begin                 | 0     |
| Com_binlog                | 0     |
| Com_call_procedure        | 0     |
| Com_change_db             | 1     |
| Com_change_master         | 0     |
| Com_check                 | 0     |
| Com_checksum              | 0     |
| Com_commit                | 0     |
...

Com_xxx表示每个xxx语句执行的次数，通常关心以下几个统计参数：

Com_select:执行select操作的次数，一次查询只累加1.
Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加一次。
Com_update:执行update操作的次数。提交或者回滚都会累加
Com_delete:执行delete操作的次数。
上面的参数对于所有的存储引擎的表操作都会累加。下面的参数只针对Innodb存储引擎，累加的算法叶略有不同。
Innodb_rows_read:select查询返回的行数。
Innodb_rows_insert:执行insert 操作插入的行数。
Innodb_rows_update:执行update操作更新的行数。
Innodb_rows_delete:执行delete操作删除的行数。
通过以上参数，可以找出数据库的应用是以插入更新为准还是查询操作为准。
Com_commit和Com_rollback 可以了解事物提交和回滚操作的情况，如果回滚非常频繁，则可能应用编写不正确。
以下参数可以了解数据库的基本情况。
Connections:试图连接MYSQL服务器的次数；
Uptime:服务器工作时间。
Slow_queries:慢查询的次数。

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

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

通过慢查询日志定位，用–log-slow-queries[=file_name]选项启动mysql时，mysqld会写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。
慢查询日志是在查询结束以后才记录，不能很好的定位，可以通过使用show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等。

3、通过EXPLAIN分析抵消SQL的执行计划

通过以上步骤查到效率慢的sql后，可以用EXPLAIN或者desc获取如何执行select语句的信息。

mysql> explain select sum(a.score) from student a,teacher b where a.id=b.id G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: key_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: yly.a.id
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.02 sec)

每个列简单介绍下：

select_type:表示select的类型，常见的取值有SIMPLE(简单表,即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句）、SUBQUERY(子查询中的第一个select)等。
table:输出结果集的表。
type:表示MySQL在表中找到所需行的方式，或者叫访问类型，常见如下：

ALL（全盘扫描） | index（索引全扫描）  |  range（索引范围扫描）  |  ref（非唯一索引扫描或者唯一索引的前缀扫描）  |  eq_ref(使用唯一索引扫描)  |  const,system （单表中最多有一个匹配行） |  NULL（不用访问表或索引）

从左到右，性能由最差到最好。

type =ALL（全盘扫描）,如：

mysql> explain select * from student G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

type=index（索引全扫描）,如：

mysql> explain select id from student G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: index
possible_keys: NULL
          key: key_index
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using index
1 row in set (0.00 sec)

ERROR: 
No query specified

type=range (索引范围扫描)，如：

mysql> explain select * from student where id <10 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: range
possible_keys: key_index
          key: key_index
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

ERROR: 
No query specified

type=ref,使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行，ref还经常出现在join操作中，如：

mysql> explain select * from student where id =1 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: key_index
          key: key_index
      key_len: 5
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

type=eq_ref,类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是夺标连接中使用primary key 或者unique index作为关联条件，如：

mysql> explain select sum(a.score) from student a,teacher b where a.id=b.id G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: key_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: yly.a.id
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.00 sec)

ERROR: 
No query specified

type=const/system,单表中最多有一个匹配行，例如，根据主键primary key 或者唯一索引unique index进行的查询；

mysql> alter table student add  unique index uk_id(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from (select * from student where id='1');
ERROR 1248 (42000): Every derived table must have its own alias
mysql> explain select * from (select * from student where id='1') a G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: student
         type: const
possible_keys: uk_id,key_index
          key: uk_id
      key_len: 5
          ref: const
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

type=null,不用访问表或者索引，直接就能够得到结果，如：

mysql> explain select 1+2 from dual G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
1 row in set (0.00 sec)

ERROR: 
No query specified

type还有其他值，如ref_or_null（于ref类似，区别在于条件中中包含对null的查询）、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询）、index_subquery(in 的后面是查询非唯一素银字段的子查询）等。

possible_keys:表示查询时可能用到的索引。
key:表示实际使用的索引。
key_len:使用到索引字段的长度。
key_len字节的计算规则：
字符串：char(n) - n字节， varchar(n)- n字节 + 2字节(变长), ，多字节charset * [1~4]字节（utf8为3字节，utf8mb4为4字节计算）
数值类型： TINYINT-1字节，SMALLINT-2字节， MEDIUMINT-3字节， INT-4字节，BIGINT-8字节
时间类型：DATE-3字节， TIMESTAMP-4字节， DATETIME-8字节
字段属性：NULL属性+ 1字节
rows:扫描行的数量。
Extra:执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
常见的有以下几种内容
Using filesort
MySQL需额外的排序操作，不能通过索引顺序达到排序效果；又叫”文件排序“，易错误理论为排序结果过大，内存中不够需写磁盘文件排序。
一般有filesort，都建议优化去掉，CPU资源消耗大。
Using index
”覆盖索引扫描“，表示查询在索引树中就可查找所需数据，不用回表数据文件(回表操作），往往说明性能不错
Using temporary
查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化

explain extented 输出结果多了filtered 字段，同时可以使用show warning 来查看更清晰易读的sql.

mysql> explain extended select * from student G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> show warnings G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `yly`.`student`.`id` AS `id`,`yly`.`student`.`username` AS `username`,`yly`.`student`.`password` AS `password`,`yly`.`student`.`date` AS `date`,`yly`.`student`.`score1` AS `score1`,`yly`.`student`.`score` AS `score` from `yly`.`student`
1 row in set (0.00 sec)

ERROR: 
No query specified

explain partitions 可以查看sql所访问的分区；

mysql> explain partitions select * from teacher G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: teacher
   partitions: p0,p1,p2,p3,p4,p5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

4、通过show profile 分析SQL

通过have_profiling 查看是否支持profile.

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

默认profiling是关闭的，可以通过set开启。

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

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.01 sec)

下面通过一个具体的例子来说明show profile 的分析过程：
1、在一个innodb引擎的学生表student上，执行一个count(*)查询：

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

2、执行查询完毕后，通过show profiles 来找出当前SQL的query id为：3；

mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00007850 | select @@version_comment limit 1 |
|        2 | 0.00008125 | SELECT DATABASE()                |
|        3 | 0.00018900 | select @@profiling               |
|        4 | 0.00029200 | select count(*) from user        |
+----------+------------+----------------------------------+
4 rows in set, 1 warning (0.00 sec)

3、使用show profile for query 4 来查看SQL执行过程中线程的每个状态和消耗的时间：

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000085 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000022 |
| init                 | 0.000011 |
| System lock          | 0.000007 |
| optimizing           | 0.000005 |
| statistics           | 0.000009 |
| preparing            | 0.000008 |
| executing            | 0.000002 |
| Sending data         | 0.000101 |
| end                  | 0.000006 |
| query end            | 0.000005 |
| closing tables       | 0.000006 |
| freeing items        | 0.000009 |
| cleaning up          | 0.000009 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

可以看到主要消耗在sending data这个状态上，为了更清晰额看到排序结果，可以查询INFORMATION_SCHAEMA.PROFILING表病按照时间做DESC排序：

mysql> select state,sum(duration)as total_r,round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=4),2) as pct_r,count(*) as calls,
    -> sum(duration)/count(*) as 'R/call'
    -> from information_schema.profiling
    -> where query_id=4
    -> group by state
    -> order by total_r desc;
+----------------------+----------+-------+-------+--------------+
| state                | total_r  | pct_r | calls | R/call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000101 | 34.12 |     1 | 0.0001010000 |
| starting             | 0.000085 | 28.72 |     1 | 0.0000850000 |
| Opening tables       | 0.000022 |  7.43 |     1 | 0.0000220000 |
| checking permissions | 0.000011 |  3.72 |     1 | 0.0000110000 |
| init                 | 0.000011 |  3.72 |     1 | 0.0000110000 |
| statistics           | 0.000009 |  3.04 |     1 | 0.0000090000 |
| cleaning up          | 0.000009 |  3.04 |     1 | 0.0000090000 |
| freeing items        | 0.000009 |  3.04 |     1 | 0.0000090000 |
| preparing            | 0.000008 |  2.70 |     1 | 0.0000080000 |
| System lock          | 0.000007 |  2.36 |     1 | 0.0000070000 |
| end                  | 0.000006 |  2.03 |     1 | 0.0000060000 |
| closing tables       | 0.000006 |  2.03 |     1 | 0.0000060000 |
| query end            | 0.000005 |  1.69 |     1 | 0.0000050000 |
| optimizing           | 0.000005 |  1.69 |     1 | 0.0000050000 |
| executing            | 0.000002 |  0.68 |     1 | 0.0000020000 |
+----------------------+----------+-------+-------+--------------+
15 rows in set (0.02 sec)

show profile 能够在做SQL优化时帮助我们了解时间都消耗在哪里。

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

从MySQL5.6版本开始，可支持把MySQL查询执行计划树打印出来，对DBA深入分析SQL执行计划，COST成本都非常有用，打印的内部信息比较全面；
功能支持动态开关，因为对性能有20%左右影响，只建议分析问题时，临时开启

使用方式：首先打开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,如

mysql> select id from student where id >1 and id <10;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.01 sec)

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

mysql> select * from information_schema.optimizer_trace G;
*************************** 1. row ***************************
                            QUERY: select id from student where id >1 and id <10
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student`.`id` AS `id` from `student` where ((`student`.`id` > 1) and (`student`.`id` < 10))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`student`",
                "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": "`student`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2,
                    "cost": 3.5
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "uk_id",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "key_index",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "best_covering_index_scan": {
                    "index": "key_index",
                    "cost": 1.401,
                    "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": "uk_id",
                        "ranges": [
                          "1 < id < 10"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.21,
                        "chosen": true
                      },
                      {
                        "index": "key_index",
                        "ranges": [
                          "1 < id < 10"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 2.21,
                        "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": "uk_id",
                      "rows": 1,
                      "ranges": [
                        "1 < id < 10"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`student`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 1,
                      "cost": 1.41,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 1.41,
                "rows_for_plan": 1,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`student`",
                  "attached": "((`student`.`id` > 1) and (`student`.`id` < 10))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`student`",
                "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.01 sec)

ERROR: 
No query specified

经过以上步骤，基本就可以找出sql的问题所在。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。