MySQL面试题总结（一）

MySQL面试题（一）

索引部分

1 索引的基本原理

索引就像是书本的目录，用来快速定位具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理：把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序（根据记录的值创建索引（哈希值或者id（b树）），记录在表里（倒排表），表里还有该记录的地址）
2. 对排序结果生成倒序排序（倒排表）
3. 在倒排表内容上拼上数据地址链（数据和地址）
4. 在查询的时候，先拿到倒排表内容。再取出数据地址链。

索引类型 有 哈希的索引 还有 b树索引

2 MySQL索引的数据结构，各自优劣

索引结构有两种 哈希和B+树

mysql采用的索引数据结构和存储引擎有关系。InnoDB默认索引是B+树索引，对于哈希索引来说，底层数据结构是一张哈希表。因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快，其余大部分场景，建议选择B+树索引。

B+树是平衡多叉树，从根节点到每个叶子节点的高度差值不超过1，同层级的节点间有指针相互连接（而且是双向的），在B+树上的常规检索，从根节点到叶子节点大的搜索效率基本相当，不会有很差情况。而且基于索引的顺序扫描，也可以利用双向指针快速左右移动，效率很高。

哈希索引：

把键值换成哈希值，存储在哈希表，检索时只需要一次哈希算法就可定位到相应位置，速度很快。

如果是等值查询 哈希很快。而且前提是键值是唯一的。如果键值不是唯一的，（如果键值大量重复，还行索引也是效率极低）需要先找到该值所在位置，然后根据链表往后扫描。

范围查询哈希完全没有，范围有序的键值通过哈希后就不连续了，也不支持like这样的部分模糊查询，哈希索引也不支持多列联合索引的最左匹配规则。多个字段选择哈希索引相当于多个字段映射成一个哈希值，就没有最左匹配了。

3 索引设计的原则

使查询更快，占用空间更小

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
2. 基数较小的表，索引效果较差，没有必要在此列建立索引，表数据不多走索引效果不好。
3. 使用短索引，如果对长字符串列进行索引，应当指定一个前缀长度，这样能够节省索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
4. 不要过度索引。索引需要额外磁盘空间，降低写操作的性能（索引有S锁）。修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间越长。所以只保持需要的索引有利于查询即可。
5. 如果有外键的数据列一定要建立索引。
6. 更新频繁字段不适合创建索引。
7. 如果不能有效区分数据的列（比如性别 只有男女）区分度低，不要建索引。
8. 尽量扩展索引，不要新建索引，比如已有a索引，现在要加b索引，就建立联合索引
9. 查询中很少设计的列，重复值多的列不要建立索引。
10. 对于定义为text，image和bit的数据类型的列不要建立索引。

4 MySQL中的锁的类型有哪些

MySQL出现死锁的原因：两个或以上的进程在执行的过程中争夺资源。

表级锁不会出现死锁，行级锁会出现死锁，页级锁也会出现死锁。（行锁 比如 你并发先select 然后 update 就有问题了 插入两条相同数据 或者并发访问同一行 就会死锁）

基于锁的属性分类：共享锁、排他锁

基于锁的粒度分类：行级锁、表级锁、页级锁、记录锁、间隙锁、临键锁

基于锁的状态分类

其实锁真正的分类就是按粒度分，只是所有锁按属性角度要么共享 要么排他。

共享锁：也叫读锁，简称S锁，当一个事务为数据加上读锁以后，其他事物只能对数据加读锁，而不能加写锁，直到所有的读锁释放之后其他事务才能对其加写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。（为了支持并发读取，读的时候可以共享，不该阻塞，读读共享、读写互斥、写写互斥）

排他锁：也叫写锁，简称X锁。当一个事务为数据加上写锁时，其他请求不能再为数据加任何锁，直到该锁释放。排他锁的目的是在数据修改的时候，不允许其他人同时改动，也不允许读取，避免出现数据冲突。

---

表锁：表锁封锁整张表，当一个事务访问的时候，下一个事务访问必须等我释放锁。特点：粒度大，加锁简单，易冲突。

行锁：上锁的时候锁住一行或多行记录，其他事务可以访问同一张表非锁定的行。特点：粒度小，加锁麻烦，不易冲突，比表锁支持并发度高。

记录锁：是行锁的一种，只不过锁的范围只是一条记录。记录锁是事务在加锁后锁住的只是表的一条记录。精准条件命中，并且命中的条件字段是唯一索引。如果是普通索引，哪怕只有一条记录也不是记录锁。

加了记录锁 可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

页锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢，可以设置页锁大小。锁定相邻的一组记录。特点：开销和加锁时间介于表锁和行锁之间，会出现死锁？

间隙锁：行锁的一种。在事务加锁后锁住表记录的某一个区间。当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则（比如相邻id1,4，那么间隙锁锁2,3,4）。范围查询并且查询未命中记录，查询条件必须命中索引，间隙锁只会出现在REPEATABLE_READ（重复读）的事务级别中。 触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生下面的问题，在同一个事务里，A事务的两次查询出的结果会不一样，比如表里面数据Id为1,4,5,7,10，那么会形成以下几个间隙区间，-n到1,1-4,7-10,10-n区间。

临键索：next-key lock是行锁的一种，是innodb的行锁的默认算法，总结来说他就是记录锁和间隙锁的组合。临键锁会把查询出来记录锁住，同时把该范围查询内所有间隙空间也会锁住，再然后他会把相邻的下一个区间也锁住。

触发条件：范围查询且命中，查询命中了索引。

结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读，重复读，幻读问题，加了临键锁之后，在范围区间内数据不允许被修改和插入。

---

如果事务A加锁成功之后设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或者排他锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，这个状态就是意向锁。

• 意向共享锁：当一个事务试图对整个表进行加共享锁之前，首先需要获取这个表的意向共享锁。
• 意向排他锁：当一个事务视图对整个表进行加排他锁之前，需要获得这个表的意向排他锁。

InnoDB默认行锁。

MyISAM默认表锁。

5 MySQL事务的 基本特性 和 隔离级别

事务的基本特性：ACID

概念：

A：原子性。一个事务的全部操作要么全部生效，要么全都失败。

C：一致性。数据库总是从一个一致性的状态转换到另一个一致性的状态。如果A只有90块，这时你让他转100块给B，支付之前我们数据库数据都是符合约束的，但是如果事务执行成功了，-10会破坏数据库约束规则，因此这个事务不能成功。

I：隔离性。一个事务的修改在最终提交前，对其他事务是不可见的。

D：持久性。一旦事务提交，所做的修改就会永久保存在数据库中。

一致性是我们的目的。通过原子性，隔离性和持久性来达到一致性的目的。

---

隔离级别：

隔离性有4个隔离级别，分别是：

• read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫作脏读。
• read commit 读已提交，两次读取结果不一致，叫做不可重复读。数据必须提交之后才能读取。不可重复读解决脏读的问题，只会读取已经提交的事务。但是可能前后两次读，读到不一样数据。在同一个事务里查询读到不同的结果 叫不可重复读。（比如，A提交x=500，然后B读取500，这时C对数据进行操作X=200，然后B第二次读取为200，产生数据冲突。）
• repeatable read 可重复读，（是读已提交之上的隔离级别，读到的一定是提交的数据）这是MYSQL的默认级别，就是每次读取结果都一样，但是有可能产生幻读。（如果A 令X=500, C读500，后面B把x改为200，C读还是500）在C的事务期间，别的事务更改了值，他不关心。
• serializable 串行。一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争问题。
• MYSQL在使用rc和ru级别时采用的是MVCC机制（多版本并发控制），会生成一个read view。可重复读rr不会重新生成read view。
• 幻读：读取范围时，读取1-10 5条数据，生成read view，每次读取都采用read view 如果此时有新数据的增加，第6条数据出现。
• 脏读：某个事务更新一个数据，另一个事务在此时同时读取一份数据，由于某些原因，前一个回滚了啊哦做，后一个事务读取的数据就是不正确的。
• 不可重复读：一个事务在两次查询中数据不一致。
• 幻读：一个事务两次查询中数据行数不一致。一个事务查询几行数据，另一个事务此时插入了新的几列数据，先前的事务在接下来的查询中，会发现有几行数据是他先前所没有的。

6 ACID是如何保证的

A原子性由undo log保证，他记录了所有需要修改数据的信息 以供回滚的日志信息，事务回滚时撤销已经执行成功的SQL。

C一致性由其他三大特性保证。程序代码要保证业务上的一致性。

I隔离性有MVCC保证。

D持久性由内存+redo log来保证。MYSQL修改数据的同时在内存和redo log 记录这次操作，宕机的时候可以从redo log恢复。

InnoDB redo log 写盘，InnoDB事务进入prepare状态，如果前面prepare成功，binlog写盘，再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB事务则进入commit状态（在redo log 里面写一个commit记录）（事务成功确认： 一个是redo log要commit记录 ）

redo log的刷盘在系统空闲时进行。

主从同步通过binlog

以上资料多为看b站视频做的笔记，大部分和视频内容一致，因为自己也在学习，很多知识点不清楚具体原理，也不知道是否正确，欢迎指正。