1. MySQL 深入总结

余召东 发表于 2024-11-22 15:44:14

# mysql 集群架构 1. master-slave 架构模式 高可用： master 挂了，slave 可提升为 master，对位提供服务。 2. 复制模式 异步复制、半同步复制、全同步复制。 异步复制：不需要等待 slave 将 binlog 日志同步到 relay log 中，就提交事务。 半同步复制：需要等待一个 slave 将 binlog 日志同步到 relay log 中，就提交事务。 全同步复制：需要等待所有 slave 将 binlog 日志同步到 relay log 中，就提交事务。 3. 复制流程 3.1 从服务器连接主服务器： 从服务器上的 IO 线程通过网络连接主动连接到主服务器。 从服务器向主服务器发送一个复制请求，该请求包含从服务器所期望的开始位置（binlog 文件和位置）。 3.2 主服务器响应连接请求： 主服务器在接收到从服务器的复制请求后，会为该从服务器启动一个 binlog dump 线程。 binlog dump 线程从指定的 binlog 文件和位置开始读取二进制日志。 3.3 主服务器发送 binlog 数据： binlog dump 线程将读取到的二进制日志事件发送给从服务器。 从服务器的 IO 线程接收这些 binlog 数据，并将其写入从服务器的中继日志（Relay Log）。 3.4 从服务器应用 binlog 数据： 从服务器的 SQL 线程从中继日志中读取事件，并逐个将其应用到从服务器的数据库中。 总示意图： 从服务器: 1. IO 线程 --------------> 向主服务器发起连接请求 （启动连接） 主服务器: 2. Binlog Dump 线程 -----> 响应连接请求，发送 binlog 数据 （传输二进制日志） 从服务器: 3. IO 线程 <-------------- 接收 binlog 数据，写入中继日志 4. SQL 线程 -------------> 从中继日志中读取并应用日志 （应用变化） 总结： 尽管主服务器的 binlog dump 线程中扮演了发送日志的角色，但这一过程的启动和驱动是由从服务器主动发起的。 # mysql 内部架构 1. mysql server 层 + 存储引擎层 2. InnoDB 和 MyISAM的区别。 锁的级别：MyISAM 采用表级锁定，一次只能有一个写操作。InnoDB 采用行级锁定，这在高并发写操作的情况下表现得较好。 事务：InnoDB 支持事务，确保数据的一致性和完整性。MyISAM 不支持事务。 # 事务 1. 定义 - what 事务（Transaction）是一个数据库操作序列，其中的操作要么全部成功，要么全部失败，保障了数据的完整性与一致性。 2. 使用场景 - why 保证业务场景的一致性。 3. 事务特性（设计原则）：ACID - how 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，即事务是一个不可分割的工作单元。 一致性（Consistency）：执行事务前后，数据库都必须保持一致状态，不允许出现违反数据库约束的状态。比如外键约束。 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，各并发事务之间相互独立。 持久性（Durability）：一旦事务提交，其结果必须永久保存到数据库中，即使系统发生故障也不能丢失。 4. 事务的隔离级别 - how 读未提交（READ UNCOMMITTED）：最低隔离级别，允许看到未提交的事务，这会导致“脏读”。 读已提交（READ COMMITTED）：一个事务只能看到已经提交的事务，这会导致“不可重复读”。 可重复读（REPEATABLE READ）：默认的隔离级别，确保一个事务看到的一致数据快照，避免“不可重复读”。 串行化（SERIALIZABLE）：最高隔离级别，确保事务完全串行化地执行，避免“幻读”。 5. innodb 存储引擎各个隔离级别的实现原理 - how RC 和 RR 隔离级别，采用的是 MVCC + 一致性读视图实现，提高了读写并发执行。详见：https://www.cnblogs.com/DengGao/p/mysql.html RC 隔离级别，每次读的时候，重新生成一份读视图，而 RR 隔离级别，复用第一次生成的读视图。 # 索引 1. 定义 - what 2. 使用场景 - why 加快查询。 3. 分类 - how 一级索引、二级索引 4. 实现原理 - how Innodb 存储引擎使用的是 B+ 树。 因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。 # 锁 1. 定义 - what 2. 使用场景 - why 使用场景：防止并发，产生脏数据。 3. 分类 - how 根据锁粒度不同，分为表锁、行锁。粒度越小，并发越高。 控制并发程度不同，分为共享锁、排它锁。排它锁和共享锁、排它锁和排它锁不可以并发执行。 行锁的具体实现：行锁、间隙锁、next-key 锁。 4. 对于 mysql innodb 存储引擎，锁的具体使用场景如下 - how 1. 事务里的【写】操作（insert、update、delete），四种隔离级别，都会加排他锁。 2. 事务里的【当前读】操作，四种隔离级别，都会加锁。加锁如下： select * from table where ? lock in share mode; 加共享锁。 select * from table where ? for update; 加排他锁。 3. 事务里的【快照读】操作，前三种隔离级别，不会加锁，最后一种隔离级别，会加共享锁。简单的 select 操作，就是快照读。select * from table where ?; 4. 事务里的锁都是同一时刻释放的，就是事务提交或者回滚的那一刻。 5. 排它锁和共享锁、排它锁和排它锁是不可以并发执行的。 6. 在读未提交、读已提交和可重复度的隔离级别下，【快照读】是不加锁的，因此并发性能会高不少。 7. 在RC、RR隔离级别下，为了防止事务等待，我们近可能做到： 7.1 加锁时间要尽可能短。因此使用小事务。 7.2 加锁范围尽可能小。因此where条件尽可能锁定少量行。 # 死锁 1. 死锁产生的原因 两个事务循环等待对方持有的锁。比如事务1持有锁A，等待锁B；事务2持有锁B，等待锁A。 2. 死锁的解决方案 2.1 预防死锁 小事务：保持事务尽可能短，即减少事务在同一时间段内锁定的资源数量，降低死锁的概率。 按一致的顺序访问资源：确保所有事务按照相同的顺序请求锁定资源，避免循环等待。 设置合理的锁粒度：避免一次性锁定太多的资源，锁的粒度要适当，不要过大。 2.2 检测与处理死锁 当出现死锁，回滚其中一个事务。 2.3 等待超时。 加锁超时后，自动回滚。 # mysql 文件组成以及IO操作流程 https://www.cnblogs.com/DengGao/p/12734775.html 1. redo log：innodb 存储引擎独有。写日志优先。物理日志。 1.1 作用：保证数据持久性、原子性、崩溃恢复。 1.2 相较于直接写数据的方式，存在的意义： 提高性能：写 redo log 比直接写数据文件要快，因为 redo log 采用顺序写入。这样就能加速事务的提交，提升数据库的整体性能。 2. undo log：innodb 存储引擎独有。undo log 并不存储当前数据，存储的是修改前的数据快照。逻辑日志。 2.1 作用：回滚、用于RC/RR隔离级别的多版本并发控制，进而提高并发性。 3. bin log：mysql server 日志。逻辑日志。 3.1 作用：主从复制。 3.2 两种模式： statement格式：SQL语句。 row格式：行内容（记两条，更新前和更新后）。推荐。 4. innodb 为什么采取两阶段提交。redo log 的 prepare阶段和 commit 阶段。 确保两个日志的一致性，从而保证分布式场景下，主从一致性。 如果 redo log 直接提交，那么事务就不能回滚了。binlog写入失败，会导致从库与主库的数据不一致。 # 总结 1. 尽可能不使用大事务的原因： 影响响应时间：大事务的执行时间较长，会影响系统的整体响应时间，增加事务等待时间，降低用户体验。 锁定资源时间长：大事务需要长时间持有锁，这可能导致并发事务等待锁，从而引发锁争用和死锁问题，影响系统的并发性能和吞吐量。 回滚代价高：如果大事务中途出现错误或需要回滚，这将导致大量的回滚操作，产生极大的系统开销。这不仅影响性能，还可能导致更多的资源争用问题。 日志和内存开销大：事务需要记录日志以保证数据的一致性和持久性。大事务会占用更多的日志空间和内存资源，可能导致数据库的性能下降。 2. 一条 sql 是如何执行的。（结合 mysql 机构、索引、锁、事务等信息综合阐述） 分析器（词法分析、语法分析） ->优化器（寻找适合的索引） -> 执行器（调用存储引擎的接口） -> 写undolog（在修改数据之前，记录 undo log 以用于事务回滚。）-> 变更 buffer pool 中的数据 -> 更新 redo log (prepare 阶段) -> 写入 binlog -> 写入 redo log (commit 阶段) 3. innodb 存储引擎的 buffer pool 是干啥的 读取数据时主要依赖于缓冲池（Buffer Pool），以提高读取性能。下面详细介绍这一过程： 3.1 检查 Buffer Pool。 当执行一个读取操作时，InnoDB 存储引擎首先会检查缓冲池（Buffer Pool）中是否已经有需要的数据页。缓冲池是一个内存区域，用于缓存经常使用的数据页和索引页。 命中缓冲池：如果数据页已经存在于缓冲池中，即发生缓冲池命中（Buffer Pool Hit），则直接从缓冲池中读取该数据。这种方式最快，因为数据已经在内存中，避免了磁盘 I/O 操作。 未命中缓冲池：如果数据页不在缓冲池中，即未命中缓冲池（Buffer Pool Miss），则需要进行以下步骤： 3.2 从磁盘读取数据页 加载数据页： InnoDB 将从磁盘上的数据文件中加载所需的数据页到缓冲池中。这涉及一次磁盘 I/O 操作，通常会比内存访问慢得多。 缓存到 Buffer Pool：一旦数据页加载进缓冲池，InnoDB 会将其缓存下来，以便后续的读取操作可以直接从缓冲池中获取数据，减少磁盘 I/O 操作。这一机制提升了系统的整体性能。 读取脏页：在一些情况下，数据页可能在磁盘上与缓冲池中的版本不一致。特别是在存在未刷新的脏页（Dirty Pages）时，读取操作总是读取缓存池中的最新版本数据，即使这些数据还没有被写回磁盘。 3.3 LRU 链表管理 缓冲池中使用类似 LRU（Least Recently Used，最近最少使用）算法的链表来管理数据页的缓存策略。最近使用的数据页会被保存在链表的前端，而较少使用的页会被移到后端。当缓冲池满时，较少使用的数据页将被移出，腾出空间给新加载的数据页。 通过上面的步骤和机制，InnoDB 大幅度提高了数据读取的性能和效率。缓冲池在这个过程中起到了关键作用，它不仅减少了磁盘 I/O 操作的频率，还保证了读取操作能够迅速获得所需的数据。 4. 如果查询条件获取的数据量过多，对数据库性能造成影响，甚至 java 进程 oom。怎么处理？ 根据查询条件，分场景处理。 1. 如果是 in 查询，比如：select * from 分班记录 where userId in ()。那么我们可以分批次查询，降低每批次 in 条件里面的元素数量。 2. 如果是 = 查询，比如：select * from 分班记录 where teacherId = xxx。处理方式如下： 2.1 前端展示场景，可以分页。存在深度分页的问题，如何解决？解决不了，只能从业务上面解决。 深度分页，可能是个伪需求。超过N页，直接拒绝请求。 业务场景，筛选条件做限制。也就是说，where 条件再增加一个字段，建立联合索引，刷选出足够少的数据量。 2.2 一个清洗数据的临时 job。分批次按照id进行游标查询，满足条件的记录，批量更新。 2.3 查询 pipe 库，隔离对核心业务的影响。 2.4 mysql 分库。 此分库方式，数据是随机存储到数据库里面，并没有分库的key路由。查询的时候，从多个数据库聚合数据。类似分布式数据库。 2.5 使用分布式数据库。 5. 如果并发过多，对数据库性能造成影响。怎么处理？ 1. mysql 分库。 1.1 此分库方式，数据是按照key路由到数据库。查询的时候，也是按照路由的key找到数据库查询。 1.2 限制并发。比如限流。 2. 多个 slave，读写分离。 6. mysql 表行数多（比如1亿），有性能问题吗？如果有，如何解决呢 1. 存在性能问题。表是树状结构，数量越大，层级越多。 2. 可以采用分表的方式。 7. 有个疑问，对于二级索引，如果叶子节点一个键值对应多个主键，这多个主键是采用什么数据结构存储的呢?
来源:https://www.cnblogs.com/DengGao/p/18562438
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！

页: [1]

翼度科技's Archiver

1. MySQL 深入总结