mysql - 6 个表的慢内连接

Question

抱歉，我的 SQL 知识很业余。

SQL fiddle :http://sqlfiddle.com/#!2/5640d/1请点击上面的链接引用数据库结构和查询。

我有 6 个表，每个数据在每个表中只占一行，我有 3 个相同的列 Custgroup、RandomNumber 和 user_id 在所有 6 个表中。

Custgroup是组名，在组内每条数据都有一个唯一的RandomNumber。

查询在第一次运行时很慢(随机花费几秒到几分钟)，之后会很快，但只针对前几页。如果我点击第 20 或 30+ 页，它将不停地加载(刚刚用了大概5分钟)。而且数据不多，只有5000行，以后麻烦大了。而且我还没有添加任何WHERE子句，因为我需要对每一列进行过滤在我的网站上。(不是我的主意，是我老板要求的)。

我尝试将其更改为 LEFT JOIN、JOIN 和我能找到的任何其他方式，但加载仍然很慢。

我为所有表的 user_id、Custgroup 和 RandomNumber 添加了 INDEX。无论如何要解决这个问题？我从来不擅长使用 JOIN，对我的数据库来说真的很慢。

或者请告诉我，如果我的表结构真的很糟糕，我愿意重做。

谢谢。

**已编辑

运行说明:

    id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra

    1   SIMPLE  tE  ALL     NULL    NULL    NULL    NULL    5685    

    1   SIMPLE  tA  ALL     NULL    NULL    NULL    NULL    6072    Using join buffer

    1   SIMPLE  t1  ref     user_id,Custgroup,RandomNumber  RandomNumber    23  func    1   Using where

    1   SIMPLE  tB  ALL     NULL    NULL    NULL    NULL    5868    Using where; Using join buffer

    1   SIMPLE  tC  ALL     NULL    NULL    NULL    NULL    6043    Using where; Using join buffer

    1   SIMPLE  tD  ALL     NULL    NULL    NULL    NULL    5906    Using where; Using join buffer


    Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
    PRIMARY BTREE   Yes No  ID  6033    A       
    RandomNumber    BTREE   No  No  RandomNumber    6033    A       
    Custgroup   BTREE   No  No  Custgroup   1   A       
    user_id BTREE   No  No  user_id 1   A       

已编辑:EXPLAIN EXTENDED .....

    id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
    1   SIMPLE  tE  ALL     NULL    NULL    NULL    NULL    6084    100.00  
    1   SIMPLE  t1  ref     user_id,Custgroup,RandomNumber  RandomNumber    23  func    1   100.00  Using where
    1   SIMPLE  tB  ALL     NULL    NULL    NULL    NULL    5664    100.00  Using where; Using join buffer
    1   SIMPLE  tC  ALL     NULL    NULL    NULL    NULL    5976    100.00  Using where; Using join buffer
    1   SIMPLE  tA  ALL     NULL    NULL    NULL    NULL    6065    100.00  Using where; Using join buffer
    1   SIMPLE  tD  ALL     NULL    NULL    NULL    NULL    6286    100.00  Using where; Using join buffer

Answer 1

这种结构的逻辑索引必须是

CREATE INDEX UserAddedRecord1_ndx ON UserAddedRecord1 (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_A_ndx ON UserAddedRecord1_A (Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_B_ndx ON UserAddedRecord1_B (Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_C_ndx ON UserAddedRecord1_C (Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_D_ndx ON UserAddedRecord1_D (Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_E_ndx ON UserAddedRecord1_E (Custgroup, RandomNumber);

如果您要添加 WHERE 子句，它们应该在 JOIN 条件(前提是您运行相等或 IN 搜索，例如 City = "New York")。例如，如果 City 在 UserAddedRecord1_B 中，则 UserAddedRecord1_B_ndx 应该是 City, Custgroup, RandomNumber。

但在这一点上，我不得不问，为什么？显然，您总是拥有同一用户的记录。例如:

t1.Cell,t1.Name,t1.Gender,t1.Birthday
tA.Email,tA.State,tA.Address,tA.City,tA.Postcode

...很明显，您不能在这里有两个不同的用户(并且将电子邮件放在同一个 block 中，因为 Postcode 告诉我这并不是真正打算作为一对多关系)。

tB.Website,tB.Description,
tC.Model,tC.Capital,tC.Registry,tC.NoEmployees,
tD.SetUpDate,tD.PeopleInCharge,tD.Certification,tD.AddOEM,
tD.NoResearcher,tD.RoomSize,tD.RegisterMessage,
tE.WebsiteName,tE.OriginalWebsite,tE.QQ,tE.MSN,tE.Skype

这些都是一个大型“用户信息表”的所有部分，分为(可选？)部分。

我推测这种结构源自某种将表单提交部分映射到表的遗留/框架系统。因此，有人可能在表 B、C 和 E 中有一个条目，而其他人在表 A、C 和 D 中有一个条目。

如果这是真的，并且如果所有表的 user_id 都相同，那么加快速度的一种方法是在 上显式添加条件每个表的user_id，并适当修改索引和JOIN:

CREATE INDEX UserAddedRecord1_ndx ON UserAddedRecord1 (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_A_ndx ON UserAddedRecord1_A (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_B_ndx ON UserAddedRecord1_B (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_C_ndx ON UserAddedRecord1_C (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_D_ndx ON UserAddedRecord1_D (user_id, Custgroup, RandomNumber);
CREATE INDEX UserAddedRecord1_E_ndx ON UserAddedRecord1_E (user_id, Custgroup, RandomNumber);

... FROM UserAddedRecord1 t1
JOIN UserAddedRecord1_A tA USING (user_id, CustGroup, RandomNumber)
JOIN UserAddedRecord1_B tB USING (user_id, CustGroup, RandomNumber)
JOIN UserAddedRecord1_C tC USING (user_id, CustGroup, RandomNumber)
JOIN UserAddedRecord1_D tD USING (user_id, CustGroup, RandomNumber)
JOIN UserAddedRecord1_E tE USING (user_id, CustGroup, RandomNumber)
     WHERE t1.user_id = '1'

尝试fiddle

要做的事情是将所有表合并到一个表中，所有字段都在一行中，然后，也许出于遗留目的，您可以创建VIEW 看起来像表 1、A、B、C、D 和 E，每个表都有元组的“垂直”分区。但是大型 SELECT 您将在包含所有字段的完整表上运行(并且您也会在重复的列上保存)。