Mysql基础知识2 发布于 2025 年 9 月 11 日 全文约 2,559 字 · 预计阅读 9 分钟 * InnoDB（聚集索引） B+树的叶子节点直接存储的数据，MyIsam（非聚集索引） B+ 树叶子节点存储的是数据的地址。 * 聚集索引：叶子节点包含了完整的数据记录（比如：InnoDB）。 * InnoDB（聚集索引）  * MyIsam（非聚集索引）  * 联合索引（索引最左前缀原理）  * Mysql数据库插入数据的时候就排序了，插入的时候排好的序。 * **如果存储引擎是InnoDB**，Mysql数据库查询数据的时候，每次从磁盘加载到内存是16Kb，即为一页（**页结构是InnoDB存储引擎里面的**）。当一行数据不足16Kb的时候就会记载多行直到16Kb，这样就不用在查询一行数据读取一下磁盘（因为一次性加载了多行数据到内存，局部性原理），这样就会节省时间，提高效率。Mysql页图例如下： > **如果主键ID不是自增的，因为ID是需要再插入的时候就要排好序的，此时会发生这种情况：第一页的大小已经达到16Kb，但是再次插入数据的时候主键ID的值要比第一页最后一个ID要小（插入的主键ID应该放在第一页），这时候就会把插入的这条数据放在第一页，并且把之前第一页最后的一条数据 "挤" 到第二页，这样会对性能造成损失，所以建议在创建主键的时候使用整型（int/big int），且主键自增，这样就不会出现 “挤数据” 这种情况。**  **两个页之间指针连接：** 页目录：把 “用户数据区域” 几条数据合并成一个组，每一组里面的起始ID记录下来放在页目录里面，做为页目录里面的一个元素。这样我们在当前页查找数据的时候就不用在 “用户数据区域” 里面一条一条遍历了，直接先在页目录里面查找（二分查找） 我们需要的数据在哪一个组里面，然后在对应的组里面遍历就好了。  如果有很多页，但是我们要查询的数据在后面的页里面，我们此时要遍历页这个链表，这样会对性能造成影响。为了提高性能，我们可以模仿页目录，创建一个记录每一页起始ID的 “目录”，这样我们在查询的时候先在这个 “目录” 里面判断我们查找的数据是在哪一个页里面（二分查找），然后在遍历页里面数据。（这个整个结构就是B+树）  **数据库B+树结构如下图：**  * 如果每一页最多有两条数据，即如下图： > **叶子节点（数据页）存的是整个表的数据。非叶子节点（索引页）起到加速查询的作用。** > ** 聚集索引：索引和数据放在一起 ** > **查询的时候，如果是从第一个叶子节点向后轮询查找的叫做“全表扫描”。如果是从索引查找的叫做“走索引”。**  * 联合索引 =>（**最左前缀原则**） 思考联合索引也把结构做成B+树（叶子节点也存放记录），怎么样？ 虽然这样可以提高查询效率，但是这样会造成的不良结果是：1、数据的冗余（主键B+树叶子节点已经有记录数据了，这里的B+树叶子节点也有数据）。2、增加、更新数据的时候麻烦，因为每一个B+树叶子节点里面对应的数据都要添加或者更新。所以这种结构是不可取的。  ** 优化联合索引** > **经过上面的分析，找到一个折中解决方法。我们可以把叶子节点里面的 数据记录 换成 主键ID。通过联合索引查找到对应的主键ID，然后再利用主键索引来查找对应的数据记录。（这种通过查找到主键ID，然后再拿着主键ID，在主键索引中查找数据的方法 叫做“回表”）。**  * Innodb 中的 Buffer Pool缓冲池 Buffer Pool 缓冲池 是 Innodb中来缓存查询记录的一块区域（Buffer Pool是在内存中，默认是128M），Buffer Pool 缓冲池中的每一个页（16Kb）中包含一个控制块。 脏数据：当我们更新数据的时候，先更新Buffer Pool缓冲池页里面的数据，后续会有专门的线程定时去把新的数据同步到磁盘。 ** Buffer Pool缓冲池中的链表：** 1. free链表：链表中的每个元素指向的是Buffer Pool缓冲池中空白的页  2. flush链表：链表中的每一个元素指向的是脏数据页  **lru链表**：最频繁使用的页在LRU链表的最前端，而最少使用的页在LRU链表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU链表尾端的页。LRU链表分为热数据区和冷数据区，**冷数据区占了总链表的37%**。冷数据区是不常访问的缓存页，热数据区是经常访问的缓存页。lru链表作用：用来淘汰不常被访问的缓存页。 在Innodb引擎中，有一个**midpoint位置**。 新读取到的页，虽然是最新访问的页，但并不直接放到LRU链表的首部，而是放到LRU链表的midpoint位置。midpoint位置是通过参数innodb_old_blocks_pct来控制的。表示新读取的页插入到LRU链表尾端的37%的位置。在Innodb中，把midpoint之后的链表称为LRU冷数据链表，之前的链表称为LRU热数据链表。可以简单的理解为LRU热数据链表中的页都是活跃的热点数据。 **冷数据区域内的页如果被访问了两次，且两次访问的时间差大于1秒，这时候这个页就会从冷数据区域移动到热数据区域。限制两次访问的时间差大于1秒是因为：一个页里面可以放置很多条数据，当我们从磁盘中读数据写入当前页的时候，当前页在很短的时间内会被多次访问，此时我们需要给定一个时间限制。**  * redolog（是InnoDB特有的）-》是物理日志，记录的是“在某个数据页上做了什么修改” **redolog作为异常宕机或者介质故障后的数据恢复使用** 当我们更新数据的时候： > 1、我们先修改buffer pool里面的数据 --》脏页 > 2、update语句生成 redo log > 3、redo log持久化 (事务提交的时候持久化) > 4、binlog 和 undolog持久化 > 5、修改成功 当我们在更新完数据以后，还没来得及把更新的数据持久化到磁盘，如果Mysql挂掉了；我们再次重启之后，如果我们查询之前没持久化的那条数据的时候，Mysql会把磁盘里面的老数据 和 redo log合并起来返回给我们。 * binlog(Mysql本身拥有的)-》是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”（可以简单理解为记录的SQL语句） **binlog可以作为恢复数据使用，主从复制搭建** * undolog(是InnoDB特有的) 数据库事务开始之前，会将要修改的记录放到Undo日志里，当事务回滚时或者数据库崩溃时，可以利用UndoLog撤销未提交事务对数据库产生的影响。 在MySQL中，undo log日志的作用主要有两个： > 1、提供回滚操作【undo log实现事务的原子性】 > 2、提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】 ------------ ### 分库分表 #### 分库分表中间件 > proxy代理层：mysql-proxy、mycat、atlas #### 分库分表带来的问题： > 1、ACID 分布式事务 > 2、维护成本 > 3、跨库查询问题 > 4、分布式全局唯一ID #### 解决方法 （可以使用分库分表中间件：proxy代理层：mysql-proxy、mycat、atlas） ------------ ##### 垂直拆分的特点：（结构拆分） > 1、每个库（表）的结构都不一样 > 2、每个库（表）的数据都（至少有一列）一样 > 3、每个库（表）的并集是全量数据 优点： > 1、拆分后业务清晰(专库专用按业务拆分); > 2、数据维护简单、按业务不同放到不同机器上 缺点： > 1、如界单表的数据量大、写读压力大 > 2、受某种业务来决定、或者被限制。也就是说一个业务往往会影响到数据库的瓶颈（性能问题) > 3、部分业务无法关联 join、只能通过程序接口去调用，提供了开发复杂度。(商品信息、订单信息、会员信息) ------------ ##### 水平拆分的特点：（内容拆分） > 1、每个库（表）的结构都一样 > 2、每个库（表）的数据都不一样 > 3、每个库（表）的并集是全量数据 优点： > 1、单库（表）的数据保持在一定的量（减少），有助于性能提高 > 2、提高了系统的稳定性和负载能力 > 3、切分的表的结构相同、程序改造较少 缺点： > 1、数据的扩容很有难度维护量大 > 2、拆分规则很难抽象出来 > 3、分片事务的一致性的问题部分业务无法关联 join、只能通过程序接口去调用 ------------ #### 分库 **垂直拆分**  **水平拆分**  ------------ #### 分表 **垂直拆分**  **水平拆分**  Mysql基础知识2 https://blog.gamehomexx.asia/index.php/archives/105/ 无责任编辑 joewang 发布时间 2025 年 9 月 11 日 最后编辑时间 2025 年 9 月 11 日 版权协议 CC BY-NC-SA 4.0 Mysql 广告 广告位招租 联系站长投放广告 了解更多 → 分享 文章目录 × Mysql基础知识2 ← 上一篇ThinkPHP6智障坑 下一篇 →Mysql基础知识 发表评论 取消回复 提交评论 评论 暂无评论
* InnoDB（聚集索引） B+树的叶子节点直接存储的数据，MyIsam（非聚集索引） B+ 树叶子节点存储的是数据的地址。 * 聚集索引：叶子节点包含了完整的数据记录（比如：InnoDB）。 * InnoDB（聚集索引）  * MyIsam（非聚集索引）  * 联合索引（索引最左前缀原理）  * Mysql数据库插入数据的时候就排序了，插入的时候排好的序。 * **如果存储引擎是InnoDB**，Mysql数据库查询数据的时候，每次从磁盘加载到内存是16Kb，即为一页（**页结构是InnoDB存储引擎里面的**）。当一行数据不足16Kb的时候就会记载多行直到16Kb，这样就不用在查询一行数据读取一下磁盘（因为一次性加载了多行数据到内存，局部性原理），这样就会节省时间，提高效率。Mysql页图例如下： > **如果主键ID不是自增的，因为ID是需要再插入的时候就要排好序的，此时会发生这种情况：第一页的大小已经达到16Kb，但是再次插入数据的时候主键ID的值要比第一页最后一个ID要小（插入的主键ID应该放在第一页），这时候就会把插入的这条数据放在第一页，并且把之前第一页最后的一条数据 "挤" 到第二页，这样会对性能造成损失，所以建议在创建主键的时候使用整型（int/big int），且主键自增，这样就不会出现 “挤数据” 这种情况。**  **两个页之间指针连接：** 页目录：把 “用户数据区域” 几条数据合并成一个组，每一组里面的起始ID记录下来放在页目录里面，做为页目录里面的一个元素。这样我们在当前页查找数据的时候就不用在 “用户数据区域” 里面一条一条遍历了，直接先在页目录里面查找（二分查找） 我们需要的数据在哪一个组里面，然后在对应的组里面遍历就好了。  如果有很多页，但是我们要查询的数据在后面的页里面，我们此时要遍历页这个链表，这样会对性能造成影响。为了提高性能，我们可以模仿页目录，创建一个记录每一页起始ID的 “目录”，这样我们在查询的时候先在这个 “目录” 里面判断我们查找的数据是在哪一个页里面（二分查找），然后在遍历页里面数据。（这个整个结构就是B+树）  **数据库B+树结构如下图：**  * 如果每一页最多有两条数据，即如下图： > **叶子节点（数据页）存的是整个表的数据。非叶子节点（索引页）起到加速查询的作用。** > ** 聚集索引：索引和数据放在一起 ** > **查询的时候，如果是从第一个叶子节点向后轮询查找的叫做“全表扫描”。如果是从索引查找的叫做“走索引”。**  * 联合索引 =>（**最左前缀原则**） 思考联合索引也把结构做成B+树（叶子节点也存放记录），怎么样？ 虽然这样可以提高查询效率，但是这样会造成的不良结果是：1、数据的冗余（主键B+树叶子节点已经有记录数据了，这里的B+树叶子节点也有数据）。2、增加、更新数据的时候麻烦，因为每一个B+树叶子节点里面对应的数据都要添加或者更新。所以这种结构是不可取的。  ** 优化联合索引** > **经过上面的分析，找到一个折中解决方法。我们可以把叶子节点里面的 数据记录 换成 主键ID。通过联合索引查找到对应的主键ID，然后再利用主键索引来查找对应的数据记录。（这种通过查找到主键ID，然后再拿着主键ID，在主键索引中查找数据的方法 叫做“回表”）。**  * Innodb 中的 Buffer Pool缓冲池 Buffer Pool 缓冲池 是 Innodb中来缓存查询记录的一块区域（Buffer Pool是在内存中，默认是128M），Buffer Pool 缓冲池中的每一个页（16Kb）中包含一个控制块。 脏数据：当我们更新数据的时候，先更新Buffer Pool缓冲池页里面的数据，后续会有专门的线程定时去把新的数据同步到磁盘。 ** Buffer Pool缓冲池中的链表：** 1. free链表：链表中的每个元素指向的是Buffer Pool缓冲池中空白的页  2. flush链表：链表中的每一个元素指向的是脏数据页  **lru链表**：最频繁使用的页在LRU链表的最前端，而最少使用的页在LRU链表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU链表尾端的页。LRU链表分为热数据区和冷数据区，**冷数据区占了总链表的37%**。冷数据区是不常访问的缓存页，热数据区是经常访问的缓存页。lru链表作用：用来淘汰不常被访问的缓存页。 在Innodb引擎中，有一个**midpoint位置**。 新读取到的页，虽然是最新访问的页，但并不直接放到LRU链表的首部，而是放到LRU链表的midpoint位置。midpoint位置是通过参数innodb_old_blocks_pct来控制的。表示新读取的页插入到LRU链表尾端的37%的位置。在Innodb中，把midpoint之后的链表称为LRU冷数据链表，之前的链表称为LRU热数据链表。可以简单的理解为LRU热数据链表中的页都是活跃的热点数据。 **冷数据区域内的页如果被访问了两次，且两次访问的时间差大于1秒，这时候这个页就会从冷数据区域移动到热数据区域。限制两次访问的时间差大于1秒是因为：一个页里面可以放置很多条数据，当我们从磁盘中读数据写入当前页的时候，当前页在很短的时间内会被多次访问，此时我们需要给定一个时间限制。**  * redolog（是InnoDB特有的）-》是物理日志，记录的是“在某个数据页上做了什么修改” **redolog作为异常宕机或者介质故障后的数据恢复使用** 当我们更新数据的时候： > 1、我们先修改buffer pool里面的数据 --》脏页 > 2、update语句生成 redo log > 3、redo log持久化 (事务提交的时候持久化) > 4、binlog 和 undolog持久化 > 5、修改成功 当我们在更新完数据以后，还没来得及把更新的数据持久化到磁盘，如果Mysql挂掉了；我们再次重启之后，如果我们查询之前没持久化的那条数据的时候，Mysql会把磁盘里面的老数据 和 redo log合并起来返回给我们。 * binlog(Mysql本身拥有的)-》是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”（可以简单理解为记录的SQL语句） **binlog可以作为恢复数据使用，主从复制搭建** * undolog(是InnoDB特有的) 数据库事务开始之前，会将要修改的记录放到Undo日志里，当事务回滚时或者数据库崩溃时，可以利用UndoLog撤销未提交事务对数据库产生的影响。 在MySQL中，undo log日志的作用主要有两个： > 1、提供回滚操作【undo log实现事务的原子性】 > 2、提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】 ------------ ### 分库分表 #### 分库分表中间件 > proxy代理层：mysql-proxy、mycat、atlas #### 分库分表带来的问题： > 1、ACID 分布式事务 > 2、维护成本 > 3、跨库查询问题 > 4、分布式全局唯一ID #### 解决方法 （可以使用分库分表中间件：proxy代理层：mysql-proxy、mycat、atlas） ------------ ##### 垂直拆分的特点：（结构拆分） > 1、每个库（表）的结构都不一样 > 2、每个库（表）的数据都（至少有一列）一样 > 3、每个库（表）的并集是全量数据 优点： > 1、拆分后业务清晰(专库专用按业务拆分); > 2、数据维护简单、按业务不同放到不同机器上 缺点： > 1、如界单表的数据量大、写读压力大 > 2、受某种业务来决定、或者被限制。也就是说一个业务往往会影响到数据库的瓶颈（性能问题) > 3、部分业务无法关联 join、只能通过程序接口去调用，提供了开发复杂度。(商品信息、订单信息、会员信息) ------------ ##### 水平拆分的特点：（内容拆分） > 1、每个库（表）的结构都一样 > 2、每个库（表）的数据都不一样 > 3、每个库（表）的并集是全量数据 优点： > 1、单库（表）的数据保持在一定的量（减少），有助于性能提高 > 2、提高了系统的稳定性和负载能力 > 3、切分的表的结构相同、程序改造较少 缺点： > 1、数据的扩容很有难度维护量大 > 2、拆分规则很难抽象出来 > 3、分片事务的一致性的问题部分业务无法关联 join、只能通过程序接口去调用 ------------ #### 分库 **垂直拆分**  **水平拆分**  ------------ #### 分表 **垂直拆分**  **水平拆分** 
评论 暂无评论