这篇文章给大家介绍数据架构设计中数据库高阶应用示例分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

创新互联公司于2013年开始，先为山阳等服务建站，山阳等地企业，进行企业商务咨询服务。为山阳企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

1、数据恢复备份如何设计

• 全量备份的方式

  使用crontab结合MySQLdump来做定时备份

• 增量时间点补偿

• 如何补偿

• 考虑修改的变化：update、delete

• 借助我们的binlog

  # 第一步，先做全量备份,可以定时处理
18 20 * * * mysqldump -uroot -p123456 --databases icoding_admin > /usr/local/bak/bak.sql
16 20 * * * rm -rf /usr/local/bak/bak.sql
# 第二步，开启binlong statement/row/mixed
# 对binlog做数据导出
# 场景1:如果数据量小，比如关键的某一条数据，row模式下可以直接手工处理
mysqlbinlog --base64-output=decode-rows --start-position=9623 --stop-position=9858  -vv mysql-bin.000002
# 场景2:数据量特别大，需要导出进行处理
mysqlbinlog --start-position=9623 --stop-position=9858 mysql-bin.000002 > ist.sql
# 第三步，增量数据的恢复根据需要把当前binlog记录关掉
mysql> set sql_log_bin=0;
mysql> source ist.sql

数据一定要定时全量备份，开启binlog

作业1：自己实现一下自动全量备份和增量备份组合

2、MySQL的SQL优化索引命中规则

# 执行计划
explain select * from pms_product where id=1;
# 组合索引一定是最左匹配原则
# 如果你在表上建立了很多组合索引，索引文件膨胀，修改、删除、更新会比较慢

• 适合建立的列

• 频繁查询的列

• 有外键关联的列

• 不适合建立的列

• 值变化少的列

• 频繁更新的

• 表的记录比较少：比如配置表

• 如何保证数据库死而不僵

  上来就 create table

  先做E-R图

  设计的时候要满足第三范式

  实际应用中，我们会主动打破第三范式，提升查询效率

• show processlist;

• kill id; 3、数据库架构的设计3.1. 数据库架构设计的步骤

• 逻辑设计

• 物理设计

  数据库结构落库 3.2. 数据库命名

• 数据库名称：小写加下划线

• 数据库名称禁止使用保留字

• 见名知意

• 临时表：tmp_tablename_20200620

• 备份表：bak_tablename_20200620

• 所有存储相同的列名和类型长度必须一致 3.3. 数据库设计规范

• 尽量使用innoDB，v5.6以后innoDB已经是默认引擎

• 字符集统一UTF-8（varchar(255) UTF8 255*3=765个字节）

• 一定要给列加注释

• 控制一个单表的数据量大小

• 控制表的宽度，列限制为4096

• 禁止在表中建立预留字段：ext_float_1,ext_char_2

• 禁止在数据库中存放图片，文件，二进制流

• 不得不存：将内容数据和文件流程数据分开（外键表）需要使用时再关联

• select * 就会将大字段带出，浪费内存、I/O

• 禁止对线上数据库进行压测

• 会产生大量的垃圾数据和日志文件

• 禁止从开发环境连接测试或生产数据库 3.4. 数据库索引设计规范

• 单张表的索引数量建议不超过5个，如果列比较多可以酌情增加

• 每个innodb表都应该有个主键，innodb是一个索引组织表

• 表数据的存放都是按照主键顺序来的

• 如果没有主键，mysql会优先选择一个非空唯一索引来做排序

• 如果非空唯一索引都没有，mysql会自己生成一个36字节的主键，但性能不好

• 不要使用UUID，MD5，HASH等字符串做主键，建议使用增长序列来做主键

• 组合索引的字段匹配是自左向右

• 一般将区分度最高的列放在组合索引最左侧

• 将字段长度小的放最左侧

• 最频繁的放最左侧

• 避免建立冗余和重复索引（index(a,b,c) index(b,c) index(a)）

• 尽量避免使用外键约束 3.5. 数据库字段设计规范

• 优先选择符合存储的最小数据类型

• 避免使用TEXT、BLOB类型

• 避免ENUM类型：修改枚举类型值需要alter语句

• 尽量将列定义为NOT NULL

• 日期格式建议使用timestamp或int来保存 3.6. 数据库的开发规范

• 程序连接数据库的SQL一定使用Preparement

• 降低词法和语法分析的重复执行

• 防止SQL注入

• 索引使用尽量避免前后%

• 使用join或exists来优化in操作

• 不同的应用访问数据库用不同的账号

• 禁止使用不含列名的insert

• 避免子查询（子查询结果集无法使用索引）

• 避免使用JOIN连接过多的表，阿里手册建议不要超3张表

• 减少数据库的交互次数 3.7. 数据库操作行为规范

• 超100w行的批量写操作，分批进行

• 禁止为程序用户授予super权限

• grant all privileges

• 授权的时候遵循权限最小原则

• 当数据库连接慢，MySQL会给super留一个保留连接

2. MySQL数据库分区表应用

2.1. 分区表介绍

MySQL自己本身是支持表的逻辑分区的

查看数据库是否支持分区表

```sql
mysql> show plugins;

为什么要使用分区表：

- 是否遇到几千万的大表
- 查询困难，历史数据是不太关心的
- 如果历史数据要归档，将数据从原来的库中挪走

如果有一种文件组织形式，将2017年的数据放一个文件，将2018的放一个，2019年的放一个，2020年的放一个

这个时候就可以通过MySQL提供的分区表实现

分区表的分区类型

- HASH分区
- LIST分区
- RANGE分区
- KEY分区

## 6.2. HASH分区

- 根据MOD将分区键计算后分到制定表区域
- 可以基本平均的分布
- HASH分区键值必须是INT类型，或者通过函数转成INT

```sql
```shell
CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(customer_id) PARTITIONS 4;

insert into customer_login_log values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log values(2,'2020-06-20 22:30:02',2,1);
insert into customer_login_log values(3,'2020-06-20 22:30:03',3,1);
insert into customer_login_log values(4,'2020-06-20 22:30:04',4,1);

创建以后的内容

-rw-r----- 1 mysql mysql  8767 Jun 20 22:30 customer_login_log.frm
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p3.ibd

查看分区表是否创建成功

```shell

```sql
mysql> explain partitions select * from customer_login_log;

查询每个分区多少数据

```sql

```sql
select table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='customer_login_log';

查询具体的某个分区数据

```sql
select * from customer_login_log partition(p1,p2);
select * from customer_login_log partition(p3) where customer_id=3;

## 6.3. LIST分区

- 按照分区键的枚举来进行分区的
- 各分区的列表不能重复
- 每一行数据都必须要找到对应的分区才能插入数据

```shell
CREATE TABLE `customer_login_log_list` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(login_type)(
	PARTITION jishu VALUES in (1,3,5,7,9),
	PARTITION oushu VALUES in (2,4,6,8)
);

insert into customer_login_log_list values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log_list values(2,'2020-06-20 22:30:02',2,2);
insert into customer_login_log_list values(3,'2020-06-20 22:30:03',3,3);
insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,4);

如果分区键不在分区中

mysql> insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,0);
ERROR 1526 (HY000): Table has no partition for value 0

2.4. RANGE分区

• 根据分区的不同范围值将数据放不同文件中

• 多个分区要连续，不能重叠

• 要有封口的MAXVALUE

CREATE TABLE `customer_login_log_range` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020),
	PARTITION maxyear VALUES LESS THAN MAXVALUE
);

insert into customer_login_log_range values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range values(9,'2023-06-20 22:30:04',4,4);

如果我们需要后期新增range分区，就不能maxvalue封口

CREATE TABLE `customer_login_log_range1` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020)
);

alter table customer_login_log_range1 add PARTITION(
PARTITION y2021 VALUES LESS THAN (2021),
PARTITION y2022 VALUES LESS THAN (2022),
PARTITION y2023 VALUES LESS THAN (2023)
)

一个没有创建分区的表

CREATE TABLE `customer_login_log_range_no` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into customer_login_log_range_no values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range_no values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range_no values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range_no values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(9,'2023-06-20 22:30:04',4,4);

alter table customer_login_log_range_no PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020),
	PARTITION y2021 VALUES LESS THAN (2021),
	PARTITION y2022 VALUES LESS THAN (2022),
	PARTITION maxyear VALUES LESS THAN MAXVALUE
)
# 数据会按照分区规则进行数据重新组装，数据会进入相应分区

如果要删除分区使用命令，不能直接删除文集

alter table customer_login_log_range drop partition y2017;

关于数据架构设计中数据库高阶应用示例分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

网页名称：数据架构设计中数据库高阶应用示例分析
当前URL：http://kswjz.com/article/ishehi.html