大纲

Mysql

  1. 数据库三大范式是什么？
  2. mysql有关权限的表都有哪几个？
  3. mysql的binlog有几种录入格式，分别有什么区别？
  4. mysql存储引擎myisam与innodb的区别
  5. 什么是索引？
  6. 索引有哪些优缺点
  7. 索引有哪几种类型
  8. 创建索引时需要注意什么
  9. 使用索引查询一定能提高查询性能吗，为什么
  10. myisam索引与innodb索引的区别
  11. mysql中有哪几种锁
  12. mysql中innodb支持的四种事务隔离级别名称，以及逐级之间的区别
  13. char和varchar的区别
  14. 主键和候选键的区别
  15. 如何在unix和mysql时间戳之间进行转换
  16. myisam表类型将在哪里存储，并且还提供其存储格式
  17. mysql里记录货币用什么字段类型好
  18. 百万级别或以上的数据如何删除
  19. 什么是最左前缀原则，什么是最左匹配原则
  20. 什么是聚簇索引？何时使用聚簇索引与非聚簇索引
  21. mysql查询缓存
  22. mysql连接器
  23. mysql分析器
  24. mysql优化器
  25. mysql执行器
  26. 什么是临时表，何时删除临时表
  27. 谈谈sql优化经验
  28. 什么叫外链接
  29. 什么叫内链接
  30. 使用union和union all时需要注意什么
  31. myisam存储引擎的特点
  32. innodb存储引擎的特点
  33. 脏读、不可重复读
  34. innodb为什么使用b+树（b+ b hash）


某学教育

Mysql架构体系设计深入剖析

  1. mysql背后优秀的架构设计是如何实现的
  2. mysql磁盘管理：mysql的数据是怎么组织的
  3. 页内记录维护（顺序保证/插入策略/页内查询）
  4. mysql内存管理（页面管理/页面淘汰/LRU）：全表扫描对内存有什么影响？如何避免热数据被淘汰？没有空闲页怎么办
  5. mysql索引为什么采用B+树结构
  6. 如何根据记录数去估算索引树的高度？
  7. 主键索引、聚簇索引、二级索引与联合索引具备哪些特点？
  8. 索引下推ICP、覆盖索引都带来了哪些优势
  9. 是否知道联合索引对于order by以及order by的意义
  10. 什么情况下会导致索引失效

  11. 在海量数据与高并发场景下如何进行主键设计？

    企业千亿级海量数据事务处理与分库分表设计方法提炼

  12. InnoDB是如何实现加行级锁的过程？

  13. InnoDB中间隙锁是如何解决幻读的？
  14. InnoDB中Next-Key锁的锁定区间是哪些？
  15. InnoDB出现死锁的原理是什么以及如何解决的？
  16. MVCC如何实现多版本控制？如何解决读写冲突？
  17. 回滚日志Insert Undo log 和 Update Undo log的区别是什么？
  18. Undo log 和 ReadView 是如何实现多版本控制与保证事务原子性的？
  19. Undo log如何清理？为什么InnoDB count(*)这么慢？
  20. 重做日志Redo log如何实现事务持久性？
  21. checkpoint检查点机制与脏页落盘以及Redo log落盘的关系
  22. Mysql如何进行优雅的库表设计？

  23. 在海量数据与高并发场景下如何优雅的分库分表方案？

    架构师实战中必备的Mysql之企业千亿级海量数据真实案例设计与实战

  24. 不同场景下分库分表的落地方案有哪些？（用户库分表/商品库分表/系统消息库分表）

  25. 分库分表时，分表分少了怎么进行处理？
  26. 数据库拓展在请求路由中会带来哪些问题？
  27. 如何实现千亿级电商商品数据与推送数据水平拆分？
  28. 如何实现万亿级微信消息垂直拆分？
  29. 企业及数据库Sharding Sphere是如何实现分库分表的？
  30. Mysql如何高效的实现数据库冗余部署
  31. Mysql数据库的高可用方案有哪些？

  32. 分布式落地方案有哪些？

    内容

    Mysql

    数据库三大范式是什么？

  33. 每个列都不可以再拆分

  34. 在第一范式的基础上，非主键列完全依赖于主键，而不是依赖于主键的一部分
  35. 在第二范式的基础上，非主键只依赖于主键，不依赖于其他非主键

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

mysql有关权限的表都有哪几个？

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别是user、db、table_prev、columns_priv、和host。下面分别介绍一下这些表的结构和内容：

  • user：记录各个账号在各个数据库上的操作权限
  • table_priv：记录数据表级的操作权限
  • columns_priv：记录数据列级的操作权限

  • host：配合db表对给定主机上的数据库操作权限作更细致的控制。这个权限表不受grant和revoke语句的影响

    mysql的binlog有几种录入格式，分别有什么区别？

    有三种格式：statement、row和mixed

  • statement：每一条修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog的日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关信息，同时还有一些使用了函数之类的语句无法被记录复制。

  • row：不记录sql语句的上下文相关信息，仅保存哪些记录被修改。记录单元为一行的改动，基本是可以全部记下来。但是由于操作很多，会导致大量行的改动（如alter table）被记录，因此这种模式的文件保存的信息太多，日志量太大。

  • mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row

    mysql存储引擎myisam与innodb的区别

  • 锁粒度方面：由于锁粒度不同，InnoDB比MyISAM支持更高的并发；InnoDB的锁粒度为行锁，MyISAM的锁粒度为表锁、行锁会对每一行进行加锁。所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁。

  • 可恢复性上：由于InnoDB是有事务日志的，所以在发生由于数据库崩溃等事件后，可以根据日志文件进行恢复。而MyISAM没有事务日志
  • 查询性能上：MyISAM要优于InnoDB，因为InnoDB在查询过程中，需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接查到内存数据。

  • 表结构文件上：MyISAM的表结构文件包括：frm（表结构定义）、myi（索引）、myd（数据）；而InnoDB的表数据文件为ibd（数据）和frm（表结构定义）

    什么是索引？

    索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。
    索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。索引的实现通常使用B树及其变种B+树
    更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它也要占据物理空间

    索引有哪些优缺点

    优点：

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因

  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

  • 时间方面：创建索引和维护索引需要耗费时间，具体地，当对表的数据进行行增加、删除和修改的时候，索引也需要动态的维护，会降低 增/删/改的执行效率

  • 空间方面：索引需要占据物理空间

    索引有哪几种类型

    主键索引：
    数据列不允许重复，不允许为null值，一个表只能有一个主键。
    唯一索引：

  • 可以通过alter table table_name add unique (column) ；创建唯一索引

  • 可以通过alter table table_name add unique (column, column); 创建唯一组合索引

普通索引：
基本的索引类型，没有唯一性的限制，允许为null值

  • 可以通过alter table table_name add index index_name (column)；创建普通索引
  • 可以通过alter table table_name add index index_name (column1, column2)；创建组合索引

全文索引：
是目前搜索引擎使用的一种关键技术

  • 可以通过alter table table_name add fulltext (column)；创建全文索引。

    创建索引时需要注意什么*使用count查看？

  • 非空字段：应该指定列为not null，除非你想存储null。在mysql中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值

  • 取值离散大的字段：变量各个取值的差异程度大的列更适合做索引，可以通过count（）函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高

  • 索引字段越小越好：数据库的数据存储以页为单位，一页存储的数据越多，一次IO操作获取的数据越大，效率越高

    使用索引查询一定能提高查询性能吗，为什么*索引范围查询是啥

    通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条insert、delete、update将为此付出4、5次的磁盘IO。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询（index range scan）适用于两种情况

  • 基于一个范围的检索，一般查询返回结果集小于表中记录的30%

  • 基于非唯一性索引的检索

    myisam索引与innodb索引的区别

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引

  • InnoDB的主键索引的叶子结点存储着行数据，因此主键索引非常高效
  • MyISAM索引的叶子结点存储的是行数据地址，需要再寻址一次才能得到数据。

  • InnoDB非主键索引的叶子结点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

    mysql中有哪几种锁

  • 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

  • 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高

  • 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般

    mysql中innodb支持的四种事务隔离级别名称，以及逐级之间的区别

    SQL标准定义了四种隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离一般支持更高的并发处理，并拥有更低的系统开销
    sql标准定义的四个隔离级别为：

  • read uncommitted: 读取未提交数据

    • 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不必其他级别好多少。读取未提交的数据，也被称之为脏读（dirty read）
  • read committed：读取提交内容
    • 这是大多数数据库系统的默认隔离级别（但不是mysql默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable read），因为同一事务的其他实例在该实例处理期间可能会有新的commit，所以同一select可能返回不同结果
  • repeatable read：可重读
    • 这是mysql的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题

  • serializable：串行事务

    • 这是最高级别的隔离，它通过强制事务排序，使之不可能相互冲突，从而解决幻读的问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

      char和varchar的区别*没说varchar

  • char和varchar类型在存储和检索方面有所不同

  • char列长度固定为创建时声明的长度，长度范围是1到255

  • 当char值被存储时，它们被用空格填充到特定长度，检索char值时需要删除尾随空格

    主键和候选键的区别

    表格的每一行都由主键唯一标识，一个表只有一个主键
    主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用

    如何在unix和mysql时间戳之间进行转换

  • UNIX_TIMESTAMP是从mysql时间戳转换为Unix时间戳的命令

  • FROM_UNIXTIME是从unix时间戳转换为mysql时间戳的命令

    myisam表类型将在哪里存储，并且还提供其存储格式

    每个MyISAM表格以三种格式存储在磁盘上：

  • “.frm”文件 存储表定义

  • 数据文件具有”.MYD” （mydata）扩展名

  • 索引文件具有”.MYI” (myindex)拓展名

    mysql里记录货币用什么字段类型好

    Numeric和Decimal类型被mysql实现为同样的类型，这在sql92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模能被（并且通常）指定
    如：
    salary Decimal(9, 2)
    在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将用于存储小数点后的位数。
    因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99

    百万级别或以上的数据如何删除

    关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的，所以当我们对数据增加、修改、删除都会产生额外的堆索引文件的操作，这些操作需要消耗额外的IO，会降低增、删、改的执行效率。所以，在我们删除数据库百万级别数据的时候。查询mysql官方手册得知删除数据的速度和创建索引的数量是成正比的。

  • 所以我们想要删除百万数据的时候可以先删除索引

  • 然后删除其中无用数据
  • 删除完成后重新创建索引（此时数据较少了）

  • 与之前的直接删除要快很多，如果期间删除中断，会导致删除回滚，造成更多时间浪费。

    什么是最左前缀原则，什么是最左匹配原则

    就是最左优先，在创建多列索引时，要根据业务需求，where字句中使用最频繁的一列放在最左边。
    最左前缀匹配规则，非常重要的原则是，mysql索引会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a=1 and b=2 and c > 3 and d=4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，abd的顺序可以任意调整。=和in可以乱序，比如a=1 and b=2 and c=3建立(a,b,c)索引就可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

    什么是聚簇索引？何时使用聚簇索引与非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，找到了索引也就找到了数据

  • 非聚簇索引：将数据存储与索引分开的结构，索引结构的叶子结点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也是为什么索引不在key buffer命中时速度缓慢的原因

    Mysql连接器

    以下问题为sql的一系列流程，非单独问题
    首先需要有一个连接池，或者已经连接到数据库。

    mysql查询缓存

    如果开启查询缓存，当执行完全相同的sql语句，且表中数据没有变化的时候，服务器就会直接从缓存中读取结果。如果数据被修改，之前的缓存就会失效，所以修改比较频繁的表不适合开启查询缓存。
    如果没有开启查询缓存，或者没有命中查询缓存，则会进入下一步

    mysql分析器

  • 首先，mysql会根据你写的sql语句进行解析，分析器先做词法分析，你写的sql就是由多个字符串和空格组成的一条sql语句，mysql需要识别出sql代表的含义。

  • 然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个sql是否满足mysql的语法。如果sql语句不正确，就会返回 You hve and error in your SQL syntax

    mysql优化器

    经过分析器的词法分析和语法分析后，这条sql语句就是合法的sql语句，mysql就知道你要做什么了。但是在执行前，还需要进行优化器的处理，优化器会判断你使用了哪种索引，使用了何种连接，优化器的左右就是确定效率最高的执行方案。

    mysql执行器

    mysql通过分析器知道了你的sql是否合法，你要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了执行阶段。开始执行这条sql语句，sql会首先判断你有没有执行这条语句的权限，没有权限的话，就会返回权限错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，使用引擎提供的结构。对于有索引的表，执行逻辑也差不多。

    什么是临时表，何时删除临时表

    在执行sql语句的过程中，通常会临时创建一些存储中间结果的表，临时表只对当前连接可见，在连接关闭时，临时表会被删除并释放表空间。
    临时表分为两种：一种是内存临时表，一种是磁盘临时表。

  • 内存临时表使用的是memory引擎

  • 磁盘临时表使用的MyISAM引擎

mysql会在下面这几种情况产生临时表

  • 使用了union查询。union有两种，一种是union，一种是union all，它们都用于做联合查询；区别是使用union会去掉两个表中的重复数据，相当于对结果集做了一下去重。使用union all则不会去重，返回所有的行。
  • 使用temptable孙发或者是union查询中的视图。temptable算法是一种创建临时表的算法，它是将结果放置到临时表中，然后再使用这个临时表进行相应的查询。
  • order by 和 group by的字句不一样时也会产生临时表。
  • distinct查询并且加上order by时。
  • sql中用到了SQL_SMALL_RESULT选项时。如果查询结果比较小的时候，可以加上SQL SMALL RESULT来优化，产生临时表
  • FROM中的子查询

  • EXPLAIN查看执行计划结果的Extra列中，如果使用Using Temporary就标识会用到临时表。

    谈谈sql优化经验

  • 查询语句无论是使用哪种判断条件，等于、小于、大于，where左侧的条件查询字段不要使用函数或者表达式

  • 使用explain命令优化select查询，对于复杂、效率低的sql语句，通常使用explain sql来分析这条sql语句，方便分析优化
  • 当select语句只需要使用一条记录时，要使用limit 1
  • 不要直接使用select ，而是具体写出需要查询的表字段，因为使用explain来分析会发现，select 使用的是全表扫描，type = all
  • 为每一张表设置一个ID键
  • 避免在where语句中对字段进行null
  • 避免在where中使用 ! 或 > 操作符
  • 使用BETWEEN AND 替代 IN
  • 为搜索字段创建索引
  • 选择正确的存储引擎，InnoDB、MyISAM、Memory等
  • 使用LIKE %abc% 不会走索引，而使用LIKE abc%会走索引
  • 对于枚举类型的字段（即有固定罗列值的字段），建议使用ENUM而不是Varchar，比如性别、星期、类型、类别等。
  • 拆分大的DELETE或INSERT语句
  • 选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数
  • 字段设计尽可能使用NOT NULL

  • 进行水平切割或者垂直分割

    什么叫外链接

  • 左外连接（left outer join 或 left join）：又称为左连接，这种连接方式会显示全部的左表，和符合条件的右表。右表不符合的地方均为NULL

    • image.png
    • 表A 表B
    • mid kind mid name
    • A 猫 A 咪咪
    • B 狗 B 旺财
    • C 蛇 D 饭团
    • 左连接查询：select a.mid, a.kind, b.name from a left join b on a.mid = b.mid
    • 结果
    • mid kind name
    • A 猫 咪咪
    • B 狗 旺财
    • C 蛇 Null
  • 右外连接（right outer join 或 right join）：又称为右连接，他与左连接相对，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示NULL
    • image.png
    • 表A 表B
    • mid kind mid name
    • A 猫 A 咪咪
    • B 狗 B 旺财
    • C 蛇 D 饭团
    • 右连接查询：select a.mid, a.kind, b.name from a right join b on a.mid = b.mid;
    • 结果
    • mid kind name
    • A 猫 咪咪
    • B 狗 旺财
    • NULL Null 饭团 // 因为mid是从表a选择出来的，所以没有饭团的mid

  • 全外连接（full outer join 或 fulljoin）

    • 完整返回左表和右表中的所有行。如果在某行在另一个表没有匹配行时，则另一个表的选择列表包含空值。
    • 表A 表B
    • mid kind mid name
    • A 猫 A 咪咪
    • B 狗 B 旺财
    • C 蛇 D 饭团
    • 全连接查询：
      • select a.mid, a.kind, b.name from a left join b on a.mid = b.mid
      • UNION
      • select a.mid, a.kind, b.name from a right join b on a.mid = b.mid
    • 结果
    • mid kind name
    • A 猫 咪咪
    • B 狗 旺财
    • C 蛇 Null
    • Null Null 饭团
    • mysql不支持fulljoin 可以通过
      • select from a *LEFT JOIN b ON a.name = b.name
      • UNION
      • select from a *RIGHT JOIN b ON a.name = b.name

        什么叫内链接(inner join)

        返回两个表中都条件都符合的字段。
        image.png

        使用union和union all时需要注意什么

  • 通过union连接的sql分别单独取出的列数必须相同

  • 使用union时，多个相等的行会被合并，由于合并比较耗时，一般不直接使用union进行合并，而是通常采用union all进行合并

    myisam存储引擎的特点

    在5.1版本之前，myisam是mysql的默认存储引擎，myisam的并发性比较差，使用的场景比较少，主要特点是：

  • 不支持事务操作，没有原子特性，这一设计主要是为了效率和性能

  • 不支持外键操作，如果强行加外键，mysql不会报错，只不过外键不起作用
  • myisam默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况
  • myisam会在磁盘存储三个文件，文件名和表名相同，拓展名分别是frm（表定义）、myd（存储数据）、myi（索引），这里需要特别注意的是myisam只缓存索引文件，并不缓存数据文件
  • myisam支持的索引类型有全局索引（full-text）、b-tree索引，r-tree索引
    • full-text：它的出现是为了解决针对文本的模糊查询效率较低的问题
    • b-tree：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶子结点
    • r-tree：它的存储方式和b-tree有一些区别，主要设计用于存储空间和多维数据的字段做索引，目前mysql只支持geometry类型的字段作r-tree索引。相对于btree，r-tree的优势在于范围查找
  • 数据库所在的主机如果宕机，myisam的数据文件容易损坏，而且难以恢复

  • 增删查改性能方面，select性能较高，适合于查询较多的情况。

    innodb存储引擎的特点

    自从5.1版本之后，默认的存储引擎变成了InnoDB，相对于myisam，innodb有了较大改变，主要特点是：

  • 支持事务操作，具有事务原子隔离特性，默认的隔离级别是可重复读（repetable-read），是通过mvcc（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。

  • innodb支持外键操作
  • innodb默认的锁粒度是行级锁，并发性能比较好，会发生死锁的情况
  • 和myisam一样的是，innodb存储引擎也有frm文件存储表结构定义，但是innodb的表数据和索引是存储在一起的，都位于b+树的叶子结点，而myisam的表数据和索引是分开的。
  • innodb有安全日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致丢失的数据问题，保证数据的一致性。
  • innodb和myisam支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。
  • 增删改查性能方面，如果执行大量的增删改操作，推荐使用innodb，它在删除操作时是对行删除，不会重建表。

    innodb为什么使用b+树（b+ b hash）

    因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
    至于哈希结构，虽然增删查改为O(1)复杂度 但是不适合范围查找，范围查找需要全表搜索

    Mysql架构体系设计深入剖析

mysql背后优秀的架构设计是如何实现的

mysql磁盘管理：mysql的数据是怎么组织的

一个表->有一个文件->有多个page->有多个行
page：
一个表内存有page，page是交互的最小单位，一个page 16KB大小，一个page最小存储2行记录。

  • 页头
    • 记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针、页面空间使用情况等。
  • 虚记录（最小/大虚记录）
    • 最大虚记录：比页内最大主键还大
    • 最小虚记录：比页内最小主键还小
    • 比如查询第100号记录，不在最大与最小虚记录内，则不在该页内
  • 记录堆
    • 行记录存储区，分为有效记录和删除记录两种
  • 自由空间链表
    • 已删除记录组成的链表
  • 未分配空间
    • 页面未使用的记录空间
  • slot区
    • 用来查询，没有slot区需要遍历查，有slot之后先查slot，找到slot对应的数据列表
  • 页尾
    • 页面最后部分，共占8个字节，主要存储页面的校验信息

查询一次，取出的是一个或多个page放在内存，而不是一行记录。成为预热

Question： 一个page最大16KB，如果一条记录大于16KB该怎么办
Answer：会存在溢出页，如果还存不下，会连续存储在溢出页，page内有一个指针指向溢出页的地址，在查询的时候一并查询出。

数据：

  • 顺序保证：
    • 逻辑连续，在一条数据内有一个NextRecord记录下一条数据的指针。

页内记录维护（顺序保证/插入策略/页内查询）

  • 顺序保证：
    • 物理顺序：类似数组，插入2之后插入1，要先让2位移2，再插入1。
    • 逻辑顺序：在一条数据内有一个NextRecord记录下一条数据的指针。
    • 类似于数组与链表的区别，数组的插入性能差
  • 插入策略：
    • 选择使用：
      • 自由空间列表
      • 未使用空间列表

  • 页内查询：

    • 遍历
    • 二分查找

      mysql内存管理（页面管理/页面淘汰/LRU）：全表扫描对内存有什么影响？如何避免热数据被淘汰？没有空闲页怎么办

  • 预分配内存

    • 内存池（BufferPool）：在内存里缓存一些数据，方便多次重复使用数据，当mysql刚被创建出来时，
      • 要在内存池中先创建一些空闲页 freelist
        • 有新数据进来先去找freelist
      • 已经被使用的页叫数据页 lrulist
        • 当空闲页不足，要从lru中淘汰
      • 已经对数据修改了的页叫脏页 flushlist，但还是数据页，只是被修改过了，是数据页中的脏页
        • 通常使用脏页进行落盘（持久化）
    • 内存页面管理
      • 页面映射
      • 页面数据管理
    • 数据淘汰
      • 内存页都被使用
        • 从lru中进行数据淘汰
      • 需要加载新数据
  • 数据加载单元
  • 数据内外存交换
  • 全表扫描对内存的影响：
    • 会导致缓冲池中的很多热点数据进行淘汰
    • 解决方案：mysql采取了两个lru链表，一个热数据链表占（new）5/8，一个冷数据链表（old）3/8，新数据先进入冷数据链表。
    • 冷热区如何进行访问交换：
      • 冷数据区有一个访问记录次数，如果两次访问的时间小于一个值的，就不会进入到热区。
      • 冷区有访问会移动到冷区表头，热区表不移动（为了减少移动次数）
  • 如何避免热数据被淘汰：
    • 双LRU List 分冷热区

  • 没有空闲页怎么办：


    • Mysql内存管理——LRU

  • 页面装载

    • 磁盘数据读取到内存
    • 数据来了先从Free list中取
    • free list中取 > LRU中淘汰 > LRU Flush
  • 页面淘汰
    • LRU尾部淘汰
    • Flush LRU淘汰
      • lru链表中将第一个脏页flush并释放
      • 刷新后将新的数据直接放在LRU尾部还是FreeList？
  • 位置移动
    • old到new
      • innodb_old_blocks_time old区存活时间大于这个值，就有机会进入到new区
    • new到old
  • lru_new操作
    • mysql设计思路：减少移动次数
    • 有两个重要参考
      • free_page_clock：buffer pool 淘汰页数
      • LRU_new长度1/4
    • 当前free_page_clock - 上次移动到Header时free_page_clock > lru_new长度的1/4

mysql索引为什么采用B+树结构

b+树的结点存的是page，数据只存在叶子节点，非叶子节点存索引值。页与页之间物理有序，行与行之间逻辑有序。 ·

如何根据记录数去估算索引树的高度？

BigINT类型主键3层可以存储约10E条数据
16KB/(8B(key) + 8B(指针)) =1K
10^3 10^3 10^3 = 10E
32字节主键3层可以存储6400W

主键索引、聚簇索引、二级索引与联合索引具备哪些特点？

  • 聚簇索引
    • 等于InnoDB的主键索引索引和记录在一颗索引树上就是聚簇索引，没有主键就选择唯一键，没有唯一键就会创建一个默认RowID为主键
  • 二级索引
    • 非主键索引都存在二级索引，先从二级索引找，找到的数据再去聚簇索引找。这个过程称为回表
    • 叶子中存储主键值
    • 一次查询需要走两遍索引*可以优化
    • 主键大小会影响所有索引的大小

  • 联合索引（特殊的二级索引）

    • key由多个字段组成
    • 最左前缀原则
      • 如果不是按照最左开始查找，无法使用索引
      • 不能跳过中间列
      • 某列使用范围查询，后面的列不能使用索引
      • 索引覆盖
    • 一个索引只创建一棵树
    • 按第一列排序，第一列相同按第二列排序

      索引下推ICP、覆盖索引都带来了哪些优势

      一个where，只会直接用到一个二级索引树

  • 索引下推（ICP）：

    • where a = 10 & b > 20 & c = 30 & d = 40
      • a = 10: index key
      • b > 20: index key 遇到大于小于、函数 innodb停止过滤之后的key
      • c = 30: index filter
      • d = 40: table filter
      • table filter的处理在server层，index key的处理在innodb中。本来index filter是放在server层过滤，现在下推到Innodb层进行过滤。

        是否知道联合索引对于order by以及order by的意义

        MYSQL事务基本概念

  • 事务特性

    • A（atomicity原子性）：全部成功或全部失败
    • I（Isolation隔离性）：并行事务间互不干扰 ——— mvcc undolog
    • D（Durability持久性）：事务提交后，永久生效 ——— mvcc undolog
    • C（Consistency一致性）：通过AID保证 -redo log
  • 并发问题
    • 脏读（dirty read）：读取到未提交的数据
    • 不可重复读（non-repeatable read）：两次读取结果不同
    • 幻读（phantom read）：select操作得到的结果所表征的数据状态无法支持后续的业务操作

  • 隔离级别

    • Read Uncommitted（读取未提交内容）：最低隔离级别，会读取到其他事务未提交的数据，脏读；
      • 读到其他事务还没提交的数据，基本不会遇到这种情况，低于一般sql的默认事务级别
    • Read Committed（读取提交内容）：事务过程中可以读取到其他事务已经提交的数据，不可重复读；
      • 两次select对同一张表同一条记录的结果不一样
      • 因为另一个事务对这条进行了update
    • Repeatable Read（可重复读）：每次读取相同结果集，不管其他事务是否提交，幻读；
      • 一张表的多条记录发生了insert 或 delete
    • Serializable（串行化）：事务排队，隔离级别最高，性能最差；

      MYSQL事务实现原理

  • MVCC

    • 多版本并发控制
      • 数据发生变化（insert，delete）会留下历史变更
    • 解决读-写冲突
      • 读不加锁，写才加锁，但是读也不能读到正在更改的数据，所以会读到历史版本的数据
        • 当前读：增删改的时候读到的数据
        • 快照读：select的时候基本读到的数据，读的地方在undo log
          • 单select读的是快照读
          • select for update读的是当前读
    • 隐藏列
    • 可见性判断
      • 创建快照这一刻，还未提交事务
      • 创建快照之后创建的事务
    • read view
      • 快照读 活跃事务列表
      • 列表中最小事务ID
      • 列表中最大事务ID
    • image.png
  • undo log
    • 为了保证事务的回滚，可以找到上一条的状态
    • 保证事务原子性
    • 实现数据多版本
    • delete undo log：用于回滚，提交后清理
    • update undo log：历史数据写入undo log，用于回滚，同时实现快照读，不能随便删除

  • redo log

    • 实现事务持久性
    • 记录修改
    • 用于异常恢复
    • 循环写文件
      • write pos:写入位置
      • check point：刷盘位置
      • check point -> write post：待落盘数据
    • 写入流程
      • 记录页的修改，状态为prepare
      • 事务提交，将事务记录为commit状态
      • image.png
      • 刷盘时机
        • innodb_flush_log_at_trx_commit
        • image.png
    • 意义
      • 提交小，记录页的修改，比写入页代价低
      • 末尾追加，随机写变顺序写，发生改变的页不固定

        什么情况下会导致索引失效

        索引失效问题分析：

  • A=XX OR B=XX

    • 5.1开始，引入Index merge
  • 隐式类型转换
    • where

  • 索引列包含计算

    • where name - 20 = 30

      在海量数据与高并发场景下如何进行主键设计？

      索引使用优化分析

  • 存储空间

  • 主键选择
    • 自增主键，顺序写入，效率高：写入磁盘利用率高，查询走两级索引
    • 随机主键，结点分裂、数据移动：写入磁盘利用率低，存在查询走两级索引；
    • 业务主键：写入、查询磁盘利用率都高，可以使用一级索引
    • 联合主键：影响索引大小，不宜维护，不建议使用
  • 联合索引使用
    • 按索引区分度排序
    • 覆盖索引
    • 索引下推
  • 字符串索引
    • 设置合理长度
    • 不支持%开头的模糊查询

Mysql库表设计实践

  • 表必须有主键，建议使用业务主键
  • 单张表中索引数量不超过5个
  • 单个索引字段数不超过5个
  • 是否分表
    • 建议单张表不超过1KW
  • 分表方式
    • 取模：存储均匀&访问均匀
    • 按时间：冷热库

  • 分库

    • 按业务垂直分
    • 水平查分多个库

      Mysql锁实现原理拆解以及设计深度剖析

  • 锁粒度

    • 行级锁
      • 作用在索引上
      • 聚簇索引&&二级索引
    • 记录锁
    • 间隙锁 — 解决了幻读
      • 解决可重复读模式下的幻读问题
      • gap锁不是加在记录上的
      • gap锁锁住的位置
    • 临键锁（next-key锁）相当于记录锁+间隙锁
    • 表级锁
      • 全表扫描“表锁”，RC中给所有行+锁，RR中给所有行和间隙+锁，效果与表锁一样，但不是表锁
  • 类型
    • 共享锁（S）
      • 读锁，可以同时被多个事务获取，阻止其他事务对记录的修改
    • 排他锁（X）
      • 写锁，只能被一个事务获取，允许获得锁的事务修改数据
      • question：所有当前读加排他锁，都有哪些是当前读？
        • select for update
        • update
        • delete

  • InnoDB加锁过程

    • 死锁
      • T1: UPDATE t_user SET xx=xx WHERE name = ‘f’;
      • T2: SELECT * FROM t_user WHERE age > 33 FOR UPDATE;
      • image.png
      • T2要锁uid=130，T1要锁uid=120，锁是在索引上的索引项加锁，两个都要锁uid，所以发生死锁。
      • mysql会选择kill掉影响最小的事务，让另一个事务提交成功，一个事务提交失败。

        企业千亿级海量数据事务处理与分库分表设计方法提炼

        InnoDB是如何实现加行级锁的过程？

  • mysql InnoDB行锁是通过给索引上的索引项加锁来实现的

    • 只有通过索引条件检索数据，InnoDB才能使用行锁，否则，InnoDB将使用表锁
    • 由于mysql的行锁是对索引加锁，不是针对记录加锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。设计应用的时候要注意这一点。
    • 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
    • 即便在条件中使用了索引字段，但是否使用索引来检索数据是由mysql通过判断不同执行计划的代价来决定的，但如果mysql认为全表扫描效率更高，比如对于一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。此外，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正的使用了索引。
  • oracle是通过在数据块中对相应数据行加锁来实现的

    InnoDB中间隙锁是如何解决幻读的？

    间隙锁：

    InnoDB中Next-Key锁的锁定区间是哪些？

    InnoDB出现死锁的原理是什么以及如何解决的？

    MVCC如何实现多版本控制？如何解决读写冲突？

    回滚日志Insert Undo log 和 Update Undo log的区别是什么？

    Undo log 和 ReadView 是如何实现多版本控制与保证事务原子性的？

    Undo log如何清理？为什么InnoDB count(*)这么慢？

    重做日志Redo log如何实现事务持久性？

    checkpoint检查点机制与脏页落盘以及Redo log落盘的关系

    Mysql如何进行优雅的库表设计？

    在海量数据与高并发场景下如何优雅的分库分表方案？