1. 索引为什么能加快查询没有索引查WHERE UserId 10001只能一行行扫全表叫全表扫描。表越大越慢。数据库常用B Tree做索引。可以把它想成一本已经排好序、带目录的厚书不是链表那样从头翻到尾。B Tree 长什么样树分三层理解就够了实际可能更深原理一样[根节点几个键用来指路] / | \ [中间节点] [中间节点] [中间节点] ← 还是指路不存完整数据 / \ / \ [叶子]—[叶子]—[叶子]—[叶子]—... ← 真正的数据或指向数据的键在这里几个关键特点1. 数据都在叶子层中间节点和根节点只存键用来判断往左走还是往右走像目录页。真正要找的记录在叶子节点。这里的键就是索引列的值用来排序和指路不是主键专属名词。索引建在UserId上键就是1001、5000、10001这类 UserId 的值联合索引(TenantId, Status)键是(10, 1)、(10, 2)、(20, 1)这样的组合先比 TenantId 再比 Status根节点、中间节点里存若干键 指向子节点的指针例如小于 5000 走左边500010000 走中间大于 10000 走右边叶子节点存键 实际内容主键索引的叶子存完整行二级索引的叶子存索引列 主键值第 2 节主键值用来回聚集索引取完整行所以根和中间节点不存 Name、Age 那些列只存索引列的值做分界要到叶子才能拿到完整数据或主键。2. 叶子是一条有序链表叶子节点之间用指针连起来键从小到大排好。所以不仅能快速找UserId 10001找UserId 10001或ORDER BY UserId也很顺顺着链表往后扫就行。3. 每个节点对应磁盘上的一页数据库按页比如 16KB读写磁盘。B Tree 一个节点尽量塞满一页一层层往下通常 34 层就能管几百万行——每次查询只读少数几页比扫整张表少几个数量级。查一次怎么走索引在UserId上查WHERE UserId 10001从根节点看10001 比这里的键大还是小决定进左边还是右边分支到中间节点继续比大小、选分支到叶子节点找到 UserId10001 的那条或对应的主键若还要其他列拿主键回聚集索引取完整行回表第 2 节整个过程像猜数字游戏里的二分但不是对半切而是每个节点有很多分支多路更适合磁盘一次读一页。为什么不用别的结构Hash按 key 算位置等值查快但不支持范围查询和排序、、ORDER BY用不上。二叉树每个节点只有两个分支层数高磁盘要读很多次不适合大数据量。B Tree 在等值、范围、排序之间比较均衡又贴合磁盘按页读写的特点所以 MySQL、SQL Server 等主流数据库索引都用它。代价是 INSERT/UPDATE/DELETE 时要维护这棵树可能分裂、合并节点占磁盘和 Buffer Pool。索引换读性能不是免费的。2. 聚集索引和非聚集索引一张表的数据在磁盘上存一份但可以用多种方式快速找到行。InnoDB 里分两种索引聚集索引主键索引和非聚集索引二级索引。聚集索引数据本身按什么顺序存聚集索引的叶子节点里放的是完整的数据行不是只放一个指针。可以想成一本按学号排好序的花名册每一页直接印着学号、姓名、班级、电话——找学号 10001翻到对应页信息全在上面不用再查别的书。InnoDB 里主键就是聚集索引。表里的行在叶子层按主键值排好序存放逻辑有序不等于磁盘上物理紧挨着。没定义主键数据库会选一个唯一非空列或生成隐藏 row id。一张表只能有一个聚集索引——数据只能按一种顺序组织。选主键就是在选整张表默认按什么键排序存放。非聚集索引另建一本目录指向主键在Email上建索引是另外长一棵B Tree。这棵树叶子存的是Email 值 主键 Id不存姓名、年龄等其他列。很多资料说叶子存的是指向主键的指针和上面并不矛盾——这里的指针一般不是内存地址而是主键这个值本身。InnoDB 拿主键 Id 再去聚集索引树里查就能定位到完整行所以主键值相当于指向聚集索引里那一行的逻辑指针。二级索引叶子(Emailzhangxx.com, Id42) ↓ 拿 Id42 当钥匙去主键树里开对应那一行SQL Server 若表没有聚集索引Heap 堆表二级索引叶子存的才是 RID页号槽位更接近物理地址意义上的指针。InnoDB 有聚集索引习惯记叶子 索引列 主键值即可。像图书馆书名目录卡片上写《数据库原理》→ 架号 A-3-12卡片没有书的内容要按架号去书架取书。所以 Email 索引叫二级索引或非聚集索引。可以有多个Email 一棵、Phone 一棵、CreatedAt 一棵互不冲突。回表二级索引查完还要再查一次主键SELECTName,AgeFROMUsersWHEREEmailzhangxx.com;Email 索引的叶子里只有(Email, Id)没有 Name、Age。流程是1. 在 Email 索引树里找到 zhangxx.com → 得到 Id 42 2. 拿 Id42 去主键聚集索引树里找 → 取出完整一行Name、Age 等第 2 步就是回表二级索引定位到主键再回聚集索引取完整数据。查一次实际走了两棵树所以二级索引能覆盖查询列时更省见第 4 节覆盖索引。若 SQL 是SELECT Id FROM Users WHERE Email zhangxx.comId 已经在 Email 索引叶子上了不用回表。主键为什么要短而稳定每个二级索引的叶子都带主键值像每张目录卡片都写着架号。主键从INT改成UUID字符串每一棵二级索引的叶子都变胖占更多磁盘和内存写入也更慢。主键尽量短、入库后不改是 InnoDB 的常见建议。3. 联合索引与最左匹配联合索引(TenantId, Status, CreatedAt)把三列绑在一起排序不是三列各排各的。叶子里的顺序类似(1, 1, 2024-01-01) (1, 1, 2024-01-02) (1, 2, 2024-01-01) (2, 1, 2024-01-01) ...先比 TenantId相同再比 Status再比 CreatedAt。B Tree 只能利用从左到右的连续前缀快速定位。能正常用上索引的写法索引(TenantId, Status, CreatedAt)SQL 条件用到的索引列说明WHERE TenantId 1TenantId最左列有等值能定位WHERE TenantId 1 AND Status 2TenantId, Status连续前两列能定位WHERE TenantId 1 AND Status 2 AND CreatedAt 2024-01-01三列等值 范围CreatedAt 在最后理想写法WHERE TenantId 1 AND CreatedAt 2024-01-01TenantId中间缺 StatusCreatedAt 通常用不上索引定位只能在 TenantId1 的结果里再筛 CreatedAt最后一种能用到 TenantId但不如三列顺序齐全的 SQL 高效。Extra 里可能出现Using index condition索引下推不等于 CreatedAt 参与了 B Tree 的连续区间定位。用不上或几乎用不上索引的写法SQL 条件原因WHERE Status 2缺最左列 TenantId树不是按 Status 全局排序的WHERE CreatedAt 2024-01-01缺 TenantId同上WHERE Status 2 AND CreatedAt ...没有 TenantId最左匹配不满足WHERE TenantId 1 AND CreatedAt ... AND Status 2有 TenantId但 Status 夹在中间没出现在条件里CreatedAt 又是范围——若索引顺序是(TenantId, Status, CreatedAt)通常只能用到 TenantId 定位Status 和 CreatedAt 难以一起缩小范围。若实际查询常这样写应把索引改成(TenantId, CreatedAt, Status)或保证 WHERE 列顺序和索引一致。范围条件截断后面的列索引(TenantId, Status, CreatedAt)条件WHERETenantId1ANDStatus0ANDCreatedAt2024-06-01TenantId 等值、Status 范围——Status 一旦出现范围后面的 CreatedAt往往不能再用来缩 B Tree 区间CreatedAt 只能在扫到的索引条目上额外过滤。所以建索引时等值列放前范围/排序列放后。其他常见“失效”写法不单是最左匹配WHERE YEAR(CreatedAt) 2024对索引列包函数用不上 CreatedAt 索引WHERE Email LIKE %qq.com前缀通配Email 索引失效LIKE zhang%可以隐式类型转换字符串列传数字可能全表扫优化器判断扫全表更便宜选择性太低、表很小也会不走索引——不是语法错了是优化器的选择是否走索引以EXPLAIN为准别只背规则。建联合索引怎么排顺序按实际 SQL来经常一起查、用等值过滤的放前面范围查询、ORDER BY的放后面兼顾多个查询模式有时一个索引服务多条 SQL。TenantId 在多租户系统里选择性可能不高但几乎每条 SQL 都带也常放第一位。4. 覆盖索引查询需要的列全在索引里不用回表叫覆盖索引。例如索引(TenantId, Status, CreatedAt)只查这三列Extra 可能出现Using index。覆盖索引省的是回表不是扫行数——若仍要扫百万行索引页照样慢。5. 建索引最佳实践适合建WHERE、JOIN、ORDER BY 里高频出现的列选择性高的列distinct 值多比如用户 ID、订单号联合索引覆盖常见查询组合而不是每个单列各建一个少建或不建低选择性列单独建索引如性别、是否删除优化器常直接全表扫写多读少的表盲目加索引INSERT/UPDATE 要维护每棵索引树索引列上包函数WHERE YEAR(CreatedAt)2024用不上 CreatedAt 索引改成范围CreatedAt 2024-01-01隐式类型转换字符串列传数字可能走不了索引LIKE %abc前缀通配索引用不上LIKE abc%可以其他一张表索引不是越多越好按慢查询和写入成本权衡上线前用 EXPLAIN 看type、key、rows、ExtraMySQL 8 可用 EXPLAIN ANALYZE 看实际耗时大表加索引用 Online DDL避开业务高峰6. 事务与 ACID事务把多条 SQL 包成一个单元要么全成功要么全失败。含义A 原子性全成功或全回滚转账不能只扣不加C 一致性事务前后满足约束主键、外键、唯一键余额不为负这类业务规则还要代码保证I 隔离性并发时一个事务什么时候能看到别的事务改过的数据由隔离级别决定第 7 节D 持久性提交后宕机可恢复靠 redo log 等 WAL 机制事务边界按业务原子性划不是每条 SQL 一个事务也不是整个 HTTP 请求无脑包一个大事务。7. 隔离级别隔离级别只回答一个问题事务 A 什么时候能看到事务 B 修改的数据事务 ASELECT balance → 100 事务 BUPDATE balance200COMMIT 事务 A再 SELECT balance → 第二次能不能看到 200取决于隔离级别。B 能不能改、怎么改是另一个问题锁第 10 节。MySQL InnoDB 默认可重复读Repeatable Read。三种读异常脏读— 读到 B 还没提交的数据B 回滚后读到的是假的BUPDATE 100→200未 COMMIT ASELECT → 200 BROLLBACK不可重复读— 同一行读两次中间 B 改并提交了两次结果不同ASELECT 订单10 → 10 BUPDATE 订单10→11COMMIT A再 SELECT 订单10 → 11幻读— 同一条件查两次中间 B 插入或删了行行数变了ASELECT WHERE type1 → 5 行 BINSERT type1COMMIT A再 SELECT WHERE type1 → 6 行四种级别隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不会可能可能可重复读不会不会可能*串行化不会不会不会*InnoDB 默认可重复读普通 SELECT 靠 MVCC 往往也看不到新插入的行锁定读靠 Next-Key Lock 防插入第 9、10 节。别简单记成 RR 间隙锁。读已提交RC— InnoDB 里每条 SELECT 生成新的 Read View只读已提交的版本。第一次读到 100B 提交变成 200A 再 SELECT 就看到 200所以可能不可重复读但不会脏读。可重复读RRInnoDB 默认— 事务里第一次普通 SELECT 时创建 Read View之后同一事务内的普通 SELECT 都用这个快照A第一次 SELECT → 创建 Read View看到 100 BUPDATE→200COMMIT A再 SELECT → 仍看到 100读旧版本第 8 节同一事务内重复读同一行结果一致。挡幻读要分读法普通 SELECT 靠 MVCCSELECT ... FOR UPDATE靠 Next-Key Lock 不让别人往范围内插。串行化— 读写都加范围锁相当于排队并发最差一般业务很少用。隔离级别管的是读到哪个版本写与写冲突丢失更新、超卖靠锁或乐观锁第 10 节。8. MVCCInnoDB 实现隔离级别主要靠MVCC多版本并发控制不是给每次普通 SELECT 都加共享锁。更新一行时不直接覆盖旧数据而是生成新版本旧版本留在Undo Log里不会马上删掉。每条数据可能同时存在多个版本。事务读的时候Read View决定当前事务能看见哪个版本未提交的看不到已提交的是否能看到取决于隔离级别和 Read View 何时创建。MVCC 负责读锁负责写。两者一起实现事务隔离普通 SELECT 读快照 → MVCCUPDATE / DELETE / INSERT、SELECT ... FOR UPDATE→ 锁最新版本经典面试题为什么普通 SELECT 通常不会阻塞 UPDATE因为 SELECT 默认是快照读读 Undo Log 里的历史版本UPDATE 改的是最新版本并加行锁。读旧版本、写新版本互不挡路。这是 MVCC 最大的价值。9. 快照读与当前读InnoDB 里两种读法普通 SELECT ↓ 快照读一致性读 ↓ MVCC读 Read View 可见的版本不加行锁 SELECT ... FOR UPDATE / LOCK IN SHARE MODE ↓ 当前读 ↓ 读最新已提交版本 加锁UPDATE、DELETE 内部也是当前读最新版本再改。口诀普通 SELECT → 快照读不加锁靠 MVCCUPDATE / DELETE / INSERT → 加锁SELECT … FOR UPDATE → 当前读 加锁10. 锁MVCC 解决了读哪个版本但没解决别人还能不能改、能不能往范围里插行写与写、锁定读与写之间靠锁。为什么有 Gap Lock库存表 id 为 10、20、30。事务 ASELECT*FROMstockWHEREidBETWEEN10AND20FORUPDATE;若只锁 id10 和 id20 两行事务 B 仍可以INSERT id15。A 再查 BETWEEN 10 AND 20多了 15这就是幻读。所以要锁 10 和 20 之间的空隙不让 B 插入 15——这就是Gap Lock间隙锁。三种锁按业务理解业务含义名字锁一行Record Lock行锁锁两个键之间的空隙防插入Gap Lock锁这一行 锁左侧空隙Next-Key LockInnoDB 可重复读下范围锁定读常用锁等待会导致慢查询。死锁是两个事务互相等对方持有的锁InnoDB 会回滚代价较小的一个应用捕获后重试即可。减少锁问题事务尽量短、按相同顺序访问行、避免大事务。丢失更新与超卖隔离级别和 MVCC 管不好写与写的冲突。两人同时读-改-写会丢失更新库存并发会超卖。常用乐观锁UPDATE ... SET versionversion1 WHERE id1 AND version1悲观锁SELECT ... FOR UPDATE再改原子扣减UPDATE stock SET nn-1 WHERE n111. 慢 SQL 怎么排查别一上来加索引。顺序大致是确认慢在 DB 还是应用网络、ORM N1、循环查库加索引没用看执行计划走没走索引、是否回表、是否 filesort、是否临时表对比估算行数和实际行数差很多可能是统计信息过期先ANALYZE TABLE看是否在等锁show processlist或慢查询日志里的 Lock 时间再决定改 SQL、加/改索引、拆查询、归档历史数据一条典型慢因SELECT *导致无法覆盖索引深分页LIMIT 100000, 20要扫过前 10 万行改 Keyset 分页WHERE Id LastId ORDER BY Id LIMIT 20。12. 常见 SQL 写法注意点只查需要的列列表页别SELECT *深分页用游标/Keyset别大 OFFSET子查询表达不存在关系优先NOT EXISTS。NOT IN若子查询结果里有 NULL可能整句查不出任何行——SQL 比较遇 NULL 不是 true/false而是 UNKNOWN三值逻辑NOT IN里混进 NULL 会把结果搞成 UNKNOWNWHERE 当 false 过滤掉。例子查询返回(1, 2, NULL)WHERE Id NOT IN (...)对所有 Id 都可能不匹配。NOT EXISTS不受这个坑影响COUNT(*)统计行数COUNT(column)不统计 NULL 行JOIN 按语义选别为了所谓性能硬换写法最终以执行计划为准