MySQL存储文件组成和常见相关问题

原创

andelwu

修改于 2019-02-22 15:29:51

1.5K0

修改于 2019-02-22 15:29:51

文章被收录于专栏：andelwu的花园andelwu的花园

作者：吴洪辉

1. 使用yum安装mariadb

yum install mariadb-server

2. 安装完成后，我们可以看到有以下安装文件：

/usr/libexec/mysqld： mariadb二进制文件
/usr/bin/mysql*： mariadb的配套工具
/usr/share/mysql: 字符集xml配置、语言包、初始化db的sql脚本、参考配置文件
/usr/lib64/mysql/plugin: 插件库
/etc/my.cnf: mariadb配置文件
/etc/my.cnf.d： mariadb子配置文件目录

3. 安装完成后，建好数据目录，修改/etc/my.cnf和/etc/my.cnf.d/server.cnf的配置，通过systemctl start mariadb即可启动数据库。

### Step a.创建目录
[root@VM_32_32_centos ~]# mkdir /data/instance1/{data,log,log_bin,tmpdir}
### Step b.修改/etc/my.cnf
[mysqld]
datadir=/data/instance1/data
...
[mysqld_safe]
log-error=/data/instance1/log/mariadb.log
...
### Step c.修改/etc/my.cnf.d/server.cnf
[mysqld]
log_bin=/data/instance1/log_bin/binlog
sql_log_bin=on
sync_binlog=1
tmpdir=/data/instance1/tmpdir
slow_query_log=on
long_query_time=1 
innodb_file_per_table=on

4. 首次启动数据库，mariadb会在数据目录下进行初始化安装DB，并生成对应的数据目录和相关文件、日志。

我们来观察一下数据目录结构：

|-- data                                # 数据目录
|   |-- aria_log.00000001
|   |-- aria_log_control
|   |-- ibdata1     
|   |-- ib_logfile0
|   |-- ib_logfile1
|   |-- mysql                           # mysql系统库目录
|   |   |-- columns_priv.frm
|   |   |-- columns_priv.MYD
|   |   |-- columns_priv.MYI
|   |   |-- general_log.CSM
|   |   |-- general_log.CSV
|   |   |-- general_log.frm
|   |   |-- ...
|   |-- performance_schema              # performance_schema系统库目录
|   |   |-- cond_instances.frm
|   |   |-- db.opt
|   |   |-- events_waits_current.frm
|   |   |-- events_waits_history.frm
|   |   |-- ...
|   |-- test                            # test库目录
|   `-- VM_32_32_centos-slow.log        # 慢查询日志
|-- log                                 # 运行日志，错误日志也包含在里面
|   `-- mariadb.log
|-- log_bin                             # binlog目录
|   |-- binlog.000001
|   `-- binlog.index
`-- tmpdir                              # 临时表目录

这里面的文件可以分为

数据目录数据目录中的数据文件有: - ibdata1: InnoDB 的共享表空间, 主要存储数据字典、变更缓冲区、Double write buffer、Undo log - ib_logfile0/ib_logfile1: InnoDB的事务日志，又称Redo log - aria_log*: aria存储引擎的控制文件和日志 - mysql/: mysql系统库的存储目录 - performance_schema/: performance_schema系统库的存储目录 - test/: test库的存储目录每个库目录下存储每张表的数据和索引、表定义、数据库选项定义。当创建一张innodb表后，会在相应的库目录下生成表frm文件、表数据和索引文件。
慢查询日志当一条SQL的响应时间大于参数long_query_time时，数据库将该SQL记录在文件中以便优化分析。慢查询是数据库系统响应变慢的最主要原因，当你的业务遇到问题时，应该优先排查慢查询优化不合理的业务SQL。建议在测试阶段调低long_query_time的值并进行合理压测，提前发现问题SQL避免线上业务出现故障。相关参数： slow_query_log=on long_query_time=1
运行日志/错误日志：当数据库启动、停止、检测到死锁、崩溃或者有其他错误信息时，mariadb会将错误信息记录在该文件里面。如果你的业务中遇到数据库返回了死锁，可以从错误日志里面去分析对应的死锁日志。
binlog目录: binlog.000001: binlog文件，当到达max_binlog_size大小后进行切换，切换时序号向上递增，可以借助mysqlbinlog工具来解析查询日志。 binlog.index: 记录系统中有哪些binlog文件. 用途： - 数据库主从同步。 - 数据修改历史排查。开启了binlog后，binlog中包含了所有数据的修改信息，但binlog仅能查到什么时候修改了哪条数据，无法记录ip来源和登录用户信息。
临时文件：当使用临时表或者查询的中间结果超过了tmp_table_size时，会在磁盘上创建临时文件避免占用过多内存，默认为/var/tmp，可通过修改数据库启动参数--tmpdir或者修改配置文件移动到其他位置。

关于MySQL存储空间的常见问题

1. 为什么用SQL查询出来的文件大小和整个数据库存储占用空间有差异？

查询语句：SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length + data_free) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

该查询语句里面包含了数据块、索引块、已分配未使用的空闲空间。

- 对MyISAM引擎来说，index_length和data_length为表索引文件、数据文件的字节大小
- 对Innodb引擎来说，index_length和data_length为表和索引的近似字节数。data_free为表空间中已分配但未使用的字节数。例如在表中删除了一部分数据，这时候innodb仅会在文件中标识这些数据已删除，不会减少文件大小，这些空间在后续插入数据的时候可以重复使用，如果发现data_free很大时，可以通过optimize table命令缩容整理。因为绝大多数的业务来说都是使用innodb引擎，所以以上查询可以得到每个库的数据块、索引块、已分配未使用的空闲空间总和，并且实际文件大小会比这个值稍大一些。
整个数据库存储占用空间除了业务库目录文件大小外，还需要加上binlog、error log、redo log和系统自带库表和innodb共享表空间等占用大小。此外如果数据库当前的角色是从机的话，还需要加上relay log的大小。

2. 什么时候会产生临时文件到磁盘上？

场景1：业务使用CREATE TEMPORARY TABLE...语法创建临时表。当表的大小超过了参数指定的大小时，会使用临时文件存储。

场景2：在执行查询SQL时，根据生成的执行计划(explain查看)，mysql会自动创建内部临时表.

内部临时表的创建条件：

- group by 和 order by中的列不相同
- order by列不是引用from表列表中的第一个表(驱动表)
- group by列不是引用from表列表中的第一个表(驱动表)
- 使用了sql_small_result选项
- 含有distinct的order by语句

3. 为什么生成的binlog文件超过了设定的最大binlog大小？

原因：大事务产生的binlog太大。主要出现在对大表进行批量更新、删除等业务场景中，并且由于从机需要拉取该binlog进行回放，所以大事务会引起主备延迟上涨。建议在更新、删除的SQL中使用LIMIT子句，将更新删除分为多个事务执行，每次事务操作的数据不超过10w条。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

sql

数据库

存储

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度