跳至主要內容

八股-MySQL

CharmingDaiDai2025/5/6大约 24 分钟面试八股mysql

Mysql 相关面试题

‍

优化

如何定位慢查询

回答：

  1. 介绍当时产生问题的场景：在接口测试过程中，我们发现速度极为缓慢，压测结果显示大约需要5秒钟
  2. 系统中，我们采用了运维工具——Skywalking，该工具能够监测到具体的接口。最终，问题源于SQL语句
  3. 在MySQL数据库中，我们启用了慢日志查询功能，并将阈值设置为2秒。一旦SQL语句的执行时间超过2秒，系统便会将其记录到日志中（适用于调试阶段）

‍

慢查询：

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询

表象：页面加载缓慢、接口压力测试响应时间过长（超过1秒）

‍

方案：

  • 开源工具

    • 调试工具：Arthas
    • 运维工具：Prometheus、Skywalking

  • MySQL 自带慢日志

    • 慢查询日志记录了所有执行时间超过指定参数(long_query_time​, 单位: 秒, 默认10秒)的所有SQL语句
      要开启慢查询日志, 需要在MySQL的配置文件(/etc/my.cnf​)中配置如下信息:
    • #开启MySQL慢日志查询开关
slow query log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
    • 配置完毕之后，重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log​

‍

SQL 语句执行很慢，如何分析

回答：

可以采用MySQL自带的分析工具EXPLAIN​

通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

‍

  • 聚合查询
  • 多表查询
  • 表数据量过大查询

这些可以通过 SQL 执行计划找到慢的原因

‍

采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息

​explain select * from documents where id = 1;​

image
image

  • possible key 当前sql可能会使用到的索引

  • key 当前sql实际命中的索引

通过它们两个查看是否可能会命中索引

  • key_len 索引占用的大小

  • Extra 额外的优化建议

Extra含义
Using where; Using Index查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
Using index condition查找使用了索引，但是需要回表查询数据

  • ​TYPE​ 这条 SQL 的连接类型，性能由高至低排序为：NULL、SYSTEM、CONST、EQ_REF、REF、RANGE、INDEX、ALL

    • SYSTEM：查询系统中的表
    • CONST：根据主键查询
    • EQ_REF：主键索引查询或唯一索引查询
    • REF：索引查询
    • RANGE：范围查询
    • INDEX：索引树扫描（需要优化）
    • ALL：全盘扫描（需要优化）

‍

索引

❓什么是索引

索引在项目中颇为常见，作为辅助MSQL高效获取数据的数据结构，其主要功能在于提升数据检索效率，减少数据库的I/O成本。同时，通过索引列对数据进行排序，进一步降低数据排序的成本，并有效减少CPU的消耗

‍

❓索引的底层数据结构

MySQL默认的存储引擎InnoDB，采用B+树的数据结构来存储索引。选择B+树的主要原因如下：

  1. 第一，B+树的阶数更多，路径更短
  2. 第二，B+树的磁盘读写代价更低，非叶子节点仅存储指针，而叶子节点存储数据
  3. 第三，B+树便于进行全库扫描和区间查询，其叶子节点构成一个双向链表

‍

❓B树和B+树的区别

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

‍

二叉树、红黑树

image
image

二叉树，不论哪种，在数据量很大的时候，树高很高（瘦高）

B 树

B-Tree，又称B树，是一种多路平衡查找树。与二叉树相比，B树每个节点可拥有多个分支，即多叉结构（矮胖）

以一颗最大度数（max-degree）为5（五阶）的B树为例，此类B树的每个节点最多存储4个key

image
image

B+ Tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

叶子节点才会存储数据、非叶节点只存储指针

①：磁盘读写代价B+树更低（无需加载非叶节点的数据）

②：查询效率B+树更加稳定（数据都在叶节点）

③：B+树便于扫库和区间查询（节点之间有双向指针）

‍

聚簇（聚集）索引、非聚簇索引（二级索引/辅助索引）

什么是聚簇索引、非聚簇索引？

聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的

非聚簇索引指的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引

‍

什么是回表查询？

回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表

备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引

分类含义特点
聚集索引 (Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了整行数据必须有，而且只有一个
二级索引 (Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键值可以存在多个

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引

  • 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引

  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

image
image

‍

回表查询

先通过二级索引找到主键值，再到聚集索引根据主键找到整行数据

image
image

‍

覆盖索引、超大分页优化

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

image
image

‍

❓什么是覆盖索引

覆盖索引，即查询利用索引进行，返回的列，必须在索引中全部可寻

以id进行查询，可径直走聚集索引路径，实现一次索引扫描，即刻返回数据，性能优越

若查询返回的列未在索引中创建，则可能引发回表查询，宜尽力避免使用SELECT *​

‍

❓超大分页处理

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案：覆盖索引+子查询

mysql> select * from tb_sku limit 0,10;
10 rows in set (0.00 sec)

mysql> select * from tb_sku limit 9000000,10;
10 rows in set (11.05 sec)

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

select *
from tb_sku t,
    (select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

‍

索引创建的原则

先陈述自己在实际的工作中是怎么用的

  • 主键索引

  • 唯一索引

  • 根据业务创建的索引（复合索引）

‍

1). 针对数据量较大且查询频繁的表，建立索引。单表数据量超过10万条，有助于提升用户体验

2). 针对常用于查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）的字段，建立索引

3). 尽量选择区分度高的列作为索引，并优先建立唯一索引。区分度越高，使用索引的效率也越高

4). 对于字符串类型的字段，若字段长度较长，可根据字段特性建立前缀索引

5). 尽量采用联合索引，减少单列索引的使用。在查询时，联合索引往往能覆盖索引，节省存储空间，避免回表操作，从而提高查询效率

​mysql> show index from tb_seller;​

TableNon_uniqueKey_nameSeq_in_indexColumn_name
tb_seller1tb_seller_index1name
tb_seller1tb_seller_index2status
tb_seller1tb_seller_index3address

6). 控制索引数量，索引并非越多越好。索引越多，维护索引结构的成本也就越大，进而影响增删改的效率

7). 若索引列不能存储NULL值，请在创建表时使用NOT NULL约束。当优化器了解每列是否包含NULL值时，它能更有效地确定哪个索引最适用于查询

‍

什么情况下索引会失效

回答：

  • 违反最左前缀法则（联合索引/复合索引）

    如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列

    匹配最左前缀法则，走索引：

    ​mysql> explain select * from tb_seller where name = '小米科技';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index303const1100.00NULL

    ​mysql> explain select * from tb_seller where name = '小米科技' and status = '1';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index309const,const1100.00NULL

    ​mysql> explain select * from tb_seller where name = '小米科技' and status = '1' and address = '北京市';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index612const,const,const1100.00NULL

    违反最左前缀法则，索引失效： 查询条件：status = '1' and address = '北京市'​

    ​mysql> explain select * from tb_seller where status = '1' and address = '北京市';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLALLNULLNULLNULLNULL128.33Using where

    ​mysql> explain select * from tb_seller where status = '1';​查询条件：status = '1'​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLALLNULLNULLNULLNULL1210.00Using where

    **符合最左法则，但跳跃某一列，只有最左列索引生效：**​name = '小米科技' and address = '北京市'​

    ​mysql> explain select * from tb_seller where name = '小米科技' and address = '北京市';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index303const110.00Using index condition

  • 范围查询右边的列，不能使用索引

    ​mysql> explain select * from tb_seller where name = '小米科技' and status = '1' and address = '北京市';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index612const,const,const1100.00NULL

    ​mysql> explain select * from tb_seller where name = '小米科技' and status > '1' and address = '北京市';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLrangetb_seller_indextb_seller_index309NULL110.00Using index condition

    根据前面的两个字段 name、status 查询是走索引的，但是最后一个条件 address 没有用到索引

  • 在索引列上进行运算操作，索引将失效

    ​mysql> select * from tb_seller where substring(name, 3, 2) = '科技';​

    selleridnamenicknamepasswordstatusaddresscreatetime
    baidu百度科技有限公司百度小店e10adc3949ba59abbe56e057f20f883e1北京市2088-01-01 12:00:00
    huawei华为科技有限公司华为小店e10adc3949ba59abbe56e057f20f883e0北京市2088-01-01 12:00:00
    luoji罗技科技有限公司罗技小店e10adc3949ba59abbe56e057f20f883e1北京市2088-01-01 12:00:00
    ourpalm掌趣科技股份有限公司掌趣小店e10adc3949ba59abbe56e057f20f883e1北京市2088-01-01 12:00:00
    qiandu千度科技千度小店e10adc3949ba59abbe56e057f20f883e2北京市2088-01-01 12:00:00
    sina新浪科技有限公司新浪官方旗舰店e10adc3949ba59abbe56e057f20f883e1北京市2088-01-01 12:00:00
    xiaomi小米科技小米官方旗舰店e10adc3949ba59abbe56e057f20f883e1西安市2088-01-01 12:00:00

    ​mysql> explain select * from tb_seller where substring(name, 3, 2) = '科技';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLALLNULLNULLNULLNULL12100.00Using where

    在索引列上进行运算操作（如 substring(name, 3, 2)），会导致索引失效，查询时会进行全表扫描

  • 字符串不加单引号，造成索引失效

    ​mysql> explain select * from tb_seller where name = '科技' and status = '0';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index309const,const1100.00NULL

    ​mysql> explain select * from tb_seller where name = '科技' and status = 0;​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLreftb_seller_indextb_seller_index303const110.00Using index condition

    由于在查询中没有对字符串加单引号，MySQL的查询优化器会自动进行类型转换，导致索引失效

  • 以 %​ 开头的 Like 模糊查询，索引失效

    ​mysql> explain select sellerid, name from tb_seller where name like '%黑马程序员%';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLALLNULLNULLNULLNULL1211.11Using where

    ​mysql> explain select sellerid, name from tb_seller where name like '%黑马程序员';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLALLNULLNULLNULLNULL1211.11Using where

    如果仅仅是尾部模糊匹配，索引不会失效：

    ​mysql> explain select sellerid, name from tb_seller where name like '黑马程序员%';​

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtb_sellerNULLrangetb_seller_indextb_seller_index303NULL1100.00Using index condition
    • 如果是头部模糊匹配（以 %​ 开头），索引会失效，导致全表扫描
    • 如果仅仅是尾部模糊匹配（以 %​ 结尾），索引不会失效，可以正常使用索引进行查询

‍

谈一谈对 SQL 优化的经验

  • 表的设计优化
  • 索引优化
  • SQL 语句优化
  • 主从复制、读写分离
  • 分库分表

‍

表设计优化

① 例如，设置适当的数值类型（如 tinyint​, int​, bigint​）时，应依据实际情况进行选择

② 例如，在设置字符串类型时（如 char​ 和 varchar​），char​ 类型定长效率较高，而 varchar​ 类型可变长度，效率相对较低

‍

SQL 语句优化

  1. SELECT语句务必指明字段名称

    • 避免直接使用 select *​

  2. 避免造成索引失效的SQL写法

  3. 尽量用 ​union all​​ 代替 ​union​​

    • ​union​ 会多一次过滤操作，效率较低
    • ​select * from t_user where id > 2 union all / union select * from t_user where id < 5​

  4. 避免在 ​where​​ 子句中对字段进行表达式操作

    • 这会导致索引失效

  5. Join 语句优化

    • 能用 inner join​ 就不用 left join​ 或 right join​
    • 如果必须使用 left join​ 或 right join​，一定要以小表为驱动
    • 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边
    • ​left join​ 或 right join​ 不会重新调整顺序
    • 小表决定了数据库连接次数，大表决定了每次连接的操作次数

‍

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离架构

读写分离解决的是，数据库的写入，影响了查询的效率

image
image

‍

事务

事务的特性

事务代表一系列操作的集合，构成一个不可分割的工作单元。事务将所有操作视为一个整体，一并提交至系统或撤销操作请求，确保这些操作要么全部成功，要么全部失败

事务特性：ACID

  • 原子性（Atomicity）：事务作为不可分割的最小操作单元，须保证要么完全成功，要么完全失败
  • 一致性（Consistency）：事务执行完毕后，确保所有数据均保持一致状态
  • 隔离性（Isolation）：数据库系统通过隔离机制，确保事务在不受外部并发操作干扰的独立环境中运行
  • 持久性（Durability）：一旦事务提交或回滚，其对数据库数据的变更即成为永久性记录

结合转账等案例说明

‍

并发事务带来哪些问题

问题描述
脏读一个事务读到另一个事务还没有提交的数据
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读（前提是可重复读）一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

‍

事务隔离

事务隔离级别越高，数据越安全，但是性能越低

隔离级别脏读不可重复读幻读
Read uncommitted 未提交读✅✅✅
Read committed 读已提交❌✅✅
Repeatable Read(默认) 可重复读❌❌✅
Serializable 串行读❌❌❌

‍

redo-log 和 undo-log 的区别

回答：

在数据库系统中，Redo Log​ 主要记录的是数据页的物理变化，其在服务宕机时，可用于数据同步。与之相对的，Undo Log​ 则主要记录逻辑日志。在事务回滚时，通过逆操作来恢复原始数据。例如，当我们删除一条数据时，Undo Log​ 日志文件中会新增一条DELETE​语句；若发生回滚，则执行相应的逆操作

​Redo Log​ 确保了事务的持久性，而Undo Log​ 则保障了事务的原子性和一致性

‍

image​

image​

image
image
image
image

缓冲池（Buffer Pool）: 主内存中的特定区域，用于缓存磁盘上频繁操作的真实数据。在执行增删改查操作时，首先对缓冲池中的数据进行操作（若缓冲池内无数据，则从磁盘加载并缓存）。以一定频率将缓冲池数据刷新回磁盘，以此减少磁盘I/O操作，提升处理速度

数据页（Page）: InnoDB存储引擎在磁盘管理中的最小单元，每个页的默认大小为16KB。页中存储的是行数据

操作时会先看 buffer pool 里面有没有，没有就从磁盘加载进缓冲池，操作完成后，按照一定频率写回磁盘，减少了磁盘的 IO 次数

服务器宕机，会导致内存中的数据还没来得及写入磁盘就丢失，违背了持久化原则

‍

redo log

重做日志记录的是事务提交时数据页的物理修改，其核心作用在于确保事务的持久性

此日志文件由两部分构成：重做日志缓冲（Redo Log Buffer）与重做日志文件（Redo Log File）。前者位于内存之中，后者则存储于磁盘。在事务提交后，所有修改信息将被存入该日志文件。这不仅用于在刷新脏页至磁盘的过程中提供支持，亦在发生错误时，为数据恢复提供依据

image
image
image
image

如果不用redo log，当数据页发生变化后直接进行同步，但操作可能包含多条，这时是随机的磁盘 IO，性能低，使用日志文件都是追加的，是顺序的磁盘 IO

‍

undo log

回滚日志，用于记录数据被修改前的信息，其作用主要包括提供回滚和MVCC（多版本并发控制）。与undo log和redo log记录物理日志不同，它是逻辑日志

  • 可以认为，当执行delete操作删除一条记录时，undo log中会记录一条对应的insert记录；反之亦然
  • 当执行update操作修改一条记录时，它记录一条相反方向的update记录。当执行rollback操作时，就可以从undo log中的逻辑记录读取相应内容，并完成回滚

​undo log​​可以实现事务的一致性和原子性

‍

MVCC 多版本并发控制（Multi-Version Concurrency Control）

回答：

事务的隔离性是通过锁和MVCC（多版本并发控制）实现的。其中，MVCC指的是维护数据的多个版本，确保读写操作不会发生冲突。其底层实现主要分为三个部分：

  1. 隐藏字段：在MySQL中，每个表都设有隐藏字段，包括trx_id​（事务ID）和roll_pointer​（回滚指针）trx_id​记录每次操作的事务ID，且为自增roll_pointer​指向上一个版本的事务版本记录地址
  2. Undo Log日志：主要记录回滚日志，存储老版本数据。在内部，它形成一个版本链，当多个事务并行操作某一行记录时，记录不同事务修改数据的版本。通过roll_pointer​指针形成一个链表
  3. ReadView：解决事务查询选择版本的问题。内部定义了匹配规则和当前事务ID，用以判断访问哪个版本的数据。不同隔离级别的快照读产生的结果不同。在 读已提交（Read Committed） 隔离级别下，每次执行快照读时都会重新生成ReadView；而在 可重复读（Repeatable Read） 隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

指维护一个数据的多个版本，使得读写操作没有冲突

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView

‍

实现原理

记录中的隐藏字段
idagename
11tom
33cat
idagenameDB_TRX_IDDB_ROLL_PTRDB_ROW_ID
11tom
33cat
隐藏字段及其含义
隐藏字段含义
DB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段
undo log

回滚日志，于insert​、update​、delete​操作时生成，旨在便于数据回滚

在insert​操作时，生成的undo log​日志仅在回滚时需要，事务提交后，可被立即删除

而update​、delete​操作时，产生的undo log​日志不仅在回滚时需要，MVCC​版本访问亦需，故不会立即被删除

undo log 版本链
image
image
image
image

对于不同事务或相同事务对同一记录的修改，将生成一条记录的undo日志版本链表。链表的头部代表最新的旧记录，而尾部则是最早的旧记录

ReadView

ReadView（读视图）是快照读在SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）ID

当前读

读取的是记录的最新版本，读取时还需保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：
​SELECT ... LOCK IN SHARE MODE​（共享锁）、SELECT ... FOR UPDATE​、UPDATE​、INSERT​、DELETE​（排他锁）都是一种当前读

快照读

简单的SELECT​（不加锁）就是快照读。快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

  • Read Committed：每次SELECT​都会生成一个快照读
  • Repeatable Read：开启事务后第一个SELECT​语句才是快照读的发生点

readview 中包含的核心字段：

字段含义
m_ids当前活跃的事务ID集合
min_trx_id最小活跃事务ID
max_trx_id预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_idReadView创建者的事务ID

版本链数据访问规则

  1. ​​trx_id == creator_trx_id​​

    • 可以访问该版本
    • 说明：数据是当前事务更改的

  2. ​​trx_id < min_trx_id​​

    • 可以访问该版本
    • 说明：数据已经提交

  3. ​​trx_id > max_trx_id​​

    • 不可以访问该版本
    • 说明：事务在 ReadView 生成后才开启

  4. ​​min_trx_id <= trx_id <= max_trx_id​​

    • 如果 trx_id​ 不在 m_ids​ 中，可以访问该版本
    • 说明：数据已经提交
image
image
image
image

‍

主从同步原理

image​

主从复制的核心在于二进制日志

二进制日志（BINLOG）详尽地记录了所有的DDL（数据定义语言）语句与DML（数据操纵语言）语句，然而，不包括数据查询（SELECT、SHOW）语句

image
image

复制过程分为三步：

  1. Master主库在事务提交时，会将数据变更记录在二进制日志文件Binlog中
  2. 从库读取主库的二进制日志文件Binlog，并将之写入到从库的中继日志Relay Log
  3. Slave重做中继日志中的事件，从而将变更反映至自身的数据

‍

MySQL 分库分表

回答：

业务介绍

  1. 根据简历上的项目经验，构思一个数据量较大的业务（如请求数量众多或业务累积数据量巨大）
  2. 确定业务达到的量级（例如，单表数据量达到1000万条或超过20GB）

具体拆分策略

  1. 水平分库：将一个数据库中的数据分散至多个数据库中，以此解决海量数据存储和高并发的问题
  2. 水平分表：通过此方法解决单表存储容量和性能的瓶颈

水平拆分中间件

  • 使用中间件解决拆分后可能出现的问题，如sharding-sphere​、mycat​等
  1. 垂直分库：根据业务需求进行数据库拆分，在高并发情况下提升磁盘I/O和网络连接数。（常用）
  2. 垂直分表：实现冷热数据分离，确保多表之间互不影响。（常用）

‍

分库分表的时机

  1. 前提：项目业务数据量逐渐增长，或业务发展迅速，单表数据量达到1000万条或20GB以上
  2. 优化措施已无法解决性能问题（如主从读写分离、查询索引等）
  3. 遇到瓶颈（包括磁盘IO、网络带宽等）、CPU瓶颈（如聚合查询、连接数过多等）

‍

拆分策略

垂直分库
image
image

垂直分库：以表为依据，根据业务需求，将不同的表拆分至独立的数据库中

特点：

  1. 按业务对数据进行分级管理、维护、监控及扩展
  2. 在高并发环境下，提升磁盘I/O性能和数据连接数
垂直分表
image
image

垂直分表：依据字段属性，将不同字段分配至不同的表中

特点：

  1. 冷热数据分离
  2. 降低 IO 过渡争抢，两表互不干扰

拆分规则：
将不常用字段独立放置于一张表中
将text​、blob​等大字段拆分并置于附属表中

水平分库
image
image

水平分库：将库中数据拆分至多个库中

特点：

  1. 解决了单库大数量、高并发下的性能瓶颈问题
  2. 提升了系统的稳定性和可用性

路由规则

  • 根据ID节点进行取模操作
  • 按ID进行范围路由，节点1：ID范围（1-100万）
  • 节点2：ID范围（100万-200万）
水平分表
image
image

水平分表：将一个表的数据拆分至多个表中（可在同一数据库内）

特点：

  1. 优化因单一表数据量过大而产生的性能问题
  2. 避免I/O争抢并减少锁表的几率

‍

拆分后的问题

image
image

分库之后面临的问题：

  • 分布式事务一致性
  • 跨节点关联查询
  • 跨节点分页、排序函数
  • 主键防重复

分库分表中间件：

  • ShardingSphere
  • Mycat

‍

最近更新：
贡献者: CharmingDaiDai
Copyright © 2025 CharmingDaiDai