Mysql优化(一)
# sql语句性能查询Profile
Profile是sql性能监控工具,专门用来监控sql语句执行时间,用来达到sql性能监控的目的。
# Profile使用
Profile操作汇总
# 打开profile
SET PROFILING=1;
# 执行sql语句
SELECT * FROM endpoint_inventory ei;
# 分析sql执行时间
SHOW PROFILES;
# sql语句执行每个过程时间
SHOW PROFILE FOR QUERY 2;
# 分析每个过程做了什么
SHOW PROFILE ALL FOR QUERY 2;
# 关闭Profile
set PROFILING=0;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
如下图:
# Profile显示详细执行时间
SHOW PROFILE FOR QUERY 2;
# 2为sql语句监控编号
2
3
上图中纵向栏意义:
标识 | 意义 |
---|---|
starting | 开始 |
checking permissions | 检查权限 |
Opening tables | 打开表 |
init | 初始化 |
System lock | 系统锁 |
optimizing | 优化 |
statistics | 统计 |
preparing | 准备 |
executing | 执行 |
Sending data | 发送数据 |
Sorting result | 排序 |
end | 结束 |
query end | 查询 结束 |
closing tables | 关闭表 /去除TMP 表 |
freeing items | 释放物品 |
cleaning up | 清理 |
# Profile显示详细执行过程
SHOW PROFILE ALL FOR QUERY 2;
上图中横向栏意义:
标识 | 意义 |
---|---|
"Status": "query end" | 状态 |
"Duration": "1.751142" | 持续时间 |
"CPU_user": "0.008999" | cpu用户 |
"CPU_system": "0.003999" | cpu系统 |
"Context_voluntary": "98" | 上下文主动切换 |
"Context_involuntary": "0" | 上下文被动切换 |
"Block_ops_in": "8" | 阻塞的输入操作 |
"Block_ops_out": "32" | 阻塞的输出操作 |
"Messages_sent": "0" | 消息发出 |
"Messages_received": "0" | 消息接受 |
"Page_faults_major": "0" | 主分页错误 |
"Page_faults_minor": "0" | 次分页错误 |
"Swaps": "0" | 交换次数 |
"Source_function": "mysql_execute_command" | 源功能 |
"Source_file": "sql_parse.cc" | 源文件 |
"Source_line": "4465" | 源代码行 |
# 慢查询
MySQL 慢查询的相关参数解释:
slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
1、查询系统是否开启了慢查询
SHOW VARIABLES LIKE '%slow_query_log%';
2、设置慢查询
SET GLOBAL slow_query_log=1; # 1:开启 0:关闭
慢查询配置文件路径(mysql安装路径下):C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
慢查询记录时间
show variables like 'long_query_time%';
设置慢查询时间
set global long_query_time=4;
日志分析工具:mysqldumpslow
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow
# 查询计划Explain
查询计划是分析sql语句执行计划,了解sql语句如何从表中查询到目标数据。
标识 | 意义 |
---|---|
id: | 查询的唯一标识 |
select_type: | 查询的类型 |
table: | 查询的表, 可能是数据库中的表/视图,也可能是 FROM 中的子查询 |
type: | 搜索数据的方法 |
possible_keys: | 可能使用的索引 |
key: | 最终决定要使用的key |
key_len: | 查询索引使用的字节数。通常越少越好 |
ref: | 查询的列或常量 |
rows: | 需要扫描的行数,估计值。通常越少越好 |
extra: | 额外的信息 |
# 执行计划 - Id
select 查询的序列号,包含一组数字,表示查询中执行select字句或操作表的顺序。
有以下情况:
Id相同,执行顺序由上到下;
Id不同,如果是子查询,Id的序号会递增,Id越大优先级越高,越先被执行。
# 执行计划 - select_type
SIMPLE: 简单查询,不包含子查询和union
PRIMRARY: 包含子查询时的最外层查询; 使用union时的第一个查询
UNION: 包含union的查询中非第一个查询
UNION RESULT 临时结果
DEPENDENT UNION: 与 UNION 相同,但依赖外层查询的结果
SUBQUERY: 子查询
DEPENDENT SUBQUERY: 依赖外层查询的子查询
DERIVED: 用于 FROM 中的子查询(中间表)
# 执行计划 - type
type 字段描述了查询的方式,从好到坏为:
null: 不需要访问索引和表即可完成, 示例:
SELECT 1;
const: 表中仅有一行匹配,在分解查询计划时直接将其读出作为常量使用。system 是 const 类型的特例; 示例:
select * FROM qrtz_user WHERE Id=1;
eq_ref:使用 PRIMARY KEY 或 UNIQUE KEY 进行关联查询;
ref: 使用允许重复的索引进行查询;
range: 使用索引进行范围查询;
index: 在索引上进行顺序扫描。常见于在多列索引(组合索引)中未使用最左列进行查询;
all: 扫描全表,最坏的情况。
# 执行计划 - possible_keys
可能使用的索引。
# 执行计划 - keys
实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠
# 执行计划 - key_len
索引使用的字节数,相当于长度
char和varchar跟字符编码也有密切的联系,latin1占用1个字节,gbk占用2个字节,utf8占用3个字节。(不同字符编码占用的存储空间不同)
# 执行计划 - ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
# 执行计划 - rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
# 执行计划 - Extra
extra 列显示了查询过程中需要执行的其它操作,有些情况应尽力避免。
using filesort: 查询时执行了排序操作而无法使用索引排序。虽然名称为'file'但操作可能是在内存中执行的,取决是否有足够的内存进行排序。 应尽量避免这种filesort出现。
using temporary: 使用临时表存储中间结果,常见于ORDER BY和GROUP BY语句中。临时表可能在内存中也可能在硬盘中,应尽量避免这种操作出现。
using index: 索引中包含查询的所有列不需要查询数据表(回表)。可以加快查询速度。
using where: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给客户端。
using index condition: 索引条件推送(MySQL 5.6 新特性),服务器层将不能直接使用索引的查询条件推送给存储引擎,从而避免在服务器层进行过滤。
distinct: 优化distinct操作,查询到匹配的数据后停止继续搜索