企业官网建设流程全解析

做后端和数据库的同学，几乎没人能绕开MySQL索引这四个字。我最早被索引“教育”，是刚工作第一年：一张订单表，数据量才几十万行，一条简单查询没索引的时候跑了1.2秒，加了一个普通索引之后变成20毫秒，整整六十倍的差距。那一刻我才真正反应过来，SQL写得好不好，很多时候就取决于索引用得对不对。

这篇文章我打算按“全网最详细”的标准来写，把索引这件事从头到尾完整梳理一遍：从MySQL没有索引时怎么查数据，到索引底层为什么是B+树，到六类索引的区别与创建语法，到回表、覆盖索引、索引下推这些进阶概念，再到线上最常踩的索引失效坑和加索引的实操姿势。无论你是正在准备面试，还是被慢查询日志折磨得头疼，这篇都值得花十分钟静下心看完。

1. 从慢查询开始：索引到底替我们省了哪些成本

1.1 没有索引时，MySQL是怎么查数据的

想象一下，你有一张100万行的用户表，要查user_name = 'zhangsan'。如果表上没有任何索引，MySQL能做的就是把表从头到尾扫一遍，每一行都读出来，再判断user_name等不等于目标值。这个操作叫全表扫描（full table scan）。

全表扫描最大的问题不是CPU开销，而是磁盘IO。InnoDB的数据是按页存储的，默认每页16KB，100万行、每行按1KB算，就是100万页。扫描下来要读100万页，虽然Buffer Pool能缓存一部分热数据，但完全没索引时，这种读取很难命中预读，大量随机IO会让查询时间直接飙到秒级。

你可以在EXPLAIN里看到这种情况：type列显示ALL，rows列显示估算扫描的行数接近全表。ALL在MySQL的执行计划里是所有访问方式里最慢的，没有之一。看到ALL基本就意味着这条SQL该加索引了，除非表本身小到全表扫描也就几毫秒。

1.2 索引的本质：把“顺序查找”变成“树查找”

很多人觉得索引是个神秘的东西，其实它就是在表之外额外维护的一棵树，可以理解成书的目录。没有目录时，你想找某一章，得从第一页翻到最后一页；有目录时，你直接翻到对应页码就行，这就是索引存在的意义。

MySQL里的索引默认是B+树，数据按照某个或多个列的值排好序存在树里面。当查询条件命中索引时，MySQL只要在树里从根节点往下走，基本上三次磁盘IO就能定位到目标数据，这和全表扫描的百万级IO相比，完全不在一个量级。

所以索引的作用简单说就是：把查找一条数据的时间复杂度，从O(n)的全表扫描，降到O(log n)的树查找。表行数越多，这个差距越明显，这也是为什么几十万行的表加了索引能从秒级降到毫秒级。

1.3 索引不是免费的：空间、写入成本与维护成本

聊索引不能只说好处。索引本质上是用“额外的存储空间 + 写入开销”换“查询速度”。每一棵索引树都要占用磁盘空间，而且每次INSERT、UPDATE、DELETE，MySQL都要同步维护表上的所有索引树。

这意味着索引越多，写入就越慢，磁盘占用也越高。我见过不少新手把表里每个字段都加上索引，结果写入性能掉得厉害。正确的做法是只给高频查询、高频排序、高频关联的字段建索引，并且区分度太低的列（比如性别、状态枚举）不要单独建索引，因为查出来一大片，优化器很可能还是走全表扫描。

提示：索引是给查询服务的，不是给表“上保险”。加之前先问自己：有没有一条真实存在的慢SQL会用到它？

2. 索引的底层数据结构：为什么偏偏是B+树

2.1 如果只是“查得快”，哈希表不香吗

先处理一个常见的疑问：哈希表的查找复杂度是O(1)，比B+树的O(log n)还快，为什么MySQL默认不用哈希索引？

因为实际的业务查询几乎从来不只要“精确等值匹配”。user_name = 'zhangsan'这种等值查找，哈希索引确实很合适；但一旦查询变成user_name LIKE 'zhang%'、age > 18、ORDER BY create_time，哈希表就完全无能为力了。哈希是无序的，不支持范围查询，也不支持排序，更不能利用前缀去匹配。

InnoDB其实也有自适应哈希索引，但它是引擎内部基于热点数据自动构建的加速结构，不是我们手动创建的那个索引。平时能手动创建的哈希索引主要用在Memory引擎上，业务中很少直接用到。

2.2 红黑树、B树、B+树的取舍

接下来看看为什么B+树成了MySQL的默认选择。二叉搜索树和红黑树理论上也能做索引，但问题是树的高度。数据量一大，比如1000万行，平衡二叉树的高度会到20多层，意味着一次查询最多要访问20多个节点，每次都涉及磁盘IO，性能完全扛不住。

B树相比二叉树已经把树高降了很多，但B树的每个节点既存key又存数据，在16KB的页里能放下的分支数（也就是出度）有限。出度一低，树还是要变高。B+树则把数据全部放到叶子节点，非叶子节点只存key和指针，这样每个节点能容纳非常多的key，出度大大增加，树变得又矮又胖。

B+树还有一个杀手锏：叶子节点之间用双向指针串成了一个有序链表。范围查询、排序、分页都可以顺着链表顺序读取，这对数据库来说太友好了，B树就没有这个特性。

2.3 B+树的一笔账：三层能存多少行数据

我们来算一笔具体的账，看看B+树为什么能在3到4层内撑起千万级数据。

假设一行数据大小约1KB，InnoDB一个页16KB，那么叶子节点一页能存16行数据。非叶子节点存的不是数据行，而是“索引键 + 指向子节点的指针”。主键如果是BIGINT，占8字节，指针按6字节算，一条记录占14字节，那么一页能放大约1170个（16384 / 14 ≈ 1170）索引项。

这样一棵三层B+树：根节点有1170个分支，第二层有1170 * 1170个节点，第三层也就是叶子节点，总共有1170 * 1170 * 16 ≈ 2190万行。也就是说，一张2000万行的表，用BIGINT主键，InnoDB一般三到四次磁盘IO就能定位到目标数据。这个效率远超其他树结构，也是面试里特别喜欢考的一个计算点。

2.4 聚簇索引与非聚簇索引是怎么回事

在InnoDB里，索引不只是“辅助查找的结构”，它和数据存储方式深度绑定。InnoDB的表本身就是一个以主键为key的B+树，这棵树叫做聚簇索引（clustered index），它的叶子节点直接存放整行数据。换句话说，找到了主键，就是找到了这一行数据的物理存储位置。

除了主键之外，你创建的普通索引、联合索引都叫二级索引或非聚簇索引。二级索引的叶子节点不存整行，存的是“索引列的值 + 主键值”。所以当你用一个二级索引列查询时，流程是先到二级索引树里找到主键，再回到聚簇索引树里查出整行数据，这个动作就叫回表。

这里有一个很现实的主键选择问题：InnoDB要求表必须有聚簇索引，如果你没有显式定义主键，它会找一个非空唯一列做主键，实在没有就生成一个隐藏的rowid。隐藏rowid对业务完全不可控，所以建表时一定要主动设计主键。我更倾向于用自增整数或雪花ID，尽量避免用UUID做主键，因为UUID无序，插入时会频繁触发索引页分裂，产生大量碎片，写性能和空间利用率都会变差。

3. 索引分类与创建语法：别再只会建普通索引

3.1 六种索引一次性讲清楚

MySQL里的索引类型很容易把人绕晕，其实站在应用角度看，主要就是下面六种。我把它们的核心差异整理成了一张表，方便对照。

补充一个容易混淆的点：唯一索引和唯一约束其实是一回事。MySQL里建唯一约束就是创建唯一索引，两者没有本质区别，只是从“约束”和“索引”两种视角去理解而已。全文索引在InnoDB从5.6版本开始支持，但中文环境下需要考虑ngram分词，实际业务如果你要做全文搜索，我更推荐直接用专门的搜索引擎，比如Elasticsearch，而不是在MySQL里硬扛。

3.2 建索引的三种方式

很多同学问，建索引到底写哪种语法？其实三种方式都能达到目的，区别只在于使用时机。建表时就把索引定义好，可以保证从第一天起查询就是高效路径；表已经存在了，就用CREATE INDEX或ALTER TABLE追加。下面这段SQL把三种方式都覆盖了。

-- 方式一：建表时直接定义 CREATE TABLE user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(64) NOT NULL, email VARCHAR(128) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_email (email), -- 唯一索引/唯一约束 KEY idx_user_name (user_name) -- 普通索引 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 方式二：CREATE INDEX 追加 CREATE INDEX idx_user_name ON user_info(user_name); CREATE UNIQUE INDEX uk_email ON user_info(email); -- 方式三：ALTER TABLE 追加 ALTER TABLE user_info ADD INDEX idx_user_name (user_name); ALTER TABLE user_info ADD UNIQUE KEY uk_email (email);

查看和删除索引也有对应的命令。看索引信息会用SHOW INDEX FROM user_info，这个命令会列出索引的字段顺序、唯一性、基数等信息；删除索引就是DROP INDEX或ALTER TABLE ... DROP INDEX。

SHOW INDEX FROM user_info; DROP INDEX idx_user_name ON user_info;

另外特别建议做个索引命名规范：普通索引用idx_前缀，唯一索引用uk_前缀，后面接字段名，比如idx_user_name。线上索引一多，没有命名规范的人肉排查会非常痛苦。

3.3 联合索引：理解最左前缀原则

建联合索引时，字段顺序真的不是随便排的。考虑一个查询：WHERE status = 1 AND create_time > '2024-01-01'。如果status区分度低，create_time区分度高，很多人会纠结该建(status, create_time)还是(create_time, status)，这两个组合在B+树里的排序逻辑完全不同。

比如建立联合索引(a, b, c)，底层会先按a排序，a相同再按b排序，b相同再按c排序。这个排序规则决定了它只能用于匹配(a)、(a,b)、(a,b,c)这几种查询条件。如果你直接查WHERE b = 1 AND c = 2，因为跳过了a，索引无法按b定位，只能退化成扫描。最左前缀原则说的就是这个。

前缀的“最左”指的是查询条件里必须包含索引最左边的列，并且不能跳过中间的列。实际上，范围查询还会进一步影响后续列的使用：比如WHERE a = 1 AND b > 10 AND c = 2，联合索引只能把a和b用于定位，c那列因为中间隔着范围条件b，只能在b确定的范围内做过滤，不能再用精确定位。

这里再提醒一句：不要把最左前缀理解为“必须把第一个字段写在SQL最左边”。优化器会调整where条件的先后顺序，真正的关键是查询里有没有用到这个列。比如WHERE b = 2 AND a = 1，只要a参与等值匹配，索引照样能用，前面写的顺序不影响执行计划。

4. 回表、覆盖索引与索引下推：EXPLAIN里最常见的三件事

4.1 回表为什么会影响性能

前面说过，二级索引只存主键，不存整行数据。如果你执行SELECT * FROM user_info WHERE user_name = 'zhangsan'，MySQL会先到idx_user_name这棵二级索引树里找到zhangsan对应的主键id，再拿这个id回到聚簇索引树里查出完整行。第二次这个动作就是回表。

回表不是不行，而是每次回表都意味着一次额外的随机IO。如果一条SQL通过索引过滤出几百条记录，就要回表几百次，性能自然下降。真正要避免的是“大批量回表”：比如数据量大、筛选结果集大、而且查询列还不在索引里的场景。

我曾经优化过一条线上慢SQL，条件命中了索引，但SELECT带了十几个字段，结果回表几百次，耗时始终降不下来。后来把查询要的字段全部收进联合索引，做成覆盖索引，耗时直接从400毫秒降到20毫秒。这就是回表成本最真实的案例。

4.2 覆盖索引：让需要查询的字段直接长在索引树上

覆盖索引是最实用的优化手段之一。只要索引包含了查询需要的所有列，MySQL就不需要回表，直接从索引树里拿数据。比如：

SELECT id, user_name FROM user_info WHERE user_name = 'zhangsan';

如果表上有联合索引(user_name, id)，或者user_name普通索引（InnoDB二级索引天然包含主键id），那么这条SQL要的数据id和user_name都在索引树的叶子节点里，不需要回表。EXPLAIN里Extra列会显示Using index，这就是覆盖索引的标志。

日常优化慢查询时，我经常用这个思路：先看SELECT要哪些列，再把这些列和WHERE条件里的列一起设计成联合索引。但要注意覆盖索引不是越多越好，索引列越多，占用的存储空间和写入成本就越大。通常只对线上高频且关键的SQL做覆盖优化，不能所有SELECT都无脑堆列。

4.3 索引下推：MySQL 5.6之后的隐藏福利

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6引入的优化，很多老开发都不知道。它的作用是：在存储引擎层，直接对索引中包含的字段进行条件过滤，减少回表次数。

举个例子，假设有联合索引(age, city)，执行：

SELECT * FROM user_info WHERE age > 18 AND city = '杭州';

如果没有ICP，InnoDB先用age > 18从索引里取出一批主键，然后全部回到聚簇索引找到整行，再由server层去判断city是不是杭州。如果有ICP，InnoDB会在索引遍历过程中，直接利用索引里的city字段筛掉不满足条件的记录，只对剩余记录回表。在筛选率很高的情况下，收益非常明显。

对应的EXPLAIN Extra列会显示Using index condition。注意ICP并不是万能药，它只对索引里已经包含的字段生效；如果city不在索引里，还是得回表之后才能判断。

5. 索引失效的典型场景与排查技巧

5.1 一张速查表看全失效场景

索引失效是面试高频题，也是线上慢查询高发区。我先放一张速查表，把常见场景、原因和应对思路一次讲清楚。

需要说明的是，失效场景不全是绝对化规则。IS NOT NULL、!=这些能不能走索引，取决于优化器对数据分布和成本的估算，不同版本、不同数据量下行为可能不一样。所以我的习惯是：先用这张表做初筛，最终判断一律以EXPLAIN结果为准。

5.2 两个线上最容易踩的坑

第一个坑是隐式类型转换。最经典的是phone字段定义成varchar，查询时写成WHERE phone = 13800138000，漏了单引号。MySQL在比较字符串列和数字时，会把字符串转换成数字再比较，相当于在索引列上偷偷做了一次类型转换，索引自然失效。这个坑特别隐蔽，因为单独执行看起来没有任何报错，但执行计划已经变了，慢就慢在查询计划上。

第二个坑是字符集不一致。比如订单表是utf8mb4，历史老表是utf8，用user_id关联的时候，MySQL必须先把一方的字符串转换成另一方字符集才能比较，这也会让索引失效或无法高效使用。尤其是join场景，一旦驱动表和被驱动表的关联字段字符集不一致，明明两边都有索引，执行计划却可能提示Using join buffer。线上加新表的时候，一定要检查关联字段的字符集和排序规则是否一致。

5.3 三分钟快速定位：EXPLAIN看哪几列

排查索引问题一定会用到EXPLAIN。我拿到一条慢SQL，会重点看四列：type、key、rows、Extra。

• type：访问类型。性能从好到差大致是 system > const > eq_ref > ref > range > index > ALL。看到ALL基本就是全表扫描；index虽然扫了整棵索引树，也比ALL好一些。
• key：实际用到的索引。如果为NULL，说明这条SQL没有命中索引。
• rows：预估扫描行数。这只是估值，但越小越好。
• Extra：如果出现Using filesort、Using temporary，通常意味着排序或去重没能用上索引；如果出现Using index，说明走了覆盖索引；出现Using index condition，说明用了索引下推。

实操命令很简单：

EXPLAIN SELECT * FROM user_info WHERE user_name = 'zhangsan';

如果需要看更详细的代价信息，可以加上FORMAT=JSON：

EXPLAIN FORMAT=JSON SELECT * FROM user_info WHERE user_name = 'zhangsan';

JSON输出里能看到read_cost、eval_cost这些代价估算，在排查优化器为什么不选某个索引时很有用。

6. 线上建索引的实操套路与避坑指南

6.1 建索引之前，先看这三个问题

第一个问题是区分度。区分度约等于count(distinct 列) / count(*)，比如性别字段可能只有0.5，基本没有建索引的价值；而user_name、email这种可以到0.99以上。想要快速算区分度，跑这条SQL就可以：

SELECT COUNT(DISTINCT user_name) / COUNT(*) FROM user_info;

第二个问题是查询频率。是否真的有一条线上SQL会因为缺这个索引而变慢？如果只是“我觉得以后可能有用”，那就先别建。索引越少，写入越快，维护成本越低，以后排查问题也越省心。

第三个问题是写入放大。索引每多一个，每次写入都要多维护一棵树。如果你的业务写多读少，加索引更要谨慎；读多写少则相对可以放开一些。这个判断没有绝对标准，但写多读少的表加了一圈索引，写入性能下降往往比想象中明显。

6.2 大表加索引的正确姿势

大表直接执行ALTER TABLE ADD INDEX，在MySQL 8.0里默认走INPLACE算法，可以并发DML，但整个变更过程依然不是完全没有代价：重做日志会增长，主从复制会有延迟，而且DDL语句在拿元数据锁（MDL）时还可能阻塞后到的查询。所以“直接执行”只适合中小表。

如果数据量是几百万上千万行，更稳妥的方式是用专业的在线表结构变更工具，比如pt-online-schema-change或gh-ost。这类工具会用触发器或者binlog解析的方式，把新索引同步创建到一张临时表，期间业务读写不受影响，最后通过原子换表完成切换。我自己在千万级表上做过多次，整体思路都一样：

• 选择业务低峰期执行；
• 先确认磁盘空间充足，因为工具会复制一份表结构；
• 执行过程中持续监控主从延迟，延迟过大就暂停或限速；
• 执行前准备好回滚方案，变更完成后观察一段时间再收工。

注意：任何时候都不要在生产环境直接对超大表执行长时间阻塞的DDL，除非你确认影响可控、有完整预案。

6.3 用慢查询日志反推索引需求

线上的索引需求不是拍脑袋想出来的，最靠谱的来源是慢查询日志。MySQL的慢查询日志可以这样开启：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';

开启后，凡是执行时间超过1秒或没用到索引的SQL都会记入慢日志。然后用mysqldumpslow快速汇总：

mysqldumpslow -s t -t 10 /var/lib/mysql/*-slow.log

这个命令会按执行时间排序，把最耗时的SQL排在最前面。拿到SQL之后，再配合EXPLAIN和上面说的索引失效速查表，判断是该加索引、改SQL，还是调整已有联合索引的字段顺序。慢日志是索引调优的“需求池”，先看需求再做施工，才不会做出没人用的索引。

最后分享一个我自己的习惯：每次新需求上线前，我会把SQL里的WHERE条件、ORDER BY、GROUP BY字段全部摘出来，按“等值条件在前、范围条件在后、区分度高的列靠左”的方式设计联合索引，然后开一条EXPLAIN确认type不是ALL、Extra里没有Using filesort才肯合代码。这套流程不复杂，但真的帮我挡掉了好多线上慢查询。索引这东西，说到底是给优化器铺路，你铺得越规整，它跑得就越听话。