数据库应用与管理

事务简介

含义：操作的集合

事务操作：

1. 开启事务（start transaction），
2. 提交事务（commit），
3. 回滚事务（rollback）；

特性：

1. 原子性：（要么全部成功，要么全部失败），
2. 一致性：（若存在事务异常，需要回滚事务）设置提交方式：autocommit = 0 手动提交（需要配合commit指令提交操作集合（事务））
3. 隔离性：不同事务间不会相互影响
4. 持久性：一旦事务提交或回滚，数据库里的数据将永久保存。 

并发事务问题：

1. 脏读：一个事务A读到，另一个事务B未提交（uncommit）的数据。
2. 可重复读：一个事务读同一条记录，两次读取数据不同（其他事务（已提交）对记录进行更改）
3. 幻读：一个事务两次重复读数据，但数据行数不同（其他事务（已提交）对记录进行更改）

事务隔离级别：

隔离级别越高：并发性能越差，数据安全性最高。

查看事务隔离级别：select @@transaction_isolation 

注：mysql默认隔离级别：可重复读

 

 

 

存储引擎

含义：存储数据、建立索引、更新/查询数据的实现方式。

INNODB：特点：

1. 事务：dml（增删改查）遵循ACID原则 （myisam不支持）
2. 行级锁：提高并发访问性能 （myisam不支持，支持表锁）
3. 外键（foreign key）：保证数据完整性与正确性

逻辑存储结构：

表（段（区（页（行））））一个区固定1m；一个页固定16k；故：一个区 = 64页；

索引

含义： 目录作用的有序数据结构(B+树）。无索引的获取数据的方式 ----> 全表扫描（效率低）

优缺点：

 结构问题：

二叉树：层级多，i/o多搜索效率低

B树：所有节点都会保存数据，导致一层可以查找的数据量少，同量数据B数的层级比B+树多

Hash：无法支持范围匹配与排序操作。

索引分类：

 

 聚集索引选取规则：

• 辅助索引 叶子结点 ---> 聚焦索引（主键索引）
• 主键索引 叶子结点 ---> 数据

回表查询：先走二级索引获得主键值，在通过主键值获得行数据信息

索引使用规则：最左前缀原则

索引失效：

模型树空运最快；

• 模：代表模糊查询的意思，我们在使用like关键字的时候，如果是%开头，那么索引就会失效。
• 型：代表数据类型，如果数据类型错误了，索引也会失效。
• 数：代表函数的意思，对索引的字段使用内部函数，索引也会失效，这种情况应该建立基于函数的索引。
• 空：代表英文的 null 的意思，索引不存储空值，如果不限制索引列是 Not Null，数据库会认为索引列有可能存在空值，所以也不会按照索引进行计算。
• 运：代表运算的意思，对索引列进行加、减、乘、除等运算，会导致索引失效。
• 最：代表最左原则的意思，在复合索引中，索引列的顺序非常重要，如果不是按照索引列最左列开始进行查找，则无法使用索引。
• 快：代表全表扫描更快的意思，若数据库预计使用全表扫描比使用索引更快就不会使用索引。

覆盖索引： 

通过建立普通索引或联合索引，避免回表查询的查询策略，直接通过二级索引拿到行数据。

• 通过二级索引获取想要所有内容，无需回表进行聚焦索引搜索，覆盖索引。
• 通过二级索引无法获取全部内容，需要回表查询。

索引设计原则：

1. 数据量大于10w级，查询较频繁
2. 经常使用查询排序分组的字段索引
3. 尽量使用唯一索引，区分度越高索引效率越高
4. 长字段数据，根据字段特点建立前缀索引
5. 尽量多使用联合索引，实现索引覆盖减少回表查询
6. 控制索引数量，多索引维护代价大，内存占用多，影响增删改效率
7. 不能存储NULL至，可通过NOT NULL进行约束。

SQL优化： 

• insert优化（一条一条插入效率低）

 批量插入：

 手动事务提交：

 

 ！！巨量数据插入：load （符合一定规则的字符串（不符合sql语法））

• 主键优化

页分裂：

插入数据：

数据页分裂（50%页移植到新页，再插入） 

 链表顺序重拍（顺序排并）

页合并

前页存在多删，满足50%空闲，判断可否页合并

 页合并，空闲新页

 设计原则：

• 降低主键长度
• 尽量顺序操作，否则页分裂
• 尽量不用无序UUID作为主键（身份证号
• limit 优化

L:在访问巨值的限制查询，需要将0-巨量数据尾进行排列，取对应的限制区间，耗时长效率低。

解决思路：覆盖索引，

• update 优化

若通过索引进行事务update，不同行间相互隔离，可以提交（上行级锁）

若通过无索引字段（如name）进行事务update，不同行间无法同时提交（上表级锁）

 锁

• 粒度分类：

全局锁 (实现一致性数据备份）：锁住数据库全部的表，[ 实例变成只读状态，无法进行DML与DDL语句，更新操作全阻塞 

表级锁：每次操作锁住整张表

• 读锁：共享锁 （lock tables） （unlock tables）[可读不可写]
• 写锁：独占锁  [本客户端可读写，其他客户端不可读写]
• 意向锁：解决行锁表锁冲突（表锁在加锁的时候逐行检查行锁）， 不用逐行检查行锁情况。
  • 意向共享锁：与读锁兼容，排他所互斥。
  • 意向排他所：与读锁写锁都互斥。

行级锁：每次操作锁住对应行数据，针对索引项加锁

• 行锁

• 间隙锁（解决幻读现象）
• 临键锁

 InnoDB引擎

存储结构

 内存结构：

脏页：未被刷入磁盘的数据

 change buffer:二级索引修改后，等刷盘时放入bufferPool中，一起更改

redolog（持久性）

：脏页刷盘出现错误，保证事务的持久性（先保存至redobuffer 刷盘到 redolog文件（WAL模式：先存日志后刷数据（顺序磁盘IO效率高））每读一段数据redolog会变更。

注意：BufferPool中是将多操作随机操作数据页，随机刷新进入磁盘，随机IO的性能效率低！！！

undolog（原子性）

：电脑宕机回滚事务、事务更新后系统不稳定需要回滚版本、MVCC

 MVCC;

隐式字段 

 

 readview

 

 MYSQL语句执行原理