MySQL 的逻辑架构了解吗？

  1. 第一层是网络连接层，主要完成一些类似于连接处理、授权认证、及相关的安全方案。
  2. 第二层是核心服务层，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
  3. 第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信，不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎。
  4. 第四层是系统文件层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

SQL 约束有哪几种？

  1. NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
  2. UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
  3. PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
  4. FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  5. CHECK: 用于控制字段的值范围。

自增主键（AUTO_INCREMENT）理解?

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

  • 在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id) + 1作为这个表当前的自增值
  • 举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO INCREMENT的值。
  • 在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值，才有了“自增值持久化”的能力。

自增值修改机制
如果id字段被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

  1. 如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段;
  2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值 。
    自增值新增机制:
  3. 如果准备插入的值>=当前自增值，新的自增值就是 “准备插入的值+1”;
  4. 否则，自增值不变。

为什么自增主键不连续？

唯一键冲突：

  • 由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键 = 现在变了的自增值+1，所以不连续；
  • 举例：假设执行 SQL 的时候 user 表 id = 10，此时在内存中的自增 id 为11，此时发生唯一键冲突写库失败，则 user 表没有 id = 10 这条记录，之后 id 从11开始写入，因此 id 是不连续的。

事务回滚：

  • 自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突；
  • 举例： 假设同时需要对 user、staff 表进行写库操作，执行 SQL 的时候 user 表 id = 10，此时在内存中的自增 id 为11；staff 表 id = 20，此时内存中的自增 id 为21，一旦事务执行失败，事务回滚，写库失败，则 user 表没有 id = 10 这条记录，staff 表没有 id = 20 这条记录，user 表从11开始写入，staff 表从21开始写入，如此产生 id 不连续的现象。

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

  1. InnoDB优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键。
  2. 使用自增主键好处：
    • 使用自增ID，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费;
    • 新插入的行一定会在原有的最大数据行下一行，MySQL定位和寻址很快，不会为计算新行的位置而做出额外的消耗;
    • 减少了页分裂和碎片的产生。
  3. 如果使用非自增主键:
    • 由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页中间的某个位置 ， 此时MySQL不得不为了将新记录插到合适位置而移动数据 ，无疑增加了很多开销，同时分页操作也造成了大量的碎片。

VARCHAR 和 CHAR有什么区别？

  1. 固定长度 & 可变长度
    • CHAR用于存储固定长度字符串，假如申请了char(10)的空间，那么无论实际存储多少内容，该字段都占用 10 个字符。
    • VARCHAR用于存储可变长度字符串， MySQL会根据定义的字符串长度分配足够的空间。
  2. 存储方式
    • VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。
    • CHAR适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的 CHAR 不容易产生碎片。
  3. 占用字节
    • CHAR的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
    • VARCHAR的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都是非unicode的字符数据。
  4. 存贮效率
    • CHAR的存取速度比VARCHAR要快得多，因为其长度固定，方便程序的存储与查找；但是CHAR也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，是以空间换取时间效率 。
    • VARCHAR是以空间效率为首位的。