无法正确加入表.查询花费的无限时间

Question

我在我的SQLCODE面临性能问题。使用Tableau Platform

我有大约11个桌子。当我调试10个连接表的性能时，它在Tableau中非常快，但是当我再添加一张billing_transaction的表格时，即使有一小组行，查询也永远不会结束。

这是整个数据库的ERD。它创建长时间查询可能是什么问题？

这是SQL代码(我已经评论了"来自表Billing_transaction"的节目，是为了比较性能(

select 
    country.`name` as Country,
    oper.`name` as Operator,
    proj.`name` as Project,
    club.`name` as Club,
    prom.`name` as Promotor,
    cust.`idCustomer` as SubscriberID,
    cust_sub.`msisdn` as SubscriberMsidn,
    cust.`customerSince` as SubscriberStartingDate,
    cust_sub.`SubscribedDate` as SubscriberSubcribeDate,
    cust_sub.`UnsubscribedDate` as SubscriberUnSubcribeDate,
    cust_sub.`idCustomerSubscription` as SubscriptionID,
    bt.`idBillingTransaction` as BillingTransactionID,
    cust_sub.`eventId` as SubscriberEventID,
    bt_status.`name` as BillingStatus,
    bt.`price` as Pricefromsubscriber

-- FROM `customersubscription` cust_sub (WITHOUT billing_transaction it WORKS FINE)
--  LEFT JOIN `customer` cust on cust_sub.`idCustomer`=cust.`IdCustomer`
--  LEFT JOIN `promoter` prom on cust_sub.`idPromoter`=prom.`id`
--  LEFT JOIN `club` club on cust_sub.`idClub`=club.`idClub`
--  LEFT JOIN `Project` proj on club.`idProject`=Proj.`idProject`
--  LEFT JOIN `project_operator_relationships` proj_rel_oper on proj.`idProject`=proj_rel_oper.`projectId`
--  LEFT JOIN `Operator` as oper on proj_rel_oper.`operatorId`=oper.`idOperator`
--  LEFT JOIN `country` as country on oper.`idCountry`=country.`idCountry`
--  LEFT JOIN `curreny_symbol` as curr_sym on country.`idCurrencySymbol`=curr_sym.`symbol`,

FROM `billing_transaction` bt
    LEFT JOIN `customersubscription` cust_sub on bt.`msisdn`=cust_sub.`msisdn`
    LEFT JOIN `customer` cust on cust.`idCustomer`=cust_sub.`IdCustomer`
    LEFT JOIN `billing_status` bt_status on bt.`idBillingStatus`=bt_status.`idBillingStatus`
    LEFT JOIN `promoter` prom on cust_sub.`idPromoter`=prom.`id`
    LEFT JOIN `club` club on cust_sub.`idClub`=club.`idClub`
    LEFT JOIN `Project` proj on club.`idProject`=Proj.`idProject`
    LEFT JOIN `project_operator_relationships` proj_rel_oper on proj.`idProject`=proj_rel_oper.`projectId`
    LEFT JOIN `Operator` as oper on proj_rel_oper.`operatorId`=oper.`idOperator`
    LEFT JOIN `country` as country on oper.`idCountry`=country.`idCountry`
    LEFT JOIN `curreny_symbol` as curr_sym on country.`idCurrencySymbol`=curr_sym.`symbol`
where proj.`idProject` IN (3, 19)
-- where proj.`idProject` IN (3, 19, 23, 24, 27)
    and date(cust_sub.`SubscribedDate`)>='2017-04-18 01:40:00'
    and date(cust_sub.`SubscribedDate`)< '2017-04-18 02:00:00'

任何建议都将不胜感激。所有调试，但我不知道性能问题在哪里。我也不能忽略桌子

我的方法(这可能是我的新手(，是，占用billing_transaction表，请继续加入并在下一行的列中加入并添加信息，例如客户订阅，客户，运营商，俱乐部，项目，项目....一直到乡村。左连接适合这种情况？另外，从billing_transaction(主表(或子句中的任何表中的任何表中的任何表中的任何表中的任何表格都重要。

序列像这样

Each customer has one or more subscription
Each Subscription has one or more billing transaction
Each billing transaction has one or more billing status

Other relations are:
Each customer subscription have one or more club
Each customer subscription has one or more promotor
Each club has one or more project
Each project has one or more operator
Each operator has one or more countries
Each country has one or more currency

Answer 1

更改

中的左联接

SELECT ...
FROM `billing_transaction` bt
    LEFT JOIN `customersubscription` cust_sub on bt.`msisdn`=cust_sub.`msisdn`
    ...

内在的联接：

SELECT ...
FROM `billing_transaction` bt
    JOIN `customersubscription` cust_sub on bt.`msisdn`=cust_sub.`msisdn`
    ...

您将加入您应用的潜在巨大表(billing_trnsaction(。但是，这里的left join的意义是什么？没有客户可以进行计费交易吗？

同样，查看所有左JONINS ，并在可能的情况下用内连接替换它们。然后适当地应用过滤器(在存在索引的情况下(。