MySQL体系结构与基础日志

于 2022-04-11 23:44:44 发布
阅读量663 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/pass_x/article/details/124112818
本文详细介绍了MySQL的体系结构，包括单进程多线程架构、存储引擎和配置文件。重点讲解了常用日志类型如错误日志和慢查询日志的配置参数，以及如何清理慢查询日志。此外，还探讨了存储引擎的概念，强调InnoDB的重要性，并简要提及其他历史遗留的存储引擎。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、MySQL体系结构

  • MySQL是一个单进程多线程的架构
    • Like Microsoft SQL Server
    • Oracle多进程架构（except Windows）
  • 插件式存储引擎架构
  • 逻辑存储结构
    • 一个database对应schema
    • 一个database对应多个tables
  • 物理存储结构
    • 一个mysqld实例操作对应datadir目录
    • 一个数据库对应一个目录
    • 一张表对应多个文件
    • 注：information_schema数据库是没有目录的，为映射在内存中的信息

请添加图片描述

  • mysql服务结构，当客户端发起请求之后，mysqld如何处理? 见附录1（答案来源于网络）

MySQL配置文件

  • 之前几节也就写过一些简单的标签，会随着深入逐渐补充

  • [mysqld-5.6]：在此标签下写的内容只会在启动mysql5.6版本实例才会生效

  • 类似的[mysqld-5.7]：在此标签下写的内容只会在启动mysql5.7版本实例才会生效

  • [mysqldump]、[mysqladmin]：执行相关命令会加上的参数

表结构定义文件

  • 每个表对应一个表结构文件

    • 在mysql内可用show create table tablename;或desc tablename;查看

  • 表结构文件名以.frm结尾

  • 表结构文件是二进制文件

    • 可以使用mysqlfrm --diagnostic tablename.frm查看，但字符编码有些问题

    • 并不是Mysql自带的命令，需要下载MySQL Utilities工具包，提供一组命令行工具用于维护和管理 MySQL 服务器（python编写，解压后python setup.py install安装）

      在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

  • 警告：如果没有–server选项，则无法生成字符集或排序规则名称。

在这里插入图片描述

  • 可以使用–server指定连接到服务器，格式为user[:password]@host[：port][:]或login path[:port][:]

在这里插入图片描述

二、常用日志及配置参数

错误日志

  • 参数：log_error

    • 默认为：机器名.err
    • 建议统一修改成一个固定的名称，例如mysql.err
      在这里插入图片描述

  • 可将配置错误日志到系统日志文件（不推荐）

    [mysqld_safe]
syslog
syslog_tag=stock#mysqld_stock

慢查询日志

  • 将运行超过某个时间阈值的SQL语句记录到文件。（不包括锁等待值）
    • 默认名：机器名-slow.log
  • 各版本的慢查询特性
    • MySQL5.1开始可以以毫秒为单位记录运行的SQL语句
    • MySQL5.5开始可以将慢查询保存到表
    • MySQL5.6开始可以更细粒度的记录慢查询
    • MySQL5.7将时区信息写入到慢查询日志
相关参数
参数说明版本
slow_query_log是否开启慢查询日志
slow_query_log_file慢查询日志文件名
long_query_time指定慢查询阈值5.5（毫秒支持）
min_examined_row_limit扫描记录少于该值的SQL不记录到慢查询日志
log-queries-not-using-indexes将没有使用索引的SQL记录到慢查询日志
log_throttle_queries_not_using_indexes限制每分钟记录没有使用索引SQL语句的次数5.6
log-slow-admin-statements记录管理操作，如ALTER/ANALYZE TABLE
log_output慢查询日志的格式，{FILE|TABLE|NONE}5.5
log_slow_slave_statements在从服务器上开启慢查询日志
log_timestamps写入时区信息5.7
前三个参数的演示
  • 在配置文件中输入以下三条配置
[mysqld]
...
slow_query_log=on
slow_query_log_file=slow.log
long_query_time=2
  • 未重启服务，登录查看默认值
(root@localhost)  [(none)]> show variables like 'slow%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | OFF                         |
| slow_query_log_file | /mysql/data/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

(root@localhost)  [(none)]> show variables like 'long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
  • 重启后重新登录mysql查看变量
(root@localhost)  [(none)]> show variables like 'slow%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_launch_time    | 2        |
| slow_query_log      | ON       |
| slow_query_log_file | slow.log |
+---------------------+----------+
3 rows in set (0.01 sec)

(root@localhost)  [(none)]> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
  • 使用select sleep(2);验证
(root@localhost)  [(none)]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.01 sec)

(root@localhost)  [(none)]> exit
Bye
[root@mysql1 ~]# cat /mysql/data/slow.log 
/app/mysql/bin/mysqld, Version: 5.7.20-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2022-04-11T13:56:09.916929Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 2.010164  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1649685369;
select sleep(2);
线上如何清理慢查询日志

  • 如果仅仅使用> slow.log清空慢查询日志，会发现磁盘空间并没有释放。因为慢查询日志的文件句柄没有释放。

  • 要先将原慢查询日志改名

[root@mysql1 data]# mv slow.log  slow.log.bak
  • 然后在mysql中执行flush slow logs; 此命令会关闭原来的文件句柄后重新创建新的文件句柄。
(root@localhost)  [(none)]> flush slow logs;
Query OK, 0 rows affected (0.00 sec)
  • 然后就可以删除更名后的慢查询日志。
[root@mysql1 data]# ll slow.log*
-rw-r----- 1 mysql mysql 178 Apr 11 22:01 slow.log
-rw-r----- 1 mysql mysql 204 Apr 11 22:01 slow.log.bak
[root@mysql1 data]# rm slow.log.bak 
rm: remove regular file ‘slow.log.bak’? y
其余的参数介绍

  • min_examined_row_limit：至少要检测/扫描多少行才会加入到慢查询日志

    • 例如有些存储元数据信息的表，定期查看影响不是很大的情况下，可以不加入慢查询，这是就可以使用此参数

  • log-queries-not-using-indexes：对没有使用索引的语句，加入慢查询日志

    • 建议加上此参数
    • 理由：测试环境下的数据量较小，而线上环境随着业务量的增加，不使用索引的影响就会很大了
    • 可能的问题：但是一些已经使用索引，但使用了一些嵌套查询的也会记录下来，根据实际情况考虑是否启用

  • log_throttle_queries_not_using_indexes：若没有使用索引的SQL语句反复执行，每分钟限制其记录的次数

    • 目的：防止slow_log增长过快

  • log-slow-admin-statements：记录一些的管理操作

  • log_slow_slave_statements:在从机上开启慢查询日志

  • log_timestamps：写入时区信息默认记录UTC格式的时间，早8个小时

    • 建议都改为系统时间log_timestamps = system
    • 注意，此参数5.7之后才生效，所以建议配置到[mysql-5.7]标签下

补：

  • bind-address：绑定Ip地址
    • 一般情况下不允许外网直接访问数据库，都是应用通过内网访问数据库
    • MySQL只能绑定在一个IP上，否则只能设为*
    • 例
      • bind-address=127.0.0.1 #只允许本机访问。
      • bind-address=某个网卡的ip #例如bind-address=192.168.80.10，只能通过ip为192.168.80.10的网卡访问。
      • bind-address=* #此规则是系统默认配置，监听所有网卡，即允许所有ip访问。
log_output参数
  • 将log_output单独拎出来讲，因为它通用
    • 慢查询日志的格式，{FILE|TABLE|NONE}
    • file默认值：以文件的形式存储在磁盘上
    • table：以表的形式存储在mysql.slow_log中。但是不建议，下面会讲原因
(root@localhost)  [(none)]> show variables like 'log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

(root@localhost)  [(none)]> set global log_output='table';
Query OK, 0 rows affected (0.00 sec)

(root@localhost)  [(none)]> show variables like 'log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

(root@localhost)  [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
# 语句看表结构不直观，直接截图了

在这里插入图片描述

  • 在表中可以看到慢查询日志的表结构
(root@localhost)  [mysql]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.01 sec)

(root@localhost)  [mysql]> select * from slow_log\G
*************************** 1. row ***************************
    start_time: 2022-04-11 22:28:24.198969
     user_host: root[root] @ localhost []
    query_time: 00:00:02.009995
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: mysql
last_insert_id: 0
     insert_id: 0
     server_id: 6
      sql_text: select sleep(2)
     thread_id: 5
1 row in set (0.00 sec)
  • 虽然方便查询，但是存储到表中的开销较大，而且备份的时候，容易连同慢查询日志一直备份过去（而一般慢查询日志又大又不重要）。所以看具体需求，一般不建议
配置文件新增（慢查询相关）
[mysqld]
...
# slow_query_log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
min_examined_row_limit = 100
log-queries-not-using-indexes
log_throttle_queries_not_using_indexes = 10
log-slow-admin-statements
log_slow_slave_statements
[mysqld-5.7]
log_timestamps = system

通用日志

  • 可以记录数据库所有相关操作
    • 参数general_log
    • 默认文件名：机器名.log
  • 同样可以将日志保存到表
    • mysql.general_log
  • 开启性能下降明显
    • 啥都记性能可不是下降吗
    • 但是对一些银行或者对一些要求特别严格的企业会要求

三、MySQL存储引擎介绍

注：innodb存储引擎会单独放到后面讲

存储引擎的概念

  • 简单讲就是用来处理数据库的相关的CRUD操作
  • 每个数据库都有存储引擎的概念
    • Oracle数据库没有刻意强调此概念，但用如下表来组织
      • 堆表
      • 簇表
      • 索引组织表

MySQL存储引擎

注：现在除了MyISAM还有点历史残留，InnoDB几乎适用于所有场景。其他的引擎看看就行。最关键的，其他存储引擎已经不再维护和开发，大部分都是MySQL内部自己用。

  • 官方存储引擎：MyISAM、InnoDB、Memory、Federated、CSV、Archive

  • 第三方存储引擎：TokuDB 、lnfoBright、Spider

  • show engines;查看当前存储引擎

    • Federated默认没有开启，需要在配置文件中添加federated

在这里插入图片描述

  • 启动时可以禁用某些存储引擎
    • 例--skip-archive、--skip-blackhole
逐步成为历史的MyISAM

  • MySQL 5.1版本之前的默认存储引擎·堆表数据结构

  • 表锁设计，并发度会很差

  • 支持数据静态压缩

  • 不支持事务

  • 数据容易丢失

  • 索引容易损坏

  • 唯一的优点：数据文件可以直接拷贝到另一台服务器使用

  • 数据文件 .MYD

  • 索引文件.MYI

  • 修复错误数据表myisamchk

看看就好——Memory

  • 全内存存储的引擎（可以用redis呀）

  • 数据库重启后数据丢失

  • 支持哈希索引

  • 不支持事务

  • 为什么不淘汰？

    • Memory存储引擎是不能禁用的

    • MySQL内部的排序操作的临时表使用该存储引擎

  • 参数max_heap_table_size决定使用的大小

    • 默认大小:16M
    • 内存放不下时转为MylSAM表
    • 注意临时路径空间大小
    • 内存使用为会话级别，小心OOM

日志专业——CSV

  • CSV（Comma-SeparatedValues）是一种标准文件格式
  • 文件以纯文本形式存储表格数据，以,逗号分割。
  • 不支持特殊字符
    在这里插入图片描述
    在这里插入图片描述

看看就好——Federated

  • 允许本地访问远程MySQL数据库中表的数据
  • 本地不存储任何数据文件
  • 类似于Oracle中的透明网关
    • 仅支持MySQL=>MySQL的访问
    • 不支持异构数据库的访问
  • Fedorated存储引擎默认不开启
    • 配置文件federated
  • 异构数据库远程访问
    • MariaDB FedoratedX

附录1

MySQL服务结构，当客户端发起请求之后，mysqld如何处理（面试可能会问吧…）

请添加图片描述

连接层 
1. 提供连接协议TCP/IP，socket两种连接方式
2. 提供用户名和密码认证
3. 提供专用连接线程接收用户的SQL并返回结果到SQL层

SQL层 
1. 接收上层的SQL语句验证语法
2. 并判断语句类型，DDL，DCL，DML，DQL
3. 对用户是否有对库表的权限进行检查
4. 然后在语句执行前，用解析器对语句预处理，生成解析树（多种执行方案）
5. 优化器根据解析器的多种执行计划，选择最优(cpu 内存 存储开销最小)，执行器根据最优执行计划，执行SQL语句，产生执行结果，
6. 执行完后提供查询缓存
7. 执行完后提供日志记录（bin err...）

存储引擎层 
1. 负责根据SQL层执行的结果，从磁盘上拿到数据
2. 将16进制的磁盘数据，交给SQL结构化化成表，并通过连接层的专用线程返回给用户
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值