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_code
FROM PR_XXX A
LEFT JOIN VXX B
ON A.VENDOR_ID = B.VENDOR_ID
LEFT JOIN BXX C
ON A.BRAND_ID = C.BRAND_ID
LEFT JOIN CXX D
ON A.CATEGORY_ID = D.CATEGORY_ID
LEFT JOIN PXX E
ON A.PRODUCT_ID = E.PRODUCT_ID
INNER JOIN (SELECT SS.PRODUCT_ID
FROM SKU_XXX SS
WHERE SS.ENABLED = 1
GROUP BY SS.PRODUCT_ID
HAVING SUM(SS.STORE) >= 1) ST
ON ST.PRODUCT_ID = A.PRODUCT_ID
WHERE A.vendor_id = 91011
AND A.enabled = 1
AND A.status = '1'
AND a.season_code = '5678'
AND 1 = 1
AND 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_code
FROM PR_XXX A
LEFT JOIN VXX B
ON A.VENDOR_ID = B.VENDOR_ID
LEFT JOIN BXX C
ON A.BRAND_ID = C.BRAND_ID
LEFT JOIN CXX D
ON A.CATEGORY_ID = D.CATEGORY_ID
LEFT JOIN PXX E
ON A.PRODUCT_ID = E.PRODUCT_ID
INNER JOIN SKU_XXX SS
ON SS.ENABLED = 1
and SS.PRODUCT_ID = A.PRODUCT_ID
where A.vendor_id = 20
AND A.enabled = 1
AND A.status = '1'
AND a.season_code = '18SS'
AND 1 = 1
AND a.brand_id = '247'
AND E.KEY_NAME = 'BrandID'
and SS.ENABLED = 1
GROUP BY a.PRODUCT_ID
having SUM(SS.STORE) >= 1
ORDER 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)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/bian-cheng-ji-chu/105705.html