跳转至

引言

官方手册

MySQL 在设计上受 Oracle 影响颇深

MySQL 是典型的 C/S 架构

20220809201603

  • 启动/停止服务
# Mac
sudo mysql.server start/stop
# 也可以通过系统设置面板控制
# 如果是 homebrew 安装的，也可以通过 brew services 控制

# Windows
net start/stop mysql

客户端

可以通过命令行或者GUI管理工具来与服务端交互

命令行

# 连接服务
mysql -h$ip -P$port -u$user -p  # -uroot 这样连着写也没有问题
mysql -h 127.0.0.1 -u root -p  # 12345678
# -h 默认主机名为本机 127.0.0.1
#   不可以与localhost混用
#       当设置为 127.0.0.1 时，系统通过 TCP/IP 方式连接数据库
#       当设置为 localhost 时，系统通过 socket 方式连接数据库
# -P 默认端口号 3306
# -u 指定用户名，默认为本机 user 名
# -p 带此参数表明需要密码，反之不需要
#   回车后密文输入
#   也可以将密码直接写在 -p 命令后，但不建议这样，容易泄漏

SELECT VERSION();  # 查看版本
SELECT NOW()  # 查看当前时间

show databases;  # exit
create database;
use demo;
show tables;

# 断开连接
exit  # 或者quit

GUI 管理工具

  • 官方：Workbench
  • 免费：DBeaver、SQLyog 等 小巧，够用
  • 付费：Navicat、DataGrip 强大
  • 在线：phpMyAdmin 需要自己搭建

服务端

20220809202624

mysqld 可分为三层

连接层

负责与客户端交互，管理连接，权限验证

建立连接时，用户名密码认证通过，连接器会到权限表里面查出用户所拥有的权限，然后将用于此次连接中的所有与权限相关的逻辑处理，也就是说如果管理员对用户修改了权限，需要重新连接才会生效。

20241014214124

连接后没有动作，将处于空闲状态（Sleep），如果太长时间没动作（由 wait_timeout 控制，默认 8h），连接器就会自动断开

  • 短连接，是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个
  • 长连接，是指连接成功后，如果客户端持续有请求，则一直使用同一个连接

建立连接的过程比较复杂，所以通常建议使用长连接，但由于内存资源在断开连接时才会释放，所以长连接会使内存占用不断增加，导致过大而被系统强行杀掉重启

优化方式

  • 使用一段时间，定期断开长连接
  • 执行一个比较大的操作后，就断开连接，之后要查询再重连
  • 执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源（不需要重连和重新做权限验证，将连接恢复到刚刚创建完时的状态）

SQL 层

对 SQL 语句进行查询处理，涵盖 MySQL 的大多数核心服务功能

  • ~~查询缓存：命中则直接返回结果~~
  • 解析器（要做什么）：对 SQL 语句进行词法和语义分析
  • 优化器（要怎么做）：生成执行计划，确定 SQL 语句的执行路径
    • 比如，表里面有多个索引的时候，决定使用哪个索引
    • 比如，语句有多表关联的时候，决定各个表的连接顺序
  • 执行器（开始执行）：在执行之前需要判断该用户是否具备权限，如果具备权限就执行并返回结果

MySQL 8.0 以下的版本，如果设置了查询缓存，执行器会将查询结果以 key-value 对的形式进行缓存，key 是查询的语句，value 是查询的结果。

MySQL 8.0 及以上版本，已经删除了缓存功能，因为当一个表被更新，所有缓存都会被清空，以至于缓存利用率过低。

存储引擎

存储数据，提供读写接口

不同存储引擎的表数据存取方式不同，支持的功能也不同

MySQL 的存储引擎采用了插件形式，允许开发人员设置自己的存储引擎。

注意：同一数据库的不同表也可以采用不同的存储引擎

  • Memory：使用系统内存作为存储介质，以便得到更快的响应速度。但如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
  • MyISAM：MySQL 5.5 版本之前默认的存储引擎，是 MySQL 自带的引擎，最大的特点是速度快，占用资源少，但不支持事务，也不支持外键。
  • InnoDB：MySQL 5.5 版本之后默认的存储引擎，是 Innobase Oy 公司开发，以插件形式引入 MySQL 的，最大的特点是支持事务、行级锁定、外键约束等。
  • NDB Cluster，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
  • Archive：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

WAL 机制

WAL(Write-Ahead Logging 预写日志) 是一种为数据库系统提供原子性和持久性的技术族（ACID 中的 A 和 D），要求所有更新操作在写入磁盘前，都需要先预写到效率更高的日志中。目的是，当程序在执行某操作时遇到崩溃或者断电等异常重启后，可以通过检查日志来恢复正常（crash-safe）。

就像记账时，先写到里临时的小黑板上，空闲时再整理到总账上。

MySQL 使用 redo log（重做日志）和 binlog（归档日志）实现 WAL 机制

MySQL 自带的引擎 MyISAM 没有 crash-safe 能力，所以不支持事务，后来通过插件形式引入 InnoDB 引擎，拥有了 redo log 才有了 crash-safe 能力并得以支持事务。

20241015225716

浅绿色表示在 InnoDB 中执行
深绿色表示在执行器中执行

binlog 是逻辑日志，处于执行器中，记录的是这个语句的原始逻辑，可以追加写入

Timestamp: 1633456789
Event Type: QUERY
Server ID: 1
End_log_pos: 256
SQL Statement: INSERT INTO employees (name, position) VALUES ('John Doe', 'Engineer');

redo log 是物理日志，记录的是在某个数据页上做了什么修改，空间固定会用完，所以是循环覆盖写入

LSN: 1001
Type: INSERT
Space ID: 5
Page Number: 3
Offset: 128
Data Length: 30
Data: [Binary data representing the row change]

20241015230446

write pos 是当前记录的位置，一边写一边后移
checkpoint 是当前要擦除的位置

下载安装

官方下载链接

Mac

选择 LTS 版本，M1+ 选择 ARM OS 版本，点击 No thanks, just start my download 下载，安装时，一直下一步即可

最后会默认创建 root 账户，让你设置一个密码（最少 8 位）

加密方式

  • mysql_native_password 使用明文（Use Legacy Password Encryption）
  • caching_sha2_password，8.0 版本新增，某些第三方客户端连接数据库时不兼容新密码验证方式可能会报错

然后配置环境变量

export PATH=$PATH:/usr/local/mysql/bin
export PATH=$PATH:/usr/local/mysql/support-files

Windows

下载后是个压缩包，无需安装，解压后做一些相关配置即可使用

首先在解压后文件的目录下，新建一个配置文件：my.ini，然后设置以下内容

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录，注意，这里要用两个反斜线\\
basedir=C:\\path\\mysql-8.0.27-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\path\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

然后设置环境变量

  • MYSQL_HOME:安装路径
  • Path:%MYSQL_HOME%\bin

然后以管理员身份打开命令行，输入：mysqld --initialize --console，初始化完毕后会生成一个初始密码（记得保存）

然后输入：mysqld install，注册服务，成功后即可启动服务登录使用

Docker

镜像

docker pull mysql:8.0
sudo docker run --name mysql8
    -e MYSQL_ROOT_PASSWORD=passwd \  # 设置root用户的密码，此参数必选
    --network host \  # 绑定网络，方便其它容器使用mysql服务，这里绑定到host网络
    -d mysql:8.0

# 默认创建'root'@'localhost'帐户，设置密码，三种方式必选其一：
    # 1. 固定密码（出于安全考虑不建议这样）：-e MYSQL_ROOT_PASSWORD=passwd
    # 2. 密码为空：-e MYSQL_ALLOW_EMPTY_PASSWORD=yes
    # 3. 随机生成密码（推荐）：-e MYSQL_RANDOM_ROOT_PASSWORD=yes
        '''
        从容器日志中查找密码：sudo docker logs mysql8 2>&1 | grep GENERATED
        连接服务：sudo docker exec -it mysql8 mysql -u root -p
        重置密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

        默认创建的root用户貌似只能在容器内使用，无法被外网或其它容器访问（即使设置了host=%）
        可以新创建一个用户
            USE mysql;
            CREATE USER 'demo'@'%' IDENTIFIED BY 'password';
            flush privileges;
        '''
# 数据存放路径：/var/lib/mysql
#   默认挂载在宿主机的：/var/lib/docker/volumes/<container_id>，需要root权限才能访问
#   可以挂载到自定义的路径（不能使用被其它容器已占用的路径）：-v /my/own/datadir:/var/lib/mysql
# 配置文件路径：/etc/mysql/my.cnf，如果想用自定义的配置文件启动可以-v挂载

# 映射到宿主机的23306端口，避免与宿主机数据库端口冲突：-p 23306:3306
# 使用host网络模式则无法也无需指定

# 连接到容器
docker exec -it mysql8 mysql -uroot -p
mysql -h 127.0.0.1 -u root -p  # 使用 -h localhost 可能无法连接