【咨询】关于索引和合理应用

远航 · 2026 年8 月 13 日 17:16

关于一个单表，查询一个数据，可能在两列中出现

有两种写法：

SELECT *
FROM `PosSalePay` AS `p`
WHERE `p`.`TenantId` = 6 and `p`.`ThirdOrderCode` = '202608131532302473881'
union all
SELECT *
FROM `PosSalePay` AS `p`
WHERE `p`.`TenantId` = 6 and `p`.`BillId` = '202608131532302473881'

第二种写法：

SELECT *
FROM `PosSalePay` AS `p`
WHERE `p`.`TenantId` = 6 and (`p`.`ThirdOrderCode` = '202608131532302473881' or `p`.`BillId` = '202608131532302473881')

索引：

-- 索引1：覆盖 TenantId + BillId 查询路径
CREATE INDEX IX_PosSalePay_TenantId_BillId 
    ON PosSalePay (TenantId, BillId);

-- 索引2：覆盖 TenantId + ThirdOrderCode 查询路径
CREATE INDEX IX_PosSalePay_TenantId_ThirdOrderCode 
    ON PosSalePay (TenantId, ThirdOrderCode);

第一种查询计划：

======================================================================================
|ID|OPERATOR          |NAME                                    |EST.ROWS|EST.TIME(us)|
--------------------------------------------------------------------------------------
|0 |UNION ALL         |                                        |2       |464         |
|1 |├─TABLE RANGE SCAN|p(IX_PosSalePay_TenantId_ThirdOrderCode)|1       |232         |
|2 |└─TABLE RANGE SCAN|p(IX_PosSalePay_TenantId_BillId)        |1       |232         |
======================================================================================
Outputs & filters:
-------------------------------------
  0 - output([UNION([1])], [UNION([2])], [UNION([3])], [UNION([4])], [UNION([5])], [UNION([6])], [UNION([7])], [UNION([8])], [UNION([9])], [UNION([10])],
       [UNION([11])], [UNION([12])], [UNION([13])], [UNION([14])], [UNION([15])], [UNION([16])], [UNION([17])], [UNION([18])], [UNION([19])], [UNION([20])], [UNION([21])],
       [UNION([22])], [UNION([23])], [UNION([24])], [UNION([25])], [UNION([26])], [UNION([27])], [UNION([28])], [UNION([29])], [UNION([30])], [UNION([31])], [UNION([32])],
       [UNION([33])], [UNION([34])], [UNION([35])], [UNION([36])], [UNION([37])], [UNION([38])], [UNION([39])], [UNION([40])], [UNION([41])], [UNION([42])], [UNION([43])],
       [UNION([44])], [UNION([45])], [UNION([46])], [UNION([47])]), filter(nil), rowset=16
  1 - output([p.Id], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode], [p.BusinessformatName], [p.Code],
       [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId], [p.FlowNo], [p.Amount], 
      [p.BillId], [p.ThirdOrderCode], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName],
       [p.RefNo], [p.TraceNo], [p.PayType], [p.Time], [p.TenantId], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId],
       [p.SubInfo], [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], 
      [p.PosPayType]), filter(nil), rowset=16
      access([p.Id], [p.TenantId], [p.ThirdOrderCode], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode],
       [p.BusinessformatName], [p.Code], [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId],
       [p.FlowNo], [p.Amount], [p.BillId], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName],
       [p.RefNo], [p.TraceNo], [p.PayType], [p.Time], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId], [p.SubInfo],
       [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], [p.PosPayType]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([p.TenantId], [p.ThirdOrderCode], [p.Id]), range(6,202608131532302473881,MIN ; 6,202608131532302473881,MAX), 
      range_cond([p.TenantId = 6], [p.ThirdOrderCode = '202608131532302473881'])
  2 - output([p.Id], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode], [p.BusinessformatName], [p.Code],
       [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId], [p.FlowNo], [p.Amount], 
      [p.BillId], [p.ThirdOrderCode], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName],
       [p.RefNo], [p.TraceNo], [p.PayType], [p.Time], [p.TenantId], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId],
       [p.SubInfo], [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], 
      [p.PosPayType]), filter(nil), rowset=16
      access([p.Id], [p.TenantId], [p.BillId], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode], [p.BusinessformatName],
       [p.Code], [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId], [p.FlowNo], [p.Amount],
       [p.ThirdOrderCode], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName], [p.RefNo],
       [p.TraceNo], [p.PayType], [p.Time], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId], [p.SubInfo], 
      [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], [p.PosPayType]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([p.TenantId], [p.BillId], [p.Id]), range(6,202608131532302473881,MIN ; 6,202608131532302473881,MAX), 
      range_cond([p.TenantId = 6], [p.BillId = '202608131532302473881'])

第二种查询计划：

============================================================================
|ID|OPERATOR        |NAME                            |EST.ROWS|EST.TIME(us)|
----------------------------------------------------------------------------
|0 |TABLE RANGE SCAN|p(IX_PosSalePay_TenantId_BillId)|1       |10          |
============================================================================
Outputs & filters:
-------------------------------------
  0 - output([p.Id], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode], [p.BusinessformatName], [p.Code],
       [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId], [p.FlowNo], [p.Amount], 
      [p.BillId], [p.ThirdOrderCode], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName],
       [p.RefNo], [p.TraceNo], [p.PayType], [p.Time], [p.TenantId], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId],
       [p.SubInfo], [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], 
      [p.PosPayType]), filter([p.ThirdOrderCode = '202608131532302473881' OR p.BillId = '202608131532302473881']), rowset=16
      access([p.Id], [p.TenantId], [p.ThirdOrderCode], [p.BillId], [p.EnterpriseId], [p.EnterpriseCode], [p.EnterpriseName], [p.BusinessformatId], [p.BusinessformatCode],
       [p.BusinessformatName], [p.Code], [p.CreatorTime], [p.IsDeleted], [p.DeletionTime], [p.OrganizationId], [p.OrganizationCode], [p.OrganizationName], [p.OrderId],
       [p.FlowNo], [p.Amount], [p.Remark], [p.MainPayWay], [p.MainPayCode], [p.SubPayWay], [p.SubPayCode], [p.FullPayWayName], [p.CardNo], [p.PayName], [p.RefNo],
       [p.TraceNo], [p.PayType], [p.Time], [p.FullDisPromotionDetailId], [p.SubAccountResultId], [p.ExtendInfo], [p.IsCancelSub], [p.OldBillId], [p.SubInfo], 
      [p.RefundAmount], [p.OriginalAmount], [p.ChargeAmount], [p.PreSaleMode], [p.EntityCardNo], [p.DouYinCode], [p.PayTime], [p.IsOfflineO2O], [p.PosPayType]), partitions(p0)
      is_index_back=true, is_global_index=false, filter_before_indexback[false], 
      range_key([p.TenantId], [p.BillId], [p.Id]), range(6,MIN,MIN ; 6,MAX,MAX), 
      range_cond([p.TenantId = 6])

问题：
1.哪种写法更好，或者都不好，还有更好的写法？
2.OR有没有可能走双索引？

淇铭 · 2026 年8 月 13 日 17:36

如果建了两个索引 union all展开的时候针对两个查询都会命中索引你第二种应该是or没有展开

远航 · 2026 年8 月 13 日 17:37

也就是说我们这个场景最佳实践就是建两个索引然后走union？

淇铭 · 2026 年8 月 13 日 17:39

如果不建立两个索引的话即使union all展开有一个查询也命中不了索引你第二查询加一下这个hint/*+ USE_CONCAT */ 看看能否展开

远航 · 2026 年8 月 13 日 17:41

那第二个问题咨询一下，OR这种写法是不是就不可能命中我的两个索引。有个命中了，快，再计算另一个字段where的时候还是全表扫描

淇铭 · 2026 年8 月 13 日 17:43

主要没有展开展开成union 这样就可以命中两个索引

远航 · 2026 年8 月 13 日 17:52

咋写。。。没用过这个语法。。。

远航 · 2026 年8 月 13 日 17:53

hint/*+ USE_CONCAT */

没用过这个语法，咋写呀，

远航 · 2026 年8 月 13 日 18:01

试过了，加上/*+ USE_CONCAT */ 确实走了两个索引

码农101 · 2026 年8 月 13 日 23:08

开发过程中第二种更好，SQL简单容易维护；
我理解，上述两种SQL的写法，不管那种，OB优化器默认都是可以展开改写的。真实案例中，若没有展开，可能是因为缺少统计新信息造成。统计信息收集下试试看，要么就直接hint方式。参见:https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000663396?back=kb

AntTech_OXEPTD · 2026 年8 月 14 日 08:51

这俩不等价。1可能会有重复记录。 2的写法不错，只扫一次表。