本文主要是介绍MySQL总结，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1. 事务的四大特性

原子性： 要么都执行，要么都不执行

隔离性： 所有操作全部执行完以前，其他会话看不到结果

一致性： 保证数据的状态操作前和操作后保持一致

持久性： 一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

2. 事务的实现原理

以MySQL的InnoDB引擎为例：

使用redo log（重做日志）保证事务的持久性，使用undo log（回滚日志）保证事务的原子性。使用锁机制、MVCC等手段保证事务的隔离性（默认的隔离级别是REPEATABLE-READ）。

保证了事务的持久性、原子性、隔离性后，一致性才能得到保障。

3. 事务并发导致的问题

• 脏读： 当一个事务正在访问数据并且对数据进行了更改，但是这种更改还没有保存到数据库中，这时另一个事务也访问了这一数据，然后使用了这一数据。因为这个数据是还没有提交的数据，那么另一个事务读到这个数据是**“脏数据”**，依据“脏数据”所作的操作可能是不正确的。
• 丢失修改： 指一个事务在读取一个数据时，另一个事务也读取了这个数据，在第一个事务对数据进行修改后，第二个事务也进行修，这样第一个事务所修改的内容就丢失了，这个过程称为丢失修改。
• 不可重复读： 指一个事务中多次读取同一个数据，在读取完第一个数据后，另一个事务对这个数据进行了修改，那么在第二次读取数据后，这个事务读取的两次数据不一样，这种现象称为不可重复读。
• 幻读： 在整表的操作下，一个事务读取到另一个事务已经提交的数据，造成数据前后不一致，这种情况称之为幻读。

不可重复读与幻读的区别：

不可重复读重点是修改，多次读取同一条数据发现某些值被修改。

幻读重点是新增或删除，多次读取一条记录发现记录增多或减少。

4. 事务的隔离级别

• READ-UNCOMMITTED（读取未提交）： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED（读取已提交）： 允许读取并发事务已经提交的数据，可以阻止脏读，可能会导致幻读或不可重复读。
• REPEATABLE-READ（可重复读）： 对同一字段的多次读取结果都是一致的，除非数据时被本身事务自己所修改，** 可以阻止脏读和不可重复读，但幻读仍有可能发生。**
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离界别。所有的事务依次逐个进行，这样事务之间就完全不同不可能产生干扰，可以阻止脏读、不可重复读以及幻读。

注： 提升数据的隔离级别可以提升数据库的数据安全性，保证数据正确，但是由于数据库提升隔离性，是通过添加锁来实现，所以随之带来的是效率的降低，如果安全性很高，执行效率将会很低。数据库的执行效率和安全性都应该保证，所以应该在保证数据安全的情况下，尽可能的提升数据库的执行效率。

5. MVCC

MVCC原理

MVCC示例

MVCC即多版本并发控制机制。主要用来实现读写的并发，并且不需要加锁，降低系统的开销。InnoDB存储引擎通过保存数据的某个时间的快照来实现，每行数据后面隐藏了两列，分别是创建版本号和删除版本号。

核心思想就是保存一个数据的多个版本号，使得当前读写不会产生冲突。

6. MySQL的三种日志（bin log/redo log/undo log）

三种日志的详细实现可参考 JavaGuide 数据库部分

• bin log（二进制日志）： 数据库级别的日志，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。
• redo log（重做日志）： InnoDB引擎级别的日志，用来记录InnoDB存储引擎的事务日志，不管事务是否提交都会记录下来，用来数据恢复。当数据库发生故障，InnoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。
• undo log（回滚日志）： 当数据进行修改时，除了记录redo log，还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务的回滚，并且根据undo log回溯到某个特定版本的数据，实现MVCC。

bin log 和 redo log的区别：

1）bin log会记录所有日志记录，包含InnoDB，MyISAM等存储引擎，而redo log只会记录InnoDB引擎的事务日志。

2）写入磁盘的时间不同，bin log只在事务提交完成后进行一次写入，而redo log在事务进行中不断的写入。

3）bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

7. 快照读和当前读

• 快照读： 读取的是快照版本。普通的select就是快照读。通过MVCC来进行并发控制，不用加锁。
• 当前读： 读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过MVCC机制避免了幻读现象。而MVCC机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

如何避免幻读？

• 在快照读情况下，MySQL通过MVCC来避免幻读。
• 在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。

8. 乐观锁和悲观锁

乐观锁和悲观锁只是一种思想，和数据库中的锁不同。

• 悲观锁： 对用户的修改持悲观的态度，认为每个用户都想修改数据，所以提前给数据上锁。

  悲观锁的实现往往依靠数据库提供的锁机制。

  悲观锁主要分为共享锁和排他锁

  • 共享锁：即读锁。多个事务可以一起获得共享锁，可读，不可修改。
  • 排他锁：即写锁。当一个事务获得排他锁之后，其他事务不能再获得排他锁或者共享锁，进入阻塞阶段。

  悲观锁适用于写操作较多的情况。

  优点：为数据处理的安全提供了保证。

  缺点：因为加锁，所以会让数据库产生额外的开销，降低了效率，还会增加了产生死锁的机会（两个事务相互想要修改对方锁住的数据，就产生了死锁），降低了并行性。

• 乐观锁： 对用户的修改持乐观的态度，认为用户有很小的概率修改数据，当用户对数据进行提交更新的时候，才会对数据进行检测。

  乐观锁是先进行数据修改，然后判断是不是存在冲突，不存在就提交事务进行更新。

  乐观锁的实现不依靠数据库，而是依靠数据本身

  实现乐观锁可以通过CAS算法和通过版本号。CAS算法和通过版本号实现

  乐观锁适用于写操作较多的情况下

  优点：响应效率高，较好的实现了并行

  缺点：如果冲突效率高，乐观锁重试会反复进行，时间效率很低。

9. 数据库的锁

参考资料

• 行锁： 行锁就是锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引。（会出现死锁，发生锁冲突几率低，并发高。）

注： 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了；两个事务不能锁同一个索引；insert，delete，update在事务中都会自动默认加上排它锁。

• 表锁： 表锁就是锁一整张表，表锁响应的是非索引字段，即全表扫描。（不会出现死锁，发生锁冲突几率高，并发低。）

• 间隙锁： 间隙锁是InnoDB引擎在可重复读提交下为了解决幻读问题引入的锁机制。间隙锁详解

10. MySQL常用的存储引擎

• MyISAM存储引擎： 不支持事务，也不支持外键，优势是访问速度快，对事物的完整性没有要求。

支持三种不同的存储格式：静态表，动态表，压缩表

静态表：表中的字段都是固定长度的，优点是存储速度快，容易缓存，出现故障容易恢复。缺点是占用空间通常比动态表多（因为存储会按照列的宽度补足空格，取数据时会自动去掉空格）。

动态表：表中的字段不是固定长度，优点是占用空间少，缺点是频繁的更新，删除数据会更容易产生碎片。

压缩表：每个记录被单独压缩，所以访问开支小。

• InnoDB存储引擎： 支持事务，但是对比MyISM引擎，写的效率会差一些，并且会占用更多的磁盘空间保留数据和索引。特点：支持自动增长列，支持外键约束。
• MEMORT存储引擎：使用内存中的内容来创建表，数据保存在内存中，服务关闭，数据就丢失了。
• MERGE存储引擎：一组MyISAM表的组合，这些MyISAM表必须结构完全相同。

MySQL 5.5之前默认使用MyISAM存储引擎，MySQL 5.5之后默认使用InnoDB存储引擎。

MyISAM与InnoDB的区别：

11. B树，B+树

二叉树、平衡二叉树，B树，B+树概念

二叉树： 树形结构，每棵树最多有2个节点，且左节点的值要小于根节点的值，右节点的值要大于根节点的值

平衡二叉树： 在二叉树的基础上增加了平衡的条件，即树的两边层级数不会大于1

B树： B树又称平衡多路查找树，B树每个节点最大会有M个节点，每个节点存储的是关键字（每个节点上的关键字是排序的）、关键字的指针、指向子节点的指针。

B+树： B+树是B树的升级版，除了叶子节点，每个节点保存的是关键字和指向子节点的指针，B+树中并没有除了叶子节点外其他节点并没有保存关键字指针，而叶子节点保存他所有父节点的关键字以及关键字指针。因为B+树没有保存关键字指针，所以他能保存的数据更多，能让树的高度更小，因此查询的效率更高。

B树和B+树的异同：

• B树的所有节点都存放键和数据，而B+树只有叶子节点存放键和数据
• B树的叶子节点都是独立的，B+树的叶子节点有一条指向它相邻的叶子节点
• B树的检索过程是先在每个节点进行检索，如果找到数据，就直接将数据提取出来，就结果检索过程。而B+树如果找到数据，还要遍历到叶子节点，因为只有叶子节点才保存键和数据。B+树检索的效率更加稳定，因为每次检索的时间是一样的。

12. 索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有：B树，B+树和Hash。

优点：加快检索速度；创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。

缺点：创建索引和维护索引耗费很多时间，当对数据进行CRUD时，还需要修改索引；索引需要使用物理文件存储，耗费空间。

注： 如果数据库的数据量不大，索引不一定能够带来很大提升。如果要对数据进行频繁的查询则可以建立索引，如果要对数据进行频繁的修改，则不建议建立索引。

13. 索引的类别

索引的详解

索引的分类分为逻辑分类和物理分类。

• 逻辑分类

  • 按功能分：
    • 主键索引： 一张表只能有一个主键索引，不允许重复，不允许为NULL。
    • 唯一索引： 数据列不允许重复，允许为NULL。一张表可以有多个唯一索引。
    • 普通索引： 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许NULL。
    • 全文索引： 一个索引只包含一个列，一个表可以有多个单例索引。
  • 按列数分：
    • 单例索引： 一个索引包含一个列，一个表可以有多个单例索引。
    • 组合索引： 一个组合索引包含两个或两个以上的列。查询的时候遵循”最左前缀“原则。

• 物理分类

  • 聚簇索引： 索引和数据一起存放的索引。主键索引属于聚簇索引。
  • 非聚簇索引： 数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

聚簇索引的优点：

1）数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从局聚簇索引中获取数据比非聚簇快。

2）聚簇索引对于主键的排序和查找范围非常快。

缺点：

1）插入速度依赖于插入排序，按照主键的顺序插入是最快的方式，否则将会出现页分裂。因此，对于InnoDB表，经常定义一个自增的ID列作为主键。

2）更新主键的代价很高。

3）二级索引访问需要两次索引查找。

非聚簇索引的优点：

1）更新带价比聚簇索引要小，因为非聚簇索引叶子节点不存放数据。

缺点：

1）可能会二次查询（回表），当查询到索引对应的指针后，还需要根据指针或主键再到数据文件或表中查询。

2）依赖于有序数据。

14. 索引为什么使用B+树

索引查找的过程主要是产生磁盘I/O的消耗，主要靠磁盘IO次数，与磁盘存储原理有关。B+树除叶子节点外每个节点值保存键值，并不保存数据，所以每个节点保存的数据更多，所以树的高度更矮，所需要读取的磁盘IO次数更少。

局部性原理和磁盘预读

15. Hash索引和B+树索引的区别

• 哈希索引不支持排序，因为哈希表是无序的
• 哈希表不支持范围查找
• 哈希表不支持模糊查询以及多列索引的最左前缀匹配
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

16. 索引最左前缀匹配原则

参考资料

最左前缀是用于联合索引，从左到右依次连续使用索引，即如果对三个字段建立联合索引，如果第二个字段没有使用索引，那么就不能跳过去使用第三个索引。

17. 什么是覆盖索引

18 .索引什么时候会失效

• 当使用组合索引时，不适应最左匹配原则的话会失效

• 当进行like模糊匹配时，以%开头（如：%abc）的无法使用索引，非%开头（如abc%）的可以，相当于范围查询

• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

• 查询条件中进行运算时

• 判断索引不等于某个值时

• 查询条件使用or时

19. 数据库的三大范式

第一范式： 第一范式是最基本的范式。每个字段值都要满足原子性，即不能再分割。

第二范式： 确保数据表中的每一列都与主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）

第三范式： 确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

20. 分区和分表

• 分区： 把一个数据表的文件分成N个分区，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

  • 分区类型：
    • range分区： 把连续区间按范围划分
    • list分区： 基于某列的值从属于一个值列表或一个值。与Range区别是，range是连续区间的，而list是离散的、
    • hash分区： 随机分配，分区数固定
    • key分区： 类似hash，但只支持1列或多列，且mysql提供自身的hash函数

  优点:

  1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

  2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

  3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

  缺点：
  1、一个表最多只能有1024个分区

  2、MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

  3、如果分区字段中有主键或者唯一索引的列，那么有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

  4、分区表中无法使用外键约束

  5、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

• 分表： 把一个数据表分成N个小表。每个表对应三个文件：.MYD数据文件、.MYI索引文件、.frm表结构文件。

  • 分表类型：
    • 水平分表： 将一张表的不同数据行分为多张表，通过采用hash、取模的方式分表
    • 垂直分表： 将一张表的不同字段放到一张表上

  优点：

  减少单张表的访问压力，减少数据库的负担，缩短查询时间

区别：

分区和分表的区别

1）实现方式上： 分表是真正的分表，每个小表都是一张完整的表，都对应三个文件。而分区之后还是一张表，只是存放数据的区块变多了，即几个区块，就有几个.MYD数据文件、.MYI文件，但是只有一个.frm表结构文件。

2）数据处理上： 分表后，数据是存放在分表里，总表只是一个外壳，存储数据发生在一个一个的分表里。分区只是把数据分成许多小块，分区后的表还是一张表，数据处理还得自己完成。

3）提高性能上： 分表后，单表的并发能力提高了，磁盘的I/O性能也提高了（总表根据不同的查询，将并发压力分到小表里）。分区后，突破磁盘I/O瓶颈，提高读写能力，增加性能。

4）实现的难易程度上： 分表如果通过merge分表，则难易程度跟分区差不多，其他的话实现很麻烦。分区的实现较为简单。

联系：

1）都能提高mysql的性能，在高并发状态下都能得到效率提升。

2）分区和分表不矛盾，可以相互配合，对于访问量大的且表数据较多的表，可以采用分区和分表结合（如果采用merge方式则不行），如果单单是数据量很大，则可以采用分区。

21. 数据库连接池

学习资料

基本思想： 首先为数据库连接创建一个“缓冲池”，并且预先在池中放入一定数量的数据库连接管道，当有客户端请求连接时，就从缓冲池中取出管道，并分配给客户端，当客户端使用完后，再将管道放回到连接池。如果所有管道已经被使用，那就再新建一些数据库连接管道放入缓冲池中（新建的数量与系统设置的参数相关）。客户端可以通过“缓冲池”提供的getConnection方法获取数据库的连接，使用完毕后再通过releaseConnection将连接返回。 注： 客户端返回连接后，连接并没有被关闭，而是被连接池管理器回收，未下一个连接者做好准备。这个过程避免了频繁的向数据库申请资源，释放资源带来的损耗。

优点：

• 资源重用： 数据库的连接得到重用，不用频繁的创建，释放连接，导致系统的开销。在减少系统消耗的基础上，增进了系统环境的平稳性（减少内存碎片以级数据库临时进程、线程的数量）
• 更快的响应速度： 对于请求者而言，减少了创建、释放连接，因此响应速度更快。
• 新的资源分配手段： 对于多应⽤共享同⼀数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接技术。
• 统一的连接管理： 避免数据库连接泄露，较较为完备的数据库连接池实现中，可根据预先的连接占⽤超时设定，强制收回被占⽤的连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

22. 红黑树

红黑树与B+树的用途区别：

红黑树多用在内部排序，即全放在内存中，STL的map和set的内部实现就是红黑树。

B+树多用在外存上，B+树也成为一个磁盘友好的数据结构

为什么索引用B+树而不用红黑树？

我觉得还是因为红黑树的高度比较高，故IO次数较多，导致效率较低。

这篇关于MySQL总结的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！