【求助】MYSQL表关联内联视图不走索引问题？？诡异啊
首先声明：FACT_DATE_SALES_CI_INDEX 的DAY,DIM_ORG建有复合索引，同时FACT_DATE_SALES_FACT_DATE_SALES_F1表的RETAIL_SALES_DATE，DIM_ORG列上也建有复合索引
1、以内联视图的写法关联FACT_DATE_SALES_FACT_DATE_SALES_F1表，如下
mysql> explain
-> SELECT *
-> FROM FACT_DATE_SALES_CI_INDEX A
-> LEFT JOIN (SELECT MODEL_CODE, RETAIL_SALES_DATE, DIM_ORG, FLAG
-> FROM FACT_DATE_SALES_FACT_DATE_SALES_F1) B
-> ON A.DAY = B.RETAIL_SALES_DATE
-> AND A.DIM_ORG = B.DIM_ORG
-> LIMIT 20;
+----+-------------+------------------------------------+------+---------------+-------------+---------+-------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+------------------------------------+------+---------------+-------------+---------+-------------------------------+-------+-------------+
|1 | PRIMARY | A | ALL| NULL | NULL | NULL | NULL | 65 | NULL |
|1 | PRIMARY | <derived2> | ref| <auto_key0> | <auto_key0> | 215 | dftzys.A.DAY,dftzys.A.DIM_ORG | 961 | Using where |
|2 | DERIVED | FACT_DATE_SALES_FACT_DATE_SALES_F1 | ALL| NULL | NULL | NULL | NULL | 96131 | NULL |
+----+-------------+------------------------------------+------+---------------+-------------+---------+-------------------------------+-------+-------------+
3 rows in set (0.00 sec)
该查询实际耗时1.2秒左右
执行计划并不走索引，而是全表扫描
2、直接关联FACT_DATE_SALES_FACT_DATE_SALES_F1表，如下：
mysql> explain
-> SELECT *
-> FROM FACT_DATE_SALES_CI_INDEX A
-> LEFT JOIN FACT_DATE_SALES_FACT_DATE_SALES_F1 B
-> ON A.DAY = B.RETAIL_SALES_DATE
-> AND A.DIM_ORG = B.DIM_ORG
-> LIMIT 20;
+----+-------------+-------+------+---------------+----------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------------------+------+-------------+
|1 | SIMPLE | A | ALL| NULL | NULL | NULL | NULL | 65 | NULL |
|1 | SIMPLE | B | ref| inx_f101 | inx_f101 | 215 | dftzys.A.DAY,dftzys.A.DIM_ORG |393 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------------------------+------+-------------+
2 rows in set (0.00 sec)
该查询实际耗时仅仅200ms
执行计划走了FACT_DATE_SALES_FACT_DATE_SALES_F1的索引
实在不明白为什么会这样？MYSQL对内联视图不做查询转换？仅仅是套了一层子查询，什么都没做，执行计划就不走索引了，那么视图在MYSQL中的性能是不是也太废了？
而实际项目环境中，不可能不用到内联视图或视图的情况，该如何优化？或者是我理解有问题，请大神指导！！
目前生产环境就是因为类似的问题导致多处sql效率极其低下。。。
LEFT JOIN (SELECT MODEL_CODE, RETAIL_SALES_DATE, DIM_ORG, FLAG FROM FACT_DATE_SALES_FACT_DATE_SALES_F1) B
这写法本来就是会生成临时表，而系统不会帮你加索引，因此是全表扫描
页:
[1]