SQL高级优化（四）之SQL优化

SQL优化

一、SQL优化简介

解释：对于特定的要求，使用更有的SQL策略或索引策略，以达到让结果呈现的时间更短，从而提升操作效率的过程就是SQL优化。

SQL优化包含在数据库级别优化中。我们平常所说的SQL优化就是指优化SQL语句和索引。

SQL优化是伴随业务而进行优化的，并不是下面的所有操作都必须都达到才是最好的优化。

二、常规调优思路（众多解决方案之一）

查看slow-log，分析slow-log，分析出查询慢的语句。

按照一定优先级，进行一个一个的排查所有慢语句。

分析top sql，进行explain调试，查看语句执行语句。

调整索引或语句本身。

MySQL日志支持

一、MySQL5.7中日志分类

MySQL日志分类四类：错误日志（回滚等）、二进制日志（主从）、通用查询日志（记录查询等信息）、慢查询日志

二、通用查询日志

通用查询日志是记录建立的客户端连接和执行的语句

通过show variables like '%version%';查看版本信息

可以通过show variables like '%general%'查看通用查询日志是否卡开启 general_loh 属性取值

- OFF 表示关闭（默认关闭）

- ON 表示打开

通过查看日志输出格式

- File存储在数据库的数据文件中的主机名.log C:\ProgramData\MySQL\MySQL Server 5.7\Data

- TABLE 存储在数据库中的mysql/general_log

临时开启/关闭通用日志（重启失效）

# 开启

set global general_log = on;

# 关闭

set global general_log = off;

临时设置输出格式（重启失效）

# mysql/general_log

set global log_output-'TABLE';

# 主机名-slow.log

set global log_output='FILE';

# 两者都输出

set global log_output='FILE,TABLE';

永久设置。修改MySQL配置文件

windows中日志文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini中。

按需配置

配置后重启MySQL服务

日志文件中时间和系统时间不一致问题

查看系统日志文件格式 show variables like '%log_timestamps%';

修改日志文件时间格式为系统时间 set global log_timestamps = SYSTEM

三。错误日志

MySQL错误日志记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL启动和关闭的详细信息。以及MySQL每次启动和关闭的详细信息。

通过show variables like '%log_error%';查看

binlog_error_action 错误处理方式

ABORT_SERVER出现问题终止服务

IGNORE_ERROR忽略错误

log_error错误日志文件名及路径

log_error_verbosity记录级别

取值1表示记录警告信息

大于1表示所有警告信息都记录

日志文件可以通过文本编辑器打开。

四、二进制日志

包含所有更新数据（新增、删除、修改、改表等）SQL信息的记录。

MySQL主从配置就依赖这个日志文件

通过show variables like '%log_bin%';查看二进制日志

二进制日志不可以通过修改全局参数开启。全局匹配值问下（my.ini）中该参数是注释的。

# binary Logging

# log-bin

直接设置log-gin的值为日志文件名。

# Binary Logging

log-gin=mylogbin

设置后重启MySQL服务会发现log_bin参数值为ON

开启后二进制文件存储在C:\ProgramData\MySQL\MySQL Server 5.7\Data。里面有个xxx.index文件（这个文件成为二进制文件索引）里面存储了所有二进制文件清单。当重启MySQL服务或过一定时间后会自动生成一个二进制文件。增加的二进制文件编号递增。也可以使用flush logsl命令生成一个新的二进制文件。

也可以通过命令：show binary log;查看目前生成的日志文件。

由于是二进制文件，所以无法直接使用文本编辑器打开，需要借助工具才可以看见。

在navicat或sql命令中输入mysql show binlog events in 'mylogbin.000003';

在命令行输入，借助mysqlbinlog工具

输入命令式要在日志文件所在文件夹路径中运行，也可以在命令中写上日志文件全路径

D：/a.sql可以没有，没有表示直接大隐刀控制台mysql mysqlbinlog mylogbin.000003 > D:/a.sql

binlog中除了删除IAO创建表的SQL都是加密的，如果希望看见可以使用下面命令

直接打印到控制台会出现中文乱码mysql mysqlbinlog -base64-output=deode-rows -v mysqlbin.000003 > D:/a.sql

五、慢查询日志

记录所有执行时间超过long_query_time秒的所有查询或不适用于索引的查询

long_query_time默认时间为10秒。即超过10秒的查询都认为是慢查询

慢查询日志默认名称：住进-slow.log

除了查看my.ini文件以外通过show variables like '%query%';查看

- slow_query_log 表示是否开启慢查询日志。（默认开启）

- slow_query_log_file 慢查询日志文件名

- long_query_time慢查询阈值设置，查处为慢查询。此值直接设置全局参数可能无效，建议测试直接修改配置文件。

- log_queries_not_using_index是否记录不适用于索引的查询（前提是slow_query_log开启）

- 可以通过命令查看慢查询日志中慢查询SQL命令的个数。也可以使用文本编辑器直接打开慢查询日志文件。也可以查看mysql.slow_log表中的额数据（前提log_output值包含table）

show status like '%slow_queries%';

巴特西

SQL高级优化（四）之SQL优化

SQL优化

一、SQL优化简介

二、常规调优思路（众多解决方案之一）

MySQL日志支持

一、MySQL5.7中日志分类

二、通用查询日志

三。错误日志

四、二进制日志

五、慢查询日志

最新文章

热门文章