【面试题笔记-Java】MySQL数据库、索引、MVCC等知识点(自己整理)

MySQL

1.MySQL的索引

索引就是帮助MySQL高效获取数据排好序的数据结构

索引数据结构：

• 二叉树
• 红黑树
• hash表
• B树

1、单指索引一个索引只包含单个列，一个表可以有多个单列索引

2、唯一索引索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3、主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

4、组合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

5、全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

存储引擎级别是表

1.什么情况要创建索引?

主键自动建立唯一索引

频繁作查询条件的字段要创建索引

查询中与其他表关联的字段,外键关系建立索引

查询中排序的字段 order by

查询中统计或者分组的段 group by

2.什么情况不创建索引

频繁更新的字段不适给创建索引

where条件里用不到的字段不创建索引

表记录太少

数据重复且分布平均的表字段不适合创建索引

3.单值索引和组合索引的选择问题?

高并发建议组合索引(过滤性最好的字段排在前面)

4.避免索引失效

最佳左前缀法则

不要在索引上做任何操作,否则会失效

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引(只查询索引列),减少使用select *

!= <>无法使用索引会导致全表扫描

is null ,is not null也无法使用索引

like ‘%abc…’ 索引失效会导致全表扫描

字符串不加单引号索引失效

用or,索引失效，如果or前后都是索引列不会失效

5.查询优化

①小表驱动大表

②order by子句尽量避免FileSort ,order by子句使用索引最左前列,或者where子句与order by子句条件列组合满足索引最左前列

③group by:先排序后分组，遵照最佳左前缀，where高于having, 尽在where中限定条件

2.存储引擎

MyISAM和InnoDB区别

myisam:表锁，只缓存索引

innoDB:支持主外键，支持事务，行锁，缓存索引和真实数据

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

两者的对比：

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实现并不统一。

3.索引

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

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

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

4.为什么InnoDB底层使用B树而不使用Hash？

hash是直接存地址值，在某种程度上来说查找速度比B树还要快。但是如果查范围，例如id>20，就不行了，而B+树只要找到20，然后往后拿数据就行

5.为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?

在InnoDB中，他的底层是用B+实现的，必须要有查找索引，如果没有，底层会自动帮你从左往右进行每列查找，将没有重复的列自动作为索引，如果没有不重复的列，就要自动创建一列。首先，推荐整形的主键是因为在索引查找中，能够实现较快的查找，然后就是因为B+树的结构了，本来按顺序自增，就是在后面加，很少发生树结构的改变，但是如果直接插入，会导致树结构改变，开销很大，影响insert语句。id用来做聚簇索引，插入的时候可以顺序插入，如果随机数，会导致也分裂

6.联合索引的底层存储结构

底层使用B+树，排序使用从左往右排序

如果联合索引’a’,‘b’,‘c’，就要只能查带’a’的

从底层数据结构原理来解释，只有有第一个字段在，才能查找叶子结点的数据，不然在宏观上来看，是没有排序过的

7.事务

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

8.sql执行过长的时间，如何优化

9.创建索引原则

10.事务隔离级别有哪些?MySQL的默认隔离级别是?

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

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f6zUPEVZ-1597764069480)(D:\学习笔记\面试题复习\imgs\MySQL\隔离级别与读.png)]

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

总结：

1.读未提交：脏读、不可重复读、幻读

2.读已提交：不可重复度、幻读

3.可重复读：幻读

4.串行化：隔离级别最高

脏读：脏读是读到了bai别的事务回滚前的脏数据。比如事务B执行过程中修zhi改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读

11.谈谈你对MVCC的理解

MVCC就是多版本并发控制，通过行级锁的变种，避免很多不必要情况下发生加锁，减少开销。其基本思想是为每次事务生成一个新版本的数据，在读数据时，选择不同版本的数据即可实现对事务结果的完整性读取。同时MVCC只支持读取已提交和可重复提交的隔离级别。

a.是什么? MVCC即眵个不同版本的数据实现并发控制的技术，基本思想是为每次务生成一个新版本的数据， 在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。

b.作用?提高并发的读写性能

操作的时候会生成事务id

①每条记录都会保存两个隐藏列: trx_ id (事务id)和roll pointer(回滚指针)2个字段

②每次操作都会生成一条undo log日志,回滚指针指向前一条记录

查询的时候会读取出ReadView:[未提交的事务id]数组+最大事务id,并根据readview从undo log日志中最新的记录依次往下找

<1>从最新记录开始找:

如果当前记录:事务id<未提交事务的最小id，则可读

如果当前记录:最小id< =事务id< =事务的最大id,则判断事务id是否在未提交事务id的数组中，若在则不可读(只有自己可读)

如果当前记录:事务id>事务的最大id,则不可读

<2>可重复读返回的read-view是第一记录的，记提交每次查询都返回新的read-view

MVCC只针对读E提交和可重复读,如果读未提交,每次查询都取最新的记录即可。

12.MySQL为什么使用B+树做索引

1.全部遍历，时间复杂度O(N)

2.hash，优点：增删改查快，O（1），缺点，不能进行范围查询

3.二叉树：O(log2N)深度为N的结点，如果数据极端，会形成一条链表

4.平衡二叉树（AVL）：数据越大树越高，IO收到影响

5.B树：每个结点可以有多个子节点，可以控制书高，IO次数比平衡二叉树少，但是数据存储在索引上，无法加载大量数据

6.B+树：每个结点只存储索引，数据都放在叶子结点，叶子结点链表相连。最好使用整形递增索引，保证连接顺序

13.分表、分库、分区

MySQL数据量过大，考虑按业务进行垂直拆分

如果单表容量超过500W就要水平拆分

分库，通常都是一主多从，如果并发量大，就需要多个主库，进行MySQL集群访问

分区：分区查询使用 SHOW VARIABLES LIKE ‘%partition%’

range 分区，list分区

14.行表锁

1.查询锁

mysql>show open tables

2.读锁

共享锁，lock table 表名 read

3.写锁

独占锁，lock table 表名 write

4.表锁偏读，行锁偏写

5.表锁分析

①读锁示例: session1加表1的读锁

session2可读,不能改(排队等待)

session1不可改，不可读其他表

②写锁示例: session1加表1的写锁

session1可读写操作

session2不能读(排队等待)

③如何分析表锁锭

sq|: show status like ‘table%’;

table_ locks_ immediate:产生表级锁定的次数

table_ locks_waited:出现表级争用而发生等待的次数

总结:读锁会阻塞写但不会阻塞读,写锁会把读写都阻塞

15.MySQL的主从复制

slave会从master读取binlog来进行数据同步，slave将master的binlog拷贝到它的中继日志。 mysql的复制是异步且串行化的。

16.索引的最左匹配原则

最佳左前缀原则，按索引的顺序进行匹配，包括order by 和group by