企业官网建设流程全解析

手头数据库越来越慢，翻遍日志发现上百条慢查询，第一反应是加内存、扩CPU、换SSD，但我做了这么多年MySQL性能调优，最想先动手的永远是索引。MySQL是后端开发最常用的关系型数据库，线上问题里有相当大的比例都能追溯到索引设计不合理，而索引优化恰恰是投入产出比最高的手段：改一条SQL可能只省几十毫秒，但一个合理的联合索引能救活整个模块。

这篇内容从索引的底层原理讲起，逐步过渡到索引类型怎么选、联合索引怎么搭、EXPLAIN怎么读、索引失效怎么避坑，最后聊聊索引和排序、锁、事务之间的联动。覆盖了MySQL创建索引、mysql性能调优、mysql锁的分类这些常被搜索的高频话题。适合所有跟MySQL打交道的开发者和运维同学，不管你是刚入门想补基础，还是工作几年想查漏补缺，这里面都有可以直接抄作业的内容。

1. 先搞懂索引为什么能提效：全表扫描的账算给你看

1.1 一张千万行表的全表扫描成本

我见过不少项目，表刚上线时只有几万行，性能一点问题没有；等数据涨到几百万行，突然就崩了。这不是程序写得不对，而是MySQL的查询方式被迫发生了转变。没有索引时，InnoDB只能做全表扫描——把聚簇索引的叶子节点从头到尾读一遍，每一行都过一遍WHERE条件，命中的留下，不命中的丢弃。

光讲概念不好理解，我替大家算一笔账。假设订单表order_info有800万行，单行数据不算大，按1KB估算，主键索引的叶子节点总共就是8GB左右。InnoDB默认页大小是16KB，那就是51万多个数据页。老机械硬盘顺序读算150MB/s，全表扫一遍要55秒；就算换成企业级SSD，1GB/s的顺序读，也要8秒。更要命的是你业务高峰期不止一个查询，每个都这么读，磁盘IO和Buffer Pool都会被拖垮。

索引存在的意义就八个字：减少扫描的数据量。它把“我不知道数据在哪，只能全翻一遍”变成“我知道数据大致在哪，直接去那一小片区域取”。查询优化的第一原则永远是先想怎么减少扫描行数，索引是最直接、性价比最高的实现方式。

1.2 为什么偏偏是B+树而不是B树或哈希

很多人一看B+树就头疼，其实只需要抓住它的三个关键特性：矮、叶子节点才存数据、叶子节点之间有序相连。

• 矮意味着层数少，层数少意味着查询只需少数几次磁盘IO。B+树的非叶子节点只存键值和子节点指针，不存整行数据，所以一个16KB的页能塞下非常多的键值。按主键BIGINT（8字节）+指针（6字节）算，一个页约能装1170个键值。两层就是1170x1170约137万，三层就是16亿。也就是说，一张上亿行的表，普通查询最多三次磁盘IO就能定位到叶子节点。这是几乎所有数据库都选B+树的核心原因。

• B树和B+树最大的差异在于数据存放位置。B树几乎每个节点都存数据，树很容易变胖变高；B+树把数据全部收敛到叶子层，内部节点只做索引。这直接降低了树高，同时带来一个额外福利：叶子节点之间用双向链表连接，范围查询顺着链表一路读就行，不用回根节点重新走。数据库里最频繁的等值查询和范围查询，B+树一张结构全照顾到了。

哈希索引适合单点等值，查询确实能做到O(1)，但完全不支持范围查询，也没有顺序性。你可以把哈希索引想象成一本只有目录、没有页码的书，找某个词很快，但想“从第100页开始顺序看到第200页”就抓瞎了。所以InnoDB的索引主结构始终是B+树，哈希只以自适应哈希索引的形式，在极少数的辅助场景出现。

提示：InnoDB页大小默认是16KB，从5.7到8.0都没变，很多参数调优都是围绕这个“页”做文章。理解页，才是理解索引的起点。

2. 索引类型选型：主键、唯一、普通、全文索引怎么取舍

2.1 四种索引一张表说清楚

建索引之前，很多人从来没认真想过该建什么类型的索引。先放个对照表。

主键索引在InnoDB里不是“一个索引”，而是表本身。因为InnoDB是聚簇索引组织表，表数据就存在主键索引的叶子节点上。这就是为什么InnoDB要求每张表必须有主键——如果没有显式主键，它会悄悄找一个非空的唯一列当主键，再找不到就生成一个隐藏的ROW_ID。这个隐藏主键是全局自增的，在高并发下会影响写入性能，但用户完全感知不到。所以建表时老老实实给一个自增BIGINT主键，是最省心的习惯。

2.2 聚簇索引与二级索引的回表代价

很多新手拿到“回表”这个词很蒙，其实一句话就能解释：聚簇索引的叶子节点是整行数据，二级索引的叶子节点只存索引列的值和主键值。当你通过二级索引找到主键，还要再用主键去聚簇索引里查一次完整行，这个动作就叫回表。

打个比方：聚簇索引像一本正文排版完整的书，二级索引像书末的“关键词-页码”索引表。你查“索引优化”这个词，先在索引表里找到页码，再翻到对应页读正文——翻页这一下就是回表。如果正文里那些页恰好把你要的内容都附在索引表里了，那就连正文都不用翻，这就是前面提过的覆盖索引，后面细讲。

回表本身不是洪水猛兽，但量大了就是灾难。假设WHERE条件命中5万行，二级索引扫描很快，但每条都要回表查一次聚簇索引，5万次随机IO可能比全表扫描还慢。优化器很聪明，它如果发现二级索引过滤出的行数占比太高，会干脆放弃索引选择全表扫描。行数占比的阈值并没有固定值，跟表大小、统计信息、Buffer Pool命中率都有关系，唯一确定的是：回表次数越少越好。

3. 索引设计实战：列怎么选，联合索引怎么搭

3.1 区分度是索引列的第一筛选标准

没有实践经验的人建索引，容易犯一个毛病：看到WHERE后面跟了什么字段，就单独给什么字段建索引。结果索引建了一堆，慢查询一个没少。

判断一个列适不适合做索引，核心指标是区分度。区分度 = 该列不同值的数量 / 表总行数。区分度越接近1，索引选择性越好。你可以用一条SQL算出来：

SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

比如性别列只有两个值，区分度是2/100万，无限趋近于0，建索引几乎没有任何效果——因为无论查哪个性别，都要扫掉一半的行，优化器大概率还是全表扫描。而订单号、手机号这类高区分度字段，单独建索引就能直接命中极少数行。日志表里常有的status字段，状态只有三五种，但业务里90%的查询都按status过滤，这时候要不要建索引？可以建，但真正的优化是再组合一个高区分度字段做联合索引，而不是单建status索引。

我见过一张用户表同时有7个单列索引，全部独立，SQL里却经常两三个条件联合过滤。MySQL 8.0之前没有索引合并优化时，这种设计就是一个坑——优化器只能勉强选一个走，其余条件全部回表过滤。8.0里虽然Index Merge能勉强救一下，但效果远不如一个联合索引。

3.2 联合索引的最左前缀法则

联合索引是索引优化里最值钱的部分，也是最容易出问题的地方。它的底层不是“多列各建一个索引”，而是把所有索引列拼成一个复合键，按从左到右的顺序排序存储。所以查询必须从头匹配，跳过第一列直接用第二列，索引就用不上。

这就是最左前缀法则。比如联合索引(a, b, c)，能生效的查询条件组合有：a、a+b、a+b+c、a+b或a+c（命中a和c，但b的过滤条件无法走索引，只能回表过滤），而单独的b、单独的b+c、单独的a+c如果没带a，都无法走完整索引。

实操上最重要的原则是：把最常用、区分度最高的列放最左。假设订单查询通常带上user_id、order_status、create_time，那联合索引就应该从user_id开始构建，因为这是每次查询都带的条件。如果你把create_time放最左，但很多查询压根不筛时间，这个索引就废在第一个字段上了。

3.3 覆盖索引：查询的免费午餐

覆盖索引的意思是：要查的所有列都在索引里，不需要回表。这是所有索引优化里最想达到的理想状态。

最常见的优化手法就是“索引列+SELECT列”一起进联合索引。比如高频SQL是查订单的金额和状态：

SELECT order_amount, order_status FROM order_info WHERE user_id = 123;

如果你只建了user_id单列索引，查询流程是：扫二级索引找到user_id=123的所有主键，然后每条回表查order_amount和order_status。但如果建联合索引(user_id, order_amount, order_status)，二级索引的叶子节点上已经包含这三个字段，查询直接扫描索引返回，一次回表都不用。

实践中，覆盖索引不是万能的。索引列越多，写入成本和存储空间越大，尤其是VARCHAR、TEXT这类字段，尽量别往里塞。黄金法则是：覆盖高频查询、少覆盖低频查询、坚决不覆盖超大字段。

注意：MySQL 8.0开始，索引列支持隐藏功能和降序索引。降序索引（如ORDER BY a DESC, b ASC）能直接避免文件排序，但要注意它并不是免费的，写入性能会有一定牺牲。

4. 用EXPLAIN让执行计划说实话：四张表抄作业

4.1 关键字段逐个拆解

建完索引，别急着走，用EXPLAIN验证是最基本动作。EXPLAIN不会真正执行SQL，只是让优化器输出一份执行计划。关键字段就几个：

type是最直观的体检报告。const和eq_ref说明MySQL定位到唯一一行，是最优的；ref和range说明走索引做了范围扫描，正常水平；index看着像用了索引，实际上是全索引扫描，通常也比全表强不了多少；ALL就是全表扫描，该立刻优化。

我见过不少人只看key字段非NULL就认为索引生效了，这是最大误区。key有值不代表“好用”，如果type是ALL或者rows特别大，这个索引即使被采用，效率也不一定高。Extra里的Using filesort尤其要警惕——这意味着排序没走索引，MySQL要在内存或磁盘里临时排一遍。大结果集的filesort会直接吃掉大量CPU和临时空间。

4.2 两个真实案例改造

第一个案例：订单列表页按用户查最近订单，SQL是：

SELECT * FROM order_info WHERE user_id = 100 ORDER BY create_time DESC LIMIT 20;

改造前的执行计划是：type=ref，key=idx_user_id，rows=84200，Extra=Using filesort。用户订单多，排序丢给了文件排序，页面上每次都等很久。

改造方案是建联合索引(user_id, create_time)。第一个字段user_id支撑WHERE过滤，第二个字段create_time支撑ORDER BY排序。改造后Extra变成空，type仍然是ref，但rows下降到几十行，offset的排序也直接在索引上完成。这个改动就是普通索引变成联合索引，连SQL都不用改。

第二个案例：分页很深时，比如LIMIT 99990, 10，MySQL会先读出前10万条，再丢掉前99990条，只回最后10条。这是全表扫描+文件排序的典型组合。

优化思路是延迟关联：先查出目标主键，再原表关联取完整行。

SELECT t.* FROM order_info t INNER JOIN (SELECT id FROM order_info WHERE user_id = 100 ORDER BY create_time DESC LIMIT 99990, 10) tmp ON t.id = tmp.id;

子查询里只扫二级索引列，不用回表拿大字段，MySQL能走覆盖索引完成排序过滤，极大减少回表量。这个技巧在做翻页场景时，实测能把原本1.8s的查询压到0.2s以内，效果相当震撼。

5. 索引失效高发场景：七个坑一次避开

5.1 哪些写法会让索引白建

索引失效是最头疼的问题，坏就坏在SQL看起来很正常，执行计划却告诉你索引没走。我整理了七个高频场景。

1. 对索引列做函数操作。WHERE YEAR(create_time) = 2024 或者 WHERE DATE(create_time) = '2024-01-01'，优化器无法直接用索引定位区间，因为它要先对每一行算函数结果。改成范围条件 create_time >= '2024-01-01' AND create_time < '2024-01-02' 立刻就能走索引。

2. 隐式类型转换。索引列是VARCHAR，查询条件写成WHERE phone = 13812345678（不带引号），MySQL要把phone字段转成数字去比较，索引就废了。规则是：字段是什么类型，条件就写什么类型，别让数据库做隐式转换。

3. LIKE前置通配符。WHERE name LIKE '%张%' 无法用索引，因为B+树是根据前缀排序的，MySQL不知道%前的锚点在哪。解决思路：能改成范围就改范围，改不了就上全文索引或者搜索引擎。

4. 联合索引跳列。前面说过，(a,b,c)索引查询条件是a和c，中间的b没带，只能走a的索引，c的过滤被迫回表。

5. 负向查询。WHERE status != 1 或 WHERE status NOT IN (1,2)。B+树索引最适合等值匹配，负向条件无法定位区间，通常只能全索引扫描。

6. OR连接条件。WHERE a = 1 OR b = 2，如果a和b各建单列索引，MySQL 8.0之前不会做索引合并。优化方向是用UNION ALL拆开，或者改成IN。

7. 对索引列做运算。WHERE age + 1 = 30，把列放在表达式里，索引就用不上。运算应该在等号另一端完成。

5.2 索引下推ICP：被低估的8.0优化

MySQL 5.6以后有个特性叫索引条件下推，英文Index Condition Pushdown。它说的是：当你使用联合索引查询，二级索引的叶子节点上有多个字段，MySQL可以把WHERE里的部分过滤条件下推到存储引擎层，在索引扫描阶段就过滤掉不符合条件的记录，减少回表次数。

举个例子，联合索引(name, age)，查询WHERE name LIKE '张%' AND age = 28。没有ICP时，MySQL通过name前缀把命中的主键全部捞出来，再一条条回表，然后过滤age。有ICP时，age=28的判断直接在二级索引扫描阶段完成，只有同时满足name前缀和age条件的行才回表。

这个特性默认开启，你可以在EXPLAIN的Extra里看到Using index condition字样。但要注意：ICP只能帮你在索引内部过滤，不是真正的覆盖索引。最彻底的办法依然是让所有过滤列都进索引。

6. 排序、Limit与索引的三方协作

6.1 filesort到底有多贵

ORDER BY没有走索引时，MySQL会把结果集放进排序缓冲区（sort_buffer），排完再返回。结果集小还好，一旦超出sort_buffer_size，就会在磁盘上创建临时文件做归并排序。这些操作全是CPU和磁盘开销，还阻塞整个查询。

排序走索引是有条件的。最简单的是ORDER BY和WHERE条件共用同一个联合索引，并且顺序一致。前面举过(user_id, create_time)的例子，就是让排序字段紧跟过滤字段进入联合索引。另外，MySQL 8.0支持降序索引，ORDER BY create_time DESC也能直接反向扫描索引完成。

排序还有一个坑是：ORDER BY字段顺序必须和索引列顺序完全一致，只要顺序颠倒，优化器就弃用索引。工作里我把这种问题统称为“索引顺序强迫症”，设计联合索引时必须把排序字段的升降序也考虑进去。

6.2 LIMIT深分页的黄金解法

LIMIT 100000, 20 这种深度分页是很多后台管理系统的噩梦。问题核心在于MySQL必须扫描并丢弃前10万行，而不是直接从第100001行开始读。B+树没有“跳到第N行”的接口，只能顺着链表一路读。

最实用的两个方案，第一个是延迟关联，上一节已经演示过，本质是让子查询只扫索引列、利用覆盖索引定位主键，再回原表关联取完整行。第二个是游标分页：业务上把“上一页最后一条记录的排序字段值”传进来，用WHERE create_time < 上一页最后时间 ORDER BY create_time DESC LIMIT 20 来取下一页。数据量越大，游标分页的优势越明显，但需要业务改造，有些团队接受不了这种改动。

我自己做管理后台的时候，前10页用传统分页没压力，但用户点“跳转到第5000页”的场景，直接给延迟关联方案，基本能保证100ms级别返回。

7. 索引与锁、事务的高级联动

7.1 MySQL锁的分类与加锁范围

MySQL锁的分类是个高频面试题，也是优化时的隐蔽陷阱。简单分三类。按粒度：表级锁（MyISAM）、行级锁（InnoDB）、页级锁。按类型：共享锁S、排他锁X。按算法：记录锁Record Lock、间隙锁Gap Lock、临键锁Next-Key Lock。

InnoDB默认在REPEATABLE READ隔离级别下使用临键锁，即“记录锁+间隙锁”的组合。它锁的不仅是被命中的记录，还包含记录之前的一段“间隙”。这个设计解决了幻读问题，但也带来一个副作用：范围条件越大，锁住的间隙越多，并发度越低。

高并发写入场景最怕的就是“锁范围失控”。比如WHERE收到一堆等于某个user_id的UPDATE，如果user_id没有索引，InnoDB为了安全会加上锁，甚至锁掉全表间隙，把其他用户的写入全部卡住。所以更新语句的WHERE条件永远是重点检查对象：确认它走了索引，锁的范围才会收敛。

7.2 二级索引更新时的锁顺序问题

这条很多人没注意过，但实际踩坑的人不少。当UPDATE通过二级索引定位记录时，InnoDB的加锁顺序是先锁二级索引项，再回表锁主键记录。与此同时，另一条通过不同二级索引更新的并发事务，可能也按自己的顺序去锁二级索引项和主键。

两个事务如果各自的加锁顺序交叉，比如事务A先锁索引1再锁主键，事务B先锁主键再锁索引1，就会形成死锁。死锁的直接报错是“Deadlock found when trying to get lock”，很常见。

规避思路有几个。第一，尽量减少一条事务里对多条记录的UPDATE，大批量更新拆成小批次。第二，让所有更新语句都按照主键顺序访问行，比如WHERE id IN (...) 里排好序，加锁顺序就从根上统一了。第三，监控死锁日志show engine innodb status，看锁等待链，定位到底哪个索引的加锁顺序和别人冲突。

提示：用事务处理大批量数据时，我习惯把一条大的UPDATE拆成每批500条的小事务。既避免长事务锁持太久，也降低死锁概率。长事务不仅锁多，还会拖大Undo日志，影响MVCC快照读。

8. 慢查询监控与整体调优流程

8.1 打开慢查询日志，让问题自己跳出来

所有的索引优化第一步都应该先做监控，而不是凭感觉建索引。MySQL自带慢查询日志，配置一下就能把所有执行时间超过阈值的SQL记录到文件里。

-- 查看当前设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 动态开启（重启MySQL前有效） SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;

long_query_time建议从1秒起步。千万别直接设成0，不然日志会爆炸，全是些几十毫秒的正常查询。开启log_queries_not_using_indexes后，即使查询不慢，只要没走索引，也会进日志，这对发现扫全表的老代码特别有用。

拿到慢查询日志后，用mysqldumpslow分析TOP SQL，也可以把日志文件交给pt-query-digest这类工具做聚合分析。核心就是找：执行次数多、单次耗时长、总耗时占比高的三类SQL。这三个指标分别指向不同类型的问题，执行次数多说明业务逻辑高频依赖它，单次耗时长说明当前执行计划有问题，总耗时长说明它在服务器上造成了真正的压力。

8.2 整套优化流程的固定步骤

做多了以后，我的索引优化流程就固定在六个步骤。

第一步，收集慢日志，按次数、耗时、总耗时排序。第二步，对每条TOP SQL执行EXPLAIN，记录type、rows、Extra。第三步，分析WHERE、ORDER BY、JOIN字段，列出候选索引列。第四步，用区分度SQL过滤掉低区分度字段，组合成联合索引。第五步，EXPLAIN验证，看type是否提升、rows是否下降、Extra还有没有filesort。第六步，观察一段时间慢日志，确认TOP SQL没有重复出现。

这个流程看起来简单，但每一步都有一个隐藏难点：统计信息可能是旧的。MySQL索引优化器依赖表的统计信息做决策，如果统计信息长时间没更新，执行计划会非常离谱。跑业务高峰期前，可以执行ANALYZE TABLE 让统计信息新鲜一点，再回归验证执行计划。

8.3 索引优化的三个“别做”

最后分享几条经验级的避坑，这些不是技术原理，是我在线上踩出来的。

别过度建索引。一张表十几个索引，写入性能全被拖垮。二级索引每多一个，INSERT和UPDATE都要同步维护一颗B+树，高并发写入下代价非常明显。我见过最夸张的一张表23个索引，结果写入TPS只有改造前的三分之一。

别删索引太猛。删索引之前一定要看历史慢日志和监控系统，确认这个索引确实没有高频查询依赖。有些索引是给后台报表用的，白天流量低看不出来，一删报表就跑不动。

别忽略复合场景。查询优化别只盯着单列，WHERE、ORDER BY、GROUP BY、JOIN的多个字段往往可以合并进同一个联合索引。我的经验是先合并后考虑拆分，别一上来就每个字段建一个单列索引。

我心里一直有一条个人准则：每次看到全表扫描的执行计划，都当成一次“免费的检查机会”——要么是业务查询方式有问题，要么是表数据量超过了当初的设计预期。处理慢查询时不急着加索引，先花两分钟看索引设计、统计信息和数据增长趋势，往往能发现更本质的问题。索引不是越多越厉害，而是每一棵索引树都必须有它存在的理由，这条经验希望你能带走。