1、数据库 ACID 特性

  • 原子性（Atomicity） 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
  • 一致性（Consistency） 数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
  • 隔离性（Isolation） 一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • 持久性（Durability） 一旦事务提交，则其所做的修改将会永远保存到数据库中。即时系统发生崩溃，事务执行的结果也不能丢失。

2、四大隔离级别以及不可重复读和幻读出现的原因

隔离级别

  • 读未提交：最低级别，任何情况都会发生。
  • 读已提交：可避免脏读的发生。
  • 可重复读：可避免脏读、不可重复读的发生。（mysql 默认级别）
  • 串行化：避免脏读、不可重复读，幻读的发生。

脏读

A 事务读取 B 事务尚未提交的更改数据，并在这个数据基础上操作。如果 B 事务回滚，那么 A 事务读到的数据根本不是合法的。

不可重复读

A 事务读取了 B 事务已经提交的更改（或删除）数据。比如 A 事务第一次读取数据，然后 B 事务更改数据并提交，A 事务再次读取数据，两次读取的数据不一样。

幻读

A 事务读取了 B 事务已经提交的新增数据。注意和不可重复读的区别，这里是新增，不可重复读是更改（或删除）。这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必须加表级锁，防止在这个表中新增一条数据。

3、封锁的粒度、锁的类型、MVCC

封锁的粒度

行级锁以及表级锁。在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。 应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

三级封锁协议（理论上的）

  • 一级封锁协议：事务 T 中如果对数据 R 有写操作，必须在这个事务中对 R 的第一次读操作前对它加 X 锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。
  • 二级封锁协议：一级封锁协议加上事务 T 在读取数据 R 之前必须先对其加 S 锁，读完后才能释放 S 锁。
  • 三级封锁协议：一级封锁协议加上事务 T 在读取数据 R 之前必须先对其加 S 锁，直到事务结束才释放。

数据操作类型来分锁

  • 读写锁 排他锁，X 锁，写锁。 共享锁，S 锁，读锁。 在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其他事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这个是非常耗时的。

  • 意向锁 在原来的 X/S 锁之上引入了 IX/IS，IX/IX 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定： （1）一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁； （2）一个事务在获得某个数据行对象得 X 锁之前，必须先获得表的 IX 锁。 通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其他事务对表 A 加了X/IX/X/IS 锁，如果加了就表示有其他事务正在使用这个表或者表中某一行锁，因此事务 T 加 X 锁失败。

  • 悲观锁和乐观锁 悲观锁：指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。 乐观锁：假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。

乐观锁的实现：使用版本号；当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则给予更新，否则认为是过期数据。实际就是并发控制中 CAS 理论。

MVCC 版本控制协议

多版本并发控制（MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。 实现了对读的非阻塞，读不加速，读写不冲突。缺点是每行记录都需要额外的存储空间，需要做更多的维护和检查工作。

主要存储结构和实现过程：

  • 版本号： 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。 事务版本号：事务开始时的系统版本号。

  • 隐藏的列： MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号： ① 创建版本号：指示创建一个数据行的快照时的系统版本号； ② 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

  • Undo 日志： MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

  • 实现过程： 以下实现过程针对可重复读隔离级别。 当开始新一个事务时，该事务的版本号肯定会大于当前所有数据运行快照的创建版本号。 （1）Select 多个事务必须读取到同一个数据运行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其他事务的读取结果一致。 把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大于或者等于 T 的版本号，那么表示该数据行快照是其他事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须大于 T 的版本号，因为如果小于 T 的版本号，那么表示该数据行是已经被删除的，不应该去读取它。

（2）Insert 将当前系统版本号作为数据行快照的创建版本号。

（3）Delete 将当前系统版本号作为数据行快照的删除版本号。

（4）Update 将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 Delete 后执行 Insert。

Mysql的隔离性怎么实现的？ 答：写用锁，读用MVCC。

4、B/B+ Tree 原理、与其他查找树的比较

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是 B 树的特征之一，它的每一个节点最多包含 m 个孩子，m 称为 B 树的阶，m 的大小取决于磁盘页的大小。

B 树

关键字（包括数据）集合分布在整颗树中； 任何一个关键字出现只出现在一个结点中； 搜索有可能在非叶子结点结束； 其他搜索性能等价于在关键字全集内做一次二分查找；

B+ 树

关键字不保存数据，只用来索引，所有数据都保存在叶子节点（B 树是每个关键字都保存数据）。 所有叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接。 所有的非叶子节点可以看成是索引部分，节点中仅含其子树中的最大（或最小）关键字。通常在 B+ 树上有两个头指针，一个指向根节点，一个指向关键字最小的叶子节点。 同一个数字会在不同节点中重复出现，根节点的最大元素就是 B+ 树的最大元素。

B+ 树相比于 B树的查询优势

  • B+ 树空间利用率更高，可减少 I/O 次数 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗。而因为 B+ 树的内部节点只是作为索引使用，而不像 B- 树那样每个节点都需要存储硬盘指针。也就是说：B+ 树中每个非叶子节点没有指向某个关键字具体信息的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少 I/O 操作。 eg：假设磁盘中的一个盘块容纳 16bytes，而一个关键字 2bytes，一个关键字具体信息指针 2bytes。一棵 9阶 B- 树（一个结点最多8个关键字）的内部节点需要 2 个盘块。而 B+ 树内部节点只需要 1 个盘块。当需要把内部节点读入内存中的时候，B 树就比 B+ 树多一次盘块查找时间（在磁盘中是盘片旋转的时间）。

  • 增删文件（节点）时，效率更高 因为 B+ 树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，基于范围查询更好。

  • B+ 树的查询效率更加稳定 因为 B+ 树的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同，导致每一次查询的效率相当。

与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ 树作为索引结构，主要有以下两个原因：

  • 更少的查找次数 平衡树查找操作的时间复杂度和树高 h 相关， O(h) = O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ 树的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ 树大非常多，查找的次数也就更多。

  • 利用磁盘预读特性 为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

5、B+ 树索引和 hash 索引的比较（数据结构角度看索引）

B+ 树索引和 hash 索引的明显区别是： （1）如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在的位置，然后再根据链表往后扫描，直到找到相应的数据；

（2）如果是范围查询检索，原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用所用完成范围查询检索；同理，哈希索引也没办法利用索引完成排序，以及 like 'xxx%' 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

（3）哈希索引也不支持多列联合索引的最左匹配规则；

（4）B+ 树索引的关键字检索效率比较平均，不像 B 树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

6、MySQL 索引优缺点（逻辑角度看索引）

1、MySQL 有哪些索引？

索引分为单列索引和组合索引。
单列索引包括：主键索引（不允许有空值），唯一索引，普通索引；

2、为什么要有索引？（创建索引的优点）

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

3、创建索引的缺点

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

4、索引列的选择（哪些列适合作为索引列）

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

5、哪些列不适合作为索引列

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

7、MySQL 优化

1、索引优化

（1）建立聚集索引

首先聚合索引是提升查询速度的最有效的手段。基于聚合索引的性质，我们可以了解到，数据库的物理存储顺序是按照聚合索引顺序排序的，而通过聚合索引的 B+ 树，我们可以迅速的查找到任何一行的全部信息。

（2）常查询数据建立索引或者组合索引

（3）最左前缀原则

建立组合索引优化查询语句时，一定要考虑到最左前缀原则，否则你的索引建立的可以说毫无意义。

（4）较长的数据列建立前缀索引

（5）不要建立无意义的索引

对于查询次数很少的语句中的字段和索引、备注描述和大字段的索引等。

2、查询优化

（1）使用 Explain 进行分析

Explain 用来分析 select 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。比较重要的字段有：

  • select_type：查询类型，有简单查询、联合查询、子查询等；
  • key：使用的索引；
  • rows：扫描的行数；

（2）优化数据访问

  • 1）减少请求的数据量
    只返回必要的列：最好不要使用 select * 语句。
    只返回必要的行：使用 limit 语句来限制返回的数据。
    缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显。
  • 2）减少服务器端扫描的行数
    最有效的方式是使用索引来覆盖查询。

（3）重构查询方式

  • 1）切分大查询
    一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
  • 2）分解大连接查询
    将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：
    让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其他表的查询缓存依然可以使用。分解成多个单表查询，这些单表查询的缓存结果更可能被其他查询使用到，从而减少冗余记录的查询。

8、InnoDB 与 MyISAM 比较

  • MyISAM 是非事务安全的，而 InnoDB 是事务安全的；
  • MyISAM 锁的粒度是表级的，而 InnoDB 支持行级锁；
  • MyISAM 支持全文类型索引，而 InnoDB 不支持全文索引；
  • MyISAM 相对简单，效率上要优于 InnoDB，小型应用可以考虑使用 MyISAM；
  • MyISAM 表保存成文件形式，跨平台使用更加方便；
  • MyISAM 管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量 select 操作，可选择；
  • InnoDB 用于事务处理，具有 ACID 事务支持等特性，如果在应用中执行大量 insert 和 update 操作，可选择；
  • InnoDB 支持外键（从 A 表一个列（外键）去检索 B 表的主键）；
  • MyISAM 一般是非聚集索引，InnoDB 是聚集索引。

9、聚集索引和非聚集索引（从物理角度分析索引）

InnoDB 中索引的组织形式是 B+ 树，非叶子节点存 key，叶子节点存 key+data，叶子节点之间用指针联通。

  • 聚集索引：data 存放是数据页
  • 非聚集索引：data 中存放的是主键的值，得到主键后还需要在聚集索引上再查询一次在效率方面最好使用聚集索引，并给表设定唯一主键。在数据索引的存储有序的情况下，可以大大提高效率。

10、水平切分与垂直切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

Sharding（切分）存在的问题

（1）事务问题
使用分布式事务来解决，比如 XA 接口。
（2）连接
可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
（3）ID 唯一性
使用全局唯一 ID（GUID）；为每个分片指定一个 ID 范围；分布式 ID 生成器（如 Twitter 的 Snowflake 算法）。

11、数据库中 char 和 varchar 的区别

（1） char 的长度是固定的，而 varchar 的长度是可以变化的，比如，存储字符串“abc”，对于 char(10)，表示你存储的字符将占 10 个字节（包括 7 个空字符），而同样的 varchar(10) 则只占用 3 个字节的长度，10 只是最大值，当你存储的字符小于 10 时，按实际长度存储。
（2）char 的效率比 varchar 的效率稍高。varchar 比 char 节省空间，在效率上比 char 会稍微差一些，即要想获得效率，就必须牺牲一定的空间。以空间换时间。

12、数据库的热备份和冷备份

1、冷备份（慢）

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言，冷备份是最快和最安全的方法。

  • 冷备份的优点：
    （1）是非常快速的备份方法（只需要拷贝文件）
    （2）容易归档（简单拷贝即可）
    （3）容易恢复到某个时间点上（只需将文件再拷贝回去）
    （4）能与归档方法相结合，作数据“最新状态”的恢复。
    （5）低度维护，高度安全。
  • 冷备份的缺点：
    （1）单独使用时，只能提供到 “某一时间点上”的恢复。
    （2）在实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
    （3）如果磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
    （4）不能按表或按用户恢复。

2、热备份（快）

热备份是在数据库运行的情况下，备份数据库操作的 sql 语句，当数据库发生问题时，可以重新执行一遍备份的 sql 语句。

  • 热备份的优点：
    （1）可在表空间或数据文件级备份，备份时间短。
    （2）备份时数据库仍可使用。
    （3）可达到秒级恢复（恢复到某一时间点上）。
    （4）可对几乎所有数据库实体作恢复。
    （5）恢复是快速的，在大多数情况下在数据库仍工作时恢复。
  • 热备份的缺点：
    （1）不能出错，否则后果严重。
    （2）如果热备份不成功，所得结果不可用于时间点的恢复。
    （3）因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

13、数据库视图和存储过程

一、视图

视图是虚拟的表，它只包含使用时动态检索数据的查询。

  • 使用视图的好处
    ①重用 SQL 语句；
    ②简化复杂的 SQL 操作（可以方便的重用它而不必直到它的基本查询细节）；
    ③使用表的组成部分而不是整个表；
    ④保护数据（可以给用户授予表的部分访问权限而不是整个表的访问权限）；
    ⑤更改数据格式和表示（视图可返回与底层表的表示和格式不同的数据）。
    PS：因为视图不包含数据，所以每次使用视图，都必须处理执行所需的任何一个检索；
    如果使用了多个联结和过滤创建了复杂的视图或嵌套视图，性能下降比较明显。

  • 视图的规则和限制
    ①视图必须唯一命名（视图与别的视图或表不能有相同的名字）；
    ②创建的视图数目没有限制；
    ③为了创建视图，必须有足够的访问权限；
    ④视图可以嵌套；
    ⑤order by 也可用于视图；但如果该视图中检索数据的 select 语句包含 order by，那么该视图中的 order by 将被覆盖；
    ⑥视图不能索引，也不能有关联的触发器或默认值；
    ⑦视图可以和表一起使用。

  • 使用视图
    create view：创建视图；
    show create view viewname：查看创建视图的语句；
    drop view viewname：删除视图；

二、存储过程

定义：为方便以后使用而保存的一条或多条 MySQL 语句的集合，可将其视为批文件（虽然其作用不仅不限于批处理）。

  • 为什么要使用存储过程（简单、安全、高性能）
    ①通过把处理封装在容易使用的单元中，简化复杂的操作；
    ②不要求建立一些列处理步骤，保证了数据的完整性；
    ③简化对变动的管理（如果表名、列名或业务逻辑变更，只需要更改存储过程的代码），这一点的延伸就是安全性；
    ④提高性能（使用存储过程比使用单独 SQL 语句要快）；