请教sql优化（内有建表语句与题目）

Question

贴出所有相关表的  show index from ..
然后再贴出 explain select ...

Answer 1

版主抱歉，今天略忙，我明天再按此要求，贴出相关表的索引信息，与上述三条sql的执行计划信息。

Answer 2

product表：
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| product |          0 | PRIMARY  |            1 | sku         | A         |
      27 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
productType表：
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
 Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| productType |          0 | PRIMARY  |            1 | typeId      | A         |
          21 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
register表：
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| register |          0 | PRIMARY  |            1 | typeId      | A         |
       48 |     NULL | NULL   |      | BTREE      |         |
| register |          0 | PRIMARY  |            2 | sku         | A         |
       48 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+第一题执行计划：
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | re
f              | rows | Extra                    |
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NU
LL             |   11 | Using filesort           |
|  2 | DERIVED     | typ        | index | PRIMARY       | PRIMARY | 22      | NU
LL             |   21 | Using where              |
|  2 | DERIVED     | reg        | ref   | PRIMARY       | PRIMARY | 22      | te
st1.typ.typeId |    2 | Using where; Using index |
|  2 | DERIVED     | prod       | ref   | PRIMARY       | PRIMARY | 22      | te
st1.reg.sku    |    1 | Using where              |
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
第二题执行计划：
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
| id | select_type        | table | type | possible_keys | key     | key_len | r
ef                     | rows | Extra                    |
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
|  1 | PRIMARY            | prod  | ALL  | NULL          | NULL    | NULL    | N
ULL                    |   27 | Using where              |
|  2 | DEPENDENT SUBQUERY | typ2  | ALL  | PRIMARY       | NULL    | NULL    | N
ULL                    |   21 | Using temporary          |
|  2 | DEPENDENT SUBQUERY | typ1  | ref  | PRIMARY       | PRIMARY | 22      | t
est1.typ2.fatherTypeId |    1 | Using where              |
|  2 | DEPENDENT SUBQUERY | reg   | ref  | PRIMARY       | PRIMARY | 44      | t
est1.typ2.typeId,func  |    1 | Using where; Using index |
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
第三题执行计划：
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len |
ref                     | rows | Extra                                        |
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | prod  | ALL   | NULL          | NULL    | NULL    |
NULL                    |   27 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | reg   | index | NULL          | PRIMARY | 44      |
NULL                    |   48 | Using index                                  |
|  1 | PRIMARY            | typ1  | ref   | PRIMARY       | PRIMARY | 22      |
test1.reg.typeId        |    1 |                                              |
|  1 | PRIMARY            | typ2  | ref   | PRIMARY       | PRIMARY | 22      |
test1.typ1.fatherTypeId |    1 |                                              |
|  2 | DEPENDENT SUBQUERY | typ   | ALL   | PRIMARY       | NULL    | NULL    |
NULL                    |   21 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | reg   | ref   | PRIMARY       | PRIMARY | 22      |
test1.typ.typeId        |    2 | Using where; Using index                     |
|  2 | DEPENDENT SUBQUERY | prod  | ref   | PRIMARY       | PRIMARY | 22      |
test1.reg.sku           |    1 | Using index                                  |
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+