mySQL面试题

MySQL面试题

1.InnoDB索引和MyISAM索引的区别是什么？

  1. InnoDB的索引的索引在存储的时候，他的索引是和数据是存储在一个文件里的，MyisAm的索引是另外的一个文件，和数据不是在一个文件里。
  2. 索引文件所对应的B+树的最叶子结点记录的数据有所区别，InnoDB里是存储的数据行的Id，而MyIsAm里存储的是指针，指向记录所在页的指针

2.一个表如果没有创建索引，还会去创建B+树么？

会，表创建时会默认创建一个聚簇索引树，没有Id也会默认生成一个rowid,然后生成聚簇索引树

3.MySQL的binlog有有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

  • statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
  • row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
  • mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

4.InnoDB和MyisAm有什么区别？

InnoDB 和 MyISAM 是 MySQL 中常用的两种存储引擎，它们在性能、功能、事务支持等方面有显著的区别。以下是它们的主要区别：

1. 事务支持

  • InnoDB：

    • 支持事务，符合 ACID（原子性、一致性、隔离性、持久性）属性。

    • 支持 COMMIT、ROLLBACK 和 SAVEPOINT，允许对数据库进行回滚和事务管理。

    • 支持 事务隔离级别（如 READ COMMITTED、REPEATABLE READ、SERIALIZABLE）来控制并发事务的行为。

  • MyISAM：

    • 不支持事务。每个查询都是独立的，不支持回滚或事务提交。

    • 因为没有事务支持，它适用于只读或少量写入的场景。

2. 表锁与行锁

  • InnoDB：

    • 行级锁（Row-Level Locking）：通过对单个行进行加锁，允许更高的并发操作，适用于频繁写入的场景。

    • 支持死锁检测：如果出现死锁，InnoDB 会自动检测并回滚事务。

  • MyISAM：

    • 表级锁（Table-Level Locking）：对整张表加锁，这会导致当一个线程对表进行写操作时，其他线程无法读取或写入该表，限制了并发性能。

    • 因为表级锁的存在，在高并发情况下，MyISAM 的性能较差。

3. 外键支持

  • InnoDB：

    • 支持外键（Foreign Key Constraints）：InnoDB 可以通过外键约束来保证数据的完整性和参照完整性。

    • 支持级联操作（如级联更新和删除），可以确保父表和子表数据的一致性。

  • MyISAM：

    • 不支持外键：MyISAM 没有外键约束，因此不能在表之间建立参照完整性，也不能自动处理级联删除或更新。

4. 数据存储与表的结构

  • InnoDB：

    • 存储方式：InnoDB 使用 聚簇索引（Clustered Index），数据行存储在索引中。表的主键决定了数据行的物理存储顺序。

    • 表中的每一行数据都有一个 主键，如果没有显式主键，InnoDB 会自动生成一个 隐藏的聚簇索引（通常是 ROWID）。

    • 支持 多版本并发控制（MVCC），可以在高并发环境下保持读写一致性。

  • MyISAM：

    • 存储方式：MyISAM 使用 非聚簇索引（Non-Clustered Index），数据行存储在文件中，索引文件与数据文件是分开的。

    • 每个表会有两个文件：.MYD（数据文件）和 .MYI（索引文件）。数据和索引分开存储，因此不能通过主键来直接控制数据存储顺序。

5. 性能

  • InnoDB：

    • 写性能：InnoDB 支持事务和行级锁，虽然能提供更高的并发性，但会有一定的写性能开销（如日志文件写入和行锁）。

    • 查询性能：在高并发写入和复杂查询（尤其是事务性操作）时，InnoDB 的性能通常较好。

    • 适合 OLTP（在线事务处理）应用，如电商系统、银行系统等。

  • MyISAM：

    • 读性能：MyISAM 在 只读操作 和 大数据量查询 时性能非常好，因为它不需要处理事务或行级锁。

    • 写性能：由于使用表级锁，在高并发写入时，MyISAM 的性能较差。

    • 适合 OLAP（在线分析处理）应用，如数据仓库、日志分析等。

6. 崩溃恢复

  • InnoDB：

    • 支持崩溃恢复：InnoDB 使用了 写前日志（WAL） 和 重做日志 来保证数据一致性。在数据库崩溃时，InnoDB 会通过日志进行自动恢复。

    • 使用 双重写入缓冲区（Doublewrite Buffer）来防止磁盘故障导致数据损坏。

  • MyISAM：

    • 不支持自动崩溃恢复：MyISAM 在发生崩溃时，表的数据可能会损坏，必须手动修复。

    • MyISAM 在事务处理和崩溃恢复方面较为脆弱。

7. 数据完整性与恢复

  • InnoDB：

    • 提供 数据完整性（支持外键、事务、行级锁等）。

    • 数据写入时通过 日志记录 和 检查点 机制，保证数据的一致性和持久性。

  • MyISAM：

    • 没有事务支持和外键约束，数据完整性方面较弱。

    • 在发生数据库崩溃或硬盘故障时，数据可能会丢失或损坏，需要通过 myisamchk 工具进行修复。

8. 磁盘空间

  • InnoDB：

    • 一般需要更多的磁盘空间来存储事务日志、重做日志和内部索引结构。

    • 支持 表空间，可以通过 共享表空间 或 独立表空间 存储数据。

  • MyISAM：

    • 磁盘空间使用较少，通常 MyISAM 表比 InnoDB 表更紧凑，因为它不需要存储额外的事务日志或重做日志。

9. 表级锁 vs 行级锁

  • InnoDB：

    • 支持 行级锁，提高了并发性，特别是在高并发的环境下，能够更细粒度地控制锁定，减少了锁竞争。

  • MyISAM：

    • 使用 表级锁，当一个线程锁定了表时，其他线程必须等待，直到该线程释放锁。表级锁在高并发的写操作场景下性能较差。

10. 适用场景

  • InnoDB：

    • 适用于 高并发写操作 和 事务处理 的应用场景，特别是那些需要 数据一致性 和 事务支持 的系统。

    • 适合 OLTP（在线事务处理）应用，如电商平台、金融系统等。

  • MyISAM：

    • 适用于 只读查询 或 低频写入 的应用场景，如日志存储、大数据查询和报表分析。

    • 适合 OLAP（在线分析处理）应用，如数据仓库、分析平台等。

5.InnoDB引擎的4大特性

  • 插入缓存，Insert buffer

索引数据存储在磁盘上，主键索引由于天然自增，无须磁盘的随机 I/O，只需不断追加即可。但普通索引大概率无序，默认情况下需要进行随机磁盘 I/O 操作，效率极差

为了解决普通索引插入效率低下的问题，InnoDB 存储引擎引入 Insert Buffer 的概念，对于普通索引（非聚集索引）不是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓存池中，

如果在直接插入，否则先放入 Insert buffer 对象中，然后以一定频率和辅助索引页子节点进行合并操作，此时通常能将多个插入合并到一个操作中，提高插入性能

  • 二次写，double write

InnoDB 索引页一般 16KB 大小，而操作系统写文件以 4KB 为单位，这就导致同一页需要分四块分别写入。此时就存在写完一块系统崩溃或者断电等特殊情况，此时就导致写入数据不完整的问题

二次写就是为了解决该问题，double write 分为两部分，一部分 doublewrite buffer，其大小 2MB，另一部分是磁盘上共享表空间中连续的 128 个页，也是 2,B

每次刷盘时这样处理：先将脏数据写入 doublewrite buffer，doublewrite buffer 每次 1MB 写入共享表空间的磁盘上，完成以上两步后调用 fsync 函数，将数据同步到各个表空间

如果操作系统在将页写入磁盘的过程中崩溃，InnoDB 重启发现页数据损坏后，可以从共享表的 doublewrite 中找到副本，用于数据恢复

  • 自适应哈希索引，adaptive hash index

InnoDB 虽然主要使用 B+ 树作为索引结构，但在某些特殊场景下用到哈希索引。InnoDB 会监控对表上索引的查找，如果发现某个索引频繁被访问，则建立哈希索引。

InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引

  • 预读，read ahead

当 InnoDB 预计某些页很快就要被访问时，会异步加载对应页数据到缓冲池。该思路就类似空间局部性：如果某块内存被访问，那么它周围的内存大概率也会被访问。

InnoDB 采用两种预读算法提高 I/O 性能：线性预读 和 随机预读

线性预读：以块为单位，一块等于64页。如果某一块中的被顺序读取的页数超过预定值，则 InnoDB 将会异步的将下一块读取到 buffer pool 中

随机预读：以页为单位，当某一个块中的一些页在 buffer pool 中被发现时，InnoDB 会将该块中的剩余页一并读到 buffer pool 中，目前已废弃

6.什么是索引？

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

7.索引有哪几种类型？

  • 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  • 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
  • 全文索引： 是目前搜索引擎使用的一种关键技术。

8.索引的数据结构（b树，hash）

　　索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现
为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部
分场景，建议选择BTree索引。

B树索引
mysql通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B
树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树
实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

查询方式：
主键索引区:PI(关联保存的时数据的地址)按主键查询,普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快 
B+tree性质：

  • n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
  • 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  • 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
  • B+ 树中，数据对象的插入和删除仅在叶节点上进行。
  • B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

哈希索引
　　简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址
法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生
Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

9.创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

  1. 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and
    c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  2. 较频繁作为查询条件的字段才去创建索引
  3. 更新频繁字段不适合创建索引
  4. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低
  5. 尽量的扩展索引，不要新建索引。
  6. 定义有外键的数据列一定要建立索引。
  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
  8. 对于定义为text、image和bit的数据类型的列不要建立索引。

10.创建索引时需要注意什么？

  • 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

你应该用0、一个特殊的值或者一个空串代替空值；

  • 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字
  • 段的离散程度高：索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

11.使用索引查询一定能提高查询的性能吗？为什么

　　索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为
此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查
询(INDEX RANGE SCAN)适用于两种情况:

 

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

12.什么是最左前缀原则？什么是最左匹配原则

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


13.B树和B+树的区别

  1. 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
  2. B+树的叶子节点有一条链相连，而B树的叶子节点各自独立

14.使用B+树的好处

　　由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因
此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一
层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

15.Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：

  • hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。
  • 对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
    那么可以看出他们有以下的不同：
  1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。
  2. 而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。hash索引不支持使用索引进行排序，原理同上。
  3. hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
  4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
  5. hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳

定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

16.数据库为什么使用B+树而不是B树？

1. 叶子节点存储的数据

  • B-树：在B-树中，所有的节点（包括内部节点和叶子节点）都存储实际的键值和数据（或者是指向数据的指针）。查询过程中可以在任意节点（包括内部节点）找到数据。

  • B+树：在B+树中，只有叶子节点存储实际的键值和数据，而内部节点只存储键值（没有数据）。内部节点用于指引查找路径，因此它们只起到索引的作用，不存储数据。

2. 数据访问的方式

  • B-树：查询时，可以在任意节点找到数据。数据可以存储在任意层级的节点中。

  • B+树：查询时，必须从根节点一直查找到叶子节点才能获得数据。只有叶子节点存储数据，查询结束时一定会找到叶子节点。

3. 叶子节点之间的链接

  • B-树：叶子节点之间没有显式的链接，叶子节点是独立的。

  • B+树：叶子节点之间有显式的双向链表（或者单向链表），这使得B+树特别适合范围查询（如BETWEEN、LIKE等），因为你可以很容易地从一个叶子节点顺序扫描到下一个叶子节点。

4. 节点的存储结构

  • B-树：每个节点可以包含多个键值，并且每个键值对应一个指向子节点的指针。所有节点（包括内部节点和叶子节点）都具有类似的结构，存储键值和指针。

  • B+树：内部节点只存储键值和指向子节点的指针，叶子节点存储键值和指向数据的指针，叶子节点之间通过链表连接，方便范围查找。

5. 查询效率

  • B-树：因为数据存储在所有节点中，查询时可以在内部节点或叶子节点找到数据。但由于数据分布在树的各个层级，可能会导致某些查询不需要访问叶子节点。

  • B+树：所有数据都存储在叶子节点，因此查询时必须访问到叶子节点，查询效率通常较B-树略低。但因为内部节点不存储数据，B+树的层次通常比B-树浅，查询速度依然较快。且由于叶子节点通过链表连接，范围查询非常高效。

6. 插入和删除操作

  • B-树：插入和删除操作需要维护每个节点的排序。插入和删除的过程中可能需要调整内部节点和叶子节点的结构（分裂、合并等）。

  • B+树：由于B+树的内部节点不存储数据，插入和删除时，通常只涉及叶子节点的分裂或合并，因此B+树的操作相对简单。B+树的结构在执行插入和删除时更容易维护。

7. 空间利用率

  • B-树：因为每个节点都存储数据和指针，内部节点的存储空间会占用更多。尤其是当内部节点数量较大时，可能会增加空间的浪费。

  • B+树：由于内部节点只存储键值和指针，相对来说，B+树的空间利用率较高。内部节点的存储更高效，尤其是在大规模数据集的情况下。

8. 范围查询性能

  • B-树：虽然B-树支持范围查询，但由于叶子节点之间没有直接连接，所以范围查询时需要回溯到父节点，继续查找下一个节点，性能较差。

  • B+树：B+树的叶子节点通过链表相连，范围查询非常高效。可以顺序扫描叶子节点，直接获取连续的数据，尤其适用于需要频繁范围查询的场景。

9. 使用场景

  • B-树：适用于需要较高查询频率，且查询模式较为复杂（如单点查询和范围查询混合）的场景。由于所有节点都存储数据，适合一些较为简单的存储要求。

  • B+树：适用于范围查询频繁、需要高效排序、查询较为简单的场景，尤其在数据库索引、文件系统和大规模数据存储中广泛应用。

 

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

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索
引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀
索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值何时使用聚簇索引与非聚簇索引


18.非聚簇索引一定会回表查询吗？

　　不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。
举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行selectage from employee where age < 20的查询时，在索引的叶子节点上，已经包含了
age信息，不会再次进行回表查询。

19.什么是数据库事务？

　　事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上
的一组操作，要么都执行，要么都不执行

20.事务的四大特性(ACID)介绍一下?

https://chuna2.787528.xyz/hualou/p/12076017.html

  • 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
    典型实现：使用 undo log 保存修改前的值，以便失败后回滚。
  • 一致性： 一个事务的执行必须让数据库从一个合法的状态，转移到另一个合法的状态。

所谓“合法的状态”是由你的**约束（constraints）**定义的，例如：

  1. 表字段的类型约束、非空约束

  2. 唯一性约束（UNIQUE）

  3. 主外键关联关系（FK）

  4. 业务规则：例如余额不能为负数、库存不能为负数

  5. 索引规则、触发器规则等

只要事务执行前后，这些规则都没有被破坏，就叫一致性。

延伸：为什么一致性最重要？

一致性不是由数据库单独保证的，它依赖 ACID 中其他三个特性一起工作。

  • 原子性：避免部分成功部分失败 → 避免数据“半完成”状态破坏一致性

  • 隔离性：避免并发读写互相污染 → 避免脏读、幻读破坏一致性

  • 持久性：避免提交后的数据丢失 → 避免回到不一致状态

所以：
一致性 = 约束 + 原子性 + 隔离性 + 持久性

一致性是最终目标，其他三项是手段。

  • 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性： 事务一旦提交，其修改必须永久保存，不会因宕机、异常退出、系统崩溃而丢失。

21.1 MySQL（InnoDB）如何实现持久性？

 

InnoDB 使用以下核心机制确保持久性：

 

1. Redo Log（重做日志）——持久性的核心

 

这是 InnoDB 持久性的根基。

 

事务提交时，数据可能还没写入 data file，但 redo log 必须先写入磁盘（fsync）。

 

也就是说：

 

WAL（Write-Ahead Logging）：先写日志，再写数据页。

 

redo log 的作用：

 

  • 记录“如何把某个数据页修改成最新版本的操作”

  • 即使 data file 没刷新，也能根据 redo log 重放出最新数据

 

Redo log 的两阶段：

 

  1. Prepare（先将 redo 写入日志缓冲并持久化）

  2. Commit（提交标记写入 redo）

 

只要 Prepare 阶段已持久化，事务就能保证不丢。

 

这就是所谓“两阶段提交”。

 

2. Undo Log（用于原子性与一致性）

 

虽然 Undo 主要用于一致性/回滚，但在宕机恢复时它同样参与：

 

  • 未提交事务 → 根据 Undo 回滚掉

  • 已提交事务 → 根据 redo 重放

 

3. Doublewrite Buffer（双写缓冲区）

 

防止 部分写成功、部分写失败 导致 Page 破坏。

 

流程：

 

  1. 页先写到 doublewrite buffer（连续区域）

  2. 再从 buffer 写到数据文件

 

宕机时如果数据文件损坏，可从 doublewrite 区恢复。

 

这使得 InnoDB 可以保证页写入的原子性。

 

4. Buffer Pool 刷盘（非实时）

 

提交事务不等于立即写入数据文件。
InnoDB 可以将页先放在 memory buffer pool 中延迟刷新。

 

真正保证持久性的是 redo log，而不是数据页写入。

 

21.什么是脏读？幻读？不可重复读？

  • 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

 

　　事务 A 读到了事务 B 未提交的数据。

　　例子：

　　B 给余额 +100，但还没提交

　　A 读到了余额变化

　　B 回滚

　　A 看到的数据根本不存在

  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据

　　事务 A 在同一次事务中两次读同一行，但第二次被事务 B 修改过了。

　　例如：

　　A 第一次读余额 = 100

　　B 修改余额 = 200 并提交

　　A 第二次读余额 = 200

  • 幻读(Phantom Read):幻读指同一个事务里两次查询相同的范围条件，但第二次查询时出现了“新增或被删除”的记录。
    这些新增或消失的行在第一次查询中根本不存在，就像“幻影”一样突然出现或消失

　　事务 A 扫描一批行，之后事务 B 插入一行满足条件的记录，A 再扫描就多出一条“幻影行”。

　　例如：

　　A 扫描 “age > 20”，有 10 行

　　B 插入一行 age=21

　　A 再次扫描变成 11 行

重点：幻读为什么会发生？本质是InnoDB的锁无法锁住不存在的行。

 

你只能锁住已有行，而范围内还没插入的行无法锁住，导致其他事务在该范围内插入数据，从而产生幻读。

 

例子：

 

 
SELECT * FROM user WHERE age > 20;

 

假设当前年龄大于 20 的行只有 ID=1、ID=2、ID=3
你给这三行加锁也挡不住别人插入一个 age=25 的行，因为那一行还不存在——锁不了。

 

 

22.什么是事务的隔离级别？MySQL的默认隔离级别是什么？

一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。

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

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默
认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

 

在 MySQL 的 InnoDB 中：

 

  • 快照读（普通 SELECT）会出现幻读

  • 加锁读（SELECT … FOR UPDATE / LOCK IN SHARE MODE）通过 Next-Key Lock（记录锁 + 间隙锁） 可以避免幻读

  • 在默认的 REPEATABLE READ 级别下，InnoDB 能够完全避免幻读，因此不会产生幻读问题

 

 

23.按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法

　　关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
　　MyISAM和InnoDB存储引擎使用的锁：行级锁，表级锁和页级锁对比

行级锁：行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

24.从锁的类别上分MySQL都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

  • 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
  • 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁
    只可以加一个，他和其他的排他锁，共享锁都相斥。
    锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小

25.MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将完成表锁，并发将无从谈起
InnoDB存储引擎的锁的算法有三种
Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

 

26.数据库的乐观锁和悲观锁是什么？怎么实现的？

  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

    两种锁的使用场景
    从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时
    候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

27.mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率
要高，这种说法其实是不准确的。这个是要区分环境的。
1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索
引。所以无论那个表大，用not exists都比not in要快

28.varchar与char的区别

char的特点

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

varchar的特点

  • varchar表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；
  • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
  • 对于varchar来说，最多能存放的字符个数为65532总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体

情况还需具体来设计数据库才是妥当的做法。

 

29.如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

　　对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数
据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，
就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

id ：表示一个查询中各个子查询的执行顺序

  1. id相同执行顺序由上至下。
  2. id不同，id值越大优先级越高，越先被执行。
  3. id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type：每个子查询的查询类型，一些常见的查询类型。例如Simple,primary,subquery,union，derived等。

type：访问类型，可以看到是否走索引.此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

  • system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。system是const的一个特例，表示表里只有一条元组匹配时为system。
  •  eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。
  •   ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。
  •  range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
  •    index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。
  •     ALL：全表扫描，扫描聚簇索引的所有叶子节点。

possible_keys:此列显示在查询中可能用到的索引。如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性能。

　　key:此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，

　　MySQL认为索引对当前查询帮助不大而选择了全表查询。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force index、ignore index。

 

key_len:  此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，

将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。

key_len计算规则：

  • 字符串：char(n)：n个数字或者字母占n个字节，汉字占3n个字
  • varchar(n)： n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。

数字类型：

  • tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

时间类型

  • date：3字节 timestamp：4字节 datetime：8字节

ref:  此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名

rows: 此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。

 Extra:

此列是一些额外信息。常见的重要值如下：

1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

 

30.大表数据查询，怎么优化

  1. 优化shema、sql语句+索引；第二加缓存，memcached, redis；
  2. 主从复制，读写分离；
  3. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
  4. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，
  5. 做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表

31.超大分页怎么处理？

超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select *from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地

的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age

> 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select *

from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.从需求的角度减少这种请求…

主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.


32.主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。
因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索

引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内

存碎片，进而造成插入性能的下降。总之，在数据量大一些的情况下，用自增主键性能会好一些。关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索

引，如果没有唯一键，会生成一个隐式的主键

33.mysql索引失效的场景？

  1. 不符合最左匹配原则：

例如有一条sql:select * from table where a =1 and b = 1 and c = 1; 假如我们创建了一个(a,b,c)的索引，假如我们想用这个索引，则一定要有a字段的查询条件，因为a是索引的最左边字段

　　2.不正确的like查询：%写在左边会导致索引失效

例： select * from table where name like '%tom';

　　3.对索引列进行了函数计算:

例： select * from table where a + 1 = 2;

　　4.索引列进行了类型转换：表中是varchar类型，传进来的是数值会导致索引失效

　　5.不等于也会导致索引失效：

　　6.错误的order By 会导致索引失效：

　　7. or 连接查询条件会导致索引失效

　　8.select * 会导致索引失效，而select b 不会

　　9.范围查询的数据量过大也可能导致索引失效，Mysql预估全表扫描快的话，也不会走索引

　　10.条件中使用is null,或者is not null会导致索引失效

34.什么是MVCC?

https://www.bilibili.com/video/BV1AW4y1g75F/?spm_id_from=333.337.search-card.all.click&vd_source=912db34243165a8f1c8423819c1adaf4

35.宕机后如何保证数据不丢？

重启时，InnoDB 会进行以下步骤：

  1. 扫描 redo log：

    • 找出所有处于 prepare 阶段但未 commit 的事务

    • 找出所有 commit 事务的 redo 记录

  2. 对未提交的事务：

    • 使用 undo log 回滚它们

  3. 对已提交但尚未落盘的数据页：

    • 使用 redo log 重放修改，恢复到最新状态

最终保证：

  • 已提交的数据 → 必然存在

  • 未提交的数据 → 必然不存在

这正是持久性的落地实现。

 34.什么是page?

1. Page的基本概念

  • Page大小：在InnoDB中，Page的默认大小是16KB（每个页面的存储容量）。这个大小可以通过配置innodb_page_size来调整，但一般情况下，16KB是默认且常见的设置。

  • Page结构：每个Page由多个部分组成，包括：

    • Header：包含Page的元数据信息，如Page的类型、Page编号、父Page编号等。

    • Body：实际存储数据部分。它包括索引记录、数据记录、事务日志等内容。

    • Trailer：包含一些用于恢复的校验和信息。

2. Page的分类

InnoDB中的Page根据不同的用途可以分为几类：

  • 数据页（Data Page）：这些页面存储了表中的实际数据。当你执行SELECT、INSERT、UPDATE等操作时，数据存储在这些页面中。它们是存储数据的“核心”部分。

  • 索引页（Index Page）：这些页面存储了表的索引数据，InnoDB使用B+树来组织索引数据。索引页也有类型，可以是聚簇索引页（用于主键索引）或非聚簇索引页（用于其他索引）。

  • Undo日志页（Undo Log Page）：用于存储事务的undo日志，支持事务的回滚操作。

  • 系统页（System Page）：用于存储数据库的元数据信息，包括表空间、数据字典、事务日志等。

  • Insert Buffer Pages：用于存储“插入缓冲”中的数据，以提高插入操作的效率。

3. Page的结构细节

InnoDB中的Page并不是简单的存储数据，它还会保存额外的管理信息。例如：

  • Page类型：Page可以是数据页、索引页、UNDO页等，每种类型有不同的结构和功能。

  • Page标记：每个Page都有一个标记位，表明该Page是否已经被分配、是否是数据页或索引页等。

  • Record Slot：每个Page都有多个记录槽（Record Slot），每个槽对应一个数据行。记录槽的索引可以快速定位数据行。

4. 如何管理Page

InnoDB使用一个叫做**缓冲池（Buffer Pool）**的机制来缓存频繁使用的Page。为了提高性能，缓冲池会将最近使用的Page保持在内存中，减少磁盘I/O。缓冲池大小可以通过innodb_buffer_pool_size参数进行调整。

  • 页分配：当InnoDB需要存储新的数据或索引时，它会从表空间中分配一个空闲的Page。这个Page可以来自于内存中的缓冲池，也可以从磁盘加载到缓冲池中。

  • 页清理：在事务提交或回滚时，InnoDB会根据需要将数据刷新到磁盘。页清理是一项重要的操作，旨在保持数据的一致性和持久性。

5. Page与事务的关系

在InnoDB中，Page在事务中的管理非常重要。InnoDB的事务特性（ACID）离不开Page的机制：

  • 原子性（Atomicity）：即使一个事务在中途失败，InnoDB也可以通过事务日志（比如undo日志）回滚已修改的Page，从而保证数据的原子性。

  • 一致性（Consistency）：事务开始和结束时，InnoDB会通过日志和Page的管理保证数据库的一致性。即使发生崩溃，也能通过Page和日志进行恢复。

  • 隔离性（Isolation）：InnoDB通过MVCC和锁机制来管理并发事务对Page的访问，避免事务间的数据冲突。

  • 持久性（Durability）：当事务提交时，数据会持久化到磁盘，并通过日志和Page来保证即使系统崩溃也不会丢失数据。

6. Page的I/O操作

InnoDB会根据访问模式来选择如何读取和写入Page：

  • Lazy Write：InnoDB会将修改过的Page延迟写回磁盘。只有当这些Page被替换、脏页（dirty pages）超过阈值时，才会进行写入。

  • Write-Ahead Logging（WAL）：InnoDB使用WAL协议，即在修改数据Page之前，先将修改记录写入日志。这保证了事务的持久性和一致性。

7. Page的恢复

当系统崩溃或发生故障时，InnoDB依靠Page和日志文件进行恢复：

  • Redo日志：记录了已提交事务的修改，保证持久性。

  • Undo日志：记录了未提交事务的修改，用于回滚操作。

通过这些日志和Page的结合，InnoDB能够在系统崩溃后自动恢复未提交的数据，恢复到一致的状态。

总结

InnoDB的Page是其存储引擎的核心组成部分，掌握Page的结构和管理方式对理解数据库的性能、事务管理以及数据恢复至关重要。Page的设计使得InnoDB能高效地处理大规模的数据存储，并通过缓冲池、日志机制等保证事务的ACID特性。

35.什么是行溢出?

行溢出（Row Overflow）是InnoDB存储引擎在存储数据行时，遇到某些情况导致一行数据无法完全存储在一个Page内的现象。由于InnoDB中每个Page的大小是固定的（默认16KB），当数据行的大小超过单个Page的存储容量时，就会发生行溢出。

为什么会出现行溢出？

行溢出通常发生在以下几种情况下：

  1. 数据行的列值过大：例如，如果表中的某些列存储了长文本（如TEXT、BLOB类型），或者某些列的数据量过大（例如超大的VARCHAR类型字段），则可能导致一行数据占用的字节数超过了Page的容量。

  2. 过多的列：如果表中有很多列，尤其是大数据类型（如TEXT、BLOB）的列，整行数据的大小可能会超过单个Page的限制。

  3. 大字段的内存存储：对于一些大字段（如TEXT、BLOB类型），InnoDB通常不会将它们的内容直接存储在数据行所在的Page中。相反，这些大字段的数据会存储在外部的页（称为“外部存储页”或“溢出页”）中，如果这些外部数据页无法被当前Page容纳，也可能发生行溢出。

行溢出的工作原理

当一行数据无法完全存储在一个Page内时，InnoDB会采取以下方式处理：

  1. 溢出页（Overflow Pages）：InnoDB会为无法完全存储的列（通常是大字段，如TEXT、BLOB）分配一个或多个溢出页。这些溢出页是独立于数据页的，可以跨多个Page存储。

  2. 行数据的分割：如果行数据本身无法在Page内完全存储，InnoDB会将一部分数据存储在当前Page中，剩余部分存储到新的溢出页或数据页。这意味着一行数据可能会分布在多个页上。

  3. 指针链：在行数据的存储结构中，InnoDB会为溢出的部分（如超大的列）创建指向溢出页的指针。这样，InnoDB就能通过指针链来读取整行数据，即使它分布在多个Page中。

行溢出如何影响性能？

  1. 额外的磁盘I/O：当数据行分布在多个Page或溢出页时，InnoDB需要进行额外的磁盘I/O操作以读取完整的行数据。这会导致性能下降，尤其是在访问涉及溢出页的数据时。

  2. Page碎片化：行溢出可能导致Page的碎片化，因为一部分数据存储在当前Page中，剩余部分存储在溢出页中。这可能会导致存储效率下降和I/O性能的影响。

  3. 查询效率下降：查询包含大数据类型字段（如TEXT、BLOB）的表时，尤其是在没有合适索引的情况下，可能会因为行溢出而变得更慢。

如何避免行溢出？

  1. 合理设计表结构：避免设计过多的TEXT、BLOB等大字段类型。考虑将大字段数据放到专门的表中，而不是直接放在主表中，从而避免行数据溢出。

  2. 使用合适的数据类型：如果某些列只存储较小的字符串或数字数据，尽量使用VARCHAR、INT等更小的数据类型，避免使用TEXT、BLOB类型。

  3. 增加Page大小：虽然InnoDB默认的Page大小为16KB，如果存储的数据量较大，可以考虑调整innodb_page_size来增大Page的大小（例如，设置为32KB或64KB）。这样可以减少行溢出的发生，但是这会增加内存使用，因此要根据实际需求来决定。

  4. 使用压缩：对于某些场景，可以启用InnoDB的表压缩功能，压缩数据以减小单行的大小，从而降低行溢出的概率。

总结

行溢出是InnoDB在存储数据时遇到的一个常见问题，尤其是在表中有较大的数据字段（如TEXT、BLOB）时。它导致数据无法完全存储在一个Page内，需要通过溢出页来解决。虽然行溢出本身不会导致数据丢失，但它会增加磁盘I/O操作，影响查询和写入的性能。因此，合理设计数据库表结构，避免过多使用大字段，可以有效减少行溢出的发生。

 

36.索引的优缺点

索引的优点

  • 加速查询：索引的主要作用是加速数据的检索，尤其是在使用WHERE子句、ORDER BY、JOIN等操作时。

  • 减少I/O操作：通过索引，数据库可以在更少的I/O操作中查找到所需的数据，从而提高查询性能。

  • 加速排序和分组：使用索引可以帮助加速ORDER BY、GROUP BY等操作，减少对整个表的扫描。

索引的缺点

  • 增加写入成本：每当对表执行INSERT、UPDATE或DELETE操作时，索引也需要更新，这会增加额外的写入开销。

  • 占用空间：索引会占用额外的磁盘空间，尤其是当索引字段较多或数据量较大时。

  • 不适合所有查询：某些复杂的查询，尤其是涉及大范围扫描的数据，可能不会从索引中受益。

37.什么是索引下堆？

索引下堆（Index-Only Heap），通常是数据库优化中的一个概念，涉及到 索引结构 和 数据存储结构 之间的关系。在数据库中，堆表（Heap Table）是最常见的表存储结构，它没有按照任何索引顺序存储数据，而是 无序存储 数据。理解索引下堆的概念需要从堆表、索引和数据库如何管理存储谈起。

1. 什么是堆表？

在关系型数据库中，堆表（Heap Table）是一种数据存储方式，其数据行没有按照任何特定顺序存储。也就是说，数据表中的记录是按插入顺序存储的。堆表通常用于存储那些不依赖于特定顺序的表。

特点：

  • 数据的存储是无序的。

  • 每次插入新记录时，数据会放置在表的 末尾 或根据存储系统的要求进行放置。

  • 查询时需要 全表扫描，若没有索引，查询性能会比较差。

2. 什么是索引下堆？

索引下堆并不是一个标准的数据库术语，它是与某些数据库内部的实现和优化技术相关的概念，通常用来描述索引结构如何访问堆表数据。在这种情况下，"堆"指的是数据表的存储结构，而索引是用来加速查询的工具。

具体来说，索引下堆指的是在 索引中 通过 查找索引 来定位数据的存储位置，并直接访问 堆表中的数据行。这意味着索引提供了数据行在堆表中的位置（或数据的指针），从而减少了全表扫描的需要。

3. 如何理解索引下堆的工作原理？

数据存储与访问：

  • 在数据库中，堆表的数据没有顺序，数据插入时只是简单地按照存储引擎的逻辑存储。

  • 当使用索引查询时，索引并不存储数据本身，而是存储 数据行的指针（或行ID、行号等）。

  • 数据库通过索引查找数据的位置，然后从堆表中 直接读取 该数据行。

这就意味着，索引下堆的工作机制涉及两个方面：

  1. 索引存储：索引提供了数据查找的加速路径，通常只存储键值和指向数据行的指针。

  2. 堆表存储：数据表的记录按照堆结构存储，数据行无特定顺序，索引提供的是行定位，而不是数据本身。

4. 索引下堆的应用场景

  • 提高查询效率：当查询需要访问大量数据时，堆表和索引的结合可以加速数据检索。通过索引定位数据，减少全表扫描的时间。

  • 避免数据重复：在一些场景下，数据可能没有固定顺序，需要通过索引来快速定位特定数据行。

  • 分页查询：索引下堆在分页查询时也有优势，可以通过索引定位不同的页面，从而避免全表扫描。

5. 索引下堆的优势与限制

优势：

  1. 减少全表扫描：使用索引来加速查询，无需扫描整个堆表，尤其是在进行复杂的查询或有大数据量时，查询效率大大提升。

  2. 更高效的检索：索引提供了数据定位的指针，数据库只需要通过索引获取数据行的存储位置，然后从堆表中快速访问该数据。

  3. 优化分页查询：索引可以帮助更高效地定位数据，尤其是在分页查询时，通过索引跳过不需要的数据页。

限制：

  1. 占用额外存储：索引本身需要占用额外的存储空间，这可能会增加系统的存储开销。

  2. 写操作开销：对于堆表来说，插入、更新和删除操作可能会比较频繁，因为数据无序存储，且需要更新相关的索引。

  3. 索引维护开销：每次插入、更新或删除数据时，索引也需要维护，这可能会影响系统性能，尤其是在数据量非常大的情况下。

6. 索引下堆的数据库实现（例如InnoDB）

在 InnoDB 存储引擎中，表的数据通常是存储为堆表，而索引通常是非聚簇索引。每当进行查询时，非聚簇索引会存储索引的键值以及指向 堆表中数据行的指针，通过这些指针，我们可以在堆表中找到对应的记录。

对于 聚簇索引（如主键索引），它直接将数据存储在 索引的叶子节点 中，因此不需要回表。但对于 非聚簇索引，虽然索引存储了关键字和指针，但实际数据仍然保存在堆表中，需要通过回表的方式访问数据。

7. 示例：堆表和索引下堆

假设有一个 employees 表，包含以下字段：id、name、age。该表没有定义任何排序规则，因此它是一个堆表。假设我们为 id 列创建了一个非聚簇索引：

 
CREATE INDEX idx_id ON employees(id);

当执行如下查询时：

 
SELECT name, age FROM employees WHERE id = 123;

  • 数据库首先使用 索引 idx_id 查找 id = 123 对应的记录，找到索引条目和指向数据行的指针。

  • 然后，数据库会根据索引中存储的指针，再访问堆表中的对应数据行，获取 name 和 age 字段的值。这就需要执行 回表操作。

总结

  • 索引下堆 这个概念本质上指的是 堆表 和 索引 结合的使用方式，数据库通过索引定位数据，而数据本身依然存储在堆表中。

  • 堆表 是一种没有特定顺序的存储方式，数据是按插入顺序存储的。

  • 使用 索引 查找时，索引存储的是指向数据行的指针，而数据本身存储在堆表中，数据库可以通过索引定位数据，然后访问堆表来检索数据。

索引下堆 主要用于优化查询，避免全表扫描，但需要注意存储开销和写操作开销。

 

38.mysql 的SQL查询流程

MySQL 的 SQL 查询流程涉及多个步骤，从接收到 SQL 查询到最终返回结果，MySQL 会经过一系列的处理。整个流程可以大致分为以下几个阶段：

1. 客户端发送 SQL 查询请求

当客户端应用程序发起 SQL 查询时，它会通过网络连接（如 TCP/IP）将 SQL 语句发送到 MySQL 服务器。这一阶段只是发送数据，MySQL 并没有对 SQL 语句进行任何处理。

2. 连接管理（Connection Management）

在 SQL 查询到达 MySQL 服务器后，首先会通过连接管理模块进行处理：

  • 验证客户端身份：MySQL 会验证连接的用户名和密码，检查权限，并创建一个连接线程来处理这个请求。

  • 连接池：如果启用了连接池机制，MySQL 会从连接池中获取一个可用连接，或者根据需要新建一个连接。

  • 连接管理会将请求传递给下一个模块：查询解析器。

3. 查询解析（Query Parsing）

查询解析是将 SQL 查询语句转换为服务器能够理解的内部结构的过程。

  • 语法解析：MySQL 会首先检查 SQL 查询的语法是否正确，生成一个 解析树（Parse Tree）。如果 SQL 有语法错误，服务器会返回错误信息。

  • 语义检查：接下来会对 SQL 进行语义分析，检查表、字段等是否存在，验证权限等。如果没有问题，则继续进入下一步。

4. 查询优化（Query Optimization）

查询优化是 MySQL 查询处理的一个重要环节，它的目的是根据 SQL 查询的内容，生成一个 执行计划，选择最优的查询策略，以提高查询效率。优化过程包括以下几个步骤：

4.1 查询重写（Query Rewrite）

  • MySQL 会尝试对 SQL 语句进行 重写，比如：

    • 合并多个查询。

    • 替换某些运算符以提高效率。

    • 简化查询语句等。

4.2 执行计划生成（Execution Plan Generation）

  • 选择最佳索引：优化器会根据表的索引、查询条件、表的大小等因素，选择最合适的 索引。常见的优化包括：

    • 使用 覆盖索引，避免回表。

    • 使用 最优的连接算法（如 Hash Join、Nested Loop Join）。

    • 选择 最优的扫描方式（全表扫描、索引扫描等）。

4.3 选择最优执行路径（Execution Path Selection）

  • 优化器会生成多个 执行计划，然后根据 成本估算（比如执行时间、磁盘 I/O 等）选择一个成本最低的执行计划。

5. 查询执行（Query Execution）

查询执行是 MySQL 根据优化后的执行计划进行实际操作的过程。

5.1 执行计划执行

  • 表扫描：如果没有合适的索引，MySQL 会执行全表扫描（Full Table Scan）。这会读取表中的所有记录，逐行匹配查询条件。

  • 索引扫描：如果查询使用了索引，MySQL 会利用索引来加速查询过程。通常情况下，索引可以帮助快速定位符合条件的记录，避免全表扫描。

    • 主键索引：通常情况下，主键索引是聚簇索引，数据存储是有序的，因此使用主键索引查询会非常高效。

    • 非聚簇索引：在使用非聚簇索引时，MySQL 会先通过索引找到符合条件的记录，然后根据索引的指针访问实际数据行（这时可能发生“回表”）。

5.2 执行顺序

  • JOIN 操作：如果 SQL 查询涉及多个表的连接（JOIN），MySQL 会根据优化器选择的执行计划，决定连接的顺序和方式。例如，嵌套循环连接、哈希连接等。

  • 排序与分组：如果查询需要对结果进行排序（ORDER BY）或分组（GROUP BY），MySQL 会按照排序或分组的要求对数据进行处理。

  • 聚合函数：如果查询包含聚合函数（如 COUNT()、SUM()、AVG() 等），MySQL 会计算聚合结果。

5.3 锁管理

  • 事务隔离：在执行查询时，MySQL 还会根据事务的隔离级别进行锁的管理。例如，在 InnoDB 引擎中，执行查询时可能会使用 行锁 或 表锁 来保证数据的隔离性。

  • 读写锁：对于某些操作，MySQL 会加读锁或写锁，确保数据的一致性。

6. 存储引擎层（Storage Engine Layer）

存储引擎层是执行查询的最后一环，它负责实际的数据存储和检索。存储引擎将根据查询请求读取磁盘上的数据，并返回给 MySQL 服务器。

  • InnoDB 存储引擎：如果表使用的是 InnoDB 存储引擎，查询过程会使用 InnoDB 缓冲池 来缓存数据页和索引页，减少磁盘 I/O。

  • MyISAM 存储引擎：如果表使用 MyISAM 存储引擎，查询过程中会使用 键缓存（Key Cache）来缓存索引。

在存储引擎层，MySQL 会执行以下操作：

  • 从磁盘加载数据页到内存（缓冲池）。

  • 通过 索引 或 全表扫描 查找所需的数据。

  • 执行 数据锁定 或 事务管理。

  • 将查询结果返回给 MySQL 服务器层。

7. 查询结果返回

查询结果通过服务器层返回给客户端。MySQL 将查询结果以数据包的形式发送到客户端，客户端应用程序可以使用这些数据进行进一步处理。

  • 查询缓存：如果查询缓存被启用，MySQL 会首先检查查询缓存。如果该查询已经存在于缓存中，MySQL 会直接返回缓存结果，而不需要重新执行查询。

  • 结果格式化：MySQL 会将查询结果按照客户端要求的格式进行封装（例如，CSV、JSON、HTML 等），然后发送给客户端。

SQL 查询的总结流程

  1. 客户端发起 SQL 查询。

  2. 连接管理：验证用户身份，分配线程。

  3. 查询解析：分析 SQL 语法并构建查询树。

  4. 查询优化：生成执行计划，选择最优查询路径。

  5. 查询执行：

    • 读取数据（通过索引或全表扫描）。

    • 执行 JOIN、排序、分组等操作。

    • 加锁和事务管理。

  6. 存储引擎处理：读取数据、加锁、缓存等。

  7. 结果返回给客户端。

 

39.如果涉及到多个or 或者 in 该如何优化

 

当 SQL 查询中涉及多个 OR 或 IN 子句时，可能会导致查询效率低下，尤其是当数据量较大时，因为 MySQL 在处理 OR 和 IN 时，可能无法有效使用索引，或者需要进行全表扫描。为了优化这种查询，通常可以尝试以下几种方法：

 

1. 使用 UNION 替代 OR

 

当查询中包含多个 OR 条件时，使用 UNION 可以将查询拆分成多个单独的查询，并在每个查询中只包含一个条件，这样 MySQL 可以为每个查询条件使用适当的索引。UNION 操作会合并多个查询结果并去除重复记录（如果需要保留重复记录，可以使用 UNION ALL）。

2.使用 IN 替代多个 OR

当查询中有多个相同列的 OR 条件时，使用 IN 可以提高效率。IN 查询可以让 MySQL 通过一次索引扫描来匹配多个值，而 OR 可能会导致多个不同的索引扫描。

3. 利用联合索引

当查询条件涉及多个字段，并且这些字段经常一起被查询时，考虑创建 联合索引（Composite Index）。联合索引是针对多个列的索引，可以加速查询条件中多个字段的检索。

 

40.explain中 type和extra字段的作用

1. type 字段

type 字段描述了 MySQL 如何访问表中的数据，并反映了查询的效率。它显示了 MySQL 执行查询时所选择的连接类型（访问类型），不同的 type 表示查询性能的好坏。类型的顺序通常按性能从高到低排列，常见的 type 值包括以下几种：

常见的 type 值及含义

type 值含义性能
const 查询的表只有一行，通常用于主键或唯一索引查询。MySQL 在这种情况下可以将表视为常量，直接返回结果。 性能最佳，查询非常高效
eq_ref 对每一行数据使用唯一索引查找其他表的匹配记录。通常用于连接条件中的等值连接。 性能较好，适用于连接操作
ref 使用非唯一索引查找匹配记录，通常是索引查找多个匹配项。 性能一般，适用于范围查询或非唯一索引
range 对索引进行范围扫描，如 BETWEEN、<、> 等条件，返回索引区间中的数据。 性能较好，但受索引范围影响
index 扫描整个索引，而不是数据表中的数据。通常用于某些索引操作，如索引覆盖查询。 性能较差，需要扫描大量索引数据
ALL 全表扫描，意味着 MySQL 需要扫描整个表来查找符合条件的记录，通常是最差的执行方式。 性能最差，可能需要扫描大量数据

type 字段的详细解析

  • const：这是最理想的查询类型，意味着查询只需要访问表中的一行数据，通常发生在通过主键或唯一索引进行查询时。例如，查询 SELECT * FROM employees WHERE id = 1 时，id 是主键，MySQL 只需要一次查找就能返回结果。

  • eq_ref：表示每次从表中读取一行，并进行与其他表的连接。通常发生在连接查询中，且连接条件是通过主键或唯一索引进行的。这是第二高效的连接类型。

  • ref：表示使用非唯一索引来查找记录，通常是对一个表中的字段进行查找，而该字段不是唯一的。性能比 eq_ref 差，但通常比 ALL 要好。

  • range：表示范围扫描，通常应用于带有范围条件（例如 BETWEEN、<、>）的查询。这时 MySQL 会查找索引区间内的数据。

  • index：表示 MySQL 扫描整个索引（而不是扫描数据表），这通常发生在执行覆盖索引查询时。覆盖索引查询意味着查询的所有列都已经包含在索引中，无需回表读取数据。

  • ALL：表示全表扫描，这通常是查询最不理想的情况，因为它意味着 MySQL 必须检查每一行数据来找出符合条件的记录。

优化建议：

  • 尽量避免 ALL 类型的扫描，尽量利用索引加速查询。

  • 如果 type 是 range，可以考虑优化索引设计，确保范围查询能有效利用索引。

  • 使用适当的索引来保证查询能使用 eq_ref 或 ref 类型的访问，避免 ALL 类型的全表扫描。

2. Extra 字段

Extra 字段提供了执行查询时的额外信息，能够反映出一些查询细节和优化的方向。通过 Extra 字段，你可以了解 MySQL 在执行查询时是否使用了临时表、是否进行了排序等信息。这些信息对于性能调优非常重要。

常见的 Extra 字段值及含义

Extra 值含义
Using where 查询中有 WHERE 子句，表示 MySQL 在返回结果之前进行了一些额外的过滤。
Using temporary 使用了临时表，表示查询需要使用临时表来存储中间结果。通常发生在 GROUP BY、ORDER BY 或联合查询时。
Using filesort 使用了文件排序，表示查询的结果需要在磁盘上进行排序。可能会影响查询性能，特别是数据量大时。
Using index 查询是通过索引完成的，表示 MySQL 使用了索引来查询数据，通常是覆盖索引查询。
Using join buffer 使用了连接缓冲区，通常出现在多表连接的查询中。表示连接操作没有使用索引，而是通过一个缓冲区进行处理。
Distinct 查询使用了去重操作，通常与 DISTINCT 或 GROUP BY 配合使用。
No tables used 查询没有实际使用任何表，通常出现在子查询中。
Impossible WHERE 查询的 WHERE 子句不可能为真（比如某些字段的值设置了不可能的值）。通常 MySQL 会直接跳过这个查询。
Using index condition 表示使用了索引条件下推（Index Condition Pushdown），这是一个查询优化技术，MySQL 在索引扫描时提前进行过滤。

Extra 字段的详细解析

  • Using where：表示查询在返回结果之前对数据进行了 WHERE 过滤。如果查询条件较复杂，可能会导致额外的计算开销。优化建议是，尽量使过滤条件能被索引覆盖，避免 MySQL 在查询后再进行额外的 WHERE 过滤。

  • Using temporary：表示查询中使用了临时表。通常是由于查询包含了 GROUP BY、ORDER BY、DISTINCT 等操作时，MySQL 会先在内存中创建一个临时表。如果数据量很大，可能会导致性能下降。优化建议是，尽量避免不必要的排序和分组操作，或者考虑优化查询结构，减少临时表的使用。

  • Using filesort：表示查询需要对结果集进行文件排序（不是通过索引排序）。文件排序通常比索引排序要慢，因为它涉及到磁盘 I/O。优化建议是，检查查询是否可以使用索引进行排序，避免 Using filesort 出现。

  • Using index：表示查询通过索引执行，且索引已经包含了查询所需的所有数据（即覆盖索引）。这种情况下，MySQL 不需要访问数据行，直接从索引中获取结果，是一种非常高效的查询方式。

  • Using join buffer：表示查询的连接操作没有使用索引，而是通过一个连接缓冲区进行的。优化建议是，检查连接条件是否能够通过索引优化，避免 MySQL 使用全表扫描或缓冲区进行连接。

  • Distinct：表示查询使用了 DISTINCT 去重操作。DISTINCT 会增加额外的计算开销，尤其是当结果集很大时。优化建议是，确保查询条件能有效使用索引，减少不必要的去重操作。

  • Impossible WHERE：表示查询的 WHERE 子句不可能为真。例如，WHERE age < 30 AND age > 40 这样的条件永远不会满足。优化建议是，检查 SQL 查询中的 WHERE 子句，确保没有矛盾或无效的条件。

41.索引创建的最佳实践

选择合适的列创建索引

  •  

    高选择性列：创建索引时，优先考虑选择性高的列。选择性是指列中的不同值的数量与列中的总记录数的比率。高选择性意味着列中的值具有较好的区分度，有助于更快地定位数据。

    • 例如，ID、邮箱地址、用户名 等是高选择性列，适合创建索引。

  •  

    低选择性列：对于值重复度很高的列（如性别、国家等），创建索引效果不明显，反而会增加索引维护的开销。

    • 例如，gender（性别）字段的值可能只有“男”和“女”，创建索引的效益会非常低。

3.2 避免过多的索引

  • 每个索引都需要消耗存储空间，并且在插入、删除、更新数据时会增加额外的维护开销。

  • 尽量避免对每个查询字段都创建索引，过多的索引会增加数据写入的成本，特别是对大数据量表的性能影响较大。

3.3 考虑索引的顺序

  •  

    在创建联合索引时，列的顺序非常重要。索引列的顺序应该与查询中使用这些列的顺序一致。

    • 比如，如果查询经常是 SELECT * FROM table WHERE column1 = ? AND column2 = ?，那么索引应该创建为 (column1, column2)。

    • 如果查询经常是 SELECT * FROM table WHERE column2 = ? AND column1 = ?，则应将索引创建为 (column2, column1)。

3.4 避免使用 SELECT *

  • 在创建索引时，尤其是复合索引时，避免使用 SELECT * 来查询所有列。尽量显式指定需要的列，这样可以减少不必要的索引扫描和数据加载，避免回表操作。

3.5 覆盖索引（Covering Index）

  •  

    覆盖索引是指索引本身包含了查询所需要的所有列，查询时直接从索引中返回数据，而无需回表（即无需访问表中的数据行）。

    • 如果查询的列都在索引中，就可以避免回表，从而大大提高查询效率。

     
    CREATE INDEX idx_column1_column2 ON table_name (column1, column2);

    查询时，直接从索引中获取 column1 和 column2 的数据，而不需要回到数据表查找其他字段。

3.6 定期检查和优化索引

  • 随着表数据量的增加，或者随着查询模式的变化，某些索引可能会变得无用或不再高效。因此，定期对数据库中的索引进行检查和优化是非常必要的。

  • 可以使用 SHOW INDEX 来查看表的索引信息，使用 OPTIMIZE TABLE 来优化表和索引。

42.什么是三星索引？

1. 第一颗星：最小化索引片的宽度

  • 定义：如果查询中的等值条件的索引行是相邻的或足够接近的，索引会被标记为第一颗星。

  • 收益：这种优化的目标是最小化扫描的索引片宽度，即通过将 WHERE 子句中的等值条件字段放在复合索引的最前面，减少需要扫描的索引范围，进而提升查询性能。

实现：

  • 将 WHERE 后的等值条件列作为索引最开头的列。

  • 例如，如果你查询条件是 WHERE a = 5 AND b = 10 AND c = 20，创建索引时可以将 a、b 和 c 按顺序依次加入索引中，且a 排在最前面。

2. 第二颗星：避免排序操作

  • 定义：如果索引行的顺序与查询语句中需要排序的字段顺序一致，那么索引会被标记为第二颗星。

  • 收益：通过避免额外的排序操作，能直接通过索引读取数据，减少了查询的额外开销。

实现：

  • 将 ORDER BY 子句中的列加入到复合索引中，并保持列的顺序。

  • 例如，如果查询是 ORDER BY b, c，那么在创建复合索引时，应将 b 和 c 排列在索引的前面，保持顺序。

3. 第三颗星：覆盖索引

  • 定义：如果索引行中包含了查询语句中的所有列，那么索引就会被标记为第三颗星。

  • 收益：这样做的好处是避免了回表操作，只需要通过索引读取数据，不需要再访问数据表，从而大大提高查询性能。

43.mysql8以上为什么会删除查询缓存？

1. 查询缓存的性能问题

1.1 频繁的缓存失效
查询缓存的一个重要问题是频繁的缓存失效。每当数据库中的数据发生变化（如 INSERT、UPDATE、DELETE 操作）时，相关的查询缓存会失效，需要重新计算缓存。这在高频写操作的应用场景中表现得尤为严重：
  • 每一次写入操作都会使得查询缓存相关的条目失效，导致大量的缓存刷新和无效的缓存命中。
  • 当有大量写操作时，缓存几乎无法被有效利用，因为写入会不断清空缓存，造成缓存的利用率低。
1.2 内存管理的复杂性
查询缓存需要占用大量内存，在大型数据库或高负载的环境下，查询缓存的管理变得复杂：
  • 当缓存的大小不够时，MySQL 需要进行缓存的清理和更新，这增加了系统的开销。
  • 如果数据量庞大，缓存的占用量会非常大，容易造成内存的浪费，或者增加内存管理的复杂性。
1.3 锁竞争问题
查询缓存的管理涉及到锁的使用：
  • 每次写操作都会对查询缓存进行锁定（全局锁），从而影响性能，特别是在高并发写操作的环境中。锁竞争会导致写操作的延迟，甚至影响到整个数据库的性能。

1.4 无法优化复杂查询
查询缓存只能缓存完整的查询结果，而对某些复杂的查询（例如包含 JOIN、GROUP BY、ORDER BY 的查询）无法进行有效缓存。复杂查询结果的不确定性和可变性使得查询缓存的命中率较低，无法提供显著的性能提升。

44.mysql中如何进行慢查询优化

1. 启用慢查询日志

首先，要确保启用了 MySQL 的慢查询日志，以便收集慢查询的详细信息。

启用慢查询日志的步骤：

 
SET GLOBAL slow_query_log = 'ON'; -- 启用慢查询日志 SET GLOBAL slow_query_log_file = '/path/to/slow-query.log'; -- 指定慢查询日志的存储路径 SET GLOBAL long_query_time = 2; -- 设置慢查询的阈值，单位为秒（例如：2秒）

long_query_time 参数表示查询执行超过这个时间的查询会被记录到慢查询日志中。

其他有用的配置：

  • log_queries_not_using_indexes: 记录没有使用索引的查询。

  • log_slow_admin_statements: 记录慢的管理类语句（如 CREATE、ALTER）。

通过慢查询日志，你可以看到执行时间较长的查询以及它们的执行计划，从而有针对性地进行优化。

2. 分析慢查询日志

获取慢查询日志后，可以使用以下工具来分析查询性能瓶颈：

  • MySQL 自带的 mysqldumpslow 工具，可以用来快速分析日志。

    例如：

     
    mysqldumpslow -s t /path/to/slow-query.log

    这里的 -s t 表示按时间排序，显示执行时间最长的查询。

  • Percona Toolkit 的 pt-query-digest：这是一个更强大的分析工具，可以提供查询的详细统计信息和执行计划，帮助你找出瓶颈所在。

    例如：

     
    pt-query-digest /path/to/slow-query.log

3. 优化慢查询

一旦找到了慢查询，可以通过以下几种方式进行优化：

3.1 使用索引

  • 添加适当的索引：查看查询中涉及的 WHERE、JOIN 和 ORDER BY 等条件，确定是否缺少适当的索引。通过 EXPLAIN 分析查询的执行计划，查找哪些地方缺少索引。

    • 例如，针对某些字段频繁查询，可以考虑为这些字段创建 单列索引 或 复合索引。

    示例：

     
    CREATE INDEX idx_customer_id ON orders(customer_id);

3.2 优化查询语句

  • 避免使用 SELECT *：只查询需要的列，减少数据传输和处理的开销。

    示例：

     
    SELECT name, price FROM products WHERE product_id = 123;

  • 减少 JOIN 的数量：如果查询中包含多个表的 JOIN，可以考虑：

    • 使用 子查询 或 分步查询 来优化。

    • 如果多个表之间的连接是频繁的，可以通过建立适当的索引来加速 JOIN 操作。

  • 使用合适的查询条件：

    • 确保查询的条件能最大限度地利用索引，避免不必要的全表扫描。

    • 尽量避免在查询条件中使用 OR，可以通过拆分查询或使用 UNION 来替代。

    示例：

     
    SELECT * FROM orders WHERE status = 'completed' AND customer_id = 1001;

3.3 使用覆盖索引

  • 覆盖索引：如果查询只需要某些列的数据，并且这些列可以通过索引覆盖，MySQL 可以直接从索引中获取结果，而不需要回表。这样可以大大提高查询速度。

    例如：

     
    CREATE INDEX idx_status_customer ON orders(status, customer_id); SELECT status, customer_id FROM orders WHERE status = 'completed' AND customer_id = 1001;

3.4 避免使用 LIKE 开头的查询

  • 避免 LIKE 查询以 % 开头，因为这样无法使用索引，会导致全表扫描。尽量避免这种模式，或者考虑使用全文索引。

    示例：

     
    -- 不推荐，无法使用索引 SELECT * FROM users WHERE name LIKE '%John';

    如果确实需要这种查询，可以考虑使用 全文索引（FULLTEXT），尤其是对于大文本搜索。

3.5 避免不必要的排序和分页

  • ORDER BY 排序：如果查询中包含 ORDER BY，且排序字段没有索引，会导致排序操作非常耗时。可以通过为排序字段添加索引来加速排序。

    示例：

     
    CREATE INDEX idx_order_date ON orders(order_date);

  • LIMIT 分页：对于大数据量的分页查询，LIMIT 后面的偏移量会影响查询性能，尤其是当偏移量非常大的时候。可以考虑使用更高效的分页方式（如基于 索引 的分页）。

    示例：改用基于 id 的分页：

     
    SELECT * FROM orders WHERE id > 1000 LIMIT 100;

3.6 分区表和数据分片

  • 分区表：对于数据量非常大的表，可以考虑使用 分区表。通过分区可以将数据划分为多个小的部分，优化查询性能。

    例如，可以根据日期将表按月份进行分区，查询时只需要访问相关的分区。

    示例：

     
    CREATE TABLE orders ( id INT, order_date DATE, ... ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p0 VALUES LESS THAN (2021), PARTITION p1 VALUES LESS THAN (2022), PARTITION p2 VALUES LESS THAN (2023) );

  • 数据分片：对于大规模的应用，可以考虑 数据分片（Sharding），将数据分布到多个独立的数据库中，从而分散查询负载。

4. 查看执行计划（EXPLAIN）

使用 EXPLAIN 来分析查询的执行计划，了解查询是如何被 MySQL 执行的，以及是否能够有效利用索引。

例如：

 
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND order_date > '2022-01-01';

执行计划的输出会显示：

  • 类型（type）：显示访问表的方式。ALL 表示全表扫描，index 表示通过索引访问。

  • 可能的键（possible_keys）：列出查询可能使用的索引。

  • 实际使用的键（key）：实际使用的索引。

  • 行数（rows）：MySQL 预计需要扫描的行数。

通过查看这些信息，您可以进一步优化查询。

5. 使用缓存

  • 查询缓存：尽管 MySQL 8 已经移除了查询缓存，但如果你使用的是 MySQL 5.7 或更早版本，可以通过启用查询缓存来减少频繁查询的响应时间。需要注意的是，查询缓存只适用于只读的查询，数据频繁更新时查询缓存效果较差。

  • 外部缓存系统：对于高并发的应用，可以使用 Redis 或 Memcached 等外部缓存系统，缓存热点数据，减少对数据库的压力。

6. 定期优化表

  • OPTIMIZE TABLE：对于频繁更新、删除数据的表，MySQL 的数据文件可能会碎片化，导致查询性能下降。定期执行 OPTIMIZE TABLE 可以重建表并回收空间，提升查询效率。

    示例：

     
    OPTIMIZE TABLE orders;

7. 硬件优化

  • 增加内存：增加服务器的内存容量，尤其是 InnoDB Buffer Pool，可以缓存更多的数据，减少磁盘 I/O 操作。

  • 优化磁盘 I/O：使用 SSD 代替传统的 HDD，提高磁盘的读写速度，减少查询延迟。

总结

慢查询优化是一个系统的工程，从 分析慢查询日志 到 查询重构、索引优化、硬件优化 等都需要综合考虑。通过以上方法，你可以有效提高 MySQL 查询的性能，减少慢查询对系统的影响，保证高并发环境下的稳定性和响应速度。

 

45.mysql中join如何优化？

优化 MySQL 中的 JOIN 查询需要从以下几个方面入手：

  1. 选择合适的 JOIN 类型。

  2. 为连接条件创建合适的索引。

  3. 避免不必要的 JOIN。

  4. 调整 JOIN 顺序。

  5. 使用 EXPLAIN 分析执行计划。

  6. 避免在 ON 或 WHERE 子句中使用复杂表达式。

  7. 考虑使用临时表或子查询。

  8. 避免不必要的 DISTINCT 和 GROUP BY。

 

46.mysql中常见的索引类型

  • 普通索引（INDEX）

  • 唯一索引（UNIQUE INDEX）

  • 主键索引（PRIMARY KEY）

  • 全文索引（FULLTEXT INDEX）

  • 组合索引（Composite Index）

  • 自增索引（AUTO_INCREMENT）

  • 空间索引（SPATIAL INDEX）

  • 哈希索引（Hash Index）

47.order by优化

优化 ORDER BY 查询的关键在于：

  1. 使用索引：尤其是复合索引，可以加速排序操作，避免全表扫描。

  2. 减少排序的数据量：通过精确的过滤条件和分页，减少排序时需要处理的行数。

  3. 避免在排序字段中使用函数或表达式：避免使索引失效。

  4. 限制排序字段数量：尽量避免排序多个字段。

  5. 优化执行计划：通过 EXPLAIN 分析查询，减少临时表和文件排序的使用。

48.group by 优化

优化 GROUP BY 查询可以通过以下方法来提升性能：

  1. 使用索引：为分组字段创建索引，特别是当分组字段是查询条件时。

  2. 减少 GROUP BY 字段的数量：只按必要的字段进行分组。

  3. 避免在 GROUP BY 中使用函数：尽量避免使用函数或表达式。

  4. 使用 HAVING 进行聚合后的筛选：避免在分组前使用 WHERE。

  5. 减少排序和临时表的使用：增大内存限制，避免临时表操作。

  6. 避免不必要的 DISTINCT：避免在 GROUP BY 中使用多余的 DISTINCT。

  7. 使用分区表：对大表进行分区，减少查询的扫描量。

49.mvcc详细

一、为什么需要 MVCC（解决什么问题）

数据库并发场景下，核心矛盾只有一个：

读和写如何并发而不互相阻塞，同时还能保证事务隔离性？

如果不用 MVCC，只有两条路：

  1. 读加锁 → 并发差（读会阻塞写）

  2. 不加锁 → 数据不一致（脏读、不可重复读）

MVCC 的目标：

  • 读不加锁

  • 写不阻塞读

  • 在一定隔离级别下保证一致性

👉 InnoDB 选择的是：
“多版本 + 快照读” 的方案

二、MVCC 的核心组成（三大件）

InnoDB 的 MVCC 不是一个单一机制，而是由 三部分协同工作：

1️⃣ 隐藏列（每行都有）

InnoDB 表中，每一行记录，物理上都会额外包含两个隐藏字段：

字段名含义
DB_TRX_ID 最近一次修改该行的事务 ID
DB_ROLL_PTR 指向 undo log 的指针

（如果表没有主键，还会有 DB_ROW_ID）

2️⃣ undo log（回滚日志）

undo log 中保存的是：

行被修改之前的旧版本数据

作用有两个：

  1. 事务回滚

  2. MVCC 构造历史版本（重点）

每一次 UPDATE / DELETE：

  • 当前行写新版本

  • 老版本写入 undo log

  • 通过 DB_ROLL_PTR 串成一个 版本链

3️⃣ Read View（读视图）

Read View 是 MVCC 的灵魂

它决定：

当前事务能看到哪个版本的数据

Read View 本质是一个 事务 ID 的可见性快照

三、版本链是怎么形成的（非常关键）

假设一行数据经历了多次修改：

 
V3（当前行） ↑ DB_ROLL_PTR V2（undo log） ↑ V1（undo log）

每个版本都有：

  • 自己的 DB_TRX_ID（修改它的事务 ID）

这条链，就是 MVCC 判断可见性的基础

四、Read View 结构（面试重点）

Read View 里有 4 个核心字段：

字段含义
creator_trx_id 创建该 Read View 的事务 ID
m_ids 当前活跃事务 ID 列表
min_trx_id m_ids 中最小的事务 ID
max_trx_id 下一个将分配的事务 ID

五、可见性判断规则（一定要会）

假设要判断某行版本的 DB_TRX_ID = trx_id 是否可见：

规则 1：trx_id < min_trx_id

✅ 可见
说明该事务在 Read View 创建前就提交了

规则 2：trx_id ≥ max_trx_id

❌ 不可见
说明该版本是 Read View 创建之后才产生的

规则 3：trx_id 在 m_ids 中

❌ 不可见
说明该版本来自一个未提交的活跃事务

规则 4：其它情况

✅ 可见

如果当前版本不可见，就沿着 undo log 向前找历史版本

六、MVCC 与“快照读 / 当前读”

1️⃣ 快照读（Snapshot Read）

不加锁，走 MVCC

典型 SQL：

 
SELECT * FROM t WHERE id = 1;

  • 使用 Read View

  • 读的是“某个时间点的一致性快照”

  • 不会阻塞写

2️⃣ 当前读（Current Read）

读最新数据，并加锁

典型 SQL：

 
SELECT * FROM t WHERE id = 1 FOR UPDATE; SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; UPDATE / DELETE / INSERT

  • 不走 MVCC

  • 必须读最新已提交版本

  • 通过锁保证一致性

七、MVCC 和事务隔离级别的关系（必考）

READ UNCOMMITTED

  • 不用 MVCC

  • 直接读最新数据（可能脏读）

READ COMMITTED（RC）

  • 每一次 SELECT 都生成一个新的 Read View

  • 可能发生 不可重复读

 
第一次 SELECT → Read View 1 第二次 SELECT → Read View 2

REPEATABLE READ（RR，默认）

  • 事务第一次 SELECT 时创建 Read View

  • 后续 SELECT 复用同一个 Read View

  • 解决不可重复读

 
事务开始 ↓ 第一次 SELECT → Read View ↓ 后续 SELECT 都用这个 View

SERIALIZABLE

  • 不靠 MVCC

  • 全部转成加锁读

  • 并发性能最低

八、MVCC 能不能解决幻读？（重点澄清）

结论一句话：

MVCC + RR 只能解决“快照读”的幻读

示例：

 
SELECT * FROM t WHERE age > 20;

  • 快照读 → 不会看到新插入的行

  • 当前读（FOR UPDATE） → 仍然可能幻读

👉 真正解决幻读的是：Next-Key Lock（间隙锁 + 记录锁）

九、MVCC 为什么性能好

  1. 读不加锁

  2. 读写不冲突

  3. undo log 顺序 IO

  4. 索引页 + 快照判断，代价可控

这就是 InnoDB 能支撑高并发 OLTP 的核心原因之一。

十、用一句“面试级总结”

InnoDB 的 MVCC 通过 undo log 构建版本链，结合 Read View 的可见性判断规则，实现了在 READ COMMITTED 和 REPEATABLE READ 隔离级别下的非阻塞一致性读，使得普通 SELECT 不加锁即可获得事务级别的一致性视图。

50.bin log

一、什么是 Binlog

  • 定义：binlog 是 MySQL 数据库的二进制日志，记录了所有 会修改数据库数据的操作。

  • 作用：

    1. 数据恢复：可以从 binlog 回放恢复数据到某个时间点。

    2. 主从复制：复制机制依赖 binlog 将主库操作传递给从库。

    3. 审计与审查：可以追踪哪些操作修改了数据。

注意：binlog 不记录 SELECT 查询，只有会修改数据的 DML（INSERT、UPDATE、DELETE）和 DDL（CREATE、ALTER、DROP）会被记录。

二、Binlog 的存储格式

MySQL 支持三种 binlog 格式：

格式描述优缺点
STATEMENT（语句模式） 记录执行的 SQL 语句 优点：日志量小；缺点：可能会因为非确定性函数（NOW()、RAND()）导致主从不一致
ROW（行模式） 记录行级变化数据（哪一行被改了，改成什么） 优点：精确复制；缺点：日志量大
MIXED（混合模式） 语句模式 + 行模式结合 优点：兼顾日志量和一致性，MySQL 会自动选择最合适的模式

  • 查看当前 binlog 格式：

 
SHOW VARIABLES LIKE 'binlog_format';

三、Binlog 的内容

1️⃣ Binlog Header（文件头）

  • 存储 binlog 版本信息、创建时间等元数据

  • 每个 binlog 文件开头都有文件头

2️⃣ 事件（Event）

binlog 是一系列事件（Event）的集合，每个事件表示一次数据库操作。主要类型：

事件类型描述
QUERY_EVENT 语句模式下，记录 SQL 语句
TABLE_MAP_EVENT 行模式下，用于映射表结构
WRITE_ROWS_EVENT 插入行记录
UPDATE_ROWS_EVENT 更新行记录
DELETE_ROWS_EVENT 删除行记录
XID_EVENT 事务提交事件，标记事务结束
ROTATE_EVENT binlog 文件切换事件

 

 

 

 

 

 



 

 

 

 51.mysql线上修改大表结构有哪些风险，该如何正确操作

一、风险分析

1️⃣ 表锁阻塞

  • InnoDB ALTER TABLE 默认行为：

    • 会创建 新表 + 复制数据 + 替换旧表

    • 对表加 写锁，阻塞 DML（INSERT/UPDATE/DELETE）

  • 风险：

    • 高并发业务表修改期间可能 长时间阻塞写操作

    • 大表数据量大时，可能持续 数分钟到数小时

2️⃣ 复制延迟（主从）

  • 线上大表修改会在 主库生成大量 binlog

  • 从库回放时可能 延迟累积

  • 对业务一致性有影响

3️⃣ 回滚困难

  • ALTER TABLE 是 不可回滚操作

  • 过程中失败可能导致表处于锁定或半更新状态

  • 数据丢失或业务不可用风险高

4️⃣ I/O / CPU 压力

  • 大量数据复制、索引重建 → 高 I/O 消耗

  • 对线上业务造成性能抖动，可能触发慢查询或阻塞事务

解决方案：

  1. 尽量使用 在线 DDL（INPLACE + LOCK=NONE）

  2. 对不支持在线的操作，使用 pt-online-schema-change / gh-ost

  3. 分批迁移、低峰期操作

  4. 先测试再上线，确保备份和回滚方案

52.行锁何时会升级为表锁

  • InnoDB 不会自动把行锁升级为表锁

  • 表锁出现通常是因为：

    1. 全表扫描修改（没有索引或大范围修改）

    2. 显式表级锁

    3. DDL 或复制操作

  • 行锁 + Next-Key Lock 已经能保证 事务隔离 + 幻读防护，无需升级

53.说一下乐观锁和悲观锁

二、悲观锁

1️⃣ 原理

  • 每次访问数据前，假设会有并发冲突，先对数据加锁

  • 常用锁类型：

    • 行锁（SELECT … FOR UPDATE / UPDATE / DELETE）

    • 表锁（LOCK TABLES）

  • 事务执行期间，其他事务对锁定资源操作会被阻塞

2️⃣ MySQL 示例

 
START TRANSACTION; -- 当前读 + 排它锁 SELECT * FROM orders WHERE order_id=1001 FOR UPDATE; UPDATE orders SET status='paid' WHERE order_id=1001; COMMIT;

  • 保证事务修改安全

  • 避免脏写、不可重复读

3️⃣ 优缺点

  • 优点：

    • 保证数据一致性

    • 实现简单

  • 缺点：

    • 阻塞其他事务

    • 并发性能低

    • 容易死锁

三、乐观锁

1️⃣ 原理

  • 假设不会发生冲突，不加锁直接操作数据

  • 提交或更新时通过 版本号或时间戳 检测是否有其他事务修改

  • 如果冲突 → 重试或失败

2️⃣ MySQL 实现方式

a. 版本号（version）字段

 
-- 表结构 orders(id, status, version) -- 更新语句 UPDATE orders SET status='paid', version=version+1 WHERE order_id=1001 AND version=1;

  • 事务 T1 获取 version=1

  • 提交时检查 WHERE 条件是否成立

  • 如果其他事务已更新 version → WHERE 不成立 → 操作失败

b. 时间戳字段

 
UPDATE orders SET status='paid', updated_at=NOW() WHERE order_id=1001 AND updated_at='2023-12-16 10:00:00';

  • 提交时检查记录是否被其他事务修改

3️⃣ 优缺点

  • 优点：

    • 并发性能高，无锁阻塞

    • 避免死锁

  • 缺点：

    • 需要应用层重试逻辑

    • 冲突多时性能反而差

54.mysql中,count(*),count(1),count(列名），有什么区别

一、COUNT(*)

1️⃣ 语义

  • 统计表中行数，包括列值为 NULL 的行

  • 不忽略任何行

2️⃣ 底层原理

  • InnoDB：扫描整张表的行记录，但不读取列值，只需要知道行存在

  • MyISAM：表有一个行数计数器，COUNT(*) 可以直接返回，非常快

3️⃣ 特点

  • 最标准、最安全的计数方式

  • 性能最优（尤其 MyISAM），InnoDB 在大表上也比较高效

二、COUNT(1)

1️⃣ 语义

  • 统计表达式 1 的行数

  • MySQL 会将 1 转换为非 NULL，每行都计数

  • 等效于 COUNT(*)

2️⃣ 特点

  • 在逻辑上和 COUNT(*) 一样

  • 历史上有人误认为 COUNT(1) 比 COUNT(*) 快，但实际上 MySQL 编译器优化后几乎一致

三、COUNT(列名)

1️⃣ 语义

  • 统计指定列非 NULL 值的行数

  • 忽略该列值为 NULL 的行

2️⃣ 示例

 
CREATE TABLE t(id INT, name VARCHAR(20)); INSERT INTO t VALUES (1,'A'), (2,NULL), (3,'B'); SELECT COUNT(*) FROM t; -- 3 SELECT COUNT(1) FROM t; -- 3 SELECT COUNT(name) FROM t; -- 2 （name=NULL 的行不计数）

3️⃣ 底层原理

  • InnoDB 需要遍历每行，检查指定列是否为 NULL

  • 性能略低于 COUNT(*)，但差异不大

四、总结对比表

函数统计范围是否计数 NULL底层效率
COUNT(*) 全表行 是 高（MyISAM O(1)，InnoDB 遍历行）
COUNT(1) 全表行 是 几乎等同 COUNT(*)
COUNT(列名) 指定列 否 略低于 COUNT(*)，需要检查 NULL

五、优化建议

  1. 大表统计总行数 → 使用 COUNT(*) 最安全

  2. 统计非 NULL 列 → 用 COUNT(列名)

  3. 不要为了性能用 COUNT(1) → 和 COUNT(*) 差别不大，易混淆

55.mysql的主从复制作用和原理

一、MySQL 主从复制的作用

  1. 读写分离 / 扩展读性能

    • 主库（Master）负责写操作（INSERT、UPDATE、DELETE）

    • 从库（Slave）负责读操作（SELECT）

    • 可以大幅提升系统的读吞吐量

  2. 高可用 / 容灾备份

    • 从库可以在主库故障时接管服务（通过切换或者 MHA/Proxy）

    • 提供异地备份或数据容灾

  3. 数据备份与审计

    • 从库可以用作备份库，避免备份对主库性能影响

    • 可分析 binlog，进行数据审计或回放

  4. 业务隔离

    • 从库可以做复杂报表查询，避免影响主库事务性能

二、主从复制的原理

MySQL 主从复制基于 二进制日志（binlog），流程如下：

1️⃣ 主库（Master）

  1. 开启 binlog（记录所有会修改数据的 SQL/行操作）

     
    [mysqld] log_bin=mysql-bin server_id=1 binlog_format=ROW

  2. 每次事务提交时，写入 binlog

  3. binlog 记录了事务的操作序列（事件 Event）

2️⃣ 从库（Slave）

  1. 启动复制线程：

    • IO 线程：连接主库，读取 binlog，写入 relay log（本地日志）

    • SQL 线程：解析 relay log，重放操作到从库

  2. 保持与主库数据一致（异步复制）

3️⃣ 复制流程图

 
Master (binlog) ┌─────────────┐ │ 事务提交 │ │ 写 binlog │ └─────────────┘ │ ▼ Slave (relay log) ┌─────────────┐ │ IO 线程 │ ---> 从主库拉取 binlog 写 relay log └─────────────┘ │ ▼ ┌─────────────┐ │ SQL 线程 │ ---> 解析 relay log 重放到从库 └─────────────┘

三、复制类型

  1. 异步复制（默认）

    • 主库提交事务无需等待从库

    • 优点：性能高

    • 缺点：主库故障可能导致数据丢失（从库未同步的事务）

  2. 半同步复制

    • 主库提交事务需等待至少一个从库确认收到 binlog

    • 优点：数据安全性提高

    • 缺点：性能略下降

  3. 全同步复制

    • 需要所有从库确认 → 常用在 Galera Cluster 等集群

    • 事务提交延迟大

四、复制模式

  1. 基于语句（Statement）

    • 主库记录 SQL 语句

    • 优点：日志量小

    • 缺点：非确定性语句可能导致主从不一致

  2. 基于行（Row）

    • 主库记录每行数据变化

    • 优点：精确复制，避免主从不一致

    • 缺点：日志量大

  3. 混合模式（Mixed）

    • 语句 + 行，根据情况选择最优模式

五、关键配置参数

 
[mysqld] server_id=1 # 主从唯一标识 log_bin=mysql-bin # 开启 binlog binlog_format=ROW # 行模式复制 sync_binlog=1 # 提交 binlog 同步到磁盘

六、复制注意事项

  1. 主从时钟不同步 → 时间戳可能不一致

  2. SQL 语句中存在非确定性函数 → 使用 ROW 或 MIXED 模式

  3. 主库负载过高 → 可以通过异步复制降低阻塞

  4. 监控复制延迟：

     
    SHOW SLAVE STATUS\G;

七、总结

  • 作用：

    1. 读写分离

    2. 高可用和容灾

    3. 数据备份和审计

    4. 业务隔离

  • 原理：

    • 基于 binlog → 主库写 binlog → 从库 IO 线程拉取 → SQL 线程重放

    • 支持 异步、半同步、同步 复制

  • 复制模式：

    • 语句模式、行模式、混合模式

    • 行模式最安全

 

 56.mysql如何进行分库分表

一、分库（Database Sharding）

1️⃣ 什么情况下需要分库（动机）

分库解决的是数据库层面的瓶颈，核心目标是：

  • 突破单库性能瓶颈

  • 降低单库并发压力

  • 提升整体系统可用性

典型触发场景：

  • 单库 QPS 已接近或超过瓶颈

  • CPU / IO / 连接数长期打满

  • 数据量虽然不大，但并发极高

  • 写压力远大于读压力（如交易、日志）

面试关键词：
并发压力、连接数瓶颈、单点故障

2️⃣ 分库的本质

分库 = 把不同的数据，放到不同的数据库实例中

每个库：

  • 有独立连接池

  • 独立 IO / CPU

  • 独立事务空间

👉 从“纵向扩展”变成“横向扩展”

3️⃣ 常见分库方式

（1）业务垂直分库（最常见）

按业务拆

 
user_db → 用户相关 order_db → 订单相关 payment_db → 支付相关

特点：

  • 天然解耦

  • 跨库事务少

  • 最优先选择

适用场景：

  • 微服务架构

  • 不同业务数据无强耦合

（2）数据水平分库

同一张逻辑表，拆到多个库

 
order_0_db order_1_db order_2_db

路由规则示例：

 
user_id % 3

特点：

  • 解决并发 + 数据量

  • 复杂度明显上升

4️⃣ 分库带来的核心问题

问题说明
跨库事务 无法使用本地事务
JOIN 失效 只能在应用层处理
全局唯一 ID 不能自增主键
运维复杂 备份、扩容复杂

5️⃣ 分库的典型配套方案

  • 分布式事务

    • 最终一致性（MQ + 本地事务）

    • TCC / Seata（慎用）

  • 全局 ID

    • Snowflake

    • 号段模式（Leaf）

  • 禁止跨库 JOIN

    • 数据冗余

    • 反范式设计

二、分表（Table Sharding）

下面开始只讲分表，不涉及分库

1️⃣ 什么情况下需要分表（动机）

分表解决的是单表瓶颈：

  • 单表数据量过大（千万 / 亿级）

  • 索引高度膨胀

  • 查询 / 更新变慢

  • DDL 操作（加索引）风险极高

注意：
分表不是为了解决并发，而是为了解决“表太大”

2️⃣ 分表的本质

分表 = 把一张逻辑表，拆成多张物理表

 
order ↓ order_0 order_1 order_2

但：

  • 逻辑上还是一张表

  • 应用层负责路由

3️⃣ 分表的常见方式

（1）水平分表（最常用）

按某个字段拆数据行：

  • user_id

  • order_id

  • 时间

示例：

 
order_id % 16

优点：

  • 单表数据量可控

  • 读写性能稳定

（2）时间分表（日志 / 历史数据）

 
order_202401 order_202402

特点：

  • 冷热数据分离

  • 易归档、易清理

适合：

  • 日志

  • 账单

  • 行为流水

（3）垂直分表（字段拆）

 
order_main → 常用字段 order_detail → 大字段 / JSON / TEXT

目的：

  • 减少 IO

  • 提高缓存命中率

4️⃣ 分表后的关键设计点

（1）分表键选择

必须满足：

  • 查询条件高频使用

  • 尽量等量分布

  • 不轻易变更

反例：

  • status

  • type

  • boolean 字段

（2）主键设计

  • ❌ 自增主键

  • ✅ 全局唯一 ID

原因：

  • 自增在多表中会冲突

  • 插入热点严重

5️⃣ 分表的常见问题

问题说明
跨表查询 需要全表扫描
分页困难 需要二次查询
聚合统计 应用层聚合
扩容 重新分片成本高

三、分库 vs 分表（明确区分）

维度分库分表
解决问题 并发 / 连接数 单表数据量
扩展维度 实例级 表级
复杂度 高 中
是否常同时使用 可单独 可单独

四、真实项目中常见组合（只总结，不展开）

    • 先 垂直分库

    • 热点表 水平分表

    • 极端场景 分库 + 分表

 




 

posted @ 2023-03-23 16:32  杨阳洋^_^!  阅读(28)  评论(0)    收藏  举报