Database

This is my personal notes for Database.

数据库范式

• 第一范式: 列不可分

  第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

  举例: 联系人表(姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；

• 第二范式: 确保表中的每列都完全依赖主键

  第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

  举例: 订单表(商品名称, 商品单价, 商品数量, 下单时间), 商品数量, 下单时间完全依赖订单ID，但是商品单价, 商品名称缺依赖的是商品ID, 所以要拆分成订单表和商品表。

• 第三范式: 确保每列都和主键列直接依赖,而不是间接依赖

  第三范式需要确保数据表中的每一列数据都和主键直接依赖，而不能间接依赖。

  举例: 订单表(商品数量, 下单时间, 客户ID, 客户姓名, 客户城市), 客户姓名和客户城市依赖客户ID, 间接依赖订单ID，要将客户信息单独拆分成客户信息表。

数据库索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

• 1 索引的底层实现原理和优化

  　　在数据结构中，我们最为常见的搜索结构就是BST和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论BST还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树 结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。

  • B-Tree:

    B-Tree将所有的值通过有序的多结点树结构排列, B-Tree的阶代表节点最多的孩子节点个数, 2-3树是3阶B-Tree, 注意其节点是有序排列的值, 所以搜索不一定会到叶子节点, 可能在上层就找到了target

  • B+Tree (InnoDB存储引擎的索引实现)

    B+Tree和B-Tree的区别在于B+Tree只在叶子节点顺序记录真正的值, 而上层节点都只记录其孩子节点的最大值(最小值), 相当于上层都是索引, 所以每次搜索都会搜索到叶子节点找到target
    

• 2 为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

  数据库索引采用B+树而不是B树的主要原因：
  B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低

  B+tree的磁盘读写代价更低(IO更少): B+tree的内部结点并没有指向关键字具体信息的指针，没有指向磁盘中具体的地址, 因此其内部结点相对B-Tree更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；

  B+tree的查询效率更加稳定: 由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

  为什么用B+ Tree不用Hash:

  • (1). Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能实现范围查找。
  • (2). 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样, 当需要按照索引进行order by时，hash值没办法支持排序。
  • (3). Hash 索引遇到大量Hash值相等的情况(hash冲突)后性能并不一定就会比B-Tree索引高。
  • (4). Hash 索引不能利用部分索引键查询。
    对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

• 3 索引的优缺点

  优点:

  • (1) 大大加快数据的检索速度，这也是创建索引的最主要的原因；

  • (2) 加速表和表之间的连接；

  • (3) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

  • (4) 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

  缺点:

  • (1) 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度, 比如插入数据的速度会变慢

  • (2) 空间方面：索引需要占物理空间。(大的索引内存存不下还要放到磁盘里)

• 4 什么情况下设置了索引但无法使用？

  (1) 以“%”开头的LIKE语句，模糊匹配；

  (2) OR语句前后没有同时使用索引；

  (3) 字符串没有加引号 eg: num = 123（如varchar不加单引号的话会自动转换为int型)

  (4) 对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，会创建3个索引col1, (col1，col2), (col1，col2，col3)。 最左匹配会一直匹配到第一个!=, <>的地方。

  如: where col1>1 and col2>2, 会使用索引col1, where col1>1 and col3>3 会使用索引col1, where col1=1 and col2=2 and col3>3 会使用索引(col1,col2,col3)。

  可以交换query字段的前后顺序, MySQL会自动优化, 判断以什么样的顺序执行效率最高。

• 5 什么样的字段适合创建索引？

  经常作查询选择的字段 (select)

  经常作表连接的字段 (join)

  经常出现在where, order by, group by 后面的字段

• 6 创建索引时需要注意什么？

  表的主键、外键必须有索引；
  数据量超过300的表应该有索引；
  经常与其他表进行连接的表，在连接字段上应该建立索引；
  经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
  索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
  联合索引的建立需要进行仔细分析, 尽量考虑用单字段索引代替 非空字段：应该指定建立索引的列为NOT NULL

• 7 索引的分类

  (1) 普通索引和唯一性索引：索引列的值的唯一性

  (2) 单个索引和联合索引：索引列所包含的列数

  (3) 聚簇索引与非聚簇索引：
  索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，辅助索引的叶子节点的 data 域记录着主键的值。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

  InnoDB使用的就是聚簇索引
  

• 8 主键、自增主键、主键索引与唯一索引概念区别

  主键：指字段 唯一、不为空值 的列；

  主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

  自增主键：字段类型为数字、自增、并且是主键；

  唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

• 9 为什么重复率高的字段不适合建索引

  一个表可能会涉及两个数据结构(文件)，一个是表本身，存放表中的数据，另一个是索引。访问索引需要付出额外的IO开销，从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

数据库事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

• (1) 事务的特征

  • 原子性(Atomicity)：

    事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

    原子性通过回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

  • 一致性(Consistency)：

    数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

  • 隔离性(Isolation)：

    一个事务所做的修改在最终提交以前，对其它事务是不可见的。

    隔离性只在Repeatable Read和Serializable中满足, 通过mvcc(多版本并发控制)实现，在并发访问的时候，数据存在版本的概念，MVCC可以通过提供历史版本从而实现读取被锁的数据的历史版本，即实现隔离性, 避免了互斥等待。在需要时，通过undo日志构造出历史版本。

  • 持久性(Durability)：

    一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

    持久性通过重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

• (2) 事务并发带来的问题

  1、更新丢失：一个事务的更新覆盖了另一个事务的更新。事务A：更新money=100。事务B：更新money=200。A,B同时执行，后提交的事务B覆盖了事务A的更新。更新丢失本质上是写操作的冲突，解决办法是一个一个写。

  2、脏读：一个事务读取了另一个事务未提交的数据。事务A：更新money+=100。事务B：查询money。事务A发生后（还未提交），事务B查询money多了100元。事务A由于某种问题，比如超时，进行回滚。事务B查询到的数据是假数据。脏读本质上是读写操作的冲突，解决办法是写完再读。

  3、不可重复读：一个事务两次读取同一个数据，两次读取的数据不一致。事务A：money+=100。事务B：两次查询money。事务B第一次查询money，事务A还没有发生，第二次查询money，事务A已经发生了，导致一个事务中，两次读取同一个数据，读取的数据不一致。不可重复读本质上是读写操作的冲突，解决办法是读完再写。(不可重复读重点在于update和delete, 数据不一致)

  4、幻象读：一个事务两次读取一个范围的记录，两次读取的记录数不一致。事务A：查询id=1的记录。事务B：插入id=1的记录。事务A第一次查询的时候，id=1不存在，准备insert id=1的记录(insert也要先读), 而此时事务B已经执行, 导致主键冲突insert失败。幻象读本质上是读写操作的冲突, 解决办法是读完再写。(幻读的重点在于insert, 有和无)

  

• (3) 隔离级别

  隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：

  Read Uncommitted：
  最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；

  Read Committed：
  在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；

  Repeatable Read：
  在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。

  此级别只能防止部分幻读, 即select快照读不会出现幻读, 但是update,insert,delete之类的当前读会出现幻读。因为当前读会读取最新记录，而快照读不会。

  Serializable：
  最高级别的隔离，只允许事务串行执行。

  MySQL默认的隔离级别是Repeatable Read。

• (4) mysql的事务支持

  MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

  MyISAM：不支持事务，用于只读程序提高性能；
  InnoDB：支持ACID事务、行级锁、并发；
  Berkeley DB：支持事务。

MySQL的6种日志

• 1、事务日志

  作用：事务日志主要用来保障RDBMS的ACID，磁盘随机IO转换为顺序IO，尽可能降低宕机造成的内存中的数据丢失

  场景：试想一种情景，一个事务在执行过程中，数据库主机宕机，事务中更改的数据在内存中已修改，但并未同步到持久性存储上

  详细解释：正常情形下，事务执行过程数据的修改在事务提交后会同步到磁盘上，但同步到磁盘上由于更改数据不可控，因此很可能IO产生在多个不连续的数据块上，这种随机IO性能很慢，因此MySQL为了保证数据库的ACID特性，就引入了事务日志，事务日志在初始化数据时会直接生成两个文件ib_logfile0、ib_logfile1,这两个文件在生成时大小就为5M(大小可设定)，其内部从磁盘上划分了5M连续的磁盘空间，用于保存事务日志，事务在执行过程中，数据的更改在未提交前都会先写到事务日志中，由于事务日志是连续的磁盘空间，因此IO时是顺序的，性能比较高，可以保证数据及时写入事务日志；事务日志包括redo和undo两种，数据库恢复重启时会重做这两种日志，从而保证了数据的一致性。

  工作模式：基于InnoDB存储引擎的MySQL之所以可以从崩溃中恢复，正是依赖于事务日志，当数据库实例宕机后，重启时MySQL会自行检查事务日志，然后依次处理；

  事务日志分为redo log和undo log两种；

  (1)、对于事务日志中未正常提交的事务，则会记录到undo log中，因为事务未正确执行完，因此必须回滚，从而保证数据一致性

  (2)、对于事务日志中已正常提交但未同步到持久化存储上时，则会记录到redo log中，因此MySQL会重新执行一遍事务，然后让数据存储到磁盘上，从而保证数据一致性

• 2、二进制日志

  MySQL中的二进制日志(binary log)是一个二进制文件，主要用于记录可能引起数据库内容更改的SQL语句或数据行记录，例如新增(Insert)、更新(Update)、删除(Delete)、授权信息变更(Grant Change)等，除记录这些外，还会记录变更语句的发生时间、执行时长、操作数据等额外信息，但是它不会记录诸如Select、Show等这些不会引起数据修改的SQL语句。

  作用：主要用于主从复制架构，日志审计

  涉及常用命令：

  show master logs 查看二进制日志有哪些

  show master status 查看当前使用的日志文件

  show binlog events in ‘mysql-bin.000004’ 查看指定二进制日志的事件详细信息

• 3、查询日志

  开启查询日志功能，会记录所有的查询操作，这在繁忙的数据库应用中会增加额外的IO开销，磁盘空间增长也会很快，不建议开启；通常用于DBA调试优化时临时开启

• 4、慢查询日志

  查询日志，所有的查询都会记录；但慢查询日志会记录执行比较慢的查询语句，究竟执行多久定义为慢，这个可以通过变量long_query_time来设定，慢查询通常会开启，便于DBA进行分析并进行性能优化

• 5、中继日志

  中继日志主要用于在主从复制架构中，在 slave节点上开启该日志类型，用于从master同步二进制日志数据；

• 6、错误日志

  记录MySQL守护进程启动和关闭过程中产生的错误信息，运行中产生的错误信息

实践中如何优化MySQL

实践中，MySQL的优化主要涉及四个方面的优化: SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化，如下图所示：

• 1 SQL语句的优化

  • (a) 通过慢查询日志发现有问题的SQL
    MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。慢查询日志的相关参数如下所示：

    

    通过MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的SQL、可以通过pt_query_disgest(一种mysql慢日志分析工具)分析Rows examine(MySQL执行器需要检查的行数)项去找出IO大的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象。

  • (b) 通过explain查询和分析SQL的执行计划

      explain select * from table_user
    

    explain命令输出的结果有10列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

    重点字段type: 该列称为关联类型或者访问类型，它指明了MySQL决定如何查找表中符合条件的行，同时是我们判断查询是否高效的重要依据。

    • ALL：全表扫描，这个类型是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型，因为这样的查询，在数据量最大的情况下，对数据库的性能是巨大的灾难。
    • index：全索引扫描，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型是扫描全部的索引，主要优点是避免了排序，但是开销仍然非常大。如果在 Extra 列看到 Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要少很多。
    • range：范围扫描，就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这个类型通常出现在 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 的操作中，key 列显示使用了哪个索引，当 type 为该值时，则输出的 ref 列为 NULL，并且 key_len 列是此次查询中使用到的索引最长的那个。
    • ref：一种索引访问，也称索引查找，它返回所有匹配某个单个值的行。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。
    • eq_ref：使用这种索引查找，最多只返回一条符合条件的记录。在使用唯一性索引或主键查找时会出现该值，非常高效。
    • const、system：该表至多有一个匹配行，在查询开始时读取，或者该表是系统表，只有一行匹配。其中 const 用于在和 primary key 或 unique 索引中有固定值比较的情形。
    • NULL：在执行阶段不需要访问表。

  • (c) 语句的优化
    优化insert语句：一次插入多值；

    应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；

    应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；

    优化嵌套查询：子查询可以被更有效率的连接(Join)替代；

    很多时候用 exists 代替 in 是一个好的选择。

• 2 索引优化
  可以为经常出现在 join, where, order by, group by 的字段添加索引, 需要注意索引失效的情况

• 3 数据库表结构的优化
  数据库表结构的优化包括选择合适的数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

  • (a) 选择合适的数据类型

    使用较小的数据类型解决问题；

    使用简单的数据类型(mysql处理int要比varchar容易)；

    尽可能的使用not null 定义字段；

    尽量避免使用text类型，非用不可时最好考虑分表；

  • (b) 表的范式的优化

    一般情况下，表的设计应该遵循三大范式。

  • (c) 表的垂直拆分

    把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下几种拆分手段：

    把不常用的字段单独放在同一个表中；

    把大字段独立放入一个表中；

    把经常使用的字段放在一起；

    这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。

  • (d) 表的水平拆分

    表的水平拆分用于解决数据表中数据过大的问题，水平拆分每一个表的结构都是完全一致的。一般地，将数据平分到N张表中的常用方法包括以下两种：

    对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值； 针对不同的hashID将数据存入不同的表中；

    表的水平拆分会带来一些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了一些切实的好处：

    表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度；

    表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。

    需要把数据存放到多个数据库中，提高系统的总体可用性(分库，鸡蛋不能放在同一个篮子里)。

• 4 系统配置的优化

  • (a) CPU：核心数多并且主频高的
  • (b) 内存：增大内存
  • (c) 磁盘配置和选择：磁盘性能

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。

进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程具有以下特点：

• (a) 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；

• (b) 存储过程可以重复使用，大大减小开发人员的负担；

• (c) 对于网络上的服务器，可以大大减小网络流量，因为只需要传递存储过程的名称即可；

• (d) 可以防止对表的直接访问，只需要赋予用户存储过程的访问权限, 从而确保数据的安全;

drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

什么叫视图？游标是什么？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作。特别地，对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

在操作mysql的时候，MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行（零行或多行）。使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL中的悲观锁与乐观锁的实现

• (1) 悲观锁

  悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

  通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select … for update时会获取被select中的数据行的行锁，因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

  这里需要特别注意的是，不同的数据库对select… for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。另外，mysql还有个问题是: select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在mysql中用悲观锁务必要确定使用了索引，而不是全表扫描。

• (2) 乐观锁

  乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

  乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳

  乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

• (3) 应用场景

  一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

Java JDBC 对事务的支持

对于JDBC而言，每条单独的语句都是一个事务，即每个语句后都隐含一个commit。实际上，Connection 提供了一个auto-commit的属性来指定事务何时结束。当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说，每个SQL操作都是一个事务；当auto-commit为false时，每个事务都必须显式调用commit方法进行提交，或者显式调用rollback方法进行回滚。auto-commit默认为true。

try {  
    conn.setAutoCommit(false);  //将自动提交设置为false        
    ps.executeUpdate("修改SQL"); //执行修改操作  
    ps.executeQuery("查询SQL");  //执行查询操作                 
    conn.commit();      //当两个操作成功后手动提交     
} catch (Exception e) {  
    conn.rollback();    //一旦其中一个操作出错都将回滚，使两个操作都不成功  
    e.printStackTrace();  
} 

为了能够将多条SQL当成一个事务执行，必须首先通过Connection关闭auto-commit模式，然后通过Connection的setTransactionIsolation()方法设置事务的隔离级别，最后分别通过Connection的commit()方法和rollback()方法来提交事务和回滚事务。

MySQL存储引擎中的MyISAM和InnoDB区别详解

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点： 不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：

• (a) 存储结构：
  每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

• (b) 存储空间：
  MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

• (c) 可移植性、备份及恢复：
  MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

• (d) 事务支持：
  MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。

• (e) AUTO_INCREMENT：
  在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

• (f) 表锁差异：
  MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

• (g) 全文索引：
  MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

• (h) 表主键：
  MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

• (i) 表的具体行数：
  MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

• (j) CURD操作：
  在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

• (k) 外键：MyISAM不支持外键，而InnoDB支持外键。

  通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁、外键等等。尤其在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，必须需要注意的是，任何一种表都不是万能的，合适的才是最好的，才能最大的发挥MySQL的性能优势。如果是不复杂的、非关键的Web应用，还是可以继续考虑MyISAM的，这个具体情况具体考虑。