1. 查询方式的整体设计与思路拆解前两天在梳理项目里的数据访问层突然觉得“查询”这件事特别值得拿出来聊一聊。可能有人觉得查询嘛无非就是写个SQL、调个接口、返回结果有什么好总结的但真当你把线上业务的查询链路拉出来看一遍你就会发现查询方式的选择直接决定了这个系统能扛多大流量、出问题时好不好排查、后续迭代是轻松还是痛苦。我这篇文章想做的就是把常见的技术领域中那些查询方式整合起来聊一遍包括SQL查询、JSON数据查询、组合查询、缓存查询、分页查询、慢查询排查与优化以及查询安全。不搞那种教科书式的罗列而是站在实际项目角度去拆解每种查询方式解决什么问题、底层怎么运作、用的时候有哪些坑。这里需要先捋清楚一个核心问题为什么会有这么多种查询方式而不是一套方案打天下答案很简单因为数据形态不一样访问场景不一样性能和一致性要求也不一样。比如你查一个用户资料数据存在关系型数据库里行数不多直接用主键查就行但你要在几千万条订单流水里做多条件筛选还要支持模糊搜索商品名称这个时候简单的SELECT * FROM table WHERE name LIKE %xxx%就会把数据库拖垮你得考虑全文索引、搜索引擎或者干脆上ES。再比如查一个配置项每次请求都要读一次数据库这个开销完全可以省掉那就可以引入缓存查询。所以说查询方式本质上是对“怎么从数据里把想要的东西拿回来”这个问题的不同求解策略每种策略背后都有它的适用场景和代价。一个合格的后端或者数据开发应该具备的能力不是记住某个查询语法而是在面对业务需求时能快速判断用哪种查询方式性价比最高。我在实际设计查询逻辑的时候一般会按三个维度来做决策数据放在哪MySQL、Redis、Elasticsearch、ClickHouse还是接口对端的数据源查询条件长什么样等值查询、范围查询、模糊查询、多字段组合、嵌套结构查询对延迟和一致性的容忍度能接受缓存脏读吗允许查询结果有毫秒级延迟吗把这三个维度想清楚再去选具体的查询方式思路就会清晰很多。接下来我按场景分类把主流的查询方式逐个拆开讲。2. SQL查询从基础语法到执行顺序2.1 单表查询与条件过滤的正确写法关系型数据库里的查询最常用的就是SQL。很多人写SQL属于“能跑就行”但查询效率和对索引的利用程度差别巨大。以MySQL为例一个最简单的查询SELECT id, user_name, mobile FROM user_info WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;普通人看这句SQL觉得平平无奇但懂行的人会先看三样东西查询的字段有没有被索引覆盖WHERE条件里的字段是不是都走得了索引排序字段和limit有没有让MySQL走filesort甚至临时表。这里有个特别容易被忽略的点查询字段的选择。很多人习惯SELECT *图省事。但如果你只需要id和name两个字段SELECT *会把所有列都捞出来尤其那种字段特别宽的表每行多出来的字节数累加起来就是巨大的IO开销。正确做法是只查需要的字段同时尽量让查询字段覆盖在索引里这样就能走覆盖索引直接从句柄索引里拿数据连回表都省了。WHERE条件的写法也有讲究。我们常说“能走到索引的条件是查询优化的第一优先级”前提是你别破坏索引结构。比如在索引字段上做函数运算、隐式类型转换、前导通配符模糊匹配都会让索引失效这一点后面单独说。2.2 多表关联与子查询的执行逻辑多表查询是业务里的常态常见的关联方式有INNER JOIN、LEFT JOIN、RIGHT JOIN子查询则有IN、EXISTS、派生表等写法。很多新人分不清什么时候用JOIN、什么时候用子查询更看不出两者在性能上的差异。简单说JOIN是“横向拼接”它把两张表的关联结果放在一行里返回而子查询经常是“纵向过滤”它先算出一批结果集再去匹配外层主查询。-- 关联查询查用户及其最近一单订单 SELECT u.id, u.user_name, o.order_no, o.amount FROM user_info u LEFT JOIN order_info o ON o.user_id u.id AND o.pay_status 1 WHERE u.status 1; -- 子查询查有支付成功订单的用户 SELECT id, user_name FROM user_info WHERE id IN (SELECT user_id FROM order_info WHERE pay_status 1);两条SQL的业务语义不一样不能简单说谁好谁坏。但从执行层面看MySQL优化器对IN子查询的优化策略一直在变化MySQL 5.6以后会把半连接semi-join优化做得更聪明某些场景下IN子查询的执行计划比JOIN更好但某些场景下JOIN又能避免重复扫描。结论是不要凭直觉写把两条SQL都EXPLAIN跑一遍看扫描行数和查询代价。2.3 SQL执行顺序与优化器行为这是SQL查询里最值得聊透的一件事SQL语句书写的顺序和它实际被执行的顺序完全是两码事。我遇到过很多人以为SQL是先SELECT再FROM再WHERE于是卡在“为什么WHERE里不能用SELECT里的别名”这种问题上。SQL的实际逻辑执行顺序大致是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT注意SELECT其实是比较靠后才执行的所以你在SELECT里定义的别名WHERE阶段还看不到。而GROUP BY和HAVING都在SELECT之前所以HAVING可以访问分组字段但不能访问只在SELECT里计算出来的别名除非MySQL做了特殊扩展。理解了执行顺序很多“玄学”问题就迎刃而解了。比如你写SELECT user_id, COUNT(*) AS cnt FROM order_info WHERE create_time 2025-01-01 GROUP BY user_id HAVING cnt 10;这条SQL在标准SQL里是会报错的因为HAVING执行时别名cnt还没生效。但MySQL的优化器做了别名扩展允许这么用。可如果你迁移到PostgreSQL或者SQL Server直接报错。这就是对执行顺序理解不到位带来的隐患。所以说SQL查询不光是语法问题更重要的是你得知道数据库引擎拿到你的SQL之后内部是怎么一步步处理数据的。你理解了执行顺序就能解释为什么某些查询慢、为什么某些写法能用索引、为什么查同一条数据不同写法时间差出10倍。3. 查询提速与索引优化从理论到实战3.1 B树索引的核心机制索引之所以能加速查询是因为它改变了数据组织方式让数据库可以从O(n)的全表扫描变成O(log n)的树查找。关系型数据库里最常用的索引结构是B树它的特点是叶子节点保存数据或指向数据的指针非叶子节点只存索引键值并且叶子节点之间用链表串联。生活化类比一下B树索引就像一本新华字典的部首目录你要查一个字不需要从第一页翻到最后一页先定位到部首再根据笔画数找到精确页码。全表扫描等于把整本字典从头读到尾索引查询则是拿着目录跳着找。MySQL的InnoDB引擎里主键索引就是聚簇索引它的叶子节点直接存整行数据普通索引二级索引的叶子节点存的是主键值。所以通过二级索引查询如果SELECT的字段不在索引里还得回表再查一次主键对应的数据行。这就解释了为什么前面说“覆盖索引”重要——如果查询字段全部在索引里引擎直接返回不用回表。建索引的常规经验是等值查询最左前缀匹配联合索引的字段顺序要和WHERE条件里的匹配顺序一致。范围查询会中断索引之后的匹配比如WHERE a 1 AND b 2 AND c 3索引(a,b,c)的话c的匹配大概率走不到索引因为b的范围条件让后续字段失效。区分度高的字段放前面比如性别这个字段区分度极低放在联合索引最前面几乎等于没建索引。3.2 索引失效的典型场景自查清单我在排查慢查询时最常见的索引失效原因有这么几类整理成一个自查清单给你参考场景示例后果索引列使用函数WHERE DATE(create_time) 2025-01-01索引失效全表扫描隐式类型转换WHERE mobile 13800138000mobile是varchar索引失效类型转换破坏索引匹配前导通配符WHERE user_name LIKE %张无法走索引张%可以走OR连接非索引列WHERE id 1 OR mobile 138...可能退化为全表扫描联合索引违反最左前缀索引(a,b,c)但条件只用了b索引失效或效率极低这几个坑我基本都踩过一遍。最典型的是用DATE(create_time)这种写法过滤日期范围明明create_time上有索引但因为套了一层函数MySQL只能对每行做函数计算再比较索引彻底废了。正确写法是范围条件WHERE create_time 2025-01-01 AND create_time 2025-01-02这么写不仅走索引而且范围扫描的效率远高于对函数结果的判断。3.3 慢查询日志与分析工具链前面说了理论落到实操怎么发现查询慢才是第一步。MySQL有慢查询日志开启方式很简单在配置文件里设置slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time设成1秒超过1秒的SQL会被记录下来。加上log_queries_not_using_indexes还能把没走索引的查询都打出来这个对巡检特别有用。拿到慢查询日志之后别急着改SQL先用EXPLAIN看执行计划。重点看这几个字段type从好到差依次是system const eq_ref ref range index ALL。如果看到ALL基本就是全表扫描。key实际使用的索引名。如果为NULL说明没走索引。rows预估扫描行数。这个数字和表总行数越接近说明过滤性越差。Extra如果出现Using filesort或者Using temporary说明排序或去重在用临时表需要优化。我见过太多人慢查询日志拉出来几十条随便改两个索引就交差结果第二天慢查询换了一拨。真正高效的做法是把慢SQL按照“扫描行数/返回行数”的比率排序优先处理那些扫描行数高但返回行数极少的SQL同时看执行计划里有没有全表扫描和临时文件排序。索引优化完成之后同一个SQL的执行时间从800ms降到12ms这种案例真不是夸张。关键在于你理解了索引匹配的底层逻辑而不是盲目的加索引。4. 组合查询、JSON查询与缓存查询实战4.1 多字段动态组合查询的实现套路业务系统里最复杂的往往不是单表查询而是那种前台页面给你十几个筛选条件用户随便勾几个查询条件就是动态拼接的组合查询。我记得做过一个订单管理后台筛选条件包括订单号、用户手机号、商品名称、时间范围、订单状态、支付渠道等十来个字段用户每次勾选都不一样。这时候最笨也最常见的写法是用MyBatis的动态SQL在XML里用if标签拼接条件。代码长、容易出错而且每个条件都得小心测试。另一种思路是把查询条件抽象成查询对象在代码层面构造查询条件QueryWrapperOrderInfo wrapper new QueryWrapper(); wrapper.eq(StringUtils.hasText(orderNo), order_no, orderNo); wrapper.eq(payStatus ! null, pay_status, payStatus); wrapper.ge(startTime ! null, create_time, startTime); wrapper.le(endTime ! null, create_time, endTime); wrapper.like(StringUtils.hasText(productName), product_name, productName);好处是条件变动集中在一个方法里不存在XML拼接漏条件的问题代码review也直观。实际项目里不管是MyBatis-Plus还是Spring Data JPA都有类似的条件构造器推荐优先使用。组合查询的索引设计原则是按照高频查询条件建联合索引比如订单状态创建时间基本上是所有筛选场景都会带的那就建idx_status_create_time。如果还经常按用户维度查就再建一个idx_user_id_create_time。索引不是越多越好因为写操作要维护索引索引过多会导致插入、更新变慢。4.2 JSON字段查询与半结构化数据处理近些年的业务数据越来越喜欢用JSON格式存储比如商品属性、用户扩展信息、接口日志里的请求参数。MySQL从5.7开始支持JSON类型PostgreSQL对JSON的支持更成熟一些。JSON查询方式和传统SQL有一条明显的分界线传统SQL查询的是固定列结构JSON查询要处理的是嵌套的、动态变化的半结构化数据。假设有一张product_info表里面有个JSON字段attrs存的是商品的可选属性{color: red, size: L, brand: nike}在MySQL里要查所有颜色为红色的商品可以这么写SELECT id, product_name FROM product_info WHERE JSON_CONTAINS(attrs-$.color, red);不过需要注意JSON_CONTAINS这样的函数查询在性能上很难直接走普通索引。MySQL支持对JSON字段生成虚拟列并加索引这是解决JSON查询性能问题的正解ALTER TABLE product_info ADD COLUMN color VARCHAR(20) GENERATED ALWAYS AS (attrs-$.color) VIRTUAL, ADD INDEX idx_color (color);这样后续查询直接搜虚拟列color就可以走索引了。如果你用的是PostgreSQL本身支持GIN索引直接加速包含查询写法更顺滑性能也更稳。从设计角度讲JSON字段不是用来替代关系模型的。当查询逻辑复杂、关联维度多的时候正确的做法是把JSON里的高频字段提取成正式列JSON只放那些低频的、结构不稳定的扩展属性。我见到的很多团队图省事把所有扩展信息塞进一个JSON字段结果后面报表统计、数据分析全都卡在一个大JSON解析上教训挺深刻的。4.3 缓存查询什么时候该上Redis怎么保持一致性缓存查询是读写链路里绕不过去的话题。先说结论缓存不适合存强一致性的数据它是用来扛高并发读的。比如商品详情页、用户首页信息流、热搜榜单这些都是读多写少、对一致性要求没那么精细的场景适合用缓存查询来扛流量。常见的缓存查询模式是Cache Aside也就是旁路缓存读先查Redis有就返回没有则查MySQL查到后写回Redis再返回。 写先更新MySQL然后删Redis里的缓存或者更新缓存。这里有个经典问题先更新数据库还是先删缓存我的实践是更新数据库之后删除缓存而不是修改缓存。原因在于缓存里的数据倾向于是“数据快照”和数据库的行数据未必一一对应可能存在数据加工比如缓存的是某个聚合值、JSON串、RESTful响应体。直接修改缓存里的值很容易改错格式而删除缓存让下一次读请求回源重新构建是最稳妥的。缓存穿透、缓存击穿、缓存雪崩这三个词大家听得耳朵起茧但实际处理手段我要重新说一遍穿透查询一个根本不存在的数据每次都会打到数据库。解决办法是布隆过滤器拦截或者缓存空值并设置短过期时间。击穿某个热点key突然失效大量请求一起打库。解决办法是互斥锁只有第一个请求去建缓存其他请求等待或直接返回旧值。雪崩大量key同时过期数据库被打满。解决办法是过期时间加随机数避免同一时刻集中失效。我自己在实际项目里最常用的套路是缓存过期时间设置为基础过期时间 随机5到10分钟既能保证数据最终一致又能打散过期瞬间。对于极端热点的keylocal cache本地进程缓存配合Redis两级缓存效果更好但要注意本地缓存的更新问题别让多台机器上的缓存长期不一致。缓存查询看起来就是把数据放到Redis里再查一次但真要做得稳一致性方案、过期策略、降级预案都得提前想好。很多线上事故就是缓存层抖动直接拖垮数据库所以缓存查询必须配套熔断和降级逻辑。4.4 分页查询的深分页问题与优化方案分页查询人人会写但数据量一大问题就出来了。最经典的深分页场景ORDER BY create_time DESC LIMIT 100000, 20这个查询在数据量千万级时可能直接干到几秒甚至几十秒。为什么因为LIMIT 100000, 20并不是“跳过前10万行只取20行”那么简单。MySQL的做法是扫描到偏移量100000的位置然后把前10万行全部丢掉再拿后面的20行返回。这个丢弃的过程浪费了大量的IO和CPU。解决深分页有几个路数第一种用游标分页代替偏移量分页。比如按主键id排序SELECT id, create_time FROM order_info WHERE id 100000 ORDER BY id LIMIT 20;这种写法每次只查20行后面的查询以前一次结果的最大id为起点性能非常稳定。缺点是不能随便跳页只适合“上一页/下一页”的业务形态。第二种先join一个延迟关联的结果集。也就是先查出需要的20个主键再回表取数据SELECT o.* FROM order_info o INNER JOIN ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON tmp.id o.id;这样内层查询走的是索引覆盖速度快很多外层再按主键回表取20行代价可控。第三种如果业务允许把结果集预聚合。比如按天的统计结果提前算好放汇总表查询直接按天范围查汇总数据彻底绕开大表深分页的问题。这也是一种典型的“查询方式重新设计”的思路。5. 慢查询排查、执行计划分析与查询安全5.1 通过EXPLAIN读懂执行计划我一直觉得EXPLAIN应该是每个写SQL的人的基本功。它不会直接告诉你SQL怎么改最快但能告诉你数据库打算怎么执行你的SQL这才是优化的起点。EXPLAIN SELECT u.id, u.user_name, o.order_no FROM user_info u LEFT JOIN order_info o ON o.user_id u.id WHERE u.status 1;执行计划里最需要关注的信息我之前零散提了几次这里系统列一下type字段的含义const主键或唯一索引等值查询最快。ref非唯一索引等值匹配。range索引范围扫描比如BETWEEN、、。index全索引扫描比全表扫描好一点但也不快。ALL全表扫描最慢。Extra字段里的两个危险信号Using filesort查询里ORDER BY字段没有走索引MySQL在内存或磁盘里做了排序操作。Using temporary查询用到了临时表通常出现在GROUP BY、DISTINCT、多表JOIN时。发现了这些问题对应的优化方向就是调整索引。比如ORDER BY create_time DESC慢看有没有(create_time)索引GROUP BY user_id慢看有没有(user_id)索引。基本上执行计划里的每一个危险标识都能对应到一个具体的索引调整方案。5.2 慢SQL治理的优先级排序慢查询日志收集上来之后一堆SQL摆在面前先优化哪个我的原则是先看“影响面”和“性价比”。影响面取决于两个指标这条SQL的执行频率以及单次执行耗时。频率高但执行慢的必须最先处理因为它是系统吞吐量的瓶颈。比如某个接口每次都查一次商品列表单次300ms频率每秒500次这系统不卡才怪。性价比则看优化的复杂度。有些SQL只要加个索引就快了5分钟搞定收益巨大有些SQL要改表结构、改代码逻辑、做数据迁移就得排期。我一般做四类标记优先级特征处理策略P0执行频率高且每一条都慢立即优化通常是缺索引或SQL写错P1频率不高但单次极慢优化索引必要时改写SQLP2返回大量数据的低效查询关注limit、查询字段裁剪P3后台离线统计类慢查询安排批量任务优化不阻塞线上5.3 查询安全SQL注入与参数化查询查询方式总结里如果不聊安全等于白写。SQL注入是Web安全里最常见也最致命的一种攻击方式。很多初学者以为注入就是把 OR 11 --拼到字符串里但实际上注入手法花样很多报错注入、盲注、时间盲注、联合查询注入都可以演变成数据泄露工具。防御的核心原则就一条查询参数永远不能直接拼接SQL字符串。必须使用参数化查询让数据库把参数值和SQL语句分开处理。Java的JDBC正确写法是String sql SELECT * FROM user_info WHERE user_name ? AND status ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, userName); ps.setInt(2, status); ResultSet rs ps.executeQuery();MyBatis里使用#{}而不是${}select idselectUser resultTypeUser SELECT * FROM user_info WHERE user_name #{userName} AND status #{status} /select#{}会被解析成预编译参数占位符而${}是直接字符串替换${}如果用在ORDER BY、表名这类动态结构上一定得自己做好白名单校验。还有一个容易被忽略的查询安全细节敏感字段的脱敏处理。查询接口返回的用户手机号、身份证号、银行卡号数据库里是明文接口层也应该按需脱敏。很多数据的泄露不是被攻击者拖库拖走的而是业务查询接口权限控制不严任何登录用户都能通过接口遍历出全量数据。查询安全这块我的经验是宁可查询功能少做一些权限校验和参数校验不能省。匿名用户可以查什么、登录用户可以查什么、管理员可以查什么一定要在查询入口层做清晰的数据权限隔离。6. 查询方式的选型总结与扩展思考说了这么多不同的查询方式最后把选型逻辑捋一遍。如果你要面对一个全新的业务查询需求可以参考下面这张选型表来定方案查询场景推荐方式原因单条主键或唯一索引查询SQL 聚簇索引性能最优O(log n)定位多条件组合筛选SQL 联合索引 / 动态条件构造器平衡灵活性和性能列表分页查询游标分页按id/时间避免深分页性能问题模糊搜索大文本完整索引fulltext/ ESLIKE %xx% 在数据量大时不可用高并发读场景缓存查询Redis 本地缓存把数据库读压力挡在更前一层半结构化属性的动态筛选JSON列 虚拟列 索引兼顾存储灵活性和查询效率后台报表聚合分析预聚合汇总表 / OLAP引擎绕开事务型数据库的聚合性能瓶颈查询方式这么多本质上没有银弹。我见过一些团队陷入“优化执念”没必要的场景也强行上ES、上缓存结果架构复杂了数据一致性反而更难保证。查询方式的选择永远服务于业务场景不是越高级越好。我个人的建议是先把SQL查询和索引优化玩透这是性价比最高的起点。大部分线上查询问题靠优化索引和调整SQL写法就能解决一大半。然后在这个基础上按需引入缓存、搜索、OLAP引擎。每一步都基于真实的性能数据来做决策而不是因为“别人家用了ES所以我也要用”。再分享一个实际的技巧每次完成一个查询方式优化之后把这个优化前后的SQL、执行计划、耗时对比记录下来。攒一段时间之后你就会发现自己对“什么场景该用什么查询方式”的判断越来越准。这套笔记不仅是个人技术成长的沉淀也是团队里新人最宝贵的参考材料。查询不是业务逻辑里最出彩的部分但恰恰是这层打底的质量决定了系统能不能在流量高压下站得稳。把每一种查询方式用扎实、用到位比追新花样有意义得多。