数据库系统的常识

MySQL

事务处理

• 事务来自于2个独立的需求: 并发数据库访问 和 系统错误恢复.
• 一个事务可以被看作是 一个单元的一系列SQL语句的集合.

事务特性 (A.C.I.D)

• A - 原子性 (Atomacity): 事务必须是原子工作单元,对于其数据修改,要么全都执行,要么全都不执行.
• C - 一致性 (Consistency): 事务将数据库从一种一致状态转变为下一种一致状态.
• I - 隔离性 (Isolation): 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离.
• D - 持久性 (Durability): 事务完成之后,它对于系统的影响是永久性的.即使出现致命的系统故障也将一直保持.

事务的隔离级别

• 不对数据库进行并发控制,会产生异常情况:
  
  1. 脏读 (Dirty Read): 一个事务读取另一个事务尚未提交的修改产生脏读,同一事务内不是脏读. - 由于读取过程中,另一事务更新了数据没有及时提交造成的(数据可能被回滚).
  2. 非重复读 (Nonrepeatable Read): 一个事务对同一行数据重复读取两次得到不同的结果. - 由于查询过程中,其他提交事务修改或删除数据造成的.
  3. 幻像读 (Phantom Read): 事务在操作过程中进行两次查询,第二次查询结果包含第一次查询中未出现的数据(不要求两次查询的SQL语句相同). - 由于两次查询中,另一事务插入数据造成的.
  4. 丢失修改 (Lost Update): a. 两个事物更新相同的数据源,第一个被提交,第二个被撤销,那么第一个做的更新也会被撤销. b. 两个并发事务同时读取同一行数据,第一个进行修改提交,第二个也进行修改提交,造成第一次写操作失效.
• 为了兼顾并发效率和异常控制,标准SQL规范里定义了4个事务隔离级别:
  
  1. 未提交读 (Read Uncommitted): 更新语句没有提交,别的事务可以读到改变数据. - 允许 脏读.
  2. 已提交读 (Read Committed): 只能读取到已提交的数据. - 不允许 脏读, 允许 非重复读.(多数数据库默认该级别).
  3. 可重复读 (Repeatable Read): 同一事务先后执行同一查询语句得到一样的结果. - 不允许 脏读,非重复读, 允许 幻像读.
  4. 串行读 (Serializable): 串行化的读,当前事务执行时不允许别的事务并发进行.每次读需要获得 表级共享锁,读写都会阻塞. - 不允许 不一致现象 出现.
• 各隔离级别对各种异常的控制能力:

事务隔离的实现 - 锁

• 共享锁(S锁): 用于只读操作(SELECT),锁定共享的资源. - 不阻止其他用户 读, 只阻止其他用户 写.
• 更新锁(U锁): 用于可更新的资源.防止多个会话读取,锁定,资源更新时发生死锁.
• 独立锁(X锁,排他锁): 一次只有一个独占锁用在一个资源上,阻止其他锁.写是独占锁,可有效的防止 脏读.
• Read Uncommited: 一个事务在写数据, 另外一事务则 不允许 同时进行 写操作, 但允许其他事务 读数据. 该隔离级别可以通过 “排他写锁” 实现.
• Read Committed 读取数据的事务允许其他事务继续访问该数据; 但 未提交的写事务 会禁止 其他事务访问该数据. 可以通过 “瞬间共享读锁” 和 “排他写锁” 实现.
• Repeatable Read 读取数据的事务 禁止写事务, 但 允许读事务, 写事务则禁止任何其他事务. 可以通过 “共享读锁” 和 “排他写锁” 实现.
• Serializable 读数据 加 共享锁, 写数据 加 排他锁, 读写互斥.
• 一般处理并发问题时的步骤:
  
  1. 开启事务.
  2. 申请写权限,也就是给对象(表或记录)加锁.
  3. 假如失败,则结束事务,过一会重试.
  4. 假如成功,也就是给对象加锁成功,防止其他用户再用同样的方式打开.
  5. 进行编辑操作.
  6. 写入所进行的编辑结果.
  7. 假如写入成功，则提交事务,完成操作.
  8. 假如写入失败，则回滚事务，取消提交.
  9. (7.8)两步操作已释放了锁定的对象,恢复到操作前的状态.

索引

• 数据库创建索引的优点:

  提高系统的性能

  1. 创建唯一性的索引,保证数据库表中每一行数据的唯一性.
  2. 加速数据的检索速度 – 最主要的原因.
  3. 加速表与表之间的连接,特别在实现数据的参考完整性方面特别有意义.
  4. 使用分组和排序子句进行数据检索时,可以显著的减少查询中分组和排序的时间.
  5. 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能.

• 数据创建索引的缺点:

  1. 创建索引和维护索引需要消耗时间,且随着数据增加而增加.
  2. 索引需要占物理空间,除了数据表占据数据空间之外,每个索引还要占一定的物理空间.
  3. 表的数据增加,删除,修改时,索引要动态维护,降低了数据维护的速度.

• 什么样的列建立索引:

  1. 在主键的列上,强制该列的唯一性和组织表中数据的排列结构.
  2. 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度.
  3. 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的.
  4. 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间.
  5. 在经常使用在where子句中的列上面创建索引,加快条件的判断速度.

• 什么样的列不创建索引:

  1. 在查询中很少使用或者作为参考的列不应该创建索引.
  2. 对于那些只有很少数据值的列也不应该增加索引(比如性别,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大.增加索引,并不能明显加快检索速度).
  3. 对于那些定义为text,image和bit数据类型的列不应该增加索引.因为这些列的数据量要么相当大,要么取值很少.
  4. 当修改性能远远大于检索性能时,不应该创建索引,因为修改性能和检索性能是矛盾的.

• 创建索引的方法: 直接创建和间接创建.

• 索引特征:

  1. 唯一性索引. – 保证在索引列中的全部数据是唯一的,不包含冗余数据.
  2. 复合索引. – 一个索引创建在多列上,可以减少表中索引的数量.