小（200行）与大（20亿行）数据库SELECT及UPDATE最优查询咨询

基于两种场景：小型数据库（200行数据）、大型数据库（20亿行+每日新增），结合表结构（ID为主键，code、x_key为非复合索引，group列g5分组占20%数据），给出如下最优方案：

一、小型数据库（200行数据） #

1. 单条数据查询最优选择 #

优先选择：SELECT name,title FROM table WHERE code=? AND x_key=? LIMIT 1

• 原因：数据量极小，全表扫描成本可忽略，但code、x_key为索引，组合条件能精准缩小范围，LIMIT 1让数据库找到匹配行后立即终止查询，进一步缩短响应时间。
• 若code本身可唯一确定单条数据，简化为SELECT name,title FROM table WHERE code=? LIMIT 1即可。

2. 多条数据查询最优选择 #

• 仅按group查询：SELECT name,title FROM table WHERE group=?
  无需额外优化，数据量小，全表扫描或索引（若后续给group加索引）效率差异可忽略。
• 带模糊查询：SELECT name,title FROM table WHERE group=? AND name LIKE '%test'
  前缀模糊匹配无法利用name索引，但数据量小，直接执行即可，无需额外处理。

二、大型数据库（20亿行+每日新增） #

1. 单条数据查询最优选择 #

优先选择：SELECT name,title FROM table WHERE code=? AND x_key=? LIMIT 1

• 原因：
  1. 仅用code=?可能返回大量行（无唯一约束），数据库需扫描大量索引条目后回表；
  2. 组合code+ x_key条件，可利用两个单索引的联合过滤（数据库会选择代价更低的索引执行），大幅缩小扫描范围；
  3. LIMIT 1强制数据库找到第一条匹配行就停止，避免不必要的扫描，对超大库性能提升显著。
• 若需限定group，可追加AND group=?，但因group无索引，仅在code+ x_key返回行极少时追加，否则会增加回表过滤成本。

2. 多条数据查询最优选择 #

• 仅按group查询：
  必须先给group列添加单索引，再执行SELECT name,title FROM table WHERE group=?
  原因：g5分组占20%（约4000万行），无索引时全表扫描会耗时极久，添加索引后可直接定位分组数据，大幅降低扫描行数。
• 带模糊查询：
  先给group列添加索引，再执行SELECT name,title FROM table WHERE group=? AND name LIKE '%test'
  原因：name LIKE '%test'无法利用name索引，需先通过group索引过滤出目标分组的行，再在小范围内过滤name，比全表扫描效率高得多。

三、UPDATE操作最优方案 #

小型数据库 #

无特殊限制，直接用精准条件定位即可，比如：
UPDATE table SET col1=?, col2=? WHERE code=? AND x_key=?
数据量小，锁表或性能影响可忽略。

大型数据库 #

1. 单条/少量行更新：
   优先用主键ID定位，比如：UPDATE table SET col1=?, col2=? WHERE ID=?
   主键为聚簇索引（如InnoDB），可直接定位到行，无需扫描索引或回表，是最快的更新方式；若无法用ID，用code+ x_key组合条件（建议给两者建复合索引），避免扫描大量数据。
2. 批量行更新：
   比如更新group=g5的大量行，需分批更新，每次更新1000-10000行，示例：

   UPDATE table SET col1=? WHERE group='g5' AND ID > ? LIMIT 1000;
   

   循环执行直到更新完成，避免一次性锁表过多行，影响数据库并发性能；同时避免超大事务，减少日志写入压力。

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