MySQL总结

MySQL总结

---

目录

1. MyISAM和InnoDB区别
2. 索引
3. 事务
4. 锁机制和InnoDB锁算法

注：题目从牛客 Java部门面经整理而来。
2020秋招面经大汇总！（岗位划分）

---

1. MyISAM和InnoDB区别

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

---

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其他存储引擎。

实现了四个标准隔离级别，默认级别是可重复读。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其他存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

---

两者的对比：

1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。MySQL-InnoDB-MVCC多版本并发控制

---

2. 索引

0. 前置知识

先从 MySQL 的基本存储结构说起

MySQL的基本存储结构是页(记录都存在页里边)：

1. 各个数据页可以组成一个双向链表
2. 每个数据页中的记录又可以组成一个单向链表
3. 每个数据页都会为存储在它里边儿的记录生成一个页目录
   1. 在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
   2. 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页
2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

使用索引之后

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

1. 索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2. 索引有什么用？

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
2. 可以大大加快数据的检索速度（大大减少需要检索的数据量），这也创建创建索引的主要目的
3. 帮助服务器避免排序和临时表
4. 将随机IO变成顺序IO
5. 可以加速表和表之间的连接，特别是实现数据的参考完整性方面特别有意义

3. 索引有那么多优点，为什么不对表中的每一个列创建一个索引呢？

1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
3. 创建索引和维护索引需要耗费时间，这种时间随着数量的增加和增加

4. 索引是如果提高查询速度呢？

将无序的数据变成相对有序的数据（就像查目录一样）

5. 使用索引的注意事项

1. 在经常需要搜索的列上，可以加快搜索的速度
2. 在经常使用WHERE子句的列上面创建索引，可以加快条件的判断速度
3. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询速度
4. 对于中大型表索引都是非常有效的，但是特大型表的话维护开销很大，不适合建索引
5. 在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度
6. 避免WHERE子句中对字段施加函数，这会造成无法命中索引
7. 使用InnoDB时候使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键
8. 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描
9. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗，MySQL5.7可以通过查询sys库的chema_unused_Indexes视图来查询哪些索引从未使用
10. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

6. MySQL索引主要使用的两种数据结构

哈希索引

1. 哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。
2. 哈希索引无法用于排序和分组，只支持精确查找，无法用于部分查找和范围查找

BTree索引

1. MySQL的BTree索引使用的是B树中的B+Tree。但对于主要的两种存储引擎MyISAM和InnoDB的实现方式是不同的

7. MyISAM和InnoDB实现的BTree索引方式的区别

1. MyISAM

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

2. InnoDB

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

8. 覆盖索引介绍

1. 是什么？
   如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”

2. 有什么用？
   在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值，最终还是要通过主键再查找一次，这样就比较慢，覆盖索引就是把要查询的列和索引是对应的，不做回表操作

3. 例子
   创建索引(username,age)，在查询数据的时候：select username,age from user where username = ‘Java’ and age = 22。要查询出的列在叶子节点都存在，所以不用“回表”

9. 选择索引和编写利用这些索引的查询的3个原则

1. 单行访问是很慢的，特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立)，如果服务器从存储中读取一个数据块只为了获取其中一行，那么就浪费了很多工作，最好读取的块中能包含尽可能多需要的行，使用索引可以创建位置引，用以提升效率
2. 按顺序访问范围数据是很快的，这有两个原因
   1. 顺序I/O不需要多次磁盘寻道，所以随机I/O要快很多（特别是机械硬盘）
   2. 如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按组进行聚合计算了
3. 所以覆盖查询是很快的，如果一个索引包含了查询需要所有列，那么存储引擎就不需要再回表查找行，这避免了大量的单行访问

10. 最左前缀原则

MySQL中的对多个列建立索引，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

11. 注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

---

3. 事务

1. 是什么？

事务是逻辑上的一组操作，要么都执行，要么都不执行

2. 例子

如A给B转账1000元，涉及到两个关键操作：1. 将A账户余额减少1000元，将B账户余额增加1000元。但是万一这两个操作之间出现错误如银行系统崩溃，导致A余额减少而B余额没有增加，那么就不对了。所以事务就是保住这两个关键操作要么都成功，要么都失败。

3. 事务的4大特性

1. 原子性(Atomicity)
   事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不能完成
2. 一致性(Consistency)
   执行事务前后，数据库的完整性保存一致，多个事务对同一个数据读取的结果是相同的
3. 隔离性(Isolation)
   数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致
4. 持久性(Durability)
   一个事务被提交后，它对数据库的修改是持久的，即使数据库发送故障也不会对其有任何影响

4. 并发事务带来哪些问题？

在典型的应用程序中，多个事务并发执行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但也可能会导致以下问题

1. 脏读(Dirty read)
   当一个事务正在访问数据并对数据进行了修改，而这种修改还没有提交到数据库中，这时另一个事务也访问了这个数据，然后使用了这个数据。因为这个数据还没有提交到数据库，那么另一个事务读到的这个数据就是“脏数据”。
2. 丢失修改(Lost to modify)
   指在一个事务读取一个数据时，另一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
   例如：事务1读取某表中数据A=20，事务2也读取A=20，事务1修改A=A-1,事务2也修改A=A-1,最终的结果A=19,事务1的修改丢失
3. 不可重复读(Unrepeatableread)
   指在一个事务内多次读取同一个数据，在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。就出现了一个事务内两次读到的数据不一样的情况，因此称为不可重复读
4. 幻读(Phantom read)
   幻读和不可重复读类似，第一个事务T1读取几行数据，接着另一个事务T2插入了一些数据时，在随后的查询中，第一个事务T1就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

5. 不可重复读和幻读的区别

不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点是新增或者删除，比如多次读取一条记录发现记录增多或者减少了

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其他事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert和delete数据，所以当事务A读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现多了一条之前没有的数据，这就是幻读，不能通过行锁来避免

避免幻读需要Serializable隔离级别，读用读锁，写用写锁，读写互斥，这么做可以有效避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力

出于性能考虑，成熟的数据库读使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题

6. 事务隔离级别有哪些?

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

1. READ-UNCOMMITTED(读未提交)
   最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读、幻读
2. READ-COMMITTED(读已提交)
   允许读取并发事务已经提交的数据，可以阻止脏读，但是不可重复读和幻读仍有可能发生
3. REPEATABLE-READ(可重复读)
   对统一字段的多次读取结果是一致的，除非数据是被本身事务自己修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
4. SERIALIZABLE(串行化)
   最高的隔离级别，完成服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间完成不可能产生干扰，该级别可以防止脏读、不可重复读、幻读

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ(可重复读)，可以通过 SELECT @@tx_isolation; 命令来查看

7. 为什么InnoDB存储引擎默认隔离级别是REPEATABLE-READ?

1. InnoDB存储引擎在可重复读隔离级下使用的是Next-Key Lock锁算法，就已经可以避免幻读的产生，达到了SQL标准的SERIALIZABLE隔离级别。并且隔离级别越低，事务请求的锁越少，所以打大部分数据库系统的隔离级别都是READ-COMMITTED
2. InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE隔离级别

---

4. 锁机制和InnoDB锁算法

1. MyISAM和InnoDB存储引擎使用的锁

1. MyISAM使用的是表级锁(table-level locking)
2. InnoDB支持行级锁(row-level locking)和表级锁，默认行级锁

2. 表级锁和行级锁对比

1. 表级锁

1. 是什么？
   MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，MyISAM和InnoDB引擎都支持表级锁
2. 优点
   实现简单，资源消耗也比较少，加锁快，不会出现死锁
3. 缺点
   其锁定粒度大，触发冲突的概率最高，并发度最低，

2. 行级锁

1. 是什么？
   MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。
2. 优点
   行级锁大大减少了数据库操作的冲突，加锁粒度小，并发度高
3. 缺点
   加锁开销大，加锁慢，会出现死锁

3. InnoDB存储引擎的锁算法

1. 行锁（Record Lock）
   锁直接锁在索引记录上面

2. 间隙锁（Gap Lock）
   锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读以上的级别，解决幻读问题

3. Next-Key Lock
   行锁和间隙锁的组合，锁住记录本身，还会锁住索引之间的间隙。对于行的查询，都是使用该方法，目的是解决幻读的问题

   4. 相关知识点

   1. InnoDB对于行的查询使用next-key lock
   2. Next-Key Lock为了解决幻读问题
   3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
   4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，防止幻读问题