SQL优化

一、SQL优化简介

  • 解释：对于特定的要求，使用更有的SQL策略或索引策略，以达到让结果呈现的时间更短，从而提升操作效率的过程就是SQL优化。
  • SQL优化包含在数据库级别优化中。我们平常所说的SQL优化就是指优化SQL语句和索引。
  • SQL优化是伴随业务而进行优化的，并不是下面的所有操作都必须都达到才是最好的优化。

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

  • 查看slow-log，分析slow-log，分析出查询慢的语句。
  • 按照一定优先级，进行一个一个的排查所有慢语句。
  • 分析top sql，进行explain调试，查看语句执行语句。
  • 调整索引或语句本身。

MySQL日志支持

一、MySQL5.7中日志分类

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

二、通用查询日志

  • 通用查询日志是记录建立的客户端连接和执行的语句
  • 通过show variables like '%version%';查看版本信息

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

  • 可以通过show variables like '%general%'查看通用查询日志是否卡开启 general_loh 属性取值
    • OFF 表示关闭（默认关闭）
    • ON 表示打开

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

  • 通过查看日志输出格式
    • File存储在数据库的数据文件中的主机名.log C:\ProgramData\MySQL\MySQL Server 5.7\Data
    • TABLE 存储在数据库中的mysql/general_log

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

  • 临时开启/关闭通用日志（重启失效）
# 开启
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';

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

  • 永久设置。修改MySQL配置文件
  • windows中日志文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini中。
  • 按需配置

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

  • 配置后重启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%';查看二进制日志

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

  • 二进制日志不可以通过修改全局参数开启。全局匹配值问下（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%';查看

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

    • 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优化_MySQL_08
作者：张亚飞
gitee：https://gitee.com/zhangyafeii
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。