MySQL面试必备知识点

一、事务

用Java JDBC操作数据库时，事务都是默认提交的，相当于一条SQL语句执行之前开启事务，SQL语句执行完成后，事务再进行提交，那么有的时候，事务不仅仅代表一条SQL语句，而是代表一组做增删改查操作的SQL语句，此时事务就得保持一定的特性，否则就会导致数据库数据不一致，或者在数据库做并发操作时，导致严重的后果。

本章内容我们就来看看事务的基本概念，看看在校招中事务常常考察的两个问题：

*“事务的ACID特性是什么？”*

*“事务的隔离级别有哪些？”*

（一）概念

事务是一组SQL语句的执行，要么全部成功，要么全部失败，不能出现部分成功，部分失败的结果。保证事务执行要的原子操作；

只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库（Commit）；

如果有部分事务处理失败，那么事务就要回退到最初的状态（rollback），因此，事务要么全部执行成功，要么全部失败。

（二）ACID特性

每一个事务必须满足四个特性：原子性，一致性，隔离性，持久性。

1.原子性（Atomic）

事务是一个不可分割的整体，****事务必须具有原子特性****，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。

2.一致性（Consistency）

一个事务执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态必须****由用户来负责，由并发控制机制实现****。

例如：就拿网上购物来说，你只有让商品出库，又让商品进入顾客的购物车****才能构成一个完整的事务****！

由于并发操作带来的****数据不一致性包括读脏数据（脏读），不可重复读和虚读（幻读）****。

3.隔离性（Isolation）

当两个或者多个事务并发执行时，为了保证数据的安全性，将一个事物内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到。

例如：对任何一对事务T1和T2，对T1而言，T2要么在T1开始之前已经结束，要么在T1完成之后再开始执行。

****隔离性使得每个事务的更新在它被提交之前，对其它事务都是不可见的****。

4.持久性（Durability）

事务完成以后，数据库管理系统（DBMS）保证它对数据库中的数据的修改是永久性的，即使数据库因为故障出错，也应该能够恢复数据！

（三）隔离级别

事务处理不经隔离，并发执行 事务时通常会发生以下的问题

1.脏读（Dirty Read）

一个事务读取了另一个事务未提交的数据。

例如：当事务A和事务B并发执行时，当事务A更新后，事务B查询读取到A尚未提交的数据，此时事务A回滚，则事务B读到的数据就是无效的脏数据。

*（即：事务B读取了事务A尚 未提交 的数据）*

2.不可重复读（Non-Repeatable Read）

一个事务的操作导致另一个事务前后两次读到不同的数据。

例如：当事务A和事务B并发执行时，当事务B查询读取数据后，事务A更新操作更改事务B查询到的数据，此时事务B再次去读该数据，发现前后两次读的数据不一样。

*（事务B读取了 事务A 已提交 的数据）*

3.虚读（Phantom Read）

一个事务的操作导致另一个事务前后两次查询的结果数据量不同。

例如：当事务A和事务B并发执行时，当事务B查询读取数据后，事务A新增或者删除了一条满足事务B查询条件的记录，此时事务B再去查询，发现查询到前一次不存在的记录，或者前一次查询的一些记录不见了。

*（事务B读取了事务A 新增加 的数据或者 读不到事务A删除 的数据）*

隔离级别	脏读	不可重复读	幻读
未提交读	可以	可以	可以
已提交读	不可以	可以	可以
可重复读	不可以	不可以	可以
串行化	不可以	不可以	不可以

需注意：****事务隔离级别越高，为避免冲突所花费的性能也就越多****。

二、索引

​ 索引就像书籍的目录，当我们要搜索想看的内容的时候，先从目录搜索，然后找到内容的页数，再翻到相应的页去查看就可以了，比我们一页页翻着查找要快很多，所以索引的核心就是加快SQL的查询。

​ 当我们表中的数据量上万，几十万甚至上百万的时候，一个SQL查询所花费的时间就会很长，那么此时就需要用到索引来加速SQL查询，具体内容请看下面对索引的描述。

​ 但由于索引也是需要存储成索引文件的，因此对于索引的使用，也涉及大量的磁盘I/O操作，如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL查询效率，适得其反，因此掌握索引的执行过程和实现原理，非常重要！

（一）概念

​ 索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。

​ 优点：提高查询效率

​ 缺点：索引不是越多越好。

*过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重*

（二）分类

1. 普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引
2. 唯一性索引：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引。
3. 主键索引：使用Primary Key修饰的字段会自动创建索引
4. 单列索引：在一个字段上创建索引
5. 多列索引：在表的多个字段上创建索引
6. 全文索引：使用FULLTEXT参数可以设置全文索引，只支持CAHR，VARCHAR和TEXT类型的字段上，

（三）索引的底层实现原理

​ MySQL支持两种索引，一种的B-树索引，一种是哈希索引，大家知道，B-树和哈希表在数据查询时的效率是非常高的。

一般没有指明类型的时候，基本都是说的B-Tree索引。大多数MySQL引擎都支持这种索引，但实际上很多存储引擎使用的是B+Tree。这种索引指的是使用B-Tree或B+Tree数据结构来存储数据。

（四）聚簇索引

聚簇索引并不是单独的索引类型，而是数据存储方式。

InnoDB存储引擎的聚簇索引实际再同一结构中保存了B-Tree索引和数据行。

聚簇索引：数据行实际上存放在索引的叶子页中。

表示数据行和相邻的键值紧凑的存储在一起
所以一个表只能有一个聚簇索引
索引覆盖另算

InnoDB主键为聚簇索引
MyISAM主键是非聚簇索引

三、引擎

​ MySQL是一个关系型数据库。它诞生了许多优秀的引擎。创建一个数据库，保存为数据目录下一个子文件。而引擎就是在创建表时，MySQL在数据库子目录下保存表的定义的方式。

​ 不同的存储引擎保存数据和索引的方式是不同的。

（一）分类

1.InnoDB

2.MyISAM

3.其他引擎：Archive、Blackhole、CSV、Memory

4.第三方存储引擎：OLTP类、面向列、社区存储引擎

（二）InnoDB存储引擎

1.InnoDB的数据存储在表空间

2.InnoDB使用MVCC来支持高并发，并且实现了四个标准的隔离级别。
默认级别REPEATABLE READ（可重复读），并且通过间隙锁来防止幻读的出现

3.InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能，不过二级索引必须包含主键列。

4.磁盘读取数据时采用的是可预测性预读。

5.作为事务型存储引擎，InnoDB通过一些机制和工具支持热备份。

（三）MyISAM存储引擎

1.MyISAM将表存储到两个文件中，数据文件和索引文件，分别以.MYD和.MYI扩展名

2.MyISAM对整张表加锁，而不是针对行。
读取时对需要读取的所有表加共享锁，写入时则对表加排他锁。可并发插入。

3.修复，可手工或自动执行检查和修复操作。

4.索引，支持全文索引

5.压缩表。如果表创建并导入数据后，不会再进行修改，则适合采用MyISAM压缩表。

（四）两个引擎的区别

1. 不同的索引方式
2. InnoDB支持事务，MyISAM不支持事务
3. MyISAM不支持全文索引