mysql - 为什么Mysql JOIN查询-完整扫描不使用索引

Question

当我只在一张表中选择查询时，我们得到了使用索引的响应，但是当使用 join 语句查询时，mysql 对表进行全扫描？

第一个查询

select *
    from t_deposit_trx
    where remit_tx_id = '3a33ff14-8d31-45d0-b64f-8a251c4b19a5'

1 SIMPLE t_deposit_trx ref t_deposit_trx_remit_tx_id_index
t_deposit_trx_remit_tx_id_index 110 const 1 Using index condition

第二个查询

select tx_id
from t_settle_trx
where report_date >= '2019-03-01'
  and report_date <= '2019-03-16'
  and tx_type = 'CANCEL'

1 SIMPLE t_settle_trx range t_settle_trx_report_date_tx_type_index t_settle_trx_report_date_tx_type_index 196 5263 Using index condition

2个查询操作良好。使用索引和速度都很好。

但是连接两个表，速度很慢。

select * from t_deposit_trx
force index (t_deposit_trx_remit_tx_id_index)
where remit_tx_id in (
  select tx_id
  from t_settle_trx
  where report_date >= '2019-03-01'
    and report_date <= '2019-03-02'
    and tx_type = 'CANCEL'
)

1 PRIMARY t_deposit_trx ALL 55724
1 PRIMARY t_settle_trx range t_settle_trx_report_date_tx_type_index t_settle_trx_report_date_tx_type_index 196 299 Using index condition; Using where; FirstMatch(t_deposit_trx); Using join buffer (flat, BNL join)

我们可以看到上面的结果..t_settle_trx 使用范围扫描并获取 tx_id ，接下来我希望查询使用索引“t_settle_trx_report_date_tx_type_index”但它使用全扫描..

不知道为什么？

Answer 1

查询 2不是最优的。扭转索引:

INDEX(tx_type, report_date)

也就是说，无论基数如何，都将使用 = 测试的列放在第一位。

IN ( SELECT ... ) 不是JOIN。 (在较新的版本中，它可能会被转换为JOIN。)

尝试一下:

SELECT  d.*
    FROM  t_settle_trx AS s
    JOIN  t_deposit_trx AS d  ON s.tx_id = d.remit_tx_id
    WHERE  s.tx_type = 'CANCEL'
      AND  s.report_date >= '2019-03-01'
      AND  s.report_date <  '2019-03-01' + INTERVAL 2 DAY

s: INDEX(tx_type, report_date, tx_id)
d: INDEX(remit_tx_id)

将带有 JOIN 的 SELECT 视为从具有 WHERE 子句的表开始。

(注意:我排列了表格和 where 子句的顺序，以便您可以看到优化器的思考方式。表格的顺序和 where 子句的顺序不相关；优化器会根据需要重新排列.)

我的配方应该

• 充分利用两个表上的索引。
• 避免全表扫描
• 在它查看的第一个表上进行所有过滤

全表扫描不一定是一件坏事。如果无论如何都会触及表的大部分，那么简单地扫描表实际上更快，而不是在索引的 BTree 和数据的 BTree 之间跳转。 (您的具体案例还有其他缺陷；我重点关注它们。)

INDEX(tx_type, report_date, tx_id)

是“覆盖”，因为查询所需的所有列都可以在一个索引中找到。 “覆盖”可以为您带来轻微的额外性能提升。它在EXPLAIN中通过Using index(而不是Using index condition)指示。

对于 tx_seq，这可能是最佳的:

INDEX(tx_type, report_date, tx_seq)