数据库基础 and MySQL(一)
-
利用到存储引擎(自动提交特性)实现事务机制,InnoDB称为事务安全型引擎,MyISAM不支持事务
MySQL在5.5之前,MyISAM是MySQL的默认存储迎引擎,支持全文索引、压缩和空间函数等,但是不支持就事务、行级锁、外键…,奔溃之后无法安全恢复
5.5版本之后使用InnoDB -
事务的ACID特性
- 原子性(Atomicity):事务是一个不可分割的最小工作单元
- 一致性(Consistency):保持事务前后数据的完整性一致
- 隔离性(Isolation):存在并发事务处理的时候,不同事务之间的数据是隔离开的 — 考虑下怎么做到事务之间隔离的,隔离级别的实现
- 持久性(Durability):事务提交之后,事务对数据的修改是永久性的
MySQL InnoDB使用redo log(重做日志)来保证事务的持久性,通过undo log(回滚日志)保证事务的原子性,通过锁机制(行锁加间隙锁)和MVCC保证事务的隔离性,保证了原子性、持久性、隔离性之后一致性才能得到保证
-
事务隔离级别
发生事务过程中可能出现的问题
脏读:事务B读到事务A未提交的数据,之后事务A发生回滚,事务B读到了不存在的数据这就是脏读
不可重复读:在同一个事务内,两次查询同一批数据结果是不一样的,可能其他事务存在更新操作
幻读:是相对于插入和删除操作而言的,事务A修改(update)数据库表中的部分行,这时事务B向表中插入(insert)数据,事务A再查询数据库表时就会发现还有未更新的数据,产生幻觉这就是幻读SQL标准定义的四种隔离级别:读未提交(READ UNCOMMITTED),读提交(READ COMMITTED),可重复读(REPEATABLE READ),串行化(SERIALIZABLE)
隔离级别 脏读 不可重复读 幻读 读未提交 可能 可能 可能 读提交 不可能 可能 可能 可重复读 不可能 不可能 可能 串行化 不可能 不可能 不可能 MySQL的默认事务隔离级别是可重复读,但是MySQL通过使用行锁+间隙锁组合Next-Key锁解决了幻读问题
可重复读的实现原理则是采用了MVCC(多版本并发控制)的方式,为每行数据添加隐藏字段DB_TRX_ID(事务ID)、DB_ROLL_PTR(滚动指针,指向回滚段撤销日志记录—“以我启动的时刻为准,如果一个数据版本是在我启动之前生成的,就认;如果是我启动以后才生成的,我就不认,我必须要找到它的上一个版本,同时自己的修改还是认的” -
delete & truncate & drop
- delete是逐条删除,不会删除表结构,保留auto_increment值,属于DML(data maintain language)语言事务提交之后生效
- truncate清空数据(删除表之后重建表),不会删除表结构,但会重置auto_increment值,属于DDL(data define language)语言操作立即生效
- drop将对象所占用的空间全部释放,包括数据、表结构和索引等,属于DDL(data define language)语言操作立即生效
-
数据库建表范式
- 第一范式:字段不能再进行拆分
- 第二范式:在第一范式的基础上消除部分依赖(存在属性A/B/C,关系[A,B]->C、A->C、B->C)
- 第三范式:在第二范式的基础上消除传递函数依赖(存在关系[A/B/C],A->B、B->C,认为C对A存在传递函数依赖)
-
MySQL使用不同字符集进行比较之前需要进行转换,这个时候会造成索引失效
-
redo log(重做日志)保证MySQL拥有了奔崩溃恢复能力,保证数据的持久性和完整性
redo log产生过程:数据页加载到Buffer Pool -> 数据页数据做了修改操作 -> 记录重做日志redo log buffer -> 文件系统缓存(page cache) -fsync-> 磁盘(redo.file )
innoDB刷盘策略提供了innodb_flush_log_at_trx_commit 参数:- 0 => 每次事务提交不进行刷盘操作
- 1 => 每次事务提交都将进行刷盘操作
- 2 => 每次事务提交时都只把 redo log buffer 内容写入 page cache
刷盘时机:①innoDB后台线程每隔1秒把redo log buffer的内容将写到文件缓存(page cache),然后调用fsync刷盘;②当redo log buffer占用空间达到innodb_log_buffer_size一半时,后台线程会主动刷盘
redo log日志文件组 : 硬盘上存储了多个redo log日志文件,每个文件的大小都是一样的,采用环形数组的形式循环写,两个重要属性是①write pos当前记录位置,边写边后移;②当前擦除位置,同样往后推移,MySQL加载日志文件组恢复数据时后移 -
binlog是逻辑日志,用来记录语句的原始逻辑,可以保证MySQL集群架构的数据的一致性,binlog 日志有三种格式,可以通过binlog_format参数指定
- statement:记录SQL语句,会出现数据不一致的问题,例如now()
- row:需要使用mysqlbinlog工具解析,存储的日志保存了数据的详细信息,但是需要消耗更多的资源和IO
- mixed:折中方式,判断产生数据不一致时,使用row,反之使用statement
-
两阶段提交:redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,如果在事务提交之后binlog写入异常,这时两种日志逻辑是不一致的,数据恢复就会存在问题,两阶段提交这样子就出现了
将redo log写入拆分成两个步骤prepare和commit,在事务中发生更新操作时,写入redo log处于prepare阶段,当写入binlog之后,将状态设置为commit阶段
为什么需要两阶段提交?
- 先写 redo log 直接提交,然后写 binlog,假设写完 redo log 后,机器挂了,binlog 日志没有被写入,那么机器重启后,这台机器会通过 redo log 恢复数据,但是这个时候 bingog 并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。 - 先写 binlog,然后写 redo log,假设写完了 binlog,机器异常重启了,由于没有 redo log,本机是无法恢复这一条记录的,但是 binlog 又有记录,那么和上面同样的道理,就会产生数据不一致的情况。
-
MySQL主要分为Server和存储引擎层:
①server层主要包括连接器(身份认证和权限相关操作)、查询缓存(不实用,8.0版本之后舍弃)、分析器(词法和语法解析)、优化器(确定最优执行方案)、执行器(权限校验并执行调用存储引擎)
②存储引擎层采用可替换插件式架构,NySQL5.5.5版本之后默认使用InnoDB作为存储引擎
-
MySQL性能优化篇
-
以SQL执行情况作为切入点,分为三种情况:单条SQL运行慢、部分SQL运行慢、全部SQL运行慢,可以使用explain来查看SQL执行计划
① 单条SQL运行慢
创建并正确使用索引:尽量避免索引失效情况
垂直拆分:不常用字段单独存表、text等大字段分表、组合查询字段放在同一张表
水平拆分:表的行数超过200万行时,根据id取模的方式对数据进行查询、更新和删除
字段使用简单数据类型:tinyint、smallint、mediumint>int>varchar,尽可能避免字节空间的无效占用② 部分SQL运行慢
进行慢查询分析,通过慢查询日志记录运行时间超过long_query_time阈值的SQL,然后针对所有慢日志进行逐个优化
② 全部SQL运行慢
这种情况说明数据库的承载能力达到了峰值,可以调整数据软(my.cnf)硬件配置来提升性能,也可以采用读写分离的方式减少数据访问冲突
-
从层级角度对MySQL进行优化
① 硬件和操作系统层面硬件:cpu、可用内存大小、磁盘读写速度、网络带宽…
操作系统:应用文件句柄数、网络配置…② 架构设计层面
搭建MySQL主从(主主)集群、读写分离、分库分表…,针对热点数据可以引入更为高效的分布式数据库Redis,MongoDB等,缓解MySQL的IO访问压力
③ MySQL程序配置优化
调整数据库相关软件配置my.cnf,包括数据库最大连接数、缓存池大小等等
④ SQL执行优化
慢查询日志分析 - 获取慢SQL、执行计划分析 - explain、show profile - MySQL提供查看SQL语句资源消耗(cpu,IO…)情况的工具
-