mysql的视图引,索与事务

基础

mysql:开源的关系型数据库管理系统,更像一个软件,核心任务是高效,安全的存储,管理操作数据(在磁盘上存储),InnoDB 是 MySQL 最主流的存储引擎:

• 支持事务
• 支持行级锁改一行锁一行，并发高、不卡死； 对比 MyISAM 是表级锁，一整张表锁住。
• 支持外键可以做表与表的关联约束。
• 聚簇索引结构整张表数据存在聚簇索引 B + 树叶子节点， 二级索引存「索引列 + 主键」，需要回表。
• 崩溃安全恢复有日志，宕机重启能自动恢复数据，不易丢数据。
• 支持 MVCC 多版本并发控制实现读写不阻塞、隔离级别。

分类:

• 关系型数据库:数据作为一张张表格,表格之间可以建立联系.
  数据库:仓库 表:货架 行:商品 列:商品属性 sql:对仓库管理发起的命令
• 非关系型数据库:Nosql:对不遵循关系模型的数据集统称分为

视图

一种虚拟的表,定义由查询语句定义,其本身不存储数据,而是动态引用基表的数据,视图本质上就是一个被保存下来的SELECT句

特点:

• 虚拟性:无实际的物理存储,数据都来源于定义的select所涉及的基表
• 动态性:基表数据发生变化时,视图中的数据也会变化
• 安全性:可以通过视图暴露部分信息,隐藏基表敏感信息
• 简化型:将复杂的查询逻辑封装为视图,无需重复编写sql,直接查视图

场景:

• 简化复杂查询
• 数据权限控制:只允许查看到基表权限中的部分信息,隐藏其他信息时,可以封装为视图
• 通体数据访问接口:当基表的结构变更时(字段名修改类),可通过修改视图定义保持对外修改不变

分类:

• 普通视图:简单,复杂.

简单:基于单表创建,无无聚合、分组、去重、关联查询,可以修改数据

复杂:单 / 多表均可，包含聚合、GROUP BY、DISTINCT、多表连接、子查询,仅支持查询

• 物化视图

物化视图:(mysql原生不支持)

真实物理存储数据，把视图查询结果落地磁盘存起来,预计算、预聚合，查询直接读落地数据，速度极快,可定时刷新数据，保持和基表同步(不刷新的时候不会同步改变)

那为啥要他?反正都存在于磁盘:

首先,他毕竟是视图,肯定有视图的好处,但是他牺牲了内存换速度,普通视图

只存sql,查询实时运算

索引

索引是数据库表的目录结构，依附字段创建，存储字段值与数据行地址映射关系，不额外存完整表数据

优点:

• 加快查询跳过全表扫描，快速定位目标数据
• 约束唯一性唯一索引、主键索引，保障字段数据不重复
• 加速排序分组ORDER BY、GROUP BY 无需临时排序
• 减少锁范围精准定位行，降低并发锁冲突
• 辅助表关联外键索引提升多表联查效率

缺点:

• 占用磁盘存储空间
• 增删改数据时，同步维护索引，拖慢写入速度

• 缓存消耗更大,索引也要加载到innodb缓冲池，挤占内存

由此看来,索引并不是越多越好啊

底层

mysql innoDB默认B+树

• 二叉树：层高太高，磁盘 IO 多；
• B 树：每行带完整数据，节点存放条数少；
• 哈希表无序,不支持范围查询,存在哈希冲突 无法满足常见业务,仅适合等值查询

分类:

1. 按逻辑分

• 主键索引：唯一、非空、一张表只有一个，InnoDB 聚簇索引；
• 唯一索引：字段值唯一，允许一个 NULL；
• 普通索引：最基础，无唯一限制，只加速查询；
• 复合索引：多个字段联合建索引，遵循最左前缀原则；
• 全文索引：长文本模糊检索。
• 哈希索引

2. 按存储结构分

• 聚簇索引：主键就是聚簇索引，叶子节点存整行数据，InnoDB 唯一,也是表本身；
• 二级索引 (辅助索引)：叶子只存索引值 + 主键，查到后回表找整行。二级索引只存索引列 + 主键，查到主键后，再去聚簇索引查完整行数据，这个过程叫回表。
• 覆盖索引:查询的所有字段都包含在索引中，无需回表，直接从二级索引拿数据，性能最优。
  主键非空且唯一，一张表只有一个，是聚簇索引；
  唯一索引唯一可空，可以多个，是二级索引。

欸?那这个时候就有问题了,覆盖索引也包含了全部字段,聚簇也包含了全部字段??那他俩是不是一样?

实则不然:

聚簇索引会包含隐藏列

这里多嘴一句:

undo log(回滚日志):
不同的操作格式不同
同操作产生的Undo Log格式不同：

事务通过操作修改记录,mysql生成相应的undo log,并以链表串联起来,形成版本链
DB_ROLL_PTR，一条记录的多个历史版本被串联成单向链表

索引失效原则:

1. 模糊查询%xx、%xx%前缀通配符；
2. 索引列做运算、函数、类型转换；
3. or两边字段不同时走索引；
4. 复合索引违背最左前缀；
5. 数据量太少、重复值太多（选择性差），优化器放弃索引走全表；
6. 隐式类型转换（字符串不加单引号）；

7. not in、not exists、is not null容易失效

最左前缀是什么?

复合索引(a,b,c)能触发：

• where a
• where a,b
• where a,b,c 不能触发：
• 跳过 a 直接用 b、c
• 中间断字段 如 where a and c
• 复合索引遵循从左到右匹配，不能跳过首列、不能中间断列，否则索引失效。
  帮助最快匹配

为什么不符合最左前缀失效?

B+ 树中的排序规则是：先按第一列排序，第一列相同的情况下再按第二列排序，以此类推。因此，如果你跳过了第一列，后面的列在树中根本不是全局有序的，无法通过索引快速检索。

一个奇怪的问题:

• 所以不符合最左匹配就一定只会用主树吗?

举个例子:

现在创建了一个表:

int a,int b,int c primary key

索引 int (a,b)

select * from table where b...

显而易见,这不符合最左前缀原则,但是!他走了索引,怎么会这样??

因为索引树已经包含了所有的数据,就是覆盖索引,他不会使用索引快速定位,而是遍历联合索引树,(优化器觉得索引树要比主树小很多,而且不用回表),这样很方便.

• 追问:那加了一个数据为啥就不走了索引了?

因为加了就需要回表了,优化器觉得不值得了.

事务

可以理解为一些不可分割的集合,要么全执行,要么失败全回滚(比如银行取钱)

四大特性:

ACID

• 原子性(A):事务是一个"原子"操作单元,要么全执行,要么在某个操作步骤失败后,所有已经执行的操作全部被撤销(回滚),数据回到事务刚开始的状态
  :扣款和收款同时成功才能执行完毕,否则操作回滚
• 一致性(C):事务执行前后,数据库从一个"一致状体切换为另一个一致状态",即满足数据预设的约束(比如主键不可以重复)
  
• 隔离性(I):多个事务同时执行时,彼此的操作互不干扰,每个事务都感受不到其他事务的存在,数据库通过调整隔离级别来控制并发事务的交互程度,(避免脏读,幻读,不可重复读)
• 持久性(D):事务一旦提交,其对数据库的修改将永久保存到数据库中,即使发生系统崩溃数据也不会丢失

实现:

隔离:mvcc多版本并发控制/锁(默认使用行级锁)
原子:回滚日志(undo log0):InnoDB会在事务里修改数据之前，把旧数据写入Undo Log。
持久:重做日志(Redo Log):物理日志事务提交时，不直接刷写数据文件到磁盘（太慢），而是先把这次修改的物理操作（例如“在页XXX的偏移量YYY处写入值ZZZ”）写入Redo Log，并强制刷盘。
一致:前三个+应用层的约束

进入事务

//linux: begin or start transaction 函数:if(mysql_autocommit(mysql,0)!=0)//0关闭，1开启 SQL语句 mysql_commit(mysql)!=0)//提交事物失败c commit linux mysql_rollback(mysql)!=0)//回滚事物失败c rollback linux mysql_autocommit(mysql,1);//恢复自动提交

set auto commit=0//关闭自动提交
set auto commit =1 //开启自动提交

注意事项

• 不是所有数据库引擎都支持,(mysql里的 mylsam不支持)
• 事物开启后需要尽快提交或者回滚,避免长时间占用资源导致性能下降
• 隔离级别越高,数据一致性越好,但并发效率越低

并发事务三大问题

1. 脏读

   读到其他事务未提交的修改数据。

2. 不可重复读

   同一事务内，两次读同一条数据，结果不一样（被别人改了并提交）。

• 事务 T1 开启事务，第一次查：工资 5000
• 事务 T2 修改该条记录工资为 8000，提交
• 事务 T1 还没结束，再次查同一条记录，变成 8000
• 同一事务、同一条数据，前后读数不一样 →不可重复读

1. 幻读

   同一事务范围查询，别人插入 / 删除新行，再查多出或少了行。

• 事务 T1：开启事务，查询id 1~10的用户，查出 3 条
• 事务 T2：插入一条 id=7 的新数据，立刻提交
• 事务 T1 再次查询id 1~10，突然多出来一条
• 范围统计行数变了，凭空多出记录 →幻读

问题的解决:隔离性(低到高)

定义了多个并发性事物之间的交互规则,用于平衡一致性和并发性.,在数据库中定义了四个等级,针对不同程度的并发性问题(脏读,不可重复读,幻读)解决程度不同:
(从低到高)(隔离性越高并发性越低)

• 读,未提交(read uncommitted):
  一个事务可以读取另一个事务未提交的修改数据
  问题:存在脏读,(eg:对面改了未提交万一回滚了),几乎不使用 只适用于对数据一致性非常低的场景
• 读,已提交(read committed):
  一个事务可以读取另一个事务已提交的修改数据
  问题:避免脏读,存在"不可重复读,幻读"的问题(eg:通一次事务内两次连续读到不同数据,因中间被其他事务修改并提交)
  大多数数据库的默认隔离级别(sql)默认
• 可重复读(repeatable read):
  同一事务多次读取同一数据,结果始终一致(即使其他事务修改并提交也看不到变化)
  问题: 解决了脏读,不可重复读,存在幻读(eg:同一事务内两次执行范围查询结果行数不同,因为中间被别的事务插入/删除数据,看不到数据但是可以看到行数)
  mysql中innodb的默认级别
• 串行性(seriallzable):
  完全没有并发性,事务一个一个来(单线程)
  问题:完全解决,并发性无 适用于对数据一致性要求非常高的并发量极低的场景(金融)
  并发极低、阻塞多、容易死锁、性能最差

InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

-- 1. 读未提交 set session transaction isolation level read uncommitted; -- 2. 读已提交 set session transaction isolation level read committed; -- 3. 可重复读（InnoDB 默认） set session transaction isolation level repeatable read; -- 4. 串行化 set session transaction isolation level serializable; //查询当前隔离级别 - SELECT @@transaction_isolation;(>8.0) - SELEC@@tx_isolation(<5.7) - SELECT @@global.transaction_isolation(>8.0) - SELEC@@global.tx_isolation(<5.7) //普通连接 mysql_query(&conn, "set session transaction isolation level read committed;"); //预处理连接 MYSQL_STMT* stmt = mysql_stmt_init(&conn); char *sql = "set session transaction isolation level repeatable read;"; mysql_stmt_prepare(stmt, sql, strlen(sql)); mysql_stmt_execute(stmt);

• session：只对当前连接生效
• global：对所有新连接生效

四个级别具体实现

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了
• 串行化:直接加读写锁
• 读提交/可重复 ,通过read view的触发机制不同
  「读提交」隔离级别是在每个读取数据前都生成一个 Read View(也就是说,在一个事务期间,多次读取一个数据,前后两次的值可能不一样,因为这个期间可能有一个事务修改并提交)，而「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

举例:

• 可重复读
  事务 A 和 事务 B 差不多同一时刻启动，那这两个事务创建的 Read View

ReadView { m_ids [51,52]; min_trx_id 51; max_trx_id 53 ; creator_trx_id 51;// b 52

1.a去读一条信息:
a在undo_log里找到这个值的时候,会看到txd_id,这时,这个id是50,不在m_ids里,且比 creator_trx_id 51小,说明这是开启事务前已经提交的,所以可见
2.b修改了记录
这时mysql生成相应的undo log,在undo_log里可以看见52的值要比51还要大,而且比53要小,说明这时和a一起开始事务的事务修改记录,所以a不会读,而是接着往下找到找到 trx_id 等于或者小于事务 A 的事务 id 的第一条记录

• 读提交:

现在有两个事务依旧如上
a,b几乎同时开启,获得id:[51,52],这时产生的read view:
1.b将某个值改了,未提交,a看到的还是旧值:
当b提交事务了以后,a在查找这条记录,发现trx_id在 m_ids 列表里，说明该记录的 trx_id 的事务是未提交，于是事务 A ，不读取这条,而是往下找
2.当b提交了,a就可以读到新数据
a发现trx_id比事务 A 的 Read View 中的 creator_trx_id 要大，而且不在 m_ids 列表里，说明该记录的 trx_id 的事务是已经提交过的了，于是事务 A 就可以读取这条记录

read view

Read View 就是一个快照，它定义了事务在执行SELECT时，能看到哪些已提交的数据，看不到哪些未提交的数据。

他里边有四个字段名:

ReadView { m_ids // 当前活跃（未提交）事务ID 列表 min_trx_id // 最小活跃事务ID max_trx_id // 最大事务ID+1（未来事务起点） creator_trx_id // 创建这个 ReadView 的事务ID }

配合行记录里的隐藏字段：

• DB_TRX_ID：最后修改这行的事务 ID
• DB_ROLL_PTR：指向 undo log 里的旧版本（形成版本链）