mysql MySQL数据库备份优化

2020-10-26 本文已影响0人 dylan丶QAQ

起因：在工作中常常要用到mysql，平常只是对数据库crud，并没有认真的了解过她，sql语句也只是会一些最基本的，和常用的，一些不常用的都要去网上百度，即决定学习一下mysql，来了解她，虽然开发很多都是黑盒，但追本溯源总是我们想要的。

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

1.3. statement模式下数据恢复

自己drop database一次，并通过binlog进行恢复恢复的过程也记录进binlog了是否需要记录？

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.数据库出现问题后如何死而不僵

1mysql> show processlist;
2mysql> kill pid;

不要以为每天把功能完成了就行了，这种思想是要不得的，互勉~！