MySQL面经

事务

什么是事务？

事务是用于处理操作量大、复杂度高的数据。比如在人员管理系统中，要删除一个人员的信息，不仅要删除他的基本资料信息，还要删除与之相关的信息，比如说他的文章、邮箱等等。这些数据库操作语句就构成了一个事务。
在MySQL中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。事务需要注意以下几个特点：

• 在MySQL中，只有使用InnoDB存储引擎的数据库或表才支持事务。
• 事务处理用来保证数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
• 事务用来管理update、insert和delete操作。

事务ACID是什么？

一般来说，事务必须满足四个条件（ACID），如下：

• 原子性（Atomicity）：也叫做不可分割性，一个事务（transaction）的所有操作，要么全部完成，要么全部不完成，不会结束在中间状态的某个环节。事务在大部分情况下执行失败，就会被回滚（rollback）到事务开始之前的状态，好像这个事务就没有执行过一样。
• 一致性（Consistency）：在事务开始之前和事务结束之后，数据库的完整性不会被破坏。这就需要加入的资料符合预设的规则，包括资料的精确度、串联性和后续数据库能够自发性地完成预设的执行。
• 隔离性（Isolation）：数据库支持大量并发的事务对其数据进行修改或读写操作，隔离性防止并发的事务在交替执行时导致数据不一致的情况。事务隔离分为不同的级别，分别为：读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）和可串行化（serializable）。
• 持久性（Durability）：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。

在MySQL中命令行的默认配置下，事务都是自动提交的，即执行完SQL语句后就自动执行commit操作。因此要显式地开启一个事务需要使用命令BEGIN或START TRANSACTION或执行命令SET AUTOCOMMIT = 0，用来禁止当前会话的自动提交。

Spring事务失效的可能原因？

1. Service类内部方法调用。
2. 异常处理。
3. 事务显式提交。

事务并发会带来哪些问题？

之前介绍过事务的ACID，其中隔离性强调数据库支持大量并发事务对其数据进行修改或者读写操作，在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务，也就是多个用户对同一数据进行操作。并发虽然是必须的，但是可能会带来以下问题：

• 脏读：当一个事务正在访问数据并对其进行了修改，但是还未提交事务时，另外一个事务也访问了该数据，并使用了该数据，由于数据的修改并未上传到数据库，所以第二个事务访问到的数据是“脏数据”，这种行为被称为“脏读”，依据“脏数据”所做的操作可能是会出现问题的。
• 不可重复读：指在一个事务内多次读取同一数据，在这个事务还未结束时，另外一个事务也访问了该数据并对其进行了修改，那么就可能导致第一个事务两次读取到的数据不一致，这种情况就被称为“不可重复读”。
• 幻读：指在一个事务内读取多行数据，在这个事务还未结束时，另外一个事务插入或删除了一些数据，在随后的查询中，第一个事务读取到的数据就比原本读取到的多或少，就像出现了幻觉一样，所以称为幻读。

不可重复读和幻读的区别？

总的来说，不可重复读的重点是修改，幻读的重点是删除或新增。以下用具体的例子来说明。

• 不可重复读：同样的条件下，读取过的数据，再次读取的时候不一样了。事务1中的A先生读取自己的工资是1000元的操作还没有结束，事务2的B先生将A先生的工资修改为了2000元，那么当A先生再次读取工资的时候就变成了2000元，这就是不可重复读。
• 幻读：同样的条件下，第1次和第2次读取到的记录数不一样了。假设某工资表中工资大于3000的有4人，事务1多次读取了所有工资大于3000的记录数，第一次查询到了4条记录，该操作尚未结束。这时事务2向工资表里又插入了一条工资大于3000的记录，那么事务1再次读取时查询到的记录就是5条了，这就是幻读。

事务的四个隔离级别是什么？

SQL标准定义了四个隔离级别：

• 读未提交（read uncommitted）：最低的隔离级别，允许读取事务尚未提交的数据变更，可能会造成脏读、不可重复读和幻读。
• 读已提交（read committed）：允许读取事务已经提交的数据，可以避免脏读，但可能导致不可重复读和幻读。
• 可重复读（repeatable read）：对同一字段多次读取的结果是一致的，除非事务本身进行修改，可以避免脏读和不可重复读，但可能导致幻读。
• 可串行化（Serializable）：最高的隔离级别，完全服从ACID的隔离级别，所有的事务依次执行，可以避免脏读、不可重复读和幻读。

索引

MySQL中索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构。索引的建立对于MySQL的高效运行是很重要的，因为索引可以大大提高MySQL的检索速度。索引是占据物理空间的，在不同的存储引擎中，索引存在的文件也不同。

索引有哪些分类？

1. 主键索引：一张表只能有一个主键索引，不允许重复、不允许为NULL值，但是主键可以有多个列。
2. 唯一索引：一张表可以有多个唯一索引，索引列的值必须唯一，允许为NULL值。如果是联合索引，则列值的组合必须唯一。
3. 普通索引：基本的索引类型，一张表可以创建多个普通索引，允许数据重复，没有唯一性的限制，允许NULL值插入。
4. 全文索引：全文索引的类型为FULLTEXT，全文索引可以在char、varchar和text类型的列上创建，全文索引查找的是文本中的关键词，主要用于全文检索。
5. 联合索引：一个联合索引包含两个或两个以上的列。查询的时候遵循MySQL联合索引的“最左前缀”原则，即使用where时条件要按照建立索引时字段的排列方式放置，索引才会生效。

索引的数据结构有哪些？

MySQL中常用的索引数据结构有：B-Tree索引、B+Tree索引和Hash索引等。
B-Tree索引
B-树是专门为外部存储器设计的一种平衡的多路查找树，它类似于普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点，B-树具有以下特点：

1. 所有键值分布在整棵树中，索引值和具体数据都在每个节点中。
2. 所有索引元素不重复，搜索有可能在非叶子节点结束，性能逼近二分查找。
3. 叶子节点具有相同的高度，叶子节点的指针为空。
4. 节点的索引从左到右依次递增。

B+Tree索引
B+树是B-树的变体，也是一种多路搜索树，在MySQL中索引默认使用这种结构。

1. 非叶子节点不存储数据，只存储冗余索引，可以在不增加树高度的情况下，添加更多的分支从而存放更多的索引。
2. 叶子节点包含所有索引字段和具体数据。
3. 叶子节点用指针连接，在进行范围查找时，能提高区间访问的性能。

参考博客：link

Hash索引

1. Hash索引就是采用一定的Hash算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次Hash算法即可立刻定位到相应的位置，速度非常快。
2. Hash索引仅仅能满足"=","In","<=>"查询，不支持任何范围查询，例如where price > 100，这是因为经过相应的Hash算法处理之后Hash值的大小关系，并不能保证和Hash运算前一致。
3. 如果某一个索引位置上存在多个元素，那么它们就以链表的形式来存储，这是处理哈希冲突的一种方式。

B-Tree和B+Tree的区别

1. B+树非叶子节点不存储数据，所有数据存储在叶子节点导致查询时间固定为O(logn)；而B-树查询时间复杂度不固定，与索引值在树中的位置有关，最好的情况下时间复杂度为O(1)。
2. B+树叶子节点两两相连可大大增加区间访问性，可使用在范围查询等场合；而B-树每个节点索引值和数据在一起，无法进行区间查找。
3. B+树更适合外部存储，由于非叶子节点不存储数据，每个节点能索引的范围更大更精确。

参考博客：link

MySQL为什么使用B+Tree而不选用B-Tree或者红黑树作为索引的数据结构？

红黑树是一种传统的用来搜索的平衡二叉树，这种树在一般情况下查询性能非常好，但当数据量非常大的时候，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中，而磁盘读取的时间远远超过了数据在内存中的比较时间，就会导致程序大部分时间会阻塞在磁盘I/O上，而索引的效率依赖于磁盘 I/O 的次数，红黑树这类平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分数据加载到内存中则无法完成旋转操作，而且平衡二叉树的高度相对较大，这样的话，逻辑上很近的节点在物理上也会相隔较远，无法很好地利用磁盘预读（局部性原理），所以诸如红黑树这类平衡二叉树在数据库和文件系统上就被pass掉了。
快速索引需要有效的减少磁盘 I/O 次数，B+Tree和B-Tree的比较主要集中在索引的效率上：

1. B+树叶子节点两两相连的特点使得B+树可以很好的利用局部性原理，利用磁盘提前将这些数据读入内存，减少了磁盘 I/O 的次数。
2. 由于B-树的所有节点都存了索引值和数据，而B+树只有叶子节点存放了数据，非叶子节点只存放了索引值，这就使得B+树在一次磁盘I/O当中能读出的索引值更多，从而减少查询时候需要的I/O次数。
3. 还有一点就是B-树查询的时间复杂度也不固定，与索引在树中的位置有关。
   以上三点其实也就是B-Tree和B+Tree的主要区别。

参考博客：link

聚簇索引和非聚簇索引的区别？

索引从物理上可以分为聚簇索引和非聚簇索引，有时也称为辅助索引或二级索引。聚簇是为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块。
聚簇索引
聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树并且B+树的叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为，B+树叶子节点存放的是整行数据记录和索引值，找到索引也就找到了数据。
非聚簇索引
数据和索引是分开的，可以理解为，B+树叶子节点存放的是指向实际记录行的指针和索引值。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

存储引擎

简单介绍一下MySQL的存储引擎及特性？

• MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，也就是说存储引擎是基于表的，但并不是所有的关系型数据库都有存储引擎这个概念，其他大多数数据库系统仅支持一种类型的数据存储。MySQL默认配置了许多不同的存储引擎，例如，Innodb、MyISAM、CSV、Memory和Archive等，可以根据业务需求选取一种最适配最高效的存储引擎。目前 MySQL常用的两种存储引擎是InnoDB和MyISAM。
• MySQL对于多引擎有很好的兼容，一个数据库服务器上不同的数据库完全可以使用不同的数据引擎，甚至一个数据库中的多个表也可以使用不同的引擎。

简单介绍一下InnoDB和MyISAM？

InnoDB是MySQL 5.5版本及之后MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成大量的短期事务，短期事务大部分情况下是可以正常提交的，很少被回滚。InnoDB自动崩溃恢复的特性使得它在非事务存储需求中也很流行，因此，除非有非常特别的原因需要使用其他存储引擎，否则就优先考虑InnoDB引擎。而MyISAM是MySQL 5.5版本之前的默认引擎，虽然后来被InnoDB所取代，MyISAM仍因其简单性、高性能以及对全文搜索的支持而被广泛使用。

InnoDB和MyISAM有什么区别？

1. 事务支持：InnoDB支持事务，具有事务提交（commit）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事务安全（transaction-safe）型表，对于 InnoDB 每一条 SQL 语句都默认封装成事务进行提交，这样就会影响速度，优化速度的方式是将多条 SQL 语句放在 begin 和 commit 之间，组成一个事务去提交。而MyISAM是非事务安全型的，强调的是性能，每次查询具有原子性，其执行速度比InnoDB更快，但是不提供事务支持。
2. 索引结构：InnoDB和MyISAM所使用的索引的数据结构虽然都是B+树，但是区别在于：

• InnoDB 中 B+ 树存储的都是实际的数据，这种索引被称为聚簇索引。
• MyISAM 中 B+ 树存储的内容是实际数据的地址值，它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚簇索引。

3. 外键支持：InnoDB支持外键约束，MyISAM不支持外键约束。
4. 表锁差异：InnoDB支持行级锁，行锁大幅度地提高了多用户并发操作的性能。MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁。

MySQL的锁是计算机协调多个进程或线程并发访问某一资源的机制。
表级锁：每次操作锁住整张表。开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度低。
行级锁：每次操作锁住一行数据。开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

5. 存储结构：InnoDB引擎下的所有表都保存在同一数据文件中，也可能是多个文件，或者是独立的表空间文件，InnoDB表的大小只受限于操作系统文件的大小；每个MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，.MYD (MYData）文件存储数据，.MYI (MYIndex)文件存储索引。
6. 存储空间：InnoDB需要更多的存储空间，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引；MyISAM可被压缩，存储空间较小。支持三种不同的存储格式：静态表、动态表和压缩表，当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
7. 全文索引：InnoDB在MySQL 5.5版本之前不支持全文索引，5.5版本之后支持全文索引。MyISAM支持全文索引。