數據架構設計中數據庫高階應用示例分析

這篇文章給大家介紹數據架構設計中數據庫高階應用示例分析，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

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;

關于數據架構設計中數據庫高階應用示例分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。