如何提升MySQL搜索速度？Django站点慢查询优化咨询

一、索引优化方案 #

1. 前缀索引（针对前缀匹配的LIKE查询） #

如果你的LIKE查询是前缀匹配（如title LIKE 'xxx%'），可以给字符串字段创建前缀索引，避免全表扫描。

• 创建索引示例：

  ALTER TABLE content ADD INDEX idx_title_prefix (title(50));
  

• 前缀长度选择：先通过以下语句评估区分度，尽量选能接近1的长度，平衡索引大小和查询效率：

  SELECT COUNT(DISTINCT LEFT(title, 50))/COUNT(*) AS distinct_rate FROM content;
  

2. 全文索引（针对自然语言模糊查询） #

MySQL自带的全文索引比LIKE '%xxx%'高效数倍，适合标题、描述这类文本字段的关键词搜索。

• 创建联合全文索引示例：

  ALTER TABLE content ADD FULLTEXT INDEX idx_ft_title_desc (title, description);
  

• 查询示例（Django中可直接用SearchVector/SearchQuery实现，无需手写SQL）：

  SELECT id, title FROM content WHERE MATCH(title, description) AGAINST('目标关键词' IN NATURAL LANGUAGE MODE);
  

  注意：InnoDB需MySQL 5.6+支持全文索引，若需中文搜索，可先将中文分词后存储，或使用第三方分词插件适配。

3. JSON字段索引优化 #

如果查询涉及JSON内部字段，可通过虚拟列+索引的方式优化：

• 先创建JSON字段的虚拟列：

  ALTER TABLE content ADD COLUMN json_tag VARCHAR(255) GENERATED ALWAYS AS (content->>'$.tag') STORED;
  

• 给虚拟列创建索引：

  ALTER TABLE content ADD INDEX idx_json_tag (json_tag);
  

• 查询时直接用虚拟列替代JSON路径，即可命中索引：

  SELECT id FROM content WHERE json_tag LIKE 'xxx%';
  

二、查询语句优化 #

• 避免前后通配的LIKE '%xxx%'：这种写法会直接跳过所有索引，强制全表扫描。如果业务允许，尽量改成前缀匹配，或切换为全文索引。
• 只查询必要字段：禁用SELECT *，只取业务需要的字段（比如id、title），减少磁盘IO和内存占用。Django中可使用only()方法：

  Content.objects.filter(title__startswith='xxx').only('id', 'title')
  

• 优化分页查询：避免使用LIMIT offset, size（偏移量过大时会扫描大量数据），改用基于主键的游标分页：

  # 假设上一页最后一条数据的id是last_id
  Content.objects.filter(id__gt=last_id, title__startswith='xxx').only('id', 'title')[:10]
  

三、数据库配置调整 #

• 合理分配InnoDB缓冲池：即使内存有限，也尽量将innodb_buffer_pool_size设为物理内存的50%-70%（比如2G内存设为1G），让更多热点数据缓存到内存，减少磁盘读写。
• 开启表压缩：对于存储大量text/JSON的表，启用InnoDB压缩可大幅减少磁盘占用，提升IO效率：

  ALTER TABLE content ROW_FORMAT=COMPRESSED;
  

• 定期整理表碎片：低峰期执行OPTIMIZE TABLE content;，整理表碎片，减少.myd文件体积（注意执行时会锁表）。

四、数据结构优化 #

• 拆分大字段：将text、JSON这类大字段拆分到单独的关联表（比如content_detail），与主表content通过id一对一关联。查询主表时只加载小字段，需要详情再关联查询，提升主表的缓存和查询效率。
• 清理冗余数据：检查是否有重复、过期的数据，定期清理，减少表的整体数据量。

五、Django层面优化 #

• 缓存高频查询结果：用Django缓存框架将常用查询结果缓存（内存/文件均可），避免重复查询数据库：

  from django.core.cache import cache
  
  def get_content_list(keyword):
      cache_key = f"content_search_{keyword}"
      result = cache.get(cache_key)
      if not result:
          result = list(Content.objects.filter(title__startswith=keyword).only('id', 'title'))
          cache.set(cache_key, result, 300)  # 缓存5分钟
      return result
  

• 避免N+1查询：如果存在外键关联，使用select_related/prefetch_related批量加载关联数据，减少数据库请求次数。

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