【mysql】索引、事务、日志、备份

---

前言

本博客内容仅为记录博主思路，仅供参考，一切以自己实践结果为准。

---

一、索引

1.1 概念

• 功能：查找索引后，直接指向数据所在的物理地址
• 意义：加速查询速度，为字段排序
• 副作用：额外占用磁盘空间，修改数据时需要同步修改索引（较麻烦）

---

1.2 分类/操作

类别/操作	创建方法
普通索引	alter table 表 add index 字段_index (字段);
唯一索引	alter table 表 add unique index 字段_index (字段);
主键索引	alter table 表 add primary key;
组合索引	alter table 表 add index 索引名 (字段1,…);
全局索引	alter table 表 add fulltext 索引名 (字段);
删除索引	alter table 表 drop index(索引名);
索引分析	explain select * from 字段;

1.3 应用场景

• 唯一性较好的字段
• 超过300行的字段
• 经常表连接的字段
• 不常更新的字段
• 小字段

二、事务

2.1 概念/特性

• 概念：一个不可分割的操作序列
• 特性：原子性（无法分割），隔离性（独立，保证数据安全），一致性（事务的目的），持久性（最终效果）

---

2.2 隔离级别

级别	效果	隔离级别全称
ru：未提交读	允许脏读	read uncommitted
rc：已提交读	不允许脏读	read committed
rr：可重复读	不允许脏读，可重复读（mysql默认级别）	repeatable read
串行读	一次只允许有一个事务	serializable

• 脏读：一个事务可以看到其他事务未提交的修改
• 不可重复读：第一个事务未结束时，恰巧别的事务修改了对应的数据，那么第一个事务刷新再读取的时候，数据就会变为被修改后的数据（可重复读：确保一个事务中执行的select语句都能得到相同结果）
• 幻读：第一个事务修改所有数据的同时，恰巧别的事务添加了新的事务，此时第一个事务并不会修改别的事务刚添加的数据
• 丢失更新：第一个事务读取数据修改的同时，第二个事务也在打开修改数据，此时第二个事务先保存，第一个事务后保存，那么第一个事务就会把第二个事务修改的数据覆盖掉，等同于第二个事务没有进行任何操作
• 注：mysql默认的事务处理级别是repeatable read （可重复读）, 而Oracle和SQL Server是 read committed（已提交读） 。

---

2.3 命令

命令	效果
begin	开启事务
commit	提交事务
rollback	回滚事务到初始
rollback to 快照点名称	回滚事务到某个快照点
savepoint 快照点名称	保存快照点
自动提交事务功能	set autocommit=1;（0为关，默认开启）
全局事务隔离级别	set global transaction isolation level 隔离级别;
当前会话隔离级别	set session transaction isolation level 隔离级别;
查询全局隔离级别	show global variables like ‘%isolation%’ ;
查询会话隔离级别	show session variables like ‘%isolation%’;

三、日志

• 日志是维护中非常重要的存在，通过日志，我们可以快速得到自己想要的东西，比如错误日志产生，效率低下，数据恢复等

3.1 开启日志

• mysql的日志存储路径默认为：/usr/local/mysql/data，默认配置文件为：/etc/my.cnf配置文件中的[mysqld]子配置中

日志分类	开启配置语句	默认情况
错误日志	log-error=存储路径	默认开启
二进制日志	log-bin=mysql-bin	默认开启
慢查询日志	slow_query_log=ON（打开）long_query_time=2（慢查询时间）slow_query_log_file=存储路径（存储位置）	默认关闭
慢查询日志	set global slow_query_log=ON;（在mysql中开启慢查询）	默认关闭
通用日志	general_log=ON（打开）general_log_file=存储路径（存储位置）	默认关闭

3.2 确认日志

• mysql中查看日志是否开启

日志	命令
二进制日志	show variables like ‘log_bin%’;
慢查询日志	show variables like ‘%slow%’;
慢查询时间设置	show variables like ‘long_query_time%’;
通用日志	show variables like ‘general%’;

四、备份与恢复

4.1 完全备份

• 特点：备份/恢复时间长，备份数据全面（对数据库进行完整的备份与恢复）

4.1.1 冷备份/恢复

• 特点：必须关闭mysql服务才可以进行冷备份，在大多数生产环境中是不允许停止服务的（相对逻辑备份快）

#备份
systemctl stop mysqld													#关闭mysql数据库服务
tar zcf /mnt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data			#压缩备份mysql数据并重命名为.tar.gz结尾

#恢复
tar zxf mysql_all-2022-06-05.tar.gz -C /mnt								#解压数据压缩包
\cp -rf ./data /usr/local/mysql/										#将解压后的数据包移动/复制过去即可
systemctl start mysqld													#启动mysql数据库服务

---

4.1.2 逻辑备份/恢复

• 特点：不用关闭mysql的服务也能备份，恢复时候根据存储的mysql语句一条条恢复（相对较慢）
• 工具：利用MySQL自带的备份工具mysqldump进行备份与恢复

mysqldump -u root -p密码 --databases 库 >备份路径								#备份库
mysqldump -u root -p密码 [-d] 库名 [表名1] [表名2] >备份路径    					#备份表（-d表示只备份数据表结构）

查看备份文件：grep -v "^--" 备份路径|grep -v "^-"|grep -v "^$"					#通过过滤查看备份的内容

mysql -u root -p密码 <备份文件													#恢复库
mysqldump -u root -p密码 库 <备份文件											#恢复表

---

4.2 增量备份

• 特点：通过二进制日志间接实现（flush logs刷新日志），没有冗余、备份时间短、恢复时间长

4.2.1 备份类别

• 先在/etc/my.cnf中[mysqld]子配置中设置log-bin=mysql-bin开启二进制日志
• 然后再：binlog_format = MIXED指定二进制日志的记录格式为混合模式

类型	特点
STATEMENT	基于sql语句备份，较快，高并发是容易出错（默认是这个类型）
ROW	基于数据库行内容备份，较慢，安全准确
MIXED	混合模式，兼有两者优点，生产环境一般都用这个

4.2.2 备份命令

mysqladmin -u root -p密码 flush-logs									#备份日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v 备份路径		#查看备份

---

4.2.3 恢复类别

类别	命令
一般恢复	mysqlbinlog --no-defaults 备份路径
位置恢复	mysqlbinlog --no-defaults --start-position=‘位置点’ --stop-position=‘位置点’ 备份路径
时间恢复	mysqlbinlog --no-defaults --start-datetime=‘时间点’ --stop-datetime=‘时间点’ 备份路径

4.3 差异备份

• 由于差异备份会产生冗余较大的情况，因此很少用到差异备份

五、思维导图

六、结语

• 索引是加快查询的一种手段，日志是了解数据库时间的手段，命令较多需要多读多背多练习