mysql8基础（十四）SQL技巧、常用工具与日志

文章目录1. 常用SQL技巧1.1 SQL编写顺序与逻辑处理顺序1.2 正则表达式2. SQL常用函数2.1 字符串函数2.2 日期函数2.3 聚合函数3. mysql常用工具3.1 mysql客户端直接执行SQL3.2 mysqladmin管理程序3.3 mysqlbinlog日志管理工具3.4 mysqldump备份迁移工具3.5 mysqlshow对象查找工具4. MySQL日志4.1 错误日志4.2 二进制日志4.2.1 概述4.2.2 日志格式4.2.3 日志读取4.2.4 日志删除4.3 通用查询日志4.4 慢查询日志4.4.1 文件位置和配置4.4.2 日志的读取1. 常用SQL技巧1.1 SQL编写顺序与逻辑处理顺序selectdistinct...from左表join右表on连接条件where...groupby...having...orderby...limit...SQL逻辑处理顺序from左表on连接条件join右表where...groupby...having...selectdistinct...orderby...limit...1.2 正则表达式2. SQL常用函数运算函数2.1 字符串函数2.2 日期函数2.3 聚合函数3. mysql常用工具3.1 mysql客户端直接执行SQL3.2 mysqladmin管理程序3.3 mysqlbinlog日志管理工具3.4 mysqldump备份迁移工具-T参数导出两份文件一份为sql格式的创建语句一份为txt的格式存放数据mysqldump-uroot-p123-T/tempbook aamysqlimport导入工具导入-T参数导出的txt文件mysqlimport-uroot-p123 book/temp/aa.txt导入sql文件source/目录/aa.sql3.5 mysqlshow对象查找工具4. MySQL日志4.1 错误日志错误日志是 MySQL中最重要的日志之一它记录了当mysqld 启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时可以首先查看此日志。该日志是默认开启的默认存放目录为mysql的数据目录,默认的日志文件名为hostname.err(hostname是主机名)。查看日志位置指令showvariableslikelog_error%;4.2 二进制日志4.2.1 概述二进制日志BINLOG记录了 MySQL 中引起数据变化的 DDL数据定义语言和 DML数据操作语言事件一般不记录普通的SELECT查询。Binlog 对数据恢复和主从复制都非常重要MySQL 的主从复制就是通过 Binlog 实现的。MySQL 8 中 Binlog 默认已经开启可以通过下面的命令查看SHOWVARIABLESLIKElog_bin;还可以设置过期时间# 过期时间 单位秒binlog_expire_logs_seconds2592004.2.2 日志格式查看当前二进制日志格式SHOWVARIABLESLIKEbinlog_format;格式记录内容特点STATEMENT记录实际执行的 SQL 语句日志量通常较小、可读性较好复制时从库重新执行这些 SQL但某些非确定性语句可能带来主从不一致风险。ROW记录每一行数据实际发生的变化而不是原始 SQL数据一致性更可靠但批量更新大量数据时日志量可能较大。MySQL 8 默认使用该格式。MIXEDSTATEMENT 与 ROW 混合使用通常优先使用 STATEMENT在某些不适合语句级复制的场景自动切换为 ROW以兼顾日志量和复制可靠性。4.2.3 日志读取随便插入条语句并查询执行mysqlbinlog.exe binlog.000305但是看到的不是原始SQL使用mysqlbinlog.exe -vv binlog.000305查看4.2.4 日志删除方式示例说明删除全部 BinlogRESET MASTER;MySQL 8.0 中可用会删除当前所有 Binlog并重新从新的日志文件开始。MySQL 8.4 中已改为RESET BINARY LOGS AND GTIDS;。删除指定文件之前的日志PURGE BINARY LOGS TO binlog.000100;删除binlog.000100之前的所有 Binlogbinlog.000100本身会保留。删除指定时间之前的日志PURGE BINARY LOGS BEFORE 2026-09-01 00:00:00;删除指定时间之前生成的 Binlog。自动过期清理binlog_expire_logs_secondsMySQL 可以按照设置的过期时间自动清理旧 BinlogMySQL 8 默认过期时间为 30 天。例如-- 删除指定日志文件之前的 BinlogPURGEBINARYLOGSTObinlog.000100;-- 删除指定时间之前的 BinlogPURGEBINARYLOGS BEFORE2026-09-01 00:00:00;-- 查看自动过期时间SHOWVARIABLESLIKEbinlog_expire_logs_seconds;4.3 通用查询日志通用查询日志General Query Log会记录客户端连接、断开以及 MySQL Server 接收到的 SQL 语句包括查询和数据修改操作。与 Binlog 不同普通的SELECT查询也会记录在通用查询日志中。通用查询日志默认关闭可以通过以下命令查看SHOWVARIABLESLIKEgeneral_log;查看日志文件位置SHOWVARIABLESLIKEgeneral_log_file;暂时开启SETGLOBALgeneral_logON;长期启用可以在 MySQL 配置文件的 [mysqld] 节点中配置[mysqld]# 开启通用查询日志general_log1# 日志文件名general_log_filemysql_query.log重启后执行SQL打开日志查看4.4 慢查询日志慢查询日志Slow Query Log主要用于记录执行时间较长的 SQL方便定位和优化慢 SQL。默认情况下一条 SQL 满足以下条件时会被记录到慢查询日志执行时间 long_query_time并且扫描行数 min_examined_row_limit。其中long_query_time慢查询时间阈值MySQL 8 默认值为 10 秒最小可设置为 0并支持微秒级精度。min_examined_row_limit最少扫描行数只有扫描行数达到该值的 SQL 才会被记录。慢查询日志默认关闭。4.4.1 文件位置和配置可以在 MySQL 配置文件的 [mysqld] 节点中配置[mysqld]# 开启慢查询日志slow_query_log1# 慢查询日志文件名slow_query_log_fileslow_query.log# 超过2秒认为是慢查询long_query_time2修改配置后记得重启。如果没有指定绝对路径日志文件默认写入 MySQL 数据目录。可以通过以下命令查看当前配置-- 查看慢查询日志是否开启ON 表示开启OFF 表示关闭SHOWVARIABLESLIKEslow_query_log;-- 查看慢查询日志文件的保存路径SHOWVARIABLESLIKEslow_query_log_file;-- 查看慢查询时间阈值执行时间达到该值的 SQL 才可能被记录SHOWVARIABLESLIKElong_query_time;-- 查看最少扫描行数扫描行数达到该值的 SQL 才可能被记录SHOWVARIABLESLIKEmin_examined_row_limit;4.4.2 日志的读取为了方便测试给表中插入500W条数据慢查询日志的输出方式由 log_output 控制-- 查看日志输出方式可取 FILE、TABLE、FILE,TABLE 或 NONESHOWVARIABLESLIKElog_output;常见取值FILE → 写入日志文件 TABLE → 写入 mysql.slow_log 表 FILE,TABLE → 同时写入文件和表使用 FILE 方式可以直接打开慢查询日志文件查看。如果使用 TABLE 方式可以执行SELECT*FROMmysql.slow_log;查看慢查询记录。