下面对tpc-h的第21个查询查看原始写法和改写的等价写法的执行计划。
mysql> explain -- 21 -> select -> s_name, -> count(*) as numwait -> from -> supplier, -> lineitem l1, -> orders, -> nation -> where -> s_suppkey = l1.l_suppkey -> and o_orderkey = l1.l_orderkey -> and o_orderstatus = 'F' -> and l1.l_receiptdate > l1.l_commitdate -> and exists ( -> select -> * -> from -> lineitem l2 -> where -> l2.l_orderkey = l1.l_orderkey -> and l2.l_suppkey <> l1.l_suppkey -> ) -> and not exists ( -> select -> * -> from -> lineitem l3 -> where -> l3.l_orderkey = l1.l_orderkey -> and l3.l_suppkey <> l1.l_suppkey -> and l3.l_receiptdate > l3.l_commitdate -> ) -> and s_nationkey = n_nationkey -> and n_name = 'GERMANY' -> group by -> s_name -> order by -> numwait desc, -> s_name limit 10;
6 rows in set (0.00 sec)mysql> explain -- 21 fix -> select s_name ,count(*) as numwait from -> (select -> s_name,l_suppkey ,l_orderkey -> -- count(*) as numwait -> from -> supplier, -> lineitem l1, -> orders, -> nation -> where -> s_suppkey = l1.l_suppkey -> and o_orderkey = l1.l_orderkey -> and o_orderstatus = 'F' -> and l1.l_receiptdate > l1.l_commitdate -> and s_nationkey = n_nationkey -> and n_name = 'GERMANY' -> ) l1 -> where exists ( -> select -> * -> from -> lineitem l2 -> where -> l2.l_orderkey = l1.l_orderkey -> and l2.l_suppkey <> l1.l_suppkey -> ) -> and not exists ( -> select -> * -> from -> lineitem l3 -> where -> l3.l_orderkey = l1.l_orderkey -> and l3.l_suppkey <> l1.l_suppkey -> and l3.l_receiptdate > l3.l_commitdate -> ) -> group by -> s_name -> order by -> numwait desc, -> s_name -> limit 10;
7 rows in set (1 min 44.56 sec)我们从执行计划可以看出表之间的主要访问路径，是通过索引还是表扫描，但并不包含成本的估算值。我们直接从执行计划查看它的优劣还是有难度的。需要对MySQL有相当的使用经验才能编写出高效执行的SQL语句。从测试的情况看，用表的关联来替代In子查询 操作能提高效率。
2.统计信息收集和管理
正确的统计信息对查询优化器得出较好的执行计划有十分重要的影响，在大量插入或更新数据以后，需要重新收集统计信息。收集指定表的统计信息的语法如下：
ANALYZE TABLE 表名还有其他的优化手段，比如添加必要的索引，这其实已经从添加外键约束的过程中体现了，因为对MySQL来说，外键必须有索引，添加了索引后，第13个查询的时间大大缩短了，效果显著。
