数据库基础 and MySQL（一）
数据库基础 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..)情况的工具
-
