事务+引擎+索引

事务

事务：完成增删改查的一套操作到提交成功的一个过程

事务的执行过程如下:

1. 系统会为每个事务开辟一个私有工作区

2. 事务读操作将从磁盘中拷贝数据项到工作区中,在执行写操作前所有的更新都作用于工作区中的拷贝.

3. 事务的写操作将把数据输出到内存的缓冲区中,等到合适的时间再由缓冲区管理器将数据写入到磁盘。

事务的四大特性

原子性：操作不可再分，要么全部执行成功，要么全部执行失败

隔离性：在并发事务发生的情况下，为了保证数据的准确性，把每个事务隔离开也就是说优先获取操作权限的事务可进行执行

一致性：一个事务对数据库中的数据进行增删改查时需要遵循数据库中表的相关的结构要求，数据要求等

永久性：事务执行结束后，进行提交后把数据写入了硬盘中，该数据不会因为一些故障而丢失，而是永远保存在硬盘中

由于数据库存在立即修改和延迟修改,所以在事务执行过程中可能存在以下情况:

1、在事务提交前出现故障,但是事务对数据库的部分修改已经写入磁盘数据库中。这导致了事务的原子性被破坏。

2、在系统崩溃前事务已经提交,但数据还在内存缓冲区中没有写入磁盘。系统恢复时将丢失此次已提交的修改，对事务持久性的破坏

对数据一致性的破坏主要来源于

1、并发事务的执行

2、系统出现故障或者事务出现故障

并发控制技术：保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。 日志恢复技术：保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

所以事务的隔离性和原子性解决了以上两个问题

隔离性的实现——并发控制技术

共享锁(S):事务T对数据A加共享锁,其他事务只能对A加共享锁但不能加排他锁。

设置共享锁：select * from user where id = 1 LOCK IN SHARE MODE;

排他锁(X):事务T对数据A加排他锁,其他事务对A既不能加共享锁也不能加排他锁

设置排他锁：select * from user where id = 1 FOR UPDATE;

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

基于锁的并发控制技术：某一个事务想对数据进行相关的操作就会向锁管理器申请相应的锁，如对数据进行增删改需要申请排他锁，对数据进行读取需要获取共享锁。

实现流程：事务会按照执行的具体要求去申请相应类型的锁，申请的锁会被发送给锁管理器，锁管理器会判断当前事务申请的操作数据的锁是否已经被使用，没有则正常执行，否则等待其他事务执行完之后在进行获取

存在问题：多个事务相互等待彼此的锁，最终形成死锁。数据一直被加共享锁，其他事务无法获取该数据的排他锁，形成饥饿现象。

基于时间戳的并发技术：在每个事务开始之前会给予一个时间标记，例如计时器。最开始执行的事务会被一直执行直到执行完毕，回滚事务，此时再把该事务的时间戳给予0，然后让后面的事务即时间戳大的执行。

两种并发技术的主要区别:一种基于锁控制，一种基于的是回滚事务

乐观锁与悲观锁

乐观并发控制:对于并发执行可能冲突的操作,假定其不会真的冲突,允许并发执行,直到真正发生冲突时才去解决冲突,比如让事务回滚。

悲观并发控制:对于并发执行可能冲突的操作,假定其必定发生冲突,通过让事务等待(锁)或者中止(时间戳排序)的方式使并行的操作串行执行。

引擎

InnoDB引擎：提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

支持事务，支持的事务是安全的，该引擎还提供了行级锁和外键约束，还有缓冲池、提供了缓冲数据和索引

索引数据结构：B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是 聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

适用场景：用于事务处理，具有ACID事物支持，应用于执行大量的insert和update操作的表

MyIsAM引擎：不支持事务，不支持行级锁，不支持外键约束，操作数据的时候会锁定数据的所在的整张表，并且保留表的行数，所以在查询数据的时候特别快。 

索引数据结构：B+Tree树来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内 容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据

适用场景：用于管理非事务表，提供高速检索及全文检索能力，适用于有大量的select操作的表，如 日志表

MEMORY引擎：使用存在于内存中的内容而创建的表，每一个memory只实际对应一个磁盘文件。因为是存在内存中的，所以memory访问速度非常快，而且该引擎使用hash索引，可以一次定位，不需要像B树一样从根节点查找到枝节点，所以精确查询时访问速度特别快，但是非精确查找时，比如like，这种范围查找，hash就起不到作用了。另外一旦服务关闭，表中的数据就会丢失，因为没有存到磁盘中。

适用场景：主要用于内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考 虑好数据的存储

索引

索引：对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

实现原理

通过B+Tree缩扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址“，最终通过索引检索到数据之后，获取到所关联的”物理地址“，在通过这个”物理地址“定位表中的数据

优点

索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁） 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。 对于非常小的表，大部分情况下简单的全表扫描更高效

B-Tree树

B树是一种多路搜索树，一棵m阶的B树满足下列条件：

树中每个结点至多有m个孩子 根结点的儿子数为[2, M]； 除根结点以外的非叶子结点的儿子数为[M/2, M]； 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字） 非叶子结点的关键字个数 = 指向子节点的指针个数-1； 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]； 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树； 所有叶子结点位于同一层； 以下是3阶B树

磁盘读取数据是以盘块(block)为基本单位的。

以下结合磁盘块作图 

B树的特征：

关键字集合分布在整颗树中； 任何一个关键字出现且只出现在一个结点中； 搜索有可能在非叶子结点结束； 其搜索性能等价于在关键字全集内做一次二分查找； 自动层次控制； B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+Tree树

B+树是B-树的变体，也是一种多路搜索树：（❀ 表示两者间的不同点）

树中每个结点至多有m个孩子

根结点的儿子数为[2, M]；

除根结点以外的非叶子结点的儿子数为[M/2, M]；

每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

❀ 非叶子结点的子树指针与关键字个数相同；

❀ 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区间）；

❀ 为所有叶子结点增加一个链指针；

❀ 所有关键字都在叶子结点出现；

B+树的特征：

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的； 不可能在非叶子结点命中； 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层； 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。 更适合文件索引系统； B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

为什么B+ 树比B 树更适合作为索引？

B+ 树的磁盘读写代价更低 B+ 树的数据都集中在叶子节点，分支节点 只负责指针（索引）；

B 树的分支节点既有指针也有数据 。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。

B+ 树的查询效率更加稳定 B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。

B+树更便于遍历 由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；

B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。 B+树更擅长范围查询 B+树叶子节点存放数据，数据是按顺序放置的双向链表。

B树范围查询只能中序遍历。

B+ 树占用内存空间小 B+ 树索引节点没有数据，比较小。

在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引

注：图片源于网络