金九银十，金三银四（上）

大家好，我是小瑄

所谓金九银十，金三银四，眼看十月份最后一天已经快要结束了，当初那个充满斗志，充满梦想的你在哪里？

错过了现在，那就好好准备未来吧。

目录

• 事务的四大特性？
• 数据库的三大范式
• 事务隔离级别有哪些？
• 索引

什么是索引？

索引的优缺点？

索引的作用？

什么情况下需要建索引？

什么情况下不建索引？

索引的数据结构

Hash索引和B+树索引的区别？

为什么B+树比B树更适合实现数据库索引？

索引有什么分类？

什么是最左匹配原则？

什么是聚集索引？

什么是覆盖索引？

索引的设计原则？

索引什么时候会失效？

什么是前缀索引？

• 常见的存储引擎有哪些？
• MyISAM和InnoDB的区别？
• MVCC ？
• 快照读和当前读
• 共享锁和排他锁

事务的四大特性：

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
• 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
• 隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库的三大范式

第一范式1NF

确保数据库表字段的原子性。

比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。

第二范式2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子

假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

可以拆分成三个表：

学生：student(stuent_no, student_name, 年龄)；

课程：course(course_name,credit)；

选课关系：student_course_relation(student_no, course_name, grade)。

第三范式3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为

Student(student_no,student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：

学生：(student_no, student_name, age, academy_id)；

学院：(academy_id, academy_telephone)。

2NF和3NF的区别？

• 2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
• 3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。

• 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
• 不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
• 幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL数据库为我们提供的四种隔离级别：

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
• Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

索引

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

索引的优缺点？

优点：

• 加快数据查找的速度
• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
• 加快表与表之间的连接

缺点：

• 建立索引需要占用物理空间
• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

什么情况下需要建索引？

1. 经常用于查询的字段
2. 经常用于连接的字段建立索引，可以加快连接的速度
3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不建索引？

1. where条件中用不到的字段不适合建立索引
2. 表记录较少
3. 需要经常增删改
4. 参与列计算的列不适合建索引
5. 区分度不高的字段不适合建立索引，如性别等

索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

Hash索引和B+树索引的区别？

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

为什么B+树比B树更适合实现数据库索引？

• 由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。
• B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
• B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引有什么分类？

1、主键索引：名为primary的唯一非空索引，不允许有空值。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。

对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。直接执行b = 2这种查询条件无法使用索引。

当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a = 2时候，b的值为1，4也是有序状态。当执行a = 1 and b = 2时a和b字段能用到索引。而执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段无法使用索引。

什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain select blog_id from user_like where user_id = 13;

explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explain select user_id from user_like where blog_id = 1;

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

索引的设计原则？

索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

利用最左前缀原则。

索引什么时候会失效？

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
• 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
• 判断索引列是否不等于某个值时
• 对索引列进行运算
• 查询条件使用or连接，也会导致索引失效

什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

建立前缀索引的方式：

// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是：MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

优点：访问速度快。

缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE存储引擎

ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

MyISAM和InnoDB的区别？

是否支持行级锁 : MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。

是否支持事务和崩溃后的安全恢复：MyISAM 不提供事务支持。而InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。

是否支持外键：MyISAM不支持，而InnoDB支持。

是否支持MVCC ：MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。

MyISAM不支持聚集索引，InnoDB支持聚集索引。

MVCC ？

主要从以下几点去考虑，

1、什么是MVCC？

2、MVCC用来解决什么问题？

3、MVCC是怎么实现的？

所谓MVCC，在mysql中指的是multi version concurrency control，即多版本并发控制。多版本比较好理解就是有多个版本，那么是指的什么有多个版本，这里指的是数据行，mysql中的数据行有多个版本，再看后面的并发控制，即对数据的行的读取和更新要并发控制，并发控制的目的是为了多线程下的数据安全，就像在java环境下的多线程安全，这里并不是指线程安全，而是指多个线程下的数据隔离级别。

MVCC只有在读已提交和可重复读两种隔离级别下才有效。我们都知道在读已提交隔离级别下解决了脏读，但存在不可重复读及幻读的情况，在可重复读隔离级别下解决了不可重复读和幻读（如何解决的下篇文章分享），下面就看下在这两个隔离级别下MVCC是如何其作用的。

MVCC的实现是通过undo log和read view来实现的。

在innodb引擎下的表，每个数据行都有隐藏的两列，一列是trx_id，也就是更新（insert、update、delete）这条记录的事务ID；一列是roll_pointer，指向上次修改的指针，如果是新增的则为null；如果不存在主键的话，还会有第三列row_id，在没有主键的情况下默认生成的主键；

我们都知道在mysql的事务日志中有redo log和undo log，redo log记录的是真实改变的值，而undo log记录的是和操作相反的操作，由于一条记录可能会被修改多次，这些修改连在一起就形成了一个版本链，这个版本链就是MVCC实现的基础。

如下就是一个版本链：

其中最后两列一个是trx_id，一个是roll_pointer。有了版本链，还有一个read view，看这是什么概念，翻译过来叫一致性视图，一致性视图中有以下几个属性比较重要：

m_ids，在生成read view时当前活跃的读写事务的列表

min_trx_id，m_ids中最小的

max_trx_id，m_ids中最大的+1

版本链中的trx_id是否对当前事务可见通过以下的规则进行判断，

trx_id<min_trx_id 表示数据中的事务id比当前活跃的事务id最小的还小，代表该记录在生成readview的时候已经提交，那么是可见的；

trx_id>=max_trx_id 表示数据中的事务ID比当前活跃的事务id最大的还大，代表该记录在生成readview后提交的，那么是不可见的；

min_trx_id<=trx_id<max_trx_id 当trx_id在m_ids中表示，该事务还未提交，那么是不可见的；当trx_id不在m_ids中，说明已经提交了，那么是可见的；

如果某个版本的数据对当前事务是不可见的，那么就要顺着版本链继续查找下个版本，直到找到可见的版本。

那么在读已提交和可重复读下是如何实现的，在读已提交下，是每次select都会生成read view，所以可以读到提交的数据；在可重复读隔离级别下，是在第一次select的时候生成read view，以后的select都是使用第一次生成的read view，所以解决了不可重复读。

快照读和当前读

表记录有两种读取方式。

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

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

那么MySQL是如何避免幻读？

在快照读情况下，MySQL通过mvcc来避免幻读。

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

next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。