昨天晚上，和同事讨论了一个SQL的问题，是如何选择根据某字段分组，然后取出MAX COUNT(XX) 值的数据出来。例如数据是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

test=# select * from tgroup;
id | age | point
----+-----+-------
  1 |   1 |    11
  2 |   1 |    32
  3 |   2 |    32
  4 |   2 |    13
  5 |   2 |    33
  6 |   2 |    38
  7 |   3 |    38
  8 |   2 |    38
  9 |   2 |    38
 10 |   2 |    38
 11 |   2 |    38
(11 rows)
 
test=#

现在要选择出根据 POINT 分组里包含个数最大的值。大概意思是：MAX(COUNT(*)) FROM tgroup GROUP BY POINT;
所以，一开始，我们的SQL语句是（失败）：

1

select MAX(COUNT(*)) FROM tgroup group by point;

然而，我们得出的错误是：

1

aggregate function calls cannot be nested

原来，聚集函数是不能嵌套调用的。

然后又想到能不能使用子查询来完成。SQL如下（成功） ：

1

select max(sum) from ( select count(*) as sum from tgroup group by point) as t;

再来一条，不使用子查询，而是使用ORDER BY 结合 LIMIT 来完成，SQL语句如下（成功):

1

select count(*) as sum from tgroup group by point order by sum desc limit 1;

最后使用PG里的CTE表达式最容易理解（成功）：

1

with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;

那他们的性能是何呢？测试了一千一百五十多万的数据。每条SQL性能如何？

1
2
3
4
5
6
7

test=# select count(*) from tgroup;
  count  
----------
11534336
(1 row)
 
test=#

使用子查询的性能（POINT没有索引）

1
2
3
4
5
6
7

test=# select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
   max  
---------
6291456
(1 row)
 
Time: 3055.716 ms

ORDER BY 结合 LIMIT（POINT没有索引）

1
2
3
4
5
6
7
8

test=# select count(*) as sum from tgroup group by point order by sum desc limit 1;
   sum  
---------
6291456
(1 row)
 
Time: 3047.152 ms
test=#

使用CTE表达式（POINT没有索引）

1
2
3
4
5
6
7

test=#  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
   max  
---------
6291456
(1 row)
 
Time: 25675.005 ms

后面为POINT添加索引，速度只有CTE表达式的加快了一倍（添加索引其实也不太科学，POINT的数据分布不均匀，重复的数据比较多，因为是通过 insert into select 的方式来生成大量数据的，只是想看一下添加索引后的效果）:

1
2
3
4
5
6
7
8

test=#  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
   max  
---------
6291456
(1 row)
 
Time: 11735.775 ms
test=#

其他两种方式，并没有什么变化。看执行计划，其他两种依然是使用Seq Scan的方式，而添加了索引后，CTE的方式使用了 CTE Scan + IndexOnlyScan的方式。

1
2
3
4
5
6
7
8
9
10
11

test=# explain  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
                                                QUERY PLAN                                               
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=955503.54..955503.55 rows=1 width=8)
   CTE cte
     ->  WindowAgg  (cost=0.43..695980.98 rows=11534336 width=4)
           ->  Index Only Scan using tgroup_point on tgroup  (cost=0.43..522965.94 rows=11534336 width=4)
   ->  CTE Scan on cte  (cost=0.00..230686.72 rows=11534336 width=8)
(5 rows)
 
Time: 0.909 ms

总结

看来没有 WHERE 或其他条件过滤数据而且数据量非常大的情况下，不适宜使用CTE表达式，因为它本质是一个一次性视图，生成一张这么大的视图，性能也快不到哪里去(可能使用物化视图会好点,不过没有测试过）。在大量数据情况下，还是使用普通的全表扫描比使用生成CTE再全表扫描来得快。这也应验了之前翻译篇文章的强调：CTE表达式的作用，真的不是为了加快查询速度，而仅仅是为了方便。冏 ~~。

上星期请教了条SQL为什么没有使用到索引的问题，引起了我对SQL执行顺序的疑惑，查了不少资料，收集到比较认可的答案如下，引用资料也在后面标识了。有什么不同的见解，还请大牛指出。

各部分的执行顺序

1
2
3
4
5
6
7
8
9
10
11

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP(LIMIT)

来源资料：
Stackoverflow whats-the-execute-order-of-the-different-parts-of-a-sql-select-statement

Stackoverflow order-of-execution-of-the-query

MSDN order-of-execution-of-sql-queries

翻译于dba.stackexchange.com
版权归原作者所有，本人只是业余爱好翻译。

这个问题是相当广泛的，但尽我所能给你一个普通的回答。

CTEs

• 不可以建索引（但可以使用引用到的对象的索引）
• 不能有约束
• 本质上是一个一次性视图
• 只在下一个执行查询时存在
• 可以递归
• 没有专门的统计状态数据（只依赖于底层对象）

临时表

• 是存在于临时数据库的真正物化的表
• 可以建索引
• 可以有约束
• 在当前连接的会话中会一直存在
• 可以被其他查询或子查询引用
• 引擎可以有专门为它准备的统计数据

至于何时使用哪个，他们有不同的应用场景。如果你会有一个非常大的结果集，或者需要不上一次地引用它，那请使用临时表。如果需要使用递归，一次性，或者做一些简单的逻辑计算，那就使用CTE。

注意，CTE永远不应该用于性能。使用CTE并不会提高你的性能，因为，它仅仅是个一次性视图。你可以用它做一些其他的事，但是为了加快查询真的并不是它的用途。

源文地址

作者:Robin Schumacher 和 Gary Carter,EnterpriseDB 公司

原文:
Replication is one of the most popular features used in RDBMS’s today. Replication is used for disaster recovery purposes (i.e. backup or warm stand-by servers), reporting systems where query activity is offloaded onto another machine to conserve resources on the transactional server, and scale-out architectures that use sharding or other methods to increase overall query performance and data throughput.

翻译:
今天，“复制”是关系数据库的最受欢迎的功能之一。复制的目的是用于灾难恢复（也就是备份或者叫“热”备用服务器），用于在事务服务器上报告系统，将查询活动转移到其他机器，以节约资源。并且这种分区或其他方法的横向扩展构架将会提升整体的查询性能以及数据的吞吐率。

原文:
Replication is not restricted to only the major proprietary databases; open source databases such as MySQL and PostgreSQL also offer replication as a feature. While MySQL has offered built-in replication for a number of years, PostgreSQL replication used to be accomplished via community software that was an add-on to the core Postgres Server. That all changed with the release of version 9.0 of PostgreSQL, which now offers built-in streaming replication that is based on its proven write ahead log technology.

翻译:
复制功能，并不仅仅限制在专利的商业数据库，一些开源的比如：MySQL 和 PostgreSQL 也提供复制的功能。当MySQL已经内建提供复制功能好几年时，PostgreSQL的复制功能还只是社区通过附加组件形式被添加到核心PostgreSQL服务器。但是，直到PostgreSQL9.0版本时，一切都发生改变了，PostgreSQL现在提供内建一种基于WAL(预写日志）技术的流复制功能。

原文:
With the two most popular open source databases now providing built-in replication, questions are being asked about how they differ in their replication technologies. What follows is a brief overview of both MySQL and PostgreSQL replication, with a brief compare and contrast of the implementations being performed immediately afterwards.

翻译:
现在，这两个最流行的开源数据库都已经提供了内建的复制功能，那么问题来了：他们两者之间的复制功能有什么区别？下面对MySQL和PostgreSQL作一些简要概述，作一个简单的比较和它们的实现区别之后来简单实践一下。

原文：
An Overview of MySQL Replication

Asynchronous replication was introduced into Oracle’s MySQL with version 3.23 and today it remains the primary feature employed by many MySQL users to create scale-out architectures, standby servers, read-only data marts, and more. The various supported MySQL replication topologies include:

• Single master to one slave

• Single master to multiple slaves

• Single master to one slave to one or more slaves

• Circular replication (A to B to C and back to A)

• Master to master

The major replication topology not currently supported in Oracle’s MySQL today is multi-source replication: having one or more master servers feed a single slave.

A graphical view of how MySQL replication functions can be represented as follows:

翻译:
MySQL的复制功能概要

异步复制在MySQL的3.23版本时就被引入了。今天，异步复制仍然是许多MySQL用户用于横向扩展、备库、只读数据等等的主要功能。MySQL支持的各种拓扑结构包括：

• 单主，一从

• 单主，多从

• 单主一从到多从

• 环形复制（A - B - C - A）

• 主对主

对于现在的MySQL主要不支持的拓扑结构是“多源复制”，也就是：拥有一个或多个主服务器，但只有一个从库。

MySQL复制功能的图形化表示如下：

原文：
Object, data, and security operations run on the master are copied to the master server’s binary log. A user has the option of replicating an entire server, one or more databases, or just selected tables (although filtering by table is only done on the slave). The slave server obtains information from the master’s binary log over the network , copies the commands and/or data, and first applies them to the slave’s relay binary log. That log is then read by another process – the SQL thread – that applies the replicated operations/data to the slave database and its binary log.

翻译：
在主库上，对象、数据和安全的操作全部都会被复制到主库服务器的二进制日志。用户可以选择复制整个服务器、一个或多个数据库、或仅仅选择某些表来进行复制（尽管表过滤只是在从库完成）。从库通过网络传输从主库获取二进制日志信息，复制的命令或数据会首先应用到从库的 relay 二进制日志里。这个 relay 二进制日志，会被其他工具处理 —— SQL 线程，SQL线程会将replay二进制日志里复制的操作命令和数据应用到从库自己的二进制日志里。

原文：
Prior to release 5.1, MySQL replication was statement-based, meaning that the actual SQL commands were replicated from the master to one or more slaves. However, certain use cases did not lend themselves to statement-based replication (e.g. non-deterministic function calls) so in MySQL 5.1 row-based replication was introduced. A user now has the option of setting a configuration parameter to use either statement or row-based replication.

翻译：
在5.1版本之前，MySQL 复制是基于语句的，意味着实际的SQL命令是通过复制从主库到一个或多个备库中的。然而，某些使用情况不能让它们基于语句复制（例如，非确定函数调用）。所以，在MySQL 5.1版本提供了基于行复制功能。用户现在可以通过配置相应的参数来决定是使用基于语句还是基于行复制。

原文：
The primary bottleneck for busy MySQL replication configurations is the single-threaded nature of its design: replication operations are not multi-threaded at the moment, although MySQL has declared it is coming in a future release. This limitation can cause some slave servers under heavy load to get far behind the master in regards to applying binary log information.

翻译：
在繁忙时的MySQL复制配置里，主要的瓶颈是由于它自身的单线程设计性质：复制操作都不是多线程的，尽管MySQL已经声称将来会将这个多线程特性添加到未来的版本中。这个限制可能导致从库在高负载情况下应用执行二进制日志将会远远落后于主库的日志信息。

原文：
Setting up MySQL replication is a fairly painless process. Although various setup procedures exist, in general, the following is a basic outline of how it is done:

• The master and slave servers are identified

• The master server is modified to include a replication security account

• The master server’s MySQL configuration file is modified to enable binary logging. A few other parameters are included as well (e.g. a unique server ID, type of replication such as statement or row-based, etc.)

• The slave server’s MySQL configuration file is modified to include a unique server ID

• The master server is restarted

• The master server’s log file position is recorded

• The master’s data is copied to the slave to initially seed the slave server. This can be done via a cold backup/restore, using the mysqldump utility, locking the master tables and doing a file copy, etc.

• The slave server is restarted

• The MySQL CHANGE MASTER command is executed on the slave server to set the master host name on the slave server as well as other parameters such as the master account username and password, the log file name, and beginning log file position

翻译：
设置MySQL的复制功能是一个非常痛苦的过程。尽管有许多种设置规则存在，但通常，以下是一些通常的步骤：

• 主库和从库都要标识

• 主库添加一个用于安全复制的账号

• 在主库中修改配置文件以开启二进制日志功能，还有一些其他的参数也要开启。 （比如：唯一的服务器ID， 复制的类型，等等）

• 在从库中修改配置文件，添加一个唯一的服务器ID。

• 重启主服务器

• 主库的日志文件位置被记录。

• 主库的数据将被初始化到从库中。这是通过冷备份/恢复完成的，例如使用：mysqldump 工具，锁住主库的表然后完成文件复制，等等。

• 重启从库

• MySQL CHANGE MASTER 命令是在从库服务器上执行设置将主库的主机名以及其他的参数如：主库的用户名，密码，日志文件名以及日志文件起始位置设置到从库。

原文：
Once set up, MySQL replication is quite reliable. Being asynchronous in nature, however, there are use cases that could result in data loss between a master and slave. To help combat these situations, MySQL 5.5 introduced semi-synchronous replication where a pending transaction is sent from a master to a slave, but not committed on the slave; it merely ‘lands’ safely on the slave to be run as soon as possible. Once the master is notified that the transaction is safely recorded on the slave, then the transaction is committed on the master.

翻译：
一旦设置完毕，MySQL复制是相当可靠的。因为本质上是异步的，所以有一些使用情况下还是会导致在主库和从库之间丢失数据。为了帮助解决这个问题，MySQL 5.5 提供了“半同步”复制，它会将事务从主库发送到从库，但在从库并未提交（只是记录到relay二进制日志）。它仅仅是尽可能快速地被安全送到从库。一旦主库收到事务已经被安全地记录在从库上，然后主库才会正式提交事务。

原文：
In terms of MySQL replication limitations and missing features, besides the already mentioned single threaded nature of the implementation and the inability to perform multi-source replication, other wish-list items include a full synchronous option, conflict detection and resolution, time-delayed replication, changing the binary log to a storage engine, better replication filtering on the master, global statement ID’s, and graphical tools to manage replication functions.

翻译：
MySQL在复制功能方面有许多限制以及不足，除了之前已经提过的单线程性质，还有不能进行多源进制，其他的希望加入的特性包括：完全同步选项，冲突检测及解决办法，延时复制，更改二进制日志的存储引擎，在主库上更好的复制过滤功能，全局语句的ID标识以及管理复制函数的图形化工具。

原文：
There are third-party providers of MySQL replication solutions that overcome some of the current shortcomings in what is provided out-of-the-box with MySQL. One example is Continuent’s Tungsten product.

For more information about Oracle’s MySQL replication, see: http://dev.mysql.com/doc/refman/5.5/en/replication.html.

翻译：
有许多第三方提供即开即用的MySQL复制解决方案来克服当前MySQL内建复制功能的缺点。一个例子是：Continuent’s Tungsten 的数据库产品。
更多关于MySQL复制功能，请看：MySQL5.5复制

原文：
An Overview of PostgreSQL Replication

PostgreSQL replication is based on a mature and long used technology called write ahead log (WAL) archiving. WAL technology has been in use since version 7.1 and has been used in features such as backup and restore and warm standby servers (i.e. slave servers offline kept in synch with the master to step in during crash recovery) for high availability.

PostgreSQL 9.0 introduced significant enhancements producing extremely fast WAL processing that results in near real-time replication and hot standby capabilities for slave servers. The supported PostgreSQL replication topologies include:

• Single master to one slave

• Single master to multiple slaves

A graphical view of how PostgreSQL replication functions can be represented as follows:

翻译：
PostgreSQL的复制功能概要

PostgreSQL 复制功能是基于一个成熟并且被长时间使用的技术，叫WAL（预写日志）归档。WAL技术已经在PostgreSQL 7.0版本被使用，并且是用在备份/恢复和热备用服务器的高可用中。（比如，从库离线【注，这里我认为是作者写错了，应该是online在线，而不是offline离线】保持与主库同步，并且在主库崩溃时介入以进行恢复）。

PostgreSQL9.0 版本显著地改进以产生极快的WAL日志处理，结果就是一个几乎是近实时复制并且是双机（主从）热备功能的从库。PostgreSQL支持的复制拓扑结构包括：

• 单主单从

• 单主多从

A graphical view of how PostgreSQL replication functions can be represented as follows:

PostgreSQL复制功能的图形化表示如下：

原文：

All objects and data (including schema) and security operation executed on the master are written to the WAL log directly on the slave machine for safety (avoiding complete data loss in the event of a catastrophic master failure). WAL also ensures that no transaction is committed on the master until a successful write of the WAL log has occurred. No filtering is currently possible (although replication with filtering is possible with the xDB Replication Server from EnterpriseDB) so a complete copy of the master is replicated on the slave.

翻译：
所有的对象和数据（包括模式）和在主库安全的操作都被写到WAL日志会立即安全地同步地从库(完全避免了在主库发生灾难时导致的数据丢失)。WAL也会确保在主库上不会有事务被提交，直到该事务已经成功地写到入WAL日志。当前版本并没有选择性复制功能（尽管选择性复制功能可能在 EnterpriseDB 的 xDB复制服务器提供了），所以主库会完整地被复制到从库。

原文：
The slave then applies the WAL log by directly rewriting the raw table data on disk, which is much faster than statement based replication. It is also safer since statements such as:

INSERT INTO table (column) VALUES (SELECT function());

may have unexpected and inconsistent results if the function returns different values on different servers - perhaps because it involves a generated timestamp or uuid.

翻译：
从库然后直接应用磁盘上的WAL日志来重写元数据，这点比基于语句复制更加快速。并且，遇到以下这种SQL时都是安全的：

INSERT INTO table (column) VALUES (SELECT function());

如果这个function函数在主从两个不同的服务器之间产生不同的值，这可能有意想不到和不一致的结果。比如可能它调用一个产生UUID或Timestamp的函数。

原文：
The primary limitations of PostgreSQL replication are topology based. It cannot currently do cascading replication or filter tables by rows for replication. Again, these are capabilities available in a separate replication solution from EnterpriseDB called xDB Replication Server.

翻译：
PostgreSQL复制的主要限制是它的拓扑结构。它目前不能进行联级复制，也不能通过行来过滤表进行复制。同样，这些功能可以通过 EnterpriseDB 里单独提供一种叫 xDB 复制服务器来解决。

原文：
Setting up PostgreSQL replication is very straightforward. WAL logging is always enabled with minimal configuration needed by the user to utilize replication. The basic process to get replication going is:

• The master and slave servers are identified

• The postgresql.conf file on the master is edited to turn on streaming replication

• The pg_hba.conf file on the master is edited in order to let the slave connect

• The recovery.conf and postgresql.conf files on the slave are edited to start up replication and hot standby

• The master is shutdown and the data files are copied to the slave

• The slave is started first

• The master is started

翻译：
设置PostgreSQL复制是非常简单的。开启WAL日志是用户使用复制的最小化配置。使用复制的基本流程如下：

• 主库和从库都要标识

• 编辑在主库的postgresql.conf 配置文件里，以开启流复制

• 编辑在主库的pg_hba.conf 配置文件，以让从库连接到主库

• 编辑在从库的 recovery.conf 配置文件和postgresql.conf 配置文件，以开始复制和热备用

• 关闭主库，并且复制data目录所有文件到从库

• 先启动从库

• 再启动从库

原文：
The secret sauce to PostgreSQL 9.0’s extremely reliable WAL based replication is a set of enhancements to efficiently stream very small WAL segments compared to earlier versions. Like MySQL there are cases where data loss could occur – however, depending on how you configure the system, your hardware architecture, and load, its possible the data loss could be as small as a single transaction.

PostgreSQL does not currently have native synchronous replication. However, there are multiple replication options available from other community and third-party software providers. PostgreSQL offers multiple solutions for multi-master replication, including solutions based on two phase commit. Offerings include Bucardo, rubyrep, PgPool and PgPool-II and Tungsten Replicator as well as some proprietary solutions. Another promising approach, implementing eager (synchronous) replication is Postgres-R, however it is still in development. Yet another project implementing synchronous replication is Postgres-XC, which is a shared-nothing, transactional scale-out solution that is still under development.

For more information on PostgreSQL replication see:

PostgreSQL Documentation: http://www.enterprisedb.com/docs/en/9.0/pg/high-availability.html

Bucardo: http://bucardo.org/wiki/Bucardo

PgPool-II: http://pgpool.projects.postgresql.org/

Tungsten Replication: http://www.continuent.com/community/tungsten-replicator

翻译：
PostgreSQL9.0非常可靠的秘密武器在于基于WAL日志复制，它是一套增强的高效流，利用非常小的一段WAL来进行早期版本的比较。像MySQL在有些情况下可能会导致数据丢失，然而，这取决于你的系统、你的硬件构架以及负载如何，它丢失的数据可能非常小，如：一个事务的数据。

PostgreSQL目前还没有本地同步复制。然而，有许多种通过社区和第三方软件提供商提供的可用复制方案。PostgreSQL 提供许多“多主复制”的解决方案，包括基于两阶段提交的解决方案。包括：Bucardo, rubyrep, PgPool and PgPool-II and Tungsten Replicator 以及其他一些专有的解决方案。另一种非常有前景的解决办法，实现 饥饿（同步）复制的Postgres-R产品，然而，它目前还在开发中。还有另一个项目实现同步复制的是Postgres-XC，它是一个无共享、事务横向扩展的解决方案，目前也是处于开发中。

更多关于PostgreSQL复制，请看：
PostgreSQL 文档: http://www.enterprisedb.com/docs/en/9.0/pg/high-availability.html

Bucardo: http://bucardo.org/wiki/Bucardo

PgPool-II: http://pgpool.projects.postgresql.org/

Tungsten Replication: http://www.continuent.com/community/tungsten-replicator

原文：
A Brief Compare and Contrast of MySQL and PostgreSQL Replication

Those wanting to use an open source database for a particular application project that requires replication have two good choices in MySQL and PostgreSQL. But, the question naturally arises, which should be used? Is one just as good as the other?

As demonstrated above, there are both feature and functional differences between how MySQL and PostgreSQL implement replication. However, for many general application use cases, either MySQL or PostgreSQL replication will serve just fine; technically speaking, from a functional and performance perspective, it won’t matter which solution is chosen. That said, there still are some considerations to keep in mind in deciding between the different offerings. Some of these include the following:

• Oracle’s MySQL offers both statement and row-based replication, whereas PostgreSQL only uses the latter based on write ahead log information. There are pro’s and con’s to using statement-based replication, which MySQL has documented here: http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html. It is generally acknowledged that row or WAL-based replication is the safest and most reliable form of replication. It does, however, result in larger log files for MySQL than the statement-based option does.

• MySQL currently supports more replication topologies than PostgreSQL (e.g. ring, etc.). However PostgreSQL does have a number of community supported replication offerings that help close this gap (e.g. Bucardo’s master-to-master solution).

• In regard to data loss, MySQL 5.5 offers the semi-synchronous option, which helps minimize the risk of master-slave synchronization problems due to a master server going down. For PostgreSQL, a full synchronous replication option is in development and scheduled for release sometime in 2011.

• As to replication filtering, MySQL provides filtering on the slave server, whereas with PostgreSQL, no filtering is available; in other worlds, the entire database from the master is replicated to the slave. With MySQL, all the information is sent, but then options exist to selectively apply the replicated events on the slave. However, as the MySQL binary log is not used for crash recovery purposes in the same way as PostgreSQL’s WAL is, a user can configure a MySQL master so only certain databases are logged and, in that sense, a filter for the master server is available.

• Both MySQL and PostgreSQL replication are single-threaded at the current time.

• With respect to monitoring replication, MySQL provides a number of SHOW commands to understand the state of replication between a master and slave. To date, PostgreSQL offers functions to compute the differences in log positions between the master and slave servers, but that is all that is currently provided in 9.0.

• For failover and load balancing, the PostgreSQL community provides pgPool, which is middleware that provides connection pooling, load balancing, failover, and more between replicated servers. MySQL 5.5 supports connection pooling in the Enterprise edition, but failover and load balancing must be handled via a third-party product or custom development.

翻译：
简单对比一下MySQL和PostgreSQL复制

那些想为一个需要复制功能的特定应用的项目使用开源数据库的人，MySQL和PostgreSQL是两个很好的选择。但是，问题自然而然产生了，我们应该使用哪个？还是说这两个一样好？

综上所述，MySQL和PostgreSQL都有复制功能，但是有不同的实现。然而，对于一般的应用来说，无论是MySQL还是PostgreSQL复制功能会工作得挺好；从技术上说，以及从功能和性能来看，它不会不管要选哪个数据库。这意味着，在不同产品之间还有一些值得注意的事项，其中包括以下内容：

• MySQL提供基于语句和基于行的复制，而PostgreSQL只有基于WAL日志信息。有赞成也有反对使用基于语句复制的，MySQL有文档介绍：http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html 。一般认为，基于行或基于WAL 复制是最安全和最可靠的复制形式。的确如此，然而，这会导致比基于语句复制的形式产生更大的日志文件。

• MySQL目前比PostgreSQL支持更加多的复制拓扑结构（比如：环形等）。然而，PostgreSQL有许多种社区支持的复制选项，这就缩小了这个因拓扑结构种类而导致的距离（比如 Bucardo 的 主-主解决方案）

• 考虑到数据丢失，MySQL 5.5 提供了半同步选项，这有助于减小因主库崩溃而导致主从同步问题的风险。对于PostgreSQL，完全同步复制特性正在开发，并且计划于2011年正式可用。

• 对于过滤复制，MySQL 提供了在从库过滤，而在PostgreSQL，并不能使用过滤，换句话说，就是完整地将数据库从主库复制到从库。对于MySQL，所有信息都会被发送到从库，但如果开启过滤复制，从库会有选择地将事件应用到从库。然而，对于MySQL二进制日志并不是用于灾难恢复的，但PostgreSQL的WAL是可以用于灾难恢复的，用户可以配置MySQL主库指定哪些数据库会被记录到日志，在这种意义上，过滤器对于主库是可用的。

• 目前，MySQL和PostgreSQL复制都是单线程的

• 对于复制的监控，MySQL提供了许多 SHOW 命令去了解主从复制的状态。至今，PostgreSQL提供一些函数去计算主从日志位置的区别，但是当前只是在PostgreSQL9.0版本才提供的。

• 对于故障切换和负载均衡，PostgreSQL社区提供pgPool的中间件，pgPool提供连接池，负载均衡，故障切换和更多种复制形式。MySQL 5.5 在企业版里提供连接池，但是对于故障切换和负载均衡必须通过第三方产品或定制开发。

原文：
Conclusions

As was previously stated, for many application use cases, both Oracle’s MySQL and PostgreSQL replication will be an equally good choice. The best way to determine which is right for you is to download both and put each through a comprehensive evaluation.

You can download Oracle’s MySQL at http://www.mysql.com/downloads/, while both community and EnterpriseDB’s offerings of PostgreSQL can be found at: http://www.enterprisedb.com/products/download.do.

By Robin Schumacher and Gary Carter, www.enterprisedb.com

18 Nov 2010

翻译：
结论

正如前面所指出一样，对于许多应用程序的用例，MySQL和PostgreSQL都是非常好的选择。要决定哪一个最适合你，最好的办法就是同时下载它们两个，然后使用进行综合评估。

你可以在 http://www.mysql.com/downloads/ 下载MySQL，PostgreSQL的社区版和EnterpriseDB提供的PostgreSQL版本都可以在这个地址里找到：http://www.enterprisedb.com/products/download.do

作者：Robin Schumacher and Gary Carter, www.enterprisedb.com
18 Nov 2010

注：版权是原作者所有，我只是出于业余爱好进行翻译。

这也是我的处女版翻译文章，有许多不足或表达不清晰的地方，恳请各位指出，我会加以修改，一起为开源、为PostgreSQL作出一份力量。
于2014年12月9号星期二晚，广州

什么是窗口函数

PostgreSQL窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数，作为窗口函数；否则它们作为常规的聚合函数。

我个人的理解：窗口也是一种分组，但和 group by 的分组不同。窗口，可以提供分组之外，还可以执行对每个窗口进行计算。可以相像成是group by 后，然后对每个分组进行计算，而不像Group by ，只是单纯地分组。

窗口函数的语法

1
2
3
4

function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )

窗口函数的调用总是包含一个OVER子句，即窗口函数的名称和参数。 该语法区别于普通函数或聚合功能。OVER子句决定究竟将 查询的行如何通过窗口函数拆分处理。OVER子句内的PARTITION BY分区指定 行划分成组，或分区，共享相同的PARTITION BY值。 对于每一行，窗口函数通过同一个分区作为当前行的行进行计算

如果OVER不使用PARTITION BY时即代表整个表。

典型的窗口函数用法

1

select path, cost, sum(cost) over (partition by path ) as sum_cost from tcost;

内置的窗口函数

在 PostgreSQL 技术群里，今天发现有个人在群里“求救”，说想要执行一条SQL语句，获取某表中以某字段为组，并且sum（其他字段）结果最小的，所有结果。

比如，有张表如下：

1

path(int)  cost(decimal)

想要的结果是，以path所有相同的字段分组，并且 sum(cost)字段，选择出sum(cost)值最小的，所有path字段。如：

我写的SQL：

1
2

with cte as (select path, cost, sum(cost) over (partition by path ) as sum_cost from tcost)
select path , cost from cte where sum_cost = (select min(sum_cost) from cte);

我的解题思路

在没有接触到PostgreSQL之前，我一直使用MySQL，每次想到“组”这个字，总会想到 group by 。虽然可能使用group by 也可能实现相同的结果，但是经常需要表自身join表，所以性能方面对于数据量大的表话，是满足不了要求的，即使是有索引。因为索引最适合于那种存在索引，而且选择率低的表，否则的话，索引的优势其实和全表扫描差不多，甚至有时候，常常是全表扫描比索引的全表扫描性能还要好。（当然，在PostgreSQL中，如果是只读索引来扫描的话，性能是最好的）。因为MySQL的 InnoDB 是索引组织表，所以索引全表和普通的全表扫描，性能几乎是没有差别。但是在PostgreSQL中，这种差别就很明显了，选择率大的索引全表扫描，比顺序全表扫描SeqScan慢好多。

说远了，回到题目上来。这思路虽然也是要分组，但这种分组跟group by 的分组差得比较远，这种需要一种“窗口函数”（Window Function，在Oracle里叫分析函数）来处理这钟需求，而且这种窗口函数的性能是比那种需要自表连接的性能快好多的，即使是没有索引情况下。之前在群里也遇到这种情况，利用窗口函数几秒钟就可以出结果，但那种自连接的（特别在数据量大的情况下）要几十分钟。这种窗口分组来处理数据，可以避免好多性能问题，而且非常易于理解。

所以，对于PostgreSQL，一有那种需要那种类似窗口的分组操作，首先要想到 Window Function，真的是非常好用。

ps aux | grep postgres

1
2
3
4
5
6

postgres 31964  0.0  0.3 162180 12436 pts/0    S    18:09   0:00 /usr/local/pg/bin/postgres -D /usr/local/pg/data
postgres 31966  0.0  0.0 162312  2192 ?        Ss   18:09   0:00 postgres: checkpointer process                  
postgres 31967  0.0  0.0 162180  1820 ?        Ss   18:09   0:00 postgres: writer process                       
postgres 31968  0.0  0.0 162180   960 ?        Ss   18:09   0:00 postgres: wal writer process                   
postgres 31969  0.0  0.0 163032  2064 ?        Ss   18:09   0:00 postgres: autovacuum launcher process           
postgres 31970  0.0  0.0  18000   948 ?        Ss   18:09   0:00 postgres: stats collector process

1
2
3
4
5
6
7

postgres: logger process:  在PostgreSQL中称为SysLogger(8.0)，用于整个系统的日志输出；
postgres: checkpointer process:  在PostgreSQL中称为Checkpointer(9.2），用于处理checkpoints；
postgres: writer process:  在PostgreSQL中称为BgWriter，用于将脏页刷出到磁盘；
postgres: wal writer process:  在PostgreSQL中称为WalWriter(8.3)，处理预写日志输出；
postgres: autovacuum launcher process:   在PostgreSQL中称为AutoVacuum(8.1)，用于系统的自动清理；
postgres: archiver process:  在PostgreSQL中称为PgArch，用于预写日志归档；
postgres: stats collector process:  在PostgreSQL中称为PgStat，用于统计数据收集。

简介

PostgreSQL官网

PostgreSQL 官网号称：它是最世界最先进的开源数据库（顺便说一下，MySQL官网说它自己是最流行的开源数据库）。我觉得这一句话，也已经非常足够概括 PostgreSQL 了（MySQL 官网的那句话，也概括了它自己的特点，一个是最先进，一个是最流行）。也有号称是 Oracle 的开源版，因为PostgreSQL被经常拿来与Oracle这个重量级的商业数据库比较。的确，PostgreSQL 与 Oracle 兼容性非常强，Oracle 的 DBA 可以非常快地上手 PostgreSQL。在 PostgreSQL 技术群里的大牛大多都是从 Oracle 转到 PostgreSQL，使我印象非常深刻就是 @德哥，绝对是PostgreSQL的大神级别。我也是跟着德哥的视频一边看，一边学习的。

安装（以 Ubuntu 下源码安装 PostgreSQL 9.3.5 为例）

下载

PostgreSQL 源码下载地址

安装与使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

#安装必要的编译环境
sudo apt-get install build-essential libreadline-dev
#解压
tar -xvjf postgresql-9.3.5.tar.bz2
#创建一个目录，将PostgreSQL安装到这个目录
sudo mkdir /usr/local/pg
#配置
cd postgresql-9.3.5
./configure --prefix=/usr/local/pg
#开始编译
make -j
-j：开启多核编译
如果有：All of PostgreSQL successfully made. Ready to install. 表示编译成功
#安装
sudo make install
如果出现：PostgreSQL installation complete. 表示安装成功
#添加 PostgreSQL 用户
sudo useradd -s /bin/bash -d /home/postgres postgres
#创建 PostgreSQL 的 data 数据目录
sudo mkdir /usr/local/pg/data -p
#修改拥有者身份
sudo chown postgres:postgres /usr/local/pg/ -R
#初始化数据库
su - postgres
/usr/local/pg/bin/initdb -D /usr/local/pg/data
#启动数据库
/usr/local/pg/bin/postgres -D /usr/local/pg/data >logfile 2>&1 &
或者
/usr/local/pg/bin/pg_ctl -D /usr/local/pg/data -l logfile start
#创建数据库
/usr/local/pg/bin/createdb test
#连接数据库
/usr/local/pg/bin/psql test

配置环境变量

为了避免每次使用绝对路径以及一些参数问题，可以使用一些环境变量来代替，PostgreSQL 在没有指定参数时，就会读取这些环境变量的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

vi /home/postgres/.bash_profile
#这个是默认的PostgreSQL端口
export PGPORT=5432
#这个是PostgreSQL数据目录
export PGDATA=/usr/local/pg/data
#所使用的语言
export LANG=en_US.utf8
#PostgreSQL 安装目录
export PGHOME=/usr/local/pg
#PostgreSQL 连接库文件
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
#将PostgreSQL的命令行工具添加到 PATH 环境变量 ，这样子就不用每次使用绝对路径了
export PATH=$PGHOME/bin:$PATH
#PostgreSQL的 man 手册
export MANPATH=$PGHOME/share/man:$MANPATH
#PostgreSQL的默认用户
export PGUSER=postgres
#这个是PostgreSQL默认主机地址
export PGHOST=127.0.0.1
#连接数据库时默认的数据库名
export PGDATABASE=postgre

PostgreSQL所有环境变量

PostgreSQL 的 bin 目录常用工具说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

#clusterdb
 
     — 对一个PostgreSQL数据库进行建簇
 
#createdb
 
    — 创建一个新的 PostgreSQL 数据库
 
#createlang
 
    — 定义一种新的 PostgreSQL 过程语言
 
#createuser
 
     — 定义一个新的 PostgreSQL 用户帐户
 
#dropdb
 
     — 删除一个现有 PostgreSQL 数据库
 
#droplang
 
    — 删除一种 PostgreSQL 过程语言
 
#dropuser
 
    — 删除一个 PostgreSQL 用户帐户
 
#ecpg
 
    — 嵌入的 SQL C 预处理器
 
#pg_basebackup
 
    -- 做一个PostgreSQL集群的基础备份
 
#pg_config
 
    — 检索已安装版本的 PostgreSQL 的信息
 
#pg_dump
 
    — 将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中
 
#pg_dumpall
 
    — 抽出一个 PostgreSQL 数据库集群到脚本文件中
 
#pg_restore
 
    — 从一个由 pg_dump 创建的备份文件中恢复 PostgreSQL 数据库。
 
#psql
 
    — PostgreSQL 交互终端
 
#reindexdb
 
    -- 重新建立一个数据库索引
 
#vacuumdb
 
    — 收集垃圾并且分析一个PostgreSQL 数据库
 
#pg_receivexlog
 
    从另一台运行PG的数据库里接收 wal 日志
 
#pg_resetxlog
 
    重置一个 PostgreSQL 数据库集群的预写日志以及其它控制内容

PostgreSQL 的 data 目录说明

代价模型

总代价 = IO 代价 + CPU 代价
COST = P * a_page_cpu_time + W * T

P：计划运行时访问的页数，a_page_cpu_time 是每个页读取的时间花费，其积反映了IO代价
T：访问的元组。反映了CPU花费。（存储层是以页面为单位，数据以页面的形式读入内存，每个页面上可能有多个元组，访问元组需要解析元组结构，才能把元组上的字段读出，这消耗的是CPU）。如果是索引扫描，则还会包括索引读取的花费。
W：权重因子。表明IO到CPU的相关性，又称选择率（selectivity）。选择率用于表示在关系R中，满足条件“A<op>a”的元组数与R的所有元组N的比值。

单表扫描算法

全表扫描，局部扫描。单表扫描与IO操作密切相关。

1）顺序扫描（SeqScan）。当无索引可用，或访问表中的大部分数据，或表的数据量很小时，使用顺序扫描效果较好。

2）索引扫描（IndexScan）。根据索引键读索引，找出物理元组的位置。【如果选择率很高，不适宜使用索引扫描】

3）只读索引扫描（IndexOnlyScan）。根据索引键读索引，索引中的数据能够满足条件判断，不需要读取数据页面。比索引扫描少了读取数据的IO花费。

4）行扫描（RowIdScan）。用于直接定位表中的某一行。对于元组，通常为元组增加特殊的列，通过特殊的列计算出元组r物理位置，然后直接读取元组对应的页面，获取元组。在PostgreSQL中称为【Tid】扫描，此种方式是在元组头上增加名为【CTID】的列，用这列的值可直接计算本条元组的物理存储位置。

5）并行表扫描（ParallelTableScan）。对同一个表，并行地、通过顺序的方式获取表的数据，结果是得到一个完整的表数据。

6）并行索引扫描（ParallelIndexScan）。对同一个表，并行地、通过索引的方式获取表的数据，将结果合并在一起。

7）组合多个索引扫描（MultipleIndexScan）。

顺序扫描代价估算公式

COST = N_page * a_tuple_IO_time + N_tuple * a_tuple_CPU_time

索引扫描代价估算公式

COST = C_index + N_page_index * a_tuple_IO_time
C_index：索引的IO花费。C_index = N_page_index * a_page_IO_time
N_page_index：索引作用下的可用元组数。N_page_index = N_tuple * 索引选择率

索引

本质上是通过索引直接定位表的物理元组，加快数据获取的方式，所以索引优化的手段是物理查询优化。

如何利用索引

1）索引列作为条件出现在 WHERE，HAVING， ON 子句中。

2）索引列是被连接的表（内表）对象的列且存在于连接条件中

除了上述两种情况，还有一些特殊情况可以使用索引，如：排序操作、在索引列上求MIN、MAX值等。

（1）对表做查询，没有列对应作为过滤条件（如出现在WHERE子句中），只能做顺序扫描。

（2）对表做查询，有列对象且索引列作为过滤条件，可做索引扫描。

（3）对表做查询，有列对象作为过滤条件，但索引列被运算符“-”处理，查询优化器不能在执行前进行取反运算，这时不可利用索引扫描，只能做顺序扫描。

（4）对表做查询，有列对象作为过滤条件，且目标列没有超出索引列，可做只读索引扫描，这种扫描方式比单纯的索引扫描的效率更高。

（5）对表做查询，有索引存在，但选择条件不包括索引列对象，只能使用顺序扫描。

（6）对表做查询，有索引存在，选择条件包括索引列对象，可使用索引扫描，对选择条件中不存在索引的列作为过滤器被使用。

（7）对表做查询，有索引存在，选择条件包括索引列对象，但索引列对象位于一个表达式中，参与了运算，不是“key=常量”格式，则索引不可使用，只能是顺序扫描。如：
select a. from a where a.a1 + a.a3 = 2；（a1列是索引），这时只能做顺序扫描。
或
select a. from a where a.a1 = 2 - a.a3 ；（a1列是索引），这时只能做顺序扫描。

（8）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符 > 或 < ，可做索引扫描。

（9）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符 <> ，不可做索引扫描。

（10）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符BETWEEN-AND ，可做索引扫描。

索引列的位置对使用索引的影响

（1）索引列出现在目标列，通常不可使用索引（但不是全部情况下不能使用索引）

（2）聚集函数MIN / MAX用在索引列上，出现在目标列，可使用索引。

（3）索引列出现在WHERE子句中，可使用索引。

（4）索引列出现在 JOIN / ON 子句中，作为连接条件，有时不可使用索引。（这取决于代价估算模型）

（5）索引列出现在 JOIN / ON 子句中，作为限制条件满足“key <op> 常量 ”格式可用索引。

（6）（5）索引列出现在 WHERE子句中，但与子查询比较，格式上不满足"key <op> 常量"，不可用索引。

索引列对GROUP BY子句的影响

（1）索引列出现在 group by 子句中，不触发索引扫描。

（2）WHERE子句出现索引列，【且】GROUP BY 子句出现索引列，索引扫描被使用。

（3）WHERE子句中出现非索引列，且GROUP BY子句出现索引列，索引扫描不被使用。

索引列对HAVING子句的影响

（1）WHERE子句出现非索引列，且GROUP BY和HAVING子句出现索引列，索引扫描被使用。

索引列对ORDER BY子句的影响

（1）ORDER BY子句出现索引列，可使用索引。

（2）ORDER BY子句使用非索引列，不可使用索引扫描。

索引列对 DISTINCT 的影响

（1）DISTINCT 子句管辖范围内出现索引列，不可使用索引。

联合索引对索引使用的影响

（1）使用联合索引的全部索引键，可触发索引的使用。

（2）使用联合索引的前缀部分索引键。如：key_part_1 <op> 常量。可触发索引的使用。

（3）使用部分索引，但不是联合索引的前缀部分，如“key_part_2 <op> 常量"，不可触发索引的使用。

（4）使用索引索引的全部索引键，但索引键不是AND操作，不可触发索引的使用。

多个索引对索引使用的影响

（1）WHERE子句出现两个可利用的索引，优选最简单的索引。（但这也是要根据代价估算模型来决定的）

（2）WHERE子句出现两个可利用的索引且索引键有重叠部分，优选最简单的索引。

聚簇索引

是指表的一个或多个列作为索引的关键字，以关键字的具体值为依据，把所有具有相同值的元组连续放在外存上。当从磁盘扫描读取的块进入内存时，相同值的其他元组在内存中的概率增大，能有效减少IO。即：聚簇索引确定表中数据的物理顺序。聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

主要解决的问题

如何找出SQL语句等价的变换形式，使得SQL执行更高效。

可优化的思路

（1）子句局部优化。
如等价谓词重写，WHERE和HAVING条件化简中的大部分情况。

（2）子句间关联优化。
子句与子句之间关联的语义存在优化的可能，如外连接消除、连接消除、子查询优化、视图重写等。

（3）局部与整体的优化。
协同局部与整体。如OR重写并集规则需要考虑UNION操作（UNION是变换后的整体形式）的花费和OR操作（OR是局部表达式）的花费。

（4）形式变化优化
多个子句存在嵌套，可能通过形式的变化完成优化。如：嵌套连接消除。

（5）语义优化
根据完整性约束，SQL表达的含义等信息对语句进行语义优化

（6）其他优化
根据一些规则对非SPJ做的其他优化，根据硬件环境进行的并行查询优化。

它们都是依据关系代数和启发式规则进行。

关系模型

关系数据结构

即二维结构，二维表。即数据库中的表。
关系是一种对象
关系即是表
关系的元数据，即表结构，通常称为列或属性。
关系的数据，即表的行数据，通常称为元组（tuple），也称为记录(record)。        

关系操作集合

并，交，差，积，选择，投影，连接，除。。
选择：单个关系中筛选元组。
投影：单个关系中筛选列。
连接：多个关系中根据列间的逻辑运算筛选元组（自然连接，等值连接）
除：多个关系中根据条件筛选元组（NOT EXISTS 的子查询实现除）
并：多个关系合并元组（用UNION实现并）
交：多个关系中根据条件筛选元组（用两次NOT IN 实现交）
差：多个关系中根据条件筛选元组（NOT IN 子查询实现差）
积：无连接条件。N*M条元组

关系类型

R <op> S

自然连接：R和S中“公共属性”，结果包括公共属性名字上相等的所有元组的组合，在结果中把重复的列去掉。（是同时从列和行的角度进行去重）

@-连接：R和S中没有公共属性，结果包括在R和S中满足操作符@的所有组合。@通常包括：< <=, =, =, >=。即从关系R和S的广义笛卡儿积中选取A，B属性相等的那些元组，是从“行”的角度进行运算。

等值连接：操作符是 = 的@-连接。

半连接：结果包括在S中公共属性名字上相等的元组的所有的R中的元组。即结果包括R的部分元组，而R中的部分元组的公共属性的值在S中同样存在。SQL中没有自己的连接操作符，使用EXISTS， IN 关键字做子句的子查询常被查询优化器转换为半连接。

反连接：结果是在S中没有在公共属性名字上相等的元组的R的元组。即为半连接的补集，反连接有时称为反半连接。在SQL中没有自己的连接操作符，使用了 NOT EXISTS 则被查询优化器转换为反半连接。

外连接（左外连接）：结果包括R中的所有元组。若在S中有在公共属性名字上相等的元组，则正常连接；若在S中没有公共属性名字上相等的元组，则依旧保留此元组，并将对应的其他列设为NULL

外连接（右外连接）：结果包括S中的所有元组。若在R中有在公共属性名字上相等的元组，则正常连接；若在R中没有公共属性名字上相等的元组，则依旧保留此元组，并将对应的其他列设为NULL

外连接（全外连接）：结果包括S和R中的所有元组。对于每个元组，若在另一个关系中有在公共属性名字上相等的元组，则正常连接；若在另一人关系中没有公共属性名字上相等的元组，则依旧保留此元组，并将对应的其他列设为NULL

关系完整性约束

查询句与二叉树

叶子是关系（表）

内部结点是运算符（或称算子，操作符，如 LEFT OUT JOIN，表示左右子树的运算方式）

子树是子表达式或SQL片段

根结点是最后运算符的操作符

根结点运算后，得到的是SQL查询优化后的结果

这样一棵树就是一个查询的路径

多个关系连接，连接顺序不同，可以得出多个类似的二叉树

查询优化就是找出代价最小的二叉树，即最优的查询路径。

基于代价估算的查询优化就是通过计算和比较，找出花费最少的是优二叉树。

从运算符的角度考虑优化

不同的运算符优化可c减少中间生成物的大小和数量，节约IO和内存CPU等，从而提高执行速度。前提是优化前和优化后是等价的。

选择 —— 基本选择性质

对同一个表的同样选择条件，作一次即可。
可优化的原因：
幂等性：多次应用同一个选择有同样的效果
交换性：应用选择的次序在最终结果中没有影响
选择可有效减少在它的操作数中的元组数的运算（元组数减少）

选择 —— 分解有复杂条件的选择

合取：合并多个选择为更少的需求值的选择，多个等式可以合并。它等价于针对这些单独的一系列选择。
析取：分解它们使得其成员选择可被移动或单独优化。它等价于选择的并集。

选择 —— 和叉积

尽可能选做选择：关系有N和M行，先做积运算将包含N*M行。先做选择运算，减少N和M，则可避免不满足条件的条件参与积的运算，节约时间减少结果的大小。

尽可能下推选择：如果积不跟随着选择运算，可尝试使用其他规则从表达式树更高层下推选择。

选择 —— 和集合运算

选择下推到的集合运算中：选择在差集，交集和并集算子上满足分配律。

选择 —— 和投影

在投影之前进行选择：如果选择条件中引用的字段是投影中的字段的子集，则选择与投影满足交换性。

投影 —— 和基本投影性质

尽可能先做投影：投影是幂等性的；投影可以减少元组大小。

投影 —— 和集合运算。

投影下推到集合运算中：投影在差集，交集和并集运算上满足分配律。

运算规则主导的优化

连接、笛卡儿积 交换律

做连接、做积运算，可交换前后位置，其结果不变。如两表连接算法中嵌套连接算法，对外表和内表有要求，外表尽可能小则有利于做“基于块的嵌套循环连接“，所以，通过交换律可以把元组少的表作为外表。

连接、笛卡儿积 结合律

做连接、做积运算，如果新的结合有利于减少中间关系的大小，则可优先处理。

投影的串接定律

在同一个关系上，只需要做一次投影运算，且一次投影时选择多列同时完成。所以许多数据库优化引擎为同一个关系收集齐本关系上的所有列（目标列和 WHERE， GROUP BY 等子句的本关系的列）

选择的串接定律

选择条件可以合并，使得可一次就检查全部条件，不必多次过滤元组，所以可以把同层的合取条件收集在一起，统一判断。

选择与投影的交换律

（1）先投影后选择，可以改为先选择后投影，这对于以行为存储格式的主流数据库而言，很有优化意义。存储方式总是在先获得元组后才能解析得到其中的列。

（2）先择选后投影，可以改为带有选择条件中列的投影后再选择，最后完成最外层的投影，这样，使得内层的选择和投影可以同时进行。

选择与笛卡儿积的分配律

条件下推到相关的关系上，先做选择后做积运算，这样可以减小中间结果的大小。

选择与并的分配律

条件下推到相关的关系上，先做选择后做并运算，可以减小每个关系输出结果的大小。

选择与差的分配律

条件下推到相关的关系上，先做选择后做差运算，可以减小每个关系输出结果的大小。

投影与笛卡儿积的分配律

先做投影后做积，可减少做积前每个元组的长度，使得再做积后得到新元组的长度变短。

投影与并的分配律

先做投影后做并，可减少做并前每个元组的长度。

OLTP

On-line Transaction Processing, OLTP。

SPJ

SELECT， 投影（PROJECT）， 连接（JOIN）

查询优化对SPJ的优化方式如下：

1）选择操作。对应的是限制条件（格式类似 field<op>consant）优化方式是选择操作下推，目的是尽量减少连接操作前的元组数，使得中间临时关系尽量少。这可减少IO和CPU等的消耗。

2）投影操作：对应SELECT查询的目的的列对象。优化方式是投影操作下推。目的是尽量减少连接操作前的列数，使得中间临时关系尽量小（选择操作是使元组个数”尽量少“，投影操作，是使一条元组”尽量小“）。这样，虽然不能减少IO（多数数据库存储方式是行存储，元组是读取的最基本单位，所以想要操作列必须读取一行数据）。但可以减少连接后的中间关系的元组大小，节约内存。

3）连接操作：对应的是连接条件。（格式为field1<op>field2, field1和field表示”不同表“上的列对象。表示两个表连接条件。（1）”多表连接中每个表被连接的顺序决定着效率。“，即如果ABC三个表，ABC， ACB， CBA， BCA等不同的连接后结果一样的话，则要计算哪种效率最高。（2）多表连接每个表被连接的顺序由用户语义决定，这决定着表之间的前后连接次序是不能随意更换的。

4）非SPJ（在SPJ的基础上存在 GROUP BY 操作的查询，这是一种复杂的查询）。

子查询优化

它是一种比较耗时的操作，优化子查询对查询效率的提升有着直接的影响。

子查询可出现的位置及对优化的影响

1）目标列
这时，只能是标量子查询，否则数据库可能返回类似”错误：子查询必须只能返回一个字段“的提示。

2）FROM子句
相关子查询不能出现在FROM子句中；非相关子查询出现在FROM子句中，可上拉子查询到父层，在多表连接时统一考虑连接代价后择优。

3）WHERE子句

4）JOIN/ON子句
它们处理方式同FROM子句和WHERE子句

5）GROUP BY子句
目标列必须和 GROUP BY 关联。可将子查询写在GROUP BY位置，但没有什么实用意义。

6）HAVING子句

7）ORDER BY 子句

子查询优化技术

1）子查询合并
等价的情况下。多个子查询能够合并成一个子查询。这样可以把多次表扫描，多次连接减少为单次表扫描和单次连接。如：

select * from t1 where a1 < 10 and (exists (select a2 from t2 where t2.a2 < 5 and t2.b2 = 1) or exists (select a2 from t2 where t2.a2 < 5 and t2.b2 = 2)

可优化为

select * from t1 where a1 < 10 and ( exists (select a2 from t2 where t2.a2 < 5 and ( t2.b2 = 1 or t2.b2 = 2));

2）子查询展开
又称子查询反嵌套，又称为子查询上拉。把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列。实质上是把某些子查询重写为等价的多表连接操作。如：

select * from t1, ( select * from t2 where t2.a2 > 10) v_t2 where t1.a1 < 10 and v_t2.a2 < 20

可优化为

select * from t1, t2 where t1.a1 < 10 and t2.a2 < 20 and t2.a2 > 10

3）聚集子查询消除
将聚集函数上推，将子查询转变为一个新的不包含聚集函数的子查询，并与父查询的部分或者全部表做左外连接。

4）其他
利用窗口函数消除子查询的技术。子查询推进等技术

子查询展开

1）如果子查询出现了聚集、GROUP BY， DISTINCT 子句，则子查询只能单独求解，不可以上拉到上层。

2）如果子查询只是一个简单格式（SPJ）的查询语句，则可以上拉到上层，这样往往能提高查询效率。

子查询展开的规则

1）如果上层查询的结果没有重复（即SELECT子句中包含主键），则可以展开其子查询，并且展开后的查询的SELECT 子句前就回上 DISTINCT 标志。

2）如果上层有 DISTINCT 标志，则可以直接展开子查询

3）如果内层查询结果没有重复元组，则可以展开。

子查询展开的步骤

1）将子查询和上层查询的FROM子句连接，为同一个FROM子句，并修改相应的运行参数

2）将子查询的谓词符号进行相应修改。如 IN修改为=ANY

3）将子查询的WHERE条件作为一个整体与上层查询的WHERE条件合并，并用AND条件连接词连接。

子查询优化说明

子查询类似： 10 IN (select ...）这不能做上拉操作，所以不能优化
子查询类似：出现 random()等易失函数，子查询结果不能确定，所以查询优化器就不能对子查询优化。

ALL/SOME/ANY类型

如果子查询没有 GROUP BY 子句，也没有聚集函数。则可以使用如下表达式做等价转换：

val > ALL (select ...) 等价为 val > MAX(select ...)

val < ALL (select ...) 等价为 val < min( select ...)

val > any (select ...) 等价为 val > min(select ....）

val < any (select ...) 等价为 val<max(select ....)

val >= ALL 同上
val <= ALL
val >= ANY
val <= ANY

视图重写

就是将视图的引用重写为对基本表的引用。如：
create table t_a ( a int , b int );
create view v_a as select * from t_a;

基于视图的命令：
select col_a from v_a where col_b > 100;
经过视图重写后：
select col_a from ( select col_a , col_b from t_a) where col_b > 100;
再经过优化后，则是：
select col_a from t_a where col_b > 100;

简单的视图（SPJ）可以被查询优化器较好地处理。
但复杂视图则不能被查询优化器很好地处理。

等价谓词重写

1）LIKE规则
如：name like 'abc%'
重写为
name >= 'abc' and name < 'abd';
应用like规则的好处：转换前针对 like 谓词只能进行全表扫描。如果name列上存在索引，则转换后可以进行索引范围扫描。

如果没有通配符（%或_）。则是与 = 等价
name like 'abc'
重写为
name = 'abc'

2） BETWEEN-AND规则
sno BETWEEN 10 AND 20 
重写为
sno >= 10 and sno <=20
好处：如果sno建立了索引，则可以用索引扫描代替原来的BETWEEN-AND谓词限定的全表扫描，从而提高了查询的效率。

3）IN转换OR规则
IN只是IN操作符，而不是IN子查询。改为OR可以更好地利用索引进行优化。将IN改为若干个OR可能会提高效率。
age IN （8, 12, 21）
重写为
age = 8 or age = 12 or age = 21
效率是否提高，需要看数据库对IN谓词是否只支持全表扫描。如果数据库对IN谓词只支持全表扫描且OR谓词中表的age列存在索引，则转换后的查询效率会更好。

4）IN转换ANY规则
因为IN可以转换为OR，而OR可转换为ANY，所以可以直接把IN转换为ANY。这可能会提高效率。
age IN (8, 12, 21)
重写为
age any (8, 12, 21)
效率是否提高，依赖于数据库对ANY操作的支持情况。
如：PostgreSQL没有显式支持 ANY 操作，但在内部实现时把IN操作转换为了ANY操作。（通过 explain 知道）


5）OR转换为ANY规则
这样可以更好地利用 MIN/MAX 操作进行优化。但（PG9.2.3 和 MySQL 5.6.10 目前都还没有支持）

6）ALL/ANT 转换为集函数规则
这样可以更好地利用 MIN/MAX 操作进行优化。如：
sno > ANY (10, 2*5+3, sqrt(9))
重写为
sno > sqrt(9)
通常，聚集函数MAX(), MIN()等的效率比ANY， ALL谓词的执行效率高。

7）NOT规则
NOT (col_1 != 2) 重写为 col_1 = 2 其他类似
好处：如果 col_1 上建立了索引，则可以用索引扫描代替原来的全表扫描。

8）OR重写并集规则
如：
select * from student where ( sex = 'f' and sno > 15 ) or age > 18；
这条SQL会强迫查询优化器使用顺序存取，因为这个语句要检索的是OR操作的集合。假设，sex, age 上有索引，则可优化为：
select * from student where sex = 'f' and sno > 15 union select * from student where age > 18；

条件简化

1）把HAVING条件并入WHERE条件。（只有SQL语句不存在 GROUP BY 条件 或聚集函数的情况下才可以使用）

2）去除表达式中冗余的括号。这样子可以减少语法分析时产生的AND和OR树的层次。

3）常量传递。如：col_1 = col_2 and col_2 = 3 。改为：col_1 = 3 and col_2 = 3;

4）消除死码。如：永恒为假的条件。

5）表达式计算：如：where col_1 = 1 + 2 ，改为 where col_1 = 3

6）等式变换：化简条件（如反转关系操作符的操作数顺序）。如： -a = 3; 简化为 a = -3；

7）不等式变换。化简条件。如：a > 10 and b = 6 and a > 2 ，简化为 b = 6 and a > 10

8）布尔表达式变换。

9）谓词传递闭包。

10）任何一个布尔表达式都能被转换为一个等价的合取范式（CNF）。如：and 操作符是可交换的，所以优化器可以按先易后难的顺序计算表达式。

11）索引的利用。

外连接消除

外连接的左右子树不能互换。
查询重写的一项技术就是把外连接，转换为内连接。意义：
1）查询优化器在处理外连接操作时所需要的时间多于内连接

2）优化器在选择表连接顺序时，可以有更多更灵活的选择，从而sk以选择更好的表连接顺序。

3）表的一些连接算法，将规模小的或筛选严格的条件的作为外表，可以减少不必要的IO开销，极大地加快算法执行的速度。

嵌套连接消除

嵌套连接是指：当执行连接操作的次序不是从左到右逐个进行时，就说明这样的连接表达式存在嵌套。

1）如果连接表达式只包括内连接（JOIN ON），括号可以去掉，这意味着表之间的次序可以交换。

2）如果连接表达式包括外连接，括号不可以去掉，意味着表之间的次序只能按原语义进行，至多能执行的就是外连接向内连接转换。