企业官网建设流程全解析

1. 先搞懂索引到底干了一件什么事

聊MySQL索引之前，我想先抛一个场景。前阵子帮朋友排查一个线上慢查询，某张业务表八百万行数据，一条SELECT * FROM order_info WHERE user_id = 128901跑了两秒多。加了一个普通索引之后，直接降到二十毫秒。前后差了上百倍，但代码一行没改，就是加了一行create index的语句。这就是索引的价值，也是为什么我觉得每个写SQL的人都应该把它彻底吃透。

很多人对索引的理解停留在“加了索引查询就快了”，但没想过它到底为什么快、快在哪个环节、什么情况下加了反而拖后腿。我在实际项目里见过不少开发同学，遇到慢查询就盲目加索引，结果索引建了一大堆，数据写入变慢了，磁盘空间也撑不住，最后还得一个个删。所以这篇文章我想把索引的创建和删除讲透，包括背后的数据结构原理、具体SQL语法、设计时的取舍标准，以及生产环境里那些坑。

1.1 为什么MySQL要专门搞一套索引结构

在没有索引的情况下，MySQL执行一条查询就是全表扫描：从第一行数据开始，一行一行比对，直到找到目标记录。你可以把它想象成一本没有目录的字典，想找一个“张”字，只能从第一页翻到最后一页，运气好可能几页就找到了，运气不好整本翻完。索引本质上是给存储引擎另外维护的一套“目录结构”，让数据查找不用再从头扫到尾。

MySQL默认的存储引擎InnoDB用的是B+树。B+树这个结构有两个核心特性：一是所有数据都存储在叶子节点，并且叶子节点之间通过指针串联成双向链表；二是非叶子节点只存索引键值和子节点指针，每一层节点数量有限，所以树的高度很矮。一般两三百万行的表，B+树高度也就三四层，查找一次只需要走三四次磁盘I/O。全表扫描要读多少页？几万甚至几十万个数据页，高下立判。

索引这么好用，为什么不全表每一列都加索引？因为索引是要额外存储、额外维护的。每插入一条记录、删除一条记录、更新某个带索引的字段，InnoDB都要同步维护所有相关索引的B+树。写入性能的损耗，以及索引表空间占用的磁盘，都是成本。这也是为什么索引创建和删除都不是可以随手为之的小事。

1.2 主键索引和二级索引到底有什么区别

InnoDB里数据表本身其实就是一个索引结构，叫作聚簇索引。每张表默认以主键作为聚簇索引的键值，叶子节点上存的是整行完整数据。如果你建表时没指定主键，InnoDB会自己选一个非空唯一索引作为主键，如果也没有，就隐式生成一个内部主键。总之，InnoDB表一定有一个聚簇索引。

除了聚簇索引之外的索引，统称为二级索引，也有人叫辅助索引。二级索引的叶子节点只存两样东西：索引键值和对应主键值。查询时如果二级索引覆盖不了全部所需字段，就需要拿着主键值回头去聚簇索引里把整行数据捞出来，这个过程叫回表。

举个例子，表结构里有主键id、字段user_id和user_name。如果我在user_id上建了一个索引，执行SELECT * FROM t WHERE user_id = 10086，MySQL会先在二级索引的B+树里定位到键值为10086的叶子节点，拿到主键id，然后拿着这个id去聚簇索引里读取完整行。一次查询至少走两次B+树查找。但如果我只需要user_id这一个字段，写成SELECT user_id FROM t WHERE user_id = 10086，二级索引的叶子节点里就有这个值，完全不用回表，这叫做覆盖索引优化。

这个细节极其重要，因为很多慢SQL的问题不一定是没有索引，而是建了索引但select了过多不需要的列，导致本来可以覆盖查询的场景硬生生多出大量回表I/O。我在后面讲创建索引时，会专门提到怎么利用覆盖索引来优化SQL。

2. 创建索引的4种正确姿势与设计红线

创建索引听起来很简单，不就是一行SQL嘛。但我在项目里review过不少人的操作，真不是每个人都能把那一行SQL写对的。建错的索引、冗余的索引、选择度极低的索引，带来的问题甚至比没有索引还难看。

2.1 建表时直接定义索引的语法解析

在建表语句里，可以把索引直接写在字段定义后面，也可以在表定义的末尾统一声明。两种写法看起来差不多，但可读性和维护性差别挺大：

CREATE TABLE user_info ( id BIGINT NOT NULL AUTO_INCREMENT, user_id VARCHAR(32) NOT NULL, user_name VARCHAR(64), status TINYINT DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uk_user_id (user_id), KEY idx_user_name (user_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上面这段SQL里，PRIMARY KEY (id)建的是主键聚簇索引；UNIQUE KEY uk_user_id (user_id)建的是唯一二级索引，既约束了user_id不能重复，也能加速查询；KEY idx_user_name (user_name)建的是最普通的二级索引。

我建议项目里统一规范索引命名：主键叫PRIMARY，唯一索引叫uk_开头字段名，普通索引叫idx_开头加字段名。别小看这个习惯，后面你们DBA巡检慢查询、或者出线上故障临时排查索引时，一眼就能看出这个索引是哪个字段上的、用来干什么的，省太多时间了。

2.2 表已经建好了，加索引用CREATE INDEX还是ALTER TABLE

大多数时候表早就上线了，不可能为了加索引重建表。这时候有两种写法：CREATE INDEX和ALTER TABLE ... ADD INDEX。

-- 方式一 CREATE INDEX idx_user_name ON user_info(user_name); -- 方式二 ALTER TABLE user_info ADD INDEX idx_user_name(user_name);

这两条SQL本质上做的事情几乎一样，底层都是添加一个二级索引，执行过程也都会触发表的重建或在线DDL。区别在于ALTER TABLE的能力更完整，它除了能加普通索引，还能加主键、加唯一约束、删除主键等，而CREATE INDEX只能创建普通或唯一索引。如果只是单纯加索引，我个人更喜欢用CREATE INDEX，语义更清晰；但如果你要同时加多个索引、或者调整约束，就统一用ALTER TABLE，一条语句搞定多种变更。

顺便说一句，MySQL 8.0里支持了不可见索引和函数索引。不可见索引的意思是索引保留在表上，但优化器查询时完全忽略它，相当于一个开关。函数索引比如ALTER TABLE t ADD INDEX idx_year(created_at, YEAR(created_at))，MySQL 8.0可以直接在虚拟列上建索引，本质上还是普通索引，但对那些经常把时间字段套函数的查询帮助很大。这些虽然名字听着花哨，底层的索引结构并没有变。

2.3 创建索引前必看：字段选择度决定瓶颈

这是最重要的部分。我在项目里见过有人给一个只有0和1两个值的status字段建索引，还建得理直气壮。结果呢？数据分布差不多各占一半，优化器一计算发现全表扫描和走索引回表的成本差不多，干脆就不走索引，等于白建。

索引值区分度，也就是选择度，是决定索引有没有用的关键指标。选择度的计算方式很简单：COUNT(DISTINCT column) / COUNT(*)，这个值越接近1，说明重复值越少，索引过滤效果越好；越接近0，说明大部分行都是同一个值，索引几乎没意义。

我建议建索引前先跑一条SQL验证一下：

SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity FROM user_info;

如果算出来的选择度低于0.1，这个字段除非用来满足覆盖索引需求，否则基本不用考虑建索引。高选择度字段才是索引的主战场，比如订单号、手机号、邮箱这种几乎每条记录都不同的列。

还有一类特殊情况：字段值很长，比如URL、文章正文，建索引时整字段存进去会导致索引树非常大、每一层能存的数据量变小，树变高，B+树优势就会被削弱。这种场景我一般建议建前缀索引，只取前N个字符：

ALTER TABLE article ADD INDEX idx_url_prefix(url(20));

注意前缀索引有个硬伤：它没法用于覆盖索引，因为索引树里存的不是完整的列值。

2.4 联合索引的最左前缀法则，别再傻傻建N个单列索引

很多人遇到多条件查询，习惯性地每个条件字段各建一个单列索引，比如WHERE user_id = ? AND status = ? AND create_time > ?，给三个字段各建一个索引。这种做法在MySQL里大部分时候只有一个索引会被真正用到，剩下两个就是浪费。

正确的设计是建联合索引，比如(user_id, status, create_time)。联合索引的B+树先按第一个字段排序，第一个字段相同再按第二个字段排序，以此类推。所以它能直接用于最左前缀：索引里最左边的连续字段集合都可以用来加速查询。

具体来说，(user_id)、(user_id, status)、(user_id, status, create_time)这三种查询条件都能命中这个联合索引前段，但(status, create_time)这种不包含最左列的条件，就用不上这个联合索引。

建联合索引时还有个实用经验：把等值查询的字段放前面，范围查询字段放后面。因为范围查询一旦出现，后续字段就没法继续利用索引做精确定位了。比如WHERE user_id = ? AND create_time > ?，就应该建(user_id, create_time)而不是(create_time, user_id)。这个细节做错了，整个索引的过滤性能会大打折扣。

我还见过一种冗余索引的场景：表上已经有一个(user_id, status)联合索引，又单独建了个user_id单列索引。其实单列的user_id索引完全可以被联合索引替代，是完全的冗余。冗余索引不仅多占磁盘、拖慢写入，还会让优化器偶尔犯迷糊。清理冗余索引本来就是DBA日常巡检的重点工作之一。

3. 删除索引：语法容易，代价评估难

删除索引看起来就是一行SQL的事，但真正做起来，特别是大表环境下，要考虑的远比语法多得多。

3.1 删除索引的标准语法与执行细节

MySQL里删除索引同样有两种写法，对应前面创建时的两种方式：

-- 方式一 DROP INDEX idx_user_name ON user_info; -- 方式二 ALTER TABLE user_info DROP INDEX idx_user_name;

这两条SQL效果完全一致。DROP INDEX不能用来删主键索引，需要ALTER TABLE ... DROP PRIMARY KEY。不过生产环境里多数的删除操作其实发生在三类场景：一是清理冗余索引；二是某个SQL改了查询条件，旧索引不再被任何查询命中；三是当前索引结构设计不合理，需要换成新的联合索引。

删除索引之前，我习惯先查一下这个索引是否还有人用。方法也很简单——开启MySQL的性能字典或者慢查询日志观察一周，或者用sys.schema_unused_indexes视图直接看系统统计结果：

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'yourdb';

这个视图是MySQL 5.7和8.0自带的，能识别出过去一段时间内从未被使用的索引。我建议不要看了这个结果就匆匆删除，先确认对应的表上有没有比较低频但关键的业务查询，比如每月跑一次的报表SQL也会用到这个索引，一周观察窗口可能根本体现不出来。

3.2 Online DDL与锁表问题，大表删索引前必须知道

很多同学以为MySQL执行DROP INDEX是瞬间完成的。如果你在几十万行的表上操作，确实体感上是瞬间；但如果是几千万行的大表，就没这么简单了。

InnoDB从5.6开始支持了Online DDL，但不同索引操作的在线程度不一样。官方文档里索引创建是ALGORITHM=INPLACE，允许DML并发；索引删除同样支持INPLACE。真正值得注意的不是锁不锁表，而是操作期间产生的日志量和资源消耗。大表重建索引或删除索引，会导致大批量数据页变更，redo log写入量飙升，同时主从同步也可能因为DDL产生延迟。

我自己踩过一次坑：凌晨对一张四千多万行的日志表做索引整理，删掉一个废弃索引再加一个新索引，结果主库redo log所在磁盘空间被撑满，数据库直接只读了。当时整个核心链路全部挂掉，最后是清理binlog加重启实例才恢复，非常惊吓。

我的经验是：线上大表任何索引结构变更，都别直接在主库上执行。要么用 pt-online-schema-change 这类工具做在线无锁变更，要么至少先在从库上跑一遍，确认耗时和空间消耗都在预期范围内，再在低峰期执行。对于超大表，我个人的建议是优先考虑新建一张新表，在低峰期切换，把索引结构调整放到新表创建时一次性完成，比在线上表里反复增减索引稳妥得多。

3.3 删除索引之后的验证：执行计划里藏着答案

删除索引很容易，难的是删完之后的验证。很多人删完索引就跑，结果等到下一次运营报表跑出来才发现，一条核心SQL从原来的几十毫秒变成了几十秒，这时候再补齐索引又要折腾一轮，而且还要等创建索引期间的重建资源消耗。

我每次删除索引后，一定会把相关SQL的执行计划拉出来重新看一遍。举一个我在项目里实际遇到过的例子。有一张订单表，原来有个idx_order_no (order_no)单列索引。后来因为业务需要扩大了查询维度，我在(order_no, order_status)上建了联合索引。理论上单列索引是冗余的，可以删掉。但删除之后，我拿EXPLAIN SELECT * FROM order_info WHERE order_no = '20240115001'验证，发现确实走的是联合索引，key显示的是联合索引名，type是ref，完全没问题。但如果当时查的是SELECT order_no FROM ...，覆盖索引的叶子节点没有完整数据，就会多出回表步骤，这也要提前评估。

删除索引后的验证流程我总结成三步：先用EXPLAIN确认关键SQL走的是期望索引，type不是ALL；再通过SHOW PROFILE或者直接压测对比耗时；最后观察一段时间线上监控，确认没有慢查询召回。三步都过了，这条索引才算是真正删除干净了。

4. 生产环境里最容易踩的索引坑，含死锁案例分析

创建和删除索引本身不难，难的是索引在真实并发环境下的行为。我打算专门用一节来聊生产环境里那些让人头疼的索引相关坑，这些内容很多是常规文档不会写清楚、但实战中经常撞见的。

4.1 二级索引更新时的加锁顺序为什么会触发死锁

先看一个很有意思的问题。MySQL通过二级索引更新数据时，加锁顺序是先锁二级索引项，再回表锁聚簇索引里的主键记录。这个顺序是固定的，看起来也合理，但高并发下偏偏容易形成交叉等待。

我举个例子。假设表里有一个联合索引(group_id, order_id)，两条不同的业务记录，一条在group_id=1组，一条在group_id=2组。事务A要更新1组的一条订单，它的加锁顺序是：锁二级索引(group_id=1)项，然后锁对应的主键记录。事务B要更新2组的一条订单，它加锁顺序相同：先锁二级索引(group_id=2)项，再锁对应的主键记录。两个事务各干各的，看起来井水不犯河水，锁资源不重叠，怎么会死锁？

真正容易死锁的场景，是两个事务同时锁了对方即将要锁的二级索引项。比如事务A先锁了对二级索引项1的回表主键记录，又准备去锁二级索引项2对应的主键；事务B则先锁了二级索引项2对应的主键，又准备去锁二级索引项1对应的主键。这个时间窗口极小，它存在的前提是更新条件涉及多个二级索引项，或者一个事务里有多条更新语句，形成了锁获取顺序的不一致。MySQL死锁日志里看到类似“waiting for this lock to be granted”两条互相等待的记录，大概率就是这种交叉。

我实际遇到过一个真实的死锁案例。场景是一张订单表，二级索引建在(shop_id, order_status)上，有一个批量结转业务，会同时更新某几家店铺的状态。业务代码先查出一批shop_id对应的订单，按shop_id排序后逐条UPDATE。但两个并发事务查到的shop_id集合顺序不一样，事务A先更新shop_id=1的订单，事务B先更新shop_id=2的订单，两条UPDATE操作都走二级索引加锁，而后各自动态扩展锁范围，最终死锁，导致业务侧报错重试，重试又加重锁争用。

解决办法也不复杂，优先考虑业务操作按主键排序后执行，让所有事务的加锁顺序统一；或者干脆改成对同一类shop_id的订单先加锁再批量更新。核心思路就是：让并发事务争取锁资源的顺序尽量一致，交叉等待自然就不存在了。这也是索引设计之外的另一个重要认知：索引影响的不只是查询速度，还有锁的粒度和顺序。

4.2 索引表空间与碎片：为什么删数据后表空间不变成空白

索引表空间这个热搜词值得展开聊一下。InnoDB表的数据和索引统一存储在表空间文件里，无论是ibd文件还是系统表空间。删除大量数据之后，你有没有发现ibd文件大小几乎没变？这是因为InnoDB回收的最小单位是数据页，页内数据删空后，页本身在索引树上的空间会保留复用，而不是直接归还给操作系统。

这种情况在频繁insert和delete的日志表上特别明显。我处理过一个案例：某报表明细表每天写入几十万条，三个月清理一次历史数据，DELETE删了上千万行，但表空间文件一直涨到40GB没降下来。后续一插入数据，直接落回那些被删除后留下的空闲页里，文件大小不变，性能也说得过去，但大量碎片导致索引扫描的随机I/O增多，页面密度低，实际可用空间被严重浪费。

索引碎片整理的标准做法有两个：ALTER TABLE table_name ENGINE=InnoDB或者OPTIMIZE TABLE table_name。这两个操作都会重建整张表，包括所有二级索引，整理后碎片会消除，表空间会明显收缩。注意，对大表来说，重建意味着原表整个被复制一遍，需要至少两倍表空间大小的空闲磁盘，而且执行期间有大量磁盘I/O和写入，务必在低峰期操作。

顺带一提，我可以在information_schema里查看索引的存储统计信息：

SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE FROM information_schema.innodb_index_stats WHERE TABLE_NAME = 'order_info';

这个视图能看到索引的统计值，比如叶子节点数量和大小，通过对比多张表可以快速定位有没有异常膨胀的索引。我在做容量规划时，会定期拉这个视图做趋势分析。

4.3 索引失效的六种常见情况，一条条对号入座

索引建了，不代表查询就一定走索引。以下六种场景，放在任何版本的MySQL里都成立，值得挨个排查一遍。

第一种是在索引列上做函数运算。WHERE DATE(create_time) = '2024-01-01'即使create_time上有索引，也别指望走索引，因为优化器无法对函数处理后的结果做范围匹配。解决办法是改成WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'，这也解释了为什么MySQL 8.0的函数索引和虚拟列在实际业务里非常实用。

第二种是隐式类型转换。索引列是varchar类型，查询条件却传了数字，MySQL会把列上每个值转成数字再比较，索引照样失效。比如WHERE phone = 13800138000，phone列是varchar，这个查询就是典型的索引杀手。统一下发参数类型的强类型校验，或者改造SQL加引号，都是常见的修复方式。

第三种是前导模糊查询。LIKE '%keyword%'没有前缀，B+树只能顺序扫描。如果业务确实需要模糊搜索，可以评估改用全文索引，或者把关键字额外存一张倒排表。

第四种是OR两侧条件不都走索引。WHERE user_id = 10086 OR status = 1，如果status上有索引、user_id上没有，那基本就是全表扫描。

第五种是联合索引不满足最左前缀。这个前面讲过，不再重复。

第六种是优化器判断走索引代价更高。最常见的就是必要条件过滤比例太低，比如索引选择度极低，优化器宁可全表扫描。这类问题是数据分布问题，单独建索引解决不了，需要重新设计SQL语义或者考虑别的手段。

我在实际项目中排查慢SQL时，基本按照这个顺序往下捋，每次都很快定位。强烈建议把这六种情况抄下来贴在工位上，写SQL之前扫一眼，能帮你少走非常多弯路。

4.4 索引命中的确认手段：EXPLAIN输出怎么看才不踩坑

最后再分享一个非常实用但是经常被人忽视的工具：EXPLAIN。有些同学会看EXPLAIN的select type和table，但只看这两个远远不够。判断索引是否真正命中，重点看四列：key、type、rows、Extra。

key列显示的是优化器最终选择使用的索引名，如果为NULL，说明没有走索引。type列的优化等级从高到低大概是 system > const > eq_ref > ref > range > index > ALL，至少要达到ref或range以上，才算有效索引访问。rows列显示预估扫描行数，这个值越接近实际命中的行数越好，如果rows显示百万级别但type是ALL，基本确定是全表扫描。

Extra列里最值得关注的两个词：一个是Using index，代表覆盖索引，即查询的所有字段都能从二级索引里拿到，不用回表，这是最优状态；另一个是Using filesort，代表排序没走索引，可能需要额外的临时文件排序。我之前优化过一个列表页排序慢的问题，就是让排序字段进了联合索引，Extra里不再出现Using filesort，从几百毫秒降到几十毫秒。

另外说一句，MySQL 8.0的EXPLAIN还支持EXPLAIN ANALYZE，它能真实执行语句并输出每一步的耗时和扫描行数，对于复杂调优场景非常直观。我建议排查慢SQL时先用EXPLAIN做静态分析，再需要细粒度定位时用EXPLAIN ANALYZE跑一遍，两个工具配合着用。

5. 我的几条实战经验与建议

索引管理这块儿，做到最后，拼的不是某个具体的语法，而是整体设计纪律。

我对团队的要求很简单：建索引必须有SQL验证过程，要么用EXPLAIN对比前后执行计划，要么用性能监控数据说话；删索引必须走审批，并且要查清楚这个索引在所有SQL里是否真的无人使用，不能只看系统视图，还要跟业务开发确认低频报表的依赖；命名规范从头立住，永远不要出现随手敲的index1index2这种名字，否则半年后连你自己都不确定它是干嘛的。

还有一个小技巧，是我自己一直在用的：给每个索引写上备注。MySQL虽然不能在索引上直接注释，但可以在字段上用COMMENT字段记录用途，或者在团队的数据库维护文档里把每个索引的使用场景、创建日期、负责人、关联SQL全部记录下来。这个文档看着很土很啰嗦，但在排障时是真的救命。

另外想说一句，索引设计是一个动态过程。业务初期数据量小，单索引随便建没问题；数据量涨到千万级，联合索引和覆盖索引的设计就要重新审视；过亿后，可能连索引本身都要考虑拆分到单独的磁盘文件，甚至引入归档表来减少热数据规模。不要指望一次设计就能吃一辈子，定期做索引健康检查，什么季度做一次全量巡检，这就是我经历过线上事故后养成的肌肉记忆。

mysql创建索引和删除索引这两件事，写出来也就几十行SQL，但背后牵扯的数据结构、锁机制、成本评估、业务理解，每一层都有学问。希望这篇文章能帮你把这些点串起来，下次再面对慢查询和索引调优，不只是会敲那两行命令，而是真正知道自己在干什么。