數(shù)據(jù)架構(gòu)設(shè)計(jì)中數(shù)據(jù)庫高階應(yīng)用示例分析

這篇文章給大家介紹數(shù)據(jù)架構(gòu)設(shè)計(jì)中數(shù)據(jù)庫高階應(yīng)用示例分析，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

1、數(shù)據(jù)恢復(fù) 備份如何設(shè)計(jì)

• 全量備份的方式

  使用crontab結(jié)合mysqldump來做定時(shí)備份

• 增量時(shí)間點(diǎn)補(bǔ)償

• 如何補(bǔ)償

• 考慮修改的變化：update、delete

• 借助我們的binlog

  # 第一步，先做全量備份,可以定時(shí)處理
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做數(shù)據(jù)導(dǎo)出
# 場景1:如果數(shù)據(jù)量小，比如關(guān)鍵的某一條數(shù)據(jù)，row模式下可以直接手工處理
mysqlbinlog --base64-output=decode-rows --start-position=9623 --stop-position=9858  -vv mysql-bin.000002
# 場景2:數(shù)據(jù)量特別大，需要導(dǎo)出進(jìn)行處理
mysqlbinlog --start-position=9623 --stop-position=9858 mysql-bin.000002 > ist.sql
# 第三步，增量數(shù)據(jù)的恢復(fù)根據(jù)需要把當(dāng)前binlog記錄關(guān)掉
mysql> set sql_log_bin=0;
mysql> source ist.sql

數(shù)據(jù)一定要定時(shí)全量備份，開啟binlog

作業(yè)1：自己實(shí)現(xiàn)一下自動(dòng)全量備份和增量備份組合

2、MySQL的SQL優(yōu)化 索引命中規(guī)則

# 執(zhí)行計(jì)劃
explain select * from pms_product where id=1;
# 組合索引一定是最左匹配原則
# 如果你在表上建立了很多組合索引，索引文件膨脹，修改、刪除、更新會(huì)比較慢

• 適合建立的列

• 頻繁查詢的列

• 有外鍵關(guān)聯(lián)的列

• 不適合建立的列

• 值變化少的列

• 頻繁更新的

• 表的記錄比較少：比如配置表

• 如何保證數(shù)據(jù)庫死而不僵

  上來就 create table

  先做E-R圖

  設(shè)計(jì)的時(shí)候要滿足第三范式

  實(shí)際應(yīng)用中，我們會(huì)主動(dòng)打破第三范式，提升查詢效率

• show processlist;

• kill id; 3、數(shù)據(jù)庫架構(gòu)的設(shè)計(jì) 3.1. 數(shù)據(jù)庫架構(gòu)設(shè)計(jì)的步驟

• 邏輯設(shè)計(jì)

• 物理設(shè)計(jì)

  數(shù)據(jù)庫結(jié)構(gòu)落庫 3.2. 數(shù)據(jù)庫命名

• 數(shù)據(jù)庫名稱：小寫加下劃線

• 數(shù)據(jù)庫名稱禁止使用保留字

• 見名知意

• 臨時(shí)表：tmp_tablename_20200620

• 備份表：bak_tablename_20200620

• 所有存儲(chǔ)相同的列名和類型長度必須一致 3.3. 數(shù)據(jù)庫設(shè)計(jì)規(guī)范

• 盡量使用innoDB，v5.6以后innoDB已經(jīng)是默認(rèn)引擎

• 字符集統(tǒng)一UTF-8（varchar(255) UTF8 255*3=765個(gè)字節(jié)）

• 一定要給列加注釋

• 控制一個(gè)單表的數(shù)據(jù)量大小

• 控制表的寬度，列限制為4096

• 禁止在表中建立預(yù)留字段：ext_float_1,ext_char_2

• 禁止在數(shù)據(jù)庫中存放圖片，文件，二進(jìn)制流

• 不得不存：將內(nèi)容數(shù)據(jù)和文件流程數(shù)據(jù)分開（外鍵表）需要使用時(shí)再關(guān)聯(lián)

• select * 就會(huì)將大字段帶出，浪費(fèi)內(nèi)存、I/O

• 禁止對線上數(shù)據(jù)庫進(jìn)行壓測

• 會(huì)產(chǎn)生大量的垃圾數(shù)據(jù)和日志文件

• 禁止從開發(fā)環(huán)境連接測試或生產(chǎn)數(shù)據(jù)庫 3.4. 數(shù)據(jù)庫索引設(shè)計(jì)規(guī)范

• 單張表的索引數(shù)量建議不超過5個(gè)，如果列比較多可以酌情增加

• 每個(gè)innodb表都應(yīng)該有個(gè)主鍵，innodb是一個(gè)索引組織表

• 表數(shù)據(jù)的存放都是按照主鍵順序來的

• 如果沒有主鍵，mysql會(huì)優(yōu)先選擇一個(gè)非空唯一索引來做排序

• 如果非空唯一索引都沒有，mysql會(huì)自己生成一個(gè)36字節(jié)的主鍵，但性能不好

• 不要使用UUID，MD5，HASH等字符串做主鍵，建議使用增長序列來做主鍵

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

• 一般將區(qū)分度最高的列放在組合索引最左側(cè)

• 將字段長度小的放最左側(cè)

• 最頻繁的放最左側(cè)

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

• 盡量避免使用外鍵約束 3.5. 數(shù)據(jù)庫字段設(shè)計(jì)規(guī)范

• 優(yōu)先選擇符合存儲(chǔ)的最小數(shù)據(jù)類型

• 避免使用TEXT、BLOB類型

• 避免ENUM類型：修改枚舉類型值需要alter語句

• 盡量將列定義為NOT NULL

• 日期格式建議使用timestamp或int來保存 3.6. 數(shù)據(jù)庫的開發(fā)規(guī)范

• 程序連接數(shù)據(jù)庫的SQL一定使用Preparement

• 降低詞法和語法分析的重復(fù)執(zhí)行

• 防止SQL注入

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

• 使用join或exists來優(yōu)化in操作

• 不同的應(yīng)用訪問數(shù)據(jù)庫用不同的賬號(hào)

• 禁止使用不含列名的insert

• 避免子查詢（子查詢結(jié)果集無法使用索引）

• 避免使用JOIN連接過多的表，阿里手冊建議不要超3張表

• 減少數(shù)據(jù)庫的交互次數(shù) 3.7. 數(shù)據(jù)庫操作行為規(guī)范

• 超100w行的批量寫操作，分批進(jìn)行

• 禁止為程序用戶授予super權(quán)限

• grant all privileges

• 授權(quán)的時(shí)候遵循權(quán)限最小原則

• 當(dāng)數(shù)據(jù)庫連接慢，MySQL會(huì)給super留一個(gè)保留連接

2. MySQL數(shù)據(jù)庫分區(qū)表應(yīng)用

2.1. 分區(qū)表介紹

MySQL自己本身是支持表的邏輯分區(qū)的

查看數(shù)據(jù)庫是否支持分區(qū)表

```sql
mysql> show plugins;

為什么要使用分區(qū)表：

- 是否遇到幾千萬的大表
- 查詢困難，歷史數(shù)據(jù)是不太關(guān)心的
- 如果歷史數(shù)據(jù)要?dú)w檔，將數(shù)據(jù)從原來的庫中挪走

如果有一種文件組織形式，將2017年的數(shù)據(jù)放一個(gè)文件，將2018的放一個(gè)，2019年的放一個(gè)，2020年的放一個(gè)

這個(gè)時(shí)候就可以通過MySQL提供的分區(qū)表實(shí)現(xiàn)

分區(qū)表的分區(qū)類型

- HASH分區(qū)
- LIST分區(qū)
- RANGE分區(qū)
- KEY分區(qū)

## 6.2. HASH分區(qū)

- 根據(jù)MOD將分區(qū)鍵計(jì)算后分到制定表區(qū)域
- 可以基本平均的分布
- HASH分區(qū)鍵值必須是INT類型，或者通過函數(shù)轉(zhuǎn)成INT

```sql
```shell
CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
  `login_time` datetime NOT NULL COMMENT '用戶登錄時(shí)間',
  `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);

創(chuàng)建以后的內(nèi)容

-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

查看分區(qū)表是否創(chuàng)建成功

```shell

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

查詢每個(gè)分區(qū)多少數(shù)據(jù)

```sql

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

查詢具體的某個(gè)分區(qū)數(shù)據(jù)

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

## 6.3. LIST分區(qū)

- 按照分區(qū)鍵的枚舉來進(jìn)行分區(qū)的
- 各分區(qū)的列表不能重復(fù)
- 每一行數(shù)據(jù)都必須要找到對應(yīng)的分區(qū)才能插入數(shù)據(jù)

```shell
CREATE TABLE `customer_login_log_list` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
  `login_time` datetime NOT NULL COMMENT '用戶登錄時(shí)間',
  `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);

如果分區(qū)鍵不在分區(qū)中

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分區(qū)

• 根據(jù)分區(qū)的不同范圍值將數(shù)據(jù)放不同文件中

• 多個(gè)分區(qū)要連續(xù)，不能重疊

• 要有封口的MAXVALUE

CREATE TABLE `customer_login_log_range` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
  `login_time` datetime NOT NULL COMMENT '用戶登錄時(shí)間',
  `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分區(qū)，就不能maxvalue封口

CREATE TABLE `customer_login_log_range1` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
  `login_time` datetime NOT NULL COMMENT '用戶登錄時(shí)間',
  `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)
)

一個(gè)沒有創(chuàng)建分區(qū)的表

CREATE TABLE `customer_login_log_range_no` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
  `login_time` datetime NOT NULL COMMENT '用戶登錄時(shí)間',
  `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
)
# 數(shù)據(jù)會(huì)按照分區(qū)規(guī)則進(jìn)行數(shù)據(jù)重新組裝，數(shù)據(jù)會(huì)進(jìn)入相應(yīng)分區(qū)

如果要?jiǎng)h除分區(qū)使用命令，不能直接刪除文集

alter table customer_login_log_range drop partition y2017;

關(guān)于數(shù)據(jù)架構(gòu)設(shè)計(jì)中數(shù)據(jù)庫高階應(yīng)用示例分析就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。