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

⋅jdbc事务中提交几千条update，未等commit即报Connection timed out，是否会造成锁表 ⋅求教：随机数生成机制的设计思路 ⋅请教sql优化（内有建表语句与题目） 更多帖子 关注私信空间博客 dinghun8leech 本版专家分：35 结帖率 100%

目的：性能优化；
建表语句与初始数据：

drop database if exists test1;

create database test1;

use test1;



/** 产品分类表 */

create table productType (

       typeId varchar(20) not null primary key, /*分类id*/

       name varchar(50),  /*分类名*/

       fatherTypeId varchar(20)  /*上级分类*/

);



/** 产品表 */

create table product (

       sku varchar(20) not null primary key,  /*库存量单位，可看作商品id*/

       name varchar(50),  /*商品名*/

       maxPrice double,  /*最高价*/

       minPrice double,  /*最低价*/

       normalCount int,  /*良品库存*/

       normalSellCount int,  /*可销售的良品数*/

       badCount int  /*损坏件数*/

);



/** 产品分类关系表 */

create table register (

       typeId varchar(20),

       sku varchar(20),

       primary key (typeId, sku)

);



insert into productType values('hf1', 'erji', null);

insert into productType values('hf2', 'taishishebei', null);

insert into productType values('hf3', 'zhoubian', null);

insert into productType values('hf4', 'cd', null);



insert into productType values('hf1-1', 'ruer', 'hf1');

insert into productType values('hf1-2', 'ergua', 'hf1');

insert into productType values('hf1-3', 'bianxie', 'hf1');

insert into productType values('hf1-4', 'toudai', 'hf1');

insert into productType values('hf1-5', 'dongquan', 'hf1');

insert into productType values('hf1-6', 'dongtie', 'hf1');

insert into productType values('hf1-7', 'jingdian', 'hf1');

insert into productType values('hf2-1', 'zhuanpan', 'hf2');

insert into productType values('hf2-2', 'jiema', 'hf2');

insert into productType values('hf2-3', 'erfang', 'hf2');

insert into productType values('hf2-4', 'yitiji', 'hf2');

insert into productType values('hf3-1', 'xiancai', 'hf3');

insert into productType values('hf3-2', 'jiemian', 'hf3');

insert into productType values('hf4-1', 'jpop', 'hf4');

insert into productType values('hf4-2', 'jazz', 'hf4');

insert into productType values('hf4-3', 'bossanova', 'hf4');

insert into productType values('hf4-4', 'ost', 'hf4');



insert into product values('0001', 'hd600', 1900, 1600, 800, 700, 50);

insert into register values('hf1-4', '0001');

insert into register values('hf1-5', '0001');

insert into product values('0002', 'hd800', 12000, 7000, 10, 7, 0);

insert into register values('hf1-4', '0002');

insert into register values('hf1-5', '0002');

insert into product values('0003', 'sr009', 31000, 27000, 21, 17, 1);

insert into register values('hf1-4', '0003');

insert into register values('hf1-7', '0003');

insert into product values('0004', 'e4c', 2500, 1700, 300, 250, 13);

insert into register values('hf1-1', '0004');

insert into register values('hf1-6', '0004');

insert into product values('0005', 'ew9', 1380, 950, 5, 1, 0);

insert into register values('hf1-2', '0005');

insert into register values('hf1-5', '0005');

insert into product values('0006', 'esw11ltd', 6700, 4360, 7, 1, 0);

insert into register values('hf1-3', '0006');

insert into register values('hf1-5', '0006');

insert into product values('0007', 't1', 8900, 6800, 20, 15, 1);

insert into register values('hf1-4', '0007');

insert into register values('hf1-5', '0007');

insert into product values('0008', 'r10', 75000, 11000, 2, 2, 0);

insert into register values('hf1-4', '0008');

insert into register values('hf1-5', '0008');

insert into product values('0009', 'qualia 010', 64000, 11000, 3, 2, 1);

insert into register values('hf1-4', '0009');

insert into register values('hf1-5', '0009');

insert into product values('0010', 'esw9', 1980, 1900, 22, 20, 1);

insert into register values('hf1-3', '0010');

insert into register values('hf1-5', '0010');

insert into product values('0011', 'sr007', 17000, 16000, 10, 9, 1);

insert into register values('hf1-4', '0011');

insert into register values('hf1-7', '0011');

insert into product values('0012', 'ad900', 1900, 1550, 15, 12, 1);

insert into register values('hf1-4', '0012');

insert into register values('hf1-5', '0012');

insert into product values('0021', 'mdac2a', 1900, 1850, 37, 30, 1);

insert into register values('hf2-2', '0021');

insert into register values('hf2-3', '0021');

insert into register values('hf2-4', '0021');

insert into product values('0022', 'lyra2', 18500, 17200, 20, 8, 0);

insert into register values('hf2-2', '0022');

insert into register values('hf2-3', '0022');

insert into register values('hf2-4', '0022');

insert into product values('0023', 'hilo', 17500, 17000, 25, 4, 0);

insert into register values('hf2-2', '0023');

insert into register values('hf2-3', '0023');

insert into register values('hf2-4', '0023');

insert into product values('0024', 'ha5000', 12000, 9800, 20, 9, 0);

insert into register values('hf2-3', '0024');

insert into product values('0025', 'dac32', 18500, 12100, 50, 47, 1);

insert into register values('hf2-2', '0025');

insert into product values('0026', 'mdac3', 1900, 1600, 100, 99, 1);

insert into register values('hf2-2', '0026');

insert into register values('hf2-3', '0026');

insert into register values('hf2-4', '0026');

insert into product values('0027', 'supermo', 11200, 9800, 75, 66, 2);

insert into register values('hf2-1', '0027');

insert into product values('0028', 'd100', 2980, 2750, 80, 80, 0);

insert into register values('hf2-1', '0028');

insert into product values('0031', 'qed audio s', 2400, 2200, 150, 130, 0);

insert into register values('hf3-1', '0031');

insert into product values('0032', 'amazon basic', 25, 21, 2750, 2749, 1);

insert into register values('hf3-1', '0032');

insert into product values('0033', 'ova', 1140, 1100, 500, 8, 0);

insert into register values('hf3-2', '0033');

insert into product values('0034', 'sp2', 4850, 4700, 2, 2, 0);

insert into register values('hf3-2', '0034');

insert into product values('0041', 'aaaa', 50, 50, 12, 9, 0);

insert into register values('hf4-1', '0041');

insert into register values('hf4-4', '0041');

insert into product values('0042', 'bbbb', 300, 295, 5, 5, 0);

insert into register values('hf4-2', '0042');

insert into product values('0043', 'cccc', 200, 195, 75, 65, 0);

insert into register values('hf4-2', '0043');

题目：
1、查询各二级类目下最高价低于3000，好件可卖数>10的所有商品sku数，并按sku数排序。
2、查询当前一级类目名为erji，taishishebei下的可卖数低于10的商品明细。
3、查出划分到不止一个二级类目下的所有商品，统一只保留按字母顺序的第一个类目关系。

我的答案：
第一题：



select t.typeId, t.name, t.skuCount

from (

select typ.typeId as typeId, typ.name as name, count(prod.sku) as skuCount

from product prod left join register reg on reg.sku = prod.sku

     left join productType typ on reg.typeId = typ.typeId

where not isNull(typ.fatherTypeId) and prod.maxPrice < 3000 and prod.normalSellCount > 10

group by typ.typeId

) t

order by t.skuCount;

第二题（in or exists）：



select prod.* 

from product prod 

where prod.normalSellCount < 10

and prod.sku in (

    select distinct reg.sku

    from productType typ1 left join productType typ2 on typ1.typeId = typ2.fatherTypeId 

         left join register reg on reg.typeId = typ2.typeId

    where typ1.name = 'erji' or typ1.name = 'taishishebei'

);



select prod.* 

from product prod 

where prod.normalSellCount < 10

and exists (

    select null

    from register reg left join productType typ2 on reg.typeId = typ2.typeId

         left join productType typ1 on typ2.fatherTypeId = typ1.typeId

    where (typ1.name = 'erji' or typ1.name = 'taishishebei')

           and reg.sku = prod.sku

);

第三题（in or exists）：



select distinct prod.*, typ2.name as typeName

from product prod left join register reg on reg.sku = prod.sku

     left join productType typ1 on reg.typeId = typ1.typeId

     left join productType typ2 on typ1.fatherTypeId = typ2.typeId 

where prod.sku in (

    select reg.sku

    from product prod left join register reg on reg.sku = prod.sku 

         left join productType typ on reg.typeId = typ.typeId

    where not isNUll(typ.fatherTypeId) group by reg.sku having count(typ.typeId) > 1

) order by typ2.name;



select distinct prod.*, typ2.name as typeName

from product prod left join register reg on reg.sku = prod.sku

     left join productType typ1 on reg.typeId = typ1.typeId

     left join productType typ2 on typ1.fatherTypeId = typ2.typeId 

where exists (

    select null 

    from register reg left join productType typ2 on reg.typeId = typ2.typeId

         left join productType typ1 on typ2.fatherTypeId = typ1.typeId

    where reg.sku = prod.sku

          group by(reg.sku) having count(reg.typeId) > 1

) order by typ2.name;

请教各位前辈，此三题还有什么解法，效率更高？？谢谢。

0 2014-04-27 16:05:13

回复数 4 只看楼主引用举报楼主

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：471951 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 贴出所有相关表的 show index from ..
然后再贴出 explain select ...

0 2014-04-27 20:35:35

只看TA 引用举报 #1 得分 0

⋅jdbc事务中提交几千条update，未等commit即报Connection timed out，是否会造成锁表 ⋅求教：随机数生成机制的设计思路 ⋅请教sql优化（内有建表语句与题目） 更多帖子 关注私信空间博客 dinghun8leech 本版专家分：35: 引用 1 楼 ACMAIN_CHM 的回复:
贴出所有相关表的 show index from ..
然后再贴出 explain select ...

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

0 2014-04-28 20:07:24

只看TA 引用举报 #2 得分 0

⋅jdbc事务中提交几千条update，未等commit即报Connection timed out，是否会造成锁表 ⋅求教：随机数生成机制的设计思路 ⋅请教sql优化（内有建表语句与题目） 更多帖子 关注私信空间博客 dinghun8leech 本版专家分：35

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                                  |

+----+--------------------+-------+-------+---------------+---------+---------+-

------------------------+------+----------------------------------------------+

0 2014-04-29 18:05:48

只看TA 引用举报 #3 得分 0

dinghun8leech 本版专家分：35: 顶一下~
要6个字？

0 2014-05-12 12:41:48

只看TA 引用举报 #4 得分 0

请教sql优化（内有建表语句与题目） [问题点数：100分，结帖人dinghun8leech]