Oracle数据库对SQL查询做了哪些优化

Question

我看网上有很多介绍数据库查询如何优化的，却没有人提到具体的数据库产品自身如何对SQL如何做优化。
我想简单了解一下数据库对复合查询、大表链接会做什么样的优化，算法上、内存磁盘管理上都可以。请任举Oracle、MySQL、SQLite为例都行。
感觉数据库是一个大黑盒，有人告诉我数据库会优化复合查询，所以能写复合查询就不要自己拆开写。有人告诉我，数据库在处理大数据的时候，会根据数据量的大小来决定中间结果是写入内存还是还是写入硬盘，所以，不要自己擅用临时表。
所以想要了解一下数据库的一些优化方案，而不是自己盲目去学习所谓别人总结出来的“优化法则”。
所以请列举一些数据库产品，如MySQL会做的一些优化项。

Answer 1

oracle内部有个优化器，以前是RBO，现在都改用CBO了。CBO有三个组件：

查询转换器（Query Transformer）

评估器（Estimator）

计划生成器（Plan Generator）

这概念就比较多了，简单描述一下吧：

查询转换器--查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式以产生较好的执行计划。有以下几种转换技术：视图合并（View Merging）、谓词推入（Predicate Pushing）、子查询反嵌套（Subquery Unnesting）、物化视图的查询重写（Query Rewrite with Materialized Views）和OR扩展（OR-expansion）。

评估器--评估器通过计算三个值来评估计划的总体成本：选择性（Selectivity）、基数（Cardinality）、成本（Cost）。

计划生成器--计划生成器的作用就是生成不同的执行计划，然后选择其中总体成本最低的一个。

由于不同的访问路径、连接方式和连接顺序可以任意组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果，因此一个SQL可能存在大量不同的执行计划。

 

这就是sql语句被解析生成执行执行计划的流程。优化器参考统计信息，决定sql的执行计划。比如两张表关联，哪张表作为驱动表，是否使用索引等。

 

不知道你所说的复合查询是个什么概念，如果是指包含子查询的查询，那么建议你最好不要这么干，oracle会改写子查询或者视图，但改写的效果可能很差。能不用就不要用，或者减少子查询和视图的嵌套。

Answer 2

这么大的问题？ 太大了。 说清楚一本书了。简单说 后台的优化处理器，会对 执行计划，做处理，会分析当前日志里面的数据结构，预估出适合的执行计划。磁盘存储等都和查询无关，是自身的结构，最多勉强说对主键都自动加了索引而已。索引原理楼主自己去看吧，当做一张表就行了。 不过现在oracle 也开始研究云存储，毕竟是趋势，过两年估计会部分实现。云存储可以先看看 hadoop。 执行计划又和你当前数据库默认配置有关，具体去看hint吧。

追问

感觉数据库是一个大黑盒，有人告诉我数据库会优化复合查询，所以能写复合查询就不要自己拆开写。有人告诉我，数据库在处理大数据的时候，会根据数据量的大小来决定中间结果是写入内存还是还是写入硬盘，所以，不要自己擅用临时表。
所以想要了解一下数据库的一些优化方案，而不是自己盲目去学习所谓别人总结出来的“优化法则”。
所以请列举一些数据库产品，如MySQL会做的一些优化项。

Answer 3

慎用SELECT DISTINCT：
DISTINCT子句仅在特定功能的时候使用，即从记录集中排除重复记录的时候。这是因为DISTINCT子句先获取结果集然后去重，这样增加SQLServer有用资源的使用。当然，如果你需要去做，那就只有去做了。
当如果你知道SELECT语句将从不返回重复记录，那么使用DISTINCT语句对SQLServer资源不必要的浪费。

Answer 4

要是全表扫描了 就看看用索引是否能快点。。。等等