Skip to content
Moatkon

MySQL

1. 如何去分析一个MySQL语句

使用Explain解释计划。

Explain解释计划有以下字段:

  • id: 表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行

  • select_type: 表示 select 查询的类型，主要是用于区分各种复杂的查询，例如: 普通查询、联合查询、子查询等

  • table: 输出行所引用的表

  • partitions: 使用的哪个分区，需要结合表分区才可以看到

  • type: 连接类型(“join type”),查询使用了何种类型，它在 SQL优化中是一个非常重要的指标,以下性能从好到坏依次是: system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    • system: 系统表，少量数据，往往不需要进行磁盘IO
    • const: 常量连接
    • eq_ref: 主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
    • ref: 非主键非唯一索引等值扫描
    • range: 范围扫描
    • index: 索引树扫描
    • ALL: 全表扫描(full table scan)

    system > const > eq_ref > ref > range > index > ALL

  • possible_keys: 表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。可能使用到的索引

  • key: 区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL. 实际的索引

  • key_len: 表示查询用到的索引长度(字节数)，原则上长度越短越好

  • ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数: 表示查询用到的索引长度(字节数)，原则上长度越短越好

  • rows: 以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数. 只是一个估算值

  • filtered: 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

  • Extra: 不适合在其他列中显示的信息,都显示在这，例如: using file sort ，using where， using join buffer，using index等

    using file sort: 如果索引不能用于满足 ORDER BY 子句，MySQL 将执行 filesort 操作来读取表行并对它们进行排序。 filesort 构成查询执行中的额外排序阶段。如果结果集太大而无法放入内存，文件排序操作将根据需要使用临时磁盘文件。某些类型的查询特别适合完全内存中的文件排序操作。例如，优化器可以使用 filesort 在内存中高效地处理ORDER BY的操作，而无需临时文件。取自官网解释。配置排序缓存大小通过sort_buffer_size,这使得用户可以将 sort_buffer_size 设置为较大的值以加速较大的排序，而不必担心小型排序会使用过多的内存

2. MySQL有哪些索引

  1. 普通索引:只有普通索引可以使用

  2. 唯一索引: 唯一索引的更新就不能使用Change Buffer

  3. 前缀索引: 定义字符串的一部分作为索引。这个索引会回表查完整的信息

  4. 覆盖索引: 简单理解就是【索引的字段就是我们查的字段】,可以直接获取，减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

  5. 联合索引: 联合索引是指对表中的多个列创建的索引。

    • 适合的场景: 由A即B的场景。将A和B建立联合索引，较少回表的操作
    • 最佳实践: 一般都是设计联合索引，很少用单个字段做索引，因为还是要尽可能让索引数量少，避免磁盘占用太多，影响增删改性能
    • 最左匹配原则:
      • 如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)
      • 例如，like ‘moatkon%‘,可以走到索引

  6. 索引下推: 简单理解就是指在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数来提高查询效率。

  7. 主键索引: 属于聚集(簇)索引,也属于稠密索引。聚集索引即索引结构和数据一起存放的索引。

    优点: 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

    缺点:

    1. 更新代价大,如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。
    2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

  8. 非聚集索引: 非聚集(簇)索引即索引结构和数据分开存放的索引,属于稀疏索引。(在聚簇索引之上创建的索引称之为辅助索引(也称非聚集索引),辅助索引访问数据总是需要二次查找,即回表,因为是分开的)

    优点: 更新代价比聚集索引要小。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

    缺点:

    1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
    2. 可能会二次查询(回表),这是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

3. 不走索引的场景

  1. 对字段做了函数计算，就用不上索引了，这是MySQL的规定。因为对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。使用了函数,不走索引
  2. 隐式类型转换不会走索引，因为mysql在隐式转换时会使用转换函数，而使用了函数就不会走索引。使用了函数,不走索引 生产遇到过,merchant_code = 1123,正确应该是merchant_code = '1123'
  3. 隐式字符编码转换不会走索引。使用了函数,不走索引
  4. 在联合索引的场景下，查询条件不满足最左匹配原则。

    MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。MySQL 8.0.13 版本中，对于range查询，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描

  5. 在联合索引下，使用了select * 会导致索引失效

    解决办法: 在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引； 就是select具体的字段

  6. 模糊查询时(like语句),模糊匹配的占位符位于条件的首部
  7. 查询条件使用or关键字。
    • 其中一个字段没有创建索引，则会导致整个查询语句索引失效;只有两边都有索引才走索引
    • or两边为“>”和“<”范围查询时，索引失效
  8. 两列数据做比较，即便两列都创建了索引，索引也会失效。例如: id > age
  9. 查询条件使用is null时正常走索引，使用is not null时，不走索引。

4. 创建索引需要注意哪些事项

  • 被频繁查询的字段 : 我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段 : 被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段 : 索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

    索引的本质是一种数据结构,而数据结构是有序的(这里只针对MySQL的索引哈)

  • 被经常频繁用于连接的字段 : 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

    外键在实际的业务开发场景中,很少会被使用,反正我还没有碰到过,只是在学习的时候会接触到外键

  • 不为 NULL 的字段 : 索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

4.1 MySQL创建索引一定会锁表吗?

在 MySQL 5.6 之前，创建索引时会锁表，所以，在早期 MySQL 版本中一定要在线上慎用，因为创建索引时会导致其他会话阻塞（select 查询命令除外）。

在 MySQL 5.6.7 版本中得到了改变，因为在 MySQL 5.6.7 中引入了 Online DDL 技术（在线 DDL 技术），它允许在创建索引时，不阻塞其他会话（所有的 DML 操作都可以一起并发执行）。

  • ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的并发控制。
    • ALGORITHM=INPLACE,表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）
    • ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML
    • ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式
  • LOCK
    • NONE: 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。
    • EXCLUSIVE: 持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景
    • SHARED: 允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景
    • DEFAULT: 根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值

5. MySQL的索引深入

索引本质上是一种的数据结构

5.1 MySQL索引有哪些数据结构

1. Hash(无序)
  1. 可能会出现Hash冲突
  2. 可以快速的精确查询，但是不支持范围查询,适合等值查询的场景,例如Redis、Memcached这些NoSQL的中间件。

    如果是有序的数据结构，比如【有序数组】，支持范围和等值查询了。但是有序数据适合静态的数据，对于CUD的业务就不适合了，成本高

2. B+树

MySQL为什么要选择B+?

同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

非叶子节点冗余的原因是为了提高范围查找的效率

B+树的优势:

  • 有序: 排序效率高
  • 查询索引时的磁盘IO效率高(结构整体高度低)
  • 范围查询的效率高(有指针)

一个B+树的节点中到底存多少个元素最合适？

(等同于问)B+树中一个节点到底多大合适？

B+树中一个节点为【一页或页的倍数】最为合适。

原因如下:

  • 如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费
  • 如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费

这里详细说了为什么MySQL会采用B+树

3. 其他算法

二叉树: 有序,所以支持范围查询。时间复杂度是O(log(N)),为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了

平衡二叉树: 是否可以使用平衡二叉树做索引?不行! 索引也不只是在内存里面存储的，还是要落盘持久化的。如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。

B树: B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

6. MySQL InnoDB Redo Flush是什么

将redo log更新到磁盘

详细看redo log

6.1 脏页、干净页

脏页: 内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”

理解脏页的前提是达成一致,即脏页是内存页,即最新的数据是在内存中

干净页: 内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“

6.2 什么时候会flush?

  1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

  2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

    如果刷【脏页】一定会写盘，就保证了每个数据页有两种状态:

    1. 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
    2. 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

  3. MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。

  4. MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

6.3 其他

  1. innodb_io_capacity参数设置: 它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试
  2. 刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题

7. 事务的特性: ACID

ACID，是指在可靠数据库管理系统(DBMS)中，事务(transaction)所应该具有的四个特性:

  1. 原子性(Atomicity): 事务中的所有操作要么全部执行成功，要么全部失败回滚，不能只执行其中一部分操作。
  2. 一致性(Consistency): 事务执行前后，数据库的完整性约束没有被破坏，数据总是从一个一致性状态转移到另一个一致性状态。例如，如果一个事务要求将某个账户的金额从 A 转移到 B，那么无论事务是否成功，最终账户 A 和账户 B 的总金额应该保持不变。
  3. 隔离性(Isolation): 事务之间是相互隔离的，每个事务对其他事务的操作是透明的，一个事务的中间结果对其他事务是不可见的。隔离性可以防止并发执行的事务之间产生脏读、不可重复读和幻读等问题。
  4. 持久性(Durability): 事务完成后，对数据库的修改将永久保存在数据库中，即使系统故障也不会丢失。

如何保证事务的四大特性?

以默认的引擎 InnoDB 为例，它保证四大特性的手段分别是:

  1. 原子性是通过 undo log(回滚日志) 来保证的，InnoDB 使用日志(undo log)来记录事务的操作，包括事务开始、修改数据和事务提交等。如果事务执行失败或回滚，InnoDB 可以使用日志来撤销已经执行的操作，确保事务的原子性。
  2. 持久性是通过 redo log (重做日志)来保证的，在事务提交之前，InnoDB 会将事务的修改操作先写入事务日志(redo log)，然后再将数据写入磁盘。即使在系统崩溃或断电的情况下，InnoDB 可以通过重放事务日志来恢复数据，确保事务的持久性。
  3. 隔离性是通过 MVCC(多版本并发控制) 和锁机制来保证的。
  4. 一致性是通过各种约束，如主键、外键、唯一性约束等，加上事务的持久性、原子性和隔离性来保证的。

MySQL中的MVCC机制

Multi-Version Concurrency Control(MVCC)，翻译过来就是多版本并发控制，MVCC是为提高MySQL数据库并发性能的一个重要设计。

同一行数据发生读写请求时，会通过锁来保证数据的一致性。MVCC可以在读写冲突时，让其读数据时通过快照读，而不是当前读，快照读不必加锁。

MySQL的InnoDB实现MVCC，就是在隔离级别为读已提交和可重复读，基于乐观锁理论，通过事务ID和read-view的记录进行比较判断分析数据是否可见，从而使其大部分读操作可以无需加锁，从而提高并发性能。但是在写数据的时候，InnoDB还是需要加排它锁的。

总结，就是用乐观锁代替悲观锁，从而提高并发性能，这就是MVCC

8. 并发事务带来的问题

在数据库执行中，多个并发执行的事务如果涉及到同一份数据的读写就容易出现数据不一致的情况，不一致的异常现象有以下几种。

  1. 脏读(Dirty read): 一个事务读到了另一个未提交事务修改过的数据，这就是脏读
  2. 幻读(Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据，接着另一个并发事务(T2)插入了一些数据时。在随后的查询中，第一个事务(T1)就会发现多了一些原本不存在的记录，就好像发生了幻觉一样(或者变幻,这个词更有利于记住)，所以称为幻读。

    重点在于新增或者删除,重点在于数量的变化。幻读的解决方案通常是使用间隙锁来解决

  3. 不可重复读(Unrepeatable read): 在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，这就是不可重复读。

    例如:有A和B两个事务同时在处理，事务A先开始从数据库中读取数据，然后继续执行代码逻辑处理，在这过程中如果事务B更新了这条数据，并提交了事务，那么当事务A再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。

    重点是修改,是值的变化,不是记录数的变化,记录数的变化是幻读。不可重复读的解决方案通常是使用行锁或者表锁来解决

脏读、不可重复读和幻读有以下的包含关系，如果发生了脏读，那么幻读和不可重复读都有可能出现。

9. 事务隔离级别

SQL 标准根据四种不一致的异常现象，将隔离性定义为四个隔离级别(Isolation Level)，隔离级别和数据库的性能呈反比，隔离级别越低，数据库性能越高；而隔离级别越高，数据库性能越差，具体如下:

  • READ-UNCOMMITTED(读取未提交的数据): 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交的数据): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
  • REPEATABLE-READ(可重复读,可以一直读): 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化,依次按顺序操作): 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

表格形式呈现:

隔离级别脏读不可重复读幻读
读未提交出现出现出现
读已提交不出现出现出现
可重复读不出现不出现出现
串行化不出现不出现不出现

MySQL的默认事务隔离级别是可重复读

如何设置MySQL的事务隔离界别
  1. 命令行
    终端窗口
    SET {SESSION | GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
  2. 配置文件 my.conf
    终端窗口
    [mysqld]
    transaction-isolation = REPEATABLE-READ

10. MySQL日志

10.1 Binary Log 二进制日志

binlog 日志有三种格式，可以通过binlog_format参数指定:

  1. statement: 记录的内容是SQL语句原文。同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。
  2. row: 记录的内容不再是简单的SQL语句了，还包含操作的具体数据。这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。
  3. mixed: 记录的内容是前两者的混合。MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。
binlog写入机制:

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中

其实就是缓存(Buffer)的思想,减少磁盘写IO

10.2 事务日志 redo log(重做日志)

概念：重做日志用来实现事务持久性，主要有两部分文件组成，重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者是在磁盘中。

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

内容：物理格式的日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。

10.3 undo log(回滚日志)

概念：回滚日志，用来记录数据被修改前的信息。正好跟前面的重做日志进行相反操作。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读；

内容：逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

11. redo log与binlog事务不一致解决方法

背景

redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入。所以redo log与binlog的写入时机不一样。在binlog提交事务准备写入时宕机了，在恢复的时候就会导致数据不一致

解决方法

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

12. MySQL主从复制什么原因会造成不一致，如何预防及解决

  1. 从库写入: 人为原因导致从库与主库数据不一致
  2. binlog非row格式,如果是基于binlog做一些操作就会导致数据不一致
  3. 主从复制过程中，主库异常宕机
  4. 设置了ignore/do/rewrite等replication等规则
  5. 从库中断很久，binlog应用不连续，监控并及时修复主从

解决

  1. 主库binlog采用ROW格式。
  2. 主从实例数据库版本保持一致
  3. 主库做好账号权限把控，不可以执行set sql_log_bin=0
  4. 从库开启只读，不允许人为写入。
  5. 定期进行主从一致性检验。

13. canal - 伪装者

实现原理: canal 模拟 MySQL slave 的交互协议，伪装自己为 MySQL slave ，向 MySQL master 发送 dump请求, MySQL master 收到 dump 请求，开始推送 binary log 给 slave (即canal), canal 解析 binary log 对象(原始为 byte 流)

canal如何做高可用,防止canal挂了

Canal Server 和 Canal Adapter 依赖 Zookeeper 实现 HA 高可用. Zk可以自行安装

canal高可用主要配置:

终端窗口
#Canal Server 地址
canal.register.ip = 11.8.36.104


#Canal Admin 连接信息
canal.admin.manager = 11.8.36.104:8089
canal.admin.port = 11110
canal.admin.user = admin
#mysql5 类型 MD5 加密结果 -- admin
canal.admin.passwd = 4ACFE3202A5FF5CF467898FC58AAB1D615029441


#自动注册
canal.admin.register.auto = true
#集群名
canal.admin.register.cluster = canal-cluster-1
#Canal Server 名字
canal.admin.register.name = canal-server-1

另一台 Canal Server 也是一样的方式配置，修改 canal.register.ip = 11.8.36.105 和 canal.admin.register.name = canal-server-2 即可。

Canal Adapter: Canal 最初只支持将数据从 MySQL 同步到 Kafka，RabbitMQ 等消息队列中，从 1.1.1 版本开始，Canal 实现了一个配套落地的模块 Canal Adapter，实现对 Canal Server 订阅的 binlog 消息进行消费，支持将数据输出至 HBase，MySQL，Elasticsearch，Kudu 中。

14. MySQL主从同步

MySQL 本身就自带有一个主从复制的功能，可以帮助我们实现负载均衡和读写分离

基本原理:

通过binlog来实现

详细描述:

主节点 log dump 线程: 当从节点连接主节点时，主节点会为其创建一个 log dump 线程，用于发送和读取 Binlog 的内容。在读取 Binlog 中的操作时，log dump 线程会对主节点上的 Binlog 加锁；当读取完成发送给从节点之前，锁会被释放。主节点会为自己的每一个从节点创建一个 log dump 线程。

从节点I/O线程: 当从节点上执行start slave命令之后，从节点会创建一个 I/O 线程用来连接主节点，请求主库中更新的Binlog。I/O 线程接收到主节点的 log dump 进程发来的更新之后，保存在本地 relay-log(中继日志)中。

从节点 SQL 线程: SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。对于每一个主从连接，都需要这三个进程来完成。

当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 log dump 进程，而每个从节点都有自己的 I/O 进程，SQL 进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时 I/O 进程可以很快从主节点获取更新，尽管 SQL 进程还没有执行。如果在 SQL 进程执行之前从节点服务停止，至少 I/O 进程已经从主节点拉取到了最新的变更并且保存在本地 relay log 中，当服务再次起来之后就可以完成数据的同步。

14.1 MySQL主从复制有哪些

  • 主从同步: 当用户写数据主服务器必须和从服务器同步了才告诉用户写入成功，等待时间比较长。
  • 主从异步: 只要用户访问写数据主服务器，立即返回给用户。
  • 主从半同步: 当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功。

15. 深分页

当偏移量比较大的时候,查询效率就比较低，例如limit 10000,10。性能会急剧下降

为什么会慢?

  1. limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
  2. limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

如何优化?

基于慢点原因可以从减少回表次数做以下优化:

  1. 已知范围使用between…and…
  2. 子查询优化
  3. inner join 延迟关联
  4. 标签记录(分页游标),即将上一页的结果作为条件查下一页。适用场景: 无翻到指定页的情况，类似于App首页瀑布流的这种形式

网站当前构建日期: 2024.11.18