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效率极其低下。。。 作者: fcy_n 时间: 2017-7-17 17:59
LEFT JOIN (SELECT MODEL_CODE, RETAIL_SALES_DATE, DIM_ORG, FLAG FROM FACT_DATE_SALES_FACT_DATE_SALES_F1) B