行业资讯

数据架构设计中数据库高阶应用示例分析

发布时间：2022-01-05 09:09:50 作者：柒染
来源：亿速云 阅读：154

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

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

  # 第一步，先做全量备份,可以定时处理
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;
# 组合索引一定是最左匹配原则
# 如果你在表上建立了很多组合索引，索引文件膨胀，修改、删除、更新会比较慢

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分区

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;

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

推荐阅读：
  1. JS高阶函数应用
  2. XML数据库的示例分析

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

数据库

上一篇：ZFS怎么使用

下一篇：glusterfs volume管理相关问题有哪些

相关阅读

您好，登录后才能下订单哦！

密码登录
登录注册
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
产品服务
地区划分
帮助支持
关于我们
行业资讯-文章归档 问答-问答归档
广州亿速云计算有限公司

7*24小时在线电话：400-100-2938

7*24小时在线QQ：800811969

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有
粤ICP备17096448号-1 粤公网安备 44010402001142号