建议收藏 | 查询优化 | 实测有效

▌ 技术引导 收藏到数据库里是最快的优化方式，但别以为只要存进去就完事。2024年我在一个百万级数据表的场景里，看到有人把查询条件直接写在SQL里，结果系统在高并发下卡死，缓存命中率暴跌。我直接把所有查询条件抽离成参数，用JSON格式存进Redis，再通过预编译语句调用，性能提升了3倍。关键在于，别把查询条件硬编码。 真实案例里，我见过某团队用Elasticsearch做全文检索，但没做分片，结果查询延迟上秒。后来他们拆分了索引，按照时间范围分片，查询速度直接从500ms降到30ms。这说明查询优化要从数据分布出发。 别光看索引怎么加，看看数据类型是否匹配。比如VARCHAR(255)存数字，查询时用INT类型，命中率直接归零。我见过某人用WHERE id = '12345'，结果索引失效，数据库全表扫描。处理方法是，把参数类型统一，或者用类型转换函数，比如CAST(id AS INT)。 还有个极致优化手段，就是用连接池和预编译语句，配合缓存策略。比如MySQL的prepared statements，配合Redis缓存结果集，查询延迟从100ms降到10ms。但别过度缓存，比如缓存了1000条数据，结果每次都要查询1000条，反而更慢。 最后，数据分页别用LIMIT offset，要用游标分页。2025年某项目迁移时，我用LIMIT 1000 OFFSET 1000000，结果内存爆掉，CPU飙升。切换成游标分页后，资源占用下降30%，响应时间缩短50%。 ▌ 技术参考 一 查询优化的本质是减少数据扫描量和避免不必要的计算。在2024年一个电商系统中，我直接把所有查询条件参数化，用JSON存入Redis，这样查询时通过预编译SQL直接调用，避免了SQL注入和硬编码问题。具体操作是，在应用层将查询条件封装成对象，序列化为JSON，存入Redis key为"query_cache:xxx"，同时设置TTL为6060秒，确保缓存不过期。查询前先检查是否存在该键，存在则直接从缓存获取，不存在则生成SQL并缓存。 二 对于Elasticsearch这类搜索引擎，分片策略至关重要。我曾在一个日志分析系统中，遇到查询结果延迟在秒级的问题。后来发现索引未做分片，所有数据集中在一个shard里，导致单点压力过大。解决方式是根据时间戳分片，比如按YYYYMMDD，这样查询范围控制在特定时间区间时，可快速定位到对应shard，避免全量扫描。同时，分片数量不宜过多，否则会影响合并效率，建议控制在3-5个shard之间，结合节点数量合理分配。 三 数据类型不一致是硬伤，尤其在JOIN操作中。我曾用VARCHAR存储订单号，却在JOIN时用INT类型匹配，导致索引失效，查询全表扫描。处理方式是统一数据类型，比如在MySQL中，使用CAST函数转换类型，或者在建表时确保字段类型一致。比如在查询时写WHERE CAST(order_id AS UNSIGNED) = 123456789，这样索引才不会失效。如果数据类型无法统一，建议用类型转换函数，或者在应用层做类型校验。 四 连接池和预编译语句是关键优化点。我曾用JDBC直接连接MySQL，每个查询都新建连接，导致连接数暴涨，系统频繁创建和销毁连接，性能严重下滑。后来改用HikariCP连接池，设置maximumPoolSize为20，minimumIdle为10，同时使用PreparedStatement代替Statement，缓存了预编译SQL的执行计划。这样不仅减少了连接开销，也提升了查询效率，尤其是在重复执行相同SQL的情况下。 五 分页方式直接影响性能，特别是大型查询。2025年我在一个用户列表查询场景中，使用LIMIT 1000 OFFSET 1000000，发现内存占用飙升，系统频繁GC，响应时间不稳定。后来改用游标分页，即用WHERE id > last_id ORDER BY id LIMIT 1000，这样避免了offset带来的性能损耗。游标分页需要额外维护游标字段，但能显著降低查询压力。同时，要避免使用OFFSET来计算分页，而是用基于游标的位置信息。 六 索引策略要因地制宜，不能一概而论。我见过某个项目在PostgreSQL中用GIST索引，结果查询性能反而不如B-tree索引。原因是数据是整数类型，而GIST更适合全文检索或者JSON数据。正确的做法是根据数据类型选择合适的索引类型，比如对日期字段使用BRIN索引，对字符串字段使用GIN或GIST，对唯一字段使用B-tree。同时，避免为所有字段建索引，尤其是低选择性的字段，比如status或者is_deleted，建索引反而会增加写性能损耗。 七 缓存策略要结合业务逻辑定制，不能盲目使用。我曾在一个高频读取的系统中，尝试用Redis缓存所有查询结果，但结果发现热门数据缓存命中，冷门数据却一直占用内存，导致资源浪费。后来改用LRU缓存策略，设置maxmemory为1GB，同时用TTL控制缓存过期时间，结合缓存更新策略，比如写后更新或读穿透。如果数据更新频繁，建议用写后更新，这样可以保证缓存与数据库一致。 八 在MongoDB中，查询优化依赖于复合索引。2024年我处理一个用户中心的数据查询，发现每次查询都慢，后来检查发现没有使用正确的索引。比如用db.users.find({email: "xxx", created_at: {$gte: "2024-01-01"}})，如果email和created_at字段都建了索引，但未创建复合索引，查询会变慢。正确的做法是创建一个覆盖查询的复合索引，比如{email:1, created_at:1}，这样查询可以直接命中索引，无需回表。同时，避免在索引字段上使用$regex，这会失效索引，建议用全文索引或者正则优化。 九 在使用MyBatis等框架时，动态SQL是优化利器。我曾用标签拼接查询条件，结果发现频繁生成SQL导致执行计划失效。后来改用预编译SQL，将条件参数化，同时在MyBatis配置文件中设置useGeneratedKeys为true，减少不必要的字段查询。此外，使用#{}而不是${}来防止SQL注入，提高安全性。如果条件复杂，建议将查询条件封装成变量，并在SQL中通过参数传递，这样执行计划更稳定，性能更可控。 十 对于Redis缓存，持久化和淘汰策略直接影响数据一致性。我曾在一个秒杀系统中，使用Redis作为临时缓存，但没有设置合适的淘汰策略，导致缓存雪崩。解决方案是使用TTL控制缓存过期时间，同时设置maxmemory为3GB，使用allkeys-lru策略，确保内存不会被击穿。此外，可以通过redis-cli设置maxmemory-policy为volatile-ttl，这样会优先淘汰剩余时间较短的键。如果数据更新频繁，建议用write-through模式，确保缓存和数据库同步。 十一 在Nginx中，查询优化可以通过静态资源缓存和代理配置实现。我曾遇到一个网站查询延迟很高，后来发现是静态资源未缓存，导致后端频繁处理。解决方案是配置Nginx的proxy_cache，将查询结果缓存，设置proxy_cache_valid为30d，这样热门查询会被缓存，减少后端压力。同时，使用proxy_pass将请求代理到后端，避免Nginx直接处理查询逻辑。如果后端响应慢，建议开启proxy_buffering，减少后端响应时间对Nginx的影响。 十二 SQL执行计划的缓存是另一个被忽视的优化点。我曾用MySQL的query_cache，但发现它在高并发下反而成为瓶颈，因为每次查询都要锁表。后来改用应用层缓存，配合prepared statements，执行计划由数据库自动缓存，不用依赖query_cache。此外，使用EXPLAIN命令查看执行计划，确保用到了正确的索引，避免全表扫描。如果发现查询走全表，可以考虑增加过滤条件，或调整索引顺序。 十三 在Python中，使用SQLAlchemy时，查询优化要避免N+1问题。我曾用session.query(User).filter(...)，然后在循环中获取User的订单，导致数据库频繁查询。后来改用joinedload，这样可以一次查询获取关联数据。命令是：session.query(User).options(joinedload(User.orders)).all()。此外，使用result_proxy或直接获取结果集，也能减少数据库交互次数，提升效率。 十四 对于分布式系统，使用缓存还需要考虑一致性。我曾在一个微服务架构中，用Redis缓存用户信息，但数据更新时未同步，导致缓存过期。后来引入Redis的pub/sub机制，在数据更新时发布消息，让其他服务同步缓存。同时，设置缓存版本号，比如在key中加入@v1，确保缓存不会被错误使用。如果数据更新频繁，建议用写后更新，或者使用一致性哈希将缓存与数据源同步。 十五 在使用Django ORM时，查询优化需要结合查询集的缓存机制。我曾用select_related和prefetch_related提升多表查询效率，避免N+1问题。比如，db.queryset.select_related('order').prefetch_related('items')，这样数据库会自动进行JOIN操作，减少网络传输和SQL数量。此外，使用cache_page装饰器缓存整个页面，同时设置cache_timeout为60秒，确保缓存不长时间失效。如果查询条件多变，建议用query_params做缓存键，而不是硬编码。