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 唯一,也是表本身二级索引 (辅助索引)叶子只存索引值 主键查到后回表找整行。二级索引只存索引列 主键查到主键后再去聚簇索引查完整行数据这个过程叫回表。覆盖索引:查询的所有字段都包含在索引中无需回表直接从二级索引拿数据性能最优。主键非空且唯一一张表只有一个是聚簇索引唯一索引唯一可空可以多个是二级索引。欸?那这个时候就有问题了,覆盖索引也包含了全部字段,聚簇也包含了全部字段??那他俩是不是一样?实则不然:聚簇索引会包含隐藏列藏列字节数作用DB_TRX_ID6字节最后修改此行的事务ID用于MVCCDB_ROLL_PTR7字节回滚指针指向undo log中的旧版本数据DB_ROW_ID6字节没有主键的话,聚簇索引自动生成,作为聚簇索引键(隐式创建)这里多嘴一句:undo log(回滚日志):不同的操作格式不同同操作产生的Undo Log格式不同操作数据页中的DB_ROLL_PTRUndo Log类型Undo Log内容INSERTNULLInsert Undo Log主键值UPDATE指向新的Undo LogUpdate Undo Log被修改列的旧值 旧DB_TRX_ID(事务id) 旧DB_ROLL_PTRDELETE指向新的Undo Log也是 Update Undo Log整行旧数据 旧DB_TRX_ID 旧DB_ROLL_PTR事务通过操作修改记录,mysql生成相应的undo log,并以链表串联起来,形成版本链DB_ROLL_PTR一条记录的多个历史版本被串联成单向链表索引失效原则:模糊查询%xx、%xx%前缀通配符索引列做运算、函数、类型转换or两边字段不同时走索引复合索引违背最左前缀数据量太少、重复值太多选择性差优化器放弃索引走全表隐式类型转换字符串不加单引号not in、not exists、is not null容易失效最左前缀是什么?复合索引(a,b,c)能触发where awhere a,bwhere 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 commit0//关闭自动提交set auto commit 1 //开启自动提交注意事项不是所有数据库引擎都支持,(mysql里的 mylsam不支持)事物开启后需要尽快提交或者回滚,避免长时间占用资源导致性能下降隔离级别越高,数据一致性越好,但并发效率越低并发事务三大问题脏读读到其他事务未提交的修改数据。不可重复读同一事务内两次读同一条数据结果不一样被别人改了并提交。事务 T1 开启事务第一次查工资 5000事务 T2 修改该条记录工资为 8000提交事务 T1 还没结束再次查同一条记录变成 8000同一事务、同一条数据前后读数不一样 →不可重复读幻读同一事务范围查询别人插入 / 删除新行再查多出或少了行。事务 T1开启事务查询id 1~10的用户查出 3 条事务 T2插入一条 id7 的新数据立刻提交事务 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) - SELECtx_isolation(5.7) - SELECT global.transaction_isolation(8.0) - SELECglobal.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 ViewReadView { m_ids [51,52]; min_trx_id 51; max_trx_id 53 ; creator_trx_id 51;// b 521.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 viewRead View 就是一个快照它定义了事务在执行SELECT时能看到哪些已提交的数据看不到哪些未提交的数据。他里边有四个字段名:ReadView { m_ids // 当前活跃未提交事务ID 列表 min_trx_id // 最小活跃事务ID max_trx_id // 最大事务ID1未来事务起点 creator_trx_id // 创建这个 ReadView 的事务ID }配合行记录里的隐藏字段DB_TRX_ID最后修改这行的事务 IDDB_ROLL_PTR指向 undo log 里的旧版本形成版本链