1、什么是派生表derived
关键字：子查询-->在From后where前的子查询
例子：
mysql> explain select * from (select * from t) a where id=2;+----+-------------+------------+------+---------------+-------------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra |+----+-------------+------------+------+---------------+-------------+---------+-------+------+-------+| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | const | 0 | NULL || 2 | DERIVED | t| ALL | NULL| NULL | NULL | NULL | 4 | NULL |+----+-------------+------------+------+---------------+-------------+---------+-------+------+-------+
主要经历以下三步：
1、执行子查询，select * from t
2、把子查询的结果写到临时表 A表
3、回读，应用上层SELECT的WHERE条件 id=2。
执行这样的子查询是非常低效的，因为扫描基表 t 时没有使用父选择（id=2）的高选择性条件。 我们从t表中读取太多记录，然后我们必须将它们写入一个临时表并再次读取，然后才能过滤掉它们。
Oracle DBA一定觉得它很傻，不错贼J儿啥，这尼玛都不能子查询展开。
MySQL 5.7开始优化器引入derived_merge，可以理解为Oracle的子查询展开，有优化器参数optimizer_switch='derived_merge=ON’来控制，默认为打开。
但是仍然有很多限制，当派生子查询存在以下操作时该特性无法生效：UNION 、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作
2、派生表SQL优化一例
mysql> select @@version;+------------+| @@version |+------------+| 5.6.16-log |+------------+SELECT A.*, b.vendor_name, c.chinese_name, c.english_name, d.name AS category_name, e.value, a.season_codeFROM PR_XXX ALEFT JOIN VXX BON A.VENDOR_ID = B.VENDOR_IDLEFT JOIN BXX CON A.BRAND_ID = C.BRAND_IDLEFT JOIN CXX DON A.CATEGORY_ID = D.CATEGORY_IDLEFT JOIN PXX EON A.PRODUCT_ID = E.PRODUCT_IDINNER JOIN (SELECT SS.PRODUCT_IDFROM SKU_XXX SSWHERE SS.ENABLED = 1GROUP BY SS.PRODUCT_IDHAVING SUM(SS.STORE) >= 1) STON ST.PRODUCT_ID = A.PRODUCT_IDWHERE A.vendor_id = 91011AND A.enabled = 1AND A.status = '1'AND a.season_code = '5678'AND 1 = 1AND a.brand_id = '1234'AND E.KEY_NAME = 'XXX'ORDER BY product_id LIMIT 0, 10执行计划如下：+----+-------------+------------+-------------+----------------------------------------------------------------+-----------------------------------+---------+--------------------------+---------+---------------------------------------------------------------------------------+| id | select_type | table | type | possible_keys| key| key_len | ref | rows | Extra |+----+-------------+------------+-------------+----------------------------------------------------------------+-----------------------------------+---------+--------------------------+---------+---------------------------------------------------------------------------------+| 1 | PRIMARY | A| index_merge | PRIMARY,vendor_id,IDX_VENDOR_ID_STATUS,IDX_BRAND_ID,IDX_STATUS | IDX_VENDOR_ID_STATUS,IDX_BRAND_ID | 9,8 | NULL | 5 | Using intersect(IDX_VENDOR_ID_STATUS,IDX_BRAND_ID); Using where; Using filesort || 1 | PRIMARY | B| const | PRIMARY | PRIMARY | 8 | const| 1 | NULL || 1 | PRIMARY | C| const | PRIMARY | PRIMARY | 8 | const| 1 | Using where|| 1 | PRIMARY | D| eq_ref | PRIMARY | PRIMARY | 8 | ger-prd-db.A.category_id | 1 | NULL || 1 | PRIMARY | E| ref| IDX_PRODUCT_PROPERTY_PRODUCT_ID| IDX_PRODUCT_PROPERTY_PRODUCT_ID | 8 | ger-prd-db.A.product_id | 3 | Using where|| 1 | PRIMARY | <derived2> | ref| <auto_key0>| <auto_key0> | 9 | ger-prd-db.A.product_id | 10 | Using index|| 2 | DERIVED | SS| index | IDX_PRODUCT_ID| IDX_PRODUCT_ID| 9 | NULL | 1715829 | Using where|+----+-------------+------------+-------------+----------------------------------------------------------------+-----------------------------------+---------+--------------------------+---------+---------------------------------------------------------------------------------+
关键表索引信息
mysql> show index from PR_XXX ;+---------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| PR_XXX |0 | PRIMARY |1 | product_id | A| 283783 | NULL | NULL | | BTREE || || PR_XXX |0 | vendor_id |1 | vendor_id | A|92 | NULL | NULL | | BTREE || || PR_XXX |0 | vendor_id |2 | product_code | A| 283783 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_VENDOR_ID_STATUS |1 | vendor_id | A|82 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_VENDOR_ID_STATUS |2 | status | A|392 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_BRAND_ID|1 | brand_id | A| 2866 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_STATUS |1 | status | A|10 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_PRODUCT_CATEGORY_ID |1 | category_id | A|276 | NULL | NULL | | BTREE || || PR_XXX |1 | IDX_SPU_ID |1 | spu_id | A| 283783 | NULL | NULL | YES | BTREE || |+---------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+9 rows in set (0.00 sec)mysql> show index from SKU_XXX;+-----------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| SKU_XXX |0 | PRIMARY |1 | sku_store_id | A| 1715829 | NULL | NULL | | BTREE || || SKU_XXX |1 | IDX_SKU_ID |1 | sku_id | A| 1715829 | NULL | NULL | YES | BTREE || || SKU_XXX |1 | IDX_PRODUCT_ID |1 | product_id | A| 857914 | NULL | NULL | YES | BTREE || |+-----------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
建议进行SQL改写：
SELECT A.*, b.vendor_name, c.chinese_name, c.english_name, d.name as category_name, e.value, a.season_codeFROM PR_XXX ALEFT JOIN VXX BON A.VENDOR_ID = B.VENDOR_IDLEFT JOIN BXX CON A.BRAND_ID = C.BRAND_IDLEFT JOIN CXX DON A.CATEGORY_ID = D.CATEGORY_IDLEFT JOIN PXX EON A.PRODUCT_ID = E.PRODUCT_IDINNER JOIN SKU_XXX SSON SS.ENABLED = 1and SS.PRODUCT_ID = A.PRODUCT_IDwhere A.vendor_id = 20AND A.enabled = 1AND A.status = '1'AND a.season_code = '18SS'AND 1 = 1AND a.brand_id = '247'AND E.KEY_NAME = 'BrandID'and SS.ENABLED = 1GROUP BY a.PRODUCT_IDhaving SUM(SS.STORE) >= 1ORDER BY a.product_id limit 0, 10;改写后的执行计划+----+-------------+-------+-------------+---------------------------------------------------------------------------------------------------+-----------------------------------+---------+--------------------------+------+--------------------------------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra|+----+-------------+-------+-------------+---------------------------------------------------------------------------------------------------+-----------------------------------+---------+--------------------------+------+--------------------------------------------------------------------------------------------------+| 1 | SIMPLE | A | index_merge | PRIMARY,vendor_id,IDX_VENDOR_ID_STATUS,IDX_BRAND_ID,IDX_STATUS,IDX_PRODUCT_CATEGORY_ID,IDX_SPU_ID | IDX_VENDOR_ID_STATUS,IDX_BRAND_ID | 9,8 | NULL | 5 | Using intersect(IDX_VENDOR_ID_STATUS,IDX_BRAND_ID); Using where; Using temporary; Using filesort || 1 | SIMPLE | B | const | PRIMARY| PRIMARY | 8 | const| 1 | NULL || 1 | SIMPLE | C | const | PRIMARY| PRIMARY | 8 | const| 1 | Using where || 1 | SIMPLE | D | eq_ref | PRIMARY| PRIMARY | 8 | ger-prd-db.A.category_id | 1 | NULL || 1 | SIMPLE | SS | ref| IDX_PRODUCT_ID | IDX_PRODUCT_ID| 9 | ger-prd-db.A.product_id | 2 | Using where || 1 | SIMPLE | E | ref| IDX_PRODUCT_PROPERTY_PRODUCT_ID | IDX_PRODUCT_PROPERTY_PRODUCT_ID | 8 | ger-prd-db.A.product_id | 3 | Using where |+----+-------------+-------+-------------+---------------------------------------------------------------------------------------------------+-----------------------------------+---------+--------------------------+------+--------------------------------------------------------------------------------------------------+
优化前：2 rows in set (2.12 sec)
优化后：2 rows in set (0.00 sec)
