数据库事务与索引详解-优快云博客

本文链接：https://blog.youkuaiyun.com/sinat_41859004/article/details/128488461

数据库类型

数据库的类型主要分为四大类：一：关系型数据库。二：非关系型数据库。三：网状数据库。四：层次数据库。

目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

关系型数据库

关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，可依据根据其行列值定位数据，它是目前主流的数据库技术。在关系型数据库中，对数据的操作基本上都建立在一个或多个表格上，可采用结构化查询语言（SQL）对数据库进行操作。

关系型数据库支持事务处理能力，其遵循ACID特性（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability））。

什么是事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

举例：事务最经典的例子就是转账。
假如小明要给小红转账1000元，这个转账会涉及到的两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误，比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就出现问题了。而事务就是保障这两个关键操作要么都成功，要么都失败。

事务的四大特点（ACID）

1.原子性

事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。（原子性有undo log保证）

2.一致性

执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。（一致性由代码层面保证）

3.隔离性

并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务至今数据库是独立的。（隔离性由MVCC或者锁保证）

4.持久性

一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。（持久性由内存+redo log保证）

并发事务带来的问题（什么是脏读？幻读？不可重复读？）

脏读(Dirty Read)

一个事务在处理过程中读取了另一个事务未提交的数据。第一个事务修改了一份数据，但是未提交确认，而第二个事务读取了同一份数据，由于某些原因，第一个事务又放弃修改，RollBack了操作，此时，第二个事务就得到一个脏数据。

幻读(Phantom Read)

在一个事务的两次查询中数据不一致。第一个事务查询了一次数据，而第二个事务在其查询后插入一行记录，而此时第一个事务再次查询了包含这个新纪录的数据，就会发现有几列数据是它先前所没有的。这时幻读发生了。

不可重复读(Non-Repeatable Read)

反复读同一个数据却得到不同的结果，这是因为在反复几次读取的过程中，有事务将数据修改了。

脏读和不可重复读的区别：
脏读是某一事务读取了另外一个事务未提交的数据，不可重复读是读取了其他事务提交的数据。

不可重复读和幻读的区别：
1.不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）；
2.不可重复读重点在于修改，比如多次读取一条记录发现其中某些列的值被修改，而幻读的重点在于新增或者删除，发现记录增多或者减少。

事务的隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

READ-UNCOMMITTED（读取未提交）

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED（读取已提交）

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ（可重复读）

对同一字段的多次读取结果都是一致的，除非数据时被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE（可串行化）

最高的隔离级别，完全服从ACID的隔离级别。所有的事务一次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

注：
Mysql 默认采用 REPEATABLE-READ（可重复读） 隔离级别；
Oracle 默认采用 READ-COMMITTED（读取已提交） 隔离级别。

事务隔离机制的实现基于 锁机制和并发调度。其中并发调度使用的是MVCC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，使用大部分数据库系统的隔离级别都是READ-COMMITTED（读取已提交）；但InnoDB存储引擎默认使用 REPEATABLE-READ(可重复读)，~~并不会有任何性能损失。~~ 且在分布式事务的情况下一般会用到 SERIALIZABLE（可串行化） 隔离级别。

MySQL数据库

MySQL是最流行的关系型数据库管理系统之一。具有代表性的数据库管理系统有：Oracle、SQL Server、DB2等。

MySQL存储引擎

定义：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。在关系型数据库中，数据的存储是以表的形式存储的，因此，存储引擎也可称为表类型（存储和操作此表的类型）。

常用的存储引擎

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，也是目前MYSQL的默认事务型引擎。它支持事务，并且还提供了行级锁和外键的约束。

InnoDB的主要特性

1.InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）特性。
2.InnoDB支持行级锁，增加了多用户部署和提高了性能。
3.InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4.InnoDB支持外键完整性约束。外键是指定表中与另一个表的另一个字段匹配的字段，用来建立主表与从表的关联关系，并对相关表中的数据设置了约束，这使MySQL能够保持参照完整性。

使用场景

由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求较高（比如银行），要求实现并发控制，比如售票，那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

MyISAM引擎（原本MySQL的默认引擎）

MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

特征
它拥有较高的插入、查询速度，但不支持事物和外键，也不支持行级锁。

使用场景
如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

InnoDB索引与MyISAM索引的区别

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效；MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
InnoDB支持行锁和表锁，默认为行锁；MyISAM只支持表锁
InnoDB支持事务，具有事务提交和回滚的能力；MyISAM不支持事务。
InnoDB的表和索引在一个逻辑表空间中。MyISAM中，每个表被存放在分离的文件中。
InnoDB支持外键，MyISAM不支持外键。
InnoDB支持哈希索引，MyISAM不支持哈希索引。
InnoDB支持数据库异常奔溃后的安全恢复：MyISAM不支持。使用InnoDB的数据库在异常奔溃后，数据库重新启动的时候会保证数据库恢复到奔溃前的状态。这个恢复的过程依赖于redo log
InnoDB支持MVCC，MyISAM不支持。（MVCC可以看作是行锁的升级，可以有效减少加锁操作，提高性能）
MyISAM允许没有任何索引和主键的表存在，索引都是保存的行地址；如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

【题】自增主键

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把MySQL重启，再insert一条记录，这条记录的ID是18还是15？

答：如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失；
如果表的类型是InnoDB，那么是15。因为InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPIIMIZE操作，都会导致最大ID丢失。

索引

什么是索引？

索引是一种用于快速检索数据的数据结构。常见的索引结构有(按照数据结构来分)：B树，B+树和Hash(Mysql中没用此结构)。索引查询都是比全表扫描要快的，但是数据库数量不大的情况下，使用索引不能带来很大的查询效率的提升。

索引的优点

使用索引可以大大加快数据检索速度(大大减少检索的数据量)，这也是创建索引的主要原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点

创建索引和维护索引需要耗费额外时间。当对表中的数据进行增删改的时候，如果数据列上有索引，那么索引也需要动态修改。会降低SQL执行效率。
索引需要使用物理文件（索引文件）存储，也会耗费一定空间。

索引的底层数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

1.B+树索引

mysql通过存储引擎获取数据，大部分使用的是InnoDB。按照实现方式分，InnoDB的索引类型目前有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引指的是B树索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。

B+tree性质：
1）n个子树节点包含n个关键字，不是用来保存数据而是保存数据的索引。
2）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身按照关键字的大小自小而大顺序链接。
3）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
4）B+ 树中，数据对象的插入和删除仅在叶节点上进行。
5）B+树有2个头指针，一个是树的根节点，一个是最小关键字的叶节点。

2.哈希索引

类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

Hash表：Hash表是键值对的集合，通过key即可快速取出对应的值，因此hash表可以快速检索数据。(接近O(1))。为什么MySQL中没有使用Hash结构的索引，原因是Hash冲突问题，最重要是Hash索引不支持顺序和范围查询，比如这个sql语句， Select * from t where id < 500。像这种范围查询，直接遍历比500小的叶子节点就行了，如果用Hash索引，就要把1-499的数据每个都进行一次Hash来定位。

索引使用场景（重点）

order by
当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的。
join
对join语句匹配关系（on）涉及的字段建立索引能够提高效率
索引覆盖
如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

索引类型

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引最大的区别就是叶子节点存储的数据不同。
聚簇索引：聚簇索引叶子节点存储的是行数据。
非聚簇索引：非聚簇索引叶子节点存储的是主键Id值和索引列数据。

因此通过聚簇索引就可以直接找到行数据，而通过非聚簇索引只能找到主键Id值，再进行一次回表查询。（故聚簇索引比非聚簇索引访问速度更快，但是聚簇索引更新的代价更高）

聚簇索引一般为主键索引，一张表只能有一个主键，因此聚簇索引也只能有一个。非聚簇索引没有数量上的限制。MySQL中主键索引为聚簇索引，其他普通索引为非聚簇索引。

innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
联合索引：使用表中的多个字段创建索引，就是联合索引，也叫组合索引或者复合索引。联合索引使用最左匹配原则，MYSQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如>,<,between和前导模糊查询等条件，才会停止匹配。所以，在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

全文索引：目前搜索引擎使用的一种关键技术。

索引的算法有哪些？

索引算法有BTree算法和Hash算法

BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：只要它的查询条件是一个不以通配符开头的常量
select * from user where name like ‘jack%’;如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
select * from user where name like ‘%jack’;

Hash算法

Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

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

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值；
被频繁作为条件查询和排序的字段（因为索引是排好序的）应该考虑适当建立索引；被频繁更新的字段应该慎重建立索引，虽然能够带来查询上的效率，但是维护索引的成本也不小，如果一个字段经常被查询，且经常被修改，那就更不应该在这种字段上建立索引了。
尽可能建立联合索引而不是单列索引：因为所以是需要额外占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。如果表中的字段过多，索引过多，那么当这个表的数据到达一定体量后，索引占用的空间也很多。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
避免冗余索引：能够命中索引(a,b)就肯定能命中索引(a)，那么索引(a)就是冗余索引。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

使用索引的一些建议

避免where子句中对字段施加函数，这会造成无法命中索引。
在使用InnoDB时使用与业务无关的自增主键作为主键。即使用逻辑主键，而不要使用业务主键。
删除长期未使用的索引，不用的索引会造成性能损耗。
在使用limit offset查询缓慢时，可以借助索引来提高性能

面试题

数据库为什么使用B+树而不是B树

1.B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
2.B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
3.B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
4.B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
5.增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

B树和B+树的区别

B树，全称为多路平衡查找树（B是Balabced平衡），是左小右大的顺序存储结构，B+树是B树的一种变体。
两者的异同：

B树的所有节点既存放键(key)也存放数据(data)，而B+树只有叶子节点存放key和data，其他内存节点只存放key。
B树的叶子节点都是独立的；B+树叶子节点有一条引用链指向与它相邻的叶子节点。
B树的检索过程相当于对范围内每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。（相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定）
B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

B+树在满足聚簇索引和覆盖索引的时候需不需要回表查询数据？

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

注：非聚簇索引一定要回表查询吗？、
不一定，如果满足覆盖索引就不需要回表查询。

日常工作中你是怎么优化SQL的？

1.索引角度：适当建立索引。（查询、排序语句会大大提高效率），看看sql语句有没有用到索引。
2.避免查询一些不必要的数据（这个可能导致本身能使用覆盖索引，还用不到造成一次回表查询）。
3.如果你查询的结果集过多的话是不是考虑分批量的去进行查询。
4.不要有太多表连接的操作。过多的表连接会对SQL语句的编译时长有影响，连接越多造成的连接的性能开销也就越大。可以适当的拆开，分成几个小部分去执行。