1. 先弄明白YashanDB的查询为什么会慢聊查询优化之前我必须先把一个观念摆正索引不是万能的SQL改写也不是银弹。很多人一遇到查询慢就急着加索引结果加了一堆写入变慢、磁盘膨胀查询还是没快多少。真正的调优思路是先把慢查询的根因找出来再对症下药。1.1 慢查询的物理根源磁盘I/O与内存数据库所有查询最终都要落到一个核心矛盾上——内存处理速度比磁盘快几个数量级。一块普通SSD的顺序读速度大约在500MB/s到2GB/s而内存带宽轻松达到几十GB/s。两者的差距意味着一个查询如果频繁访问磁盘性能天花板就被锁死了。YashanDB是基于磁盘存储的关系型数据库它的数据文件、日志文件都在磁盘上。查询执行时数据库会先把数据页读入内存缓冲区再在内存里做过滤、连接、排序等操作。如果一个查询需要访问的数据量远大于缓冲区的容量就会出现频繁的页换入换出表现就是查询延迟飙升。我遇到过很多“加索引没用”的案例最后排查下来问题出在数据库的缓冲区配置上。YashanDB的缓冲区大小如果设置得太小再好的索引也没用因为索引本身也要占缓冲区空间。你建一个几GB的索引缓冲区只有几百MB索引页读进来又被挤出去等于每个查询都在做磁盘随机读。另一个容易被忽略的点是日志写入。对于写入密集的业务提交日志的fsync操作会拖慢整体性能间接影响查询响应。如果事务提交频率过高日志落盘就变成了瓶颈。这个可以在业务层面做批量提交优化减少不必要的同步等待。1.2 逻辑层面SQL写法与执行计划的偏差物理层面的资源问题解决之后再看逻辑层面。最典型的情况是一个SQL看起来没毛病但执行计划偏偏走了全表扫描。为什么会这样因为SQL的写法、表数据分布、统计信息的准确度共同决定了执行计划的选择。举个例子你在一个大表的status字段上建了索引然后写SELECT * FROM orders WHERE status PENDING。如果这张表里90%的数据都是PENDING状态优化器一算走索引回表还不如直接全表扫描快于是它就放弃索引了。这是优化器的理性选择不是它傻。反过来如果PENDING只占1%索引就派上用场了。还有一种情况是统计信息过期。YashanDB的优化器依赖表的统计信息行数、字段分布、空值率等来估算执行代价。如果表数据发生了大规模变化但统计信息还停留在旧状态优化器就会做出错误判断。比如明明两张表都很大但因为统计信息显示一张表只有几行优化器就选了错误的嵌套循环连接结果跑出几十秒。这里要记住一个原则先看执行计划再决定怎么改。不要凭感觉加索引、改SQL那样往往事倍功半。2. 索引设计性价比最高的提速手段如果让我说一个查询优化最值得投入的方向那必然是索引。索引设计得好一条本来要扫几百万行的查询瞬间就能降到几十毫秒。但索引也不是昨天建了今天就生效它需要你对数据特征和查询模式有清晰的认知。2.1 从一个真实案例理解索引数据结构先看一个具体场景。业务系统里有一张订单表大概2000万行字段包括order_id主键、customer_id、create_time、status、amount。最常见的查询是按customer_id找出某个客户最近1个月的订单。在没有索引的情况下数据库只能全表扫2000万行逐行判断customer_id是否匹配再过滤时间范围。这个操作在磁盘上就是一次大范围的顺序读也要消耗不少时间。加上索引之后数据库会根据customer_id的B树快速定位到目标记录再回表获取完整行数据扫描量直接缩小到几千甚至几百行。YashanDB的索引结构采用经典的B树。B树的好处在于层数固定且矮胖一个三层B树就能支撑千万级别的数据量。也就是说即使数据量翻了几倍索引查找的代价几乎不变。这就是为什么索引能稳定提升查询速度。但这里有个关键细节索引查找快不代表每个带索引的查询都快。如果你查的是SELECT * FROM orders WHERE customer_id 100索引找到对应的主键后还要回表去取整行的所有列。回表是随机I/O如果命中行数太多性能反而不如全表扫描。实操中我建议为高频查询设计覆盖索引让索引自带查询需要的所有字段省掉回表这一步。比如上面的查询如果只需要order_id、create_time、amount三个字段建一个(customer_id, create_time, amount)的复合索引就能实现完全覆盖。2.2 复合索引的最左前缀与覆盖索引复合索引是YashanDB索引优化的重头戏也是最容易翻车的地方。很多新手把多个字段塞进一个索引以为字段越多越好结果查询根本不走这个索引因为没遵守最左前缀原则。最左前缀原则说的是复合索引的命中条件是从索引第一个字段开始连续匹配不能跳过字段。假设你建了(customer_id, create_time, status)三列复合索引那么以下查询能用上索引WHERE customer_id 100WHERE customer_id 100 AND create_time 2024-01-01WHERE customer_id 100 AND create_time 2024-01-01 AND status A以下查询用不上这个索引WHERE create_time 2024-01-01缺少首个字段WHERE status A跳过了第二个字段理解了最左前缀你就知道复合索引的字段顺序不能随便排。高频等值查询的字段放最前面范围查询字段放后面这是基本规则。因为等值匹配能把索引定位到确定区间范围匹配只能在这之后缩小范围。覆盖索引是另一个提高查询速度的利器。它的思路是让索引包含查询需要的全部列这样数据库执行查询时只要扫描索引页就够了完全不需要回表。比如经常要统计每个客户在某个时间段的订单总额建(customer_id, create_time, amount)的复合索引执行SELECT customer_id, SUM(amount) FROM orders WHERE customer_id 100 AND create_time 2024-01-01 GROUP BY customer_id时所有数据都从索引里拿到效率极高。一个我常用的技巧是分析业务查询时先看SELECT的字段列表再看WHERE和ORDER BY的字段把这两部分合并起来设计索引。同时要注意索引不是越多越好每个索引都会拖慢INSERT、UPDATE和DELETE。一般一个表保持在5个以内索引是比较合理的。2.3 让索引失效的五个坑索引建对了查询就快了吗不一定。我踩过不少索引失效的坑这里做个整理你可以拿着对照排查。坑1对索引列做了函数运算。比如WHERE UPPER(name) YASHAN或者WHERE YEAR(create_time) 2024。函数运算会导致索引无法定位数据库只能全表扫描。解决办法是改成范围查询像WHERE create_time 2024-01-01 AND create_time 2025-01-01或者干脆把函数处理的结果单独列出来建索引。坑2隐式类型转换。如果索引列是字符串类型但查询条件传了数字比如WHERE phone 13800138000phone列是varchar数据库会把列做类型转换再比较索引失效。我在对接外部系统接口时遇到过多次这个问题排查起来很隐蔽。解决办法是检查SQL参数类型是否和表结构定义一致。坑3LIKE前缀模糊查询。WHERE name LIKE %张%这样的条件无法走索引。如果业务确实需要前缀模糊匹配考虑用全文索引或者对字段做倒排索引设计YashanDB也支持全文检索能力。坑4OR条件中含非索引列。如果WHERE a 1 OR b 2a有索引但b没有数据库可能放弃a上的索引改走全表扫描因为如果走索引只能拿到半个结果集还要再扫另一半。改成UNION ALL就能让两段查询各自走索引。坑5索引选择性太低。就像1.2节里说的status字段如果某个取值占比过高优化器会判定走索引不如全表扫于是放弃索引。这种字段通常不适合单独建索引考虑和其他筛选性强的字段组合使用。索引优化的核心不只是会建索引还要知道什么时候索引会被浪费。多花10分钟看执行计划比盲试各种索引写法高效得多。3. 看懂执行计划优化前先学会体检查询优化就像给人看病不做检查就开药是不负责任的。数据库的“体检报告”就是执行计划Execution Plan。YashanDB提供了EXPLAIN工具可以展示优化器为一条SQL选择的执行路径。学会读执行计划你才真正摸到了优化的门道。3.1 EXPLAIN的基本用法与关键字段YashanDB中你可以在SQL前加EXPLAIN例如EXPLAIN SELECT o.order_id, o.amount, c.name FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE o.create_time 2024-06-01 AND o.status PAID;执行后会返回一棵执行计划树。你需要重点看这几个字段操作类型每个节点的执行动作比如TABLE SCAN表扫描、INDEX SCAN索引扫描、HASH JOIN哈希连接、NESTED LOOP嵌套循环。行数估算优化器预判这个节点会返回多少行。如果估算和实际差很多说明统计信息可能过期。代价相对成本值YashanDB的优化器用这个值来衡量操作耗时虽然不能直接换算成毫秒但可以用来比较不同执行计划的优劣。我拿到一条慢SQL的第一件事就是看执行计划的根节点操作类型。如果看到的是全表扫描而且表数据量很大那基本可以确定这是慢查询的直接元凶。3.2 常见扫描类型解读与应对策略执行计划里常见的几种访问方式每种都要有对应的优化策略。全表扫描TABLE ACCESS FULL如果计划里出现这个同时表很大说明当前SQL条件没有合适的索引可用。应对方式是分析WHERE条件在筛选性最好的字段上建索引。索引唯一扫描/索引范围扫描INDEX UNIQUE SCAN / INDEX RANGE SCAN这是比较健康的状态说明优化器用上了索引。索引唯一扫描多用于主键等值匹配索引范围扫描用于等值区间匹配。索引回表TABLE ACCESS BY INDEX ROWID索引扫描之后拿着rowid回到数据表取行性能取决于回表行数。回表行数少几百行没什么问题如果达到几万行就要考虑加覆盖索引。哈希连接HASH JOIN两张表连接时优化器把较小的表构建成哈希表再扫描大表进行匹配。这种连接适合等值连接且数据量较大的场景本身并不一定是坏事。问题在于如果驱动表错误会影响性能。嵌套循环连接NESTED LOOP JOIN外层表每取一行就去内层表匹配一次。适合外层表数据量很小的情况。如果外层表有几万行内层表没有索引性能就会非常糟糕。让优化器选择的执行计划和执行路径更优核心是两件事一是必要的索引二是准确的统计信息。3.3 统计信息执行计划的“参考地图”我已经不止一次遇到这样的局面SQL没问题、索引也建了但执行计划就是不走索引。深挖下去根因是统计信息过期了优化器误以为表只有几十行所以选择了全表扫描。YashanDB提供了统计信息收集命令也可以手动触发。我的经验是在表数据量发生较大变化比如单次导入超过10%的行数之后尽快对相关表执行一次统计信息更新。具体命令格式可能因版本而异但逻辑是统一的——收集每个表的行数、列分布、空值率、数据长度等元信息让优化器能有据可依。生产环境里我建议把统计信息收集做成定时任务。比如每周末凌晨业务低峰期跑一次全库的统计信息收集。遇到刚完成大批量数据变更后临时手动收集一次这对接下来的查询性能至关重要。这里顺便分享一个小技巧分析执行计划时不要只盯着树形图看还要对比计划中估算的行数和SQL实际返回的行数。两者偏差超过一个数量级基本就能断定统计信息有问题。4. SQL改写与参数调整不花钱也能提速索引和统计信息都搞定之后还有一块潜力可挖——SQL本身的写法以及数据库运行参数的调整。这一节里我不讲太玄的理论只分享几个配合YashanDB特性反复验证过的实用技巧。4.1 表连接方式的优化多表查询是慢SQL的高发区尤其当表的数据量都在百万级以上时。连接方式选择错误带来的性能差距可能不止10倍。先看一个常见场景。业务报表需要查每个客户最后一次下单时间SELECT c.customer_id, c.name, t.last_order_time FROM customers c LEFT JOIN ( SELECT customer_id, MAX(create_time) AS last_order_time FROM orders GROUP BY customer_id ) t ON c.customer_id t.customer_id;这个SQL如果直接跑执行计划很可能选择对orders做全表扫描加哈希聚合然后和customers做哈希连接。如果orders表特别大每次执行都耗在扫描上。我的优化思路是把子查询结果物化或者利用YashanDB的物化视图功能提前把聚合结果算好查询时直接取数。另外一个经典问题是多表连接时的驱动顺序。优化器会尽量选择小表作为驱动表但你无法完全控制它。当你发现某条连接查询的驱动表明显不对时可以尝试调整SQL的连接顺序。在某些数据库里显示控制连接顺序的提示符能够影响优化器的判断。YashanDB的具体提示语法建议查阅对应版本的官方文档但思路是通用的尽量让小结果集驱动大结果集。4.2 子查询与分页查询的经典改写子查询的性能问题大多出在“相关性”。以下这种写法在customer_id无索引时会对每个外层行都做一次内层查询SELECT * FROM orders o WHERE amount ( SELECT AVG(amount) FROM orders WHERE customer_id o.customer_id );改写思路是把相关子查询转化为一次性的聚合连接SELECT o.* FROM orders o JOIN ( SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id ) a ON o.customer_id a.customer_id WHERE o.amount a.avg_amount;后者只需要对orders做两次扫描一次聚合、一次过滤性能提升立竿见影。这种改写方式在YashanDB上验证没问题而且能让执行计划更简单稳定。分页查询也值得单独说。LIMIT/OFFSET分页在数据量大时有一个经典问题越往后翻OFFSET越大数据库需要扫描并丢弃前N行数据。比如LIMIT 20 OFFSET 2000000意味着要扫描200万行然后只返回20行。优化方案有两个思路。一是记住上一页最后一条记录的游标用WHERE条件代替OFFSET-- 上一页最后一条记录的create_time和order_id分别是 last_time, last_id SELECT * FROM orders WHERE (create_time, order_id) (last_time, last_id) ORDER BY create_time DESC, order_id DESC LIMIT 20;这里的原理是利用索引的有序性跳过已经看过的数据而不是从头扫描发现要跳过的行。二是对超大表考虑用键集分页Keyset Pagination往往能把分页响应时间从秒级降到毫秒级。4.3 YashanDB关键参数的手动调优除了SQL本身数据库运行参数也会影响查询速度。这里要提醒一句具体参数名会根据YashanDB版本有所不同下面的建议要对照官方文档确认后再调整。首先关注缓冲区大小。缓冲区越大能缓存的数据页越多磁盘访问就越少。如果YashanDB有类似共享缓冲区或数据页缓存区大小的配置项可以优先调整。注意不是越大越好太大的缓冲区会导致管理开销增加甚至系统内存不足。推荐从系统可用内存的25%-40%开始测试然后逐步调整观察。其次是排序和聚合操作相关的内存配置。查询里的ORDER BY、GROUP BY、DISTINCT、哈希连接等操作如果内存不够就会溢写到磁盘性能骤降。给这些操作分配合理的临时内存上限可以减少磁盘溢写。一般的策略是设置一个适度的基础值再根据典型的复杂查询实际需求微调。最后是并发相关参数。数据库的并发线程数、连接池大小如果配置不当会出现CPU跑不满但查询排队等待的情况。检查你的应用连接数是否超过了数据库能同时处理的上限如果连接池设得过大反而会造成上下文切换开销拖慢每个查询。调整参数时我的习惯是每次只改一个参数充分压测后再改下一个。同时调整多个参数出了问题都不知道是哪个引起的。5. 实战排查定位慢查询的完整路径前面讲了原理和优化手法最后落到实操。真正的日常运维中你不会一开始就知道哪条SQL慢而是要先把它找出来。这一节分享一套从0到1的慢查询定位方法以及我长期积累的排查清单。5.1 开启慢查询日志YashanDB支持慢查询日志功能。你可以设置一个时间阈值比如超过2秒的SQL就被记录到日志中。这个阈值不能设得太低否则日志量太大也不能太高否则会漏掉真正需要优化的SQL。我的建议是从1秒开始观察一段时间的日志量再微调。慢查询日志里通常包含这些信息SQL文本、执行耗时、返回行数、扫描行数、执行时间点。不要光看执行耗时还要看扫描行数和返回行数的比例。如果扫描100万行返回1000行说明索引设计有优化空间如果扫描行数很多且返回行数也很多可能是业务本身要全量数据这时候考虑上聚合和物化手段。5.2 常见问题速查表我在YashanDB实际优化项目中整理了一张速查表涵盖了90%的情况这里分享出来供你参考现象可能原因优先排查方向查询长时间无响应表锁或行锁冲突查数据库会话杀掉阻塞头会话同一条SQL时快时慢统计信息过期或缓存命中率波动手动收集统计信息检查缓冲区命中率索引有但查询不走隐式类型转换或函数运算检查WHERE字段类型与索引列是否一致大表连接查询极慢连接列无索引或驱动表选择错误在连接列上补索引用提示符调整驱动顺序分页越往后越慢OFFSET过大导致扫描量大改键集分页或用游标方式分页数据库CPU高但查询慢并发SQL过多查会话数优化应用连接池减少短事务请求5.3 一个综合优化案例之前帮一个客户排查过一次典型慢查询。业务场景是订单统计报表每天凌晨跑一次执行时间从最初的10分钟恶化到40多分钟。当时第一反应是数据量增长导致的全表扫描变慢但看执行计划后发现其实表数据量只增长了一倍不至于导致4倍的耗时增长。进一步看执行计划发现问题在两张表的连接上。订单明细表有近千万行另一个维度表有几十万行。连接条件里的维度表主键是字符串类型而订单明细表里关联字段是数字类型。两边类型不一致导致隐式转换维度表的索引完全用不上每次都走哈希连接加全表扫描。排查思路是这样的先检查两边连接字段的类型定义确认不一致后把订单明细表里的数字字段改成字符串类型并重新建索引。改造后重新收集统计信息执行时间从40分钟降到了5分钟以内查询结果完全一致。这个案例说明了一个核心问题SQL写得再漂亮字段类型设计不合理照样翻车。数据模型设计阶段就要考虑关联字段的类型一致性这比后期优化省力得多。最后再分享一个小技巧每次做查询优化前先做一次基线记录把慢SQL、执行计划、执行时间、系统负载全部记下来。优化完再对比一份。这样不仅能验证优化效果还能帮你积累一套针对业务环境的慢查询知识库。以后出现类似问题翻翻记录能省不少时间。