# MySQL面试总结

# 库表设计

# 数据库三范式

# 第一范式(字段不可分割)

数据库表设计第一范式是指数据库表中的每一个列都不可再分，即表中的列具有原子性。如果某一列具有多值，按第一范式，该列就应该单独拆解出去一个新表，新表与原表具有一对多关系。

# 第二范式(非主键字段都和主键字段有强关联)

满足2NF的前置条件为需要满足1NF，2NF针对联合主键定义，对非联合主键也同样生效，即表中非主键字段对联合主键不能仅仅是部分依赖(如a，b，c，d4列，abc为联合主键，d列一定要只能通过abc列唯一确定，而不是通过ab列唯一确定)

# 第三范式(不能有冗余字段)

满足3NF的前置条件为满足2NF，3NF要求一个数据表中不能包含已在其他表中包含的非主键信息。(即表之间除了外键之外，不能有冗余字段)。

# 反范式设计

常见的反范式设计主要是增加冗余字段(即满足2NF，而不满足3NF)，这样可以减少表之间的连接查询，从而提升性能。拿我自己负责的系统举例（遵从业界规范），每个表都有一个逻辑主键id字段，不存在所谓联合主键的概念，即可以确定至少满足了2NF的设计，至于是否满足3NF，则是看具体情况具体分析的。

# 索引

# Mysql索引为什么使用B+树而不使用红黑树呢?

CPU，内存，磁盘，这3者之间的运算速度不是一个量级的，CPU远大于内存，内存远大于磁盘。在数据库的使用场景中，数据是需要持久化到磁盘中的，而数据的运算则需要在CPU中进行，因此怎么尽可能快的读取磁盘中的数据到内存进而转交给CPU运算就成了重点。
针对重复度低且有序的数据，我们可以通过建立数据索引的方式来提速，这样在查找时只要查找索引，就可以在磁盘中快速查到到数据。
针对索引的数据结构，我们选择B+树的原因在于它的树高比较低，树高对应树的遍历，也就对应磁盘的读写次数。以亿级数据举例，在Mysql中，B+树的阶约为1600，树高不超过4层，也就是读写4次磁盘就能找到数据了。而亿级数据构建的红黑树，树高最大为54。读取54次磁盘和读取4次磁盘，性能显然是天差地别。

# 索引在什么情况下可能失效?

查询条件包含or，每一个条件都需要包含索引，否则很可能导致索引失效
如果字段是字符串类型，where查询时涉及整数的字符串一定要加引号，否和很可能因为发生隐式转换导致索引失效
like关键字查询时，如果查询条件如'%abc','%abc%'，则索引失效，如查询条件'abc%'，则索引不会失效
针对索引列的特殊操作（如加减乘除四则运算、Mysql内置函数使用）可能导致索引失效
索引字段的!=以及not in运算可能导致索引失效
Mysql内置优化器评估全表扫描的性能比索引要好的情况（如in语句中包含过多的字段），索引会失效

# 什么是回表，如何减少回表?

# 什么是回表

回表指的是当使用普通索引查询时，如果需要的字段不在索引中，需要根据索引找到的主键ID，再回到主键索引（聚簇索引）中查找完整记录的过程。

# 如何避免回表

在select中只返回必要的字段，不要使用*通配符返回(一定触发回表)，如果select返回的字段不在索引中，可以考虑增加联合索引以免回表。

# 大表如何创建索引

在单表数据量达到10G这个数量级的时候，我们就不能随随便便给表增加索引了，因为索引的操作会锁表（10G数据量表会锁5到10分钟），这个动作我们只能在变更窗实施，当然也可以创建临时表，将原表数据拷贝到临时表，再删除原表，重命名临时表的方式来实现(无需自己实施，有现成的工具类pt-online-schema-change解决这个问题)。

# 聚簇索引和非聚簇索引的区别?

聚簇索引指的是索引和索引对应的数据存储在一起，非聚簇索引则表示索引和索引对应的数据分开存储,一个表里只有一个聚簇索引（主键及对应的数据）。因此select * from pk;这个操作不会涉及回表。

# 事务

# 事务有那几个特性?如何保证这些特性?

# 原子性

原子性指的是针对事务的一批操作，要么全部成功，要么全部失败，mysql通过undolog(回滚日志保证原子性)，具体来说就是事务操作会提前记录回滚日志,如果事务执行失败就会执行回滚日志。

# 持久性

持久性指的是一旦事务执行成功，就应该持久化存储（永久生效），mysql通过redolog保证持久性。当修改数据时，InnoDB 不会立即将更新后的数据页写入磁盘（随机IO，速度慢），而是先把这个修改记录到 redo log 中（顺序IO，速度快），并更新内存中的数据页。事务提交时，会强制将 redo log 刷盘（fsync）。

# 隔离性

隔离性指的是两个事务修改同一个数据时的隔离策略，底层要解决的问题和Java中的并发控制一样，Mysql通过锁机制(针对两个事务“加锁”)和多版本并发控制MVVM来保证事务的隔离性。

# 一致性

只要原子性，隔离性，持久性三个操作满足，就一定满足一致性。

# 事务的隔离级别?每个级别可能出现的问题?

# 读未提交

A事务可以读取到B事务尚未提交的数据，这种隔离级别最低，性能最快，同样引发的问题也最多。且部分问题根本无法解决，因此没有数据库使用这个隔离级别。该隔离级别会出现脏读(只有这个级别会引发脏读问题)，不可重复读,幻读问题。

脏读的示例

-- 事务A
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 余额从1000改为900，但未提交

-- 事务B（使用读未提交隔离级别）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE user_id = 1; -- 读取到900（脏读！）

-- 事务A回滚
ROLLBACK; -- 余额恢复为1000，但事务B已经读到了不存在的900余额

# 读已提交

读已提交指的是事务只能读取到其他事务已提交的数据，在这个隔离级别下不会出现脏读问题，但是依然会出现不可重复读以及幻读问题。

不可重读的示例

时间	事务A	事务B	结果
T1	SELECT age FROM users WHERE id = 1;		返回 25
T2		UPDATE users SET age = 26 WHERE id = 1;
T3		COMMIT;	修改已提交
T4	SELECT age FROM users WHERE id = 1;		返回 26 ← 不可重复读！

# 可重复读

可重复读指的是在一个事务中多次执行相同的查询读取数据，读取的结果一致。可重读是基于快照实现的，可重读隔离级别下仅会出现幻读问题。

幻读的示例

幻读指的是A事务可以读取到B事务已经提交的写入数据(特指新增/删除数据)。

-- 事务A（RR级别）
SELECT * FROM orders WHERE amount > 1000; 
-- 返回3条记录（基于快照读）

-- 事务B插入新数据并提交
INSERT INTO orders (amount) VALUES (1500);
COMMIT;

-- 事务A尝试更新（注意：不是SELECT！）
UPDATE orders SET status='processed' WHERE amount > 1000;
-- 此时会更新4条记录！包括事务B新增的1500

-- 事务A再次查询
SELECT * FROM orders WHERE amount > 1000; 
-- 仍返回3条记录（快照读），但实际影响了4条数据！

# 串行化

事务的串行化就是将两个事务之间(两个线程)的资源竞争转换成线程按顺序执行，这个方案不会出现任何问题，但是性能奇差，没有任何一个数据库使用这个方案来作为事务的隔离级别。

# 脏读，不可重复读，幻读三者的区别?

问题	描述	核心操作
脏读	读到了别人没提交的数据。	UPDATE
不可重复读	读到了别人已提交的修改。	UPDATE
幻读	读到了别人已提交的新增或删除。	INSERT / DELETE

# Mysql是如何实现可重复读的?

解决可重复读的唯一方式只有“快照”，也就是在一个事务开始执行的时候对Mysql所有数据做快照，让当前事务查询快照获取数据，当然，实际实现中显然不能这样做。Mysql是通过MVCC（多版本并发控制）来实现的。

# 针对每一个事务生成事务id

在Mysql中，每一个事务开始时，Mysql都会生成一个事务id。

# 数据表中增加隐藏字段及维护数据链

在事务对行数据进行修改的时候，将当前事务id写入到隐藏字段中。同时Mysql还会使用undolog维护修改数据的历史版本数据链。

# 生成ReadView

在没一个事务第一次执行select语句的时候，Mysql都会生成专属于该事务的ReadView,ReadView中记录当前时间点还未提交的事务ID,以及当前时间点系统中最大的事务ID。

# 可见性判断

当查询一条数据的时候，就遍历这条数据的事务链表，如果链表的id小于ReadView最小的那个，说明这个事务是已提交的，可见。
遍历事务聊表，如果链表的id大于ReadView最大的那个，说明事务是由未来版本修改的，不可见。
ReadView最小值小于当前遍历的事务id小于ReadView最大值时,判断ReadView中是否包含该事务Id,包含则说明事务还在活跃，不可见。不包含则说明事务已经提交完了，可见。

# 旧版本事务的清理

旧版本的事务undolog不可能一直保留，应该有清理机制清理undolog，具体使用什么清理机制不用过度关注，知道undolog会被清理就可以了，非要说的话完全可以类比jvm的垃圾回收机制，标记清扫+STW就可以了。

# Mysql可重复读如何解决幻读问题?

间隙锁指的是在事务中，针对包含索引的字段，可以锁定一个范围的数据，以防止其他事务在间隙中插入新记录。如可以针对索引为[3,10]，[20,30]的数据段加间隙锁。间隙锁有如下特定:

只在可重复读事务隔离级别生效。
普通的SELECT语句不会触发间隙锁，只有SELECT ... FOR UPDATE;查询才会触发间隙锁。

# 调优

Mysql调优的思路首先是开启慢查询日志记录慢SQL,找到慢SQL之后通过explain分析SQL的执行计划,通过performance_schema分析执行耗时,通过Optimizer Trace分析详情，找到问题后采取相应的措施优化即可。

# 开启慢查询日志

首先，我们可以通过如下三个sql查看慢查询日志是否开启，设置的阈值大小，以及慢查询日志的存放路径:

# 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';
# 查看慢查询的阈值时间(默认是10s),实际项目中DBA设置的时间是1s(高并发场景下该参数会要求设置在200ms以下)
SHOW VARIABLES LIKE 'long_query_time';
## -- 临时修改（重启后失效）
## SET GLOBAL long_query_time = 2;
## -- 永久修改，需要在配置文件中设置
## -- my.cnf 或 my.ini 文件中添加：
## -- long_query_time = 2
# 查看慢查询日志的归档路径
# 日志默认存储在Mysql的安装目的下的Data目录(通过everything找文件名即可找到)
# 在我的windows下存储在C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-D1GIEIDB-slow.log中
SHOW VARIABLES LIKE 'slow_query_log_file';
## 记录未使用索引的查询（即使执行时间较短）
SET GLOBAL log_queries_not_using_indexes = ON;

# 慢查询日志示例

# SQL执行的时间 Time: 2025-08-27T10:09:19.096816+08:00 
# SQL执行的用户 User@Host: nacos[nacos] @  [10.6.1.1]  Id:   351 
# 查询时间 Query_time: 246.846545  Lock_time: 0.000019 Rows_sent: 1  Rows_examined: 18492 
# 补充说明，这条sql执行慢的原因不详，数据库就2w+数据，执行解释计划发现是全表查询,需要针对f_lore_id字段加索引
SET timestamp=1756260312;
select loreconten0_.f_id as f_id1_25_, loreconten0_.f_create_time as f_create2_25_, loreconten0_.f_flag as f_flag3_25_, loreconten0_.f_update_time as f_update4_25_, loreconten0_.f_content as f_conten5_25_, loreconten0_.f_content_cn as f_conten6_25_, loreconten0_.f_content_en as f_conten7_25_, loreconten0_.f_lore_id as f_lore_i8_25_, loreconten0_.f_sort as f_sort9_25_ from k7_lore_lore_content loreconten0_ where loreconten0_.f_lore_id='e608b714-c50d-4e77-b4a6-74bcdb5e0bb8';

# explain查看sql执行计划

找到慢查询的sql后，我们可以通过explain+sql的方式查看sql的执行计划:<br/

explain select * from lore_lore_content lc where lc.f_lore_id = 'e608b714-c50d-4e77-b4a6-74bcdb5e0bb8';

# id:查询序列号
## id相同：执行顺序从上到下
## id不同：id值越大优先级越高，越先执行
## id为NULL：表示结果集，不需要查询

# select_type ：查询类型
## SIMPLE：简单SELECT，不包含子查询或UNION
## PRIMARY：最外层的查询
## SUBQUERY：子查询中的第一个SELECT
## DERIVED：派生表（FROM子句中的子查询）
## UNION：UNION中的第二个或后面的SELECT语句
## UNION RESULT：UNION的结果
## DEPENDENT SUBQUERY：依赖外部查询的子查询
## UNCACHEABLE SUBQUERY：结果无法缓存的子查询

# table:表名

# type:链接类型 (重点关注 type 字段：避免 ALL 和 index)
## 类型	描述	性能
## system	表只有一行记录	最佳
## const	通过主键或唯一索引查询	极佳
## eq_ref	关联查询使用主键或唯一索引	优秀
## ref	使用普通索引查询	良好
## fulltext	全文索引搜索	良好
## ref_or_null	类似ref，但包含NULL值	良好
## index_merge	索引合并优化	良好
## unique_subquery	在IN子查询中使用唯一索引	良好
## index_subquery	在IN子查询中使用普通索引	一般
## range	索引范围扫描	一般
## index	全索引扫描(完整遍历索引)	较差
## ALL	全表扫描	最差

# possible_keys:可能使用的索引

# key:实际使用的索引

# ref:具体索引的哪些列被使用

# rows: 预估扫描行数

# Extra：额外信息
## Using index：使用覆盖索引 (性能良好)
## Using index for group-by：使用索引优化GROUP BY (性能良好)
## Using where：在存储引擎检索后过滤 
## Using temporary：使用临时表 (需要关注，可能需要优化)
## Using filesort：使用文件排序 (需要关注，可能需要优化)
## Using join buffer：使用连接缓存
## Impossible WHERE：WHERE条件永远为false
## Select tables optimized away：使用聚合函数优化

# 执行计划的分析思路

重点关注 type 字段：避免 ALL 和 index
检查 key 字段：确保使用了合适的索引
分析 rows 字段：扫描行数越少越好
注意 Extra 字段：避免 Using temporary 和 Using filesort

# 分析Performance Schema

Performance Schema（性能模式）是 MySQL 提供的一个强大的用于监控 MySQL 服务器在运行时内部执行情况的功能。它是在代码层植入的、用于收集数据库性能数据的存储引擎。其核心目标是尽可能小地影响数据库性能的前提下，提供丰富的服务器内部运行数据，帮助你进行性能诊断、问题排查和优化。你可以把它想象成一个为 MySQL 服务器安装的“实时诊断和性能仪表盘”。

# 查看Performance Schema是否启用

SHOW VARIABLES LIKE 'performance_schema';

# 查看performance_schema库的表结构

# 使用performance_schema库
USE performance_schema;
# 查看库表
SHOW TABLES;

# 查看当前连接状态

SELECT * FROM threads WHERE TYPE='FOREGROUND';

# 查找执行最耗时的10条SQL

SELECT 
    DIGEST_TEXT AS query,
    SCHEMA_NAME AS db,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT AS total_latency,
    AVG_TIMER_WAIT AS avg_latency,
    SUM_ROWS_SENT AS rows_sent,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_CREATED_TMP_TABLES AS tmp_tables,
    SUM_NO_INDEX_USED AS no_index_used,
    DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 10;

# 分析Optimizer Trace

# 启用和使用Optimizer Trace

-- 1. 开启优化器跟踪
SET SESSION optimizer_trace = "enabled=on";

-- 2. 执行你的查询
select * from lore_lore_content lc where lc.f_lore_id = 'e608b714-c50d-4e77-b4a6-74bcdb5e0bb8';

-- 3. 查看跟踪结果
SELECT * FROM information_schema.OPTIMIZER_TRACE;

-- 4. 关闭跟踪
SET SESSION optimizer_trace = "enabled=off";

# 整体优化思路

-- 推荐的性能分析工作流：
-- 1. 先用 Performance Schema 找到慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

-- 2. 用 EXPLAIN 查看执行计划
EXPLAIN SELECT * FROM slow_query_table WHERE conditions;

-- 3. 如果执行计划不符合预期，使用 Optimizer Trace 深入分析
SET SESSION optimizer_trace = "enabled=on";
EXPLAIN SELECT * FROM slow_query_table WHERE conditions;
SELECT JSON_PRETTY(TRACE) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- 4. 根据分析结果优化（添加索引、重写查询等）
-- 5. 用 Performance Schema 验证优化效果

# SQL调优问题总结

多数的慢SQL都和索引有关，比如未加索引，索引不生效、不合理等，只要优化索引就能解决问题。
优化SQL语句，如针对in元素过多问题(通过分批处理)以及深分页问题(基于上一次数据过滤)等问题进行处理。
单表数据量过大导致的慢查询可考虑从业务层面水平分表(如我在负责的一个短信业务，前端只支持查询某一个月的短信发送记录)。
全文检索类场景通过SQL无法很好的优化，可以考虑引入ES全文检索组件。

# 慢SQL场景分析

# 隐式转换导致索引失效

如数据库表中user_id字段是一个字符串类型参数，我们在查询时使用了整数，如userId=123去查询，SQL执行过程中不会报错，而是将123转换成字符串去查询，虽然也能查询到结果，但是索引会失效。这种情况一般很容易识别和发现，通过explain查看SQL的执行计划，只要看到走了全表扫描没走索引一般都能分析出来。

# 最左匹配

Mysql中，如果我们针对a,b,c三个字段建立索引，实际Mysql生成的索引是(a)，（a,b），(a,b,c)三个索引，使用索引时，我们的查询条件只能是(a)，（a,b），(a,b,c)，否则索引不生效。
针对这种情况，我们只能增加新的索引来解决问题。极特殊的情况下，我们的查询结果也只有a,b,c那么我们可以考虑索引覆盖的情况，也就是全遍历索引而不是全遍历表。

# 索引字段使用不等于判断

如果我们使用了不等于条件做判断，这种情况是没办法走索引的，如下:

-- 以下查询通常无法有效使用索引
SELECT * FROM users WHERE status != 'deleted';
SELECT * FROM orders WHERE amount <> 100;

我们可以通过把不等于条件改成多个等于条件的拼接/in语句来实现走索引:

-- 原始查询（索引失效）
SELECT * FROM users WHERE status != 'deleted';

-- 优化后（可能使用索引）
SELECT * FROM users 
WHERE status = 'active' 
   OR status = 'pending' 
   OR status = 'suspended';

-- 当状态值有限时使用IN
SELECT * FROM users 
WHERE status IN ('active', 'pending', 'suspended');

把不等于改成取反的等于肯定是有问题的，业务不够直观，每次增加等于的条件的时候都需要修改不等于的sql，建议使用覆盖索引来优化:

-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_status_name (status, name);

-- 查询只使用索引字段
SELECT id, name FROM users WHERE status != 'deleted';

# limit深分页问题

limit深分页指的是在分页查询中，当偏移量（offset）非常大时，MySQL 需要扫描大量数据才能返回结果，导致性能急剧下降的问题。

基本分页语法:

-- 普通分页
SELECT * FROM table_name LIMIT 0, 20;      -- 第1页，快速(0.005s)
SELECT * FROM table_name LIMIT 1000, 20;   -- 第51页，较慢(0.045s)
SELECT * FROM table_name LIMIT 100000, 20; -- 第5001页，极慢！(0.450s)
SELECT * FROM table_name LIMIT 1000000, 20; -- 第50001页，灾难级！(1.2s)

基于主键使用游标分页来优化:

# 游标的性能10万偏移量0.001s(无论多少数据量都是这个性能)
-- 第一页
SELECT * FROM test_paging ORDER BY id LIMIT 20;

-- 第二页：记录上一页最后一条的id
-- 假设第一页最后一条 id = 20
SELECT * FROM test_paging WHERE id > 20 ORDER BY id LIMIT 20;

-- 第三页：记录第二页最后一条 id = 40  
SELECT * FROM test_paging WHERE id > 40 ORDER BY id LIMIT 20;

基于时间使用游标分页来优化:

-- 第一页
SELECT * FROM test_paging ORDER BY created_time DESC, id DESC LIMIT 20;

-- 第二页：记录上一页最后一条的时间和id
-- 假设第一页最后一条: created_time = '2023-10-01 10:00:00', id = 100
SELECT * FROM test_paging 
WHERE (created_time < '2023-10-01 10:00:00') 
   OR (created_time = '2023-10-01 10:00:00' AND id < 100)
ORDER BY created_time DESC, id DESC 
LIMIT 20;

通过子查询进行优化:

# 子查询的性能是10万数据量0.01s
SELECT t.* 
FROM test_paging t
INNER JOIN (
    # 这里查询的是索引，索引的查询极快
    SELECT id 
    FROM test_paging 
    ORDER BY id 
    LIMIT 100000, 20
) AS tmp ON t.id = tmp.id;

业务侧优化(限制最大深度):

// Java代码示例
public PageResult queryUsers(int page, int size) {
    // 限制最大页码
    int maxPage = 1000;  // 根据业务设定
    if (page > maxPage) {
        throw new BusinessException("超出最大查询范围");
    }
    
    // 或者限制最大偏移量
    int maxOffset = 10000;
    if ((page - 1) * size > maxOffset) {
        throw new BusinessException("请使用更精确的搜索条件");
    }
    
    return userMapper.selectByPage((page - 1) * size, size);
}

# in子查询元素过多

当 IN 子句中包含大量数据时，MySQL 可能会遇到以下问题：

-- 问题示例：IN 中包含大量数据
-- 几十个，直接用in语句
-- 上万个，不能用in语句
-- 几百上千，需要时刻关注，如有问题及时调整
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 10000);
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped', ...); -- 大量枚举值

针对in子句中包含大量数据的情况，我们可以使用VALUES表来优化:

-- MySQL 8.0 支持 VALUES 表
SELECT u.* 
FROM users u 
JOIN (
    VALUES 
        ROW(1), ROW(2), ROW(3), ..., ROW(10000)
) AS t(id) ON u.id = t.id;

我们也可以在应用程序中做分批处理来优化:

// Java 示例
// 代码中分批处理增加了网络IO的时间，为什么反而比一个in语句还快呢?
// 答案是in语句中内容过多，优化器很难准确估算哪种方式最优，经常选择错误的执行计划。
public List<User> getUsersByIds(List<Long> ids) {
    List<User> result = new ArrayList<>();
    int batchSize = 1000; // 每批处理1000个ID
    
    for (int i = 0; i < ids.size(); i += batchSize) {
        int end = Math.min(i + batchSize, ids.size());
        List<Long> batchIds = ids.subList(i, end);
        
        // 执行分批查询
        List<User> batchResult = userMapper.selectByIds(batchIds);
        result.addAll(batchResult);
    }
    return result;
}

# order by关键字使用了文件排序

当MySQL无法使用索引直接得到排序结果时，它就需要对数据进行排序，这个排序过程就被称为“文件排序”。这个名字来源于早期MySQL版本，当时排序操作通常需要在磁盘上创建临时文件来完成。但是，现在的MySQL版本会尽量在内存中完成排序。排序是在内存完成还是在磁盘完成其实无关紧要，无论在哪个地方完成，排序操作的性能都会很慢。在explain中，只要在Extra列中看到Using filesort，就说明发生了文件排序。

针对该问题的优化，我们可以针对order by中的字段添加索引

SELECT * FROM `users` ORDER BY `country`, `last_login` DESC;
-- 创建一个复合索引，字段顺序必须与 ORDER BY 顺序一致
CREATE INDEX idx_country_login ON users(country, last_login DESC);

针对该问题的优化，我们可以把所有涉及的字段都添加到覆盖索引中

-- 慢查询
SELECT `id`, `name`, `price` FROM `products` WHERE `category_id` = 10 ORDER BY `price` DESC;
-- 针对所有值建索引，所有操作都走全索引完整，不用操作库表，性能也还可以
CREATE INDEX idx_category_price_covering ON products(category_id, price DESC, id, name);

# 左右连接(以左连接为例)，关联的字段编码格式不一样

在左外连接中，驱动表（左表）和被驱动表（右表）的索引使用情况如下：

驱动表（左表）：通常，左外连接会先读取左表的所有行，然后对于每一行，去右表中查找匹配的行。因此，左表通常会是全表扫描（除非有WHERE条件限制左表，那么左表可能会使用索引来限制行数）。但是，如果左表有索引，并且查询中包含了可以利用索引的条件（例如，在WHERE子句中对左表的过滤条件），那么左表可能会使用索引。
被驱动表（右表）：在左外连接中，对于左表的每一行，数据库需要去右表中查找匹配的行。此时，右表的连接字段上的索引就非常重要。如果右表的连接字段上有索引，那么数据库通常可以使用索引来快速查找匹配的行，避免全表扫描。
所以，对于左外连接，右表的连接字段上有索引是至关重要的，因为右表会被用来做查找操作。而左表通常不需要在连接字段上建立索引来加速连接本身，但是如果有WHERE条件过滤左表，那么左表在过滤条件上建立索引可能更有用。
在这个例子中提到了连接字段的编码不一致导致了索引失效，实际开发中强制要求所有库表字段均使用utf8mb4来定义编码，因此该问题可以避免(如果真的碰上了这个问题，其实很难分析的，因为完全看不出来，只能通过Optimizer Trace看有没有发生隐式转换)。

# 左右连接(以左连接为例)，如下SQL的索引要如何建立?

select * from A a left join B b on A.a=B.a and A.b=B.b where a.c='1' and b.d = '2'

执行计划分析(可能的执行路径)

路径1：先过滤A表，再连接B表
路径2：先过滤B表，再连接A表
路径3：全表扫描+哈希连接

推荐的索引方案

方案1：最优化索引（推荐）

-- A表索引
CREATE INDEX idx_A_c_a_b ON A(c, a, b);

-- B表索引  
CREATE INDEX idx_B_d_a_b ON B(d, a, b);
为什么这样设计：

A(c, a, b)：c用于WHERE过滤，a,b用于连接

B(d, a, b)：d用于WHERE过滤，a,b用于连接

都是覆盖索引，避免回表

方案2：基础索引

-- 如果不想建复合索引，至少需要这些
CREATE INDEX idx_A_c ON A(c);
CREATE INDEX idx_A_ab ON A(a, b);

CREATE INDEX idx_B_d ON B(d); 
CREATE INDEX idx_B_ab ON B(a, b);

使用EXPLAIN分析执行计划

EXPLAIN FORMAT=JSON
SELECT *
FROM A 
LEFT JOIN B ON A.a = B.a AND A.b = B.b 
WHERE A.c = '1' AND B.d = '2';

期望的执行计划如下：
json
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "A",
          "access_type": "ref",           // 使用索引查找
          "possible_keys": ["idx_A_c_a_b"],
          "key": "idx_A_c_a_b",
          "used_key_parts": ["c"]         // 先用c过滤
        }
      },
      {
        "table": {
          "table_name": "B", 
          "access_type": "ref",
          "possible_keys": ["idx_B_d_a_b"],
          "key": "idx_B_d_a_b",
          "used_key_parts": ["d", "a", "b"] // 用d过滤和ab连接
        }
      }
    ]
  }
}

不同数据分布下的索引选择

情况1：A.c='1' 过滤性很好

-- A表数据量很大，但c='1'的记录很少
-- 推荐：idx_A_c_a_b
-- 执行路径：A表通过c快速过滤 → 连接B表

情况2：B.d='2' 过滤性很好

-- B表数据量很大，但d='2'的记录很少
-- 推荐：idx_B_d_a_b
-- 执行路径：B表通过d快速过滤 → 连接A表

情况3：两边过滤性都一般

-- 需要复合索引来覆盖所有条件
-- idx_A_c_a_b 和 idx_B_d_a_b 都是必须的

总结

2个复合索引是最佳方案：

A(c, a, b)
B(d, a, b)

索引设计原则

WHERE条件字段在前：c, d
连接条件字段在后：a, b
覆盖查询字段：如果SELECT字段不多，可以包含在索引中
考虑数据分布：高选择性的字段放前面

验证步骤

创建推荐索引
使用EXPLAIN验证执行计划
检查是否出现"Using index"
确认扫描行数是否合理

# group by使用临时表

group by语句可能会因为没有索引而使用临时表进行存储中间数据，可以在解释计划中看到Extra字段中有Using temporary或者Using filesort就说明使用了临时表。针对group by创建临时表问题，我们有如下解决方案:

覆盖索引（最优解）

-- 如果查询只涉及分组字段和聚合函数
CREATE INDEX idx_category_amount ON sales(category, amount);

-- 查询可以完全使用索引
SELECT category, SUM(amount)
FROM sales 
GROUP BY category;  -- 使用覆盖索引，避免回表

为GROUP BY字段创建索引

-- 单字段GROUP BY
CREATE INDEX idx_category ON sales(category);

-- 多字段GROUP BY  
CREATE INDEX idx_category_status ON sales(category, status);

架构层面优化(推荐方案)
通过定时任务等操作将汇总结果存入汇总表中，查询汇总表获取结果。

# 真实项目中慢sql优化

# in子查询元素过多

下面一条sql是我的项目中一条真实的慢查询sql，该sql我省略了一些信息,慢查询日志显示该sql的扫描行数是48535行。

select count(distinct lore0_.f_id) as col_0_0_ from k7_lore_lore lore0_ left outer join k7_lore_lore_category lorecatego1_ on (lore0_.f_id=lorecatego1_.f_lore_id and lorecatego1_.f_flag=0) where lore0_.f_flag=0 and (lore0_.f_id in (
  # 这里省略了约3万个uuid
'5d1f032f-b242-4bb5-a890-b9a1bf1d6f46','5d1f032f-b242-4bb5-a890-b9a1bf1d6f46' 
 )) and lore0_.f_notify=1 and ((lore0_.f_expired_start is null) and (lore0_.f_expired_end is null) or lore0_.f_expired_start<1762740047475 and (lore0_.f_expired_end>1762740047475 or lore0_.f_expired_end is null)) and (lore0_.f_access in (5 , 6) or lore0_.f_update_policy=0 and lore0_.f_access<>7 and (lore0_.f_version is not null) and lore0_.f_version<>'') and lore0_.f_access<>8;

这条sql太长了，甚至在dbeaver中执行都会卡死，通过notepad++可以看到，该sql的in子查询中约32768个元素，且这些元素中还存在重复元素。
通过查看执行计划可以看到两个表lore0_lorecatego1_都没走索引，而是走的全表扫描
分析sql可以很明显的看到lore0_.f_id=lorecatego1_.f_lore_id，这里是两个表的关联条件，可以重点关注下lorecatego1_表中f_lore_id是否增加了索引，发现没有索引。
补充索引后再执行解释计划看看。这时发现两个表都走了索引了，lore表走的是全表扫描,category表则走的是索引
针对lore表没有办法优化，因为in中包含太多f_id了，mysql判断走全表扫描要比走索引性能更好，因此这里只能针对in语句做拆分。保留前面200条in语句后执行解释计划，发现针对lore表不再走全表扫描，而是走范围检索了,单次查询仅需12s就可以获取到结果，考虑到lore_id本身就有重复，去重加分批处理后即使增加了网络带块也影响不大，整体性能提升非常多。

# 表连接条件增加索引

下面一条sql是我的项目中一条真实的慢查询sql,慢查询日志显示该sql的扫描行数是4422942行。

select count(user0_.f_id) as col_0_0_ from k7_user_user user0_ inner join k7_user_user user1_ on (user0_.f_agent_no=user1_.f_agent_no and user1_.f_flag=0 and user1_.f_status=0 and (user1_.f_department_id in (select department2_.f_id from k7_user_department department2_ where department2_.f_code like 'BKIS1')) and (user1_.f_user_type in ('1'))) where user0_.f_flag=0 and user0_.f_status=0 and (user0_.f_department_code like '0A1A11A5A%') and user0_.f_id<>'admin' and (user0_.f_user_type in ('3' , '4'));

SQL优化前，执行需要20s以上,执行计划中user0表的索引是range索引范围扫描，考虑针对user0做优化
针对user0和user1的连接条件f_agent_no添加索引后执行，发现sql在50ms执行完毕，优化完成
优化后的执行计划如下

# 针对update语句中的关键字加索引

下面一条sql是我的项目中一条真实的慢查询sql，慢查询日志显示该sql的扫描行数是10633015行。

update
	k7_user_user u,
	k7_user_department d
set
	u.f_department_code = d.f_hierarchy_code,
	u.f_department_id = d.f_id
where
	u.f_flag = 0
	and d.f_flag = 0
	and d.f_external_department_id = u.f_department_id;

这条sql优化前需要十几秒才能执行完成,查看执行计划发现两个表u和d都走了全表扫描
针对sql中出现的关键字段d.f_external_department_id,u.f_department_id，u.f_department_code，d.f_hierarchy_code增加索引，优先针对d.f_external_department_id,u.f_department_id增加索引后观察,增加完成后发现sql执行明显加快，30ms左右即可执行完毕。此时查看解释计划虽然d表还是走了全表扫描(尝试找AI提供支持加了很多索引type最终索引成了index全覆盖索引，提升不明显)，但是扫描数明显减少。

# 6亿扫描行数的慢sql优化

下面一条sql，慢查询sql日志显示执行时间292s，扫描行数618659975。

select a.f_keyword as keyword, count(1) as searchNum,( select count(1) from k7_lore_read_record b where a.f_keyword=b.f_keyword and b.f_source=1  and b.f_department_code like '0A1A%' and b.f_create_time >= 1755360000000 and b.f_create_time <= 1758124799999) as clickNum from k7_search_search_history a  where a.f_flag=0  and a.f_department_code like '0A1A%' and a.f_create_time >= 1755360000000 and a.f_create_time <= 1758124799999 group by a.f_keyword order by searchNum desc,clickNum desc,a.f_create_time desc limit 20;

这条sql看起来复杂，执行起来也确实要200+s,执行结果来看就是一个汇总表,这个表说白了就是针对history表的keyword做分组，找出前面20个，在这前面20个的基础上再针对keyword做分组，查b表查出clickNum再做倒排。只要拆分成两个sql,使用汇总表或临时表存储数据,然后查数就可以在毫秒时间查出结果了。

# 先把这条sql的查询结果存入临时表，再针对里面的20个keyword查k7_lore_read_record表的clickNum存储，然后按需查临时表即可
select
	a.f_keyword as keyword,
	count(1) as searchNum
from
	k7_search_search_history a
where
	a.f_flag = 0
	and a.f_department_code like '0A1A%'
	and a.f_create_time >= 1755360000000
	and a.f_create_time <= 1758124799999
group by
	a.f_keyword
order by
	searchNum desc
limit 20;

# 联合查询出现全表扫描

下面一条sql是我的项目中一条真实的慢查询sql，慢查询日志显示该sql的执行时间为12s,扫描行数是8738800行。

select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '4a0bcff9-acfd-4136-86cf-9f9948f4044a'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '8e24faba-feae-401c-8a8c-5b19da131fa4'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A0A11A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A0A6A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A0A7A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A9A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A21A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A20A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A16A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A15A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A18A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A1A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A17A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A8A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A19A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A22A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A0A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A23A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A5A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A6A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A4A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A12A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A11A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A14A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A11A9A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A11A2A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A11A6A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A11A7A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A11A5A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A5A3A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '82d8d70f-c790-406e-9028-0d97387c0b96'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '50ce4f92-6578-4677-8b87-f252cc9ad38a'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = 'f7d89813-f260-4cf6-a5af-66b932b5530b'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '79c284fb-78b9-4ab0-923a-6df616ce997e'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = 'e8c9ab63-2b40-4225-9dda-58c5a80fe603'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A25A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = 'cf4b2bdd-e0c1-4fb9-b64f-f8794fda30e7'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '056b115e-daca-427a-b227-22939cfc6b0b'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A1A2A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A5A9A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r     WHERE  r.f_flag=0 and r.f_status = 0 and r.f_department_code like '0A1A5A11A%'  UNION ALL select r.f_id, r.f_login_name, r.f_name, r.f_department_id, r.f_eng_name, r.f_source, r.f_agent_no, r.f_node_no, r.f_position, r.f_act_rank, r.f_branch_code, r.f_user_type, r.f_branch_manager   from k7_user_user r   left join k7_user_user_role b on r.f_id=b.f_user_id   WHERE  r.f_flag=0 and r.f_status = 0 and b.f_role_id = '1f87cfba-61aa-415a-b966-44d86437c967';

执行这条sql的执行计划，发现有两个地方走了全表扫描，全表扫描的是UNION操作
实在看不出来为什么会这样，重新阅读sql，发现sql其实是可以改写的，所以改成如下样式:

explain 
select
	r.f_id,
	r.f_login_name,
	r.f_name,
	r.f_department_id,
	r.f_eng_name,
	r.f_source,
	r.f_agent_no,
	r.f_node_no,
	r.f_position,
	r.f_act_rank,
	r.f_branch_code,
	r.f_user_type,
	r.f_branch_manager
from
	k7_user_user r
left join k7_user_user_role b on
	r.f_id = b.f_user_id
where
	r.f_flag = 0
	and r.f_status = 0
	and b.f_role_id in (
	'82d8d70f-c790-406e-9028-0d97387c0b96',
	'4a0bcff9-acfd-4136-86cf-9f9948f4044a',
	'8e24faba-feae-401c-8a8c-5b19da131fa4',
	'50ce4f92-6578-4677-8b87-f252cc9ad38a',
	'f7d89813-f260-4cf6-a5af-66b932b5530b',
	'79c284fb-78b9-4ab0-923a-6df616ce997e',
	'e8c9ab63-2b40-4225-9dda-58c5a80fe603',
	'cf4b2bdd-e0c1-4fb9-b64f-f8794fda30e7',
	'056b115e-daca-427a-b227-22939cfc6b0b',
	'1f87cfba-61aa-415a-b966-44d86437c967'
	)
union all
select
	r.f_id,
	r.f_login_name,
	r.f_name,
	r.f_department_id,
	r.f_eng_name,
	r.f_source,
	r.f_agent_no,
	r.f_node_no,
	r.f_position,
	r.f_act_rank,
	r.f_branch_code,
	r.f_user_type,
	r.f_branch_manager
from
	k7_user_user r
where
	r.f_flag = 0
	and r.f_status = 0
	and (r.f_department_code like '0A1A0A11A%' or r.f_department_code like '0A1A0A6A%'
	or r.f_department_code like '0A1A0A7A%' 
	or r.f_department_code like '0A1A1A9A%'
	or r.f_department_code like '0A1A1A21A%'
	or r.f_department_code like '0A1A1A20A%'
	or r.f_department_code like '0A1A1A16A%'
	or r.f_department_code like '0A1A1A15A%'
	or r.f_department_code like '0A1A1A18A%'
	or r.f_department_code like '0A1A1A1A%'
	or r.f_department_code like '0A1A1A17A%'
	or r.f_department_code like '0A1A1A8A%'
	or r.f_department_code like '0A1A1A19A%'
	or r.f_department_code like '0A1A1A22A%'
	or r.f_department_code like '0A1A1A0A%'
	or r.f_department_code like '0A1A1A23A%'
	or r.f_department_code like '0A1A1A5A%'
	or r.f_department_code like '0A1A1A6A%'
	or r.f_department_code like '0A1A1A4A%'
	or r.f_department_code like '0A1A1A12A%'
	or r.f_department_code like '0A1A1A11A%'
	or r.f_department_code like '0A1A1A14A%'
	or r.f_department_code like '0A1A11A9A%'
	or r.f_department_code like '0A1A11A2A%'
	or r.f_department_code like '0A1A11A6A%'
	or r.f_department_code like '0A1A11A7A%'
	or r.f_department_code like '0A1A11A5A%'
	or r.f_department_code like '0A1A5A3A%'
	or r.f_department_code like '0A1A1A25A%'
	or r.f_department_code like '0A1A1A2A%'
	or r.f_department_code like '0A1A5A9A%'
	or r.f_department_code like '0A1A5A11A%'
	)

执行改写后的sql的执行计划，依然存在全表扫描
到这里就不太好优化了，AI反馈说是联合的下面的sql或操作太多了，所以走了全表扫描，这里就只剩下唯一一个方案了，那就是把联合操作去掉，代码里拆分成两个sql执行，同时通过userId去重(这里union all是有bug的，会导致数据重复)。

补充说明

AI提供了下面4个联合索引，添加后确实UNION ALL不再走全表了，但是考虑到这几个索引有点怪，且本身UNION ALL有数据重复问题，需要代码层面修复，因此选择了拆分UNION ALL操作 -- 为k7_user_user表创建复合索引 CREATE INDEX idx_user_status_flag ON k7_user_user(f_flag, f_status, f_department_code); CREATE INDEX idx_user_basic ON k7_user_user(f_flag, f_status, f_id);

-- 为k7_user_user_role表创建索引 CREATE INDEX idx_user_role ON k7_user_user_role(f_user_id, f_role_id); CREATE INDEX idx_role_user ON k7_user_user_role(f_role_id, f_user_id);

# 使用汇总表优化

下面一个sql执行时间21s,扫描行数16232763行。

select aa.f_name as '产品名称',
                (
                 select e.f_value 
                from k7_product_product a
                left join k7_product_product_structure b on a.f_id = b.f_product_id 
                 left join k7_product_template_structure c on b.f_template_structure_id = c.f_id 
                 left join k7_product_elements d on c.f_elements_id = d.f_id 
                left join k7_product_product_value e on (e.f_elements_id = d.f_id and e.f_product_id = a.f_id and e.f_product_structure_id = b.f_id)
                left join k7_user_department f on b.f_channel_id = f.f_id 
                where 
                a.f_id = aa.f_id
                and
                (d.f_name = '險種代碼' )
                and a.f_flag = 0 and b.f_flag =0 and c.f_flag =0 and d.f_flag =0 and e.f_flag =0 
   
                ) as '險種代碼',
                (
                         select e.f_value 
                from k7_product_product a
                left join k7_product_product_structure b on a.f_id = b.f_product_id 
                 left join k7_product_template_structure c on b.f_template_structure_id = c.f_id 
                 left join k7_product_elements d on c.f_elements_id = d.f_id 
                left join k7_product_product_value e on (e.f_elements_id = d.f_id and e.f_product_id = a.f_id and e.f_product_structure_id = b.f_id)
                left join k7_user_department f on b.f_channel_id = f.f_id 
                where 
                a.f_id = aa.f_id
                and
                ( d.f_name ='預繳保費')
                and a.f_flag = 0 and b.f_flag =0 and c.f_flag =0 and d.f_flag =0 and e.f_flag =0 
                ) as '預繳保費'
                
                from k7_product_product aa
                where aa.f_flag = 0 order by aa.f_update_time desc 
LIMIT 0, 400;

资讯了AI也没什么很好的优化方案，而且看起来这个就是一个汇总表的场景，使用汇总表应该可以解决问题。就是使用汇总表而不是实时查询的话数据无法保证强一致性，且需要定时任务触发汇总。

# 慢SQL优化的个人思路

识别SQL中where,on,in,group by,order by,select关键字后面的数据库列字段。
针对步骤1中识别到的重复度低的字段建单个索引以及复合索引，复合索引遵循左查询原则，因此select后的字段一般放在复合索引的后面。
select语句后只返回必要的字段，尽可能不使用通配符查找。
理想的优化是SQL走索引执行，最差的优化方案也要确保SQL可以走全索引覆盖(避免回表查询),不允许出现全表查询，如实在无法优化还是出现全表扫描，优先考虑拆解这类复杂SQL,通过代码实现SQL逻辑。

# 集群高可用

# 主从复制+手动切换方案项目再用

目前我所在的项目中,Mysql使用的是一主两从的主从架构，主库（Master）处理写操作和部分读操作，从库（Slave）通过复制主库的 binlog 来同步数据，主要承担读操作。业务系统操作主节点完成读写，从节点则用于Mysql的数据库备份。该方案中如果主节点宕机，业务系统会出现持续性的报错，该报错经由监控告警平台(蓝鲸agent)同步给到业务系统负责人及DBA，需要DBA手动恢复数据库。

# MySQL Group Replication (MGR)

MGR一般由一主两从节点组成，与主从复制的最大区别是MGR式由Paxos协议实现分布式一致性。当主节点发生故障时，Mysql会自动触发故障转移。

# 分库分表

# 纵向分库

纵向分库指的是将数据库中不同的数据表分别存储到不同的数据库中，如user相关的表存储到user库，order相关的表存储到order库。纵向分库也是微服务中最重要的一个实现关键点(也就是说服务和服务之间不能共库，实际实施中我们会采用逻辑分库，也就是使用同一个数据库，通过表名前缀代表逻辑上的分库，不同服务使用不同的库表)

# 纵向分表

纵向分表也就是把一个表拆分成主从表，把一些不常用的数据拆分到从表中

# 水平分库

针对微服务中，如order库的数据过多的情况，我们可以使用水平分库，也就是两个相同的order库，只不过每个order库存储的订单不同，如根据生成的雪花算法的订单id的奇偶性判断是存到order1库还是order2库。

# 水平分表

水平分表指的是两个表，表结构一样，表名不同，如我参与的一个邮件发送平台的项目，短信发送记录查看功能使用的就是水平分表，前端界面做了功能限制，不能跨月查询，后端数据库中有很多水平表，如sms_send_202511，这个表存储了11月邮件发送的记录。

# 其他

# Mybatis中#{}和${}的区别?

#{}和${}都能实现变量替换的功能，且#{}可以有效防止SQL注入，提高系统安全性，因此实际开发中，不推荐使用${}。