MySQL优化之SQL优化详解

小奥2024-02-222024-03-14

MySQL优化之SQL优化详解

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

优化SQL的步骤

当面对一个有SOL性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题SOL并尽快解决问题，我将带大家一起学习这个过程。

1. 了解SQL执行频率

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELETE的访问频次：

show global status like 'Com_______';  //注意：七个下划线代表七个字符

Com_xxx表示每个xxx语句执行的次数，我们比较关心的是以下几个统计参数：

Com_select：执行SELECT操作的次数，一次查询只累加1；
Com_insert：执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次；
Com_update：执行UPDATE操作的次数；
Com_delete：执行DELETE操作的次数。

通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。.

对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

2. 定位执行效率低的SQL

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

我们可以通过如下命令查询慢查询是否开启：

show variables like 'slow_query_log';

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢查询日志开关
slow_query_log=1
# 设置慢日志的时间为2秒。SQL语句的执行时间超过两秒，就会被视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/192-slow.log

systemctl restart mysqld

我们可以去var/lib/mysql/目录下查看一个名字带-slow.log的日志文件，初始内容为数据库版本信息。

3. 查看SQL执行的耗时

show profiles能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

然后可以通过select @@profiling;查看profile是否开启

select @@profiling;

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

set profiling=1;

然后我们就可以执行一系列的业务SQL的操作，通过如下指令查看SQL的执行耗时：

# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句的各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4. 分析SQL的执行计划

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

# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

下面简单的介绍一下每个字段的作用：

id

select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行），如果为null表示这个是一个结果集，不需要用它来进行查询。

select_type

表示SELECT的类型。

取值	含义
simple	简单的select查询，查询中不包含子查询或者union
primary	查询中包含任何复杂的子查询，最外层查询被标记为primary
union	若第二个select出现在union之后，则会被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
dependent union	与union一样，出现在union或union all语句中的，但是这个查询要受到外部查询的影响
union result	包含在union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null
subquery	在select或where列表中包含子查询
dependent subquery	与dependent union类似，表示这个subquery的查询主要受到外部表查询的影响
derived	在from列表中包含的子查询被标记为derived(衍生)，mysql或者递归执行这些子查询，把结果放在临时表里

table

输出结果集的表名，如果查询使用了别名，那么这里显示的是别名。

如果不涉及对数据表的操作，那么这显示为null，
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

type

表示连接类型，描述了找到所需数据使用的扫描方式。

性能有好到差的连接类型为null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

取值	含义
system	系统表，少量数据，往往不需要磁盘IO
const	常量连接
eq_ref	使用索引是唯一索引，对于每个索引键值，表中只有一条记录，即多表连接使用primary key或者 unique index作为关联条件
ref	使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值得记录行
ref_or_null	与ref方法类型，只是增加了null值的比较
range	索引范围扫描，常见于<、<=、>、>=、between等操作符
index	索引全扫描，MySQL遍历整个索引来查询匹配的行
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集
fulltext	全文检索索引
unique_subquery	用于where中的in形式子查询，子查询返回不重复唯一的值
index_subquery	用于in形式子查询，子查询可能返回重复值，可以使用索引将子查询去重
all	全表扫描，MySQL遍历全表来找到匹配的行

possible_key

查询可能涉及在这张表上的索引，一个或多个，但不一定被查询实际使用。

key

实际使用的到索引，如果为null，则说明没有使用索引；查询中如果使用了覆盖索引，则该索引仅出现在key列表中。

key_len

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

ref

如果是使用的常量等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者是函数，或者条件列发生了内部隐式转换，这里可能显示为func。

rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比，filter的值越大越好。

Extra

取值	含义	举例
Using where	说明SQL使用了where条件过滤数据	`explain select * from table where id > 3`
Usering index	说明SQL需要返回所有列数据均在一棵索引树，而无需访问实际的行记录	`explain select id from table`
Using index condition	说明命中了索引，但是并不是所有列数据都在索引树上，还需要访问实际的行记录	`explain select * from table t1, table t2 where t1.id= t2.id;`
Using filesort	说明得到所需结果集，需要对所有记录进行文件排序。比如在一个没有建立索引的列上进行order by	`explain select id from table order by name`
Using temporary	说明需要建立临时表(temporary table)来暂存中间结果。	`explain select name, count(*) from table group by name order by name;`

5. 分析优化器如何选择执行计划

MySQL 5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。

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

# 打开trace并设置格式为json
set OPTIMIZER_TRACE = "enabled=0n", END_MARKERS_IN_JSON=on;
# 设置最大内存
set OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;

执行完我们想要分析的SQL之后，检查information_schema.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的。

select * from information_schema.OPTIMIZER_TRACE;