吃透『MySQL』系列，手把手解析相关问题

1、 三大范式？

第一范式：字段具有原子性，不可再分（字段单一职责）

第二范式：满足第一范式，每行应该被唯一区分，加一列存放每行的唯一标识符，称为主键（都要依赖主键）

第三范式：满足一二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖-不存在其他表的非主键信息）

「范式优点与缺点」：

「优点」：范式化，重复冗余数据少，更新快，修改少，查询时更少的distinct。

「缺点」：因为一个表不存在冗余重复数据， 「查询可能造成很多关联」 ，效率变低，可能使一些 「索引策略无效」 ，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引。

2、InnoDB与MyISAM的区别？

2.1、MyISAM与InnoDB区别

1. InnoDB聚簇索引，MyISAM非聚簇索引

2. InnoDB数据与索引一起保存.ibd，MyISAM表结构.frm 索引.myi 数据.myd

3. InnoDB支持事务、外键、行锁表锁，MyISAM不支持事务、外键、只支持表锁

4. select count(*)

5. MyISAM查询更优，InnoDB更新更优

6. 都是B+tree索引

7. MyISAM支持全文索引，InnoDB5.6后支持

2.2、MyISAM

1. 不支持事务，但是每次查询都是原子的

2. 支持表级锁，每次操作对整个表加锁

3. 存储表的总行数

4. 一个MyISAM表有三个文件：表结构.frm 索引.myi 数据 .myd

5. 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

2.3、InnoDB

1. 支持ACID事务，支持四种隔离级别

2. 支持行级锁及外键约束，因此支持写并发

3. 不存储总行数

4. 主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引;最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

2.4、使用场景

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意

MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。

1. MyISAM适合读多更新少的：MyISAM索引跟数据分开放，因此有读取更快的说法。

2. InnoDB适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高

3. 需要事务，高并发场景用InnoDB：InnoDB支持事务，采用行锁

4. MyISAM查询比InnoDB快，更新InnoDB快

「场景」：MyISAM查询更优，InnoDB更新更优。

MyISAM适合读多，更新少的场景。MyISAM使用非聚簇索引，数据和索引分开存的，而InnoDB数据和索引存一起的；

数据量大时，一个内存页大小固定，读进内存的数据MyISAM就多一点(数据量小看不出差距，数据量大时差距就明显）。

因为MyISAM只把索引指针读进内存，可以存更多，查询速度也就更快，而且InnoDB还需要维护其他数据，比如其他隐藏字段 row_id tx_id等。

3、自增主键理解？

自增主键

InnoDB引擎的自增值，其实是 「保存在了内存」 里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：（查看表结构，会看到自增主键=多少）。

• 在MySQL 5.7及之前的版本， 「自增值保存在内存里」 ，并没有持久化。每次重启后，第一次打开表的时候，都会去找 「自增值的最大值」

  max(id)，然后将max(id)+1作为这个表当前的自增值。

❝

举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

❞

• 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段。

2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

自增值新增机制

1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；

2. 否则，自增值不变。

4、为什么自增主键不连续

1. 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化

2. 事务 「回滚」 （自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突）

3. 唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）

「eg：」

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1); (自增id,唯一键c,普通字段d)

这个语句的执行流程就是：

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);

2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；

3. 将传入的行的值改成(2,1,1);

4. 将表的自增值改成3；

5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

所以，在这之后，再插入新的数据行时，拿到的自增id就是3。 「也就是说，出现了自增主键不连续的情况」 。

5、Innodb为什么推介用自增ID

①主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费

②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗

③减少了页分裂和碎片的产生

「UUID」： 「大量的随机IO」 + 「页分裂导致移动大量的数据」 +数据会有碎片

「总结」：自增ID有序，会按顺序往最后插入，而UUID无序，随机生成，随机插入，会造成频繁页分裂，内存碎片化，大量随机IO

索引

6、什么是索引

• 排好序的数据结构，可以帮助快速查找数据

• 优缺点：索引可以提高查询速度，查询使用优化隐藏器提高性能，但是也会占据物理空间，降低增删改的速度，因为还要操作索引文件

7、索引类型（覆盖索引+回表+索引下推+联合索引）

「普通索引」：可以重复

「唯一索引」：唯一，可为空，表中只有一个主键索引，可多个唯一索引

「主键索引」

• 唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）

• 一般推荐用自增主键， 「保证空间利用率，减少页分裂」

「全文索引」

「覆盖索引」：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。

「回表」：通过索引找到主键，再根据主键id去主键索引查

「索引下推」

• 在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作

❝

假如执行 select * from stu where name=? and age=? ，没有索引下推先再存储引擎根据name筛选数据返回给server层，然后server层再根据age过滤有索引下推直接根据name和age在存储