听说你不想只搬砖？——Mysql中sql优化步骤

最新推荐文章于 2024-04-30 15:49:29 发布

悟空Edison

最新推荐文章于 2024-04-30 15:49:29 发布

阅读量216

点赞数

分类专栏： mysql优化文章标签： mysql sql

本文链接：https://blog.csdn.net/s296954345/article/details/107382098

版权

mysql优化专栏收录该内容

9 篇文章 0 订阅

订阅专栏

优化SQL步骤

听说你不想在工作中只做一些简单的CRUD的工作了。

那么作为极客的你，一定得知道这些优化SQL的步骤，才能在大数据级表查询中不用注释掉Thread.sleep(10000);就能让你的程序优化杠杠的。

查看SQL执行频率

查看当前连接的信息

# 查看SQL执行频率 7个_占位 查询的是当前连接的信息 此处代表命令执行操作次数
show status like 'Com_______';
# 以下是查询出的信息
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 1     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

查看全局的连接信息

### 查看全局的SQL执行频率 此处代表命令执行操作次数
show global status like 'Com_______';
### 以下是查询出的信息
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_binlog    | 0      |
| Com_commit    | 3515   |
| Com_delete    | 95     |
| Com_import    | 0      |
| Com_insert    | 18088  |
| Com_repair    | 0      |
| Com_revoke    | 0      |
| Com_select    | 101830 |
| Com_signal    | 0      |
| Com_update    | 21820  |
| Com_xa_end    | 0      |
+---------------+--------+
11 rows in set (0.01 sec)

查看InnoDB表的SQL执行频率

#查看InnoDB表的SQL执行频率 此处的value代表影响的行数
show global status like 'Innodb_rows_%';

### 以下是返回信息
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Innodb_rows_deleted  | 865      |
| Innodb_rows_inserted | 3287391  |
| Innodb_rows_read     | 37754591 |
| Innodb_rows_updated  | 7009     |
+----------------------+----------+
4 rows in set (0.01 sec)

show status字段详解

具体的Variable_name字段各种值代码的意义推荐阅读MySQL运行状态show status详解

定位低效率执行SQL

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

慢查询日志：通过慢查询日志定位哪些执行效率较低的SQL语句，用–log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
show processlist：慢查询日志在查询结束后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist;命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时的查看SQL的执行情况，同时对一些锁表的操作进行优化。注意，如果执行完成的语句是查询不出来的。当一不小心执行了一个相当慢的查询锁表了，就可以show processlist;查出进程id，kill掉。，趁还没被老板diss的时候先发制人

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

explain分析执行计划

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

查询SQL语句的执行计划：

mysql> explain select * from patient;

字段	含义
id	select查询的序列号，是一组数字，表示是查询中执行select子句或是操作表的顺序
select_type	表示SELECT类型，常见的取值有SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为（system > const > eq_ef > ref > ref_or_null > index_merge > index_subquery > range > index > all）
possible_keys	表查询时，可能使用的索引
key	表实际使用的索引
key_leng	索引字段长度
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
rows	扫描行的数量
filtered	表示此查询条件所过滤的数据的百分比
extra	执行情况的说明和描述

explain中的id

id相同表示加载顺序从上到下，普通的关联查询
id不同id值越大，优先级越高，越先被执行。包含子查询时
id有不同，也有相同。先执行id大的，然后相同id的加载顺序从上到下。例如：form子查询时，再做关联

explain中的select_type

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂子查询，最外层查询标记为该标识
SUBQUERY	在SELECT或者WHERE列表中的子查询
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED（衍生）MYSQL会执行这些子查询，把结果放在临时表中例如explain select a.* from (select * from user where id in (1,2)) a;
UNION	若第二个SELECT出现UNION之后，则标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

explain中的type

type显示的是访问类型，是较为重要的指标，可取值为：

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

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.037版本开始增加了对show profile语句的支持。show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过select @@have_profiling命令，能够看到当前MySQL是否支持profile：

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

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

#查询是否开启profiling 结果0代表关闭
mysql> select @@profiling
    -> ;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

# 设置profiling=1开启
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执行的过程

# 查询最近命令的耗时
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00022575 | select @@profiling           |
|        2 | 0.00012400 | select * from patient        |
|        3 | 0.00023025 | SELECT DATABASE()            |
|        4 | 0.00083125 | show databases               |
|        5 | 0.00118525 | show tables                  |
|        6 | 0.01512725 | select count(*) from patient |
|        7 | 0.04421450 | select * from patient        |
+----------+------------+------------------------------+
7 rows in set, 1 warning (0.00 sec)

当我们查出了最近命令耗时后还可以通过 show profile for query 查询出的query_id; 查询命令具体各个阶段的耗时

mysql> show profile for query 7;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000081 |
| Executing hook on transaction  | 0.000008 |
| starting                       | 0.000012 |
| checking permissions           | 0.000009 |
| Opening tables                 | 0.000055 |
| init                           | 0.000009 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000021 |
| preparing                      | 0.000019 |
| executing                      | 0.043880 |
| end                            | 0.000026 |
| query end                      | 0.000007 |
| waiting for handler commit     | 0.000014 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000022 |
| cleaning up                    | 0.000018 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

查看所有信息show profile all for query 7;

trace分析优化器执行计划

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

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

有兴趣的朋友可以点击相关链接学习了解，或者百度大法好～

相关链接：通过trace分析优化器如何选择执行计划

放在最后

本文根据学习B站【黑马程序员】2020最新MySQL高级教程（求职面试必备）p38-p46整理笔记，有需要的同学可以前去系统的学习。

悟空Edison

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
听说你不想只搬砖？——Mysql中sql优化步骤

优化SQL步骤听说你不想在工作中只做一些简单的CRUD的工作了。那么作为极客的你，一定得知道这些优化SQL的步骤，才能在大数据级表查询中不用注释掉Thread.sleep(10000);就能让你的程序优化杠杠的。查看SQL执行频率查看当前连接的信息# 查看SQL执行频率 7个_占位查询的是当前连接的信息此处代表命令执行操作次数show status like 'Com_______';# 以下是查询出的信息+---------------+-------+| Variable_name
复制链接

扫一扫