MySQL 字符串字段没有索引导致全表扫描

什么是全表扫描？为什么它很糟糕？

在 MySQL 中，当查询无法利用索引快速定位数据时，存储引擎（如 InnoDB）就必须从磁盘上加载表的每一行数据，逐行检查是否满足查询条件。这个过程叫做全表扫描。

全表扫描对性能的影响是毁灭性的，尤其是当表的数据量达到百万甚至千万级别时：

• 磁盘 I/O 飙升：所有数据页都需要被读入内存（Buffer Pool），造成大量的随机读或顺序读。
• CPU 使用率暴涨：每一行都需要在 MySQL 层面进行条件判断。
• 严重阻塞并发：全表扫描会长时间持有表或行的锁（取决于事务隔离级别），导致其他查询等待。
• 拖垮整个数据库：大量数据被加载到 Buffer Pool 时，可能把原本缓存的热点数据“挤”出去，降低整体命中率。

字符串字段由于其长度不固定、比较开销大、且容易写出隐式类型转换等查询，成为了无索引导致全表扫描的重灾区。

动手复现：一个典型的性能灾难

假设我们有一张用户表 users，为了让你直观感受到差异，我先构造 100 万行测试数据。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 通过存储过程插入 100 万行随机数据（此处省略具体插入过程，你实际测试时可以使用脚本批量插入）

现在 email 字段上没有索引。我们尝试查询一个用户的邮箱：

SELECT * FROM users WHERE email = 'user500000@example.com';

使用 EXPLAIN 分析查询计划

在查询前加上 EXPLAIN，就能看到 MySQL 优化器选择的执行计划：

EXPLAIN SELECT * FROM users WHERE email = 'user500000@example.com'\G

输出关键字段解读：

           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL           -- 这里显示 ALL，代表全表扫描
possible_keys: NULL          -- 可能使用的索引为空
          key: NULL          -- 实际使用的索引为空
      key_len: NULL
          ref: NULL
         rows: 995303        -- 预估需要扫描的行数，接近全表总行数
     filtered: 10.00
        Extra: Using where   -- 使用 WHERE 条件进行过滤

• type: ALL 就是全表扫描的标志。
• rows 约等于表总行数，说明每一行都要检查一遍。
• key: NULL 表示没有走任何索引。

此时执行查询，耗时可能达到几百毫秒甚至秒级，而如果你频繁调用，数据库资源会迅速被耗尽。

为什么字符串字段尤其容易引发全表扫描？

原因比单纯“没有索引”更复杂，以下几个陷阱会悄悄让索引失效，进而退化到全表扫描。

陷阱一：隐式类型转换

字符串字段与数值比较时，MySQL 会将字符串转为数值再比较，这会导致索引失效。

-- phone 字段是 VARCHAR 类型
SELECT * FROM users WHERE phone = 13800138000;   -- 传入的是整数！

等价于在内部执行 WHERE CAST(phone AS UNSIGNED) = 13800138000，对索引列做函数操作直接导致索引失效。
正确做法：传入的值务必使用引号包裹。

SELECT * FROM users WHERE phone = '13800138000';

陷阱二：使用 LIKE 以通配符开头

索引可以用于 LIKE 前缀匹配，但一旦通配符在最前面，B+ 树无法利用有序性进行查找。

-- 无法使用索引
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 可以使用索引
SELECT * FROM users WHERE email LIKE 'user5000%';

陷阱三：对索引列使用函数

任何对索引列本身的函数操作都会破坏索引的有序性。

-- 索引失效
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

-- 索引有效（但更好的做法是存储时统一大小写，并直接比较）
SELECT * FROM users WHERE username = 'admin';

陷阱四：字符集或排序规则不一致

关联查询时，两个字符串字段的字符集（charset）或排序规则（collation）不同，也会导致无法使用索引。

SELECT * FROM orders o
JOIN users u ON o.buyer_name = u.username  -- 假设 buyer_name 的 collation 与 username 不一致

此时往往会在 Extra 列看到 Using where; Using join buffer (hash join)，表明索引失效。

解决方案：给字符串字段正确创建索引

最直接有效的办法就是为频繁出现在 WHERE、JOIN、ORDER BY、GROUP BY 中的字符串字段添加索引。

1. 普通索引

如果字段值大部分是唯一的，并且查询都是精确匹配，直接创建普通索引即可。

CREATE INDEX idx_email ON users(email);

创建后再次执行 EXPLAIN：

type: ref
possible_keys: idx_email
key: idx_email
rows: 1
Extra: Using index condition  -- 或为 NULL

type 变为 ref，rows 骤降至 1，查询效率实现指数级提升。

2. 前缀索引（关键优化）

如果字符串字段非常长（如 TEXT、长 VARCHAR），直接对整个字段建立索引会消耗大量磁盘空间，并降低数据页的存储效率。此时可以使用 前缀索引，仅对字段的前 N 个字符建立索引。

如何选择前缀长度？
前缀长度需要保证足够的区分度。可以通过以下 SQL 测试不同长度下的选择性：

-- 计算完整字段的选择性
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users;

-- 计算前 8 个字符的选择性
SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS selectivity FROM users;
-- 再测试前 10、前 12，直到选择性接近完整字段即可

假设前 10 个字符的选择性已经达到 0.9999，就可以创建前缀索引：

CREATE INDEX idx_email_prefix ON users(email(10));

前缀索引的限制：无法用于 ORDER BY 和 GROUP BY，也无法用于覆盖索引（即查询需要回表取完整字段值）。

3. 全文索引

对于需要做模糊中间匹配或文本搜索的场景（如搜索文章内容），普通 B+ 树索引无法满足。此时应使用 全文索引（FULLTEXT） 配合专用的 MATCH...AGAINST 语法。

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

注意：中文场景建议使用支持中文分词的插件（如 N-gram 解析器或第三方引擎），否则分词效果差。

4. 使用虚拟列 + 索引

如果你的查询习惯是对某个字符串字段进行变换后再查询（例如总是用大写查询），可以创建一个虚拟生成列，并对该列建立索引。

ALTER TABLE users ADD COLUMN username_upper VARCHAR(50) GENERATED ALWAYS AS (UPPER(username)) VIRTUAL;
CREATE INDEX idx_username_upper ON users(username_upper);

-- 现在这个查询可以走索引
SELECT * FROM users WHERE username_upper = 'ADMIN';

如何系统性排查全表扫描？

养成在开发、测试环境中对所有 SQL 进行 EXPLAIN 的习惯。重点关注：

• type 列是否出现 ALL（全表扫描）或 index（全索引扫描，通常也很慢）。
• rows 数量是否与表实际大小差距悬殊。
• Extra 是否出现 Using filesort、Using temporary。
• 是否由于隐式转换导致 key 为 NULL。

还可以打开慢查询日志（slow_query_log），设置合理的 long_query_time，定期分析那些未使用索引的查询。

唯一索引与普通索引的抉择

字符串字段经常既是查询条件也是业务唯一键（如用户名、邮箱）。直接创建 唯一索引 可以同时保证数据完整性和查询性能：

CREATE UNIQUE INDEX uk_email ON users(email);

但要注意唯一索引对 NULL 值的处理：MySQL 中一个唯一索引可以包含多个 NULL 值。如果你需要强制字段唯一且非空，列定义应使用 NOT NULL。

总结：防患于未然

• 设计表时就要分析查询模式，高频过滤、排序的字符串字段一律创建索引。
• 字符串字段尽量用定长 CHAR（如果数据长度高度固定），比较效率更高。
• 避免在代码中将字符串字段与数字等非字符串类型直接比较。
• 模糊查询能走前缀索引就不要左模糊或全模糊。
• 定期通过 sys.schema_unused_indexes（MySQL 8.0）或 pt-index-usage 工具审查索引使用情况，去掉无用索引，但绝对不能缺索引。

索引是一把双刃剑，不加索引是慢性自杀，乱加索引是挥霍存储。字符串字段一旦丢失索引，全表扫描就是等待你的性能恶梦。请用 EXPLAIN 把你拖出深渊。