SQL性能优化指南-优快云博客

本文链接：https://blog.youkuaiyun.com/java_kider/article/details/84380152

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

1 优化 SQL 语句的一般步骤

当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题，本节将向读者介绍这个过程。

1.1 通过 show status 命令了解各种 SQL 的执行频率

MySQL 客户端连接成功后，通过 show [session|global]status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参是“session”。下面的命令显示了当前 session 中所有统计参数的值：

mysql> show status like 'Com_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 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_inserted：执行 INSERT 操作插入的行数。
● Innodb_rows_updated：执行 UPDATE 操作更新的行数。
● Innodb_rows_deleted：执行 DELETE 操作删除的行数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。对于事务型的应用，通过 Com_commit 和 Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于用户了解数据库的基本情况。
● Connections：试图连接 MySQL 服务器的次数。
● Uptime：服务器工作时间。
● Slow_queries：慢查询的次数。

1.2 定位执行效率较低的 SQL 语句

可以通过以下两种方式定位执行效率较低的 SQL 语句。
● 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。
● 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1.3 通过 EXPLAIN 分析低效 SQL 的执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如想计算 2006 年所有公司的销售额，需要关联 sales 表和company 表，并且对 moneys 字段做求和（sum）操作，相应 SQL 的执行计划如下：

mysql> explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year
= 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)

每个列的简单解释如下：
● select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等。
● table：输出结果集的表。
● type：表示表的连接类型，性能由好到差的连接类型为 system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接
中使用primary key或者unique index）、ref （与eq_ref类似，区别在于不是使用primarykey 或者 unique index，而是使用普通的索引）、ref_or_null（与 ref 类似，区别在于条件中包含对 NULL 的查询）、index_merge(索引合并优化)unique_subquery（in的后面是一个查询主键字段的子查询）、index_subquery （与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）、range（单表中的范围查询）、index（对于前面的每一行，都通过查询索引来得到数据）、all （对于前面的每一行，都通过全表扫描来得到数据）。
● possible_keys：表示查询时，可能使用的索引。
● key：表示实际使用的索引。
● key_len：索引字段的长度。
● rows：扫描行的数量。
● Extra：执行情况的说明和描述。