php - 使用索引提高性能

Question

我正在开发 DWH，而且我对 php 和 mySQL 还很陌生。我有一个事实表，上面有 6 个索引，并且我有 6 维表。维度表有两个字段:ID 和 Value。事实表有一个ID作为主键，并且有所有维度表的ID作为外键。它还有一些其他领域。我需要将事实表与维度表连接起来以获得所有维度表的值。事实表中有很多记录，当我查询它时，执行时间很长。我对这些表使用简单的联接查询。有人可以帮助我并说明我应该如何使用索引查询表以提高性能吗？查询如下:

select *  from dw.bohran_fct_etelaatenavegankhodroyi
INNER JOIN (select NameDastgahID as e1NameDastgahID, NameDastgahTitle from dw.excel_dim_namedastgah) e1 on (bohran_fct_etelaatenavegankhodroyi.NameDastgahID=e1NameDastgahID) 
INNER JOIN (select NameKhodroID as e2NameKhodroID, NameKhodroTitle from dw.excel_dim_namekhodro) e2 on (bohran_fct_etelaatenavegankhodroyi.NameKhodroID=e2NameKhodroID) 
INNER JOIN (select NoeNavganDastgahID as e3NoeNavganDastgahID, NoeNavganDastgahTitle from dw.excel_dim_noenavgandastgah) e3 on (bohran_fct_etelaatenavegankhodroyi.NoeNavganID=e3NoeNavganDastgahID) 
INNER JOIN (select KarbarieKhodroID as e4KarbarieKhodroID, KarbarieKhodroTitle from dw.excel_dim_karbariekhodro) e4 on (bohran_fct_etelaatenavegankhodroyi.KarbariID=e4KarbarieKhodroID) 
INNER JOIN (select ShahreKhodroID as e5ShahreKhodroID, ShahreKhodroTitle from dw.excel_dim_shahrekhodro) e5 on (bohran_fct_etelaatenavegankhodroyi.ShahrID=e5ShahreKhodroID)  where 1=1 

explain output of the statement

Answer 1

我终于找到了在很短的时间内完成这个查询的方法!查询是:

select distinct (B.NameDastgahID), B.ZarfiateHmaleBar, B.ShomarePelak, B1.NameDastgahTitle, B2.NameKhodroTitle, B3.NoeKhodroTitle, B4.KarbarieKhodroTitle, B5.ShahreKhodroTitle  
from dw.bohran_fct_etelaatenavegankhodroyi B
INNER JOIN dw.excel_dim_namedastgah B1 using (NameDastgahID) 
INNER JOIN dw.excel_dim_namekhodro B2 using (NameKhodroID) 
INNER JOIN dw.excel_dim_noenavgandastgah) B3 using (NoeKhodroID) 
INNER JOIN dw.excel_dim_karbariekhodro) B4 using (KarbarieKhodroID) 
INNER JOIN dw.excel_dim_shahrekhodro) B5 using (ShahreKhodroID)  
where 1=1

需要注意的是，维度表B1、B2、B3、B4、B5(使用后括号内)中的字段名称应与事实表B中的字段名称相同。