mysql like中文慢

MySQL 中使用 LIKE 查询中文数据时，查询速度可能会较慢，主要原因包括字符集、索引效率、全表扫描等因素。以下是优化建议和解决方案：

​​1. 检查字符集和排序规则​​

确保 MySQL 数据库、表和字段的字符集支持中文（如 utf8mb4），并使用合适的排序规则（如 utf8mb4_general_ci 或 utf8mb4_unicode_ci）。

-- 查看数据库字符集
SHOW CREATE DATABASE your_database;

-- 查看表字符集
SHOW CREATE TABLE your_table;

-- 修改表的字符集（如果需要）
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

​​注意​​：

• utf8mb4 比 utf8 支持更完整的 Unicode（如 Emoji）。
• 排序规则影响索引效率，utf8mb4_general_ci 比 utf8mb4_unicode_ci 更快，但排序可能不精确。

​​2. 使用前缀索引优化 LIKE 'xxx%' 查询​​

如果查询模式是 LIKE 'abc%'（前缀匹配），可以创建前缀索引：

-- 创建前缀索引（适用于 VARCHAR 类型）
ALTER TABLE your_table ADD INDEX idx_name_prefix (name(10)); -- 10 是前缀长度

​​适用场景​​：

• LIKE 'abc%'（前缀匹配）可以利用索引。
• LIKE '%abc' 或 LIKE '%abc%' 无法利用索引，仍然会全表扫描。

​​3. 使用全文索引（FULLTEXT）​​

如果查询模式是模糊匹配（如 LIKE '%关键词%'），可以使用 MySQL 的 ​​FULLTEXT 索引​​（适用于 InnoDB 和 MyISAM 引擎）。

​​（1）创建 FULLTEXT 索引​​

ALTER TABLE your_table ADD FULLTEXT INDEX idx_fulltext_name (name);

​​（2）使用 MATCH ... AGAINST 查询​​

SELECT * FROM your_table 
WHERE MATCH(name) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

​​注意​​：

• FULLTEXT 适用于较长的文本搜索，对短词（如 1-2 个字符）可能不敏感。
• 可以调整 innodb_ft_min_token_size（默认 3）和 ft_min_word_len（默认 4）来优化。

​​4. 使用 N-gram 分词（适用于中文全文搜索）​​

MySQL 的 FULLTEXT 对中文支持较差，可以使用 ​​N-gram 分词​​（需 MySQL 5.7+）。

​​（1）修改 MySQL 配置​​

在 my.cnf 或 my.ini 中添加：

[mysqld]
ngram_token_size=2  # 设置 N-gram 分词的最小长度（默认 1）

​​（2）创建 N-gram FULLTEXT 索引​​

ALTER TABLE your_table ADD FULLTEXT INDEX idx_ngram_name (name) WITH PARSER ngram;

​​（3）查询​​

SELECT * FROM your_table 
WHERE MATCH(name) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

​​优点​​：

• 对中文分词更友好，支持短词搜索。
• 比 LIKE '%关键词%' 快很多。

​​5. 使用外部搜索引擎（如 Elasticsearch）​​

如果数据量很大，MySQL 的全文搜索性能可能仍然不足，可以考虑：

• ​​Elasticsearch​​（推荐）
• ​​Sphinx​​
• ​​Solr​​

​​示例（Elasticsearch）​​：

1. 安装 Elasticsearch 并配置中文分词插件（如 ik_smart）。
2. 将 MySQL 数据同步到 Elasticsearch。
3. 使用 Elasticsearch 的 match 查询：{ "query": { "match": { "name": "关键词" } } }

​​6. 优化查询方式​​

​​（1）避免 LIKE '%xxx%'​​

• LIKE '%xxx%' 无法使用索引，只能全表扫描。
• 如果必须使用，考虑缓存结果或使用外部搜索引擎。

​​（2）使用 LIKE 'xxx%' 并加索引​​

-- 如果查询是 LIKE 'abc%'，可以加前缀索引
SELECT * FROM your_table WHERE name LIKE 'abc%';

​​（3）使用 REGEXP 替代（谨慎使用）​​

SELECT * FROM your_table WHERE name REGEXP '关键词';

​​注意​​：REGEXP 性能较差，仅适用于复杂匹配。

​​7. 其他优化建议​​

• ​​减少返回字段​​：只查询需要的列，避免 SELECT *。
• ​​分页查询​​：使用 LIMIT 减少数据量。
• ​​缓存结果​​：对高频查询使用 Redis 缓存。
• ​​升级 MySQL 版本​​：新版本（如 8.0）对全文搜索有优化。