尧图网站建设 
1. 从一次线上慢查询说起索引到底在解决什么问题这一章我们来到 MySQL 学习路线上一个真正决定“快慢”的节点——索引。前面几章都在聊建库、建表、写 SQL数据量几千条的时候怎么查都行等你真正面对线上几十万、几百万行的表一条查询从毫秒变成几秒问题一大半出在索引上。我最早接手一个订单表优化任务时SQL 看起来没有任何问题该返回的字段也不多但就是慢。后来发现 user_id 上根本没有索引表里已经攒了 300 多万行每次查询都在做全表扫描。给这一列加完索引同样的条件从 2.3 秒降到了 3 毫秒。那一刻我才真正理解“索引是什么、索引解决什么问题”。1.1 没有索引时 MySQL 在做什么在 EXPLAIN 执行计划里最扎眼的通常是 type ALL这个值的意思就是全表扫描。MySQL 把整张表从头到尾读一遍从第一行开始挨个判断 WHERE 条件符合条件就返回不符合就跳过直到最后一行结束。数据量小的时候这个成本不明显一万行、几万行的表InnoDB 一次 IO 读一个页默认 16KB整张表可能只占几十个页几十次 IO 就把全表扫完了。可当表膨胀到几百万行、占好几个 GB全表扫描要读的页数量就非常恐怖了。更要命的是这些页在磁盘上的物理位置并不连续大量随机 IO 会让机械硬盘处于来回寻道的状态SSD 虽然好一些但也经不住这种浪费。全表扫描不是设计成“坏人”的它是 MySQL 优化器在没有任何可用索引时的兜底方案。问题是它和你在实体书里找一段话完全一样必须从头翻到尾中途无法跳跃。你翻一本 500 页的小说找某个词的时候只能一页一页过但如果这本书最后有索引你直接翻到对应页码就行。索引节省的不是 10% 的力气而是几个数量级的时间差距。1.2 索引的本质是什么索引是一种额外的数据结构它把某一列或多列的值按照一定规则组织起来并在内部保存指向真实数据行的“地址”或“主键值”。MySQL 拿到查询条件后先到索引里去定位而不是直接扫全表定位到目标记录之后再回原表取完整数据。我经常把索引类比成新华字典的部首检字表正文部分按拼音排序你要查“索”字先翻拼音 suǒ 对应的页码顺着页码直接翻到那一页而不是从第一页开始逐页找。MySQL 的普通二级索引就是这个思路而主键索引更彻底——数据本身按主键顺序存放相当于整本书的正文就按页码排好了你报页码直接翻连二次定位都不需要。理解这一点之后很多直觉就能建立起来索引不是魔法它只是用空间换时间用一部分磁盘空间存储一个“目录”让 MySQL 能快速定位目标。代价是每次插入、更新、删除数据时都要同步维护索引树所以索引不是越多越好这个话题后面我会单独展开。1.3 一个最简单却能直观感受的验证实验与其听我说不如自己动手验证一把。随便找一张数据量在几十万行以上的表先看一条查询的执行计划EXPLAIN SELECT * FROM users WHERE username zhangsan;没建索引时type 一栏基本是 ALLrows 是整表行数。然后创建一个普通索引ALTER TABLE users ADD INDEX idx_username (username);再执行一次同样的 EXPLAIN你会发现 type 变成 refrows 迅速降到一个很小的数字。别小看这一步这是建立索引直觉最有效的方式。我见过很多人背了一堆索引理论遇到真实慢查询还是懵原因就是没亲手对比过优化前后的执行计划。2. 为什么选了 B树索引底层数据结构拆解很多人会背“InnoDB 用 B树”但真问一句“为什么是它”往往就答不上来了。这节把这个问题拆开从数据结构演进的角度讲清楚以后你再看到任何关于索引的讨论都不会被术语唬住。2.1 哈希索引为什么不够用最容易想到的加速结构其实是哈希表。对索引列做哈希运算得到一个定长的数字再根据数字定位到对应的桶。做等值查询时一次哈希计算加一次数组定位就能命中目标理论时间复杂度接近 O(1)速度远快于 B树。但哈希表的致命问题是无法处理范围查询。WHERE age 18、BETWEEN 一段日期、ORDER BY create_time这些操作依赖数据之间的顺序关系。哈希函数会把有序的输入值打散成看似随机的输出哈希表内部也没有“按序遍历”的能力。你只能一个一个枚举所有值再做判断等于退化回全表扫描。所以 InnoDB 的逻辑索引结构从来不是哈希表但它内部确实有一个自适应哈希索引Adaptive Hash Index这是数据库在内存里为高频热点值自动建立的加速结构不需要你手动创建也查不到逻辑上的索引定义。它只是 B树之上的锦上添花解决不了范围查询问题。讨论真正可控的索引结构还是得回到 B树。2.2 树形结构演进二叉树、红黑树、B树、B树先看二叉搜索树。假设主键字段是自增 id数据插入时按顺序增长二叉搜索树会极不均匀地长成一条链表树高等于数据行数。查最后一行主键理论上要访问 N 个节点每次节点访问都对应一次磁盘 IO这显然不能接受。红黑树是平衡二叉搜索树树高能稳定在 O(logN)。对于几百万行数据树高大概二十多层查询一条数据极限情况要访问二十多个节点。每个节点一次磁盘读二十多次 IO 对数据库来说是灾难级别的成本。虽然节点有机会被缓存在内存里但只要某一层的数据不在 buffer pool就要真真实实做一次磁盘 IO冷数据场景照样扛不住。B树的关键改进是让每个节点同时存储多个键和多个孩子指针树从“瘦高”变成“矮胖”。同样数据量B树通常只需要三四层。MySQL 在 B树基础上做了另一层改进使用 B树差别在于B树的非叶子节点只存放索引键不存放数据全部数据都存在叶子节点上叶子节点之间用双向链表串联。别小看这几个差异。非叶子节点不存数据意味着一个固定大小的页面能塞进更多索引键树高进一步下降。16KB 的页如果全放 bigint 主键和指针大约能放一千多个键三层就能覆盖上千万行。把数据集中在叶子节点所有查询都必须走到叶子层查询链路稳定没有“有时候快有时候慢”的问题。叶子节点串成链表之后范围查询和排序变得异常方便找到起点顺着链表往后扫就行磁盘顺序读的效率远高于随机 IO。2.3 三层 B树到底能覆盖多少数据这是我最喜欢算的一笔账。InnoDB 默认页大小是 16KB假设主键是 bigint占 8 字节加上指针之类的开销按 6 字节算每个索引项大约 14 字节。那么一个非叶子页大约能装 16384 / 14 ≈ 1170 个索引项。再假设一行数据加上事务字段大约 1KB一个叶子页能装 16 行左右。两层 B树能覆盖的数据量是 1170 × 16 ≈ 1.8 万行三层 B树是 1170 × 1170 × 16 ≈ 2190 万行。也就是说一张两千多万行的表绝大多数查询只通过三次 IO 左右就能定位到目标页。再加上 InnoDB 的缓冲池缓存机制真实访问成本还能进一步降低。这也是为什么几百万行的表建了索引能从秒级降到毫秒级结构优势决定了数量级差距。3. 索引分类与建索引的姿势主键、二级、复合、全文聊完底层结构再看 MySQL 索引的分类就清楚多了。语法层面常见的索引类型有主键索引、唯一索引、普通索引、复合索引、全文索引。它们不是简单的并列关系理解的关键在于 InnoDB 的数据组织方式。3.1 聚簇索引与二级索引两套完全不同的查找逻辑InnoDB 表本质上是按主键顺序组织的一棵 B树这个索引就叫聚簇索引叶子节点上存放的是整行数据。你建表时指定 PRIMARY KEYMySQL 就会用它做聚簇索引如果你不指定主键InnoDB 会找一个非空唯一索引当主键再找不到内部会生成一个隐藏的 6 字节主键。所以主键索引永远存在只是你感知不到而已。除了主键之外的其他索引都叫二级索引也叫普通索引、非聚簇索引叶子节点存储的不是整行数据而是对应行的主键值。查询流程是先通过二级索引找到主键然后再去聚簇索引里回表取整行数据。这个“回表”动作是性能优化的关键概念后面会反复提到。正因为这个结构主键设计变得非常重要。主键最好用自增 id、雪花 id 这类有序且单调的值尽量避免随机 UUID 字符串。原因很朴素聚簇索引的数据物理上按主键排序存放随机主键会频繁触发页分裂和页重组插入性能大幅下降表碎片也多。这是很多人在建表阶段就踩的坑等线上数据量大了一查 EXPLAIN 才后悔。3.2 普通索引、唯一索引、复合索引的取舍普通索引只承担加速查询的任务允许重复值适合对非唯一列做条件筛选。唯一索引在普通索引基础上多了唯一性约束索引列不允许重复值适合业务上本身就唯一的字段比如身份证号、手机号、支付流水号。用唯一索引还有一个隐藏收益数据库层面帮你防住重复数据应用层可以省掉一部分去重逻辑减少一次额外查询。复合索引是日常优化中最重要的索引形式也叫联合索引。它可以在一个索引里组织多个字段比如 (user_id, status, create_time)。查询条件同时包含这些列时有机会一次索引定位到位而不是建三个单列索引各管各的。单列索引的尴尬在于WHERE 同时出现两个条件时优化器大概率只能选择其中一个索引另一个条件只能回到表里过滤效率和复合索引完全不在一个量级上。所以建索引时重点思考的是“哪几个字段经常一起出现在查询里”而不是“哪个字段经常出现”。3.3 全文索引与倒排索引全文索引用的是倒排索引和 B树是两套体系。它先把文本内容做分词记录“词到文档”的映射关系所以你用它执行 LIKE %关键词% 这种模糊搜索时效果远好于普通索引。普通索引面对前导通配符的 LIKE 基本无能为力只能全表扫而全文索引用词表快速定位文档响应速度能提升几个档次。MySQL 的全文索引原理和专业搜索引擎一致但功能相对简单适合中小规模场景。如果文本量很大、分词需求复杂我更建议交给专门搜索引擎去处理别硬让 MySQL 扛高并发全文检索。另外记得全文索引和普通索引不是替代关系它们解决的是不同类型的问题。4. 必须吃透的命中规则最左前缀、回表、覆盖索引建了索引却不生效比没有索引更让人难受。很多朋友问“我明明加了索引为什么 EXPLAIN 里还是 ALL”答案通常出在这一节讲的规则上。索引生效的判定并不玄学核心是 B树如何利用索引列的顺序来“裁剪”搜索范围。4.1 最左前缀原则到底怎么理解复合索引 (a, b, c) 在逻辑上相当于同时创建了 (a)、(a, b)、(a, b, c) 三套索引查询条件必须包含第一个字段 a索引才能被使用。原因是索引内部先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。你说的“按条件找数据”本质上是在这棵排序树上定位起点和终点。拿电话薄类比电话薄按“姓氏、名字、中间名”排序你想找姓“王”的人顺着索引一定能精确翻到王姓区域。但如果只给一个名字叫“小明”电话薄就无能为力了因为“名字”不是第一排序键你无法在整本字典序结构里直接定位。这就是最左前缀的底层逻辑。所以设计复合索引字段顺序时要把查询中最常出现的等值条件放在最前面。比如订单查询总是先定位 user_id再筛 status 和 create_time索引顺序 (user_id, status, create_time) 就明显好于 (status, user_id, create_time)。4.2 回表与覆盖索引的代价差异二级索引找到主键之后还需要去聚簇索引取整行数据这个过程叫回表。每回一次表就是一次主键查找。如果查询命中了大量二级索引记录回表次数会成倍放大。看起来只多了一小步实际可能让查询从毫秒级变成几十毫秒甚至更差。避免回表最直接的方式是覆盖索引。所谓覆盖就是查询所需的字段全部包含在索引列里MySQL 直接在索引页拿到数据返回不需要再回表。比如 SELECT user_id, status FROM orders WHERE user_id 10086 AND status PAID索引 (user_id, status) 已经带着这两个字段查询自然不需要回表。但如果 SELECT 里多选了一个 amount而 amount 不在索引里回表就无法避免。日常优化里我经常针对高频查询把 SELECT 的列和 WHERE 的列组合成一个覆盖索引。这是性价比最高的优化手段之一尤其适合读多写少的报表类查询。4.3 什么时候索引会失效几个高频失效场景必须刻在脑子里。第一对索引列使用函数。WHERE DATE(create_time) 2024-01-01 看着很正常但 MySQL 无法在 B树上对一个函数结果做范围裁剪它只能把索引列的值全部取出来算一遍再筛选索引自然失效。正确写法是 create_time 2024-01-01 AND create_time 2024-01-02。第二隐式类型转换。手机号字段是 varchar你写 WHERE phone 13800000000数字和字符串比较时 MySQL 会尝试把索引列转换为数字导致索引失效。写这种条件时老老实实加引号。第三LIKE 前导通配符。WHERE name LIKE %张% 无法利用前缀定位索引失效但 name LIKE 张% 可以走索引因为 B树能通过前缀确定搜索边界。第四优化器主动放弃。如果字段区分度太低比如性别字段只有两个值任何一个值都能匹配表中 50% 的行优化器评估后发现走索引要大量回表不如全表扫干脆于是放弃索引。这种场景不是 MySQL 傻而是索引确实帮不上忙。4.4 用 EXPLAIN 看真实执行计划EXPLAIN 是排查索引问题的必修课。重点看四列type 表示访问类型system 到 const、eq_ref、ref、range、index、ALL 依次变差看到 ALL 基本等于全表扫描key 是实际使用的索引名为 NULL 表示没走到索引rows 是预估扫描行数越小越好Extra 里的 Using index 表示走了覆盖索引Using filesort 表示需要额外排序Using index condition 表示索引下推已生效。一条经验rows 从几十万变成几十说明索引大概率已经生效如果 Extra 里还有 Using filesort说明 ORDER BY 的字段没被索引吃透。拿一次真实查询举例EXPLAIN 输出显示 type ref、key idx_user_status_time、rows 29、Extra 为空那这就是一个非常健康的执行计划。5. 一次订单查询优化的完整链路从慢查询日志到索引调整原理讲了一堆不实操等于白看。这一节我以一个模拟的订单表为例给出一条完整可复现的优化链路。表结构大概是这样的CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status varchar(16) NOT NULL DEFAULT CREATED, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;假设数据量 500 万行运营反馈某个订单列表页面打开很慢。接下来按流程走。5.1 开启慢查询日志锁定嫌疑 SQLMySQL 提供了慢查询日志可以自动把执行时间超过阈值的 SQL 捞出来。排查时先用临时参数打开SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output TABLE;第一次排查不用纠结日志文件路径log_output 设为 TABLE 后慢查询会记录到 mysql.slow_log 表里直接查这个表就能看到候选 SQLSELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 1;拿到慢 SQL 之后别急着改先看两条信息执行次数和单次耗时。频率高且耗时长的查询优先处理偶尔一次的全表扫描可以先放着。真实环境里我见过太多人花一整天优化一个一天只跑一次的报表查询结果核心业务查询仍在跳脚慢方向比努力更重要。5.2 拿到 SQL 后先做执行计划“体检”假设捕获到的 SQL 是SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 10086 AND status PAID ORDER BY create_time DESC LIMIT 20;先执行 EXPLAINEXPLAIN SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 10086 AND status PAID ORDER BY create_time DESC LIMIT 20;大概率会看到 type ALLrows 接近 500 万Extra 里有 Using filesort。两个信号同时亮红灯没有可用索引且排序要另起炉灶。500 万行全表扫描再把结果在内存或临时表里排序慢是必然的。5.3 设计复合索引并验证效果这个查询的模式非常典型user_id 等值、status 等值、create_time 排序。按照最左前缀和“等值在前、排序在后”的规则建立复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);字段顺序的依据是user_id 和 status 都是等值筛选放前面create_time 是排序字段放最后。这样 MySQL 可以在索引内部就按 create_time 顺序取数据Using filesort 直接消失。优化完再看执行计划type 会变成 refkey 是 idx_user_status_timerows 下降到几行Extra 里不再有 Using filesort。我做过一模一样的实验优化前 2.3 秒优化后 30 毫秒以内。如果还想继续压榨可以把 SELECT 的列也纳入索引组合形成覆盖索引不过要小心索引列太多带来的写开销。这里提醒一句建完索引不是终点。插入、更新数据时要同步维护索引树写性能会有折损。对写多读少的表索引数量必须克制宁可让查询慢一点也别把写入拖垮。5.4 我习惯的优化后检查清单每次做完索引优化我都会按这个清单过一遍再看一次 EXPLAIN确认 key、rows、Extra 三项符合预期跑一次真实业务请求验证端到端延迟达标对比 buffer pool 命中率索引冷启动时第一次查询可能仍偏慢等数据进缓存后再评估持续观察慢查询日志确认这条 SQL 不再回潮。这样一轮下来绝大多数索引盲区都能被扫平。别嫌流程啰嗦线上系统出问题最大的风险不是慢而是你改了索引之后把别的查询带崩了。每一步都有验证才能保证安全上线。6. 我在生产环境积攒的索引实践经验到了这一节我不再重复教科书内容讲点我在真实环境里积攒的规矩和教训。这些事踩过一次就明白但提前知道了能少踩很多坑。6.1 不建议建索引的场景第一类是小表。几千行数据全表扫描的成本本来就极低一个索引带来的查询加速微乎其微反而增加了写入开销和磁盘占用。MySQL 优化器面对小表也经常因为统计信息判断全表扫描更划算直接放弃索引。第二类是区分度极低的字段。性别、状态、是否删除这类字段一个值就能覆盖表中大量数据。索引的目的是缩小搜索范围如果一个值能匹配三分之一的表那这个索引的“裁剪能力”就很弱优化器大概率选择扫全表。第三类是高频写入的流水表、日志表。这类表的瓶颈通常不是查询而是写入。每次写入都要维护所有的二级索引索引数量越多写入放大越严重。我一般会把这类表的查询需求合并成少数几个复合索引保住写入吞吐牺牲部分查询弹性。6.2 覆盖索引和冗余索引管理覆盖索引是性能优化里的“作弊器”但别上瘾。之前见过有人为了让一个报表查询走覆盖索引把一个 16 个字段的表建了 8 个复合索引结果写入时 MySQL 被索引维护拖到报警。每个索引都占用磁盘空间每次写操作都要同步更新成本是实打实的。覆盖索引只应针对高频核心查询设计不能每来一个查询就加一个索引。另外一个高频问题是冗余索引。已经有 (a, b) 联合索引又单独建一个 (a) 索引。根据最左前缀原则(a, b) 完全可以覆盖 (a) 的查询场景单独的 (a) 就是白占空间的冗余索引。这种问题在业务快速迭代时非常常见新需求加索引、没人删旧索引时间一长索引数量失控。我定期巡检时会用 sys.schema_redundant_indexes 这个系统视图自动找出冗余索引再逐个确认删除。注意不要盲目全删有些单独的 (a) 可能是为了别的最左前缀场景需要结合业务确认。6.3 一些我在实际踩坑中养成的习惯我给自己定了几条铁律供你参考建索引之前先确认问题真的出在索引而不是 SQL 本身写得低效复合索引字段顺序永远把等值查询字段放在排序和范围字段前面尽量让 ORDER BY 字段成为索引的一部分消灭 Using filesort大表加索引选业务低峰期使用在线 DDL 方式操作避免长时间锁表影响线上。还有一个小技巧如果某个查询已经不慢但你还想再优化先看能不能改造成覆盖索引而不是一股脑加新字段。SELECT 列尽量限制在索引列范围内每次查询都能少一次主键回表积少成多效果非常显著。最后分享一个小习惯每次做索引优化先在暂存环境里用 EXPLAIN 对比优化前后的 rows 和 Extra确认没副作用后再上生产。MySQL 索引优化没有银弹但把 B树结构、最左前缀、回表、覆盖索引这几个底层概念想透了绝大多数慢查询都能找到明确解法。这一章到这里索引的底层逻辑就算真正入门了。下一章可以继续沿着执行计划往下聊也可以去研究事务和锁看你对哪块更感兴趣。 MySQL索引优化实战：从慢查询到B+树与复合索引设计
1. 从一次线上慢查询说起索引到底在解决什么问题这一章我们来到 MySQL 学习路线上一个真正决定“快慢”的节点——索引。前面几章都在聊建库、建表、写 SQL数据量几千条的时候怎么查都行等你真正面对线上几十万、几百万行的表一条查询从毫秒变成几秒问题一大半出在索引上。我最早接手一个订单表优化任务时SQL 看起来没有任何问题该返回的字段也不多但就是慢。后来发现 user_id 上根本没有索引表里已经攒了 300 多万行每次查询都在做全表扫描。给这一列加完索引同样的条件从 2.3 秒降到了 3 毫秒。那一刻我才真正理解“索引是什么、索引解决什么问题”。1.1 没有索引时 MySQL 在做什么在 EXPLAIN 执行计划里最扎眼的通常是 type ALL这个值的意思就是全表扫描。MySQL 把整张表从头到尾读一遍从第一行开始挨个判断 WHERE 条件符合条件就返回不符合就跳过直到最后一行结束。数据量小的时候这个成本不明显一万行、几万行的表InnoDB 一次 IO 读一个页默认 16KB整张表可能只占几十个页几十次 IO 就把全表扫完了。可当表膨胀到几百万行、占好几个 GB全表扫描要读的页数量就非常恐怖了。更要命的是这些页在磁盘上的物理位置并不连续大量随机 IO 会让机械硬盘处于来回寻道的状态SSD 虽然好一些但也经不住这种浪费。全表扫描不是设计成“坏人”的它是 MySQL 优化器在没有任何可用索引时的兜底方案。问题是它和你在实体书里找一段话完全一样必须从头翻到尾中途无法跳跃。你翻一本 500 页的小说找某个词的时候只能一页一页过但如果这本书最后有索引你直接翻到对应页码就行。索引节省的不是 10% 的力气而是几个数量级的时间差距。1.2 索引的本质是什么索引是一种额外的数据结构它把某一列或多列的值按照一定规则组织起来并在内部保存指向真实数据行的“地址”或“主键值”。MySQL 拿到查询条件后先到索引里去定位而不是直接扫全表定位到目标记录之后再回原表取完整数据。我经常把索引类比成新华字典的部首检字表正文部分按拼音排序你要查“索”字先翻拼音 suǒ 对应的页码顺着页码直接翻到那一页而不是从第一页开始逐页找。MySQL 的普通二级索引就是这个思路而主键索引更彻底——数据本身按主键顺序存放相当于整本书的正文就按页码排好了你报页码直接翻连二次定位都不需要。理解这一点之后很多直觉就能建立起来索引不是魔法它只是用空间换时间用一部分磁盘空间存储一个“目录”让 MySQL 能快速定位目标。代价是每次插入、更新、删除数据时都要同步维护索引树所以索引不是越多越好这个话题后面我会单独展开。1.3 一个最简单却能直观感受的验证实验与其听我说不如自己动手验证一把。随便找一张数据量在几十万行以上的表先看一条查询的执行计划EXPLAIN SELECT * FROM users WHERE username zhangsan;没建索引时type 一栏基本是 ALLrows 是整表行数。然后创建一个普通索引ALTER TABLE users ADD INDEX idx_username (username);再执行一次同样的 EXPLAIN你会发现 type 变成 refrows 迅速降到一个很小的数字。别小看这一步这是建立索引直觉最有效的方式。我见过很多人背了一堆索引理论遇到真实慢查询还是懵原因就是没亲手对比过优化前后的执行计划。2. 为什么选了 B树索引底层数据结构拆解很多人会背“InnoDB 用 B树”但真问一句“为什么是它”往往就答不上来了。这节把这个问题拆开从数据结构演进的角度讲清楚以后你再看到任何关于索引的讨论都不会被术语唬住。2.1 哈希索引为什么不够用最容易想到的加速结构其实是哈希表。对索引列做哈希运算得到一个定长的数字再根据数字定位到对应的桶。做等值查询时一次哈希计算加一次数组定位就能命中目标理论时间复杂度接近 O(1)速度远快于 B树。但哈希表的致命问题是无法处理范围查询。WHERE age 18、BETWEEN 一段日期、ORDER BY create_time这些操作依赖数据之间的顺序关系。哈希函数会把有序的输入值打散成看似随机的输出哈希表内部也没有“按序遍历”的能力。你只能一个一个枚举所有值再做判断等于退化回全表扫描。所以 InnoDB 的逻辑索引结构从来不是哈希表但它内部确实有一个自适应哈希索引Adaptive Hash Index这是数据库在内存里为高频热点值自动建立的加速结构不需要你手动创建也查不到逻辑上的索引定义。它只是 B树之上的锦上添花解决不了范围查询问题。讨论真正可控的索引结构还是得回到 B树。2.2 树形结构演进二叉树、红黑树、B树、B树先看二叉搜索树。假设主键字段是自增 id数据插入时按顺序增长二叉搜索树会极不均匀地长成一条链表树高等于数据行数。查最后一行主键理论上要访问 N 个节点每次节点访问都对应一次磁盘 IO这显然不能接受。红黑树是平衡二叉搜索树树高能稳定在 O(logN)。对于几百万行数据树高大概二十多层查询一条数据极限情况要访问二十多个节点。每个节点一次磁盘读二十多次 IO 对数据库来说是灾难级别的成本。虽然节点有机会被缓存在内存里但只要某一层的数据不在 buffer pool就要真真实实做一次磁盘 IO冷数据场景照样扛不住。B树的关键改进是让每个节点同时存储多个键和多个孩子指针树从“瘦高”变成“矮胖”。同样数据量B树通常只需要三四层。MySQL 在 B树基础上做了另一层改进使用 B树差别在于B树的非叶子节点只存放索引键不存放数据全部数据都存在叶子节点上叶子节点之间用双向链表串联。别小看这几个差异。非叶子节点不存数据意味着一个固定大小的页面能塞进更多索引键树高进一步下降。16KB 的页如果全放 bigint 主键和指针大约能放一千多个键三层就能覆盖上千万行。把数据集中在叶子节点所有查询都必须走到叶子层查询链路稳定没有“有时候快有时候慢”的问题。叶子节点串成链表之后范围查询和排序变得异常方便找到起点顺着链表往后扫就行磁盘顺序读的效率远高于随机 IO。2.3 三层 B树到底能覆盖多少数据这是我最喜欢算的一笔账。InnoDB 默认页大小是 16KB假设主键是 bigint占 8 字节加上指针之类的开销按 6 字节算每个索引项大约 14 字节。那么一个非叶子页大约能装 16384 / 14 ≈ 1170 个索引项。再假设一行数据加上事务字段大约 1KB一个叶子页能装 16 行左右。两层 B树能覆盖的数据量是 1170 × 16 ≈ 1.8 万行三层 B树是 1170 × 1170 × 16 ≈ 2190 万行。也就是说一张两千多万行的表绝大多数查询只通过三次 IO 左右就能定位到目标页。再加上 InnoDB 的缓冲池缓存机制真实访问成本还能进一步降低。这也是为什么几百万行的表建了索引能从秒级降到毫秒级结构优势决定了数量级差距。3. 索引分类与建索引的姿势主键、二级、复合、全文聊完底层结构再看 MySQL 索引的分类就清楚多了。语法层面常见的索引类型有主键索引、唯一索引、普通索引、复合索引、全文索引。它们不是简单的并列关系理解的关键在于 InnoDB 的数据组织方式。3.1 聚簇索引与二级索引两套完全不同的查找逻辑InnoDB 表本质上是按主键顺序组织的一棵 B树这个索引就叫聚簇索引叶子节点上存放的是整行数据。你建表时指定 PRIMARY KEYMySQL 就会用它做聚簇索引如果你不指定主键InnoDB 会找一个非空唯一索引当主键再找不到内部会生成一个隐藏的 6 字节主键。所以主键索引永远存在只是你感知不到而已。除了主键之外的其他索引都叫二级索引也叫普通索引、非聚簇索引叶子节点存储的不是整行数据而是对应行的主键值。查询流程是先通过二级索引找到主键然后再去聚簇索引里回表取整行数据。这个“回表”动作是性能优化的关键概念后面会反复提到。正因为这个结构主键设计变得非常重要。主键最好用自增 id、雪花 id 这类有序且单调的值尽量避免随机 UUID 字符串。原因很朴素聚簇索引的数据物理上按主键排序存放随机主键会频繁触发页分裂和页重组插入性能大幅下降表碎片也多。这是很多人在建表阶段就踩的坑等线上数据量大了一查 EXPLAIN 才后悔。3.2 普通索引、唯一索引、复合索引的取舍普通索引只承担加速查询的任务允许重复值适合对非唯一列做条件筛选。唯一索引在普通索引基础上多了唯一性约束索引列不允许重复值适合业务上本身就唯一的字段比如身份证号、手机号、支付流水号。用唯一索引还有一个隐藏收益数据库层面帮你防住重复数据应用层可以省掉一部分去重逻辑减少一次额外查询。复合索引是日常优化中最重要的索引形式也叫联合索引。它可以在一个索引里组织多个字段比如 (user_id, status, create_time)。查询条件同时包含这些列时有机会一次索引定位到位而不是建三个单列索引各管各的。单列索引的尴尬在于WHERE 同时出现两个条件时优化器大概率只能选择其中一个索引另一个条件只能回到表里过滤效率和复合索引完全不在一个量级上。所以建索引时重点思考的是“哪几个字段经常一起出现在查询里”而不是“哪个字段经常出现”。3.3 全文索引与倒排索引全文索引用的是倒排索引和 B树是两套体系。它先把文本内容做分词记录“词到文档”的映射关系所以你用它执行 LIKE %关键词% 这种模糊搜索时效果远好于普通索引。普通索引面对前导通配符的 LIKE 基本无能为力只能全表扫而全文索引用词表快速定位文档响应速度能提升几个档次。MySQL 的全文索引原理和专业搜索引擎一致但功能相对简单适合中小规模场景。如果文本量很大、分词需求复杂我更建议交给专门搜索引擎去处理别硬让 MySQL 扛高并发全文检索。另外记得全文索引和普通索引不是替代关系它们解决的是不同类型的问题。4. 必须吃透的命中规则最左前缀、回表、覆盖索引建了索引却不生效比没有索引更让人难受。很多朋友问“我明明加了索引为什么 EXPLAIN 里还是 ALL”答案通常出在这一节讲的规则上。索引生效的判定并不玄学核心是 B树如何利用索引列的顺序来“裁剪”搜索范围。4.1 最左前缀原则到底怎么理解复合索引 (a, b, c) 在逻辑上相当于同时创建了 (a)、(a, b)、(a, b, c) 三套索引查询条件必须包含第一个字段 a索引才能被使用。原因是索引内部先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。你说的“按条件找数据”本质上是在这棵排序树上定位起点和终点。拿电话薄类比电话薄按“姓氏、名字、中间名”排序你想找姓“王”的人顺着索引一定能精确翻到王姓区域。但如果只给一个名字叫“小明”电话薄就无能为力了因为“名字”不是第一排序键你无法在整本字典序结构里直接定位。这就是最左前缀的底层逻辑。所以设计复合索引字段顺序时要把查询中最常出现的等值条件放在最前面。比如订单查询总是先定位 user_id再筛 status 和 create_time索引顺序 (user_id, status, create_time) 就明显好于 (status, user_id, create_time)。4.2 回表与覆盖索引的代价差异二级索引找到主键之后还需要去聚簇索引取整行数据这个过程叫回表。每回一次表就是一次主键查找。如果查询命中了大量二级索引记录回表次数会成倍放大。看起来只多了一小步实际可能让查询从毫秒级变成几十毫秒甚至更差。避免回表最直接的方式是覆盖索引。所谓覆盖就是查询所需的字段全部包含在索引列里MySQL 直接在索引页拿到数据返回不需要再回表。比如 SELECT user_id, status FROM orders WHERE user_id 10086 AND status PAID索引 (user_id, status) 已经带着这两个字段查询自然不需要回表。但如果 SELECT 里多选了一个 amount而 amount 不在索引里回表就无法避免。日常优化里我经常针对高频查询把 SELECT 的列和 WHERE 的列组合成一个覆盖索引。这是性价比最高的优化手段之一尤其适合读多写少的报表类查询。4.3 什么时候索引会失效几个高频失效场景必须刻在脑子里。第一对索引列使用函数。WHERE DATE(create_time) 2024-01-01 看着很正常但 MySQL 无法在 B树上对一个函数结果做范围裁剪它只能把索引列的值全部取出来算一遍再筛选索引自然失效。正确写法是 create_time 2024-01-01 AND create_time 2024-01-02。第二隐式类型转换。手机号字段是 varchar你写 WHERE phone 13800000000数字和字符串比较时 MySQL 会尝试把索引列转换为数字导致索引失效。写这种条件时老老实实加引号。第三LIKE 前导通配符。WHERE name LIKE %张% 无法利用前缀定位索引失效但 name LIKE 张% 可以走索引因为 B树能通过前缀确定搜索边界。第四优化器主动放弃。如果字段区分度太低比如性别字段只有两个值任何一个值都能匹配表中 50% 的行优化器评估后发现走索引要大量回表不如全表扫干脆于是放弃索引。这种场景不是 MySQL 傻而是索引确实帮不上忙。4.4 用 EXPLAIN 看真实执行计划EXPLAIN 是排查索引问题的必修课。重点看四列type 表示访问类型system 到 const、eq_ref、ref、range、index、ALL 依次变差看到 ALL 基本等于全表扫描key 是实际使用的索引名为 NULL 表示没走到索引rows 是预估扫描行数越小越好Extra 里的 Using index 表示走了覆盖索引Using filesort 表示需要额外排序Using index condition 表示索引下推已生效。一条经验rows 从几十万变成几十说明索引大概率已经生效如果 Extra 里还有 Using filesort说明 ORDER BY 的字段没被索引吃透。拿一次真实查询举例EXPLAIN 输出显示 type ref、key idx_user_status_time、rows 29、Extra 为空那这就是一个非常健康的执行计划。5. 一次订单查询优化的完整链路从慢查询日志到索引调整原理讲了一堆不实操等于白看。这一节我以一个模拟的订单表为例给出一条完整可复现的优化链路。表结构大概是这样的CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status varchar(16) NOT NULL DEFAULT CREATED, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;假设数据量 500 万行运营反馈某个订单列表页面打开很慢。接下来按流程走。5.1 开启慢查询日志锁定嫌疑 SQLMySQL 提供了慢查询日志可以自动把执行时间超过阈值的 SQL 捞出来。排查时先用临时参数打开SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output TABLE;第一次排查不用纠结日志文件路径log_output 设为 TABLE 后慢查询会记录到 mysql.slow_log 表里直接查这个表就能看到候选 SQLSELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 1;拿到慢 SQL 之后别急着改先看两条信息执行次数和单次耗时。频率高且耗时长的查询优先处理偶尔一次的全表扫描可以先放着。真实环境里我见过太多人花一整天优化一个一天只跑一次的报表查询结果核心业务查询仍在跳脚慢方向比努力更重要。5.2 拿到 SQL 后先做执行计划“体检”假设捕获到的 SQL 是SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 10086 AND status PAID ORDER BY create_time DESC LIMIT 20;先执行 EXPLAINEXPLAIN SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 10086 AND status PAID ORDER BY create_time DESC LIMIT 20;大概率会看到 type ALLrows 接近 500 万Extra 里有 Using filesort。两个信号同时亮红灯没有可用索引且排序要另起炉灶。500 万行全表扫描再把结果在内存或临时表里排序慢是必然的。5.3 设计复合索引并验证效果这个查询的模式非常典型user_id 等值、status 等值、create_time 排序。按照最左前缀和“等值在前、排序在后”的规则建立复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);字段顺序的依据是user_id 和 status 都是等值筛选放前面create_time 是排序字段放最后。这样 MySQL 可以在索引内部就按 create_time 顺序取数据Using filesort 直接消失。优化完再看执行计划type 会变成 refkey 是 idx_user_status_timerows 下降到几行Extra 里不再有 Using filesort。我做过一模一样的实验优化前 2.3 秒优化后 30 毫秒以内。如果还想继续压榨可以把 SELECT 的列也纳入索引组合形成覆盖索引不过要小心索引列太多带来的写开销。这里提醒一句建完索引不是终点。插入、更新数据时要同步维护索引树写性能会有折损。对写多读少的表索引数量必须克制宁可让查询慢一点也别把写入拖垮。5.4 我习惯的优化后检查清单每次做完索引优化我都会按这个清单过一遍再看一次 EXPLAIN确认 key、rows、Extra 三项符合预期跑一次真实业务请求验证端到端延迟达标对比 buffer pool 命中率索引冷启动时第一次查询可能仍偏慢等数据进缓存后再评估持续观察慢查询日志确认这条 SQL 不再回潮。这样一轮下来绝大多数索引盲区都能被扫平。别嫌流程啰嗦线上系统出问题最大的风险不是慢而是你改了索引之后把别的查询带崩了。每一步都有验证才能保证安全上线。6. 我在生产环境积攒的索引实践经验到了这一节我不再重复教科书内容讲点我在真实环境里积攒的规矩和教训。这些事踩过一次就明白但提前知道了能少踩很多坑。6.1 不建议建索引的场景第一类是小表。几千行数据全表扫描的成本本来就极低一个索引带来的查询加速微乎其微反而增加了写入开销和磁盘占用。MySQL 优化器面对小表也经常因为统计信息判断全表扫描更划算直接放弃索引。第二类是区分度极低的字段。性别、状态、是否删除这类字段一个值就能覆盖表中大量数据。索引的目的是缩小搜索范围如果一个值能匹配三分之一的表那这个索引的“裁剪能力”就很弱优化器大概率选择扫全表。第三类是高频写入的流水表、日志表。这类表的瓶颈通常不是查询而是写入。每次写入都要维护所有的二级索引索引数量越多写入放大越严重。我一般会把这类表的查询需求合并成少数几个复合索引保住写入吞吐牺牲部分查询弹性。6.2 覆盖索引和冗余索引管理覆盖索引是性能优化里的“作弊器”但别上瘾。之前见过有人为了让一个报表查询走覆盖索引把一个 16 个字段的表建了 8 个复合索引结果写入时 MySQL 被索引维护拖到报警。每个索引都占用磁盘空间每次写操作都要同步更新成本是实打实的。覆盖索引只应针对高频核心查询设计不能每来一个查询就加一个索引。另外一个高频问题是冗余索引。已经有 (a, b) 联合索引又单独建一个 (a) 索引。根据最左前缀原则(a, b) 完全可以覆盖 (a) 的查询场景单独的 (a) 就是白占空间的冗余索引。这种问题在业务快速迭代时非常常见新需求加索引、没人删旧索引时间一长索引数量失控。我定期巡检时会用 sys.schema_redundant_indexes 这个系统视图自动找出冗余索引再逐个确认删除。注意不要盲目全删有些单独的 (a) 可能是为了别的最左前缀场景需要结合业务确认。6.3 一些我在实际踩坑中养成的习惯我给自己定了几条铁律供你参考建索引之前先确认问题真的出在索引而不是 SQL 本身写得低效复合索引字段顺序永远把等值查询字段放在排序和范围字段前面尽量让 ORDER BY 字段成为索引的一部分消灭 Using filesort大表加索引选业务低峰期使用在线 DDL 方式操作避免长时间锁表影响线上。还有一个小技巧如果某个查询已经不慢但你还想再优化先看能不能改造成覆盖索引而不是一股脑加新字段。SELECT 列尽量限制在索引列范围内每次查询都能少一次主键回表积少成多效果非常显著。最后分享一个小习惯每次做索引优化先在暂存环境里用 EXPLAIN 对比优化前后的 rows 和 Extra确认没副作用后再上生产。MySQL 索引优化没有银弹但把 B树结构、最左前缀、回表、覆盖索引这几个底层概念想透了绝大多数慢查询都能找到明确解法。这一章到这里索引的底层逻辑就算真正入门了。下一章可以继续沿着执行计划往下聊也可以去研究事务和锁看你对哪块更感兴趣。