问题定义 SQL 语句 索引 TBL_XXX 表上有 idx_a、idx_b 两个非唯一索引。 问题 由于 A、B 非联合索引，因此只会用到 idx_a；当 A 列筛选出的行数很大时，结果集的每一行都要做回表，去判断是否满足 B 列条件，从而造成查询超时。 解决方案 改进后 SQL 语句 原理剖析 通过 EXPLAIN 指令可以发现，三条 SELECT 语句的 Extra 列有的是 Using where、有的是 Using intersect，这是因为 MySQL 根据 B 条件不同的取值计算出了不同的扫描行数，选择了该条件下它所认为的最优方式。 Using intersect 能够对多个扫描结果取交集，这里相当于是对 B 列每个条件得到的结果集 与 A 列分别取了交集做了多次查询，再将最后得到的结果集取并集，从而无需再回表；即使 SQL 语句后面还有 C = "xyz" 的非索引列条件，也仅需在交集上做回表，大幅降低了回表次数。 Using intersect 条件 SQL 语句为多个索引的 AND 条件时，满足： 如果是二级索引，必须是等值查询，且覆盖其中所有索引列 如果是主键索引，可以是范围查询 参考 索引合并，能不用就不要用吧！
MySQL 标签下的文章
共 2 篇
目录 概述 日志 事务 类型 索引 优化 主从架构 概述 基础架构 MySQL 主要分为 Server 层和存储引擎层： Server 层：所有跨存储引擎的功能都在这一层实现，如存储过程、触发器、视图，函数等，还有一个通用的 binlog 日志模块。 查询缓存在 MySQL 8.0 后被移除，因为缓存失效在实际业务场景中可能会非常频繁，如果对一个表更新，这个表上的所有的查询缓存都会被清空。 存储引擎：主要负责数据的存储和读取，采用可替换的插件式架构，支持 InnoDB、MyISAM 等多个存储引擎，其中 InnoDB 自带 redo log 日志模块，采用聚簇索引，支持事务、行锁、外键等（以下所有内容都是基于 InnoDB 的）。 查询语句 先检查该语句是否有权限，如果没有权限直接返回错误信息；如果有，以这条 SQL 语句为 key 在内存中查询是否有缓存（MySQL 8.0 以前），无缓存则执行下一步。 通过分析器进行词法分析，提取 SQL 语句的关键元素，如这个语句是 select，查询的表名、列，以及查询条件等。然后判断是否有语法错误。 接下来优化器会根据自己的优化算法选择其所认为执行效率最高的方案（有时不一定最好），例如上面的 SQL 可以有两种执行方案： a. 先查询表中姓名为“张三”的学生，再判断年龄是否是 18 岁。 b. 先找出学生中年龄为 18 岁的，再查询姓名为“张三”的学生。 确认了执行计划后，进行权限校验，如果没有权限就会返回错误信息，否则调用数据库引擎接口，返回引擎的执行结果。 Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端是直接显示所有记录的，是因为客户端是等查询语句完成后才会显示。 更新语句（两阶段提交） 在 InnoDB 引擎下，这个语句的执行流程如下： 先通过 where 条件查询到张三这条数据，如果该行数据不在 InnoDB Buffer Pool（内存）中，则从磁盘加载对应的数据页。 InnoDB 修改内存中的该行数据，同时生成 undo log（更新前的值，用于回滚）。 WAL（Write-Ahead Logging）机制：InnoDB 写入 redo log（保证宕机后可恢复数据），进入 prepare 状态，并通知执行器准备提交事务。 执行器记录 binlog（逻辑日志，用于主从复制、数据恢复），记录该语句。 执行器调用 InnoDB 引擎提交 redo log，将其改为 commit 状态，更新完成。 两阶段提交 上述过程中，redo log 的写入拆成了两个步骤 prepare 和 commit。 写入 binlog 时发生异常时：MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，且没有对应 binlog 日志，就会回滚该事务。 redo log 在 commit 阶段发生异常时：虽然 redo log 处于 prepare 状态，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务。 日志 binlog MySQL 中的逻辑日志，用于记录语句的原始逻辑。数据备份、主从复制需要依靠 binlog 来同步数据。它有三种格式： statement：SQL 语句原文，但 update_time=now() 会获取当前系统时间。 row：update_time=now() 变成了具体的时间。 mixed：前两者的混合，因为 row 更占用空间，恢复与同步时会更消耗 IO 资源。 写入机制 一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。 单个线程 binlog cache 的大小可以由参数 binlog_cache_size 控制，如果存储内容超过了这个参数，就要暂存到磁盘。 write 和 fsync 的时机，可以由参数 sync_binlog 控制。 在出现 IO 瓶颈的场景里，将 sync_binlog 设置成较大的值可以提升性能。 0：每次提交事务都只 write，由系统自行判断什么时候执行 fsync 。 1：每次提交事务都会执行 fsync ，防止宕机时 cache 中 binlog 丢失。 N(N>1)：每次提交事务都 write，但累积 N 个事务后才 fsync。 undo log undo log 属于逻辑日志，记录的是 SQL 语句，比如事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事