一、存储引擎概述
- MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
- 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
- MySQL常用的存储引擎：
- MyISAM
- InnoDB
- MySQL 数据库中的组件，负责执行实际的数据I/O操作
- NySQL 系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储
1.1、存储引擎的工作过程
二、MyISAM 存储引擎
- MySQL不支持事务，也不支持外键约束，只支持全文索引、数据文件和索引文件是分开保存的
- 访问速度快，对事务完整性没有要求
- MyISAM 适合查询、插入为主的应用
- MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
- .frm文件存储表结构的定义
- 数据文件的扩展名为：.MYD (MYData)
- 索引文件的扩展名为：.MYI (MYIndex)
- 表级锁定形式，数据在更新时锁定整个表
- 数据库在读写过程中相互阻塞
- 会在数据写入的过程阻塞用户数据的读取
- 也会在数据读取的过程中阻塞用户的数据写入
- 数据单独写入或读取，速度过程较快且占用资源相对少
- MyISAM 支持的存储格式：
- 静态表
- 动态表
- 压缩表
2.1、MyISAM 3中存储格式
2.1.1、静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多
2.1.2、动态表
动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 optimize table 语句或myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难
2.1.3、压缩表
压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。
三、InnoDB 存储引擎
- 支持事务，支持4个事务隔离级别
- MySQL从5.5.5版本开始，默认存储引擎为InnoDB
- 读写阻塞与事务隔离级别相关
- 能非常高效的缓存索引和数据
- 表和主键以簇的方式存储
- 支持分区、表空间、类似Oracle 数据库
- 支持外键约束、支持全文索引
- 对硬件资源要求还是比较高的场合
- 行级锁定、但是全表扫描仍然会是表级锁定
3.1、InnoDB 行锁与索引的关系
InnoDB行锁时通过给索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来记录加锁
1、delete from t1 where id=1;
如果id字段是主键，innodb对于主键使用了聚簇索引，会直接锁住整行记录。
2、delete from t1 where name='aaa';
如果name字段是普通索引，会先锁住索引的两行，接着会锁住相应主键对应的记录
3、delete from t1 where age=23;
如果age字段没有索引，会使用全表扫描过滤，这时表上的各个记录都将上锁。
四、InnoDB 与 MyISAM的区别
- MyISAM：不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，适用于不需要事务处理，单独写入或者查询的应用场景
- InnoDB：支持事务处理，外键约束，占用空间比MyISAM大，支持行级锁定，读写并发能力较好，适用于一致性要求高、数据更新频繁的应用场景
五、查看、修改存储引擎
5.1、查看系统支持的存储引擎
show engines;
5.2、查看表使用的存储引擎
show tabke status from 库名 where name='表名'\G
方法二：
use 库名;
show create table 表名;
5.3、修改存储引擎
1、通过alter table 修改
use 库名;
alter table 表名 engine=MyISAM;
2、通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
vim /etc/my.cnf
...
[mysqld]
...
default-storage-engine=InnoDB
systemctl restart mysql
tips：此方法只对修改了配置文件并重置mysql服务后新创建的表有效，已经存在的表不会产生影响
3、通过 create table 创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;
六、死锁
死锁：一般是事务互等对方资源，最后形成环路造成的。
如何尽可能避免死锁？
- 以固定的顺序访问表和行
- 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
- 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择，
- 为表添加合理的索引，如果使用索引将会为表的每一行记录加上锁，死锁的概率大大增大
七、MySQL日志管理、备份、恢复
7.1、日志的分类
MySQL的日志默认保存位置为： /usr/local/mysql/data
vim /etc/my.cnf
[nysqld]
## 错误日志 用来记录当Mysql启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log ##指定日志的保存位置和文件名
## 通用查询日志，用来记录MySQL的所有连接和语句，默认关闭
general_log=on
general_log_file=/usr/local/mysql/data/mysql_general.log
## 二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin #也可以 log_bini=mysql-bin
## 慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=on
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
systemctl restart mysqld
7.2、查看日志
mysql -uroot -p
show variables like 'general&'; # 查看通用查询日志是否开启
show variables like 'log_bin%'; # 查看二进制日志是否开启
show variables like '%slow%'; # 查看慢查询日志功能是否开启
show variables like 'long_query_time';# 查看慢查询时间设置
get global slow_query_log=on; # 在数据库中设置开启慢查询的方法
八、数据的备份
8.1、备份的重要性
- 备份的主要目的是灾难恢复
- 在生产环境中，数据的安全性至关重要
- 任何数据的丢失，都可能产生严重的后果
8.2、数据库备份的分类
8.2.1、从物理与逻辑的角度
- 物理备份：对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
- 冷备份(脱机备份)：是在关闭数据库的时候进行的
- 热备份(联机备份)：数据库处于运行状态，依赖于数据库的日志文件
- 温备份：数据库锁定表格(不可写入、但可读)的状态下进行备份
- 逻辑备份：对数据库逻辑组件(如：表等数据库对象)的备份
8.2.2、从数据库备份策略角度
- 完全备份：每次对数据库进行完整的备份
- 差异备份：备份自从上次完全备份之后被修改过的文件
- 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
8.2.2.1、完全备份
- 是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
- 优点：备份与操作简单方便
- 缺点：
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长
8.3、常用的备份方法
- 物理冷备
- 备份时数据库处于关闭状态，直接打包数据库文件
- 备份速度快，恢复时最简单
- 专用备份工具mydump 或者 mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
- 启用二进制日志进行增量备份
- 进行增量备份，需要刷新二进制日志
- 第三方工具备份
- 免费的mysql热备份软件Perocona XtrBackup
