# [Know Issue] SQL 执行计划没有使用最优的 index merge ## Key Words index merge,sql plan,optimizer ## Description ```SQL set global tidb_enable_index_merge=on; CREATE TABLE t1 ( id BIGINT NOT NULL AUTO_INCREMENT, c_id bigint(20) NOT NULL, l_hash varbinary(255) DEFAULT NULL, b_hash varbinary(255) DEFAULT NULL, s_hash varbinary(255) DEFAULT NULL, d varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (c_id,id) /*T![clustered_index] CLUSTERED */, UNIQUE KEY (id), KEY (l_hash), KEY (b_hash,d) ); ``` 原始 SQL 如下： ```SQL mysql> EXPLAIN SELECT * FROM t1 WHERE id != 107053556 AND ( -> (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf') -> OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047') -> OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047') -> ); +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TableReader_7 | 0.02 | root | | data:Selection_6 | | └─Selection_6 | 0.02 | cop[tikv] | | ne(test.t1.id, 107053556), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf")), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")), and(eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")))) | | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) ``` SQL 去掉 id != 107053556 条件后，执行计划能够正确走 index merge ```SQL mysql> EXPLAIN SELECT * FROM t1 WHERE ( -> (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf') -> OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047') -> OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047') -> ); +--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexMerge_13 | 0.00 | root | | type: union | | ├─IndexRangeScan_8(Build) | 10.00 | cop[tikv] | table:t1, index:l_hash(l_hash) | range:[0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863,0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863], keep order:false, stats:pseudo | | ├─IndexRangeScan_9(Build) | 10.00 | cop[tikv] | table:t1, index:l_hash(l_hash) | range:[0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863,0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863], keep order:false, stats:pseudo | | ├─IndexRangeScan_10(Build) | 10.00 | cop[tikv] | table:t1, index:b_hash(b_hash, d) | range:[0x30342D61613665353331393061663336323530313865633836666162373739303732366634633931386133366564373430383466346363626262333862323963656466,0x30342D61613665353331393061663336323530313865633836666162373739303732366634633931386133366564373430383466346363626262333862323963656466], keep order:false, stats:pseudo | | └─Selection_12(Probe) | 0.00 | cop[tikv] | | or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf")), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")), and(eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")))) | | └─TableRowIDScan_11 | 19.99 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.01 sec) ``` ## Root Cause 当存在替代(非选择性)索引时，Planner 将不考虑 IndexMerge，这是当前 index merge 的一个限制。 ## Resolution SQL 添加 hint，强制 TiDB 使用 index merge，从而优化查询执行的速度和效率。 Optimizer Hints 参考文档：[optimizer-hints](https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#optimizer-hints) ## Affected Version ALL