如何通过索引优化MySQL字符串搜索性能？索引创建是否正确？

我需要优化大量类似Z1719121这类字符串的搜索性能。

表结构 #

codfabrica  => varchar 191 => nullable
codoriginal => varchar 191 => nullable
aplicacao   => mediumText  => nullable

当前索引信息 #

codfabrica  => non_unique 1 | seq_in_index 1 | collation A    | cardinality 42466
codoriginal => non_unique 1 | seq_in_index 1 | collation A    | cardinality 7449
aplicacao   => non_unique 1 | seq_in_index 1 | collation NULL | cardinality 42550

Laravel查询代码 #

$allKeys = [...]; // 包含大量字符串的数组
$q = MyModel::query();
$q->whereIn('codfabrica', $allKeys);
$q->orWhereIn('codoriginal', $allKeys);
$q->orWhereRaw('MATCH (aplicacao) AGAINST (?)', array($allKeys));

当前查询性能 #

1000条字符串的数组 => 耗时5秒
2500条字符串的数组 => 耗时35秒

请问是否有优化该搜索的方法？我对INDEX不太熟悉，当前索引创建是否正确？
注：表中有42000行数据，总大小20MB，部署在AWS db.t3.small RDS服务器（2核CPU、2GiB内存）上。

一、当前索引的问题 #

• codoriginal索引：基数仅7449，远低于总行数42466，说明字段重复值多，普通索引对whereIn的优化效果有限。
• aplicacao索引：当前是普通索引，但mediumText类型的普通索引无法支持全文搜索，且collation为NULL，意味着你的MATCH AGAINST查询完全没用到索引，这是核心性能瓶颈之一。

二、针对性优化方法 #

1. 修复全文搜索索引 #

MATCH (aplicacao) AGAINST (...)必须依赖全文索引才能生效，执行以下SQL创建：

ALTER TABLE 你的表名 ADD FULLTEXT INDEX ft_aplicacao (aplicacao);

注意：MySQL 5.6及以上的InnoDB才支持全文索引，5.6以下仅MyISAM支持；另外Z1719121这类格式的字符串会被全文索引视为完整词汇，符合你的搜索需求。

2. 拆分查询，避免OR导致的索引失效 #

多字段OR查询会让MySQL无法同时利用多个索引，大概率退化为全表扫描。拆分三个独立查询再合并结果：

// 分别查询三个条件的结果
$codfabricaResults = MyModel::whereIn('codfabrica', $allKeys)->get();
$codoriginalResults = MyModel::whereIn('codoriginal', $allKeys)->get();
// 全文搜索需将数组转为空格分隔的字符串
$aplicacaoResults = MyModel::whereRaw('MATCH (aplicacao) AGAINST (?)', [implode(' ', $allKeys)])->get();

// 合并并按主键去重
$merged = $codfabricaResults->merge($codoriginalResults)->merge($aplicacaoResults)->unique('id');

3. 优化whereIn的性能 #

• 拆分大数组：将$allKeys拆分为每500个一组的小批次，分批查询后合并结果，避免大数组带来的性能陡降。
• 使用临时表替代whereIn：针对超大量关键词，先把关键词存入临时表，再用JOIN查询：

// 创建临时表
DB::statement('CREATE TEMPORARY TABLE search_keys (key_value VARCHAR(191) PRIMARY KEY)');
// 批量插入关键词
DB::table('search_keys')->insert(array_map(function($key) {
    return ['key_value' => $key];
}, $allKeys));

// 用JOIN替代whereIn
$codfabricaResults = MyModel::join('search_keys', 'codfabrica', '=', 'search_keys.key_value')->get();
$codoriginalResults = MyModel::join('search_keys', 'codoriginal', '=', 'search_keys.key_value')->get();
// 全文搜索部分不变
$aplicacaoResults = MyModel::whereRaw('MATCH (aplicacao) AGAINST (?)', [implode(' ', $allKeys)])->get();

// 合并去重
$merged = $codfabricaResults->merge($codoriginalResults)->merge($aplicacaoResults)->unique('id');

// 销毁临时表
DB::statement('DROP TEMPORARY TABLE search_keys');

4. 服务器及配置优化 #

• 升级RDS实例：db.t3.small仅2GiB内存，MySQL难以缓存全表，会频繁触发磁盘IO，可临时升级到db.t3.medium（4GiB内存）测试性能。
• 调整MySQL参数：在RDS参数组中修改：
  • innodb_buffer_pool_size：设为内存的50%-70%（比如1GiB），提升数据缓存比例。
  • max_allowed_packet：调大至足够容纳大数组查询的数据包。

5. 数据结构优化 #

如果codfabrica和codoriginal是固定格式的字符串（如前缀+数字）：

• 拆分前缀和数字部分，数字存为INT类型，缩小索引体积，提升查询速度。
• 改用CHAR类型替代VARCHAR（若长度固定），进一步优化索引效率。

内容的提问来源于stack exchange，提问作者anderlaini