大家好,我是程序员啊粥,这段时间一直在分享 MySQL 索引系列的文章,我们学会了索引模型 MySQL InnoDB 索引模型,以及和具体的索引使用原则等内容,今天开始我们学习 SQL 的优化。
说起 SQL 优化,我们需要知道一个 SQL 的执行频率,假如说你有一条慢 SQL,好几个月才执行一次,那我觉得你其实也没啥花费精力优化它的必要,毕竟执行频率太低,投入产出比不足。
关于查询 SQL 执行频率,我们可以使用 show global status like 'Com___'
,(这后边是 7 个下划线),这条命令可以显示当前数据库中增删改查等各个语句的使用次数,可以看我,我这个库中,大量的执行语句都是 select 语句,其他语句非常少。
那说明这个库中的查询时比较多的,所以我们需要额外关注查询的效率。
关于具体的查询效率,我们可以通过查询数据库的慢 SQL 日志来查询。
慢查询日志
show variables like 'slow_query_log'
set global slow_query_log = 1
; (只对当前会话生效,全局生效需要修改 my.conf 配置文件)set global long_query_time = 4
这部分基本掌握这几个命令就可以了,我们可以在临时会话中开启慢 SQL 日志,然后执行对应的 SQL 语句来记录日志。
慢查询日志可以帮我们记录具体的慢查询语句,但是为什么慢它是没发告诉我们的,因此,我们还需要借助其他的一些命令来帮助我们具体慢的原因。
使用 MySQL profiling 剖析单条 SQL 查询耗时
show profiles
能在做 SQL 优化时帮助我们了解耗时具体耗在了哪里。show prifile for query id
查看具体各个阶段的耗时这两条命令结合在一起,可以明确告诉我们这条 SQL 在执行中,到底耗时在那一步,比如是某个子查询或者 Server 层数据传输等具体原因。
得出时间消耗在那个环节之后,我们便可以使用具体的执行计划来进行针对性的优化,下边着重介绍一下关于 SQL 执行计划的使用。
通过 Explain 查询执行计划
结果输出展示:
ID
该语句的唯一标识。如果 explain 的结果包括多个 id 值,则数字越大越先执行;而对于相同 id 的行,则表示从上往下依次执行。
Select_type
查询类型,有如下几种取值:
table
表示当前这一行正在访问哪张表,如果 SQL 定义了别名,则展示表的别名
Partitions
当前查询匹配记录的分区。对于未分区的表,返回 Null
Type
连接类型,有如下几种取值,性能从好到坏排序 如下:
展示当前查询可以使用哪些索引,这一列的数据是在优化过程的早期创建的,因此有些索引可能对于后续优化过程是没用的。
key
表示 MySQL 实际选择的索引。
key_len
索引使用的字节数。由于存储格式,当字段允许为 NULL 时,key_len 比不允许为空时大 1 字节。
ref
表示将哪个字段或常量和 key 列所使用的字段进行比较。
如果 ref 是一个函数,则使用的值是函数的结果。要想查看是哪个函数,可在 EXPLAIN 语句之后紧跟一个 SHOW WARNING 语句。
rows
MySQL 估算会扫描的行数,数值越小越好。
filtered
表示符合查询条件的数据百分比,最大 100。用 rows × filtered 可获得和下一张表连接的行数。例如rows = 1000,filtered = 50%,则和下一张表连接的行数是 500。
以上就是关于 explain 执行计划结果中的字段说明,具体的含义很多都是我从官网直接拿过来的,比较枯燥,当然也不需要你死记硬背,只要你在 SQL 优化过程中,有意识的使用 explain 分析它的执行计划,然后来查阅一下每个字段是什么含义,最后针对具体的问题进行能进行优化,这就可以了。
关于 explain 的具体使用,我会在下一篇文章中进行实战演练,今天就单纯介绍一下这些字段的含义,你有个大概印象就可以。
我是程序员啊粥,让我们一起在技术中向上生长。