技术之路

一、索引的基本原理与作用在优化之前，必须理解索引是如何工作的。 索引是什么？ 索引（Index）是帮助 MySQL 高效获取数据的数据结构（通常是 B+Tree）。 你可以把它想象成一本书的目录。没有目录，你要找特定内容就得一页一页翻（全表扫描）。有了目录，你可以快速定位到对应的页码。 为什么索引能加快查询？ 大大减少需要扫描的数据行数。 使得数据检索从随机 I/O 变为更顺序的 I/O。 数据库引擎（如 InnoDB）通过遍历索引树来找到所需数据的指针，然后直接去磁盘定位行数据。 索引的代价（为什么不能乱建） 空间代价：索引也是一张表，需要占用磁盘空间。 时间代价：对表进行 INSERT、UPDATE、DELETE 操作时，MySQL 不仅要操作数据，还要更新对应的索引树，维护索引结构会降低写操作的速度。 二、索引优化核心策略1. 为合适的列创建索引 WHERE 子句中的列：这是最应该考虑建立索引的列。频繁作为查询条件的字段，例如 WHERE user_id = 123。 连接（JOIN）使用的列：例如 ON a.user_id = ...

什么是 EXPLAIN？EXPLAIN 是 MySQL 的一个关键字，用于获取 MySQL 如何执行一条 SELECT 语句的详细信息。它通过模拟执行（或实际执行，在 MySQL 8.0.18+ 的某些情况下）来展示查询的执行计划（Query Execution Plan, QEP），而不是真正返回查询结果。 通过分析 EXPLAIN 的输出，你可以： 查看表之间的连接顺序和类型。 判断是否使用了索引，以及使用了哪些索引。 估算需要扫描的数据行数。 发现潜在的性能瓶颈（如全表扫描、临时表、文件排序等）。 为优化查询提供明确的指导方向。 如何使用 EXPLAIN？使用方法非常简单，直接在你要分析的 SELECT 语句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON 即可。 1234567891011-- 最基本的使用方式EXPLAIN SELECT * FROM your_table WHERE id = 1;-- 查看更详细的 JSON 格式信息（推荐，信息更全）EXPLAIN FORMAT=JSON SELECT * FROM your_table WH...

核心优化思想优化慢查询不是一蹴而就的，应该遵循一个清晰的流程：“发现问题 -> 分析问题 -> 解决问题 -> 持续监控” 第一步：定位慢查询 (发现问题)在优化之前，你首先得知道哪些SQL是慢的。 开启慢查询日志 (Slow Query Log)这是最核心、最直接的工具。它会记录所有执行时间超过指定阈值的SQL语句。 123456789101112131415-- 查看慢查询相关配置SHOW VARIABLES LIKE 'slow_query%';SHOW VARIABLES LIKE 'long_query_time';-- 在MySQL配置文件（my.cnf或my.ini）中永久开启，修改后需重启[mysqld]slow_query_log = ONslow_query_log_file = /var/lib/mysql/mysql-slow.loglong_query_time = 2 -- 单位：秒，定义“慢”的阈值，通常设为1s或0.5slog_queries_not_usin...

一、核心思想：索引条件下推的核心思想是：在遍历索引时，尽早地利用索引中的列来过滤掉不满足条件的记录，从而减少需要回表查询的次数。 它的本质是将一部分过滤工作从存储引擎之上（Server层）“下推”到了存储引擎层来执行。 二、为什么需要 ICP？—— 解决性能瓶颈要理解ICP的价值，我们需要先了解没有ICP时，数据库是如何处理一个带有WHERE条件的查询的。 假设我们有一张表 user，并建立了一个联合索引 idx_age_name (age, name)。 12345678CREATE TABLE `user` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_age_name` (`age`,`name`)) ENGINE=InnoDB; 现在执行一个查询： 1234SELECT * FROM user WHERE age ...

详细过程分析让我们通过一个具体的例子来理解。假设我们有一张表 user_table： id (主键索引A) score (二级索引B) name 5 10 Bob 10 20 Alice 15 20 Tom 20 30 Jerry 现在，我们执行以下更新语句： 123-- 假设当前事务隔离级别为 RRBEGIN;UPDATE user_table SET name = ‘New’ WHERE score BETWEEN 15 AND 25; 第一步：在二级索引 B (score) 上加锁 定位区间：首先，InnoDB 通过二级索引 score 找到满足条件 score BETWEEN 15 AND 25 的所有记录。 找到 score=20 的两条记录（对应主键 id=10 和 id=15）。 找到 score=30 的记录（id=20），它不满足条件（30 > 25），但它是第一个大于 25 的值，对于确定右边界很重要。 添加 Next-Key Locks：为了防止其他事务插入 score 值在 (15, ...

UPDATE 语句的详细执行流程假设我们执行一条语句：UPDATE t SET c = c + 1 WHERE id = 10; 并且 id=10 这条记录存在。 开始事务（START TRANSACTION） 显式或隐式地开启一个事务。 查找记录（Find the Row） 服务器层命令解析后，进入存储引擎层。 InnoDB 通过 B+ 树索引定位到 id=10 这行记录。 加锁阶段（Acquire Lock - 关键步骤！） 时机：在真正修改数据之前，InnoDB 会尝试为这行记录加上排他锁（X Lock）。 过程： 如果此时没有其他事务持有这行记录的锁（包括共享锁或排他锁），则加锁成功。 如果其他事务已经持有这行记录的锁（例如共享锁），那么当前事务会进入等待状态，直到超时或对方事务释放锁。 写入 undo log（Write Undo Log） 时机：在加锁成功之后，在修改内存数据之前。 目的：为了事务回滚和实现 MVCC（多版本并发控制）。 内容：将 id=10 这行记录修改前的内容（c 的旧值）拷贝到 undo log 中。这样如果事务回滚，就...

什么是“最左前缀原则”？最左前缀原则（Leftmost Prefix Principle） 指的是：MySQL 中的联合索引（也称为复合索引，即由多个列组成的索引）会首先按照最左边的列进行排序，然后在相同左边列值的基础上，再按下一列排序，以此类推。 因此，当你的 SQL 查询条件中包含了联合索引的最左面的一个或多个列时，这个索引才可能被使用。 可以把联合索引想象成一个电话簿。电话簿首先按照姓氏排序，在姓氏相同的情况下，再按照名字排序。如果你只知道这个人的名字（而不是姓氏），你就无法利用这个排序规则快速找到他，必须从头到尾翻阅整个电话簿。 姓氏 + 名字 = 联合索引 只知道姓氏 = 使用索引的最左前缀 只知道名字 = 无法使用这个索引 联合索引的结构假设我们有一张 users 表，并创建了一个联合索引 idx_name_age (name, age)。 id name age city 1 Alice 28 Beijing 2 Bob 25 Shanghai 3 Charlie 30 Guangzhou 4 Alice ...

一、按锁的粒度（锁定范围）划分这是最核心的一种分类方式，锁的粒度决定了系统的并发性能和开销。粒度越小，并发度越高，但管理锁的开销也越大。 全局锁 作用范围：整个数据库实例。 典型命令：FLUSH TABLES WITH READ LOCK (FTWRL)。 理解：它会让整个数据库处于只读状态，所有数据变更操作（DML）和表结构变更操作（DDL）都会被阻塞。 使用场景：非常少，主要用于全库逻辑备份。但请注意，在支持事务的引擎（如InnoDB）中，使用mysqldump --single-transaction进行一致性读备份是更好、更非阻塞的选择。 表级锁 作用范围：整张表。 理解：MySQL服务器层实现的锁，与存储引擎无关。锁定整张表后，其他会话对这张表的写操作会被阻塞。 分类： 表锁：LOCK TABLES ... READ/WRITE。显式使用，现在很少用。 元数据锁：Metadata Lock (MDL)，这是最重要、最常见的表级锁。 作用：防止一个事务在读数据时，另一个会话修改了表结构（DDL操作），导致查询得到的结果不一致。 规则： 当对一个表做增删改查（DM...

意向锁是一种表级锁，它的核心作用是“声明意图”。 当一个事务想要对表中的某些行加上共享锁或排他锁之前，它会先取得对应表的意向锁。这样，其他事务就能通过查看表级别的意向锁，快速判断表中是否已经被加了行锁，从而避免为了检查冲突而逐行扫描，大大提高了效率。 意向锁有哪些？MySQL 中的意向锁主要有两种： 意向共享锁（Intention Shared Lock, IS） 作用声明：事务打算给表中的某些行设置共享锁（S）。 语法：SELECT ... FOR SHARE (在 MySQL 8.0+ 中，替代了 SELECT ... LOCK IN SHARE MODE) 意向排他锁（Intention Exclusive Lock, IX） 作用声明：事务打算给表中的某些行设置排他锁（X）。 语法：SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT（对于插入，情况稍特殊，涉及插入意向锁） 注意：意向锁是表级锁，但它们表示的是行级操作的意图。 作用是什么？意向锁的核心作用是实现多粒度锁机制的和谐共存，具体来说有两个主要目的： 提高效率...

一、快照读1. 定义快照读读取的是数据在某个时间点的快照（通常是一个历史版本），这个快照是基于undo log和ReadView机制实现的。它不会去读取其他未提交事务修改的数据，也不会因此去加锁（除非是在SERIALIZABLE隔离级别下）。 2. 实现原理：MVCC快照读是实现MySQL MVCC（多版本并发控制） 模型的关键。InnoDB会为每一行数据维护多个版本。当一个事务启动时，它会生成一个当前系统的“快照”（在代码中体现为一个ReadView结构），这个ReadView决定了该事务能看到哪些版本的数据： 已提交的事务所做的修改：可见 未提交的事务或在本事务开始之后才提交的事务所做的修改：不可见 3. 触发语句 普通的SELECT语句（不加锁的情况下）。1SELECT * FROM table WHERE ...; 4. 特点 不加锁：因此不会阻塞其他事务的写操作（UPDATE, DELETE, INSERT），并发性能高。 读到的可能是历史数据：在可重复读（REPEATABLE-READ） 隔离级别下，一个事务内多次快照读都会读取到同一个时间点的数据，从而实现...