连接另一个表B时，为什么Mysql对表A使用全表扫描? [英] Why does Mysql use a full table scan for table A when joining another table B?

问题描述

我有一个表A和一个表B.我从表A中选择信息，这里我只需要表B联接表A的表A中的信息.我在联合列和WHERE子句上都有索引.

I have a table A and a table B. I am selecting information from table A where I need only the information from table A where tabel B joins table A. I have indexes on columns that are joint and on the WHERE clause.

这是选择代码:

SELECT *
FROM tableA

INNER JOIN tableB AS tableB
ON tableB.id = tableA.id
AND tableB.type = 'car'

当我使用说明时，我可以看到mysql正在对tableA中的所有行使用全表扫描.此外，它正确地使用索引来联接tableB.

When I use explain I can see that mysql is using a full table scan for all the rows in tableA. Further it properly uses the index to join tableB.

因此，Mysql似乎正在做的是扫描talbeA中的所有行，以查看是否与tableB匹配.但是，因为我在两个表的ID上都有索引，所以我不希望进行tablescan.由于我的表有50.000行，因此此查询需要花费几秒钟的时间(对于我的应用程序来说确实太长了).

So what Mysql seems to be doing is scan all the rows in talbeA to see if there is a match with tableB. However as I have indexes on id of both tables I don't expect a tablescan. As my table has 50.000 rows this query is taking a few seconds (which is really too long for my application).

这是解释:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  tableA      ALL     tableA.id       NULL    NULL    49898   
1   SIMPLE  tableB      eq_ref  tableB.type     tableB.type 4   1       Use WHere

我的问题:如何优化该查询和索引，以便Mysql直接从tableA中选择与tableB匹配的行，而无需扫描talbeA中的所有行?

My question: How can I optimize this query and indexes such that Mysql directly selects those rows from tableA that have a match with tableB without scanning all rows in talbeA?

推荐答案

这是您可以为JOIN查询获得的最大优化 请向她咨询更多详细信息 https://cryptkcoding. com/blog/2012/04/06/how-to-optimize-mysql-join-queries-through-indexing/

This is the maximum optimization you can get for a JOIN query refer her for more details https://cryptkcoding.com/blog/2012/04/06/how-to-optimize-mysql-join-queries-through-indexing/

这篇关于连接另一个表B时，为什么Mysql对表A使用全表扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！