mysql-explain

最新推荐文章于 2024-05-21 09:46:31 发布

沐柌

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量142

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/little_sc/article/details/128289083

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL 之 SQL优化

一、优化SQL语句的一般步骤

1.1、查看各种SQL的执行频率

show [session|gloable] status：查询服务器状态信息，默认是session级别–当前连接的统计结果，global级别–自数据库上次启动至今的统计结果。

show status like 'Com_%'; # 查询当前session中所有统计参数的值。

Com_xxx：表示xxx语句执行的次数。
- Com_select：执行SELECT操作的次数。
- Com_insert：执行INSERT操的次数。
- Com_commit：事务提交的次数。
- Com_rollback：事务回滚的次数。
上面这些参数，可以用于了解当前应用是插入更新多还是查询操作多，以及各种类型的SQL大致的执行比例。
对于回滚比较频繁的数据库，就有可能是应用出现问题。
Connections：试图连接MySQL服务器的次数
Uptime：服务器工作时间。
Slow_queries：慢查询次数。

1.2、定位执行效率较低的SQL

两种方式：

慢查询日志定位：mysql 启动时设置 --log-slow-queries=[file_name] ，mysqld会将所有执行时间超过long_query_time秒的语句写入上面指定的日志文件中。
show processlist：查看当前MySQL在进行的线程(包括线程状态、是否锁表等)，这种可以实时查看SQL的执行情况。只有拥有PROCESS权限的用户才能看到所有正在运行的线程，否则只能看到自己正在运行的线程。

简单介绍一下 show processlist：该命令实际上查询的就是 information_schema.processlist中的数据，即 show processlist ==== select * from information_schema.processlist，接下来再看看这个表中各个字段的含义：

Id：当前线程的唯一标识ID(表主键)，可以使用 kill 线程ID杀死这个线程。
User：启动这个线程的用户。
Host：发送请求的客户端的IP和端口号。
DB：当前命令是执行在哪一个数据库上，若未指定则为NULL。
Command：指此刻线程正在执行的命令。
Time：当前线程处于当前状态的时间。
State：线程的状态。
Info：记录线程执行的语句，默认只显示前100个字符。show full processlist命令可以看完整信息。

基本常用命令：

# 按Time倒叙查询正在执行的线程。
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

# 查出所有执行时间超过5分钟的线程
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

1.3、通过EXPLAIN分析低效SQL

通过上面那些步骤就可查询到效率低的SQL，接下来就可以使用 EXPLAIN 来分析这些SQL的执行信息。

各个列的含义：

id
- id相同，执行顺序由上到下。
- 若是子查询，id序号会自增，id值越大优先级越高，越先被执行。
- id相同与不同同时存在，实际情况进行分析。
select_type：表示SELECT的类型。
- SIMPLE：简单表，即不使用表连接或子查询
- PRIMARY：主查询，即外层的查询
- UNION：UNION中的第二个或其后面的查询语句
- SUBQUERY：子查询中的第一个SELECT操作。
table：输出结果集的表。
type：表示MySQL在表中找到所需行的方式，也可叫访问类型。下面列出几种常见类型，从上到下，性能由最差到最好。
- ALL：全表扫描，遍历全表来查询匹配的行。
- index：索引全扫描，遍历整个索引来查询匹配的行。
- range：索引范围扫描，常见于 < <= > >= between等操作符。
- ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录。
- eq_ref：使用唯一索引，且每个索引键值，表中只有一条记录匹配。简单来说，就是多表连接中使用了 primary key 或者 unique key 进行等值查询。
- const：针对主键或唯一索引的等值查询扫描，最多返回一条数据。
- system：该表只有一行。
- NULL：不用访问表或者索引，就能直接得到结果。
- 其他类型：
  - ref_or_null：类似ref，区别在于条件中包含对NULL的查询。
  - index_merge：索引合并优化，一个查询里面用到了多个索引
  - unique_subquery：in 的后面是一个查询主键字段的子查询
  - index_subquery：in的后面是一个查询非唯一索引字段的子查询。
- possible_keys：表示查询时可能使用的索引。
- key：表示实际使用的索引。
- key_len：使用到索引字段的长度。
- ref：表示将哪个字段或常量和key列所使用的字段进行比较。
- rows：扫描行的数量。
- filtered：表示符合查询条件的数据百分比。
- Extra：执行情况的说明和描述。

1.4、通过 show profile 分析 SQL

# 查询当前是否支持profile
select  @@have_profiling；
#查询当前是否开启profilling
select @@profiling;
# 若未开启，则进行开启
set profiling=1;

# 查询出已执行语句的信息
show profiles ;
# 根据上面查询的Quer ID，来查看执行过程中线程的每个状态和消耗的时间。
show profile for query 48;

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

通过 trace 文件来进一步了解为什么优化器选择A计划执行而不是B计划执行，更好的理解优化器的行为。

set optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

# 执行使用的SQL语句
xxx

select * from information_schema.optimizer_trace;