MySQL 优化—— SQL 性能分析
SQL 性能分析
SQL 执行频率
MySQL 客户端连接成功后,通过 show [session | global] status
命令可以提供服务其状态信息。通过下面指令,可以查看当前数据库 CRUD 的访问频次:
SHOW GLOBAL STATUS LIKE 'Com_______';
七个下划线代表这个七个占位。
查询数据库中整体的 CURD 频次,一般针对 select 比较多的数据库。
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认 10 s)的所有 SQL 语句的日志。
MySQL 的慢查询日志默认没有开启,需要在 MySQL 的配置文件(/etc/my.cnf)中配置如下信息:
# 开启 MySQL 慢查询日志开关
slow_query_log=1
# 设置慢查询的时间为 2 秒,SQL 语句执行时间操作 2 s,就会视为慢查询,并记录到慢查询日志中。
long_query_time=2
配置完成需重启 MySQL 服务器进行测试,查看慢查询日志文件的信息:/var/lib/mysql/localhost-slow.log
查看慢查询日志的开关情况
show variables like 'slow_query_log';
profile 详情
能够在做 SQL 优化时帮助我们了解时间都耗费到哪去了。通过 have_profiling
参数,能够看到当前 MySQL 是否支持 profile
操作:
SELECT @@have_profiling;
默认情况下是关闭的(0),通过 set 语句可以选择在 session/global
级别开启 profile
:
SELECT @@profiling;
:查看 profiling
是否开启
SET profiling = 1;
:开启 profiling
相关操作效果:
# 查看每一条 SQL 的耗时基本情况
show profiles;
# 查看指定 query_id 的 sql 语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定 query_id 的 SQL 语句 CPU 的使用情况
show profile cpu for query query_id;
-
show profiles
\
列分别是:SQL 语句的 id,执行时间秒,具体的 SQL 语句。
-
show profile for query 25
这条语句在各个状态的耗时详细情况。
-
show profile cpu for query 79
可以看到具体语句 CPU 的情况。
explain 执行计划
explain 或者 desc 命令获取 MySQL 如何执行 select 语句 的信息,包括 select 语句执行过程中表如何连接和连接顺序。
语法:explain select 语句
explain 具体字段解析:
那么一般情况下重点关注的是以下几个字段:
- type:一般业务情况下是优化到
const、ref
(如果 type 类型是在后面的话) - possible_keys:可能会用到的索引与实际用到的索引进行对比,看看是否能通过索引来进行优化。
- key:实际用到的索引。
- key_len:索引的最大长度,越短越好(不丢失精度前提下)。
- filtered:值越大越好
- Extra:其他信息,也比较重要。
小结:
对于 SQL 性能分析这章,学习了 4 个点:
- SQL 执行频率:查看数据库中查询是否执行频率最高。
- 慢查询日志:查询哪些 SQL 语句超过了规定时间,标记为慢查询。
- profile 详情:查看具体的 SQL 语句执行的耗时时间,包括各个阶段的用时以及 CPU 情况。
- explain\desc:查看具体 SELECT 执行计划,根据查询到的字段去进行 SQL 优化的方案。
后续将学习 SQL 优化的具体方案,以及不同的 SQL 优化。