零基础 | 慢查询优化：架构设计原则

▌ 技术引导 零基础做慢查询优化，别想着去跟数据库厂商扯皮，你得从架构设计上下手。慢查询的根源在于数据读写路径不清晰，索引设计不合理，缓存机制失效。你可以直接在应用层加个拦截器，把SQL语句过滤出来，再通过日志分析工具定位哪些语句耗时。实践过很多次，这种做法比埋点更靠谱。比如用sysbench压测，跑了半小时发现90%的慢查询都是全表扫描，这时候就得考虑是不是索引缺失，或者查询条件不完整。别用explain搞花活，直接上profile，看真实执行计划。还有，分布式数据库下慢查询可能跨节点，得用监控工具聚合数据。记住，慢查询优化不等于调优SQL，核心是架构设计。我见过用Redis做缓存，结果缓存失效后直接冲垮MySQL，这得提前预判。 在数据分片的时候，别想太复杂，直接按日期或ID哈希分片，数据少点就容易定位问题。如果数据量还小，可以考虑在应用层用内存缓存，比如Guava Cache，这样根本不需要动数据库。不过别忘了，缓存是把双刃剑，过期策略和淘汰算法要搞清楚。我之前用Guava做缓存，结果因为没设置合适的expireAfterWrite，导致内存爆掉。直接上JVM监控工具，看堆内存变化。再比如，有些慢查询是因为锁等待，这时候要看看是不是事务设计有问题，还是同一个SQL被多个线程重复执行。真正的优化点往往藏在架构层，而不是SQL层。 慢查询的优化不只是加索引这么简单，索引多了反而会拖慢写性能。记得有一次，为了优化查询速度，我硬生生在一张表上加了十几个索引，结果写入延迟直接翻了三倍。这时候得用索引统计工具，比如pg_stat_statements或者MySQL的information_schema，分析哪些索引被频繁使用。再看查询条件是不是有where子句的字段，或者有没有使用join操作。还有，数据库连接池配置不合理也会导致慢查询，比如maxPoolSize调太小，频繁连接数据库。这种情况下，直接上连接池监控工具，看连接创建和销毁次数，再调整参数。 架构设计上，一定要分清楚主库和从库的角色，别让从库去执行复杂查询。我见过一个案例，主库写入压力大，结果从库因为要处理慢查询，导致整个集群负载过高。这时候需要把慢查询分发到专门的读库，或者用Elasticsearch做数据聚合，把查询压力从数据库甩出去。对于高并发场景，可以考虑用分库分表，或者用ShardingSphere做中间层，直接在应用层路由查询。不过分库分表也不是万能，得看数据量和访问模式，别一上来就搞分布式，搞砸了更麻烦。 还有个关键点，慢查询优化不能只盯着单条SQL，得看整个查询链路。比如，前端请求进来，中间框架做了过多的预处理，导致本来可以简单的查询变得复杂。这时候得用APM工具，比如SkyWalking或Zipkin，看请求路径，再逐步拆解。别想着一口气解决所有问题，先找到最耗时的模块，再针对性优化。我之前用OpenTelemetry做追踪，发现一个查询在应用层耗时500ms，结果查了才发现是框架默认加载了太多数据。这时候改配置，关闭不必要的加载，直接省下一半时间。 ▌ 技术参考 一 慢查询优化的核心是减少数据读取路径的复杂度，而不是单纯调整SQL。对于零基础开发者来说，第一步是搞清楚哪些查询是慢的。使用MySQL的slow query log，开启long_query_time为1秒，然后定期分析日志。使用pt-query-digest工具，可以将日志转为统计图表，看到哪些语句执行次数多，耗时长。比如执行 `pt-query-digest /path/to/slow.log`，输出结果中有query_time、lock_time、rows_sent这些指标，直接聚焦这些字段。 二 在架构设计上，必须明确分层。比如，把业务逻辑层和数据访问层分离，这样可以在业务层做缓存，在数据层做索引。使用Spring Boot的AOP，拦截所有SQL语句，在拦截器中记录执行时间，再用ELK做日志收集。比如在切面写： ```java @Around("execution( com.example.mapper..(..))") public Object around(ProceedingJoinPoint pjp) throws Throwable { long start = System.currentTimeMillis(); Object result = pjp.proceed(); long end = System.currentTimeMillis(); log.info("SQL execution time: {}ms", end - start); return result; } ``` 这样可以在不改动业务代码的情况下，抓取慢查询。 三 慢查询的常见场景是全表扫描，这时候得看索引是否覆盖查询字段。用explain分析SQL时，注意type字段，如果是ALL，那就肯定没索引。比如执行 `EXPLAIN SELECT FROM orders WHERE user_id = 123;`，然后看key是否为用户ID。如果没有索引，就考虑加索引，或者用分区表，把数据按时间分片。比如在MySQL中对orders按create_time分区，执行 `ALTER TABLE orders PARTITION BY RANGE (YEAR(create_time))`。这样分区表能提升查询效率。 四 慢查询可能是因为连接池配置不合理，导致频繁重建连接。检查连接池最大连接数是否设置过小，比如HikariCP的maximumPoolSize。如果这个值不够，会看到大量的等待连接，这时候得调高这个参数。同时，用JConsole或Arthas监控连接池状态，看是否出现连接泄露。比如执行 `jstack ` 查看线程状态，发现有大量Waiting on connection，就说明连接池不够。 五 当慢查询出现在分布式数据库中，比如TiDB，这时候得看查询是否跨节点。使用TiDB的`EXPLAIN`语句，看是否出现`Merge`或者`HashJoin`，这些操作会拖慢整体速度。比如执行 `EXPLAIN SELECT FROM orders JOIN users ON orders.user_id = users.id;`，如果查询计划中有`Merge`，说明需要优化join策略。可以考虑使用聚合中间表，或者调整分片策略，让join发生在同一个节点。 六 在Redis缓存设计上，别把所有查询都缓存起来，缓存失效策略要合理。比如使用TTL过期时间，或者基于访问频率的淘汰策略。用Redis的Lua脚本做缓存更新，避免并发问题。比如写一个脚本： ```lua local key = KEYS[1] local value = redis.call('get', key) if value then return value else local data = redis.call('get', 'orders:all') redis.call('set', key, data) return data end ``` 这种脚本可以避免缓存穿透，同时减少数据库压力。 七 使用连接池的时候，不要把connectionTimeout设得过低，否则会报错。设置成1000ms左右，给数据库足够时间响应。同时，设置maximumPoolSize为100到200之间，避免连接数爆炸。比如在HikariCP中配置： ```yaml spring: datasource: hikari: maximumPoolSize: 150 connectionTimeout: 1000 idleTimeout: 60000 ``` 这种配置可以提升稳定性，同时不影响性能。 八 对于慢查询的监控，可以使用Prometheus + Grafana组合。在MySQL中开启performance_schema，然后用exporter采集指标。比如配置MySQL exporter的监控端口，再通过Prometheus抓取，最后用Grafana展示。这样可以实时看到慢查询的分布情况，比如每个时间点的慢查询数量。 九 在Java项目中，使用装饰器模式包装DAO层，直接拦截SQL执行时间。比如用Spring AOP实现，而不是在业务层埋点。这样能避免业务逻辑改动，同时快速定位慢查询。比如在配置中添加： ```java @EnableAspectJAutoProxy @Configuration public class AopConfig { @Bean public AdviceConfig adviceConfig() { return new AdviceConfig(); } } ``` 然后在Advice类中写逻辑。 十 慢查询的另一种场景是排序操作，这时候要检查是否使用了覆盖索引。比如执行 `SELECT id, name FROM users ORDER BY name`，如果name字段有索引，那么直接可以命中，不需要回表。否则，会进行全表扫描，再排序，这样效率极低。可以使用explain看key_used是否包含排序字段。 十一 在Elasticsearch中优化查询，不要用wildcard搜索，因为性能差。使用term查询代替match查询，如果字段是keyword类型。比如写 `{"query": {"term": {"status": "active"}}}` 而不是 `{"query": {"match": {"status": "active"}}}`。对于高并发的查询，可以设置分片数，比如在索引创建时设置number_of_shards为3，这样查询能并行处理。 十二 数据库调优时，使用分区表能有效减少查询范围。比如在PostgreSQL中，对时间字段按year分区，执行 `CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31')`。这样每次查询都只扫描当年的数据，提升性能。 十三 使用连接池时，可以设置maxLifetime参数，避免数据库连接长时间无效。比如在HikariCP中设置 `maxLifetime: 1800000`，也就是30分钟。这样能减少连接泄露的问题，同时保证连接处于可用状态。 十四 对于慢查询的替代方案，可以考虑使用CQRS模式，把查询和写入分离。比如用Kafka做数据同步，再用Elasticsearch做聚合查询。这样查询操作完全从数据库中剥离，效率提升明显。 十五 在分布式架构中，不要把所有查询都丢到主库，应该用读写分离。比如用ShardingSphere做中间件，将写操作分发到主库，读操作分发到从库。这样能有效缓解主库压力，同时查询效率提升。配置时注意使用`read-write-splitting`模块，设置主库和从库的权重，避免从库负载过高。