MySQL 的内连接、左连接、右连接有有什么区别？

• inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
• left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
• right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
• full join 全连接，完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

varchar 与 char 的区别？

char：

• 定长字符串，长度是固定的；
• 如果插入数据的长度小于 char 的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比 varchar 快很多，甚至能快 50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于 char 来说，最多能存放的字符个数为 255，和编码无关

varchar：

• 不定长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar 在存取方面与 char 相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于 varchar 来说，最多能存放的字符个数为 65532（但应该看具体的编码和字段定义情况）。

日常的设计，对于长度相对固定的较短的字符串，可以使用 char，对于长度不确定的，使用 varchar 更合适一些。

什么是事务？事务基本特性ACID？

事务是满足ACID特性的一组操作。commit提交事务，rollback回滚事务。

ACID:

• A(Atomicity)：原子性，事务中的操作要么全部成功，要么全部失败。

• C(consistency)：一致性，事务提交前后，数据库的完整性不能被破坏，要满足完整性约束，即所有的预设规则都不能被破坏。

eg：假设用户A和用户B两者的钱加起来一共是700，那么不管A和B之间如何转账，转几次账，这一约束都得成立，即事务结束后两个用户的钱相加起来还得是700，这就是事务的一致性。

如果转账过程中，仅完成A扣款或B增款两个操作中的一个，即未保证原子性，那么结果数据如上述完整性约束也就无法得到维护，一致性也就被打破。可以看出，事务的一致性和原子性是密切相关的，原子性的破坏可能导致数据库的不一致。

但数据的一致性问题并不都和原子性有关。比如转账的过程中，用户A扣款了100，而用户B只收款了50，那么该过程可以符合原子性，但是数据的一致性就出现了问题。

​ 一致性既是事务的属性，也是事务的目的。“事务是用来确保无论发生什么情况，你使用的数据都将处于一个合理的状态“，这里所说的合理/正确，也就是指满足完整性约束。

​ 总的来说，一致性是事务ACID四大特性中最重要的属性，而原子性、隔离性和持久性，都是作为保障一致性的手段。事务作为这些性质的载体，实现了这种由ACID保障C的机制。

• I(isolation)：隔离性，一个事务中的修改在未提交之前，对其他事务不可见。

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

并发一致性问题

• 脏读：一个事务读到另一个事务未提交的修改过的数据，未提交的事务可能会回滚。

• 虚读（不可重复读）：在同一个事务中，事务没提交之前，对同一个数据的两次读取，这两次读取到的值是不一样的。

• 幻读：同一个事务提交之前两次查询同一个范围内的数据总量，这两次查到的数据总量不一样。（是数量上的不一致（有 增、删），具体到某一条数据上的话都是一致的，（没有改，改就属于虚读了））

事务隔离级别

• 读未提交 RU（Read Uncommitted）： 一个事务中的修改，即使未提交，对其他事务仍可见。
• 读已提交 RC （Read Committed）：一个事务只能读到已提交事务的修改。
• 可重复读 RR （Repeatable Read）：在同一个事务中，对同一条数据的多次读取，结果都是一样的。
• 串行化 （Serializable）：事务只能串行执行，性能最差。

！！！注意：我们一般说的事务的特性和事务隔离级别都是关系型数据库的特性（大概念），而MySQL等具体的数据库会有一些改进。比如下面的表的可重复读不能解决幻读问题，但MySQL的Innodb为了在可重复读时能解决幻读，引入MVCC和临键锁的机制，就能实现了，所以要弄清楚。

隔离级别	脏读	虚读（不可重复读）	幻读
读未提交	会	会	会
读已提交	不会	会	会
可重复读	不会	不会	会
串行化	不会	不会	不会

mysql怎么保证ACID？

• 原子性：undo log 记录需要回滚的日志信息。
• 一致性：一般由代码层面保证或由AID共同保证。
• 隔离性：mvcc。
• 持久性：内存+redo log。mysql在内存和redo log 中记录某次修改的操作，若事务提交，用redo log 刷新磁盘；宕机后，通过redo log 恢复数据。

数据库设计三大范式

第一范式：列表示的数据一定不可再分解（但也要看具体业务）。

第二范式：表一定要有主键，且每一列都要和主键直接相关。（这只是大概念，比如innodb在创建表时可以不定义主键，但是有隐式主键的）

第三范式：列之间不能有依赖。

MySQL基础框架

MySQL 的架构共分为两层：Server 层和存储引擎层。

• Server 层负责建立连接、分析和执行 SQL
• 存储引擎层负责数据的存储和提取

角色

• client：客户端

• Server：

  • 连接器：身份和权限认证，登录mysql，与mysql建立连接。

  • 查询缓存：执行select查询语句时（只能用在查询），会先在查询缓存中查。（mysql 8.0之后移除，此功能不太实用，对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表的某一行有更新操作，那么这整个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool）。

  • 解析器/分析器：若没有命中缓存，则走到分析器。看你的sql语句要干什么，先做词法分析，后语法分析，构建语法树。

    

  • 预处理器：检查 SQL 查询语句中的表或者字段是否存在（并不是在解析器里做的）。将 select * 中的 * 符号，扩展为表上的所有列。

  • 优化器：找到mysql认为最佳的执行方案。

  • 执行器：执行优化后的方案，从存储引擎返回数据。执行语句之前会校验你是否有权限，若无权限则会报错。

• 插件式存储引擎：负责数据的存储和读写。

执行一条select语句发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

数据库存储引擎架构

插件式架构，不同的表可以设置不同的存储引擎以适应不同的场景。存储引擎是基于表的，不基于数据库。

能说下myisam 和 innodb的区别吗？

• myisam：

  • 不支持行锁，只支持表锁，不支持mvcc；
  • 不支持事务；不支持外键；
  • 索引用b+树，但索引和数据是分开的，即叶节点存放的是对应数据的地址，还需要去磁盘中寻址；
  • 不支持崩溃后恢复。
  • 适合大量查询，少量修改的场景。

• innodb：

  • 支持行锁和表锁，支持mvcc；
  • 支持事务；支持外键；
  • 索引用b+树，数据本身就是索引，即叶节点存放的就是对应的数据；
  • 支持崩溃后恢复（innodb独有的redo log）。
  • 能应对高并发。

mysql读写分离方案

主数据库用于写和实时性要求高的读操作，从服务器用于读操作。

原因：

• 业务一般是读多写少。主数据库少（写），从数据库多（读）。
• 主从各自负责读写，有效避免锁的竞争。
• 从数据库可用myisam引擎，提升查询性能。
• 集群部署，高可用。

常用反向代理来处理请求。

数据页的结构

数据页之间的双向链表靠文件头中的两个指针来建立。

数据页中的记录（每行数据）按照「主键」顺序组成单向链表，单向链表优点是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此，数据页中有一个页目录，起到记录的索引作用。

页目录创建的过程如下：

1. 将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
2. 每个记录组的最后一条记录就是组内最大的那条记录，并且最后一组最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
3. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。InnoDB 对每个分组中的记录条数都是有规定的，防止槽内的记录过多导致搜索的时间复杂度为O(n)：

• 第一个分组中的记录只能有 1 条记录；
• 最后一个分组中的记录条数范围只能在 1-8 条之间；
• 剩下的分组中记录条数范围只能在 4-8 条之间。

mysql 索引有哪些类型？

• 从数据结构角度：

  • B+树索引：默认
  • hash索引：对索引列计算hash，得到的hash值映射其对应的数据行，适用于等值查询。
  • Full-text全文索引：用来查找文本中的关键字，基于倒排索引实现，和es的作用类似，但性能要差得多，尽量不要用这个。
  • R树索引：常用于空间索引，存储地理位置信息。

• 从物理存储角度：

  • 聚集索引（主键索引）：以主键做索引，叶节点存的是数据。一张表只能有一个，因为主键只能有一个。
    • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
    • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
    • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id（row_id） 列作为聚簇索引的索引键（key）；
  • 非聚集索引（二级索引）：非主键做索引，叶结点存数据的主键值，还需要回表用聚集索引找到最终数据。

• 从逻辑角度：

  • 主键索引：唯一、非空，最多一个主键索引

  • 唯一索引：唯一、可空，可有多个唯一索引

  • 单列（普通）索引：可不唯一、可空

  • 多列（复合/联合）索引：多个列上做索引（一个索引，查询的时候，先根据第一列的值查找，再根据下一个查找，最后找到目标，如果是非聚集索引还要回表），只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀原则：多列字段做索引，state/city/zipCode，若想要索引生效，只能使用如下的组合where state/city/zipCode,where state/city,where state，其他方式（如city，city/zipCode）索引不会生效。所以在建立联合索引的时候，把最常用的列放在最左边，能提升查询性能。

    • 联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，范围查询字段的后面的字段可用到联合索引。
    • 对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（a = 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？
      • 在MySQL 5.6 之前，存储引擎只能从 ID=2 （主键值）开始一个个回表，到「主键索引」上找出所有数据行，传给server层对比 b 字段值。
      • 而MySQL 5.6 引入的索引下推（index condition pushdown)， 存储引擎可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，满足条件的才会回表，减少回表次数，也减少了传给server层多余的信息。
    • 可以建立联合索引进行排序。如果没有索引，会进行文件排序，将查询结果集的数据暂时写入临时文件，然后在这个临时文件中排序，时间复杂度高O(nlogn），大数据集的话还会在磁盘上进行排序，比在内存中排序要慢得多。如果有了索引，依靠索引的有序性，时复可降到O(n)。

    

  • （覆盖索引。主要用于MySQL性能优化，是对满足后面的条件的索引的另一种称呼）：如果一个索引包含了（或覆盖了）查询语句中查询字段和满足条件的数据，这样的索引就叫做覆盖索引。也就是在查询某些字段的数据时，只通过非主键索引就能查到想要的所以数据，而不需要回表走聚集索引，优化了性能。

  • 空间索引：空间数据。

为什么查询走了索引还是很慢？

是否使用索引和是否进入慢查询之间并没有必然的联系。使用索引只是表示了一个SQL语句的执行过程，而是否进入到慢查询是由它的执行时间决定的，而这个执行时间，可能会受各种因素影响：

• 是否是扫描了整个索引（相当于全表扫描）或者扫描的行数太多，即使是走了主键索引（通过索引找到第一条满足条件的数据后，通过链表一个个扫描直到最后一条满足条件的数据，满足条件的数据很多，扫描的行数就很多。也就是索引的过滤性不够，where id=4肯定比where id>2 and id <100000性能就要好，后者满足条件的行数太多，引擎在优化时甚至会不走索引）
• 是否回表了或回了多少次表。（回表是有代价的）
  • 使用了覆盖索引就不用回表，所以查询的列尽量少和精准，select * 就需要回表了。
  • 使用联合索引做范围查询时，找到第一条满足最左索引列的条件后会一条一条回表，如果这个范围很大，回表的次数就太多了。可以使用索引下推进行优化。

https://cloud.tencent.com/developer/article/1940517

什么时候适合 / 不适合创建索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 降低表的增删改的效率，每次增删改，B+ 树都要动态维护有序性。

适用创建索引：

• 字段有唯一性限制的，比如商品编码；
• 经常 WHERE 查询条件的字段，如果查询条件不止一个字段，可以建立联合索引。
• 经常 GROUP BY 和 ORDER BY 的字段，减少文件排序。

不适合创建索引：

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段、起不到定位作用的字段不需要创建索引，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，也就是区分度不高，不需要创建索引。比如性别字段，只有男女，男女的记录分布均匀，没有区分度，那么无论搜索哪个值都可能得到一半的数据，还不如不要索引。 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，频繁重建索引会影响数据库性能。

如果有慢sql会从哪些思路进行优化/性能优化有哪些思路？

表结构优化?

合理的库表结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。

1、星形模型(Star Schema)和雪花模型(SnowflakeSchema)的模型选择

2、增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。

3、优化字段数据类型：

• 采用合适的数据类型，如INT、VARCHAR、DATETIME等；
• 避免使用大量的TEXT和BLOB类型，会增加磁盘I/O的次数；
• 避免使用NULL值，会增加存储空间和查询开销；

4、表设计不能有太多的列，数千的列会影响性能。可做适当的水平和垂直拆分。

5、关联操作设计的表不要太多，否则执行会很慢（mysql限制了每个关联操作最多只能有61张表，在实际应用中，经验法则得出关联操作的表数量最好控制在12个以内）

6、扩展：范式和反范式。范式主要优化的是增删改的性能，比如数据冗余、更新异常、删除异常等。绝对的范式化是实验室中的产物，在实际的应用中要混用范式化和反范式化，根据具有情况，往往会带来较高的查询效率。遵循数据库的范式要求，尤其是前三个范式。严格遵循范式设计的表通常更小、数据冗余少，做更新操作简单快捷，但是，唯一的缺点就是在做查询时需要表关联，关联查询会不仅会带来高的代价，而且还可能造成索引策略失效，导致更低效率的查询。

索引优化？

• 前缀索引优化：（字符串前几个字符的区分度要大）使用某个字段中字符串的前几个字符建立索引，减小索引项大小，增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。有一定的局限性，例如：

  • order by 就无法使用前缀索引；
  • 无法把前缀索引用作覆盖索引；

• 覆盖索引优化：query 的所有字段，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

• 主键索引最好自增：每次插入一条新记录，都是追加操作，不需要重新移动数据，当页写满，就会自动开辟一个新页。如果非自增主键索引（如uuid），插入新数据时可能会导致页分裂，会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 索引最好设置为 NOT NULL：

  • 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和数值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
  • NULL 值是一个没意义的值，但是它会占用物理空间， InnoDB 如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表。

• 防止索引失效：常见的发生索引失效的情况：

  • 使用左或者左右模糊匹配时，即 like %xx 或者 like %xx%这两种方式都会造成索引失效；要使用like xxx%就可以走索引。
  • 在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  • 联合索引未遵循最左匹配原则，导致索引失效。
  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

查询优化/sql优化？

• 避免 SELECT *，只查询需要的字段

• 小表驱动大表，即小的数据集驱动大的数据集

  • 当B表的数据集比A表小时，用in优化 exist，两表执行顺序是先查B表再查A表查询语句：SELECT * FROM A WHERE id in (SELECT id FROM B) ；
  • 当A表的数据集比B表小时，用exist优化in ，两表执行顺序是先查A表，再查B表，查询语句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

• 一般情况下不推荐使用like操作，如果非使用不可，应保证能利用前缀索引的特性，如like “%aaa%” 不会使用索引而like “aaa%”可以使用索引

• 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表

• or 查询改写成 union 查询

• 尽量避免在 where 子句中使用 != 或者 “'<” ">“ 操作符，查询引用会放弃索引而进行全表扫描

• 查询语句尽可能简单，大语句拆小语句，减少锁时间

• 避免频繁创建和删除临时表，以减少系统表资源的消耗

• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写

• 尽量避免大事务操作，提高系统并发能力

• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

• 巧用STRAIGHT_JOIN。inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。

• 避免使用GROUP BY，可以使用DISTINCT代替；

• 避免使用HAVING，可以使用WHERE代替。

B树和B+树有哪些区别？

最根本的区别：B树的每个节点都会存储data，而B+树的data只在叶节点存储。所以会出现下面各种小区别：

• B+树的查询时间复杂度固定为O（log n），B树则不固定，可能在中间层就找到。
• 每个节点（页、磁盘块）的大小是固定的，所以B+树单次磁盘IO读取的数据量比B树要多，或者说B+树的IO次数要比B树少。也就是b+树比b树更“矮胖”。
• B+树通过双向链表将叶子节点进行连接，所有数据是有序的，可以做到快速地区间访问和范围查询。根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也很有可能被访问。再加上磁盘的预读机制，会将这一大块数据都读入缓存，又能减少磁盘IO。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

MySQL的Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系？

• Buffer Pool：在存储引擎中，会将要操作的数据缓存到这里，然后对数据的增删改操作都是在这里进行。这些脏数据操作完后会异步刷盘持久化。
• undo log：记录数据操作前（事务提交前）的样子，用于回滚
• redo log：（innodb才有）记录数据操作后（事务提交后）的样子，用于宕机时的故障恢复；Redo Log Buffer是日志刷入redo log的缓存区，在存储引擎中。
• bin log：记录整个操作的语句流程，用于主从复制。

buffer pool

Innodb 存储引擎(不是server层中的)设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

• 读数据时，先查 Buffer Pool ，命中缓存则返回数据，否则再去磁盘中读取。当查询一条记录时，InnoDB 会把整个页的数据加载到 Buffer Pool 中，因为通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。
• 改数据时，先修改 Buffer Pool 中数据所在的页，并将其设置为脏页，再由后台线程将脏页刷入到磁盘。

Buffer Pool 有多大？

默认 Buffer Pool 有 128MB 。可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。

Buffer Pool 缓存什么？

InnoDB 会把存储的数据划分为若干个页作为缓存页。Buffer Pool 主要缓存索引页、数据页，undo 页，插入缓存、自适应哈希索引、锁信息等等。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。控制块也占内存空间，放在 Buffer Pool 的最前面，接着才是缓存页。图中控制块和缓存页之间灰色部分是内存碎片，需要把 Buffer Pool 的大小设置得刚刚好才不会产生碎片。

如何管理 Buffer Pool？

如何管理空闲页？

从磁盘读取数据想要缓存到buffer pool时，通过遍历这一片连续的内存空间来找到空闲的缓存页的效率很低，所以设计了空闲链表（Free 链表），链表上的节点指向空闲缓存页的控制块，也就管理了空闲页。Free 链表上除了控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

每当从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

如何管理脏页？

Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。为了能快速知道哪些缓存页是脏的，于是设计了 Flush 链表，原理跟Free 链表一样。

如何提高缓存命中率？

普通LRU算法的缺点：

• 预读失效：根据空间局部性，mysql会将被访问数据的数据的相邻数据一起加载进buffer pool，减少磁盘 IO。简单的 LRU 算法会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够时，把末尾的页淘汰掉。如果这些预读页一直不被访问到，却占用了 LRU 链表前排的位置，而末尾淘汰的页，反而可能是频繁访问的页，这样就大大降低了缓存命中率。
• Buffer Pool 污染：当某一个 SQL 语句扫描了大量的数据（即使结果集很小，但如果查询过程中全表扫描了一个数据量大的表，也会造成buffer pool污染，因为会先把数据全放到buffer pool中，再逐一访问，根据条件筛选，全跑到young区去了）时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了。

mysql对普通LRU的改进：

• 预读失效：将 LRU 划分了 2 个区域：young 区 和 old 区。默认比例63：37。预读的页插入 old 区头部，当页被真正访问的时候，才将页插入 young 区头部。如果预读的页一直没有被访问，就从 old 区域移除，就不会影响 young 区域中的热点数据。

• buffer pool污染(缓存污染)：进入 young 区的条件增加了一个停留在 old 区的时间判断。在对某个处在 old 区的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

  • 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
  • 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

  这个间隔时间默认 1000 ms。也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区头部。

  另外，MySQL 针对 young 区还做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区前 1/4 被访问的页不会移动到链表头部，后 3/4的被访问了才会。

脏页什么时候会被刷入磁盘？

InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。不用怕脏页没来得及刷盘时mysql宕机而导致数据丢失。

刷盘时机：

• 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
• MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

什么是MVCC？MVCC怎么实现的？

MVCC（Multi-Version Concurrency Control），多版本并发控制。它主要用于innodb（这是innodb才有的）在事务隔离级别为读已提交和可重复读时，通过访问版本链的方式，对于select读操作，在读写并发的情况下分别实现读已提交和可重复读。

innodb实现MVCC的3个基本点

• 隐式字段：有3个：

  • DB_ROW_ID，隐式主键，若没有定义主键，就会用这个创建主键索引

  • DB_TRX_ID，对该行数据操作的事务ID

  • DB_ROLL_PTR，对该行数据操作的回滚指针，配合undo log使用

    

• undo log：可以看出，不同事务或者相同事务对同一行数据的修改，会导致该记录的undo log成为一条记录版本的链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录

  

• ReadView，视图

  • 视图就是一个列表，记录着整个系统中小于等于当前事务id的所有活跃的事务id（begin了但未commit）。例如视图为[80,100]，当前事务id就是100，其前面活跃的事务id为80。
    • 如果要查询的某行数据对应的DB_TRX_ID事务id小于视图中的最小值80，说明它早就提交过了，对当前事务可见，直接读。
    • 如果id大于视图中的最大值100，说明是在当前事务之后发生的，不管活不活跃，都不能直接读，需要去版本链中找，直到找到可见的或者结束。
    • 如果id=90，在最大值和最小值之间，判断是否在视图中，发现不在视图中，说明已经提交，可以读；如果id=80，在视图中，则未提交，肯定不可见，不可直接读读，去版本链中找。
  • 读已提交和可重复读都是靠MVCC实现的，但他们的视图建立的时机不一样
    • 读已提交，是在事务中的每一次select时就生成一个新的视图，也就是每一次select看到的都是最新的提交过的事务，所以就实现了读已提交。
    • 可重复读，是只在事务中的第一次select时生成一个视图，后面整个事务中的所有select都用同一个视图，保证整个事务中看到的都是一样的，也就实现了可重复读。但如果是当前读的话每次当前读都会生成新的视图。

可重复读级别下MVCC能解决幻读吗？

可重复读级别下仅靠mvcc是不能完全解决幻读的，因为读有分两种：快照读和当前读。

• 快照读：select语句。因为可重复读的多次select都用同一个视图，所以快照读时仅靠MVCC可以解决幻读。
• 当前读：insert、 update、delete、select···for update等这些加锁的就是当前读，他们仅仅是加了行锁，所以仅靠MVCC是不能解决当前读的幻读问题，还需要有其他技术来保证。

行锁（record lock）、间隙锁（gap lock）和临键锁（next-key-lock）

• 产生幻读的原因是当前读的那些语句仅仅是加了行锁（也就是记录锁），对于插入“间隙”中的那些数据根本锁不住。所以为了解决幻读问题，innodb引入了一个新的锁：间隙锁。这样就不能在这些间隙中间插入（insert语句）数据，也就解决了幻读问题。但这也会导致同样的语句锁住的范围变大了，影响了并发性能，还可能产生死锁的情况。

• 如果是select···for update语句，就会产生临键锁，它其实就是行锁+间隙锁，是一个左开右闭的锁区间。

  

需要注意的是只有在可重复读的级别下innodb才会自动加间隙锁或临键锁（在能使用记录锁或者间隙锁就能避免幻读现象的场景下，临键锁就会退化成记录锁或间隙锁），读已提交是没有的，所以innodb必须要RR才可以解决幻读问题，RC不能解决幻读。

还有要注意的是，在执行 update、delete、select … for update 等具有加锁性质的语句，一定要在索引列上加锁（where要有索引列），否则就相当于全表扫描，会对每一行数据都加 next-key 锁，相当于把整个表锁住了，线上环境更要注意。

MySQL的锁有哪些？

• 按模式：
  • 乐观锁：一般是业务代码层面来实现，通过版本号
  • 悲观锁：mysql系统来实现，select for update
• 按粒度：
  • 全局锁：数据库备份时对整个数据库上锁，此时只能读
    • 应用场景主要为全库逻辑备份，缺点是会造成业务停滞。
    • 如果引擎支持可重复读的隔离级别，可在备份数据库前开启事务，创建 Read View，由于 MVCC 的支持，备份期间其他业务依然可以对数据进行更新操作，不会影响备份数据库时的 Read View。
  • 表锁：myisam和innodb都支持
  • 页锁：BDB引擎支持，其他不支持
  • 行锁：Innodb支持
• 按属性：
  • 共享锁（读锁）：一个事务加了读锁，其他事务只能读（或加读锁），但不能写（或加写锁）
  • 排他锁（写锁）：一个事务加了写锁，其他事务不能读（或加读锁），也不能写（或加写锁），是互斥的。select for update
• 按状态：
  • 意向共享锁：一个数据行加共享锁前必须先取得该表的意向共享锁。
  • 意向排他锁：一个数据行加排他锁前必须先取得该表的意向排他锁。
  • 意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。当一个事务有行锁时，mysql会自动给该表添加意向锁，当另一个事务想要锁表时，无需遍历每一行判断是否有行锁，只需判断意向锁是否存在，存在则说明有别的事务加了行锁，可快速返回该表不能启用表锁。
• 按算法：
  • 间隙锁
  • 记录锁（就是行锁）
  • 临键锁

表级锁

• 表锁
• 元数据锁（MDL）：不需要显式地使用 MDL，对数据库表进行操作时，会自动给这个表加上 MDL。事务执行期间，MDL 一直持有，所以在改变结构之前查看是否有长事务，不然会造成大量线程的阻塞。
  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；
• 意向锁
• AUTO-INC自增锁：执行完插入语句后就会立即释放。

行级锁

• 记录锁
• 间隙锁：需要注意，两个事务的间隙锁之间是相互兼容的，不会产生冲突，即使间隙范围一样，一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，间隙锁目的是为了防止其他事务插入数据，配合插入意向锁来达到这个目的。（也就是说间隙锁相当于是一种读锁，防止该线程在读的时候某个间隙内被其他线程插入数据导致幻读。所以间隙锁之间不互斥，但插入意向锁相当于写锁，就是互斥的）
• 临键锁：（因为临键锁包含间隙锁+纪录锁，所以临键锁是互斥的，间隙锁不互斥）
• 插入意向锁：
  • 不是意向锁，是一种特殊的间隙锁。
  • 一个事务在插入一条记录时，生成一个插入意向锁，判断插入位置是否已被其他事务加了间隙锁或临键锁，如果有间隙锁或临键锁，将插入意向锁的状态设置为等待状态（MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），该事务阻塞直到获取间隙锁或临键锁。
  • 插入意向锁是互斥的，同一间隙范围只能有一个事务拿到插入意向锁（不同范围不冲突）。
  • 如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入之前需要获取到插入意向锁，所以都在等待对方事务的间隙锁释放，会发生死锁。比如下面的分析：

死锁情况分析

t_order 表里现在已经有了 6 条记录，可重复读的级别下：

假设这时有两事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录。如果仅仅使用select，锁不住(1006,∞]的间隙，会出现幻读：

所以需要使用select…for update。因为1006之后没有数据了，所以两个事务并发进来都拿到了**(1006,∞]的间隙锁**，两者不互斥，事务A想要在(1006,∞]这个间隙中插入数据，就需要阻塞等待事务B释放这段间隙锁才能拿到插入意向锁，事务B同理，造成了死锁。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、非抢占式、循环等待。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。超时后，对该事务回滚，锁就释放了。
• 开启主动死锁检测。发现存在死锁时回滚其中一个事务。

但这些都是在死锁发生后的处理结果，在业务层面就要尽量避免死锁产生：订单的幂等性校验将order_no设置成唯一索引列即可。

mysql主从同步机制是怎么的？

主要涉及3个线程：

• bin log 线程：将主库的数据写进bin log文件中。
• IO 线程：从库读取主库的bin log文件，并写入从库的relay log中继日志中。
• SQL 线程：从库读取中继日志，重新执行对应的SQL语句。

主从同步方式：

• 全同步：上面3个线程都执行完了才返回响应给主库。显然性能很差。
• 半同步：从库执行完前两个线程就返回ACK给主库，主库收到至少一个从库的ack就认为同步完成。
• 异步：写完主库就可响应用户。

集群形式：

• 一主多从：最常用。使用起来简单有效，不仅可以实现 HA，而且还能读写分离，进而提升集群的并发能力。
• 多主一从：将多个 MySQL 数据库备份到一台存储性能比较好的服务器上。
• 双主复制：每台节点即是主也是从，任何一方所做的变更，都会复制到另外一方的数据库中。
• 级联复制：因为如果主节点有太多的从节点，就会损耗一部分性能用于同步，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

主从数据库在对主库写的时候加锁会不会锁从库

不会，因为从库的同步方式是通过binlog实现，不会锁主库。加锁只是对于被请求的那台节点，而不是锁整个集群。从库在sql线程执行某条写请求的sql时才会对从库加锁。

同理给从库加读锁的时候不会锁主库。

MySQL 一行记录是怎么存储的？（讨论Innodb）

表空间文件的结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成。

• 行：数据库表中的记录都是按行进行存放的，每行记录根据不同的行格式，有不同的存储结构。
• 页：InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB。页的类型常见的有数据页、undo 日志页、溢出页等，表中的记录存储在数据页。
• 区：InnoDB 用 B+ 树来组织数据，B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻两个页的物理位置并不连续，可能离非常远，那么磁盘查询时就会有大量的随机I/O。如果让链表中相邻的页的物理位置也相邻，就可以使用顺序 I/O ，那么在范围查询（扫描叶子节点）的时候性能就会很高。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
• 段：一般分为数据段、索引段和回滚段等。
  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合。

InnoDB 行格式有哪些？

Redundant、Compact、Dynamic和 Compressed。

• Redundant ：很古老，不紧凑， MySQL 5.0 版本之前使用，现在基本没人用了。
• Compact ： MySQL 5.0 之后引入，紧凑，让一个数据页中可以存放更多的行记录， MySQL 5.1 版本之后默认设置成 Compact。
• Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

COMPACT 行格式长什么样？

• 变长字段长度列表：
  • 存储变长字段实际存储的数据的字节数。
  • 按照列的顺序逆序存放，这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中。
  • 如果变长字段允许存储的最大字节数小于等于 255 字节（如ascii字符集的varchar(200)占200字节），就会用 1 字节表示「变长字段长度」；
  • 如果大于 255 字节，就会用 2 字节表示「变长字段长度」；
  • 如果无变长字段，则可不需要这个列表。
• null值列表：
  • 如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。值为1代表null，0代表非null。
  • 必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。超过8列则增加一字节。
  • 如果字段都定义成 NOT NULL ，则可不需要这个列表。
• 记录头信息：各种相关的信息，如：
  • delete_mask ：标识此条数据是否被删除。执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  • next_record：下一条记录的位置。记录与记录之间是通过链表组织，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  • record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。
• row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。不必需，占用 6 个字节。（隐藏列）
• trx_id：事务id，该数据由哪个事务生成。 必需，占用 6 个字节。（隐藏列）
• roll_pointer：该记录上一个版本的指针。必需，占用 7 个字节。（隐藏列）

varchar(n) 中 n 最大取值为多少？

varchar(n) 字段类型的 n 代表的是最长字符数，不是字节大小。

而MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有列（不包括隐藏列和记录头信息）占用的字节加起来的总长度不能超过 65535 个字节。也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。所以所有列的总字节数（不包括隐藏列和记录头信息） + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535

所以对于varchar(n) 中 n 最大取值为多少是不准确的问题，需要考虑到多个因素：

• 是什么字符集，不同字符集的一个字符占用的字节数是不同的，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 最大能存储 100 字节。
• 是否允许为null，如果允许， NULL值列表至少需要占1字节。
• 其他字段占多少字节，varchar类型的有多少列。

如果只有varchar一列，允许为null，ascii字符集，则n最大为65535-2-1=65532。（2为变成字段超过255字节，需要2字节存储。1为null值列表字节数）

MySQL 的 NULL 值是怎么存放的？

Innodb的Compact 行格式中会用**「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分**。

行溢出后，MySQL 是怎么处理的？

MySQL 一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能都存不了一条记录，就会发生行溢出，多的数据就会存到另外的「溢出页」中。

当发生行溢出时，在记录的真实数据处只保存该列的一部分数据，把剩余数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

count(*) 和 count(1) 有什么区别？哪个性能最好？

按照性能从好到坏排序：count(*)=count(1)>count(主键字段)>count(普通字段)

字符集

ASCII

• 只适用现代美国英语字符

• ASCII 字符集：1字节长度，最高位是校验位，所以只能表示128（2^7）个字符。

• ASCII 扩展字符集：1字节长度，取消校验位，能表达256（2^8）个字符。

GB2312

• 对汉字友好，基本涵盖了绝大部分常用汉字。不过绝大部分生僻字和繁体字。
• 英语字符编码和ASCII相同。英文字符只需1字节，其他用2字节。

GBK

• GBK 字符集可以看作是 GB2312 字符集的扩展，兼容 GB2312 字符集，共收录了 20000 多个汉字。

• GBK 中 K 是汉语拼音 Kuo Zhan（扩展）中的“Kuo”的首字母。

GB18030

GB18030 完全兼容 GB2312 和 GBK 字符集，纳入中国国内少数民族的文字，且收录了日韩汉字，是目前为止最全面的汉字字符集，共收录汉字 70000 多个。

BIG5

BIG5 主要针对的是繁体中文，收录了 13000 多个汉字。

Unicode 字符集

Unicode 字符集中包含了世界上几乎所有已知的字符。但它只是一种字符集，没规定具体的编码方式，下面要讲的就是它的各种编码方式，要区分好它们与Unicode。

UTF-8

• 应用最广泛。

• 使用1到4个字节来编码。

• 可以自动选择编码长度，像英文字符只需1字节，和ASCII一样。

• 中文占3个字节。

• 繁体字、复制文字、emoji占4个字节。

MySQL的utf8和utf8mb4

• utf8：只支持1-3个字节编码，不能用4字节编码，所以复杂文字等会报错。
• utf8mb4：标准UTF-8的完整实现，可以用4字节编码。

UTF-16

• 使用2个或4个字节来编码。

UTF-32

• 固定使用4个字节来编码。
• 规则最简单，但像英文字母字符浪费大量空间。