发表于 | 更新于 | 分类于

全球数据库排行

MySQL

发展历史

  • 1979年，Monty 写了最初的版本
  • 1996年，发布 1.0 版本
  • 1999年，MySQL AB 在瑞典正式宣布成立
  • 2000年4月，集成 MyISAM 和 Replication
  • 2003年，MySQL4.0 发布，正式集成 InnoDB
  • 2005年，发布 5.0 版本（提供了视图、存储过程等功能）
  • 2006年，MySQL 被 Sun 收购，出价 10 亿美元
  • 2009年，Oracle 公司收购 Sun，将 MySQL 纳入囊中
  • 2010年12月，发布 5.5 正式版本，InnoDB 改为默认存储引擎
  • 2013年2月，发布 5.6 正式版本
  • 2015年10月，发布 5.7 正式版本
  • 2018年5月，发布 8.0 正式版本

优缺点

优点

  • 免费
  • 开源（源码、社区工具丰富）
  • 简单（部署简单、门槛低、上手快）

缺点

  • 优化器对复杂 SQL 支持不好
  • 大规模集群方案不成熟，主要指中间件
  • 备份和恢复方案还是比较复杂，需要依赖外部组件
  • 缺少分库分表解决方案

行业应用

主流分支

基本概念

  • 数据库：就是一个存储结构化数据的仓库。物理操作系统文件或其他形式文件类型的集合，通常来说存在与文件系统之上。

  • 关系型数据库：是创建在关系模型基础上的数据库，典型代表有：MySQL、Oracle、Microsoft SQL Server、PostgreSQL 等。

  • 非关系型数据库（NoSQL）：对不同于传统的关系数据库的数据库管理系统的统称。与关系数据库最大的不同点是不使用SQL作为查询语言。 典型代表有：Redis、MongoDB 等。

  • 实例：MySQL 数据库由后台线程以及一个共享内存区组成。数据库实例才是真正用于操作数据库文件的。MySQL 数据库实例在系统上的表现就是一个进程。

  • 库：本质是一个文件夹。

  • 表：本质就是一个文件。

  • 记录：相当于文件中的一行。

  • 存储引擎：可以理解成文件系统，存储引擎的对象就是表。

  • OLAP：On-Line Transaction Processing，联机事务处理。

  • OLTP：On-Line Analytical Processing，联机分析处理。

  • DML：数据操作语言，主要是用来对数据进行一些操作，如 SELECT、UPDATE、INSERT、DELETE 等。

  • DDL：数据定义语言，主要是用在定义或改变表的结构，如 CTEATE、ALTER、DROP、TRUNCATE 等。

学习资料

发表于 | 更新于 | 分类于

背景

由于单机存储容量、连接数、处理能力有限，当超过一定上限后，数据库会遭遇性能瓶颈，即使优化索引，很多操作的性能仍下降严重。切分 (Sharding) 的目的就在于减少数据库的负担，缩短查询时间。

根据切分类型，可以分为两种方式：垂直切分和水平切分。

分库分表

垂直切分

① 垂直分库

概念：根据业务耦合性，将关联度低的不同表存储在不同的数据库。与”微服务治理”的做法相似，每个微服务使用单独的一个数据库。

结果：

  • 每个库的结构、数据都不一样。
  • 所有库的并集是全量数据。

场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。

② 垂直分表

概念：某个表字段较多，以列为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

结果：

  • 每个表的结构、数据都不一样；
  • 所有表的并集是全量数据；

场景：表字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读 IO，产生 IO 瓶颈。

分析：拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用 join，因为 join 不仅会增加CPU 负担并且会将两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务 service 层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

③ 优缺点

优点：

  • 解决业务系统层面的耦合，业务清晰。
  • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等。

缺点：

  • 部分表无法 join，只能通过接口聚合方式解决，提升了开发的复杂度。
  • 分布式事务处理复杂。
  • 依然存在单表数据量过大的问题（需要水平切分）。

水平拆分

① 水平分库

概念：当一个应用数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中。

结果：

  • 每个库的结构都一样；
  • 每个库的数据都不一样，没有交集；
  • 所有库的并集是全量数据；

场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

② 水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

结果：

  • 每个表的结构都一样；
  • 每个表的数据都不一样，没有交集；
  • 所有表的并集是全量数据；

场景：解决了单一表数据量过大的问题。

分析：表的数据量少了，单次 SQL 执行效率高，自然减轻了 CPU 的负担。

③ 优缺点

优点：

  • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。
  • 应用端改造较小，不需要拆分业务模块。

缺点：

  • 跨库的 join 关联查询性能较差。
  • 数据多次扩展难度和维护量极大。

几种典型的数据水平分片规则：

  • 根据数值范围：按照时间区间或 ID 区间切分，例如：按日期将不同月甚至是日的数据分散到不同的库表中，将 userId 为 19999 的记录分到第一个库或表，1000020000 的分到第二个库或表。

    • 优点：扩容简单

    • 缺点：请求量分布不均匀，导致服务器利用率不平衡

  • 根据数值取模：一般采用 hash 取模 mod 的切分方式，例如：将 Customer 表根据 cusno 字段切分到 4 个库中，余数为 0 的放到第一个库，余数为 1 的放到第二个库。

    • 优点：数据量和请求量分布均匀

    • 缺点：扩容麻烦，需要考虑对数据进行平滑的迁移

分库分表带来的问题

分库分表能有效的环节单机和单库带来的性能瓶颈和压力，突破网络 IO、硬件资源、连接数的瓶颈，同时也带来了一些问题。

  • 事务一致性问题：往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。

  • 跨节点关联查询 join 问题：考虑到性能，尽量避免使用 join 查询，一般通过字段冗余反范式设计、数据组装等方法。

  • 跨节点分页、排序、函数问题：需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。

  • 全局主键避重问题：由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的 ID 无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。一些常见的主键生成策略：

    • UUID：32 个 16 进制数字，缺点是太长和无序，会占用大量的存储空间且无序性会引起数据位置频繁变动，导致分页。
    • Twitter 的 Snowflake 算法：缺点是强依赖机器时钟，如果时钟回拨，则可能导致生成 ID 重复。
    • 美团的 Leaf

工具中间件

实践

数据库水平切分架构实践-【架构师之路】公众号

原则

  • 原则 0：能不分就不分。
  • 原则 1：数据量太大，正常的运维影响正常的业务访问。
  • 原则 2：表设计不合理，需要对某些字段进行垂直拆分。
  • 原则 3：某些数据表出现了无穷增长的情况。
  • 原则 4：安全性和可用性的考虑。
  • 原则 5：业务耦合性考虑。

总结

① 水平拆分和垂直拆分都是降低数据量大小，提升数据库性能的常见手段。

② 流量大数据量大时，数据访问要有 service 层，并且 service 层不要通过 join 来获取主表和扩展表的属性。

③ 垂直拆分的依据，尽量把长度较短，访问频率较高的属性放在主表里。

发表于 | 更新于 | 分类于

概述

根据备份方法不同：

  • 热备：数据库运行过程中直接备份，对正在运行的数据库操作没有任何的影响。
  • 冷备：备份操作是在数据库停止的情况下，这种备份最为简单，只需要复制相关的数据库物理文件即可。
  • 温备：数据库运行过程中直接备份，但是会对当前数据库操作有所影响。

根据备份后文件的内容不同：

  • 逻辑备份：是指备份出的文件内容是可读的，一般内容是 SQL 语句，如 mysqldump。
  • 物理备份：是指复制数据库的物理文件，如 xtrabackup。

根据备份数据库内容不同：

  • 全量备份：是指对数据库进行一个完整的备份。
  • 增量备份：是指在上次完全备份的基础上，对于更改的数据进行备份。
  • 日志备份：是指对二进制日志的备份，通过它可以完成 point-in-time 的恢复工作。

自建 MySQL

备份

物理备份

逻辑备份

恢复

阿里云 RDS

备份

全量备份（数据）

增量备份（日志）

恢复

单库单表恢复

整实例恢复

恢复到原实例

恢复到新实例

恢复到本地自建数据库

备份

物理备份

备份内容：共享表空间文件 .frm、独立表空间文件 .ibd、重做日志文件、配置文件 my.cnf。

优缺点：

  • 优点：备份恢复简单、速度快。
  • 缺点：备份的文件通常比逻辑文件大很多，且需要停机，不适于用于生产环境。

逻辑备份

mysqldump

语法

1
2
3
Usage: mysqldump [OPTIONS] database [tables] > [backupfile.sql]
       mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > [backupfile.sql]
       mysqldump [OPTIONS] --all-databases [OPTIONS] > [backupfile.sql]

参数

  • –single-transaction：在备份之前，先执行 start transaction 命令，以此来获得备份的一致性，当前该参数只对 InnoDB 存储引擎有效。当启用此参数时，确保没有任何 DDL 语句执行，因为一致性读并不能隔离 DDL 操作。

  • –lock-tables (-l)：在备份中，依次锁住每个库下所有表，一般用于 MyISAM 存储引擎，当备份时只对数据库进行读取操作，不过备份依然可以保证一致性。对于 InnoDB 存储引擎，不需要使用该参数。

  • –lock-all-tables (-x)：在备份中，对所有库的所有表上锁。

  • –no-data (-d)：不备份数据。

  • –extended-insert (-e)：使用全新多行 insert 语法，默认开启。

  • –master-data [=value]：通过该参数产生的备份转存文件主要用来建立一个 replication。

    • 1：转存文件中记录 change master 语句。

    • 2：转存文件中 change master 语句被注释。

  • –flush-logs：开始备份之前刷新日志。

  • –events (-E)：备份事件。

  • –routines (-R)：备份存储过程和函数。

  • –triggers：备份触发器，该选项默认启用。

  • –where=’where_condition’ (-w ‘where_condition’)：导出给定条件的数据。

实践

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 备份所有库
mysqldump --user=sre --password='xx' --all-databases > backup.sql

# 备份指定库
mysqldump -u<user> -p<password> --databases school hospital > backup.sql

# 备份指定表
mysqldump --user=sre --password='xx' school table_name > backup.sql

# 导出表结构，无数据
mysqldump --user=sre --password='xx' --all--database --no-data > backup.sql

# 备份存储过程
mysqldump --user=sre --password='xx' --single-transaction --master-data=2 -n -d -t -R user > dump.sql

# 备份InnoDB
mysqldump --socket=mysql.sock --user=user --password=pass --single-transaction --flush-logs --master-data=2 --all-databases --routines --events > dump.sql

# 备份Myisam
mysqldump --socket=mysql.sock --user=user --password=pass --lock-all-tables --flush-logs --master-data=2 --all-databases --routines --events > dump.sql

# 按条件备份
mysqldump -usre -p'xx' -t user uc_event_logs_bak --where="id > 46005503" > uc_event_logs_bak2.sql

# 后台执行任务
nohup mysql -usre -p'xx' user < uc_event_logs_bak2.sql 2> myout.file &

注意：默认是会备份视图。

恢复

二进制日志备份与恢复

作用：二进制日志非常关键，用户可以通过它完成 point-in-time 的恢复工作。对于 InnoDB 存储引擎，推荐的二进制日志的服务器配置应该是：

1
2
3
4
[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

备份：在备份二进制日志之前，可以通过 Flush Logs 命令生成一个新的二进制日志文件，然后后备份之前的二进制日志。

恢复：二进制日志的恢复主要通过工具 mysqlbinlog 实现。例如：

1
2
3
4
5
6
7
8
9
10
11
12
// 恢复单个文件
mysqlbinlog mysql-bin.000001 | mysql -uroot -p test 

// 恢复多个文件
mysqlbinlog mysql-bin.[0-10]* | mysql -uroot -p test

// 也可以先导出sql文件，再使用source导入，好处是可以对导出的文件进行修改
mysqlbinlog mysql-bin.000001 > /tmp/backup.sql
mysql -uroot -p -e "source /tmp/backup.sql"

// 也可以通过--start-position和--stop-position指定某个位置进行恢复
// 也可以通过--start-datetime和--stop-datetime指定某个时间点进行恢复

热备

XtraBackup

快照备份

MySQL 数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一个文件分区中，然后对该分区进行快照操作。支持快照功能的文件系统和设备包括 GUN/Linux 的逻辑管理器 LVM。

LVM 是 Linux 系统下对磁盘分区进行管理的一种机制，LVM 在硬盘和分区之上建立一个逻辑层，来提高磁盘分区管理的灵活性。LVM 使用了写时复制（Copy-on-write）技术来创建快照，当创建一个快照时，仅复制原始卷中数据的元数据（meta data），并不会有数据的物理操作，因此快照的创建过程非常快。

使用 LVM 快照备份 InnoDB 存储引擎表非常简单，只要把与 InnoDB 存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等放在同一个逻辑卷中，然后对这个逻辑卷做快照备份即可。在对 InnoDB 存储引擎文件做快照时，数据库无须关闭，即可以进行在线备份。

全量备份

增量备份

备份策略

参数 说明
备份周期 建议一周至少需要备份两次（例如：周一、三、五 / 二、四、六）
备份时间 建议设置为业务低峰期时间
保留时间 建议保留 30 天，超过保留天数的备份会被自动删除
日志备份 建议打开，关闭日志备份会导致所有日志被清除，并且无法使用按时间点恢复数据的功能
日志备份保留 日志备份文件保留的天数，默认为 7 天
单库单表 开通后会修改备份格式以支持该功能，默认为开启，无法关闭
实例释放后备份文件是否保留 为防止忘记续费、误操作等情况导致实例数据丢失，建议选择保留最后一个或全部保留，保留不收取费用，可以在已删除实例备份页面下载备份文件，并恢复到本地数据库

恢复

恢复方案概览

RDS 提供了多种数据恢复方案，可以将数据恢复至新实例、原实例或自建数据库。

适用场景 方案
MySQL 单库单表恢复
MySQL 实例恢复
MySQL 备份文件恢复到自建数据库

单库单表恢复

RDS MySQL 支持单库和单表的数据恢复，可以通过备份指定恢复误删的数据库或表，快速恢复 MySQL 的数据。

注意事项：

  • 恢复到原实例过程中会进行主备切换，RDS 服务可能会出现闪断，请确保您的应用有自动重连机制；恢复到新实例不会进行主备切换。
  • 超出免费备份空间额度的部分将会产生额外费用，请合理设计备份周期，以满足业务需求的同时，兼顾备份空间的合理利用。
  • 如果无法确定所有涉及的表，则需要进行实例级别的恢复。
  • 每次最多选择 50 个库或者表。
  • 实例内的表低于 50000 张才可以使用单库单表恢复功能，超过 50000 张表时无法使用。

实例恢复

总结

任何时候都需要做好远程异地备份，也就是容灾的防范。

发表于 | 更新于 | 分类于

库表规范

  • 存储引擎必须使用 InnoDB

  • 库名、表名、字段名必须用小写，采用下划线分隔

  • 表字符集默认使用 utf8，必要时候使用 utf8mb4

    1
    2
    3
    解读：
    ① 通用，无乱码风险，汉字3字节，英文1字节
    ② utf8mb4是utf8的超集，存储4字节，例如：表情符号时使用它

  • 禁止使用存储过程、视图、触发器、事件

    1
    2
    3
    解读：
    ① 对数据库性能影响较大，能让站点层和服务层干的事情，不要交到数据库层
    ② 难以调试和扩展，更没有移植性

  • 禁止在数据库中存储大文件

    1
    2
    解读：
    ① 例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

  • 禁止使用 text、blob 类型

    1
    2
    解读：
    ① 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

  • 根据业务区分使用 tinyint（1 byte）、smallint（2 byte）、int（4 byte）、bigint（8 byte）

    1
    2
    3
    解读：
    ① 合适的字符存储长度，不但节约数据库表空间，节约索引存储，更重要的是提升检索速度
    ② 整数类型指定显示宽度，例如：int(M)对大多数应用是没有意义的，他不会限制值的合法范围，对于存储和计算来说，int(1)和int(20)是相同的
    对象 年龄区间 类型 范围 字节
    人 150岁之内 unsigned tinyint 0～255 1
    龟 数百岁 unsigned smallint 0～65535 2
    恐龙化石 数千万岁 unsigned int 0～4294967295 4
    太阳 约50亿年 unsigned bigint 0～2^64-1 8

  • 根据业务区分使用 char、varchar

    1
    2
    3
    解读：
    ① 字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
    ② 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，节省磁盘空间

  • 根据业务区分使用 datetime、timestamp

    1
    2
    解读：
    ① 前者占用8个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

  • 小数类型使用 decimal，禁止使用 float 和 double

    1
    2
    3
    解读：
    ① float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果
    ② decimal(M,D)，如果M>D，占用字节M+2，否则为D+2，M是数字最大位数，D是小数点右侧数字个数

  • 表必须有主键，推荐使用 unsigned 整数为主键

    1
    2
    解读：
    ① 删除无主键的表，如果是row模式的主从架构，从库会挂住

  • 创建是否概念字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（ 1表示是，0表示否）

  • 表必备三字段：id，gmt_create，gmt_modified

    1
    2
    3
    解读：
    ① id必为主键，类型为unsigned bigint，单表时自增，步长为1
    ② gmt_create, gmt_modified类型均为datetime，前者现在时表示主动创建，后者过去分词表示被动更新

  • 字段定义必须为 NOT NULL ，并设默认值

    1
    2
    3
    4
    解读：
    ① NULL列使用索引，会影响优化器选择执行计划，很难优化
    ② NULL只能采用IS NULL或者IS NOT NULL，而在使用=、!=、in、not in时有大坑
    ③ 数值默认值设置为0，字符串默认值设置为''

  • 使用 unsigned int 存储 IPv4，不要用 char(15)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    解读：
    ① 节省存储空间，char(15)占据16个字节，unsigned int只需要4个字节
    ② int型在逻辑运算上，比char速度快
    ③ ip和数字转换，函数算法：inet_aton()和inet_ntoa()

    mysql> select inet_aton('192.168.1.200');
    +----------------------------+
    | inet_aton('192.168.1.200') |
    +----------------------------+
    |                 3232235976 |
    +----------------------------+
    1 row in set (0.00 sec)

    mysql> select inet_ntoa('3232235976');
    +-------------------------+
    | inet_ntoa('3232235976') |
    +-------------------------+
    | 192.168.1.200           |
    +-------------------------+
    1 row in set (0.00 sec)

  • 使用 varchar(20) 存储手机号，不要使用整数

    1
    2
    3
    4
    解读：
    ① 牵扯到国家代号，可能出现+/-/()等字符，例如+86
    ② 手机号不会用来做数学运算
    ③ varchar可以模糊查询，例如：like '138%'

  • 控制单表数据量，单表数据控制在千万级

  • 测试、开发、线上数据库环境必须隔离

  • 数据表、数据字段要有中文注释

SQL规范

  • 禁止使用 select *，只获取必要字段

    1
    2
    3
    4
    解读：
    ① select*会增加cpu/io/内存/带宽的消耗
    ② 指定字段能有效利用索引覆盖
    ③ 指定字段查询，在表结构变更时，能保证对应用程序无影响

  • 禁止在 where 条件列使用函数或者表达式

    1
    2
    解读：
    ① 导致不能命中索引，全表扫描

  • 禁止负向查询，以及 % 开头的模糊查询

    1
    2
    3
    解读：
    ① 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
    ② %开头的模糊查询，会导致全表扫描

  • 不得使用外键与级联，一切外键概念必须在应用层解决

  • count(*) 会统计值为 null 的行，而 count(列名) 不会统计此列为 null 值的行

  • 数据订正（delete、update 操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句

  • 推荐使用 truncate，速度比 delete 快，且使用的系统和事务日志资源少

    1
    2
    3
    解读：
    ① truncate table在功能上与不带where子句的delete语句相同
    ② truncate无事务且不触发trigger，有可能造成事故

  • 避免 in 操作，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内

  • 推荐 or 改写为 union

索引规范

  • 唯一索引使用 uniq_[字段名] 来命名，非唯一索引使用 idx_[字段名] 来命名

  • 单张表索引数量建议控制在 5 个以内

    1
    2
    3
    4
    解读：
    ① 高并发业务，太多索引会影响写性能
    ② 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
    ③ 异常复杂的查询需求，可以选择ES等更为适合的方式存储

  • 超过三个表禁止 join，需要 join 的字段，数据类型必须绝对一致，多表关联查询时，确保被关联的字段需要有索引

    1
    2
    解读：
    ① 踩过因为join字段类型不一致，而导致全表扫描的坑

  • 禁止在更新十分频繁，区分度不高的字段建立索引

    1
    2
    3
    4
    解读：
    ① 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
    ② 区分度：count(distinct left(列名, 索引长度))/count(*)
    ③ 性别不适合建索引

  • 理解组合索引最左前缀原则，避免重复建索引，如果建了 (a,b,c)，相当于建了 (a)，(a,b)，(a,b,c)

    1
    2
    3
    解读：
    ① 示例：where a=? and b=? order by c; 索引：a_b_c
    ② 存在非等号和等号混合判断条件时，在建索引时把等号条件的列前置。如：where a>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前列

  • 避免因字段类型不同造成的隐式转换，导致索引失效

  • SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

    1
    2
    3
    4
    解读：
    ① consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据
    ② ref指的是使用普通的索引（normal index）
    ③ range对索引进行范围检索

参考

《58到家 · MySQL军规》

《阿里巴巴 · Java开发手册》

​

发表于 | 更新于 | 分类于

基本概念

InnoDB 存储引擎可将所有数据存放于 ibdata* 的共享表空间，也可将每张表存放于独立的 .ibd 文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下， 默认的文件名为 ibdata1，初始化为 10M。

独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个 .frm 表描述文件，还有一个 .ibd 文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

优缺点

共享表空间

优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析这类应用最不适合用共享表空间。

独立表空间：在配置文件（my.cnf）中设置 innodb_file_per_table

优点：

  • 每个表都有自已独立的表空间。
  • 每个表的数据和索引都会存在自已的表空间中。
  • 可以实现单表在不同的数据库中移动。
  • 空间可以回收（除 drop table 操作处，表空不能自已回收）。

缺点

  • 单表增加过大，如超过100G。
  • 相比较之下，使用独占表空间的效率以及性能会更高一点。

参数详解

Command-Line Format --innodb_file_per_table
System Variable Name innodb_file_per_table
Variable Scope Global
Dynamic Variable Yes
Permitted Values (<= 5.6.5) Type boolean
Default OFF
Permitted Values (>= 5.6.6) Type boolean
Default ON

修改 InnoDB 数据存储方式：

1
2
3
4
5
6
7
mysql> set global innodb_file_per_table =ON;  
mysql> show variables like '%per_table%';  
+-----------------------+-------+  
| Variable_name         | Value |  
+-----------------------+-------+  
| innodb_file_per_table | ON    |  
+-----------------------+-------+

想要将共享表空间转化为独立表空间有两种方法：

  1. 先逻辑备份，然后修改配置文件 my.cnf 中的参数 innodb_file_per_table 参数为 1，重启服务后将逻辑备份导入即可。

  2. 修改配置文件 my.cnf 中的参数 innodb_file_per_table 参数为 1，重启服务后将需要修改的所有 innodb 表都执行一遍：alter table table_name engine=innodb; 使用第二种方式修改后，原来库中的表中的数据会继续存放于 ibdata1 中，新建的表才会使用独立表空间。

操作实战

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 导出所有数据
mysqldump -u <user> -p <password> --all-databases --master-data=2 --single-transaction > backup.sql

# 删除所有数据库 
drop database database_N

# 停止MySQL服务
service mysql stop  / service mariadb stop

# 删除文件 ibdata1, ib_logfile0, ib_logfile1
rm -rf xxx

# 修改MySQL配置文件/etc/my.cnf，增加或修改如下：
[mysqld]
innodb_file_per_table=1

# 重启MySQL服务
service mysql start

# 导入备份数据
mysql -u <user> -p <password> < backup.sql

发表于 | 更新于 | 分类于

简介

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。

我们知道关系型数据库的数据是存在表里的，可以将表理解为由行和列组成的表格，类似于 Excel 的电子表格的形式，每个表格就是一个数据。表在存储数据的同时，还要组织数据的存储结构，而这些数据的组织结构就是由存储引擎决定的。简单来说，存储引擎的作用就是规定了数据的存储结构，由实际业务决定。

插件式存储引擎的好处是，每个存储引擎都有各自的特点，可以根据具体的应用建立不同的存储引擎表，这也是 MySQL 的强大之处。

需要特别注意的是，存储引擎是基于表的，而不是数据库。

MySQL 存储引擎

MySQL 支持多种存储引擎，可以通过 show engines 语句来查看。日常开发中用到最多的存储引擎是 InnoDB 与 MyISAM 两种，都是以插件的形式集成在数据库中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | 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         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB

InnoDB 存储引擎支持事务，其设计目标主要面向 OLTP 的应用。特点是行锁设计、支持外键。从 MySQL 5.5.8 版本开始，InnoDB 是 MySQL 默认的存储引擎。

InnoDB 通过使用多版本并发控制 MVCC 来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。同时，使用一种被称为 next-key-locking 的策略来避免幻读的产生。除此之外，InnoDB 存储引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集（cluster）的方式，因此每张表的存储都是按照主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID，并以此作为主键。

MyISAM

MyISAM 存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些 OLAP 应用。在 MySQL 5.5.8 之前，MyISAM 是默认的存储引擎。此外，MyISAM 存储引擎的另一个与众不同的地方在它的缓冲池只缓存（cache）索引文件，而不缓冲数据文件，这一点和大多数数据库都非常不同。

MyISAM 存储引擎表由 MYD 和 MYI 组成，MYD 用来存放数据文件，MYI 用来存放索引文件。

NDB

NDB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC 集群。其特点是数据全部存放在内存中，因此主键查找的速度极快，并且通过添加 NDB 数据存储节点可以线性地提高数据库性能，是高可用、高性能的集群系统。

关于 NDB 存储引擎，有一个问题值得注意，那就是 NDB 存储引擎的连接操作 JOIN 是在 MySQL 数据库层完成的，而不是在存储引擎层完成。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。

Memory

Memory 存储引擎将表中数据存放在内存中，如果数据库重启或发生崩溃，表中数据都将消失。它非常适合用于存储临时数据的临时表。Memory 存储引擎默认使用哈希索引，而不是 B+ 树索引。

虽然 Memory 存储引擎速度非常快，但在使用上还是有一定的限制。比如只支持表锁，并发性能较差，并且不支持 TEXT 和 BLOB 列类型，最重要的是，存储变长字段 varchar 时是按照定长字段 char 方式进行，因此会浪费内存。

Archive

Archive 存储引擎只支持 INSERT 和 SELECT 操作，使用 zlib 算法将数据行进行压缩后存储，压缩比一般可达 1:10，Archive 存储引擎非常适合存储归档数据，如日志信息。其设计目标主要是提供高速的插入和压缩功能。

InnoDB 与 MyISAM 对比

  • 「事务」: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句，MyISAM 不支持事务。
  • 「并发」: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 「外键」: InnoDB 支持外键。
  • 「备份」: InnoDB 支持在线热备份。
  • 「崩溃恢复」: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 「其它特性」: MyISAM 支持压缩表和空间数据索引。

发表于 | 更新于 | 分类于

数据库设计

设计原则：在数据冗余和处理速度之间找到合适的平衡点。

  • 数据库表个数越少越好。

  • 表中组合主键的字段个数越少越好。主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间。

  • 3）一个表中的字段个数越少越好。

设计模式

  • 逻辑设计：根据数据实体之间的逻辑关系对表进行设计。

  • 物理设计：根据所使用的数据库特点进行表结构设计。

范式设计

  • 第一范式：是对属性的原子性约束，要求属性具有原子性，不可再分解。
  • 第二范式：是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性。
  • 第三范式：是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

基本表及其字段之间的关系，应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准，适当增加冗余，达到以空间换时间的目的。

逻辑设计

E-R 模型图：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余

物理设计

数据库类型

数据库类型 成本 开发语言 支持系统 业务场景
Oracle 商业型 php，java，python等 windows/liunx系统 企业级
SQLServer 商业型 .NET，C#等 只支持windows系统 企业级
MySQL 开源型 php，java，python等 windows/liunx系统 中小型
PgSQL 开源型 php，java，python等 windows/liunx系统 中小型

存储引擎

存储引擎 索引 事务 锁粒度 主要应用 忌用
MyISAM 支持 不支持 支持并发插入的表级锁 select，insert高负载 读写并用
InnoDB 支持 支持 支持MVCC的行级锁 事务处理 无
MEMORY 支持 不支持 表锁 中间计算，静态数据 大型数据集，持久性存储
Archive 不支持 不支持 行级锁 日志记录，聚合分析，只支持select，insert操作 随机读取，删除

字段选择

char vs varchar

  • char：适用于数据长度差不多一致，波动较小，最大数据长度小于50字节。

  • varchar：适用于数据长度变化较大，不能预知其具体长度的数据。

decimal vs float

  • decimal：用于存储精确数据，精度最高，但是占用空间很大。

  • float：用于存储非精确数据，会丢失数据精度，占用空间比 decimal 小。

int vs timestamp vs datetime

字段类型 存储空间
TINYINT 1 字节
SMALLINT 2 字节
MEDIUMINY 3 字节
INT 4 字节
BINGINT 8 字节
DATE 3 字节
DATETIME 8 字节
TIMESTAMP 4 字节
CHAR(M) M 字节，1 <= M <= 255
VARCHAR(M) L+1 字节，在此 L <= M 和 1 <=M <= 255
FLOAT 4 字节
DOUBLE 8 字节
DECIMAL 对 DECIMAL(M,D) ，如果 M>D，为 M+2 否则为 D+2

查询速度从快到慢如下：bigint > date > timestamp > time > datetime

主键与外键

主键是实体的高度抽象，主键与外键的配对，表示实体之间的连接

发表于 | 更新于 | 分类于

分区表

视图

存储过程

事件

外键

分布式事务

发表于 | 更新于 | 分类于

简介

MySQL 的复制功能是构建基于大规模、高性能应用的基础，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。

复制解决的基本问题：是让一台服务器与其他服务器保持数据同步。

其他扩展功能：

  • 数据分布：复制的开销很小，通常不会对带宽造成很大的压力，可以通过远程复制来构建不同的数据中心。
  • 负载均衡：通过复制可以将读操作分布到多个服务器上，读写分离。
  • 备份：对备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代备份。
  • 高可用性和故障切换：复制能避免 MySQL 单点失败，设计良好的故障切换系统能显著地缩短宕机时间。

复制原理

  • 主库把数据更改记录到二进制日志（Binary Log）。
  • 备库将主库的日志复制到自己的中继日志（Relay Log）。
  • 备库读取中继日志中的事件，将其重放到备库数据之上。

复制的原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。需要特别注意的是，复制不是完全实时地进行同步，而是异步实时。

这种架构有一个缺陷：在主库上并发运行的查询在备库只能串行化执行，因为只有一个 SQL 线程来重放中继日志中的事件，这是很多工作负载的性能瓶颈所在。

复制方式

MySQL支持两种复制方式：基于行的复制（MySQL 5.1 版本支持）和基于语句的复制（MySQL 3.23 版本支持），这两种复制方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。

复制文件

mysql-bin.index：二进制日志的索引文件，开启二进制日志时就会产生，该文件记录磁盘上的二进制日志文件，每一行包含了二进制文件的文件名。MySQL 依赖这个文件，除非在这个文件里有记录，否则识别不了二进制日志文件。

1
2
3
4
[root@ptdsmapp04 mysql]# cat mysql-bin.index
/data/mysql/mysql-bin.000014
/data/mysql/mysql-bin.000015
/data/mysql/mysql-bin.000016

relay-log.index：中继日志的索引文件，和 mysql-bin.index 作用类似。

1
2
3
[root@ptdsmapp04 mysql]# cat relay-log.index
./relay-log.000016
./relay-log.000017

master.info：这个文件用于保存备库连接到主库所需要的信息，格式为纯文本（每行一个值）。此文件不能删除，否则备库在重启后无法连接到主库。这个文件以文本的方式记录了复制用户的密码，所以要注意此文件的权限控制。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@ptdsmapp04 mysql]# cat master.info 
25
mysql-binlog.000234
933317366
10.133.0.51
repl
Changeme_123
3306
60
0

0
30.000

0
e1db90f2-fce1-11e8-86d2-005056b1d631
86400

0

relay-log.info：这个文件包含了当前备库复制的二进制日志和中继日志坐标，同样也不能删除这个文件，否则在备库重启后将无法从哪个位置开始复制，可能会导致重放已经执行过的语句。

1
2
3
4
5
6
7
8
9
[root@ptdsmapp04 mysql]# cat relay-log.info 
7
./relay-log.000017
933317585
mysql-binlog.000234
933317366
0
0
1

参数详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000234
          Read_Master_Log_Pos: 744390811
               Relay_Log_File: relay-log.000017
                Relay_Log_Pos: 744391030
        Relay_Master_Log_File: mysql-binlog.000234
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
          Exec_Master_Log_Pos: 744390811
                            ...
        Seconds_Behind_Master: 0
1
2
3
4
5
6
7
8
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-binlog.000234
         Position: 844390811
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
变量 说明
Slave_IO_State 显示当前 IO 线程的状态，上述状态是等待主发送二进制日志
Master_Log_File 显示当前同步的主上的二进制日志
Read_Master_Log_Pos 显示当前同步的主上二进制日志的偏移量位置，单位是字节
Relay_Log_File 显示当前写入的中继日志
Relay_Log_Pos 显示当前执行到中继日志的偏移量位置
Relay_Master_Log_File 当前中继日志同步的二进制日志
Slave_IO_Running 从中 IO 线程的运行状态， YES 表示运行正常
Slave_SQL_Running 从中 SQL 线程的运行状态， YES 表示运行正常
Exec_Master_Log_Pos 表示同步的主上二进制日志偏移量的位置，Read_Master_Log_Pos - Exec_Master_Log_Pos 可以表示当前 SQL 线程运行的延时，单位是字节
Seconds_Behind_Master 主从复制延迟，单位是秒

配置主从复制操作实践

  • 每台服务器上创建复制用户
  • 配置主库和备库
  • 通知从库连接到主库并从主库复制数据

创建复制用户

主库和备库都创建该用户：

1
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'x.x.x.%' IDENTIFIED BY 'Changeme_123';

配置主库和从库

主库 my.cnf 文件

1
2
server_id         = 10
log_bin           = mysql-bin

备库 my.cnf 文件

1
2
3
4
5
6
7
server_id         = 20
log_bin           = mysql-bin
relay_log         = relay-log
read_only         = 1
log_slave_updates = 1
sync_binlog       = 0
innodb_flush_log_at_trx_commit = 0

Tips：修改配置文件，需要重启 mysqld 服务生效

同步主从数据并启动复制

（1）克隆数据到从库

1
2
3
4
5
6
7
8
9
10
11
主库全局只读锁：
flush tables with read lock;

备份主库：
mysqldump -uroot -pHaier@123 -A --triggers --routines --events --single-transaction --master-data=2 > master.sql

传到从库：
scp master.sql haieradmin@x.x.x.x:/tmp

从库导入数据：
mysql -uroot -p'xx' < master.sql

（2）配置复制关系

1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='10.133.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-binlog.000232',
MASTER_LOG_POS=3108253;

（3）启动复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000234
          Read_Master_Log_Pos: 744390811
               Relay_Log_File: relay-log.000017
                Relay_Log_Pos: 744391030
        Relay_Master_Log_File: mysql-binlog.000234
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 744390811
                            ...
        Seconds_Behind_Master: 0

（4）检查复制线程

主库上可以看到由备库 I/O 线程向主库发起的连接：

1
2
3
4
5
6
7
8
9
10
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1257405
   User: repl
   Host: 10.133.0.52:46102
     db: NULL
Command: Binlog Dump
   Time: 79824
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

从库上也可以看到两个线程，一个是 I/O 线程，一个是 SQL 线程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 55
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 79927
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3681
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

（5）主库解锁

1
unlock tables;