Sphinx索引查询结果加载过慢，求查询及配置优化方案

问题背景 #

• 处理含100,821,614行数据的tbl_domain_urls表（字段：domain_id、url）
• 需求：按关键词统计各domain_id对应的URL数量
• 当前查询耗时超3分钟，已尝试分布式索引无明显效果

现有索引配置 #

source src2
{
  type                  = mysql
  sql_host              = 0.0.0.0
  sql_user              = spnx
  sql_pass              = 123
  sql_db                = db_spnx
  sql_port              = 3306 # optional, default is 3306
  sql_query             = select id,domain_id,url from tbl_domain_urls
  sql_attr_uint         = domain_id
  sql_field_string      = url
}
index url_tbl
{
    source   = src2
    path     =/var/lib/sphinx/data/url_tbl
}
indexer
{
    mem_limit               = 2047M
}
searchd
{
    listen                  = 0.0.0.0:9312
    listen                  = 0.0.0.0:9306:mysql41
    listen                  = /home/charlie/sphinx-3.4.1/bin/searchd.sock:sphinx
    log                     = /var/log/sphinx/sphinx.log
    query_log               = /var/log/sphinx/query.log
    read_timeout            = 5
    max_children            = 30
    pid_file                = /var/run/sphinx/sphinx.pid
    max_filter_values       = 20000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
    workers                 = threads # for RT indexes to work
    binlog_path             = /var/lib/sphinx/data
    max_batch_queries       = 128
}

当前查询及耗时 #

SELECT domain_id,count(*) as url_counter
FROM url_tbl WHERE MATCH('games')
group by domain_id limit 1000000 OPTION max_matches=1000000;show meta;

查询结果：

+-----------+-------+
| domain_id | url   |
+-----------+-------+
|      9900 |   444 |
|     41309 |    48 |
|     62308 |   491 |
|     85798 |   401 |
|       595 |  4851 |


13545 rows in set (3 min 22.56 sec)

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| total         | 13545  |
| total_found   | 13545  |
| time          | 1.406  |
| keyword[0]    | games  |
| docs[0]       | 456667 |
| hits[0]       | 514718 |
+---------------+--------+

服务器配置 #

HP Proliant 2xL5420、16GB内存、2x1TB HDD

优化方案 #

1. 优化查询语句 #

• 移除不必要的limit 1000000，实际结果仅13545行，多余的limit会导致Sphinx预留不必要的内存，增加开销
• 调整max_matches为匹配结果的合理上限，比如OPTION max_matches=20000，避免内存浪费
• 添加group_sort_mode=none参数，跳过分组后的排序步骤（若无需特定排序顺序），减少计算耗时：

  SELECT domain_id, COUNT(*) AS url_counter 
  FROM url_tbl 
  WHERE MATCH('games') 
  GROUP BY domain_id 
  OPTION max_matches=20000, group_sort_mode=none;
  

2. 调整索引配置提升效率 #

• 修改searchd配置中的preopen_indexes = 1，让searchd启动时预打开所有索引，减少查询时的磁盘IO开销（HDD磁盘IO性能较弱，预打开可降低随机IO频率）
• 在searchd中新增max_groupby_values = 20000，确保分组结果能完整返回，避免截断或额外处理
• 将sql_attr_uint = domain_id替换为sql_attr_hash_uint = domain_id，哈希属性能大幅提升分组统计的计算速度
• 提升indexer的mem_limit至8192M（服务器有16GB内存，分配一半给索引构建，减少磁盘IO，加速索引生成）
• 将searchd的workers改为fork，对于非RT索引的批量查询，fork模式比threads更稳定高效

修改后的关键配置片段：

indexer
{
    mem_limit               = 8192M
}
searchd
{
    # ... 其他原有配置 ...
    preopen_indexes         = 1
    max_groupby_values      = 20000
    workers                 = fork
}
source src2
{
    # ... 其他原有配置 ...
    sql_attr_hash_uint      = domain_id
    sql_field_text          = url  # 替换sql_field_string，text字段更适合大文本全文检索
}

3. 分布式索引优化（重新调整拆分策略） #

之前的分布式索引无效果可能是拆分方式不合理，建议按domain_id哈希值拆分索引：

• 将原索引拆分为4个独立索引，每个索引对应不同哈希范围的domain_id，例如：

  source src2_1
  {
      # ... 原有src2配置 ...
      sql_query             = select id,domain_id,url from tbl_domain_urls WHERE MOD(domain_id,4)=0
  }
  source src2_2
  {
      # ... 原有src2配置 ...
      sql_query             = select id,domain_id,url from tbl_domain_urls WHERE MOD(domain_id,4)=1
  }
  # 同理创建src2_3、src2_4
  

• 创建分布式索引统一管理：

  index url_tbl_dist
  {
      type = distributed
      local = url_tbl_1
      local = url_tbl_2
      local = url_tbl_3
      local = url_tbl_4
  }
  

• 查询时使用分布式索引url_tbl_dist，让Sphinx并行处理多个子索引的分组统计，提升整体速度

4. 服务器层面优化 #

• 确保HDD磁盘使用RAID 10（若未配置），提升磁盘读写性能
• 调整系统内核参数，增加文件句柄数和内存页缓存，减少磁盘IO等待：

  echo "fs.file-max = 65535" >> /etc/sysctl.conf
  echo "vm.swappiness = 10" >> /etc/sysctl.conf
  sysctl -p
  

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