MySql索引，事务等介绍

MySql系列文章第二弹，上文介绍了一些基础知识，这篇不出所料还是基础，主打科普，索引，事务，视图...

索引

• 几乎所有的索引都是建立在字段上

• 系统根据某种算法 将已有的数据或即将添加的数据，单独建立一个文件，文件能够实现快速的匹配数据，并且能够快速找到表中的数据

• 增加索引的前提条件 ：

  • 索引本身会产生索引文件（ 有时候可能会比数据文件还大），会非常耗费磁盘空间

• Mysql中的索引

  • 主键索引 ： primary key
  • 唯一索引 ： unique key
  • 全文索引 ： fulltext index
  • 普通索引 ： index

• 添加索引

  • 添加PRIMARY KEY(主键索引)
    • ALTER TABLE table_name ADD PRIMARY KEY(column)
  • 添加UNIQUE(唯一索引)
    • `ALTER TABLE table_name ADD UNIQUE (column)
  • 添加INDEX(普通索引)
    • `ALTER TABLE table name ADD INDEX index name (column)
  • 添加FULLTEXT(全文索引)
    • ALTER TABLEtable_name’ ADD FULLTEXT ( column)
  • 添加多列索引
    • ALTER TABLE table name ADD INDEX index name ( column1,column2, column3)

视图

• 创建

  • create view 视图名 as select 语句

• 修改

  • alter view 视图名 as select 语句

• 删除

  • drop view 视图名

• 视图的数据操作

  • 新增
    • 数据的新增就是直接对视图进行数据的新增
    • 多表视图不能新增数据
    • 单表视图可以插入数据，但是视图中包含的字段 必须有基表中
    • 所有不能为空或没有默认值的字段
  • 删除
    • 多标视图不能删除数据
    • 单表视图可以删除
  • 更新
    • 理论上无论多表还是单表 都是可以更新的
    • 更新限制：with check option
    • 如果视图在新增的时候，限定了某个字段有限制，那么在进行update的时候，系统会验证：在保证更新之后，数据依然可以被视图查询出来，否则不让更新
    • 例 ：create view aa as (select * from 表名 where a>10) with check option 后面的with check option 是对字段a的限制 如果update视图的aa的时候 就不能将字段a修改到小于10 如果不加 with check option 则没有这个限制能修改的只能是视图可以查询到的数据，这里拓展一下with check option的用法
    • 对于with check option用法，总结如下：
      • 通过有with check option选项的视图操作基表(只是面对单表)，有以下结论： 插入后的数据，通过视图能够查询出来就符合WITH CHECK OPTION 否则就不符合；
      • 首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作
      • 对于update,有with check option，要保证update后，数据要被视图查询出来
      • 对于delete,有没有with check option都一样
      • 对于insert,有with check option，要保证insert后，数据要被视图查询出来
      • 对于没有where 子句的视图，使用with check option是多余的

• 视图算法

  • 系统对视图 以及外部查询视图的select语句的一种解析方式
  • 视图算法分为3种
    • undefined:(未定义)，这不是一种实际用的算法，是一种推卸责任的算法—-告诉系统，视图没有定义算法，你看着办。
    • temptable:(临时表算法)：系统先执行视图select语句，再执行外部查询语句
    • merge:(合并算法)：系统先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行（效率高：）系统默认值
    • 指定算法：在创建视图的时候： create algorithm=算法名 view as ...

数据库事务

• 事务（transaction）：一系列要发生的连续的操作

• 事务安全:一种保护连续操作同时满足（实现）的一种机制

• 事务安全的意义：保证数据的完整性

• 事务操作分两种：自动事务（默认），手动事务

• 手动事务：操作流程：

  • 开启事务：告诉系统以下所有操作（写）不要直接写到数据表，先存放到事务日志。 start transaction
  • 进行事务操作：一系列操作
  • 关闭事务 ： 选择性的将日志文件保存到数据表
    • 提交事务：commit
    • 回滚事务：roolback

• 事务原理： 事务开启之后，所有的操作都会临时保存到事务日志，事务日志只有在得到commit 命令时才会同步到数据表，其他任何情况都会清空

• 回滚点：

  • 在某个成功的操作完成之后，后续的操作有可能成功，有可能失败，但是不管是成功还是失败，前面的操作都已成功，可以在当前成功的地方设置一个回滚点，可以供后续操作失败会到这个位置，而不是返回所有操作。这个点就是回滚点
  • 设置回滚点语法： savepoint 回滚点名字
  • 回到回滚点语法： rollback to回滚点名字
  • 关闭自动事务提交： set autocommit = 0 关了之后的写操作都要commit 手动提交

• 事务特性：四大特性（ACID）

  • atomic 原子性，事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败。
  • consistency 一致性，事务操作的前后，数据表中的数据没有变化
  • isolation 隔离性 ， 事务操作是相互隔离 不受影响的
  • durability 持久性，数据一旦提交 不可改变

• 锁机制：innodb默认是行锁，但是如果在事务操作过程中，没有使用索引，默认会进行全表检索，自动升级为表锁。

• 并发事务处理带来的问题：

• 更新丢失（Lost Update）

  • 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
  • 例如，两个程序员修改同一iava文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
  • 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

• 脏读(Dirty Reads)

  • 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态:这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
  • 一句话:事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

• 不可重复读（No-Repeatable Reads）

  • 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”
  • 一句话: 事务A读取到了事务B已经提交的修改数据，不符合隔离性

• 幻读（Phantom Reads）

  • 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读
  • 一句话: 事务A读取到了事务B体提交的新增数据，不符合隔离性
  • 幻读和脏读有点类似，
  • 脏读是事务B里面修改了数据
  • 幻读是事务B里面新增了数据

• 脏读”“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决

| 读数据一致性及允许的并发副作用

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读( Read uncommitted )	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交度( Read committed )	语句级	否	是	是
可重复读( Repeatable read)	事务级	否	否	是
可序列化 ( Serializable )	最高级别，事务级	否	否	否

• 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
• 查看当前数据库的事务隔离级别: show variables like 'tx_isolation'
• 在mysql5.7中实际测试结果（默认REPEATABLE-READ的隔离级别）：首先打开2个连接，依次开启事务A，B。：
  • 在A中update一行数据，在B中查询还是修改之前的数据，说明避免了脏读。
  • 在A中update一行数据，并commit，在B中查询还是修改之前的数据，说明避免了不可重复读。
  • 在A中inset一行数据，并commit,在B中查询不到（如果B在A commit前select过，那么就查不到，如果不select就会查到），！！呀这不是避免了幻读嘛，事实并不是这个样子。存在一个undo log，可以将他抽象的理解成一种缓存机制（快照），就幻读这个操作来说：有其他事务提交了插入数据的操作，且更新时的条件覆盖了插入的数据，则会导致幻读。