数据库进阶

目录

一、事务

1.1 什么是事务

1.2 事务的操作

1.3 并发事务问题

二、存储引擎

2.1 mysql体系结构 

2.2 什么是存储引擎

2.3 存储引擎类型

2.3.1 InnoDB

2.3.2 Myisam

2.3.3 Memory

2.4 存储引擎选择

 三、索引

3.1 什么是索引

 3.2 索引分类

3.2.1 聚集索引和二级索引

 3.3 索引语法

3.3.2 基本语法

 3.3.2 查询频次

 3.4 索引使用原则

最左前缀原则

范围查询

索引失效

覆盖索引

前缀索引

 联合索引结构

 索引设计原则

3. 5 SQL提示

 四、锁

4.1 锁分类

4.2 全局锁

 4.3 表级锁

4.3.1 表锁

 4.3.2 元数据锁

4.3.3 意向锁​

4.4 行级锁

 4.4.1 行锁

 五、InnoDB引擎

5.1 逻辑存储结构

5.2 架构

5.2.1 内存结构

 5.3 事务原理

5.4 MVCC

5.4.1 基本概念

 5.4.2 MVCC组件

 5.4.3 MVCC实现原理

---

一、事务

1.1 什么是事务

一组不可分割的操作序列，要么全部完成，要么全部不完成

1.2 事务的操作

• 查看/设置事务提交方式： select @@autocommit; 默认自动提交

• 修改事务自动提交： set @@autocommit = 0;
• 查看事务的隔离级别：select @@transaction_isolation 

• 修改事务隔离级别：set [session|global] transaction isolation level read uncommitted（隔离级别）;

1.3 并发事务问题

1. 读未提交， 可能发生脏读，幻读，不可重复读

脏读描述：两个事务a, b, 开启事务后，b在a两次查询中间修改数据但没有提交；a却能读到

a事务：

b事务：

 2. 读已提交， 可以解决脏读

设置隔离级别：

不可重复读描述： a，b两个事务，b在a事务两次查询中间修改数据并提交，造成数据不一致

a事务 

 b事务

 3. 可重复读， 不能解决幻读问题

幻读描述：a，b两个事务，a查询n条数据，想插入第n + 1条，但是b此时进行插入并提交，导致a插入失败

修改隔离级别：

a事务

b事务

         

 4. 可串行化，解决所有问题，但是效率最低

二、存储引擎

2.1 mysql体系结构 

连接层：客户端登录后，在这里进行认证，检查用户信息

服务层：提供核心功能的部分

引擎层：有若干具体引擎的实现；mysql默认innodb, index数据结构在这里被实现；可自定义自己的存储引擎，对外提供相同的接口，可实现可插拔。

存储层：数据最终保存在磁盘当中

2.2 什么是存储引擎

        存储引擎是存储数据，简历索引，更新/查询数据等技术的实现方式；不同的应用场景选择不同的存储引擎，其基于表，不同的表可以使用不同的存储引擎

2.3 存储引擎类型

2.3.1 InnoDB

mysql5.5后默认引擎

• 特点：事务，外键，行级锁

•  文件

•  innodb的逻辑存储结构：

 Page是磁盘操作的最小单元，固定大小16K;

区固定大小1M, 最多存64页

2.3.2 Myisam

 索引，数据分开存储

2.3.3 Memory

数据存储在缓存中，容易系统故障造成数据丢失；可以使用hash索引 

2.4 存储引擎选择

 三、索引

3.1 什么是索引

        mysql中高效获取数据的有序的数据结构

无索引进行全表扫描

有索引的情况通过一种数据结构维护有序索引

索引的优缺点：

 3.2 索引分类

 hash索引只有memory支持；hash索引只支持精确查找（因为hash无序，离散分布）

InnoDB存储引擎为什么选择 b+树

 B+ tree中的数据都存放在叶子节点中并采用双向链表连接，磁盘读写更稳定 

3.2.1 聚集索引和二级索引

 思考题：

 3.3 索引语法

3.3.2 基本语法

 3.3.2 查询频次

show [global|session] status like 

 3.4 索引使用原则

最左前缀原则

        针对组合索引，查询从索引最左侧开始，不跳过中间索引。

        如果跳过某一列，索引部分失效

        最左前缀失效，进行全表扫描

范围查询

        当出现>, <等范围查询时，右侧的条件索引失效；

        在业务允许范围内，尽量使用 >= 或者 <=, 索引依然生效

索引失效

1. 不符合最左前缀

2.出现范围查询

3.索引列上进行函数计算

4.使用字符串类型的列时不加单引号，索引失效

5. 进行模糊匹配，如果是头部模糊：%abc，索引失效

6.or连接的条件查询，两侧字段都必须有索引，否则都不执行

覆盖索引

尽量使用覆盖索引（查询中使用了索引，并且需要返回的列在索引中都能找到），减少select * 

 发生索引覆盖，不需要回表查询

没有索引覆盖，发生回表

 而使用select * ，很容易出现回表查询，索引不建议使用

前缀索引

 联合索引结构

 索引设计原则

3. 5 SQL提示

 四、锁

4.1 锁分类

4.2 全局锁

加锁后整个数据库实例处于只读状态，后续DDL， DML不能操作数据库；典型场景是数据库全库逻辑备份。 

 底层使用快照读操作进行一致性备份

 4.3 表级锁

锁住整张表。锁粒度大，发生锁冲突概率高。

4.3.1 表锁

1. 共享读锁，支持DQL， 不支持DML

 2.  独占写锁, 加锁用户正常，其他用户DQL， DML都不支持

 4.3.2 元数据锁

4.3.3 意向锁

# 意向共享锁
select ... lock in share mode
# 意向排他锁
insert, update, delte, select ... for update

4.4 行级锁

行级锁是基于索引组织的

 4.4.1 行锁

 

 五、InnoDB引擎

5.1 逻辑存储结构

5.2 架构

5.2.1 内存结构

BufferPool

 ChangeBuffer：主要针对非唯一的二级索引

自适应hash， 系统自行判断，如果可以优化BufferPool的查询速度就使用

 日志缓冲区

 5.3 事务原理

Innodb中的事务四个特性的实现原理：

redo log 重做日志，记录事务提交时数据页的物理修改

保证在脏页刷新下，使用redolgo进行数据恢复，维持数据持久性

undo log 回滚日志

5.4 MVCC

5.4.1 基本概念

 

 5.4.2 MVCC组件

隐藏字段：

 undo log 回滚日志

insert事务提交后，日志可以直接删除；update， delete的日志在快照读的时候也需要，不能立即删除。

undolog 版本链：不同事务或同一事务对同一记录进行修改，导致undolog生成一系列的版本信息

 readview：快照读执行读取历史数据的依据，记录维护当前活跃(即未提交)的事务id,核心字段如下：

 5.4.3 MVCC实现原理

 

 

 事务的特性：

原子性，一组操作要么全部完成，要么都不执行 -》 事务回滚解决失败

一致性，数据执行前后一致 -》 事务回滚解决失败

持久性，redolog, 能够实现脏页写入错误时的应急措施

 隔离性，MVCC + 锁