MySQL 中如何进行 SQL 调优？

平时进行 SQL 调优，主要是通过观察慢 SQL，然后利用 explain 分析查询语句的执行计划，识别性能瓶颈，优化查询语句。

1）合理设计索引，利用联合索引进行覆盖索引的优化，避免回表的发生，减少一次查询和随机 I/O

2）避免 SELECT * ，只查询必要的字段

3）避免在 SQL 中进行函数计算等操作，使得无法命中索引

4）避免使用 %LIKE，导致全表扫描

5）注意联合索引需满足最左匹配原则

6）不要对无索引字段进行排序操作

7）连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

除此之外，还可以利用缓存来优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库的压力，提升查询的效率。

还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询等等。

如何在 MySQL 中监控和优化慢 SQL？

可以利用 MySQL 自带的 slow_query_log 来监控慢 SQL，它是 MySQL 提供的一个日志功能，用于记录执行时间超过特定阈值的 SQL 语句。

在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中添加或修改以下配置项：

[mysqld]
slow_query_log = 1                    # 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 指定慢查询日志文件路径
long_query_time = 2.0                 # 设置慢查询的阈值时间（单位：秒）
log_queries_not_using_indexes = 1     # 记录未使用索引的查询

也可以通过 SQL 命令动态设置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

参数有：

  • possible_keys 字段表示可能用到的索引；
  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  • key_len 表示索引的长度；
  • rows 表示扫描的数据行数。
  • type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

  • All（全表扫描）；
  • index（全索引扫描）；
  • range（索引范围扫描）；
  • ref（非唯一索引扫描）；
  • eq_ref（唯一索引扫描）；
  • const（结果只有一条的主键或唯一索引扫描）。

例子

1）创建 employees 表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id)
);

我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 100000 之间的员工，并按薪水降序排序：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

2）我们先使用 explain 分析计划进行分析：

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

输出结果如下：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE employees NULL ref department_id department_id 4 const 5000 20.00 Using where; Using filesort

3）分析执行计划

从执行计划中看出，type 为 ref，表示使用了 department_id 索引，这是个非唯一索引。key 为 department_id 这个索引，而且 rows 为 5000，表示扫描了 5000 行匹配的 department_id = 5 的条件。从 Extra 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句。

4）找出问题

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时。

5）优化解决它！

创建复合索引

创建一个包含 department_id 和 salary 的复合索引，这样可以覆盖查询的 WHERE 和 ORDER BY 条件：

CREATE INDEX idx_department_salary ON employees (department_id, salary);

复合索引可以使查询在扫描 department_id 列时，同时按 salary 列排序，避免额外的文件排序。

如何处理 MySQL 的主从同步延迟？

首先需要明确一个点延迟是必然存在的，无论怎么优化都无法避免延迟的存在。

如果主从延迟过大，可能出现一个用户刚注册，然后登陆报该用户不存在的，这个体验对用户而言就太差了。

之所以会产生这个原因是因为数据是写到主库中的，而查询走从库，因为延迟原因使得数据还未同步完毕，导致查不到这个用户。

常见解决方式有以下几种：

  • 二次查询。如果从库查不到数据，则再去主库查一遍，由 API 封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了。
  • 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板。
  • 关键业务读写都走主库，非关键还是读写分离。比如上面我举例的用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口。
  • 使用缓存，主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题。

什么是分库分表？分库分表有哪些类型（或策略）？

随着用户量的激增和时间的堆砌，存在数据库里面的数据越来越多，此时的数据库就会产生瓶颈，出现资源报警、查询慢等场景。

首先单机数据库所能承载的连接数、I/O及网络的吞吐等都是有限的，所以当并发量上来了之后，数据库就渐渐顶不住了。

再则，如果单表的数据量过大，查询的性能也会下降。因为数据越多 B+ 树就越高，树越高则查询 I/O 的次数就越多，那么性能也就越差。

因为上述的原因，不得已就得上分库分表了。

把以前存在一个数据库实例里的数据拆分成多个数据库实例，部署在不同的服务器中，这是分库。

把以前存在一张表里面的数据拆分成多张表，这是分表。

一般而言：

  • 分表：是为了解决由于单张表数据量多大，而导致查询慢的问题。大致三、四千万行数据就得拆分，不过具体还是得看每一行的数据量大小，有些字段都很小的可能支持更多行数，有些字段大的可能一千万就顶不住了。
  • 分库：是为了解决服务器资源受单机限制，顶不住高并发访问的问题，把请求分配到多台服务器上，降低服务器压力。

在我的理解中分库分表分为四个类型，垂直分表、垂直分库、水平分表、水平分库。

垂直分表

1）简单来说就是将原本的一张表切割成多张表。举个例子：有张 student 表存储着学生家庭背景、学生入学宣言（500字）、学生信息，现在将其进行垂直分表，可以分为 学生基本信息表、学生入学宣言表、学生家庭背景表，将其进行切割。

2）垂直分表一般是将不常用的字段单独放在一张表、将大字段分一张表、把经常需要同时查出来的信息放一张表。这样做可以冷数据和热数据分开提高查询效率。

垂直分库

1）按我个人理解就是把一个数据库里面的多个表，按照功能，分成多个数据库存放。举个例子：一个数据库有很多张表，用户表、商品表、订单表等，那么可以根据功能属性进行垂直分库，将用户表等信息放到存放用户信息的数据库，将商品表、订单表存放到与商品订单有关的商品订单库。

2）这样做的好处就是将数据负载分散到不同的数据库上，从而提高系统的性能和扩展性、降低单一数据库的复杂度。

水平分表

1）我个人的理解就是在同一个数据库中，有几个相同表，里面的数据是不一样的，但表结构是一样的，数据按照固定的规则选择数据表存放，如：商品表1、商品表2。

2）提高了读写性能，减少了单表的压力、可弹性增加存储容量只需要增加一个表就行。

水平分库

1）按我的理解来说就是相同的表结构复制一份分到另一个库中，每个库的表结构是一样的，但是数据是不一样的。

2）这样做能在大数量的情况下提高读写性能，因为减少了单一数据库的读写压力。

3）能提高存储容量。可以通过增加或减少数据库进行弹性伸缩。

4）提高容错性。当一个数据库故障了，别的数据库还能正常运行，只影响小部分数据查询。

对数据库进行分库分表可能会引发哪些问题？

1）首先是事务的问题。

我们使用关系型数据库，有很大一点在于它保证事务的完整性。

而分库之后单机事务就用不上了，必须使用分布式事务来解决，而分布式事务相对而言就比较重了，而且大部分的分布式事务只能保证最终一致性，所以业务上会存在数据不一致的场景。

2）连表 JOIN 问题

在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。

此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。

这种方式实现起来稍微比较复杂，不过也是可以接受的。

还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。

或者通过宽表的形式查询，比如将数据全量存储至 ES 中，利用 ES 来查询数据。

3）全局 ID 唯一性问题

以前单库单表直接使用数据库的自增 ID 即可，但是分库分表之后，使用自增 ID 会导致重复主键的情况，此时需要利用雪花算法或者其他全局唯一 ID 发号器来生成唯一主键。

什么是雪花算法？

雪花算法（Snowflake Algorithm）是由 Twitter 开发的一种用来生成分布式系统中全局唯一的 ID 的算法。

雪花算法生成的唯一 ID 通常是一个 64 位的整数，按照以下结构组成：

  • 首位符号位（固定为 0）: 符号位始终为 0，保证生成的是正整数。
  • 41 位时间戳（毫秒级）: 表示生成 ID 的时间戳，可以支持约 69 年的时间范围。
  • 10 位机器标识（分布式部署时的机器 ID）: 可以支持 1024 台不同的机器。
  • 12 位序列号（同一机器同一毫秒内的自增序列）: 表示同一台机器同一毫秒内生成的不同 ID 的序列号。

雪花算法原理图如下：

雪花算法能生成分布式全局唯一 ID 的原因：

  • 雪花算法允许在同一毫秒内生成多个不同的 ID，通过序列号的自增保证在高并发情况下生成的 ID 唯一性。
  • 通过机器部分的标识符保证了在不同的机器上生成 ID 时不会发生冲突。
  • 利用时间戳部分的信息，确保生成的 ID 按时间递增，可以方便地对 ID 进行排序和分析。

雪花算法在同一毫秒内最多可以生成多少个全局唯一ID呢：同一毫秒的ID数量 = 1024 * 4096 = 4194304

雪花算法的缺点：

  1. 依赖服务器时间，服务器时间回拨时可能会生成重复 id。
    • 人为原因，把系统环境的时间改了；
    • 有时候不同的机器上需要同步时间，可能不同机器之间存在误差，那么可能会出现时间回拨问题。
    • 解决方案：算法中可通过记录最后一个生成 id 时的时间戳来解决，每次生成 id 之前比较当前服务器时钟是否被回拨，避免生成重复 id。
  2. 在单机上，生成的ID是递增的，但在多台机器上，只能大致保持递增趋势，并不能严格保证递增。这是因为多台机器之间的时钟不一定完全同步。

4）排序问题

单表直接通过 order by 进行排序即可，分库分表后直接利用数据库是无法实现排序的。要么利用分库分表中间件的能力进行汇总排序，要么自己在业务代码中排序，要么利用 ES 存储全量数据排序查询。

5）count 问题

其实和排序问题类似，单表可以直接 count，分库分表后无法支持，只能多表 count 然后业务代码中累加，或者单独搞一个地方来维护总数，要么还是利用 ES。

分库分表的中间件有哪些？

ShardingSphere

  • ShardingSphere是一款开源的分布式数据库中间件，提供了分库分表、读写分离、分布式事务等功能。
  • 它支持多种数据库，如MySQL、PostgreSQL、Oracle、SQL Server等，并且可以与现有的数据库系统无缝集成。

架构与原理

ShardingSphere由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar三个主要组件组成。

  • Sharding-JDBC：用于实现分库分表功能的模块，它可以在应用层通过简单的配置实现透明的分库分表操作。
  • Sharding-Proxy：用于实现数据库代理功能的模块，它可以将数据库请求路由到不同的数据库节点上，实现读写分离和负载均衡。
  • Sharding-Sidecar（规划中）：定位为Kubernetes的云原生数据库代理，以Sidecar的形式代理所有对数据库的访问。

其原理是通过数据分片和路由来实现分库分表。数据分片是将数据划分为多个片段，每个片段存储在不同的数据库实例或数据表中；路由则是根据数据的分片规则将请求路由到对应的数据库实例或数据表上。

优势与适用场景

  • 灵活的扩展性：支持水平扩展和垂直扩展，可以根据业务需求灵活调整数据库的规模和性能。
  • 高可用性：支持主从复制和多活架构，提供高可用的数据库访问和数据保护。
  • 简化开发和维护：提供了简单易用的接口和配置，可以减少开发人员的工作量和维护成本。
  • 适用于高并发访问、大数据量存储、跨地域部署等场景。

MyCAT

  • MyCAT是一个开源的分布式数据库中间件，基于Java编写，支持MySQL协议，可以作为MySQL的代理服务器使用。
  • 它支持分库分表、读写分离、全局序列号等功能，并且具有跨语言、跨平台、跨数据库的通用性。

架构与原理

  • MyCAT采用代理模式来实现数据库的路由和分片。
  • 它包括MyCAT-Server和MyCAT-DataNode两个主要部分。MyCAT-Server用于接收客户端的数据库请求，并将请求路由到不同的数据库节点上；MyCAT-DataNode则用于实际存储数据的数据库节点。

优势与使用场景

  • 易于部署和使用：对于项目来说是透明的，如果遇到升级之类的操作，只需要在中间件层面进行即可。
  • 适用于大规模MySQL集群的管理和扩展问题。
  • 但是，MyCAT的SQL支持相对较弱，可能需要对SQL语句进行一定的改写和优化。

MySQL 中如何解决深度分页的问题？

所谓的深度分页是指数据量很大的时候，按照分页访问后面的数据，例如 limit 99999990，10，这会使得数据库扫描前面的 99999990 条数据，才能得到最终的 10 条数据，大批量的扫描数据会增加数据库的负载，影响性能。

优化方式可以有三种：

1）子查询

比如 select * from table where name = '111' limit 99999990，10； 这样的一条查询语句，可以优化成：

select * from table where 
name = '111' 
and id > 
(select id from table where name = '111' order by id limit 99999990，1)
order by id limit 10;

name 有索引的情况下，这样的查询直接扫描 name 的二级索引，二级索引的数据量少，且在子查询中能直接得到 id 不需要回表。将子查询得到的 id 再去主键索引查询，速度很快，数据量也小。

如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据。

当然上面的 SQL 改成 Join 也行，本质上是一样的。

2）记录 id

每次分页都返回当前的最大 id ，然后下次查询的时候，带上这个 id，就可以利用 id > maxid 过滤了。

这种查询仅适合连续查询的情况，如果跳页的话就不生效了。

3）elasticsearch

可以考虑用搜索引擎来解决这个问题，不过 es 也会有深度分页的问题，所以如果对 es 不熟，面试就不要这样答了，防止面试官问 es 的深度分页如何解决。

如何实现数据库的不停服迁移？

迁移想着很简单，不就是把一个库的数据迁移到另一个库吗？

但是实际上有很多细节：

  • 首先关注量级，如果是几十万的数据其实直接用代码迁移，简单核对下就结束了。如果数据量大那么才需要好好设计方案。
  • 不停服数据迁移需要考虑在线数据的插入和修改，保证数据的一致性。
  • 迁移还需要注意回滚，因为一旦发生问题需要及时切换回老库，防止对业务产生影响。

双写

大部分数据库迁移都会采用双写方案，例如自建的数据库要迁移到云上的数据库这个场景，双写就是同时写入自建的数据库和云上的数据库。

我们来过一遍迁移流程：

1）将云上数据库（新库）作为自建数据库（旧库）的从库，进行数据同步（或者可以利用云上的能力，比如阿里云的 DTS）。

2）改造业务代码，数据写入修改不仅要写入旧库，同时也要写入新库，这就是所谓的双写，注意这个双写需要加开关，即通过修改配置实时打开双写和关闭双写。

3）在业务低峰期，确保数据同步完全一致的时候（即主从不延迟，这个都是有对应的监控的），关闭同步，同时打开双写开关，此时业务代码读取的还是旧数据库。

4）进行数据核对，数据量很大的场景只能抽样调查（可以利用定时任务写代码进行抽样核对，一旦不一致就告警和记录。）

5）如果确认数据一致，此时可以进行灰度切流，比如 1% 的用户切到读新的数据库（比如今天访问前 1% 的用户或者根据用户 ID 或其他业务字段），如果发现没问题，则可以逐步增加开放的比例，比如 5%->20%->50%->100%

6）继续保留双写，跑个几天(或者更久)，确保新库确实没问题了，此时关闭双写，只写新库，这时候迁移就完成了。

补充

除了主从同步，代码双写的方案，也可以采用第三方工具。例如 flink-cdc 等工具来进行数据的同步，它的优点方便，且支持异构（比如 mysql 同步到 pg、es 等等）的数据源。

什么是数据库的逻辑删除？数据库的物理删除和逻辑删除有什么区别？

逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式。一般是在表中添加一个表示删除状态的字段，如 is_deleted ，默认是 0 表示未删除，1 表示已删除。

物理删除则是直接从数据库中删除记录。

一般业务上都是使用逻辑删除，便于后续的数据分析、追溯等。

逻辑删除与唯一性问题

很多时候表需要设置唯一索引来保证数据的唯一性。

例如用户参加店铺活动，我们有个活动记录表以 userId+shopId 作为唯一索引，防止用户重复参加一个店铺活动也用于后续活动记录。

后续如果用户反悔了，他说不想参加了，此时一个方式就是直接物理删除这条记录，但是为了审计和后续的追溯，我们用的都是逻辑删除，因此使用 is_deleted 字段，标记为 1 表明已删除。

但是用户又反悔了，他又报名参加了，这时候由于我们将 userId+shopId 作为唯一索引，且这条字段仅仅是逻辑删除，表中还存在这条记录，所以此时就产生了唯一索引冲突，业务无法正常执行下去。

这就是逻辑删除与唯一性问题。

怎么处理？把 userId+shopId+is_deleted 一起作为唯一索引？

也不行，is_deleted 只有 0 和 1，如果用户多次反复横跳，这个设计无法满足。

常见有三种简单的解决方式：

1）is_deleted 改为 deleted_at ，deleted_at 存储的是时间戳，默认为空。

把 userId+shopId+deleted_at 作为唯一索引，通过 deleted_at 是否为空来判断是否被逻辑删除，然后每次删除把当前时间设置到 deleted_at 字段上，这样唯一索引就能继续生效了。

由于时间戳的随机性，不会产生唯一索引冲突

2）is_deleted 改为 bigint，存储的是主键，默认为 0 。

每次删除，把当前记录的 id 设置到 is_deleted 字段，例如 id 是 2222 ，那么 is_deleted 就变为 2222。这时候将 userId+shopId+is_deleted 作为唯一索引，就可以解决用户反复退出再加入的问题。

判断已经被删除的逻辑从 is_deleted = 1 变成 is_deleted > 0。

3）复用一条记录+日志表（流水表）

所谓的日志或者流水指的是机械地记录用户的操作记录，不会做修改和删除，只有新增。

用户某时某刻参加了活动，则记录。

用户退出了活动，则记录。

审计和追溯可以使用流水表。这时候活动表仅需设置 userId+shopId 为唯一索引即可，用户参加活动 is_deleted 为 0 ，退出活动改为 1，再次参加继续改为 0 ，仅需在一条记录上修改记录。

什么是数据库的逻辑外键？数据库的物理外键和逻辑外键各有什么优缺点？

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性。

逻辑外键实现例子

比如插入一笔订单的时候，先判断这个用户是否存在：

// 检查引用完整性
public void insertOrder(Order order) {
    if (!customerExists(order.getCustomerId())) {
        throw new IllegalArgumentException("用户不存在");
    }
    // 插入订单
    orderRepository.save(order);
}

private boolean customerExists(Long customerId) {
    return customerRepository.existsById(customerId);
}

如果是复杂的业务，例如还需要判断用户是否是已注销、是否被冻结等等，此时仅需修改 customerExists 就能实现，而物理外键就没办法实现这么灵活的需求了。

为什么不推荐使用数据库的外键？

阿里巴巴 Java 开发手册写到了：

可以看到，主要是因为数据库的外键会产生级联更新从而导致性能问题。

因为现在很多互联网公司都是高并发大流量场景，所以一般互联网公司不推荐使用外键。

数据库外键性能问题盘点

1）级联更新

按照手册举例，如果学生表的 ID 被修改，数据库同时需要改成绩表中的对应的学生 ID，如果还有其他关联表则都需要被修改。

在高并发数据量大的情况下，一次修改会产生意料之外的级联更新使得数据库压力过大，导致系统其他操作数据库的请求阻塞，很可能导致系统全面崩盘。

2）检查维护

还是拿学生成绩举例，因为设置了外键，插入成绩表对应的学生 ID 时，数据库需要去检查这个学生 ID 在对应的学生表是否存在，来保证数据外键完整性约束。

除了插入，删除、更新相关外键，数据库都需要去检查数据的完整性，这就产生了性能开销。

3）锁问题

如果多个事务并发修改学生表，那么对应锁定的数据涉及的成绩表也需要被锁定，同理并发修改成绩表，那么对应的学生的表的数据也需要被锁定。

关联外键越多锁定的数据也就越多。锁的数据多除了性能问题，还可能会带来死锁的问题。

数据库外键业务问题盘点

1）无法支持复杂的业务控制

不说其他复杂的情况，就拿删除数据使用逻辑删除的情况，数据库的外键就无法满足级联修改，还是需要开发人员编写业务代码手动控制。

2）分库分表

数据库的外键约束只能控制一个数据库实例，跨实例的情况下无法满足。

参考链接

数据库系列: 主流分库分表中间件介绍(图文总结) - Hello-Brand - 博客园 (cnblogs.com)