MySQL 面试题 011 - 020

011、什么是快照读和当前读？

不加锁的 select 操作就是快照读（即不加锁的非阻塞读），它读取的是事务开始后首次快照读的版本，有可能不是最新版本，基于 MVCC 实现，避免了加锁操作，降低了开销。但快照读有一个前提，隔离级别不能是SERIALIZABLE(可串行化)，否则也会成为当前读。

像 update，delete，insert，select lock in share mode(共享锁)，select for update 这些操作都是当前读，读取的是记录的最新版本，会对读取的记录进行加锁，从而保证其他并发事务不能修改当前记录。

012、聚集索引与非聚集索引？

聚集索引是指索引结构和数据存放在一起的索引，聚集索引的查询速度非常快，定位到索引的节点，就相当于定位到了数据，主键索引属于聚集索引。聚集索引也有缺点，必须依赖于有序的数据，并且更新代价大。

非聚集索引指的是索引结构和数据分开存放的索引，唯一索引，普通索引，前缀索引都属于非聚集索引。

下图的辅助索引指的就是唯一索引，普通索引，前缀索引等。

MyISAM和InnoDB实现B+树索引方式的区别？

这道题就是把上图用自己的话描述一遍，参考答案如下：

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB

其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主键索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主键索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主键索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

013、索引都有哪几种？

主键索引(Primary Key)：一张数据表只能有一个主键索引，并且值不能为 null，不可重复。

唯一索引(Unique Key)：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index)：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

联合索引

014、联合索引最左匹配原则？

下图的第三行就是一个联合索引。

015、索引是建立的越多越好吗？

• 数据量小的时候全表查询和索引查询效率区别不大，但建立索引会增加额外的开销，所以数据量小的时候没必要用索引。
• 数据变更需要维护索引，索引太多意味着更高的维护成本。
• 索引是需要占用空间的，索引越多意味着需要越大的空间。

016、InnoDB 在 RR 隔离级别下是如何避免幻读的？

InnoDB 存储引擎在 RR 下使用的是 Next-Key锁（行锁 + Gap锁）。

Gap锁就是间隙锁，间隙锁会锁定一个范围，在这个范围内的所有 insert 操作都会被阻塞，但其他操作不会。

对主键索引或者唯一索引会用 Gap 锁吗？

• 如果 where 条件全部命中，则不会使用 Gap 锁，只会加行锁。
• 如果 where 条件部分命中或者全未命中则会同时使用 Gap 锁和行锁。

Gap锁一般情况下用在哪里？

Gap锁通常用在普通索引（非唯一索引）或者不走索引的当前读中，如下图：

变相问答：如下图，如果进行 where c = 10 的操作，除了会对这一行加行锁外，还会有什么操作？

答：还会在该行记录前后加上间隙锁，因为只有行锁的话是无法阻止另一个 c = 10 的数据的加入的，加上间隙锁就可以阻塞 insert 操作，防止幻读。

017、索引优化需要注意？

• 经常被查询且区分度高的列做索引

• 联合索引最左匹配原则

  

• 回盘排序

  排序列（order by）和条件列建立一个联合索引

• 覆盖索引

  如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键值+当前索引值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！一般在联合索引中用的比较多。

• 小表驱动大表

018、MySQL 为表添加索引的写法？

1.添加PRIMARY KEY(主键索引)

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加联合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

019、关系型数据库的六大范式？

第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。一般说来，数据库只需满足第三范式(3NF）就行了。

1NF：字段不可再分。

2NF：在 1NF 的基础上，再满足有主键，非主键字段依赖主键。

3NF：在 2NF 的基础上，再满足非主键字段不能相互依赖。

020、优化思路？

在数据库设计层面，要允许适当的数据冗余。

然后就是进行索引优化，索引优化需要注意以下一些小细节：

• 尽量使用主键查询而不是其他索引，因为主键查询不会有回表操作

• 如果使用到排序的话，要让排序列（order by）和条件列建立一个联合索引，这样子就不用进行回表操作了

• 如果要查询的字段不是很多的话，尽量考虑覆盖索引，同样可以减少回表次数

• 在创建联合索引的时候要遵循最左匹配原则

• 要使用经常被查询且数据区分度高的列做索引，对于经常修改且数据区分度不高的列不宜建立索引，索引列尽量不要为空

• 索引不是越多越好，毕竟维护索引也是需要耗费时间和空间的，对于单表来说尽量控制在 5 个以内；联合索引所包含的字段也不要超过 5 个

• 在进行关联查询的时候尽量使用小表驱动大表

• 使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层面，不要放在数据库层面面

  select id from actor where id = 4;
  select id from actor where id + 1 = 5;
  

• 不要过早优化，一般是碰到问题的时候才进行优化

什么是回表？

使用普通索引、唯一索引查询的时候，直接查出来的不是数据，而是主键值，需要根据主键值回表再次查询才能查到具体数据。

什么是索引下推？

在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。

假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%'

根据 “最左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。

问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

不使用索引下推的话，会先使用联合索引查出符合条件的 name，然后拿着查出来的主键值回表查询所有数据再过滤掉和 age 不匹配的数据；使用索引下推的会在使用联合索引查询的时候直接过滤掉不符合条件的 age，可以有效地减少回表次数，大大提升了查询效率。