提速超大规模MyISAM表SELECT DISTINCT操作求助

一、系统工具直接导出+去重（最快方案） #

MyISAM的表数据存储在独立.MYD文件中，用mysqldump直接导出指定字段的效率远高于SQL查询：

• 导出symbol字段到文本文件：

  mysqldump -u [用户名] -p [数据库名] bigboytable --no-create-info --fields-terminated-by='\t' --columns=symbol > symbol_raw.txt
  

• 用系统命令去重（Linux/macOS环境）：

  sort -u symbol_raw.txt > unique_symbols.txt
  

sort -u依赖外部排序机制高效处理大文件，12G原始文件处理后仅生成几十MB的去重结果，全程耗时远低于数据库查询。

二、Python分批扫描+本地去重 #

适合需要对结果做额外逻辑处理的场景，避免一次性全表扫描超时：

1. 先获取表的主键范围（假设主键为id，无主键则用其他有序字段）：

   SELECT MIN(id), MAX(id) FROM bigboytable;
   

2. 用Python的pymysql或mysql-connector分批拉取数据，借助集合自动去重：

   import pymysql
   
   conn = pymysql.connect(host='localhost', user='[用户名]', password='[密码]', db='[数据库名]')
   cursor = conn.cursor()
   
   min_id, max_id = 0, 300000000  # 替换为实际查询到的范围
   batch_size = 1000000  # 每批拉取100万行，根据服务器内存调整
   unique_symbols = set()
   
   for start in range(min_id, max_id + 1, batch_size):
       end = min(start + batch_size - 1, max_id)
       cursor.execute("SELECT symbol FROM bigboytable WHERE id BETWEEN %s AND %s", (start, end))
       # 逐行读取避免内存溢出
       for row in cursor:
           unique_symbols.add(row[0])
   
   # 导出结果
   with open('unique_symbols.txt', 'w') as f:
       for sym in unique_symbols:
           f.write(f"{sym}\n")
   
   cursor.close()
   conn.close()
   

3. 注意：若无自增主键，避免使用LIMIT offset, size（offset过大时性能骤降），可改用ORDER BY symbol LIMIT size分批，需记录最后一条symbol的值作为下一批的起始条件。

三、临时表分批聚合去重 #

利用临时表的唯一约束自动去重，避免全表聚合超时：

1. 创建临时表（指定symbol的实际类型和长度）：

   CREATE TEMPORARY TABLE temp_symbols (
       symbol VARCHAR(64) PRIMARY KEY  -- 替换为symbol字段的实际定义
   ) ENGINE=MyISAM;
   

2. 分批插入数据，INSERT IGNORE会自动跳过重复值：

   -- 示例：按id分批，每次插入100万行
   INSERT IGNORE INTO temp_symbols SELECT symbol FROM bigboytable WHERE id BETWEEN 1 AND 1000000;
   INSERT IGNORE INTO temp_symbols SELECT symbol FROM bigboytable WHERE id BETWEEN 1000001 AND 2000000;
   -- 循环执行直到所有数据处理完成
   

3. 最后提取去重结果：

   SELECT symbol FROM temp_symbols;
   

四、优化建索引操作（若后续需频繁查询） #

如果必须为symbol建索引，先调整MySQL参数再重试：

1. 修改my.cnf（或my.ini）的内存参数：

   key_buffer_size = 2G  -- 设为物理内存的1/4左右，最多不超过4G
   sort_buffer_size = 64M
   read_buffer_size = 16M
   

2. 重启MySQL服务后，重新执行建索引命令：

   ALTER TABLE bigboytable ADD INDEX idx_symbol (symbol);
   

建索引期间避免对表执行读写操作，MyISAM建索引会锁表，内存充足的情况下速度会大幅提升。

五、调整MySQL超时参数避免连接断开 #

若仍需使用复杂查询，临时调整会话级超时参数：

SET SESSION wait_timeout = 86400;
SET SESSION interactive_timeout = 86400;
SET SESSION net_read_timeout = 3600;
SET SESSION net_write_timeout = 3600;

也可修改my.cnf永久调整，重启后生效。

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