企业官网建设流程全解析

1. 先搞清楚索引到底是什么——从一次慢查询事故说起

先说一次我自己的经历。有年做电商后台，订单表数据到了3000万级别，一个按用户查订单的接口突然从100毫秒变成了3秒多，监控直接报警。当时第一反应是"SQL有问题"，结果把SQL看了三遍也没看出毛病，就是简单的SELECT * FROM orders WHERE user_id = 12345。后来一查执行计划，type是ALL，全表扫描，因为建表的时候压根没给user_id加索引。

这个事给我的教训很深：写SQL之前先想想有没有索引，慢查询的第一排查对象就是"扫没扫全表"。所以这篇就把MySQL索引相关的核心知识点、面试高频题和实战优化经验一次性讲透，不啰嗦理论，全是干活能用的。

索引在我的理解里，就是数据库的目录。你在新华字典里查"索引"两个字，肯定不会从第一页翻到最后一页，你会先翻到拼音索引，找到这个字在第几页，然后直接翻过去。MySQL的索引干的就是同一件事，它用B+树这种数据结构，把字段值和行记录的位置建立映射关系，查询时直接走这个目录定位，而不是从头到尾把3000万行全扫一遍。

但目录也有目录的代价。每加一个索引，写入数据时就要多维护一棵树，磁盘占用也增加。我见过有人为了让每个字段都"走索引"，一张表建了20多个索引，结果写入性能掉了近一半。索引不是越多越好，用对了是加速器，用错了就是累赘。

对于初学者，我的建议是先把索引的运行机制搞明白，再动手建。别一上来就问"怎么建索引"，得先懂"为什么它能快"。

2. 索引的底层数据结构——为什么MySQL偏偏选了B+树

2.1 从二叉树到B+树的演进逻辑

很多人面试被问"为什么索引用B+树不用二叉树"，回答停留在"因为B+树矮"，这个答案太浅了。你得把思路理清楚。

如果数据库索引用普通二叉树，数据量大了之后树会退化成链表，查询复杂度从O(log n)跌到O(n)，这跟全表扫描有什么区别？用红黑树虽然能自平衡，但树的高度依然很高。我算过一笔账，现在InnoDB一个页是16KB，假设一个索引节点存储10行数据，一个三层的B+树能存多少行？

粗略估算：第一层根节点16KB能存几百上千个键值（每个键值加指针大概20字节左右，16KB能放800个左右），第二层每个节点也能存800个，第三层每个叶子节点能存10到15行数据。那么一个三层B+树大概能存储800×800×12≈768万行。树的高度每增加一层，存储量就指数级增长。

这个就是B+树的底气：同样的数据量，二叉树可能要20层，B+树三四层就搞定了。树矮意味着磁盘I/O次数少，MySQL查询的快慢很大程度取决于I/O次数。

2.2 为什么是B+树而不是B树

• B树每个节点都存数据，查询时可能在中途就命中，且范围查询需要中序遍历，效率低
• B+树只有叶子节点存数据，非叶子节点只存键值，同样大小的节点能容纳更多键值，树更矮更宽
• B+树叶子节点通过双向链表连接，范围查询直接顺着链表往后读就行
• B+树查询效率极其稳定，每次都必须到叶子节点才拿到数据，所有查询的I/O次数一致

我在实际用的时候，最直观的感受就是范围查询。比如查WHERE age BETWEEN 20 AND 30，B+树先在索引树里定位到20，然后顺着叶子节点的链表一路往后读，不需要回根节点重新找路径。B树要做到这个操作，得在中序遍历里反复跳转，慢多了。

2.3 聚簇索引与非聚簇索引——InnoDB的特殊之处

InnoDB的索引逻辑跟MyISAM有个本质区别：InnoDB的每个表只有一个聚簇索引，表数据本身就是按主键索引的B+树组织的。这是什么意思？就是你建了主键，InnoDB会直接拿主键当索引树的key，叶子节点上存的就是整行数据，不需要再去磁盘找数据文件。

所以在InnoDB里：

• 如果你建了主键，主键索引就是聚簇索引，数据直接挂在主键的B+树叶子节点上
• 如果你没建主键，MySQL会找一个非空的唯一索引当主键
• 如果连唯一索引都没有，InnoDB会生成一个隐藏的6字节rowid当主键

这个机制带来的一个实际影响：二级索引（辅助索引）的叶子节点存的是主键值，不是行地址。所以你用普通索引查询时，先通过索引树的叶子节点找到主键值，再用主键去主键索引树里查一次，这叫回表。

举个例子，表里有user_id和name两个字段，主键是user_id，你对name建了索引。执行SELECT * FROM user WHERE name = '张三'时，MySQL先去name索引树里找到'张三'对应的主键值user_id=1001，再用1001去主键索引树查完整行，一共查了两棵树。第一次是查询索引，第二次是回表取数据。

这个机制也解释了一个常见现象：为什么InnoDB表一定要建主键，而且建了主键别经常改。因为所有二级索引都依赖主键值，主键值越大、越长，二级索引占的空间就越多，回表速度也相对慢一些。

3. 索引的分类和创建方式——手把手带你建索引

3.1 从主键索引到全文索引，一张表格看全

MySQL索引在InnoDB下常见的有这几种类型：

3.2 建索引的三个常见方式

建索引的语法不难，但我推荐在执行前先想清楚用途。直接用代码演示一下：

-- 方式一：建表时直接定义索引 CREATE TABLE `user` ( `id` INT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(50) DEFAULT NULL, `id_card` VARCHAR(18) DEFAULT NULL, `age` INT DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_id_card` (`id_card`), KEY `idx_user_name` (`user_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 方式二：ALTER TABLE 追加索引 ALTER TABLE `user` ADD INDEX `idx_age` (`age`); ALTER TABLE `user` ADD UNIQUE KEY `uk_id_card` (`id_card`); -- 方式三：CREATE INDEX 追加索引 CREATE INDEX `idx_user_name` ON `user` (`user_name`); CREATE UNIQUE INDEX `uk_id_card` ON `user` (`id_card`);

提示：删除索引用DROP INDEX idx_name ON table_name，或者ALTER TABLE table_name DROP INDEX idx_name。建议加索引尽量用ALTER TABLE，因为它在DDL中能和表结构的其他修改保持一致。

3.3 联合索引的设计——字段顺序决定生死

联合索引是大多数开发人员用错最多的地方。它的核心规则是最左前缀原则。简单说，联合索引(a, b, c)相当于建了三个索引：(a)、(a,b)、(a,b,c)。你用b和c去查询，这个索引就用不上。

举个例子：

CREATE INDEX idx_union ON `order` (`user_id`, `status`, `create_time`); -- 能走索引 SELECT * FROM `order` WHERE user_id = 1001; SELECT * FROM `order` WHERE user_id = 1001 AND status = 1; SELECT * FROM `order` WHERE user_id = 1001 AND status = 1 AND create_time > '2023-01-01'; -- 不能走索引，或走得不完整 SELECT * FROM `order` WHERE status = 1; SELECT * FROM `order` WHERE create_time > '2023-01-01';

实际设计联合索引时，我的经验有三点：

• 区分度高的字段放前面。比如先放user_id而不是status，因为用户ID的区分度远高于订单状态
• 最常用的查询条件放最左。如果业务上最常按status+create_time查，那就把status放第一个
• 把范围查询的字段放最后。因为范围查询之后的字段无法走索引，比如create_time >之后的字段就失效了

3.4 前缀索引的妙用——大字段加速的性价比方案

对TEXT、VARCHAR(255)等大字段直接建普通索引，会有一个问题：索引占用空间大，B+树层级变多，性能不升反降。这时候用前缀索引很合适。

-- 只对前10个字符建索引 CREATE INDEX idx_content_prefix ON `article` (`content`(10));

前缀长度怎么选？我常用这个方法：先统计完整列的区分度，再对比不同前缀长度的区分度。

-- 完整列的区分度 SELECT COUNT(DISTINCT content) / COUNT(*) FROM article; -- 前缀长度10的区分度 SELECT COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) FROM article;

当两个值接近时（比如都接近0.9），说明前缀长度选得够用了。前缀索引的缺点是无法用于覆盖索引，因为索引里存的不是完整值，查询结果拿不到原字段，还是得回表。

4. 索引失效的这些场景——面试常问的实际工作也经常踩

4.1 隐式类型转换——int就是int，别乱穿衣服

你是否写过这种SQL：

SELECT * FROM `user` WHERE phone = 13800138000;

如果phone字段是VARCHAR类型，你传了数字，MySQL会先把字段值转成数字再比较，索引就废了。我之前排查过一个真实的慢查询，就是这种问题。订单表的手机号字段是VARCHAR(11)，业务代码里传的是Long类型，导致每次查询都是全表扫描，数据量大了之后接口直接超时。

解决办法就一句话：字符串字段就用字符串去匹配，SQL写成WHERE phone = '13800138000'。

关于"int+5"这个热搜词，我多说两句。如果你在WHERE里写了类似age + 5 = 20这种表达式，MySQL会放弃索引，因为索引树是按age的原始值排序的，age+5是一个计算后的结果，B+树没法直接定位。正确写法是把计算拆掉：WHERE age = 20 - 5，让字段保持裸奔状态才能走索引。

4.2 LIKE查询的通配符陷阱

LIKE 'abc%'能走索引，因为前缀是确定的，B+树可以按前缀定位。但LIKE '%abc'和LIKE '%abc%'就走不了索引，因为通配符在开头，MySQL不知道从哪个节点开始扫。

如果业务上确实需要后模糊匹配，我有三个方案供参考：

• 用LIKE 'abc%'配合应用层二次过滤
• 把值反过来存，比如存cba，查LIKE 'cba%'
• 数据量不大就接受全表扫描，别过度设计

4.3 函数操作、OR条件、NOT IN——索引失效三兄弟

函数操作就是上面说的WHERE DATE(create_time) = '2023-01-01'这种。create_time虽然是索引字段，但套了DATE()函数后，B+树没法用。正确写法是范围查询：

WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00'

OR条件只要有一个字段没有索引，整个索引就失效。比如WHERE user_id = 1001 OR status = 1，如果status没有索引，MySQL只能全表扫。解决办法就是给status也加上索引，或者拆成两个SQL用UNION ALL合并。

**NOT IN和!=**通常也无法走索引。这并不是说走了全表扫描就一定慢，要看数据分布。如果MySQL的优化器判断扫描索引还不如全表扫，它自己就会放弃索引。

4.4 find_in_set到底能不能走索引

热搜词里有find_in_set能走索引吗，这里直接给结论：不能。FIND_IN_SET(col, 'a,b,c')这种函数操作，MySQL无法使用索引优化。我之前在一个分类表上吃过亏，分类ID用逗号拼接存在一个字段里，想用FIND_IN_SET查询，结果数据量大后慢得一塌糊涂。

根本解法是做关联表，把一条记录对应多个分类ID拆分到子表里，每一行一条关联关系，然后给关联表建联合索引。

4.5 一个关于OR去重的冷知识

热搜词里还有个mysql的or能去重吗，这个问题问的人不少。说到底，OR是个条件连接符，不是去重操作符。真正去重要用DISTINCT或者GROUP BY。如果你写SELECT id FROM table WHERE a = 1 OR a = 2，返回的是所有满足条件的行，里面可能有重复的id（如果是一对多关系的话）。如果想去重：

SELECT DISTINCT id FROM table WHERE a = 1 OR a = 2;

4.6 索引失效场景速查表

5. 索引下推和覆盖索引——MySQL优化器的两个杀手锏

5.1 什么是索引下推（ICP）

这是MySQL 5.6带来的一个关键优化，也是面试高频题。先看概念，索引下推的全称是Index Condition Pushdown，意思是把WHERE条件里能过滤的部分，尽量下推到存储引擎层去过滤，减少回表次数。

我用一个例子说明。假设有联合索引(name, age)，执行：

SELECT * FROM `user` WHERE name LIKE '张%' AND age = 20;

在MySQL 5.6之前，流程是这样的：

1. 存储引擎根据name LIKE '张%'定位到索引记录
2. 凡是name以"张"开头的记录，都回表取完整行
3. 在Server层再判断age = 20，不满足就丢弃

在5.6之后，有了索引下推：

1. 存储引擎定位到索引记录后，直接在索引里判断age = 20是否满足
2. 只对满足条件的记录回表取完整行

两种方式的核心差别就在于：回表次数从"所有姓张的用户数"降低到"姓张且年龄20的用户数"。在数据量大的时候，这个差距是数量级的。

查看是否用了索引下推，在EXPLAIN的Extra列里会显示Using index condition。

5.2 覆盖索引——查询不需要回表的魔法

如果你查的字段全部包含在索引里，那MySQL根本不需要回表取完整行，这叫覆盖索引。比如有一个索引(user_id, status)：

SELECT user_id, status FROM `order` WHERE user_id = 1001;

这个查询的两个字段都在索引里，索引直接返回结果，不回表。EXPLAIN的Extra列显示Using index就代表走了覆盖索引。

覆盖索引的价值在数据量大的时候特别明显。之前优化过一个报表SQL，原SQL查了*，回表几万次，后来改成只查统计需要的字段，让所有字段覆盖进索引里，查询时间从2秒降到50毫秒。

设计索引时有意识地"按需覆盖"是个很实用的技巧：把SELECT高频查询的字段塞进联合索引里，让它既能作为查询条件，又能覆盖返回结果。不过注意别滥用，每加一个字段都会增加索引空间和维护成本。

5.3 EXPLAIN怎么看——定位慢查询的必备技能

优化SQL第一步永远是EXPLAIN。我把核心列的含义说一下：

• type：访问类型。从好到坏依次是system > const > eq_ref > ref > range > index > ALL。看到ALL就是全表扫描，重点排查
• key：实际用的索引
• rows：预估扫描行数
• Extra：Using index表示覆盖索引；Using where表示回表后过滤；Using index condition表示索引下推

一个简单的优化流程是：

EXPLAIN SELECT * FROM `order` WHERE user_id = 1001 AND status = 1;

看到type=ALL就去检查是否有索引、索引顺序是否合理、是否触发了索引失效的坑。

5.4 索引在排序中的作用——ORDER BY也能用索引

热搜词里有mysql排序，这里我多说一个点：B+树本身是有序结构，所以ORDER BY也能利用索引避免文件排序。比如建立联合索引(age, create_time)，ORDER BY age, create_time可以直接走索引的有序性，避免filesort。

但如果排序方向和索引顺序不一致，比如ORDER BY age DESC, create_time ASC，MySQL可能仍然会文件排序。另外，ORDER BY的字段顺序也要满足最左前缀原则，否则索引无法帮忙排序。

6. 实战排查——从三个真实案例看索引优化的完整过程

6.1 案例一：订单表慢查询优化

背景：订单表3000万数据，按user_id和status查，耗时2.8秒。原始表结构只有主键。

排查过程：

EXPLAIN SELECT * FROM `order` WHERE user_id = 1001 AND status = 1;

结果type=ALL，全表扫描。解决：

ALTER TABLE `order` ADD INDEX `idx_user_status` (`user_id`, `status`);

优化后耗时80毫秒。就这么简单，但80%的慢查询问题其实就出在"没建索引"或"建了没用上"这两点上。

6.2 案例二：字符串日期比较引发的索引失效

背景：按创建日期查询订单，SQL写的是：

SELECT * FROM `order` WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';

结果走不了索引。排查发现create_time字段类型是VARCHAR(20)，不是DATETIME，MySQL需要把字符串转成日期再比较，索引失效。

解决方法是把字段类型改成DATETIME。改完之后执行计划从ALL变成range，查询时间快了几十倍。

6.3 案例三：联合索引顺序引起的性能瓶颈

背景：订单查询支持按user_id、status、create_time组合筛选，现有索引是(status, user_id)，但大量SQL都是先按user_id过滤，索引利用率很低。

我重新设计索引：

ALTER TABLE `order` DROP INDEX `idx_status_user`; ALTER TABLE `order` ADD INDEX `idx_user_status_time` (`user_id`, `status`, `create_time`);

同时把create_time的范围条件放最后，避免范围之后的字段失效。优化后有筛选条件时，基本都在毫秒级返回。

这几个案例其实说明了一个道理：索引优化不是加一个索引就完事，而是要结合业务的真实查询模式来设计，并且定期用慢查询日志复盘。

6.4 慢查询日志怎么开启和使用

MySQL的慢查询日志定位慢SQL非常有用，下面是开启方法：

-- 查看当前设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志（当前会话或临时生效） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_output = 'TABLE';

设置完成后，慢SQL会记录到mysql.slow_log表里，可以直接查：

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20;

如果是持久化配置，需要修改MySQL配置文件（my.cnf或my.ini），在[mysqld]段加：

slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1

7. 排查问题速查表——照着做就对了

8. 我在MySQL索引上的几点实操心得

第一，主键能短就别长，能自增就别拖着。因为InnoDB的二级索引叶子节点都存主键值，主键是UUID的话，每个二级索引都跟着膨胀，回表也得带到主键索引树里比对更长的key。

第二，索引不是建完就一劳永逸。每半年我会做一次索引体检，重点看低频索引和冗余索引，用SHOW INDEX FROM table_name查看索引基数，用performance_schema分析索引使用情况。长期没被用到的索引该删就删，写入性能就是省出来的。

第三，把"EXPLAIN"变成肌肉记忆。任何一条性能相关的SQL，先EXPLAIN再下结论，别靠猜。我从没见过哪个SQL性能问题靠猜能猜对的。

最后再分享一个小细节：EXPLAIN的结果里，type=const代表用主键或唯一索引等值查询，这是最快的方式；type=ref代表普通索引等值查询，也不错；type=range代表范围查询，常见于BETWEEN、>=、<=；type=index代表全索引扫描，不一定慢但通常不是最优；type=ALL就是全表扫描，重点关注。记住这个序列，定位问题会快非常多。