怪兽和他的朋友们

Monster

0%

浅谈 MariaDB 的存储引擎和日志

发表于 分类于
本文字数： 9.9k 阅读时长 ≈ 9 分钟

关于 MariaDB 系列其他文章的传送门

  1. 《 浅谈 MariaDB 的存储引擎和日志 》　　　　　　您当前所在位置

  2. 《 初识 MariaDB 的主从复制 》

  3. 《 未雨绸缪，快速学习 MariaDB 的备份与恢复，包含 InnoDB 优化参数 》

logo

一、常见开源数据库的发行分支

MySQL：

  • 社区版本：5.5, 5.6, 5.7, 8.0；
  • 存储引擎使用 InnoDB，不过 InnoDB 也被 ORACLE 收购了；
  • Innobackup 是 MySQL 的备份工具，收费昂贵。

Percona-Server：

  • 存储引擎使用 XtraDB，是 Percona 公司研发的 InnoDB 的增强版，并且全面兼容 InnoDB；
  • Xtrabackup 是开源的备份工具，打击了 Innobackup；
  • percona-tools: 开源的数据库的工具包。

MariaDB：

  • MySQL 的分支，版本：5.5 对应 MySQL 的 5.5，而 MariaDB 没有所谓的 5.6，而是直接将后面的定位到 10.X；
  • 存储引擎默认使用 XtraDB，在 CentOS 7 自带的 YUM 源中的 MariaDB 就使用此引擎。使用 SHOW ENGINES; 查看存储引擎时显示的虽然是 InnoDB，但是描述里有写实际是 XtraDB，因为完全兼容，所以引擎名称为 InnoDB。但是实际上我们使用的是 Percona 公司的 XtraDB 存储引擎。

mysql_percona_mariadb

本文主要介绍 MariaDB

二、MariaDB 的安装

MariaDB 5.5 被收录在 Base 仓库，使用 yum 可直接安装。

1
yum install mariadb-server -y

注意事项：

  • MariaDB 是单进程多线程。
  • 第一次运行时后使用 mysql_secure_installation 执行安全初始化。
  • 数据文件保存在 /var/lib/mysql 下，每个数据库对应一个目录。
  • 使用客户端工具连接本地数据库时使用 mysql -uroot -hlocalhost，也可直接执行 mysql 默认效果是一样的。

三、存储引擎介绍

存储引擎也称为“表类型”，表级别概念，不建议在同一个库中的表上使用不同的 ENGINE；

SHOW ENGINES; 列出存储引擎的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

本文介绍两个存储引擎：

  • InnoDB
  • MyISAM

1. InnoDB

innodb

InnoDB 是 InnoBase 公司参考 ORACLE 模型开发的数据库存储引擎，后被 ORACLE 收购。

现在是 MySQL 和 MariaDB 的默认存储引擎。

特点是支持事务，行级锁和外键约束，InnoDB 是 事务型存储引擎，适合对事务要求较高的场景，比如在线交易系统的电商，较适用于处理大量短期事务；而冗长的事务并不适合 InnoDB 和 MariaDB，或许需要使用 ORACLE 或 PgSQL 吧。

InnoDB 的数据是存储于 表空间 (table space) 当中的，表空间有以下两种工作方式：

  1. innodb_file_per_table=OFF：所有数据库中的所有类型为 InnoDB 的表的数据和索引，都存储于同一个共享表空间中；一个表空间对应于磁盘上一个或多个串行的文件，该文件是一个黑盒，初始位于 /var/lib/mysql/ibdata1；
  2. innodb_file_per_table=ON：每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中，并存储于数据库目录下；同样支持一个表空间对应于磁盘上一个或多个串行的文件；

InnoDB 支持多版本并发控制 MVCC (Mutli Version Concurrency Control)；

  • 支持高并发；
  • 支持四个隔离级别，默认级别为 REPEATABLE-READ；
  • 锁的颗粒度：支持 行级锁 和 间隙锁，防止幻读；

支持使用 聚集索引，默认构建在主键索引上。聚集索引就是数据和索引保存在一起，找到数据即找到索引，而非聚集索引在找到索引后还需要依靠指针，再额外加载数据所在的磁盘块，才能加载数据。

支持 B 树索引和 Hash 索引：

  • B 树索引：也叫平衡树，特性是所有文件路径的层级深度一致。相反，Linux 中各文件的目录深度就各不相同。
  • 自适应 Hash 索引：就是把数据通过 Hash 计算后放在 Hash 桶中，当查询时，计算出查询数据的 Hash 值，当做查询条件，和 Hash 桶内的数据做命中查询。

总结：

  • 数据存储：表空间；
  • 并发：MVCC，间隙锁，行级锁；
  • 索引：聚集索引、辅助索引；
  • 性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
  • 备份：支持热备；

2. MyISAM

MyISAM 存储引擎支持全文索引（FULLTEXT index）、数据压缩、空间函数（GIS）索引。

  • 不支持事务、外键。
  • 锁的颗粒度为：表级锁。
  • 如果数据库发生崩溃无法保证表安全恢复，MyISAM 的衍生版 Aria 支持崩溃后表的安全恢复。

特性：

  • 加锁和并发：表级锁；
  • 修复：手动或自动修复、但可能会丢失数据；
  • 索引：非聚集索引；
  • 延迟索引更新；
  • 表压缩；

数据文件：每个表有三个文件，存储于数据库目录中：

  • tbl_name.frm：表格式定义；
  • tbl_name.MYD：数据文件；
  • tbl_name.MYI：索引文件。

适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）。

其它的存储引擎

MariaDB 自带的可切换的其他引擎：

数据引擎名 介绍
CSV 将 CSV 文件（以逗号分隔字段的文本文件）作为 MySQL 表文件。
MRG_MYISAM 将多个 MyISAM 表合并成的虚拟表。
BLACKHOLE 类似于 /dev/null，不真正存储数据。
MEMORY 内存存储引擎，支持 hash 索引，表级锁，常用于临时表。
FEDERATED 用于访问其它远程 MySQL 服务器上表的存储引擎接口。

MariaDB 额外还支持很多种存储引擎，只不过没有自带：

  • OQGraph
  • SphinxSE
  • TokuDB
  • Cassandra
  • CONNECT
  • SQUENCE

五、InnoDB 的相关配置

数据库配置参数

数据库配置参数分为：

  1. 用户可自行修改的会话级变量；

    • 支持运行时修改参数，立即生效，仅对当前会话有效；
    • 需要重启后才能生效的配置；

  2. 只有管理员才能修改的全局变量；

    • 支持运行时修改的参数，立即生效，改变后只对新会话有效；
    • 需要重启后才能生效的配置；

查看数据库参数：

  • SHOW [SESSION | GLOBAL] VARIABLES;：查看服务器配置参数；
  • SHOW [SESSION | GLOBAL] VARIABLES LIKE 'innodb%';：查看指定的服务器配置参数；

定义数据库配置参数：

  • SET [SESSION | GLOBAL] <VAR=ON|OFF>;：改变服务器配置参数。

示例：

1
2
SET GLOBAL innodb_file_per_table=ON;
SET @@global.innodb_file_per_table=ON;

查看 InnoDB 内部的各种状态信息：

  • SHOW ENGINE INNODB STATUS;

状态计数器。显示级别分为当前会话和全局，不加 SESSION 则默认显示当前会话级别：

  • SHOW [SESSION | GLOBAL] STATUS;

InnoDB 的并发控制

实现并发机制的相关概念

每个请求通过长连接或短连接的方式连接到 MariaDB 的连接器，MariaDB 会生成一个线程来维持这个连接。

  • SHOW PROCESSLIST;：可以显示所有线程。

当有多个线程同时访问一个数据集时，就要为这个数据施加一种机制，基于 MVCC（多版本并发控制），通过锁机制和事务隔离，保证数据的一致性。

1. 锁 Lock

锁类型：
为了最大化数据库事务的并发能力，数据库中的锁被设计为两种模式，分别是 共享锁 和 互斥锁。当一个事务获得共享锁之后，它只可以进行读操作，所以共享锁也叫读锁；而当一个事务获得一行数据的互斥锁时，就可以对该行数据进行读和写操作，所以互斥锁也叫写锁。

  • 读锁：共享锁，可被多个读操作共享；
  • 写锁：排它锁，独占锁。

锁粒度：

  • 表锁：在表级别施加锁，并发性较低；
  • 行锁：在行级别施加锁，并发性较高；维持锁状态的成本较大。

锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；

  • 存储引擎级别：级别以及何时施加或释放锁由存储引擎自行决定；
  • MariaDB 级别：用户可自行决定，也允许显式请求。

锁类别：

  • 显式锁：用户手动请求的锁；
  • 隐式锁：存储引擎自行根据需要施加的锁；
显式锁的使用

显示锁可以由用户手动设定。

  • 锁定表：LOCK TABLES tbl_name read|write, tbl_name read|write, ...
  • 解锁表：UNLOCK TABLES;

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。

  • 锁定：FLUSH TABLES tbl_name,... [《 WITH READ LOCK];
  • 解锁：UNLOCK TABLES;

2. 事务

什么是事务？

所谓事务，就是一组原子性的 SQL 查询、或者是一个或多个 SQL 语句组成的独立工作单元；

  • 多个步骤的 SQL 语句的执行状态必须一致，要么都执行，要么都不执行。
  • 每句 SQL 执行前的内容和执行后的内容全部被保存在撤销日志中，如果执行不下去或者宕机后，就从日志读取执行前的数据并回滚整个事务；如果执行顺利完成，则会将日志中执行后的数据同步到表中。
  • 事务日志文件的大小是固定的，可以设置，所以应该有至少一组两个，这样当一个写满后会使用另一个继续写，然后将满了的日志文件中的已执行数据同步至表中，保留执行前的数据，以释放空间。

Innodb 的事务日志是指 Redo log，简称 Log，默认保存在日志文件 /var/lib/mysql/ib_logfile* 里面。

Innodb 还有另外一个日志 Undo log，但 Undo log 是存放在共享表空间里面的（ibdata* 文件，存储的是 check point 日志序列号）。

定义事务日志相关参数：

  • innodb_log_files_in_group：一组日志文件需要几个文件，至少两个；
  • innodb_log_group_home_dir：日志存放在哪，默认在数据目录下；
  • innodb_log_file_size：每个日志文件的大小，单位字节，默认 5MB；
  • innodb_mirrored_log_groups：定义有几个日志组。
  • innodb_flush_logs_at_trx_commit：每当有事务提交时，如何将缓冲区的事务日志同步至磁盘中，以保证事务的安全性。
    • 0：每一秒，将 log buffer（内存）同步一次至 log file（事务日志）；且同时将 log file 同步到 data file（数据文件）；
    • 1：每次提交时，将 log buffer 同步至 log file，且同时将 log file 同步到 data file；偏安全性高；
    • 2：每次提交时，将 log buffer 同步至 log file，但不会将 log file 同步到 data file；偏性能好；
    • 建议关闭 autocommit，而后将此值设置为 1 或 2。

事务隔离级别，隔离效果由低到高：

隔离级别 脏读 不可重复读 幻读 描述
READ-UNCOMMITTED 未提交也可读 ✅ ✅ ✅ 读取到未提交的数据
READ-COMMITTED 提交后才可读 ❌ ✅ ✅ 读已经提交的数据，ORACLE 默认隔离级别
REPEATABLE-READ 可重复读 ❌ ❌ ✅ 可以重复读，MySQL 的默认隔离级别。
SERIALIZABLE 串行化 ❌ ❌ ❌ 可串行化。事务间完全隔离，事务不能并发，只能串行执行
✅: 可能出现 ❌: 不会出现
定义隔离级别
1
mysql> SET @@session.tx_isolation=REPEATABLE-READ;

如何判定一个存储引擎支持事务，需要通过 ACID 测试：

  • AUTOMICITY：原子性，整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
  • CONSISTENCY：一致性，数据库总是应该从一个一致性状态转为另一个一致性状态；
  • ISOLATION：隔离性，一个事务所做出的操作在提交之前，是否能被其它事务可见；出于保证并发操作之目的，隔离有多种级别；
  • DURABILITY：持久性，事务一旦提交，其所做出的修改会永久保存；
手动控制事务

启动事务

1
START TRANSACTION; SQL; SQL;

提交事务

1
COMMIT;

回滚事务

1
ROLLBACK;

事务也支持锚点（savepoints），可选择回滚至指定锚点：

  • SAVEPOINT <identifier>：定义锚点；
  • ROLLBACK [WORK] TO [SAVEPOINT] <identifier>：回滚至某个锚点；
  • RELEASE SAVEPOINT <identifier>：清除某个锚点。
关于自动提交单语句事务

MariaDB 默认开启单语句事务，即每句 SQL 为一个单独的事务，这对性能可能会产生影响。

查询当前是否开启，1 为开启，0 为关闭：

1
2
3
4
5
6
7
MariaDB [(none)]> SELECT @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

关闭单语句事务：SET @@session.autocommit=0，仅当前会话生效。

四、日志

数据库的日志分为：

  • 查询日志：general_log
  • 慢查询日志：log_slow_queries
  • 错误日志：log_error，log_warnings
  • 二进制日志：binlog
  • 中继日志：relay_log
  • 事务日志：innodb_log

1. 查询日志

记录查询的语句，信息量会很大，一般不开启。

日志可用存储位置：

  • 文件：FILE
  • 表：TABLE (mysql.general_log, mysql.slow_log)

配置：

  • general_log={ON|OFF}：启用查询日志；
  • general_log_file=HOSTNAME.log：指定日志 file 的保存路径；
  • log_output={FILE|TABLE|NONE}：定义日志存储位置，可使用分隔符 , 来指定多个。

2. 慢查询日志

运行时间超出指定时长的查询日志。

SELECT @@long_query_time;：查询当前定义的超时时间，默认 10 秒：。

日志可用存储位置：

  • 文件：FILE
  • 表：TABLE（mysql.slow_log）

配置：

  • log_slow_queries={ON|OFF}：定义慢查询是否开启，建议在配置文件中配置；
  • slow_query_log={ON|OFF}：同上，版本不同语法可能不同；
  • slow_query_log_file=：指定日志 file 的保存路径；
  • log_output={FILE|TABLE|NONE}：定义日志存储位置，可用 , 指定多个；
  • log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk：定义记录哪些类型的慢查询操作；
  • log_slow_rate_limit：定义记录日志的速率限制；
  • log_slow_verbosity：定义日志记录的详细程度。

3. 错误日志

除了记录错误日志，还记录以下几种信息：

  • mysqld 启动和关闭过程 输出的信息；
  • mysqld 运行中产生的错误信息；
  • event scheduler 运行时产生的信息；
  • 主从复制架构中，从服务器复制线程启动时产生的日志；

配置：

  • log_error=/var/log/mariadb/mariadb.log|OFF：指定了文件即开启日志，否则使用 OFF 关闭。
  • log_warnings={ON|OFF}：是否记录警告信息。

4. 二进制日志

用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合。

主要的功能用来“重放”。

二进制日志有三种格式：

定义方法：binlog_format={STATEMENT|ROW|MIXED}。

  • STATEMENT: 记录 SQl 语句；
  • ROW: 直接记录 SQL 语句执行的结果，而不是 SQL 本身；
  • MIXED: 混合，让数据库自行判断使用哪一种。

启用二进制日志

  • log_bin=/PATH/TO/BIN_LOG_FILE：指定二进制日志保存位置，指定了文件即表示开启，否则使用 OFF 关闭，切记文件不要加 .log 后缀。此参数是全局只读变量，不支持运行时修改，可以写在配置文件 /etc/my.cnf 的 [mysqld] 中；二进制日志按序号滚动生成，会自动创建 *.index 的文本文件，记录了当前日志的文件序号。
  • SET session.sql_log_bin={ON|OFF}：控制某个会话中的“写”操作语句是否会被记录于日志文件中；

查看二进制日志的相关命令

  • SHOW MASTER|BINARY LOGS;：查看二进制日志文件列表，每次重启 mariadb 会滚动一个新日志；
  • SHOW MASTER STATUS；：查看当前正在使用的二进制日志文件是哪个；
  • SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]：查看二进制日志文件中都记录了哪些事件：
    • [FROM pos]：指定从哪个字节位置开始显示；
    • [LIMIT [offset,]：指定显示几个事件，offset 使用整数；

示例：

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'master_log.000002' FROM 245 LIMIT 2;
+-------------------+-----+------------+-----------+-------------+-------------------------------+
| Log_name          | Pos | Event_type | Server_id | End_log_pos | Info                          |
+-------------------+-----+------------+-----------+-------------+-------------------------------+
| master_log.000002 | 245 | Query      |         1 |         325 | use `mydb`; flush tables tbl1 |
| master_log.000002 | 325 | Query      |         1 |         405 | use `mydb`; flush tables tbl1 |
+-------------------+-----+------------+-----------+-------------+-------------------------------+

显示结果中的字段代表了：

  • Pos：此事件在二进制日志中的起始字节位置；
  • End_log_pos：此事件在二进制日志中的结束字节位置；

一些配置和命令

  • max_binlog_size=1073741824：自定义多少字节会滚动一次；
  • sync_binlog=N：当每进行 N 次事务提交之后，MariaDB 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。默认的设置是 0，这时候的性能是最好的，但是风险也是最大的。设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。所以很多 MySQL DBA 设置的并不是使用最安全的 1，而是 100 或者是 0，这样牺牲一定的一致性，可以获得更高的并发和性能。
  • FLUSH LOGS;：把内存中的日志同步到磁盘上，二进制文件会滚动一次；

在 shell 直接查看二进制日志文件的工具

  • mysqlbinlog:
    • --start-datetime=：从指定的时间开始查看，格式 "YYYY-MM-DD hh:mm:ss"；
    • --stop-datetime=：指定结束的时间；
    • -j, --start-position=#：从指定的字节位置查看；
    • --stop-position=#：指定结束的字节位置；
    • --user, --host, --password：查看其它服务器的二进制日志。

二进制日志的显示格式及解释：

1
2
3
4
5
## at 553
##160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;
  • # at 553：事件的起始字节位置；
  • #160831 9:56:08：事件发生的日期时间；
  • id：server id 1：事件发生的服务器；
  • end_log_pos 624：事件的结束位置；
  • Query：事件的类型；
  • ID：thread_id=2：事件发生时所在服务器执行此事件的线程的；
  • exec_time=0：语句的时间戳与将其写入二进制日志文件中的时间差；
  • error_code=0：错误代码；
  • SET TIMESTAMP=1472608568/*!*/;：事件内容。

5. 中继日志

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件，一般用于主从复制。

6. 事务日志

事务型存储引擎 InnoDB 用于保证事务特性的日志文件，上文已经介绍过，就不重复了。

扩展阅读：
《 浅谈数据库并发控制 - 锁和 MVCC 》
《 数据库事务隔离级别 – 脏读、幻读、不可重复读（清晰解释）》

gong