MySQL数据库备份优化&数据架构设计

---

MySQL数据库备份优化&数据架构设计

	1. 线上数据备份恢复策略实施
	1.1. 备份流程设计
	1.2. 数据恢复流程
	1.3. statement模式下数据恢复
	2. MySQL索引优化
	2.1. Explain执⾏计划分析
	2.2. 索引命中策略分析
	2.3. 索引分析总结
	2.4. 数据库出现问题后如何死⽽不僵
	3. 数据库架构设计
	3.1. 数据库命名规范
	3.2. 数据库设计规范
	3.3. 数据库索引设计规范
	3.4. 数据库字段设计规范
	3.5. 数据库SQL开发规范
	3.6. 数据库操作⾏为规范

1. 线上数据备份恢复策略实施

1.1. 备份流程的设计

	备份⼯具
	备份⽅式

1、考虑数据量：做备份⼯具的选型

	数据量较⼩的情况下：mysqldump逻辑备份，导出的是SQL
	如果我们数据量⾮常⼤：xtrabackup

2、考虑我们的时间点补偿

	从上⼀个全量备份时点到现在这个阶段的数据
	Binlog：statement，row，mixed

3、做我们的备份⽅案

	全量备份 crontab -e （cron语法）定时执⾏备份脚本，时间选择在业务量⼩的时点（记录我们的
	备份的position）
	增量：binlog（statement和mixed模式，我们的SQL都是线性且可执⾏的）
	statement和mixed的增量适⽤于正常情况下数据库⽆法打开使⽤或数据⽂件损坏
	row特别适合单表数据异常恢复
	⼀定要确保线上不会出现直接执⾏的SQL

1.2. 数据恢复流程

	案例1：statement，mixed
	student（id,score）：4点100⾏数据，第⼆天8点的时候更新⼀条score，所有⼈score全变成90
	mysqldump：可以去到⽂件⾥找到student表把insert全部拿出来
	xtrabackup：先要恢复到⼀个新数据库
	把4-8点间所有涉及这个student表的DML除select外，还最后我出错的这个SQL外，所有SQL全部
	拿出来
	按照这个顺序：4点student所有数据执⾏->执⾏Binlog⾥的SQL
	
	案例2：row
	找到position，通过mysqlbinlog导出变更的数据，只能⽤代码去调整他的记录变更为恢复的SQL
	如果数据库不可⽤需要恢复
	⽂件转移到新数据库：物理备份
	如果数据⽂件⽆法启动
	mysqldump全量+row模式下的增量（调整起来就⾮常费劲）
	在备份的基础上，如果条件允许⼀定要做主从HA
	主机：row
	从机：statement

2. MySQL索引优化

	SQL及索引：⾼质量的SQL，避免索引失效
	数据库表结构：范式，⾄少要符合3NF
	系统配置MySQL，Linux
	硬件

2.1. Explain执⾏计划分析

	explain的⽤法:
	
	explain select * from employee where age=40 and name='张⻜'
	
	explain的作⽤:
	
	查看表的读取顺序
	读取操作类型
	哪些索引可⽤
	表之间关联
	每张表有哪些索引被优化器执⾏
	
	type:
	
	system
	const
	eq_ref
	ref
	range
	index
	ALL
	查询的效果从上到下越来越差

2.2. 索引命中策略分析

	最左匹配原则
	在索引字段上加⼊函数：不⾛索引
	is null/is not null：不⾛索引
	覆盖索引：key-value都在索引⾥，如果select columns直接使⽤的是索引列就直接使⽤覆盖索引
	只要索引条件和or挨着：就⽤不上
	!= / <> ：是否能应⽤索引

2.3. 索引分析总结

	优势：
	1、提⾼查询速度
	2、表连接的时候，加速连接
	3、保证数据唯⼀：唯⼀索引
	劣势:
	1、修改和增加数据时会提升开销
	2、索引还会占⽤物理空间
	3、在进⾏⼤量的insert或update、delete时，速度会变慢
	适合建⽴索引:
	1、数据差异化较⼤
	2、频繁查询的列，where条件⾥经常⽤到的
	3、常⽤的表关联字段
	4、查询中统计或分组的字段
	不适合的:
	1、记录值特别少
	2、值变化特别⼩，重复率⾼
	3、经常增删改的表

2.4. 数据库出现问题后如何死⽽不僵

	mysql> show processlist;
	mysql> kill pid;

3. 数据库架构设计

	做架构到底是在做什么？
	抽象能⼒
	抽象-->具象
	逻辑设计：
	1、具体内容：设计数据库的⼀个逻辑结构，与具体的DBMS⽆关，主要反映业务逻辑
	2、设计步骤：⽤关系模型
	3、使⽤⼯具来模型化：E-R图
	矩形：实体对象 1:m，n:m，1:1
	椭圆：属性
	线：关系的连接
	菱形：关系
	4、实体关系模型
	通过表格实现：字段名，类型，⻓度，约束
	实体的实例化和泛化
	5、⾄少满⾜3NF
	物理设计
	对具体数据库进⾏选型：oracle，mysql
	表的字段及存储结构
	实际⼯作中：都是并⾏的

3.1. 数据库命名规范

	所有数据库对象名称：⼩写加下花线分割
		MySQL对象名称在默认情况下是⼤⼩写敏感
		MySQL的对像其实都是⼀个⽂件，⽽linux⽂件名是⼤⼩写敏感
		Dbname / dbname，MyTable / mytable
		开发⾮常麻烦
	所有MySQL数据库对象名称禁⽌使⽤MySQL保留关键字
		⼀定要提前准备⼀份对应版本的关键字表
		建表的时候没问题，但SQL查询就挂了 ``
	所有的数据库对象名称：⻅名知义，但最⻓不要超过32个字符（不要中英⽂混合）
	所有临时表命名：tmp_tablename_20191215
	所有的备份表：bak_tablename_20191215
	索引：idx_ pk_
	所有存储类型相同的列名以及⻓度必须保持⼀致
		order：product_title 50
		erp_instock: product_title 50

3.2. 数据库设计规范

	正常情况下建议使⽤innoDB，v5.6版本后默认都是innoDB
	字符集
		UTF-8
		统⼀字符集避免乱码
		UTF-8的字符集是⼀个汉字3个字节：varchar(255) UTF-8 255*3=765字节
加⼊注释
控制单表的数据量⼤⼩：⾏
	对于⽇志数据，进⾏归档
	对于业务数据进⾏分库分表
分区表谨慎使⽤
控制表宽度
	虽然表没有⾏限制，但列最多4096
	如果列多了，占⽤内存和I/O会⾮常⼤
禁⽌在表中建⽴预留字段：varchar_column，order_second_no，remark，memo
	varchar类型
	违背上⾯的命名规则
	时间久了，不看业务代码，完全是魔⻤字段
禁⽌在数据库⾥存放图⽚、⽂件、⼆进制⽂件
	如果要⽤blob、Text存⼤⽂件，select colums....
	如何避免select * ，外键表单独放单⽂件	
禁⽌对线上环境进⾏压⼒测试
	会产⽣⼤量的垃圾数据和⽇志⽂件
	禁⽌从开发环境、测试环境连接⽣产数据库

3.3. 数据库索引设计规范

单张表索引数量建议不超过5个，如果列多可以适当增加
	索引过多：SQL在进⾏优化器评估的时候会有更⼤的开销
	绝对不允许给表的每⼀列都建⽴索引
每个innodb表都必须有⼀个主键，innoDB表就是⼀个索引组织表
表数据的实际存储顺序只能有⼀种，innoDB是按照主键进⾏存放的
	如果没有主键，mysql会优先选择第⼀个⾮空唯⼀索引来做主键
	如果上⾯这个没有，mysql会⾃动⽣成⼀个36个字节的主键，但性能不好
	不能使⽤更新频繁的列和联合索引做主键，主键不断变，数据的存放顺序就会不断变化
	不要使⽤UUID、MD5、HASH等做主键，不能保证这些值是按顺序增⻓的。如果⽣成较⼩的
	字符串就会导致不断变化数据存储的位置，影响I/O性能
要在哪些列上建⽴索引：没有最好只有最适合
	explain
	where后
	join的连接lie
	筛选项最⼤的放在索引做左侧
避免建⽴冗余和重复索引
对于频繁查询的数据列，优先考虑使⽤覆盖索引
尽量避免加⼊外键约束
	因为外键写⼊的时候会降低存储效率
	但要给这些关联字段加索引