SQL优化问题

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】OBCE-4.2.2.1
【问题描述】sql 排序不走索引
【复现路径】一个复合索引，索引列包含 过滤字段和排序字段，强制走索引，排序失效。走主键 可以走索引排序，难道OB 复合索引包含了过滤字段和排序字段 ，强制走索引 索引排序会失效吗？
表结构：

SQL ：

select /*+index(fed idx01)*/
  fed.id,
  fed.form_entity_id,
  fed.user_id,
  fed.created_time,
  fed.deleted,
  fed.updated_user_id,
  fed.updated_time,
  fed.flow_instance_id,
  fed.thread_id,
  fed.node_id,
  fed.object_version,
  fed.value1,
  fed.label1,
  fed.value10,
  fed.label10,
  fed.value11,
  fed.label11,
  fed.value12,
  fed.label12,
  fed.value13,
  fed.label13,
  fed.value14,
  fed.label14,
  fed.value15,
  fed.label15,
  fed.value16,
  fed.label16,
  fed.value17,
  fed.label17,
  fed.value18,
  fed.label18,
  fed.value19,
  fed.label19,
  fed.value2,
  fed.label2,
  fed.value20,
  fed.label20,
  fed.value21,
  fed.label21,
  fed.value22,
  fed.label22,
  fed.value3,
  fed.label3,
  fed.value4,
  fed.label4,
  fed.value5,
  fed.label5,
  fed.value6,
  fed.label6,
  fed.value7,
  fed.label7,
  fed.value8,
  fed.label8,
  fed.value9,
  fed.label9
from
  cmaas_equipment_api_log fed
WHERE
  fed.form_entity_id = 401
  and fed.deleted = 0
  and (
    fed.value2 LIKE concat(
      'http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/',
      '%'
    )
    and fed.value3 = 'ERROR'
  )
ORDER BY
  fed.id DESC
LIMIT
  100 OFFSET 0

image
强制走了索引 idx01 ，没有走索引排序。

select 
  fed.id,
  fed.form_entity_id,
  fed.user_id,
  fed.created_time,
  fed.deleted,
  fed.updated_user_id,
  fed.updated_time,
  fed.flow_instance_id,
  fed.thread_id,
  fed.node_id,
  fed.object_version,
  fed.value1,
  fed.label1,
  fed.value10,
  fed.label10,
  fed.value11,
  fed.label11,
  fed.value12,
  fed.label12,
  fed.value13,
  fed.label13,
  fed.value14,
  fed.label14,
  fed.value15,
  fed.label15,
  fed.value16,
  fed.label16,
  fed.value17,
  fed.label17,
  fed.value18,
  fed.label18,
  fed.value19,
  fed.label19,
  fed.value2,
  fed.label2,
  fed.value20,
  fed.label20,
  fed.value21,
  fed.label21,
  fed.value22,
  fed.label22,
  fed.value3,
  fed.label3,
  fed.value4,
  fed.label4,
  fed.value5,
  fed.label5,
  fed.value6,
  fed.label6,
  fed.value7,
  fed.label7,
  fed.value8,
  fed.label8,
  fed.value9,
  fed.label9
from
  cmaas_equipment_api_log fed
WHERE
  fed.form_entity_id = 401
  and fed.deleted = 0
  and (
    fed.value2 LIKE concat(
      'http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/',
      '%'
    )
    and fed.value3 = 'ERROR'
  )
ORDER BY
  fed.id DESC
LIMIT
  100 OFFSET 0

image
可以看到 没有走idx01 索引，但是走了索引排序

需求：走idx01 索引，排序 也走idx01 索引，如何实现？

3 个赞
#3

这个符合预期 你建立的正序索引 排序的时候做的倒序 不走索引是正常的

image
image1470×380 58.8 KB

4 个赞
#4 
select /*+index(fed idx01)*/
  fed.id,
  fed.form_entity_id,
  fed.user_id,
  fed.created_time,
  fed.deleted,
  fed.updated_user_id,
  fed.updated_time,
  fed.flow_instance_id,
  fed.thread_id,
  fed.node_id,
  fed.object_version,
  fed.value1,
  fed.label1,
  fed.value10,
  fed.label10,
  fed.value11,
  fed.label11,
  fed.value12,
  fed.label12,
  fed.value13,
  fed.label13,
  fed.value14,
  fed.label14,
  fed.value15,
  fed.label15,
  fed.value16,
  fed.label16,
  fed.value17,
  fed.label17,
  fed.value18,
  fed.label18,
  fed.value19,
  fed.label19,
  fed.value2,
  fed.label2,
  fed.value20,
  fed.label20,
  fed.value21,
  fed.label21,
  fed.value22,
  fed.label22,
  fed.value3,
  fed.label3,
  fed.value4,
  fed.label4,
  fed.value5,
  fed.label5,
  fed.value6,
  fed.label6,
  fed.value7,
  fed.label7,
  fed.value8,
  fed.label8,
  fed.value9,
  fed.label9
from
  cmaas_equipment_api_log fed
WHERE
  fed.form_entity_id = 401
  and fed.deleted = 0
  and (
    fed.value2 LIKE concat(
      'http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/',
      '%'
    )
    and fed.value3 = 'ERROR'
  )
ORDER BY
  fed.id ASC
LIMIT
  100 OFFSET 0

image
image751×246 16.1 KB

我改成了 fed.id ASC 排序，强制走 idx01 ，还是没走索引排序，请教

3 个赞
#5

创建idx01索引的语句发出来看看

3 个赞
#6 
create  index idx01 on cmaas_equipment_api_log(`deleted`, `value2`, `value3`, `value15`, `value1`, `value16`);
2 个赞
#7

image
image1015×180 25.6 KB

2 个赞
#8

create index idx_fed_01 on cmaas_equipment_api_log (form_entity_id,deleted,value3,id,value2);

创建这个索引试试

1 个赞
#9

你用explain extended执行 执行计划全部拷贝到txt文本里 上传一下 看看执行计划怎么走的

1 个赞
#10

我这个是分区表，索引是 本地索引，跟这个有关系嘛？

1 个赞
#11 
===============================================================================
|ID|OPERATOR              |NAME                         |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN      |                             |100     |173729      |
|1 |├─LIMIT               |                             |100     |173479      |
|2 |│ └─TOP-N SORT        |                             |100     |173479      |
|3 |│   └─TABLE RANGE SCAN|fed(idx01)                   |77426   |168875      |
|4 |└─TABLE GET           |cmaas_equipment_api_log_alias|1       |3           |
===============================================================================
Outputs & filters:
-------------------------------------
  0 - output([fed.id(0x7fa4cc4364b0)], [fed.form_entity_id(0x7fa4cc431160)], [cmaas_equipment_api_log_alias.user_id(0x7fa4cc436e90)], [cmaas_equipment_api_log_alias.created_time(0x7fa4cc437510)],
       [fed.deleted(0x7fa4cc4324c0)], [cmaas_equipment_api_log_alias.updated_user_id(0x7fa4cc437ef0)], [cmaas_equipment_api_log_alias.updated_time(0x7fa4cc438570)],
       [cmaas_equipment_api_log_alias.flow_instance_id(0x7fa4cc438bf0)], [cmaas_equipment_api_log_alias.thread_id(0x7fa4cc439270)], [cmaas_equipment_api_log_alias.node_id(0x7fa4cc4398f0)],
       [cmaas_equipment_api_log_alias.object_version(0x7fa4cc439f70)], [cmaas_equipment_api_log_alias.value1(0x7fa4cc43a5f0)], [cmaas_equipment_api_log_alias.label1(0x7fa4cc43ac70)],
       [cmaas_equipment_api_log_alias.value10(0x7fa4cc43b2f0)], [cmaas_equipment_api_log_alias.label10(0x7fa4cc43b970)], [cmaas_equipment_api_log_alias.value11(0x7fa4cc43df00)],
       [cmaas_equipment_api_log_alias.label11(0x7fa4cc43e580)], [cmaas_equipment_api_log_alias.value12(0x7fa4cc44c510)], [cmaas_equipment_api_log_alias.label12(0x7fa4cc44cb90)],
       [cmaas_equipment_api_log_alias.value13(0x7fa4cc44d210)], [cmaas_equipment_api_log_alias.label13(0x7fa4cc44d890)], [cmaas_equipment_api_log_alias.value14(0x7fa4cc44df10)],
       [cmaas_equipment_api_log_alias.label14(0x7fa4cc44e590)], [cmaas_equipment_api_log_alias.value15(0x7fa4cc44ec10)], [cmaas_equipment_api_log_alias.label15(0x7fa4cc44f290)],
       [cmaas_equipment_api_log_alias.value16(0x7fa4cc44f910)], [cmaas_equipment_api_log_alias.label16(0x7fa4cc44ff90)], [cmaas_equipment_api_log_alias.value17(0x7fa4cc450610)],
       [cmaas_equipment_api_log_alias.label17(0x7fa4cc450c90)], [cmaas_equipment_api_log_alias.value18(0x7fa4cc451310)], [cmaas_equipment_api_log_alias.label18(0x7fa4cc451990)],
       [cmaas_equipment_api_log_alias.value19(0x7fa4cc452010)], [cmaas_equipment_api_log_alias.label19(0x7fa4cc452690)], [fed.value2(0x7fa4cc4346c0)], [cmaas_equipment_api_log_alias.label2(0x7fa4cc453070)],
       [cmaas_equipment_api_log_alias.value20(0x7fa4cc4536f0)], [cmaas_equipment_api_log_alias.label20(0x7fa4cc46ef80)], [cmaas_equipment_api_log_alias.value21(0x7fa4cc46f600)],
       [cmaas_equipment_api_log_alias.label21(0x7fa4cc46fc80)], [cmaas_equipment_api_log_alias.value22(0x7fa4cc470300)], [cmaas_equipment_api_log_alias.label22(0x7fa4cc470980)],
       [fed.value3(0x7fa4cc435ba0)], [cmaas_equipment_api_log_alias.label3(0x7fa4cc471360)], [cmaas_equipment_api_log_alias.value4(0x7fa4cc4719e0)], [cmaas_equipment_api_log_alias.label4(0x7fa4cc472060)],
       [cmaas_equipment_api_log_alias.value5(0x7fa4cc4726e0)], [cmaas_equipment_api_log_alias.label5(0x7fa4cc472d60)], [cmaas_equipment_api_log_alias.value6(0x7fa4cc4733e0)],
       [cmaas_equipment_api_log_alias.label6(0x7fa4cc473a60)], [cmaas_equipment_api_log_alias.value7(0x7fa4cc4740e0)], [cmaas_equipment_api_log_alias.label7(0x7fa4cc474760)],
       [cmaas_equipment_api_log_alias.value8(0x7fa4cc474de0)], [cmaas_equipment_api_log_alias.label8(0x7fa4cc475460)], [cmaas_equipment_api_log_alias.value9(0x7fa4cc475ae0)],
       [cmaas_equipment_api_log_alias.label9(0x7fa4cc476160)]), filter(nil), rowset=256
      conds(nil), nl_params_([fed.id(0x7fa4cc4364b0)(:0)], [fed.form_entity_id(0x7fa4cc431160)(:1)]), use_batch=false
  1 - output([fed.id(0x7fa4cc4364b0)], [fed.form_entity_id(0x7fa4cc431160)], [fed.deleted(0x7fa4cc4324c0)], [fed.value2(0x7fa4cc4346c0)], [fed.value3(0x7fa4cc435ba0)]), filter(nil), rowset=256
      limit(100), offset(0)
  2 - output([fed.id(0x7fa4cc4364b0)], [fed.form_entity_id(0x7fa4cc431160)], [fed.deleted(0x7fa4cc4324c0)], [fed.value2(0x7fa4cc4346c0)], [fed.value3(0x7fa4cc435ba0)]), filter(nil), rowset=256
      sort_keys([fed.id(0x7fa4cc4364b0), ASC]), topn(100 + 0(0x7fa4cc534240))
  3 - output([fed.id(0x7fa4cc4364b0)], [fed.form_entity_id(0x7fa4cc431160)], [fed.deleted(0x7fa4cc4324c0)], [fed.value2(0x7fa4cc4346c0)], [fed.value3(0x7fa4cc435ba0)]), filter([fed.value3(0x7fa4cc435ba
      0) = 'ERROR'(0x7fa4cc435410)], [fed.form_entity_id(0x7fa4cc431160) = 401(0x7fa4cc4309d0)]), rowset=256
      access([fed.id(0x7fa4cc4364b0)], [fed.form_entity_id(0x7fa4cc431160)], [fed.deleted(0x7fa4cc4324c0)], [fed.value2(0x7fa4cc4346c0)], [fed.value3(0x7fa4cc435ba0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false,false], 
      range_key([fed.deleted(0x7fa4cc5414a0)], [fed.value2(0x7fa4cc5417c0)], [fed.value3(0x7fa4cc541ae0)], [fed.value15(0x7fa4cc541e00)], [fed.value1(0x7fa4cc542120)],
       [fed.value16(0x7fa4cc542440)], [fed.id(0x7fa4cc542760)], [fed.form_entity_id(0x7fa4cc542a80)]), range(0,http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/,
      ERROR,MIN,MIN,MIN,MIN,MIN ; 0,http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/�
      ��
      ��
      ��
      � ,ERROR,MAX,MAX,MAX,MAX,MAX), 
      range_cond([fed.deleted(0x7fa4cc4324c0) = 0(0x7fa4cc431d30)], [(T_OP_LIKE, fed.value2(0x7fa4cc4346c0), concat('http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/',
       '%')(0x7fa4cc433510), '\\')(0x7fa4cc432a90)])
  4 - output([cmaas_equipment_api_log_alias.user_id(0x7fa4cc436e90)], [cmaas_equipment_api_log_alias.created_time(0x7fa4cc437510)], [cmaas_equipment_api_log_alias.updated_user_id(0x7fa4cc437ef0)],
       [cmaas_equipment_api_log_alias.updated_time(0x7fa4cc438570)], [cmaas_equipment_api_log_alias.flow_instance_id(0x7fa4cc438bf0)], [cmaas_equipment_api_log_alias.thread_id(0x7fa4cc439270)],
       [cmaas_equipment_api_log_alias.node_id(0x7fa4cc4398f0)], [cmaas_equipment_api_log_alias.object_version(0x7fa4cc439f70)], [cmaas_equipment_api_log_alias.value1(0x7fa4cc43a5f0)],
       [cmaas_equipment_api_log_alias.label1(0x7fa4cc43ac70)], [cmaas_equipment_api_log_alias.value10(0x7fa4cc43b2f0)], [cmaas_equipment_api_log_alias.label10(0x7fa4cc43b970)],
       [cmaas_equipment_api_log_alias.value11(0x7fa4cc43df00)], [cmaas_equipment_api_log_alias.label11(0x7fa4cc43e580)], [cmaas_equipment_api_log_alias.value12(0x7fa4cc44c510)],
       [cmaas_equipment_api_log_alias.label12(0x7fa4cc44cb90)], [cmaas_equipment_api_log_alias.value13(0x7fa4cc44d210)], [cmaas_equipment_api_log_alias.label13(0x7fa4cc44d890)],
       [cmaas_equipment_api_log_alias.value14(0x7fa4cc44df10)], [cmaas_equipment_api_log_alias.label14(0x7fa4cc44e590)], [cmaas_equipment_api_log_alias.value15(0x7fa4cc44ec10)],
       [cmaas_equipment_api_log_alias.label15(0x7fa4cc44f290)], [cmaas_equipment_api_log_alias.value16(0x7fa4cc44f910)], [cmaas_equipment_api_log_alias.label16(0x7fa4cc44ff90)],
       [cmaas_equipment_api_log_alias.value17(0x7fa4cc450610)], [cmaas_equipment_api_log_alias.label17(0x7fa4cc450c90)], [cmaas_equipment_api_log_alias.value18(0x7fa4cc451310)],
       [cmaas_equipment_api_log_alias.label18(0x7fa4cc451990)], [cmaas_equipment_api_log_alias.value19(0x7fa4cc452010)], [cmaas_equipment_api_log_alias.label19(0x7fa4cc452690)],
       [cmaas_equipment_api_log_alias.label2(0x7fa4cc453070)], [cmaas_equipment_api_log_alias.value20(0x7fa4cc4536f0)], [cmaas_equipment_api_log_alias.label20(0x7fa4cc46ef80)],
       [cmaas_equipment_api_log_alias.value21(0x7fa4cc46f600)], [cmaas_equipment_api_log_alias.label21(0x7fa4cc46fc80)], [cmaas_equipment_api_log_alias.value22(0x7fa4cc470300)],
       [cmaas_equipment_api_log_alias.label22(0x7fa4cc470980)], [cmaas_equipment_api_log_alias.label3(0x7fa4cc471360)], [cmaas_equipment_api_log_alias.value4(0x7fa4cc4719e0)],
       [cmaas_equipment_api_log_alias.label4(0x7fa4cc472060)], [cmaas_equipment_api_log_alias.value5(0x7fa4cc4726e0)], [cmaas_equipment_api_log_alias.label5(0x7fa4cc472d60)],
       [cmaas_equipment_api_log_alias.value6(0x7fa4cc4733e0)], [cmaas_equipment_api_log_alias.label6(0x7fa4cc473a60)], [cmaas_equipment_api_log_alias.value7(0x7fa4cc4740e0)],
       [cmaas_equipment_api_log_alias.label7(0x7fa4cc474760)], [cmaas_equipment_api_log_alias.value8(0x7fa4cc474de0)], [cmaas_equipment_api_log_alias.label8(0x7fa4cc475460)],
       [cmaas_equipment_api_log_alias.value9(0x7fa4cc475ae0)], [cmaas_equipment_api_log_alias.label9(0x7fa4cc476160)]), filter(nil), rowset=256
      access([cmaas_equipment_api_log_alias.user_id(0x7fa4cc436e90)], [cmaas_equipment_api_log_alias.created_time(0x7fa4cc437510)], [cmaas_equipment_api_log_alias.updated_user_id(0x7fa4cc437ef0)],
       [cmaas_equipment_api_log_alias.updated_time(0x7fa4cc438570)], [cmaas_equipment_api_log_alias.flow_instance_id(0x7fa4cc438bf0)], [cmaas_equipment_api_log_alias.thread_id(0x7fa4cc439270)],
       [cmaas_equipment_api_log_alias.node_id(0x7fa4cc4398f0)], [cmaas_equipment_api_log_alias.object_version(0x7fa4cc439f70)], [cmaas_equipment_api_log_alias.value1(0x7fa4cc43a5f0)],
       [cmaas_equipment_api_log_alias.label1(0x7fa4cc43ac70)], [cmaas_equipment_api_log_alias.value10(0x7fa4cc43b2f0)], [cmaas_equipment_api_log_alias.label10(0x7fa4cc43b970)],
       [cmaas_equipment_api_log_alias.value11(0x7fa4cc43df00)], [cmaas_equipment_api_log_alias.label11(0x7fa4cc43e580)], [cmaas_equipment_api_log_alias.value12(0x7fa4cc44c510)],
       [cmaas_equipment_api_log_alias.label12(0x7fa4cc44cb90)], [cmaas_equipment_api_log_alias.value13(0x7fa4cc44d210)], [cmaas_equipment_api_log_alias.label13(0x7fa4cc44d890)],
       [cmaas_equipment_api_log_alias.value14(0x7fa4cc44df10)], [cmaas_equipment_api_log_alias.label14(0x7fa4cc44e590)], [cmaas_equipment_api_log_alias.value15(0x7fa4cc44ec10)],
       [cmaas_equipment_api_log_alias.label15(0x7fa4cc44f290)], [cmaas_equipment_api_log_alias.value16(0x7fa4cc44f910)], [cmaas_equipment_api_log_alias.label16(0x7fa4cc44ff90)],
       [cmaas_equipment_api_log_alias.value17(0x7fa4cc450610)], [cmaas_equipment_api_log_alias.label17(0x7fa4cc450c90)], [cmaas_equipment_api_log_alias.value18(0x7fa4cc451310)],
       [cmaas_equipment_api_log_alias.label18(0x7fa4cc451990)], [cmaas_equipment_api_log_alias.value19(0x7fa4cc452010)], [cmaas_equipment_api_log_alias.label19(0x7fa4cc452690)],
       [cmaas_equipment_api_log_alias.label2(0x7fa4cc453070)], [cmaas_equipment_api_log_alias.value20(0x7fa4cc4536f0)], [cmaas_equipment_api_log_alias.label20(0x7fa4cc46ef80)],
       [cmaas_equipment_api_log_alias.value21(0x7fa4cc46f600)], [cmaas_equipment_api_log_alias.label21(0x7fa4cc46fc80)], [cmaas_equipment_api_log_alias.value22(0x7fa4cc470300)],
       [cmaas_equipment_api_log_alias.label22(0x7fa4cc470980)], [cmaas_equipment_api_log_alias.label3(0x7fa4cc471360)], [cmaas_equipment_api_log_alias.value4(0x7fa4cc4719e0)],
       [cmaas_equipment_api_log_alias.label4(0x7fa4cc472060)], [cmaas_equipment_api_log_alias.value5(0x7fa4cc4726e0)], [cmaas_equipment_api_log_alias.label5(0x7fa4cc472d60)],
       [cmaas_equipment_api_log_alias.value6(0x7fa4cc4733e0)], [cmaas_equipment_api_log_alias.label6(0x7fa4cc473a60)], [cmaas_equipment_api_log_alias.value7(0x7fa4cc4740e0)],
       [cmaas_equipment_api_log_alias.label7(0x7fa4cc474760)], [cmaas_equipment_api_log_alias.value8(0x7fa4cc474de0)], [cmaas_equipment_api_log_alias.label8(0x7fa4cc475460)],
       [cmaas_equipment_api_log_alias.value9(0x7fa4cc475ae0)], [cmaas_equipment_api_log_alias.label9(0x7fa4cc476160)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([cmaas_equipment_api_log_alias.id(0x7fa4cc544cb0)], [cmaas_equipment_api_log_alias.form_entity_id(0x7fa4cc544fd0)]), range(MIN ; MAX), 
      range_cond([cmaas_equipment_api_log_alias.id(0x7fa4cc544cb0) = :0(0x7fa4cc571c70)], [cmaas_equipment_api_log_alias.form_entity_id(0x7fa4cc544fd0) 
      = :1(0x7fa4cc572540)])
Used Hint:
-------------------------------------
  /*+
      
      INDEX("fed" "idx01")
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_LATE_MATERIALIZATION(@"SEL$1")
      INDEX(@"SEL$1" "fed"@"SEL$1" "idx01")
      OPTIMIZER_FEATURES_ENABLE('4.2.2.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  fed:
      table_rows:9755603
      physical_range_rows:154850
      logical_range_rows:154850
      index_back_rows:0
      output_rows:77425
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[cmaas_equipment_api_log_ix1, cmaas_equipment_api_log_idx1, idx01, cmaas_equipment_api_log]
      pruned_index_name:[cmaas_equipment_api_log_ix1, cmaas_equipment_api_log_idx1, cmaas_equipment_api_log]
      stats version:1727189361955108
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
      
  Plan Type:
      LOCAL
  Note:
      Degree of Parallelisim is 0 because of table property
  Expr Constraints:
      concat('http://cmaas-e-fd.catl.com/flow/api/v2/flow-rest/production-execution/', '%') result is PRECISE
1 个赞
#12

这个也是符合预期 我看索引是你强制走的索引 索引排序 只有当索引列顺序和ORDER BY的列顺序完全一致，并且排序方式都是一样的情况时才能使用索引对结果做排序
如果存在表关联，则只有当ORDER BY引用的字段全部为第一个表是，才能使用索引排序
同时ORDER BY使用索引排序的时候是符合和查询语句一样的最左前缀法则的，否则ob mysql还是需要执行排序操作，无法利用索引排序

2 个赞