这个SQL怎么调优才能达到mysql的性能？

🪐 TiDB 技术问题 性能调优
1

【概述】 一个SQL在TiDB下执行太慢，MySQL下很快，不知道如何做优化。

【背景】 TiDB和MySQL表结构及索引都一致

【现象】 查询响应太慢

【问题】 MySQL中查询只需要0.35s，而TiDB需要401s，差距太离谱，不知哪个地方的优化是关键？

【统计信息是否最新】

    【执行计划内容】

|Projection_60|809181.38|root||us_stock.sym1101.symbol_region, us_stock.stk2305.f001n, <nil>->Column#526, us_stock.com3103.f017n, us_stock.com3103.f017n, us_stock.com3102.f014n, us_stock.com3104.f016n, <nil>->Column#527, <nil>->Column#528, <nil>->Column#529, <nil>->Column#530, us_stock.sym1502.f008n|
|---|---|---|---|---|
|└─HashJoin_61|809181.38|root||left outer join, equal:[eq(us_stock.sym1101.symbol_region, us_stock.sym1502.symbol_region)]|
|  ├─TableReader_234(Build)|10.89|root||data:Selection_233|
|  │ └─Selection_233|10.89|cop[tikv]||eq(us_stock.sym1502.symbol_region, "ORCL-US"), not(isnull(us_stock.sym1502.symbol_region))|
|  │   └─TableFullScan_232|10892.00|cop[tikv]|table:g|keep order:false|
|  └─HashJoin_65(Probe)|806281.62|root||left outer join, equal:[eq(us_stock.sym1101.fiu_id, us_stock.com3104.fiu_id)]|
|    ├─HashJoin_185(Build)|26025.60|root||left outer join, equal:[eq(us_stock.sym1101.fiu_id, us_stock.com3102.fiu_id)]|
|    │ ├─HashJoin_188(Build)|1453.23|root||left outer join, equal:[eq(us_stock.sym1101.fiu_id, us_stock.com3103.fiu_id)]|
|    │ │ ├─HashJoin_191(Build)|46.94|root||left outer join, equal:[eq(us_stock.sym1101.fiu_id, us_stock.com3103.fiu_id)]|
|    │ │ │ ├─IndexJoin_196(Build)|1.52|root||left outer join, inner:IndexLookUp_195, outer key:us_stock.sym1101.fiu_id, inner key:us_stock.stk2305.fiu_id, equal cond:eq(us_stock.sym1101.fiu_id, us_stock.stk2305.fiu_id)|
|    │ │ │ │ ├─TableReader_209(Build)|1.51|root||data:Selection_208|
|    │ │ │ │ │ └─Selection_208|1.51|cop[tikv]||eq(us_stock.sym1101.symbol_region, "ORCL-US")|
|    │ │ │ │ │   └─TableFullScan_207|149936.00|cop[tikv]|table:a|keep order:false|
|    │ │ │ │ └─IndexLookUp_195(Probe)|1.00|root|||
|    │ │ │ │   ├─IndexRangeScan_193(Build)|1.00|cop[tikv]|table:b, index:PRIMARY(FIU_ID)|range: decided by [eq(us_stock.stk2305.fiu_id, us_stock.sym1101.fiu_id)], keep order:false|
|    │ │ │ │   └─TableRowIDScan_194(Probe)|1.00|cop[tikv]|table:b|keep order:false|
|    │ │ │ └─Apply_213(Probe)|321844.99|root||semi join, equal:[eq(us_stock.com3103.f001d, Column#136)]|
|    │ │ │   ├─TableReader_216(Build)|321844.99|root||data:Selection_215|
|    │ │ │   │ └─Selection_215|321844.99|cop[tikv]||like(us_stock.com3103.f003v, "T%", 92)|
|    │ │ │   │   └─TableFullScan_214|730801.00|cop[tikv]|table:x|keep order:false|
|    │ │ │   └─Selection_98(Probe)|0.80|root||not(isnull(Column#136))|
|    │ │ │     └─StreamAgg_100|1.00|root||funcs:max(us_stock.com3103.f001d)->Column#136|
|    │ │ │       └─TopN_101|1.00|root||us_stock.com3103.f001d:desc, offset:0, count:1|
|    │ │ │         └─IndexReader_110|1.00|root||index:TopN_109|
|    │ │ │           └─TopN_109|1.00|cop[tikv]||us_stock.com3103.f001d:desc, offset:0, count:1|
|    │ │ │             └─Selection_108|13.69|cop[tikv]||like(us_stock.com3103.f003v, "T%", 92)|
|    │ │ │               └─IndexRangeScan_107|30.96|cop[tikv]|table:y, index:PRIMARY(FIU_ID, F001D, F003V)|range: decided by [eq(us_stock.com3103.fiu_id, us_stock.com3103.fiu_id)], keep order:false|
|    │ │ └─Apply_218(Probe)|96235.79|root||semi join, equal:[eq(us_stock.com3103.f001d, Column#259)]|
|    │ │   ├─TableReader_221(Build)|96235.79|root||data:Selection_220|
|    │ │   │ └─Selection_220|96235.79|cop[tikv]||eq(us_stock.com3103.f003v, "FY")|
|    │ │   │   └─TableFullScan_219|730801.00|cop[tikv]|table:x|keep order:false|
|    │ │   └─Selection_121(Probe)|0.80|root||not(isnull(Column#259))|
|    │ │     └─StreamAgg_123|1.00|root||funcs:max(us_stock.com3103.f001d)->Column#259|
|    │ │       └─TopN_124|1.00|root||us_stock.com3103.f001d:desc, offset:0, count:1|
|    │ │         └─IndexReader_133|1.00|root||index:TopN_132|
|    │ │           └─TopN_132|1.00|cop[tikv]||us_stock.com3103.f001d:desc, offset:0, count:1|
|    │ │             └─Selection_131|4.09|cop[tikv]||eq(us_stock.com3103.f003v, "FY")|
|    │ │               └─IndexRangeScan_130|30.96|cop[tikv]|table:y, index:PRIMARY(FIU_ID, F001D, F003V)|range: decided by [eq(us_stock.com3103.fiu_id, us_stock.com3103.fiu_id)], keep order:false|
|    │ └─Apply_223(Probe)|86826.05|root||semi join, equal:[eq(us_stock.com3102.f001d, Column#408)]|
|    │   ├─TableReader_226(Build)|86826.05|root||data:Selection_225|
|    │   │ └─Selection_225|86826.05|cop[tikv]||eq(us_stock.com3102.f003v, "FY")|
|    │   │   └─TableFullScan_224|423573.00|cop[tikv]|table:x|keep order:false|
|    │   └─Selection_144(Probe)|0.80|root||not(isnull(Column#408))|
|    │     └─StreamAgg_146|1.00|root||funcs:max(us_stock.com3102.f001d)->Column#408|
|    │       └─TopN_147|1.00|root||us_stock.com3102.f001d:desc, offset:0, count:1|
|    │         └─IndexReader_156|1.00|root||index:TopN_155|
|    │           └─TopN_155|1.00|cop[tikv]||us_stock.com3102.f001d:desc, offset:0, count:1|
|    │             └─Selection_154|3.70|cop[tikv]||eq(us_stock.com3102.f003v, "FY")|
|    │               └─IndexRangeScan_153|17.91|cop[tikv]|table:y, index:PRIMARY(FIU_ID, F001D, F003V)|range: decided by [eq(us_stock.com3102.fiu_id, us_stock.com3102.fiu_id)], keep order:false|
|    └─Apply_228(Probe)|100036.86|root||semi join, equal:[eq(us_stock.com3104.f001d, Column#507)]|
|      ├─TableReader_231(Build)|100036.86|root||data:Selection_230|
|      │ └─Selection_230|100036.86|cop[tikv]||eq(us_stock.com3104.f003v, "FY")|
|      │   └─TableFullScan_229|730815.00|cop[tikv]|table:x|keep order:false|
|      └─Selection_167(Probe)|0.80|root||not(isnull(Column#507))|
|        └─StreamAgg_169|1.00|root||funcs:max(us_stock.com3104.f001d)->Column#507|
|          └─TopN_170|1.00|root||us_stock.com3104.f001d:desc, offset:0, count:1|
|            └─IndexReader_179|1.00|root||index:TopN_178|
|              └─TopN_178|1.00|cop[tikv]||us_stock.com3104.f001d:desc, offset:0, count:1|
|                └─Selection_177|5.22|cop[tikv]||eq(us_stock.com3104.f003v, "FY")|
|                  └─IndexRangeScan_176|30.98|cop[tikv]|table:y, index:PRIMARY(FIU_ID, F001D, F003V)|range: decided by [eq(us_stock.com3104.fiu_id, us_stock.com3104.fiu_id)], keep order:false|

    【 SQL 文本、schema 以及 数据分布】

    >     SELECT
>     	a.SYMBOL_REGION,
>     	b.F001N,
>     	NULL AS F002N,
>     	c.F017N AS F003N,
>     	d.F017N AS F004N,
>     	e.F014N AS F005N,
>     	f.F016N AS F006N,
>     	NULL AS F007N,
>     	NULL AS F008N,
>     	NULL AS F009N,
>     	NULL AS F010N,
>     	g.F008N AS F011N 
>     FROM
>     	SYM1101 a
>     	LEFT JOIN STK2305 b ON a.FIU_ID = b.FIU_ID
>     	LEFT JOIN (
>     	SELECT
>     		* 
>     	FROM
>     		COM3103 x 
>     	WHERE
>     		F001D IN ( SELECT MAX( F001D ) FROM COM3103 y WHERE x.FIU_ID = y.FIU_ID AND F003V LIKE 'T%' ) 
>     		AND F003V LIKE 'T%' 
>     	) c ON a.FIU_ID = c.FIU_ID
>     	LEFT JOIN (
>     	SELECT
>     		* 
>     	FROM
>     		COM3103 x 
>     	WHERE
>     		F001D IN ( SELECT MAX( F001D ) FROM COM3103 y WHERE x.FIU_ID = y.FIU_ID AND F003V = 'FY' ) 
>     		AND F003V = 'FY' 
>     	) d ON a.FIU_ID = d.FIU_ID
>     	LEFT JOIN (
>     	SELECT
>     		* 
>     	FROM
>     		COM3102 x 
>     	WHERE
>     		F001D IN ( SELECT MAX( F001D ) FROM COM3102 y WHERE x.FIU_ID = y.FIU_ID AND F003V = 'FY' ) 
>     		AND F003V = 'FY' 
>     	) e ON a.FIU_ID = e.FIU_ID
>     	LEFT JOIN (
>     	SELECT
>     		* 
>     	FROM
>     		COM3104 x 
>     	WHERE
>     		F001D IN ( SELECT MAX( F001D ) FROM COM3104 y WHERE x.FIU_ID = y.FIU_ID AND F003V = 'FY' ) 
>     		AND F003V = 'FY' 
>     	) f ON a.FIU_ID = f.FIU_ID
>     	LEFT JOIN SYM1502 g ON a.SYMBOL_REGION = g.SYMBOL_REGION 
>     WHERE
>     	a.SYMBOL_REGION = 'ORCL-US'

【业务影响】 无法承受的响应时间

【TiDB 版本】 5.0.2

【附件】 相关日志及监控（https://metricstool.pingcap.com/)

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息
  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志（包含问题前后 1 小时日志）
2

TableFullScan_207|149936.00|cop[tikv]|table:a|keep order:false|
TableFullScan_214|730801.00|cop[tikv]|table:x|keep order:false|
TableFullScan_219|730801.00|cop[tikv]|table:x|keep order:false|
TableFullScan_224|423573.00|cop[tikv]|table:x|keep order:false|
TableFullScan_229|730815.00|cop[tikv]|table:x|keep order:false|

怎么这么多全表扫描？ 能不能尝试优化到范围或者索引查询？

3

加上索引，没有fullscan了还是一样的慢，需要400多秒。

Projection_60 534029.28 root
└─HashJoin_76 534029.28 root
├─IndexLookUp_345(Build) 1.00 root
│ ├─Selection_344(Build) 1.00 cop[tikv]
│ │ └─IndexRangeScan_342 1.00 cop[tikv] table:g, index:SYMBOL_REGION(SYMBOL_REGION)
│ └─TableRowIDScan_343(Probe) 1.00 cop[tikv] table:g
└─HashJoin_81(Probe) 534029.28 root
├─HashJoin_277(Build) 17237.69 root
│ ├─HashJoin_280(Build) 962.53 root
│ │ ├─HashJoin_283(Build) 31.09 root
│ │ │ ├─IndexJoin_288(Build) 1.00 root
│ │ │ │ ├─IndexLookUp_304(Build) 1.00 root
│ │ │ │ │ ├─IndexRangeScan_302(Build) 1.00 cop[tikv] table:a, index:SYMBOL_REGION(SYMBOL_REGION)
│ │ │ │ │ └─TableRowIDScan_303(Probe) 1.00 cop[tikv] table:a
│ │ │ │ └─IndexLookUp_287(Probe) 1.00 root
│ │ │ │ ├─IndexRangeScan_285(Build) 1.00 cop[tikv] table:b, index:PRIMARY(FIU_ID)
│ │ │ │ └─TableRowIDScan_286(Probe) 1.00 cop[tikv] table:b
│ │ │ └─Apply_308(Probe) 307445.00 root
│ │ │ ├─IndexLookUp_314(Build) 307445.00 root
│ │ │ │ ├─IndexRangeScan_312(Build) 307445.00 cop[tikv] table:x, index:F003V(F003V)
│ │ │ │ └─TableRowIDScan_313(Probe) 307445.00 cop[tikv] table:x
│ │ │ └─Selection_114(Probe) 0.80 root
│ │ │ └─StreamAgg_116 1.00 root
│ │ │ └─TopN_117 1.00 root
│ │ │ └─IndexReader_126 1.00 root
│ │ │ └─TopN_125 1.00 cop[tikv]
│ │ │ └─Selection_124 13.08 cop[tikv]
│ │ │ └─IndexRangeScan_123 30.96 cop[tikv] table:y, index:PRIMARY(FIU_ID, F001D, F003V)
│ │ └─Apply_316(Probe) 95846.00 root
│ │ ├─IndexLookUp_322(Build) 95846.00 root
│ │ │ ├─IndexRangeScan_320(Build) 95846.00 cop[tikv] table:x, index:F003V(F003V)
│ │ │ └─TableRowIDScan_321(Probe) 95846.00 cop[tikv] table:x
│ │ └─Selection_156(Probe) 0.80 root
│ │ └─StreamAgg_158 1.00 root
│ │ └─TopN_159 1.00 root
│ │ └─IndexReader_168 1.00 root
│ │ └─TopN_167 1.00 cop[tikv]
│ │ └─Selection_166 4.08 cop[tikv]
│ │ └─IndexRangeScan_165 30.96 cop[tikv] table:y, index:PRIMARY(FIU_ID, F001D, F003V)
│ └─Apply_324(Probe) 86240.00 root
│ ├─IndexLookUp_330(Build) 86240.00 root
│ │ ├─IndexRangeScan_328(Build) 86240.00 cop[tikv] table:x, index:F003V(F003V)
│ │ └─TableRowIDScan_329(Probe) 86240.00 cop[tikv] table:x
│ └─Selection_198(Probe) 0.80 root
│ └─StreamAgg_200 1.00 root
│ └─TopN_201 1.00 root
│ └─IndexReader_210 1.00 root
│ └─TopN_209 1.00 cop[tikv]
│ └─Selection_208 3.67 cop[tikv]
│ └─IndexRangeScan_207 17.91 cop[tikv] table:y, index:PRIMARY(FIU_ID, F001D, F003V)
└─Apply_332(Probe) 81252.00 root
├─IndexLookUp_338(Build) 81252.00 root
│ ├─IndexRangeScan_336(Build) 81252.00 cop[tikv] table:x, index:F003V(F003V)
│ └─TableRowIDScan_337(Probe) 81252.00 cop[tikv] table:x
└─Selection_240(Probe) 0.80 root
└─StreamAgg_242 1.00 root
└─TopN_243 1.00 root
└─IndexReader_252 1.00 root
└─TopN_251 1.00 cop[tikv]
└─Selection_250 4.24 cop[tikv]
└─IndexRangeScan_249 30.98 cop[tikv] table:y, index:PRIMARY(FIU_ID, F001D, F003V)
4

SHOW STATS_HEALTHY 通过语句查询相关表的健康度情况，如果健康度太低，建议使用analyze进行统计信息收集，提升健康度