一、事务的四大特性 ACID

  • 只有满足一致性，事务的执行结果才是正确的。

  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时要只要能满足原子性，就一定能满足一致性。

  • 在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

  • 事务满足持久化是为了能应对数据库奔溃的情况。

1.1 原子性 Atomicity

原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。

回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

1.2 一致性 Consistency

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。

1.3 隔离性 Isolation

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

1.4 持久性 Durability

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

可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

二、并发一致性问题

2.1 丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

2.2 脏读

如果一个事务中对数据进行了更新，但事务还没有提交，另一个事务可以 “看到” 该事务没有提交的更新结果，这样造成的问题就是，如果第一个事务回滚，那么，第二个事务在此之前所 “看到” 的数据就是一笔脏数据。（脏读又称无效数据读出， 一个事务读取另外一个事务还没有提交的数据叫脏读。）

例子：

  1. Mary 的原工资为 1000, 财务人员将 Mary 的工资改为了 8000 (但未提交事务)
  2. Mary 读取自己的工资，发现自己的工资变为了 8000，欢天喜地！
  3. 而财务发现操作有误，回滚了事务，Mary 的工资又变为了1000
  4. 像这样，Mary记取的工资数8000是一个脏数据。

解决办法：
　　把数据库的事务隔离级别调整到 READ_COMMITTED

图解：
　　T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

2.3 不可重复读

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（同时操作，事务1分别读取事务2操作时和提交后的数据，读取的记录内容不一致。不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。 ）

例子：

  1. 在事务1中，Mary 读取了自己的工资为1000，操作并没有完成
  2. 在事务2中，这时财务人员修改了 Mary 的工资为 2000，并提交了事务
  3. 在事务1中，Mary 再次读取自己的工资时，工资变为了2000

解决办法：
　　如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE_READ

图解：
　　T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

2.4 幻读

事务 T1 读取一条指定的 Where 子句所返回的结果集，然后 T2 事务新插入一行记录，这行记录恰好可以满足T1 所使用的查询条件。然后 T1 再次对表进行检索，但又看到了 T2 插入的数据。 （和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）。幻读的重点在于新增或者删除 (数据条数变化)，同样的条件，第1次和第2次读出来的记录数不一样。

例子：

  1. 事务1，读取所有工资为 1000 的员工（共读取 10 条记录 ）
  2. 这时另一个事务向 employee 表插入了一条员工记录，工资也为 1000
  3. 事务1再次读取所有工资为 1000的 员工（共读取到了 11 条记录，这就产生了幻读）

解决办法：
　　如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。把数据库的事务隔离级别调整到 SERIALIZABLE_READ

图解：
　　T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

三、事务隔离级别

读数据一致性及允许的并发副作用隔离级别 读数据一致性 脏读 不可重复读 幻读
未提交读 最低级别，只能保证不读取物理上损坏的数据 Y Y Y
已提交读 语句级 N Y Y
可重复读 事务级 N N Y
可序列化 最高级别，事务级 N N N

3.1 未提交读 Read Uncommitted

最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。

3.2 已提交读 Read Committed

被读取的数据可以被其他事务修改，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。

该等级也是 SQL Server、Oracle 默认的隔离等级。

3.3 可重复读 Repeated Read

所有被选中获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，即前一个事务有读锁但是没有范围锁，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。

该等级也是 MySQL 默认的隔离等级。

引申：现在主流数据库都使用 MVCC 并发控制，使用之后 RR（可重复读）隔离级别下是不会出现幻读的现象。

3.4 串行化 Serializable

所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。

四、MyISAM VS InnoDB

对比项 MyISAM InnoDB
主外键 N Y
事务 N Y
行表锁 表锁，即使操作一条记录也会锁住整个表 行锁，操作时只锁某一行，不对其他行有影响
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还缓存真实数据，内存要求较高，且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务
默认安装 Y Y

五、锁机制

5.1 乐观锁、悲观锁

1.乐观锁
顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

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

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

5.2 表锁

1.表共享读锁（Table Read Lock）
对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作。

2.表独占写锁（Table Write Lock）
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

5.3 行锁

1. 共享锁（S）
共享锁又称为读锁。若事务T对数据对象A加上 S 锁，则事务T可以读A 但不能修改A，其他事务只能再对A加 S 锁，而不能加X锁，直到T释放A上的 S 锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

2. 排他锁（X）
排它锁又称为写锁。若事务T对数据对象A加上 X 锁，则允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

3. 意向共享锁（IS）
表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁。如果需要对记录 A 加共享锁，那么此时 InnoDB 会先找到这张表，对该表加意向共享锁之后，再对记录 A 添加共享锁。

4. 意向排他锁（IX）
类似上面，表示事务准备给数据行加入排他锁，也就是说事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。如果需要对记录 A 加排他锁，那么此时 InnoDB 会先找到这张表，对该表加意向排他锁之后，再对记录 A 添加排他锁。

六、索引

索引（Index）是帮助MySQL高效获取数据的数据结构，可以被理解为排好序的快速查找数据结构。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们通常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、次要索引、覆盖索引、混合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。

6.1 索引的特点

  1. 可以加快数据库的检索速度
  2. 降低数据库插入、修改、删除等维护的速度
  3. 只能创建在表上，不能创建到视图上
  4. 既可以直接创建又可以间接创建
  5. 可以在优化隐藏中使用索引
  6. 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

6.2 索引的优点

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

6.3 索引的缺点

  1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  2. 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
  3. 当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

6.4 不会使用索引的时机

  1. 如果MySQL估计使用全表扫秒比使用索引快，则不适用索引。
1
2
select * from table_name where key>1 and key<90;
//如果列key均匀分布在1和100之间，这个查询使用索引就不是很好
  1. 如果条件中有or，即使其中有条件带索引也不会使用。
1
2
select * from table_name where key1='a' or key2='b';
//在key1上有索引而在key2上没有索引，则该查询也不会走索引
  1. 复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀）。
1
2
select * from table_name where key2='b';
//符合索引为(key1,key2),则查询将不会使用索引
  1. 如果like是以 % 开始的，则该列上的索引不会被使用。
1
2
select * from table_name where key1 like '%a';
//该查询即使key1上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引
  1. 如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。
1
2
select * from table_name where key1=1;
//如果key1列保存的是字符串，即使key1上有索引，也不会被使用。

6.5 适合建立索引的时机

  1. 为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
  2. 在union等集合操作的结果集字段上，建立索引。
  3. 经常用作查询选择 where 后的字段，建立索引。
  4. 在经常用作表连接 join 的属性上，建立索引。
  5. 考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

6.6 索引分类

6.6.1 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引。

6.6.2 唯一索引

索引列的值必须唯一，但允许有空值。

6.6.3 联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引：Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c)，可以支持[a]、[a,b]、[a,b,c] 3种组合进行查找，但不支 [b,c] 进行查找。当最左侧字段是常量引用时，索引就十分有效。

七、JOIN

例如我们有两张表，Orders 表通过外键 Id_P 和 Persons 表进行关联。

7.1 inner join

在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

inner join

我们使用 inner join 对两张表进行连接查询，sql如下：

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

查询结果集如下，此种连接方式 Orders 表中 Id_P 字段在 Persons 表中找不到匹配的，则不会列出来。

7.2 left join

在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

left join

我们使用 left join 对两张表进行连接查询，sql如下：

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

查询结果如下，可以看到，左表（Persons表）中 LastName 为 Bush 的行的 Id_P 字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。

7.3 right join

在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

right join

我们使用right join对两张表进行连接查询，sql如下：

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

查询结果如下，Orders 表中最后一条记录 Id_P 字段值为 65，在左表中没有记录与之匹配，但依然保留。

7.4 full join

在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

full join

我们使用 full join 对两张表进行连接查询，sql如下：

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

查询结果如下，查询结果是 left join 和 right join 的并集。