关系查询处理和查询优化 EXPLAIN命令

关系查询处理和查询优化
学会使用EXPLAIN命令分析查询执行计划，利用索引优化查询性能，优化SQL语句。
环境：Navicat premiun，以及使用实验一的表格。
结构：
（1）使用EXPLAIN命令查看查询执行计划。查看Part，PartSupp，Supplier三个表连接查询的查询执行计划。
（2）利用索引优化查询性能。建立索引，优化SQL查询性能。比较Part表的name上有索引和无索引时，两种执行计划有何异同，并实际执行该查询，验证有索引和无索引时此查询语句的执行性能。
（3）优化SQL语句。1>IN与EXISTS查询。比较两种执行计划，并实际测试执行性能哪种情况好。2>尽可能使用不相关子查询，避免使用相关子查询。不相关子查询一般比相关子查询执行效率要高，在可能的情况下，改写相关子查询为不相关子查询。比较两种执行计划，并实际测试执行性能哪种情况好。
（4）数据库模式规范化设计对查询性能的影响。分析TPCH数据库模式中是否存在不规范化的设计。该设计在海量数据的情况下查询效率怎样？如何在设计上进一步提高海量数据的查询效率？第三范式在一定程度上减少了不必要的冗余，提高了数据库的查询效率，但是如果数据量大且需要大量联合查询的时候，第三范式设计又可能会影响查询效率。TPCH中存在的不规范的设计如下：
1> Orders表中订单的totalprice由Lineitem表中该订单项的extendedprice，discount,tax等属性计算得出，即totalprice=SUM(Lineitem.extendedprice*(1-Lineitem.discount)(1+Lineitem.tax))。该项设计虽然不规范，但大大提高了客户所有订单金额的查询效率。例如，查询所有购物金额大于20万的客户编号及其购物总金额。
2> Lineitem表中订单明细价格extendedprice由quantity和Part表中的retailprice得出，即extendedprice=quantityPart.retailprice该项设计虽然不规范，但大大提高了客户订单总金额的查询效率。例如，查询所有购物零售总金额（折扣和加税之前的价格，即extendedprice）大于1万的订单对应的客户编号及金额。
具体的程序功能（核心代码）

（1）/*该sql语句是要查询零件名为发动机的零件，供应 该零件的供应商以及供应等详细信息，并按照供应商的账户余额（降序），供应商名称（升序），排序输出结果*/
explain select * 
from Part P,PartSupp PS,Supplier S
where P.partkey=PS.partkey 
and PS.suppkey=S.suppkey                             and P.name='发动机'
order by S.acctbal desc,S.name,P.partkey;
#（2）比较Part表的name上有索引和无索引#
//在Part表的name属性上建立索引
create index IDX_part_name on Part(name);
//删除name属性上的索引
alter table Part drop index IDX_part_name;
explain select * f
rom Part P,PartSupp PS,Supplier S
where P.partkey=PS.partkey and PS.suppkey=S.suppkey                             and P.name='发动机'
order by S.acctbal desc,S.name,P.partkey; 
（3）1>IN与EXISTS查询。
select *
from Orders 
where orderskey in(select orderskey from Lineitem                                 
where partkey in(select partkey                                 
from Part where name='发动机'));
#一般，使用exists查询效率要高于in查询，改写sql语句如下：#
select *
from Orders Owhere exists(select * from Lineitem L
                                 where O.orderskey =L.orderskey                                 
                                 and exists(select * from Part P                                  
                                 where P.partkey=L.partkey and name='发动机'));
                                

2>尽可能使用不相关子查询，避免使用相关子查询。不相关子查询一般比相关子查询执行效率要高，

//相关子查询:
select *
from Orders O1
where O1.totalprice>(select avg(O2.totalprice)                                    from Orders O2                                    where O2.custkey=O1.custkey);
//不相关子查询:
//子查询生成临时派生表，取名为avg1
select *
from Orders O1,(select O2.custkey, avg(O2.totalprice) as avgprice
from Orders O2group by O2.custkey)avg1
where O1.custkey=avg1.custkey and O1.totalprice>avg1.avgprice;

（4）

//1>Orders表中订单的totalprice由Lineitem表中该订单项的extendedprice，discount,tax等属性计算得出，
//即totalprice=SUM(Lineitem.extendedprice*(1-Lineitem.discount)*(1+Lineitem.tax))。
//查询所有购物金额大于20万的客户编号和购物总金额。
call Proc_CalTotalPrice4Order(5522);
select custkey,sum(totalprice)
from Orders
group by custkeyhaving sum(totalprice)>20000.00;
//如果不用totalprice字段，sql查询语句则为：
select O.custkey,sum(L.extendedprice*(1-L.discount)*(1+L.tax))
as sumprice
from Orders O,Lineitem L
where O.orderskey=L.orderskey
group by O.custkeyhaving sumprice>20000.00;
#2>Lineitem表中订单明细价格即extendedprice=quantity*Part.retailprice，查询所有购物零售总金额（折扣和加税之前的价格，大于1万的订单对应的客户编号及金额。#
select O.custkey,sum(L.extendedprice)
as sumextprice
from Orders O,Lineitem L
where O.orderskey=L.orderskey
group by O.custkeyhaving sumextprice>10000.00;
//如果不用extendedprice字段，sql查询语句则为：
select O.custkey,sum(L.quantity * P.retailprice) as sumextprice
from Orders O,Lineitem L,PartSupp PS,Part P
where O.orderskey=L.orderskey 
and L.partkey=P.partkey          
and L.suppkey=PS.suppkey 
and  PS.partkey=P.partkey
group by O.custkeyhaving sumextprice>10000.00;