企业官网建设流程全解析

给长字符串加索引，见过的人都知道，这不是一条ALTER TABLE ADD INDEX能轻松搞定的事。这种需求在线上出现过不止一次，反正是把我本人困住过好几回。最典型的是处理一张每天新增几十万行、里面全是带参数 URL 的记录表，字段类型是VARCHAR(2048)，想加索引提速，结果命令一提交，直接弹出一行让人头皮发麻的错误：Specified key was too long; max key length is 767 bytes。

后来我把这里面的门道彻底捋了一遍，发现解法不止“缩短字段”这一条。前缀索引、哈希冗余列、全文索引、字典表拆分，每种方案都有各自的适用场景，关键是先搞清楚你手上的查询长什么样。这篇就把这些做法串起来，从原理讲到实操，最后给出一套可以直接复用的判别流程，给同样被长字符串索引折磨的人一个完整参考。

1. 先搞清楚长字符串索引到底卡在哪儿

1.1 一条报错信息背后的“最大键长”

如果你在旧版本 MySQL 或者使用ROW_FORMAT=COMPACT的表上，给一个VARCHAR(500)的字段直接建索引，很可能遇到下面这类错误：

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

这个报错说得很直白：InnoDB 对索引键本身有一条硬性的字节数上限。过去很长一段时间里，单列索引或者复合索引各字段累计的键长不能超过 767 字节；到了 MySQL 5.7 之后的DYNAMIC/COMPRESSED行格式，配合默认 16KB 的数据页，上限放宽到了 3072 字节。注意这个上限是“字节”，不是“字符”，所以字符集一放大，问题马上就来。

很多人第一反应是“我字段类型不够长？那我改短一点”。其实更常见的根源是字符集和行格式。一张老表如果一直是COMPACT，哪怕字段只有VARCHAR(200)，在 utf8mb4 下也会嫌长，因为 200 个字符最多可能占用 800 字节，严格说并不一定超，但在复合索引里两个字段一叠加就超了。

所以遇到长字符串加索引，第一步不是急着改 SQL，而是先看三样东西：表的行格式、字段的字符集、以及索引里包含的字段数量。这三个因素共同决定了键长够不够用。

1.2 字符集是最大的“放大镜”

最典型的放大镜就是utf8mb4。它一个字符最多占 4 个字节，如果用latin1，同样的字段只要 1 字节一个字符，两者的索引体积差距可以到 4 倍。

举个例子：VARCHAR(255)在latin1下面建索引，最大键长 255 字节，完全没问题；但换成utf8mb4，同样的 255 个字符就可能达到 1020 字节，直接超出旧版 767 字节的限制。哪怕新版本放宽到 3072 字节，如果你后缀又挂了两个字段，累计起来照样可能爆。

另外TEXT和BLOB这类字段更特殊，InnoDB 不允许直接对完整长度建索引，必须指定前缀长度，比如INDEX(name(100))。也就是说，长字符串加索引的问题在TEXT字段上尤其绕不开前缀这个概念。

这里我想强调一个实操判断：看到报错先查SHOW TABLE STATUS LIKE '表名'里的Row_format，再看SHOW FULL COLUMNS FROM 表名里每个字段的Collation。这两条信息能让你少走很多弯路。很多人一上来就在网上搜“怎么突破 767 字节”，其实先确认环境和字符集，很多所谓“突破”方案根本不需要。

1.3 先别急着改表，把需求拆成四类

长字符串加索引没有银弹，关键是先搞清楚你实际的查询需求。我自己会拆成四类：

• 精确等值查询：WHERE url = 'https://xxx?a=1'，典型场景是日志去重、订单号校验。
• 前缀范围查询：WHERE name LIKE 'abc%'，常见于自动补全、编码前缀筛选。
• 模糊搜索：WHERE content LIKE '%关键词%'，资讯、商品详情、评论搜索。
• 排序去重：ORDER BY col/GROUP BY col，统计类报表。

这四类需求对索引的要求差异很大。比如前缀索引可以帮到精确等值和前缀范围查询，但对模糊搜索几乎无能为力；哈希索引只适合精确等值；全文索引则反过来，专门服务模糊搜索，对精确等值并不友好。把这四类需求先写下来，再往下选方案，就不容易白忙一场。

2. 最常见的解法：前缀索引怎么选长度

2.1 前缀索引到底在干嘛

前缀索引就是只取字段前 N 个字符参与索引排序，语法很简单：

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

这表示索引树里只存每条 URL 的前 20 个字符。查询的时候，MySQL 先用这 20 个字符去索引里定位，定位到一批候选行之后，再回表读取完整数据逐行比对。对于WHERE url = '一个具体值'这种查询，前缀长度选得够好，候选行数量会被压缩得非常小，回表后的过滤成本几乎可以忽略。

我最初用前缀索引时有个误解，以为取了前缀之后就只能匹配前缀。其实不是的，只要前缀足够长、区分度足够高，等值查询依然秒回。只是索引本身不存完整值，所以无法做到“覆盖索引”，每次命中都要回表，这算代价之一。

2.2 用一条 SQL 算出合适的前缀长度

给字段定前缀长度，核心指标是“区分度”。通俗说就是：截取前 N 个字符后，还有多少条记录是重复的。重复太多，索引定位到的候选行就多，回表成本就高，索引效果自然打折。

实际操作时，我一般直接跑下面这条“选择性评估 SQL”：

SELECT COUNT(*) AS total_count, COUNT(DISTINCT url) AS full_unique, COUNT(DISTINCT LEFT(url, 6)) AS prefix_6, COUNT(DISTINCT LEFT(url, 12)) AS prefix_12, COUNT(DISTINCT LEFT(url, 18)) AS prefix_18, COUNT(DISTINCT LEFT(url, 24)) AS prefix_24 FROM url_log;

跑完之后，把每个前缀的去重数除以总数，算出来的比例就是“选择性”。比如总数 100 万，全字段去重 80 万，LEFT(url, 18)去重 79 万，那 18 个字符前缀的选择性就是 79%，已经和完整字段的 80% 非常接近，基本可以认为再增加长度收益也不大了。

我踩过的一个坑是盲目追求 99% 的选择性，结果前缀长度越拉越长，甚至快接近字段本身，白白浪费索引空间。选择性不是越高越好，而是找到“曲线拐点”。一般和完整字段的选择性差距小于 1% ～ 2%，长度又比完整值小很多，就是很理想的前缀长度。比如 URL 里大量重复的/product/路径，可能只需要 16 ～ 24 个字符就能拉开差异。

2.3 前缀索引的三条边界要记牢

前缀索引虽然上手快，但有几条边界必须心里有数。

第一，它不能用于排序。ORDER BY url希望索引能按完整值输出顺序，但前缀索引只存了部分字符，后面的值可能是乱序的，所以优化器会直接放弃。

第二，它无覆盖索引能力。普通索引如果包含查询所需的所有列，可以只读索引不回表；但前缀索引没有完整值，覆盖能力天然缺失。

第三，不能用于唯一约束。给url(20)建唯一索引，会让两个前 20 个字符相同、后面不同的 URL 被误判为重复，直接写入失败。真要保证唯一，必须在完整列上建唯一索引，或者用下面要讲的哈希列方案。

所以我对前缀索引的定位是“低成本的普通加速方案”。它适合字段确实很长、高基数、查询以等值和前缀匹配为主的场景。缺点是上线之后如果业务查询变了，它救不了急。

3. 适合等值查询的进阶思路：哈希冗余列

3.1 用生成列把哈希值自动化

如果你只需要精确等值查询，最稳的方案之一是在表里冗余一个“哈希列”。这样索引对象从几千字节的长字符串，变成一个固定长度的哈希值，空间小、排序快、查询也干净。

MySQL 5.7 之后可以直接用生成列，建表时声明字段，数据库自动维护，不需要在业务代码里额外算一遍哈希：

CREATE TABLE url_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, url VARCHAR(2048) NOT NULL, url_hash CHAR(32) GENERATED ALWAYS AS (MD5(url)) STORED, KEY idx_url_hash (url_hash) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里我用CHAR(32)存储 MD5 的十六进制结果。生成列分STORED和VIRTUAL两种，前者把值物理存下来占空间，后者不占空间但查询时实时计算。对索引来说，建议用STORED，因为建索引本身就需要一个持久化的可访问值，牺牲一点空间换取查询和索引的稳定性。

哈希函数本身我一般优先用SHA2(url, 256)，返回 64 位十六进制字符，碰撞概率比 MD5 低得多。虽然大多数业务场景里 MD5 足够用，但你没法保证未来数据量不会暴涨，直接上 SHA2 更安心。如果对性能有顾虑，实测下来 SHA2 的开销在绝大多数应用里都不是瓶颈，真正的瓶颈往往是回表次数和网络 IO。

3.2 查询怎么改，优化器才会走索引

有了哈希列之后，查询必须写成“等值比较一个计算好的哈希值”，不能写“对字段做函数计算”。

正确写法：

SELECT * FROM url_log WHERE url_hash = SHA2('https://example.com/path?a=1', 256) AND url = 'https://example.com/path?a=1';

第二行AND url = ...不是多余的，它是防碰撞的兜底条件。万一两个不同 URL 哈希撞了，加上原始字段精确比较，结果集不会出差错。

错误写法是把函数套在字段上：

SELECT * FROM url_log WHERE SHA2(url, 256) = SHA2('https://example.com/path?a=1', 256);

这种写法会让索引失效，因为索引里存的是哈希列的值，而优化器无法直接把SHA2(url, 256)和存储列对应起来。即使功能上等值，优化器通常会选择全表扫描，一边扫一边算函数，后果就是慢查询。所以规则很简单：索引建在哪个列上，查询条件就老老实实写哪个列的等值比较。

3.3 哈希索引的两个经典坑：碰撞和排序

哈希索引的问题也很明显。

第一是碰撞。碰撞概率虽然低，但只要你把哈希列建成了唯一索引，碰上一次就是线上事故，合法记录会被拒绝写入。所以我建议哈希列索引用普通索引而不是唯一索引，唯一性交给原始字段或业务层判断。

第二是它完全丢掉了字符串顺序。WHERE url > 'https://a'这类范围查询、ORDER BY url，哈希列都帮不上忙。哈希值没有前缀含义，只适合精确匹配。

第三是额外的磁盘占用。每个 URL 额外多 64 字节的哈希列，对于数千万行的表就是几百 MB 到几 GB 的空间。设计时要评估清楚，别只看到查询变快，没看到磁盘成本。

我自己遇到的一个真实场景是：URL 字段 2048 字符，直接建前缀索引后仍要回表读很多行，后来加了 SHA2 哈希列，精确查询从 2 秒多降到几十毫秒，效果非常直接。缺点是多占了近 1GB 空间，但这个代价对查询收益是值得的。

4. 不同场景下的另外两个思路：全文索引和字典表

4.1 全文索引：专门服务模糊搜索

如果你希望从长文本里搜关键词，比如文章正文、商品描述、日志详情，前缀索引和哈希索引都帮不上忙。LIKE '%关键词%'这种中缀模糊查询，普通 B+Tree 索引只能全表扫。这时应该考虑全文索引。

InnoDB 的全文索引用法和普通查询差别挺大：

ALTER TABLE article ADD FULLTEXT INDEX ft_body (body) WITH PARSER ngram; SELECT id, title FROM article WHERE MATCH(body) AGAINST ('数据库调优' IN NATURAL LANGUAGE MODE);

中文场景要特别注意ngram分词器，没配它的话，默认分词对中文支持很差。全文索引适合“一段文本里包含哪些词”这类搜索，但它不是一个通用等值索引。如果你要查WHERE body = '完全相同的整段文本'，全文索引没有优势，反而该用哈希方案。

全文索引的维护成本不低，每次插入删除都要维护分词结果，所以它只适合真正有文本检索需求的表。数据量小的表用不用无所谓，数据量大且搜索频繁，全文索引才体现出价值。

4.2 字典表：把“加索引”变成“加外键”

还有一种绕开长字符串的思路，我愿称之为“把列拆出去”。当长字符串的取值其实比较有限，比如设备型号、商户名称、来源渠道，你完全没有必要在每个业务表里都放一个几百字节的字符串，更没必要反复给它建索引。

做法是建一张字典表，把字符串去重存进去，给每个字符串分配一个整型 ID，业务表里只存这个 ID：

CREATE TABLE merchant_dim ( id INT PRIMARY KEY AUTO_INCREMENT, merchant_name VARCHAR(500) NOT NULL, UNIQUE KEY uk_name (merchant_name) ); CREATE TABLE trade_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, merchant_id INT NOT NULL, amount DECIMAL(12,2), KEY idx_merchant (merchant_id) );

这样原来对merchant_name的等值查询变成对merchant_id的等值查询，小整数索引占用空间小、效率高，还天然解决重复字符串的空间浪费。查询时如果需要展示名称，JOIN 一下字典表就行。

这种方案的适用条件是“字符串集合有限且重复率高”。如果一个字段几乎每条记录都不一样，字典表会膨胀到你怀疑人生，那就别硬拆了。

4.3 我的判别流程：四种方案怎么选

我给团队小伙伴分享过一套很简单的判断流程，基本能覆盖大多数场景：

• 字段重复率极高、取值有限，比如状态词、来源渠道，优先拆字典表。
• 字段很长但需要精确等值查询，比如 URL、唯一标识，优先哈希冗余列。
• 字段较长且主要做前缀匹配、等值查询、又不想多占空间，前缀索引。
• 字段是长文本且需要关键词搜索，直接上全文索引，别用前面几种硬凑。

这个流程不是死的，有时候我会同时用两种。比如 URL 表既建 SHA2 哈希列应对精确查询，又留一个短前缀索引应对可能的域名前缀筛选。索引多了开销大，所以上线前一定把查询场景列清楚，再决定加几个。

5. 完整实操：给一张超长 URL 表加索引的全程记录

5.1 初始表和最初的糟糕体验

模拟一个简化版业务表，字段就是带参数的 URL，数据量大概 500 万行：

CREATE TABLE access_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, url VARCHAR(2048) NOT NULL, created_at DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

一开始没有任何索引，业务要按 URL 精确查询最近访问记录，每次都是全表扫描。我在测试库跑了一次WHERE url = 'https://example.com/product?id=1024'，耗时大概 2.3 秒。这个数字对生产环境来说绝对不可接受，尤其是查询频率一高，数据库的 CPU 和 IO 完全被拖垮。

我先检查了行格式，确认是默认的DYNAMIC，上限 3072 字节，所以直接建完整字段索引理论上可行，但 2048 字符的 utf8mb4 索引体积太吓人，而且数据页里密密麻麻全是长字符串，效率未必好。于是按前文流程走：这个需求核心是精确等值查询，最好的选择是加哈希列。

5.2 定方案：哈希加前缀，双管齐下

最终执行的建索引语句分两步走。第一步加 SHA2 哈希生成列，第二步同时建一个 24 字符前缀索引，给后续可能的域名前缀筛选留条路：

ALTER TABLE access_log ADD COLUMN url_hash CHAR(64) GENERATED ALWAYS AS (SHA2(url, 256)) STORED; ALTER TABLE access_log ADD INDEX idx_hash (url_hash); ALTER TABLE access_log ADD INDEX idx_url_prefix (url(24));

这里CHAR(64)存的是 SHA2 的十六进制结果。哈希列由数据库自动维护，不需要改一行业务代码，也不需要写回填脚本，这是生成列最大的优势。

加完索引之后，等值查询的 SQL 改成：

SELECT id, user_id, url, created_at FROM access_log WHERE url_hash = SHA2('https://example.com/product?id=1024', 256) AND url = 'https://example.com/product?id=1024';

跑完之后，同样的查询时间从 2.3 秒降到 40 毫秒以下。数据量不变，查询条件不变，只是多了两个索引，效果立竿见影。

5.3 用 EXPLAIN 确认索引真的生效

索引建完不能光看“查得快了”，还要用EXPLAIN确认优化器确实走到了索引分支：

EXPLAIN SELECT id, user_id, url, created_at FROM access_log WHERE url_hash = SHA2('https://example.com/product?id=1024', 256) AND url = 'https://example.com/product?id=1024';

返回结果里type应该是ref，key显示idx_hash，rows预估是个位数的候选行，这就说明优化器已经通过哈希列把全表扫描压缩成了极小范围的索引查找，回表只是为了拿完整 URL 做最后验证。

如果看到type=ALL，说明没走索引，大概率是查询条件写错了，比如对url字段套了函数。这时候不要急着加索引，先检查 SQL 写法。

我还会开一条performance_schema或者慢日志来观察，线上只要运行一段时间，看哪些查询命中idx_hash、哪些还在全表扫。没有验证的优化，跟没优化没什么区别。

6. 后续维护和容易后悔的操作

6.1 字符集升级引发的索引连锁反应

长字符串索引建好之后，最怕的不是查询变慢，而是表结构中途变卦。最常见的是字符集从latin1升级到utf8mb4，或者从utf8mb3升级到utf8mb4。字段本身能装下，但索引键长会随之膨胀，原来压着上限的前缀索引可能瞬间超限。

我之前就遇到过一张表，VARCHAR(300)建了(url(100))前缀索引，原来在latin1下没问题，改成utf8mb4之后，索引键长变成 400 字节，虽然没超 767，但复合索引里再叠一个字段就卡住了。最后只能删了索引重新算前缀长度。

所以升级字符集之前，先跑一遍前文提到的选择性评估 SQL，重新确认前缀长度，甚至考虑换哈希方案。别等报警了你才回头看 DDL 变更记录。

6.2 在线加索引要留足底牌

给大表加索引不是一条语句那么简单。即使 MySQL 8.0 支持很多在线 DDL，底下仍然会产生巨大的临时 IO 和日志量。我的习惯是：

• 在业务低峰期执行，避免高峰期加索引拖垮主库。
• 执行前确认磁盘剩余空间充足，至少预留表本身 1.5 倍的余量。
• 拆成多条语句，不要一条 ALTER 同时加三个索引，便于定位问题。
• 如果有主从，留意主从延迟，别让从库追不上。

如果表实在太大，还可以考虑用另外一张新表建好索引再做切换，虽然麻烦，但风险更可控。核心原则是：索引是为了解决慢查询，别让它自己变成慢查询的元凶。

6.3 我现在的固定工作流

这几轮折腾下来，我处理长字符串加索引已经有了一套固定流程。接到需求先不急着写 DDL，而是先问三个问题：查询是等值、前缀、还是模糊？字段的重复度大概多少？能不能容忍多占一点存储？

然后我会跑一遍选择性评估 SQL，把前缀长度和哈希方案都写出来对比。确定方案后，先在测试环境用 EXPLAIN 验证，再上生产。每次加完索引，我会在一个月后回来看一遍慢日志，看这个索引的实际命中率，把根本没用的索引及时清掉。

最后再分享一个小技巧：如果一张表的长字符串字段很多，也别全都堆索引，优先优化查询频率最高的那条路径。一次解决一个最痛的点，比一次性塞五个索引要稳得多。索引不是越多越好，是越准越好。