- 积分
- 7
- UID
- 35552
- 阅读权限
- 10
- 注册时间
- 2017-6-29
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
首先声明:
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效率极其低下。。。
|
|