【概述】 一个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 小时日志)