企业官网建设流程全解析

做 MySQL 调优绕不开索引，做索引设计先得把分类搞明白。很多人张口就是“建个索引”，可真要问你 B+树索引和哈希索引啥区别、主键索引跟唯一索引能不能互相替代、二级索引回表到底怎么回事、复合索引字段顺序为什么不能乱，能一次说清楚的没几个。这篇文章我把 MySQL 索引按数据结构、逻辑功能、物理存储、使用场景这几个维度拆开讲，结合我这些年维护千万级业务表的实际踩坑经历，给出可以直接用的建索引方法和避雷清单。适合正在学 MySQL 原理的开发、刚接手慢 SQL 排查的后端，以及想系统梳理索引知识的 DBA 新人。不绕弯子，直接上干货。

1. 先按底层数据结构分：B+树、哈希、全文索引谁在什么场景扛事

1.1 B+树索引：MySQL 的“大门面”，90% 的查询优化都靠它

InnoDB 引擎最常见的索引底层结构就是 B+树。B+树跟普通二叉树不一样，它有这几个特性值得记住：非叶子节点只存键值和指针，不存数据本身；叶子节点存完整索引项，并且叶子节点之间用双向链表串起来。这么一个设计带来一个关键结果——树的高度很矮，一般三到四层就能放下千万级数据。也就是说你查一条数据，最多做三四次磁盘 I/O 就到头了。

我习惯用一个类比：B+树索引就像一本带目录和页码的工具书，目录页不写正文内容，只写关键词和页码，真正内容都在正文。你在目录页一层一层往下翻，很快就能定位到页码，然后直接翻到那一页找到完整信息。这就是为啥 B+树既能做等值查询，又能高效做范围查询——叶子节点本来就是按顺序链表排列的，找到起点之后沿着链表往后扫就行。

实际项目里，InnoDB 的主键索引、二级索引、普通索引、唯一索引，默认全是 B+树结构。你不需要为每个索引声明“我要用 B+树”，引擎默认就是它。这里有个技术面试常问的点：为什么不用红黑树，也不用跳表？答案是 B+树扇出高、树矮，单次查询磁盘 I/O 次数少，而且叶子链表天然支持范围扫描。MySQL 最常用的查询场景就是“找出某个范围内的数据”，这恰好是 B+树的强项。

1.2 哈希索引和全文索引：特定场景的专用武器

哈希索引的结构是哈希表，原理是把索引键通过哈希函数算出一个散列值，指向对应数据行。它的最大优势是等值查询极快，理论上是 O(1) 复杂度，比 B+树还要快；最大劣势是没办法做范围查询，也没有顺序概念，ORDER BY 和 BETWEEN 到它这里基本歇菜。

在 InnoDB 里，你没法直接创建显式哈希索引，但引擎内部有个“自适应哈希索引”是自动启用的特性。当某些等值查询被反复执行，InnoDB 会在 B+树索引之上再建一层内存哈希缓存，用来加速这类高频等值访问。这个特性默认开启，不需要手工干预，但它只对缓冲池里的热点页生效，而且只在等值匹配场景有意义。MyISAM 和 MEMORY 引擎倒是支持显式哈希索引，但在实际生产里用得很少，更多场景是在纯内存系统里发挥作用。

全文索引就不靠 B+树了，它用的是倒排索引。简单说，就是把文档内容拆成一个个词条，再维护一张“词条到文档位置”的映射表，搜索时直接查词条，不用全表扫。MySQL 里用 FULLTEXT 关键字创建全文索引，配合 MATCH ... AGAINST 语法做全文检索。适合文章、留言、商品描述这类大文本字段的模糊搜索。不过要注意，MySQL 内置全文索引的解析器对中文分词支持一直比较一般，生产环境真要搞中文搜索，更多人还是会选 Elasticsearch，而不是把全文索引当主力。

1.3 空间索引与引擎差异：冷门但存在的分类

还有一类容易被忽略的索引是空间索引。MySQL 支持 SPATIAL 索引，底层一般用 R-Tree 结构，专门处理 GEOMETRY 类型的地理空间数据。比如地图应用里要查“某个经纬度点周围 5 公里内的门店”，这类场景普通 B+树索引很难高效支撑，而 R-Tree 能对二维空间对象做快速范围检索。不过在日常业务开发里用到空间索引的机会确实不多，知道它属于索引分类的一支、并且和 B+树有本质区别就够了。

另外，讨论索引分类时一定要带上存储引擎的差异。InnoDB 和 MyISAM 都有 B+树索引，但 InnoDB 是聚簇索引、MyISAM 是非聚簇索引；全文索引最早是 MyISAM 的卖点，InnoDB 从 5.6 版本才开始支持。有些开发在 MyISAM 表上习惯了某种行为，切到 InnoDB 后发现索引机制完全不同，这就是因为引擎层面的物理组织方式变了。下面第 3 章展开讲聚簇索引时，这个差异还会再放大。

2. 按逻辑功能分：主键索引、唯一索引、普通索引、全文索引怎么选

2.1 一张表搞定四种功能索引的核心差异

按功能划分是建表时最常接触的一层：主键索引、唯一索引、普通索引、全文索引。它们底层实现可以都是 B+树，但约束规则和使用目的完全不一样。

主键索引要求字段值非空且唯一，并且一张表只能有一个主键。唯一索引只要求值唯一，允许出现多个 NULL（在 InnoDB 里，多个 NULL 被认为是不同的值，不违反唯一性）。普通索引则啥约束都没有，纯粹是加速查询。全文索引是为了解决大文本检索匹配问题。

实际建表时，很多人默认给表加一个自增 id 作为主键，这通常是合理的，因为自增主键写数据是尾部追加，不容易产生页分裂，索引树更紧凑。但也要分场景：如果订单表需要用“订单号”关联查询，把业务唯一键也建一个唯一索引，而不是把主键改成订单号，这样既能保证关联查询走索引，又保留了主键索引的稳定性。主键一般建议选稳定、有序、占用空间小的字段，最忌讳把会频繁更新的字段设成主键——主键移动会导致整行数据在聚簇索引里大规模挪动，IO 压力非常真实，这一点到第 3 章讲聚簇索引的时候感受会更直观。

2.2 主键索引和唯一索引：差的那一个 NULL 关键在哪

主键索引和唯一索引的区别，算得上老生常谈，但又是实际操作中最容易被搞混的知识点。很多新人以为唯一索引加个 NOT NULL 约束就等于主键，这种理解最大的漏洞在于忽略了两者在 InnoDB 物理存储层面的地位完全不同。

最核心的差异可以归纳为三点。第一，约束不同：主键列不允许 NULL，唯一索引列允许有多个 NULL。第二，个数限制：一张表只能有一个主键索引，但可以有多个唯一索引。第三，物理作用不同，这一点比前两点重要得多——InnoDB 表中主键索引是聚簇索引，数据的物理存储顺序跟主键顺序一致，整张表的数据行挂在主键这棵 B+树的叶子节点上；而唯一索引只是普通二级索引，叶子节点存的是索引键值和主键值，不直接存行数据。

这个差异带出一个实际影响：如果你在唯一索引所在列上查询，命中后还需要根据叶子节点里的主键值回表才能拿到整行数据；而在主键索引上查询，因为叶子节点直接就是数据行，所以根本不用回表。所以说，主键不仅是逻辑约束，更是 InnoDB 数据组织方式的锚点。很多 DBA 会说“InnoDB 表一定要有主键”，原因就在这里：如果没有主键，InnoDB 会偷偷挑一个非空唯一索引当主键，连这个都找不到就生成隐藏的自增列，这种情况下索引效率和可维护性都会打折扣。

注意：即使某列上有唯一索引，InnoDB 也不会自动把它当主键用，除非表里确实没有主键、没有其他非空唯一索引。业务上想要“既保证唯一，又保留主键独立性”，就老老实实建一个自增主键，再另建唯一索引。

2.3 普通索引不是凑数的：适合建的几种业务场景

普通索引是最常见的索引类型，CREATE INDEX index_name ON table(column) 创建的就是普通索引。有人觉得普通索引太“弱”，不如唯一索引有存在感，这种想法不对。普通索引的真实价值在于：它能加速查询，同时不像唯一索引那样在每次写入时都要做唯一性校验，所以写入性能开销更低。

我之前维护过一个用户行为日志表，每天写入量在百万级，查询场景是“按 user_id 拉最近 30 天行为记录”。这张表高频写入，高频按 user_id 查询，但同一个 user_id 会重复出现无数次，绝不可能加唯一索引。加一个普通 B+树索引就非常合适：写入多付出的代价只是维护一颗索引树，查询则能从全表扫描变成索引定位，收益几乎是数量级的。我记得特别清楚，加索引前一条带 user_id 的查询要 800ms 左右，加索引后基本稳定在 20ms 以内。

当然，普通索引也不是随便建。如果一张表只有几千行，全表扫描本身就很快，建索引的意义更多是心理安慰。还有一个常见问题是重复索引——比如已经有了 (a,b) 复合索引，又单独建一个 (a) 索引，除非 (a) 有非常独立的查询模式，否则这个单列索引大部分情况下可以去掉。判断办法很简单：复合索引 (a,b) 本身就能覆盖对 a 的等值查询、范围查询和排序需求，单独建 (a) 基本是冗余。这种细节我后面第 5 章还会整理成清单。

3. 按物理存储分：聚簇索引、二级索引、回表与覆盖索引

3.1 聚簇索引：决定了整张表数据“放在哪”

物理存储层面，InnoDB 把 B+树索引分成两大类：聚簇索引和二级索引，也叫辅助索引、非聚簇索引。聚簇索引的特殊之处在于，索引的叶子节点直接保存整行数据。换句话说，数据行就“住在”索引树里，索引键的顺序决定了数据在磁盘上的物理排列顺序。

一张 InnoDB 表有且只有一个聚簇索引。如果你定义了主键，主键就是聚簇索引；没定义主键但有非空唯一索引，InnoDB 会选它做聚簇索引；都没有就生成隐藏的 GEN_CLUST_INDEX 自增列。这里面最关键的实操点是：你在 InnoDB 里新建的每一个二级索引，叶子节点存的不是整行数据，而是“索引键值 + 主键值”。所以主键选得长不长，直接影响所有二级索引的存储占用。主键用 varchar(64) 的 UUID，和用 bigint 自增，二级索引叶子节点每行可能多出几十字节，大表累计起来非常可观。

同理，如果主键是 UUID 这类无序值，插入时因为新记录的主键落在已有索引树的随机位置，很容易引发页分裂和索引碎片，导致写入性能波动。这也是我对自增主键偏爱的一个原因。注意，这里说的是 InnoDB；MyISAM 没有聚簇索引，它的索引和数据文件分离，索引叶子节点存的是行指针，数据行的物理存储顺序跟索引键完全没关系。

3.2 二级索引为什么要回表，覆盖索引怎么避免回表

在二级索引上查询整行数据，会有一个“回表”动作：先在二级索引 B+树上找到匹配的叶子节点，拿到主键值，再用主键去聚簇索引树上定位真正的数据行。整个过程一般是两次 B+树搜索加一次额外读取，所以二级索引查询天然比主键查询多一步，这也是很多文档里说“能用主键查就别用辅助索引查”的由来。

理解了回表，就自然理解覆盖索引的价值。如果一条查询所需的所有列，都能在二级索引的叶子节点里找到，那就不需要回表，这种索引就叫覆盖索引。最典型的例子：有一张订单表带索引 idx_user_create(user_id, create_time, status)，执行 SELECT user_id, create_time, status FROM orders WHERE user_id = 1024 AND create_time > '2024-01-01'。因为查询列都在 idx_user_create 这棵二级索引树上，MySQL 扫索引过程中直接返回结果，一次回表都不用做。这才是真性能优化——不是单纯“加了索引变快”，而是减少了整个查询的 I/O 链路。

所以 SQL 优化的一个重要习惯是：不光看 WHERE 条件能不能用索引，还要看 SELECT 的列能不能被索引覆盖。条件索引和覆盖索引经常可以合体设计成联合索引，这也是复合索引设计的常见思路。我一般会在 EXPLAIN 结果里盯着 Extra 列看有没有 Using index，看到这两个字就说明这条查询已经走了覆盖索引；如果看到 Using filesort、Using temporary，说明某些环节还需要优化，后面第 4 章、第 5 章会重点讲这些 Extra 提示。

4. 复合索引与排序优化：最左前缀原则、前缀索引、索引排序

4.1 最左前缀原则与复合索引字段顺序设计

复合索引也叫联合索引，是 MySQL 索引里使用频率最高、也最容易出问题的一类。它的核心规则是最左前缀原则：复合索引 (a,b,c) 相当于同时支持 (a)、(a,b)、(a,b,c) 三种前缀组合的检索，但跳过了 a 直接用 b 或 c，索引就失效。

举个例子，表里有索引 idx_ab(category_id, create_time)，那么 SELECT * FROM products WHERE category_id = 3 AND create_time > '2024-01-01' 可以走这个索引，因为条件里覆盖了最左列 category_id；但 SELECT * FROM products WHERE create_time > '2024-01-01' 没法走 idx_ab，因为最左列没出现，MySQL 只能全表扫或走别的单列索引。这里要专门强调一点：所谓“最左”，指的是索引定义里的列顺序，跟 SQL 里条件的书写顺序无关，优化器会自己按索引列顺序去匹配条件。

设计复合索引时，字段顺序一般按两个维度权衡：一是区分度高的列放前面，这样索引树能更快收敛；二是结合查询频率，高频等值条件列放最左，范围条件放后面。还有一个容易被忽略的点：如果 WHERE 里同时有等值条件和范围条件，比如 a=1 AND b>100 AND c=5，复合索引建议把等值列 a、c 放在前，范围列 b 放最后。因为如果范围列在中间，范围条件之后的其他字段就用不上索引排序了。这个顺序问题没有绝对公式，核心是拿着实际业务 SQL 去模拟，而不是背口诀。

提示：排查复合索引失效时，先确认 WHERE 条件里是否包含索引定义的第一个列。第一个列都没出现，后面设计得再合理也白搭。

4.2 前缀索引：用“砍短”的列做索引，省空间还能保性能

前缀索引是另一个容易被忽视的分支。当我们对一个大文本字段（比如用户地址、商品描述）建索引时，如果建全列索引，索引体积大、写入开销高，而且 B+树每层能容纳的键值数变少，树可能变高。这时候可以只取前 N 个字符建索引，比如 ALTER TABLE users ADD INDEX idx_address(address(30))，只针对 address 字段前 30 个字符建索引。

这么做的收益是索引体积显著减小，写入和查询的开销都下降；代价是区分度可能下降——如果前 30 个字符大部分都相同（比如全是“某省某市某区”开头），索引选择性就差，可能扫出大量重复前缀再做过滤。选 N 的时候，我习惯用 SQL 对比不同前缀长度下的区分度，比如统计 DISTINCT LEFT(address, n) 和 DISTINCT LEFT(address, n+10) 的比例，平衡在 90% 以上再定。还要注意，前缀索引没法用于覆盖索引场景，因为叶子节点没存完整列值，SELECT 里带了该列就必须回表。

对超长字符串字段，如果业务上查询基本都是等值匹配，另一个方案是建冗余 hash 列做索引：把字符串算一个固定长度 hash 存到新列，再用新列做唯一索引或普通索引。这个思路在精确匹配场景下比前缀索引更稳定，缺点是表结构要多出一列，写入时代码也要跟着维护。

4.3 ORDER BY 什么时候能走索引：排序优化关键点

索引不仅能加速 WHERE，还能加速 ORDER BY。因为在 B+树里数据本身就是按索引键有序的，如果排序字段刚好是索引的一部分，MySQL 可以直接按索引顺序读取，避免额外的文件排序（filesort）。判断方法还是看 EXPLAIN 的 Extra 列：如果出现 Using filesort，通常意味着排序没法完全利用索引，MySQL 得先把结果取出来再做一次排序。

举一个典型例子。表 orders 有复合索引 idx_user_create(user_id, create_time)，执行 SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC。因为 WHERE 命中了索引首列 user_id，ORDER BY 的 create_time 又是索引第二列，数据本来就是按 (user_id, create_time) 排序的，所以这个查询可以直接按索引倒序扫，Extra 里不会出现 Using filesort。相反的，如果你写 ORDER BY create_time ASC（没带 user_id 条件），或者排序字段跟索引顺序不一致，那大概率就只能 filesort 了。

关于文件排序本身，还有个参数值得提：sort_buffer_size。filesort 时 MySQL 会先尽力在排序缓冲区内完成，如果排序数据量超过缓冲区，就会生成临时文件做归并排序，性能明显下降。所以对超大结果集的分页排序，我一般会限制查询返回行数，或者把排序条件跟覆盖索引结合。不过有一说一，filesort 不等于灾难，数据量小时几十毫秒内就能完成，很多情况下为了消除 filesort 而硬加索引，反而浪费写资源。要不要为了排序建索引，得算清楚查询频率和写入频率的账。

5. 索引失效场景与排障：哪些 SQL 写法让你建的索引白搭

5.1 最常见的五种索引失效写法

我面试时爱问、平时排查慢 SQL 也总遇到的一类问题，就是索引失效。建了索引查询还是慢，大部分原因出在写法上。下面把最常见的几种索引失效场景列出来，都是可以直接对照自查的实践结论。

第一，条件列上做函数运算或表达式计算。比如 WHERE YEAR(create_time) = 2024，这种写法让 create_time 上的索引完全失效，因为 MySQL 无法采用原始列的 B+树键值来匹配计算结果。解决办法是改成 create_time >= '2024-01-01' AND create_time < '2025-01-01' 这种范围条件，索引就能用上。

第二，隐式类型转换。比如索引列是 varchar，SQL 里传了数字 123，MySQL 会尝试把列转成数字做比较，导致列上发生隐式转换，索引失效。排查时注意字段类型和参数类型的一致性，这个坑在联表 join 时尤其常见，两边 join 字段类型不一致，不但索引失效，还容易引发全表扫描。

第三，LIKE 以 % 开头。WHERE name LIKE '%abc'，因为需要匹配以 abc 结尾的任意字符串，没法从前缀开始定位，索引失效；但是 LIKE 'abc%' 是可以走索引的，因为 B+树能按 abc 前缀扫描。

第四，OR 连接时一侧没索引。WHERE a = 1 OR b = 2，除非 a、b 都有索引，否则其中一侧是全表扫描，整个查询通常就被拖垮。更稳的做法是拆成两个查询用 UNION ALL 合并，或者对 b 也建索引。

第五，对索引列使用 !=、NOT IN、IS NOT NULL 等否定条件。这类条件的匹配方式让 B+树很难精准定位，优化器大概率选择全表扫。实际业务中如果真的需要用排除条件，可以考虑改写为正向条件，或者用其他过滤条件把结果集先缩到足够小。

5.2 实操心得：建索引前必须问自己的 5 个问题

这一节是我这些年踩坑攒下来的实操清单。每次有人拿着慢 SQL 来问“要不要加索引”，我基本就是按这个思路走一遍，比直接拍脑袋建索引靠谱得多。

第一，这条 SQL 查了多少行、满足条件的数据量占全表比例多大？如果 SELECT 要返回的行数占全表 20% 以上，B+树索引基本没优势，优化器甚至更倾向全表扫描，这时候建索引可能是白费。第二，查询字段能不能被覆盖索引包含？如果 SELECT 列表里全是高频查询字段，考虑把条件列和返回列一起做进复合索引，用覆盖索引省掉回表。第三，字段本身的区分度够不够？性别、状态这类枚举值很少的列，索引选择性差，单独建索引价值很低，加进复合索引当最前列意义也有限。第四，写入代价能承受吗？每多一个索引，INSERT/UPDATE/DELETE 都要额外维护一棵树，一张写多读少的热点表，索引数量必须克制。第五，是不是已经有可以合并的冗余索引？有了 (a,b) 还要不要再建 (a)？有了单独索引还要不要再加 (a,b)？这类冗余排查建议定期做，用 SHOW INDEX FROM table 把表的所有索引拉出来审视。

提示：遇到“加了索引还是慢”，第一件事永远是跑 EXPLAIN 看执行计划，而不是凭猜测继续加新索引。看 key 列有没有用上预期的索引、rows 列估算行数是否明显变小、Extra 列有没有 Using filesort 或 Using temporary，这三个指标都正常了，再测真实耗时才有意义。

最后还有一个我特别想强调的：现在 MySQL 8.0 的优化器已经相当成熟，同样的 SQL 在不同数据分布下执行计划可能完全不同，建完索引先跑一遍 EXPLAIN 验证，而不是靠嘴上说“肯定走索引了”。执行计划才是真正的事实依据。

5.3 索引相关常见问题速查表

最后把这些年经常被问到的索引相关问题整理成速查表，方便大家直接对号入座。为了节省篇幅，每个问题我只给结论和关键动作，原理前面章节都已经覆盖到了。

这张表不敢说覆盖所有场景，但应付绝大多数面试和实际排障已经够了。重点还是要强调最后一个动作——EXPLAIN 是行走的真相，凡是拿不准的 SQL，跑一遍看执行计划再下结论。

最后聊点个人体会。索引这个东西，单看理论很容易理解，但真正用好，靠的是“读场景”和“看执行计划”这两个习惯。我见过太多人把索引当银弹，恨不得给每一列都建索引，结果写入性能掉得厉害；也见过有人因为惧怕索引维护成本，建索引时缩手缩脚，一条 5 秒的慢 SQL 硬扛了半年。合理的做法永远是折中：先跑 EXPLAIN 确认瓶颈是不是在索引，再用覆盖索引、复合索引精确解决，最后通过压测验证写入侧的代价。能把这一步做好，MySQL 性能调优的基本盘就稳住了，这也是我对 MySQL 索引导航看法的核心落点。