目录
三、对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题。
有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个
聚合和分组
在任何类型的ORM中，聚合(aggregation)都是造成混乱的根源，而Django也是如此。该文档提供了各种示例，演示了如何使用Django的ORM对数据进行分组(group)和聚合(aggregation)，但是我决定从另一个角度进行研究。
在本文中，我将QuerySet和SQL并排放置。如果您最喜欢SQL语言，那么这是适合您的Django GROUP BY速查表。
如何在Django中分组
为了演示不同的GROUP BY查询，我将使用Django内置django.contrib.auth应用程序中的模型。
如何计算行数
让我们计算一下我们有多少用户：
对行进行计数非常普遍，以至于Django在QuerySet上就为其包含了一个函数。与其他QuerySet不同，我们接下来将看到它count返回一个数字。
如何使用聚合函数
Django还提供了其它两种方法来对表中的行数。
我们将从从aggregate开始：
为了使用aggregate我们导入了聚合函数Count。在这种情况下，我们使用主键列的名称id来计数表中的所有行。
聚合返回的结果是一个字典，如下所示：
返回字典的默认键名key是id__count。最好不要依赖此命名约定，而是提供自己的名称：
如何分组
使用aggregate我们得到了将聚合函数(比如Count, Max, Sum)应用于整个表后的结果，这很有用，但是通常我们希望将表中的记录分成各个组(group)，然后在对每个组应用聚合函数。
现在让我们根据用户的活动状态分组， 再来统计每个组的人数：
这次我们使用了annotate。为了生产GROUP BY我们使用的组合values和annotate：
-
values('is_active')：根据什么分组 -
annotate(total=Count('id'))：对什么进行聚合
返回结果是is_active的值以及每种状态的人数。
顺序很重要：在调用values方法之前使用annotate不会以注释形式添加聚合函数结果。
如何在分组聚合前过滤查询集
要将聚合函数应用于过滤后的查询集，可以在查询中的任何位置使用filter。例如，仅统计公司职员(staff)的活动状态计数：
如何在使用分组聚合前对查询集进行排序
像过滤器一样，可在查询语句中的任何位置使用order_by对查询集进行排序：
注意：你可以同时对表中本身的字段(is_active)和聚合后生成的字段(total)进行排序。
如何合并多个聚合函数结果
对同一组数据使用多个聚合函数，请一次添加多个注释：
该查询将产生活动和不活动用户的数量，以及每个组中用户加入的最后日期。
如何按多个字段进行分组
就像执行多个聚合函数一样，我们可能还希望按表中多个字段进行分组。例如，按活动状态和人员状态分组：
该查询的结果包括和is_active，is_staff以及每个组中的用户数。
如何按表达式分组
GROUP BY的另一个常见用例是按表达式分组。例如，计算每年加入的用户数：
请注意，要从我们<field>__year在第一次调用时使用特殊表达式的日期开始获取年份values()。查询的结果是一个dict，键的名称为date_joined__year。
有时内置表达式还不够，您需要汇总更复杂的表达式。例如，对注册后已登录过的用户进行分组：
这里的表达相当复杂。我们首先使用annotate来构建表达式，然后通过在以下对的调用中引用该表达式，将其标记为GROUP BY键values()。从这里开始，它是完全一样的。
如何使用条件聚合
使用条件聚合，您只能聚合组的一部分。当您有多个聚合时，条件会派上用场。例如，按签约年份计算职员和编外用户的数量：
上面的SQL语句来自PostgreSQL，它与SQLite一起是当前唯一支持FILTER语法快捷方式（正式称为“选择性聚合”）的数据库后端。对于其他数据库后端，ORM将CASE ... WHEN代替使用。
如何使用Having对聚合结果进行过滤
该HAVING用于过滤聚合函数的结果，例如查找在哪些年份有100个以上的用户注册了：
如何按distinct分组
对于某些聚合函数，比如COUNT，有时希望仅对不同的事件进行计数。例如统计每个用户活动状态有多少个不同的姓氏：
如何使用聚合字段创建表达式
聚合字段通常只是一个更大问题的第一步。例如按用户活动状态的唯一姓氏百分比是多少：
第一个annotate()定义聚合字段。第二种annotate()使用聚合函数构造表达式。
如何通过各种关系进行分组
到目前为止，我们仅在单个模型中使用了数据，但是聚合通常用于跨关系。更简单的方案是一对一或外键关系。例如，假设我们UserProfile与User之间具有一对一的关系，并且我们想按配置文件类型对用户进行计数：
就像GROUP BY表达式一样，在values中使用关系将按该字段分组。请注意，结果中的用户配置文件类型的名称将为“ user_profile__type”。
如何按多对多关系分组
一种更复杂的关系类型是多对多关系。例如计算每个用户是多少个组的成员：
一个用户可以是多个组的成员。为了计算用户所属的组数，我们在User模型中使用了相关的名称“组” 。如果未显式设置相关名称(related_name)，则Django将自动以format生成名称{related model model}_set。例如，group_set。
连接查询
PostgreSQL JOIN实践及原理
最近项目使用了PostgreSQL 简单学习join语法以及原理，以后有时间搞一下SQLite源码。
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
在 PostgreSQL 中，JOIN 有五种连接类型：
CROSS JOIN ：交叉连接
INNER JOIN：内连接
LEFT OUTER JOIN：左外连接
RIGHT OUTER JOIN：右外连接
FULL OUTER JOIN：全外连接
1.数据准备
创建company表和department表 其中company表存储员工基本信息 department表存储部门信息。
company表定义以及初始化数据如下：
DROP TABLE COMPANY;
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
department表定义以及初始化如下：
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
2.连接操作
2.1 交叉连接
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
其相应查询计划如下所示：
2.2 内连接
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
2.3 左外连接
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。
2.4 右外连接
首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。
2.5 外连接
首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
由以上操作可知 大多数连接会使用Hash Join算法来实现。postgreSQL中join算法有三种nested loop join merge join 以及hash join
3.连接原理
3.1 nested loop join
nested loop join: The right relation is scanned once for every row found in the left relation. This strategy is easy to implement but can be very time consuming. (However, if the right relation can be scanned with an index scan, this can be a good strategy. It is possible to use values from the current row of the left relation as keys for the index scan of the right.)
EXPLAIN SELECT * FROM COMPANY JOIN DEPARTMENT ON DEPARTMENT.EMP_ID = COMPANY.ID WHERE company."id" = 1;
1. 表company按照id来过滤得到结果
2. 对于过滤后结果每一行，利用id从department表中进行匹配
3.2 merge join
merge join: Each relation is sorted on the join attributes before the join starts. Then the two relations are scanned in parallel, and matching rows are combined to form join rows. This kind of join is more attractive because each relation has to be scanned only once. The required sorting might be achieved either by an explicit sort step, or by scanning the relation in the proper order using an index on the join key.
set enable_hashjoin=off;
EXPLAIN SELECT * FROM COMPANY JOIN DEPARTMENT ON DEPARTMENT.emp_id = COMPANY.ID;
- 首先company表关联字段id是有序的 直接索引扫描
- Deparpment 首先按照emp_id排序 然后执行merge join
3.3 hash join
hash join: the right relation is first scanned and loaded into a hash table, using its join attributes as hash keys. Next the left relation is scanned and the appropriate values of every row found are used as hash keys to locate the matching rows in the table.
set enable_hashjoin=on;
EXPLAIN SELECT * FROM COMPANY JOIN DEPARTMENT ON DEPARTMENT.emp_id = COMPANY.ID WHERE company."id" = 1;
EXPLAIN SELECT * FROM COMPANY JOIN DEPARTMENT ON DEPARTMENT.emp_id = COMPANY.ID WHERE company.age = 32;
首先顺序扫描department表 构建hash表 key=department.emp_id 即关联字段，
然后顺序扫描company表 用company表Id来匹配hash表key 如果匹配成功 则输出。
hash join和merge join被关联的两个表都只扫描一次, nested loop join则被关联的表其中一个扫描一次, (如果前一个表的扫描结果有多行输出)另一个扫描多次.
HASH JOIN原理
参考一下hash join实现源码：
将主驱动表的关联字段作为key，主驱动表需要的字段作为value来构建hash表。
遍历被驱动表的每一行 计算该行是否与hash表中key相同 如果key相同则将被驱动表相应字段和命中hash表key对应的value一起输出，作为结果中的一行。由于hash表的使用，被驱动表的每一行查找时间复杂度为常数。
for (j = 0; j < length(inner); j++) hash_key = hash(inner[j]); append(hash_store[hash_key], inner[j]); for (i = 0; i < length(outer); i++) hash_key = hash(outer[i]); for (j = 0; j < length(hash_store[hash_key]); j++) if (outer[i] == hash_store[hash_key][j]) output(outer[i], inner[j]);
解释如下：
//利用 inner 表， 来构造 hash 表(放在内存里)
for (j = 0; j < length(inner); j++)
{
hash_key = hash(inner[j]);
append(hash_store[hash_key], inner[j]);
}
//对 outer 表的每一个元素， 进行遍历
for (i = 0; i < length(outer); i++)
{
//拿到 outer 表中的 某个元素， 进行 hash运算， 得到其 hash_key 值
hash_key = hash(outer[i]);
//用上面刚得到的 hash_key值， 来 对 hash 表进行 探测（假定hash表中有此key 值）
//采用 length (hash_store[hash_Key]) 是因为，hash算法构造完hash 表后，有可能出现一个key值处有多个元素的情况。
//对 拥有相同 的 （此处是上面刚运算的，特定的）hash_key 值的各个元素的遍历
for (j = 0; j < length(hash_store[hash_key]); j++)
{
//如果找到了匹配值，则输出一行结果
if (outer[i] == hash_store[hash_key][j])
output(outer[i], inner[j]);
}
}子查询
#1楼
MySQL版本：5.5.28-0ubuntu0.12.04.2-log
我还认为JOIN总是比MySQL中的子查询更好，但EXPLAIN是一种更好的判断方式。 这是一个子查询比JOIN更好的例子。
这是我的3个子查询的查询：
EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date
FROM `vote-ranked-listory` vrl
INNER JOIN lists l ON l.list_id = vrl.list_id
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL
ORDER BY vrl.moved_date DESC LIMIT 200;
EXPLAIN显示：
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 1 | PRIMARY | vrl | index | PRIMARY | moved_date | 8 | NULL | 200 | Using where |
| 1 | PRIMARY | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | PRIMARY | vrlih | eq_ref | PRIMARY | PRIMARY | 9 | ranker.vrl.list_id,ranker.vrl.ontology_id,const | 1 | Using where |
| 1 | PRIMARY | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
与JOIN相同的查询是：
EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date
FROM `vote-ranked-listory` vrl
INNER JOIN lists l ON l.list_id = vrl.list_id
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL
ORDER BY vrl.moved_date DESC LIMIT 200;
输出是：
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | lt3 | ref | list_tag_key,list_id,tag_id | tag_id | 5 | const | 2386 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.lt3.list_id | 1 | Using where |
| 1 | SIMPLE | vrlih | ref | PRIMARY | PRIMARY | 4 | ranker.lt3.list_id | 103 | Using where |
| 1 | SIMPLE | vrl | ref | PRIMARY | PRIMARY | 8 | ranker.lt3.list_id,ranker.vrlih.ontology_id | 65 | Using where |
| 1 | SIMPLE | lt1 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index; Not exists |
| 1 | SIMPLE | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | SIMPLE | lt2 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
rows列的比较告诉了区别，而JOIN的查询是使用Using temporary; Using filesort Using temporary; Using filesort 。
当然，当我运行两个查询时，第一个查询在0.02秒内完成，第二个查询在1分钟后仍未完成，因此EXPLAIN正确解释了这些查询。
如果我在list_tag表上没有INNER JOIN，即如果我删除了
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL
从第一个查询和相应的：
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403
从第二个查询开始，EXPLAIN为两个查询返回相同的行数，这两个查询的运行速度相同。
#2楼
仅当第二个连接表的数据明显多于主表时才会出现差异。 我有过如下经历......
我们有一个十万条目的用户表和他们的会员数据（友谊）约三十万条目。 这是一个加入声明，以便接收朋友和他们的数据，但有很大的延迟。 但是在成员资格表中只有少量数据的情况下工作正常。 一旦我们将其更改为使用子查询，它就可以正常工作。
但同时，连接查询正在使用条目少于主表的其他表。
所以我认为连接和子查询语句工作正常，它取决于数据和情况。
#3楼
子查询是解决形式问题的逻辑上正确的方法，“从A获取事实，以B中的事实为条件”。 在这种情况下，在子查询中粘贴B比进行连接更合乎逻辑。 从实际意义上说，它也更安全，因为你不必因为多次匹配B而从A中获取重复的事实时要谨慎。
然而，实际上，答案通常归结为性能。 一些优化器在给出连接和子查询时会吮吸柠檬，而另一些优化者则以另一种方式吮吸柠檬，这是特定于优化器，特定于DBMS的版本和查询特定的。
从历史上看，显式连接通常会获胜，因此连接的既定智慧更好，但优化器一直在变得越来越好，所以我更喜欢先以逻辑连贯的方式编写查询，然后在性能限制要求时进行重组。
#4楼
子查询通常用于将单个行作为原子值返回，但它们可用于将值与多个行与IN关键字进行比较。 它们几乎可以在SQL语句中的任何有意义的点上使用，包括目标列表，WHERE子句等。 可以使用简单的子查询作为搜索条件。 例如，在一对表之间：
SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');
请注意，对子查询的结果使用常规值运算符要求只返回一个字段。 如果您对检查一组其他值中是否存在单个值感兴趣，请使用IN：
SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');
这显然不同于LEFT-JOIN，你只想加入表A和B中的东西，即使连接条件没有在表B中找到任何匹配的记录，等等。
如果您只是担心速度，则必须检查数据库并编写一个好的查询，看看性能是否有任何显着差异。
#5楼
在大多数情况下， JOIN比子查询更快，并且子查询的速度非常快。
在JOIN RDBMS可以创建一个更适合您的查询的执行计划，并且可以预测应该加载哪些数据以进行处理并节省时间，这与子查询不同，在子查询中它将运行所有查询并加载所有数据以执行处理。
子查询的好处是它们比JOIN更具可读性：这就是大多数新SQL用户更喜欢它们的原因; 这是简单的方法; 但是在性能方面，JOINS在大多数情况下都更好，即使它们也不难读。
#6楼
使用EXPLAIN查看数据库如何对数据执行查询。 在这个答案中有一个巨大的“取决于”......
当PostgreSQL认为一个子查询比另一个更快时，它可以将子查询重写为连接或子查询的连接。 这一切都取决于数据，索引，相关性，数据量，查询等。
#7楼
首先，要比较两者，首先应将查询与子查询区分开来：
- 一类子查询，始终具有使用连接编写的相应等效查询
- 一类无法使用连接重写的子查询
对于第一类查询，良好的RDBMS将联接和子查询视为等效，并将生成相同的查询计划。
这些天甚至mysql都这样做。
尽管如此，有时却没有，但这并不意味着连接总是会赢 - 我在mysql中使用子查询提高了性能。 （例如，如果存在阻止mysql规划器正确估计成本的事情，并且规划器没有看到连接变量和子查询变量相同，那么子查询可以通过强制某个路径来胜过连接）。
结论是，如果要确定哪个更好，那么您应该测试连接和子查询变体的查询。
对于第二个类 ，比较没有意义，因为这些查询不能使用连接重写，在这些情况下，子查询是执行所需任务的自然方式，您不应该区别它们。
#8楼
子查询具有即时计算聚合函数的能力。 例如，查找该书的最低价格，并获得以此价格出售的所有书籍。 1）使用子查询：
SELECT titles, price
FROM Books, Orders
WHERE price =
(SELECT MIN(price)
FROM Orders) AND (Books.ID=Orders.ID);
2）使用JOIN
SELECT MIN(price)
FROM Orders;
-----------------
2.99
SELECT titles, price
FROM Books b
INNER JOIN Orders o
ON b.ID = o.ID
WHERE o.price = 2.99;
#9楼
摘自MySQL手册 （ 13.2.10.11重写子查询作为连接 ）：
LEFT [OUTER] JOIN可以比等效的子查询更快，因为服务器可能能够更好地优化它 - 这一事实并非仅针对MySQL Server。
所以子查询可能比LEFT [OUTER] JOIN慢，但在我看来，它们的强度略高于可读性。
#10楼
我认为引用答案中未充分强调的是特定（使用）案例可能产生的重复和问题结果的问题。
（虽然Marcelo Cantos确实提到过）
我将引用斯坦福大学关于SQL的Lagunita课程的例子。
学生表
+------+--------+------+--------+
| sID | sName | GPA | sizeHS |
+------+--------+------+--------+
| 123 | Amy | 3.9 | 1000 |
| 234 | Bob | 3.6 | 1500 |
| 345 | Craig | 3.5 | 500 |
| 456 | Doris | 3.9 | 1000 |
| 567 | Edward | 2.9 | 2000 |
| 678 | Fay | 3.8 | 200 |
| 789 | Gary | 3.4 | 800 |
| 987 | Helen | 3.7 | 800 |
| 876 | Irene | 3.9 | 400 |
| 765 | Jay | 2.9 | 1500 |
| 654 | Amy | 3.9 | 1000 |
| 543 | Craig | 3.4 | 2000 |
+------+--------+------+--------+
申请表
（向特定大学和专业提出的申请）
+------+----------+----------------+----------+
| sID | cName | major | decision |
+------+----------+----------------+----------+
| 123 | Stanford | CS | Y |
| 123 | Stanford | EE | N |
| 123 | Berkeley | CS | Y |
| 123 | Cornell | EE | Y |
| 234 | Berkeley | biology | N |
| 345 | MIT | bioengineering | Y |
| 345 | Cornell | bioengineering | N |
| 345 | Cornell | CS | Y |
| 345 | Cornell | EE | N |
| 678 | Stanford | history | Y |
| 987 | Stanford | CS | Y |
| 987 | Berkeley | CS | Y |
| 876 | Stanford | CS | N |
| 876 | MIT | biology | Y |
| 876 | MIT | marine biology | N |
| 765 | Stanford | history | Y |
| 765 | Cornell | history | N |
| 765 | Cornell | psychology | Y |
| 543 | MIT | CS | N |
+------+----------+----------------+----------+
让我们试着找一些申请CS专业（不论大学）的学生的GPA分数
使用子查询：
select GPA from Student where sID in (select sID from Apply where major = 'CS');
+------+
| GPA |
+------+
| 3.9 |
| 3.5 |
| 3.7 |
| 3.9 |
| 3.4 |
+------+
此结果集的平均值为：
select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');
+--------------------+
| avg(GPA) |
+--------------------+
| 3.6800000000000006 |
+--------------------+
使用连接：
select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+------+
| GPA |
+------+
| 3.9 |
| 3.9 |
| 3.5 |
| 3.7 |
| 3.7 |
| 3.9 |
| 3.4 |
+------+
此结果集的平均值：
select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+-------------------+
| avg(GPA) |
+-------------------+
| 3.714285714285714 |
+-------------------+
很明显，第二次尝试在我们的用例中产生了误导性的结果，因为它计算重复计算平均值。 同样显而易见的是，使用基于连接的语句的distinct将不会消除该问题，因为它将错误地保留3.9分中的三分之一。 正确的情况是考虑到我们实际上有两（2）名学生符合我们的查询标准，得出3.9分的两（2）次出现。
在某些情况下，除了任何性能问题之外，在某些情况下，子查询是最安全的方式。
#11楼
根据我的观察，如两个案例，如果一个表有少于100,000个记录，那么连接将快速工作。
但是如果一个表有超过100,000个记录，那么子查询是最好的结果。
我有一个表，我在下面的查询中创建了500,000条记录，其结果时间就像
SELECT *
FROM crv.workorder_details wd
inner join crv.workorder wr on wr.workorder_id = wd.workorder_id;
结果：13.3秒
select *
from crv.workorder_details
where workorder_id in (select workorder_id from crv.workorder)
结果：1.65秒
#12楼
在2010年，我会加入这些问题的作者，并会强烈投票支持JOIN 。 但是有了更多的经验（特别是在MySQL中）我可以说：是的子查询可以更好。 我在这里读过多个答案。 有人说，子查询更快，但缺乏一个很好的解释。 我希望我能提供这个（非常）迟到的答案：
首先，让我说最重要的是： 有不同形式的子查询
第二个重要声明： 规模问题
如果您使用子查询，您应该知道 ，DB-Server如何执行子查询。 特别是如果子查询被评估一次或每行！ 另一方面，现代DB-Server能够进行大量优化。 在某些情况下，子查询有助于优化查询，但较新版本的DB-Server可能会使优化过时。
选择字段中的子查询
SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo
请注意，对foo每个结果行执行子查询。 尽可能避免这种情况，它可能会大大减慢对大型数据集的查询速度。 但是如果子查询没有引用foo ，它可以由DB服务器优化为静态内容，并且只能被评估一次。
Where语句中的子查询
SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)
如果幸运的话，DB会在内部将其优化为JOIN 。 如果没有，您的查询将在巨大的数据集上变得非常非常慢，因为它将为foo每一行执行子查询，而不仅仅是select-type中的结果。
Join语句中的子查询
SELECT moo, bar
FROM foo
LEFT JOIN (
SELECT MIN(bar), me FROM wilco GROUP BY me
) ON moo = me
这是有趣的。 我们将JOIN与子查询结合起来。 在这里，我们得到了子查询的真正优势。 想象一下在wilco有数百万行的数据集，但只有少数几个不同的me 。 我们现在有一个较小的临时表来加入，而不是加入一个巨大的表。 这可以导致更快的查询，具体取决于数据库大小。 您可以使用CREATE TEMPORARY TABLE ...和INSERT INTO ... SELECT ...获得相同的效果，这可以在非常复杂的查询上提供更好的可读性（但可以将数据集锁定在可重复的读隔离级别）。
嵌套的子查询
SELECT moo, bar
FROM (
SELECT moo, CONCAT(roger, wilco) AS bar
FROM foo
GROUP BY moo
HAVING bar LIKE 'SpaceQ%'
) AS temp_foo
GROUP BY bar
ORDER BY bar
您可以在多个级别中嵌套子查询。 如果您必须对结果进行分组或排序，这可以对大型数据集有所帮助。 通常，DB-Server为此创建一个临时表，但有时您不需要对整个表进行排序，只需对结果集进行排序。 这可能会提供更好的性能，具体取决于表的大小。
结论
子查询不能替代JOIN ，您不应该像这样使用它们（尽管可能）。 在我看来，正确使用子查询是用来快速替换CREATE TEMPORARY TABLE ... 一个好的子查询以某种方式减少数据集，您无法在JOIN的ON语句中完成。 如果子查询具有关键字GROUP BY或DISTINCT ，并且最好不位于select字段或where语句中，那么它可能会大大提高性能。
#13楼
我只是考虑同样的问题，但我在FROM部分使用子查询。 我需要从大表连接和查询，“slave”表有2800万条记录，但结果只有128个这么小的结果大数据！ 我正在使用MAX（）函数。
首先我使用LEFT JOIN，因为我认为这是正确的方法，mysql可以优化等。第二次只是为了测试，我重写为JOIN的子选择。
LEFT JOIN运行时：1.12s SUB-SELECT运行时：0.06s
subselect比连接快18倍！ 就在chokito adv。 该子选择看起来很糟糕，但结果......
#14楼
- 一般规则是在大多数情况下连接速度更快（99％）。
- 数据表越多， 子查询就越慢。
- 数据表越少， 子查询的速度与连接速度相同。
- 子查询更简单，更易于理解，更易于阅读。
- 大多数Web和应用程序框架及其“ORM”和“活动记录”都会使用子查询生成查询，因为子查询更容易分担责任，维护代码等。
- 对于较小的网站或应用程序子查询是可以的，但对于较大的网站和应用程序，您通常必须重写生成的查询以加入查询，特别是如果查询在查询中使用了许多子查询。
有些人说“有些RDBMS可以将子查询重写为连接，或者当它认为一个子查询比另一个更快时可以连接到子查询 。”但是这个语句适用于简单的情况，当然不适用于带有子查询的复杂查询，这实际上导致了表现上的问题。
#15楼
如果您想使用join加速查询：
对于“内部连接/连接”，不要使用where条件而是在“ON”条件下使用它。 例如：
select id,name from table1 a
join table2 b on a.name=b.name
where id='123'
Try,
select id,name from table1 a
join table2 b on a.name=b.name and a.id='123'
对于“左/右连接”，不要在“开”状态下使用，因为如果使用左/右连接，它将获得任何一个表的所有行。因此，不要在“开”中使用它。 所以，尝试使用“Where”条件
#16楼
在旧的Mambo CMS上运行一个非常大的数据库：
SELECT id, alias
FROM
mos_categories
WHERE
id IN (
SELECT
DISTINCT catid
FROM mos_content
);
0秒
SELECT
DISTINCT mos_content.catid,
mos_categories.alias
FROM
mos_content, mos_categories
WHERE
mos_content.catid = mos_categories.id;
~3秒
EXPLAIN显示它们检查完全相同的行数，但一个需要3秒，一个接近瞬间。 故事的道德启示？ 如果性能很重要（何时不是？），请尝试多种方式，看看哪一个最快。
和...
SELECT
DISTINCT mos_categories.id,
mos_categories.alias
FROM
mos_content, mos_categories
WHERE
mos_content.catid = mos_categories.id;
0秒
同样，相同的结果，检查的行数相同。 我的猜测是，DISTINCT mos_content.catid需要比DISTINCT mos_categories.id更长的时间来计算。
#17楼
包含子查询的许多Transact-SQL语句也可以表示为连接。 其他问题只能通过子查询提出。 在Transact-SQL中，包含子查询的语句与不包含子查询的语义等效版本之间通常没有性能差异。 但是，在某些必须检查存在的情况下，连接会产生更好的性能。 否则，必须为外部查询的每个结果处理嵌套查询，以确保消除重复项。 在这种情况下，联接方法会产生更好的结果。
所以，如果你需要类似的东西
select * from t1 where exists select * from t2 where t2.parent=t1.id
尝试使用连接。 在其他情况下，它没有任何区别。
我说：为子查询创建函数消除了cluttter的问题，并允许您为子查询实现额外的逻辑。 所以我建议尽可能为子查询创建函数。
代码杂乱是一个大问题，业界几十年来一直在努力避免它。
分页查询
自己的一个网站，由于单表的数据记录高达了一百万条，造成数据访问很慢，Google分析的后台经常报告超时，尤其是页码大的页面更是慢的不行。
测试环境：
先让我们熟悉下基本的sql语句，来查看下我们将要测试表的基本信息
use infomation_schema
SELECT * FROM TABLES WHERE TABLE_SCHEMA = ‘dbname’ AND TABLE_NAME = ‘product’
查询结果：
从上图中我们可以看到表的基本信息：
表行数：866633
平均每行的数据长度：5133字节
单表大小：4448700632字节
关于行和表大小的单位都是字节，我们经过计算可以知道
平均行长度：大约5k
单表总大小：4.1g
表中字段各种类型都有varchar、datetime、text等，id字段为主键
测试实验
1. 直接用limit start, count分页语句， 也是我程序中用的方法：
select * from product limit start, count
当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条）， 如下：
select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒
我们已经看出随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为40w看下（也就是记录的一般左右） select * from product limit 400000, 20 3.229秒
再看我们取最后一页记录的时间
select * from product limit 866613, 20 37.44秒
难怪搜索引擎抓取我们页面的时候经常会报超时，像这种分页最大的页码页显然这种时
间是无法忍受的。
从中我们也能总结出两件事情：
1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。
2. 对limit分页问题的性能优化方法
利用表的覆盖索引来加速分页查询
我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。
因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。
在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：
这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒，提升了大概100多倍的速度
那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒，简直是一个质的飞跃啊，哈哈
另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
查询时间也很短，赞！
其实两者用的都是一个原理嘛，所以效果也差不多
一、limit用法
在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。
SELECT * FROM table LIMIT [offset,] rows | `rows OFFSET offset `
(LIMIT offset, `length`)
SELECT
*
FROM table
where condition1 = 0
and condition2 = 0
and condition3 = -1
and condition4 = -1
order by id asc
LIMIT 2000 OFFSET 50000
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目： mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。
二、Mysql的分页查询语句的性能分析
MySql分页sql语句，如果和MSSQL的TOP语法相比，那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。
最基本的分页方式：
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引：
举例来说，如果实际SQL类似下面语句，那么在category_id, id两列上建立复合索引比较好：
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
子查询的分页方式：
随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。
此时，我们可以通过子查询的方式来提高分页效率，大致如下：
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
JOIN分页方式
SELECT * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。
explain SQL语句：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index
为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。
实际可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。
三、对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题。
查询从第1000000之后的30条记录：
SQL代码1：平均用时6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30
SQL代码2：平均用时0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30
因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。对limit的优化，不是直接使用limit，而是首先获取到offset的id，然后直接使用limit size来获取数据。
可以看出，越往后分页，LIMIT语句的偏移量就会越大，两者速度差距也会越明显。
实际应用中，可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。
优化思想：避免数据量大时扫描过多的记录
为了保证index索引列连续，可以为每个表加一个自增字段，并且加上索引
总结：
Mysql的分页查询十分简单，但是当数据量大的时候一般的分页就吃不消了。
传统分页查询：SELECT c1,c2,cn… FROM table LIMIT n,m
MySQL的limit工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。
推荐分页查询方法：
1、尽量给出查询的大致范围
- SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;
2、子查询法
- SELECT c1,c2,cn... FROM table WHERE id>=
- (
- SELECT id FROM table LIMIT 20000,1
- )
- LIMIT 10;
3、高性能MySQL一书中提到的只读索引方法
优化前SQL:
- SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5
优化后SQL:
- SELECT c1, c2, cn .. .
- FROM member
- INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
- USING (member_id)
分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。
4、第一步用用程序读取出ID，然后再用IN方法读取所需记录
程序读ID：
- SELECT id FROM table LIMIT 20000, 10;
- SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)
最后示例：
select * from t_students limit100000,20
select * from t_students where id>=(select id from t_students order by id limit 100000,1) limit 20 --推荐 ，利用索引
select t.* from t_students t join (select id from t_students order by id limit 100000,20) a on t.id=a.id --推荐 ，利用索引
CTE
创建测试表
CREATE TABLE test_order ( client_id INT NOT NULL, order_date TIMESTAMP NOT NULL, filler TEXT NOT NULL );
插入测试数据
INSERT INTO test_order
SELECT s1.id,
(CURRENT_DATE - INTERVAL '1000 days')::DATE
+ generate_series(1, s1.id%1000),
repeat(' ', 20)
FROM generate_series(1, 10000) s1 (id);
CREATE INDEX idx_test_order_client_id_order_date ON test_order (client_id, order_date DESC);
执行普通SQL
不走索引
EXPLAIN ANALYZE SELECT client_id, max(order_date) FROM test_order GROUP BY client_id;
"Execution time: 5741.682 ms"
使用索引
EXPLAIN ANALYZE SELECT DISTINCT ON (client_id) client_id, order_date FROM test_order ORDER BY client_id, order_date DESC;
"Execution time: 4628.510 ms"
优化后SQL
EXPLAIN ANALYZE WITH RECURSIVE skip AS ( (SELECT client_id, order_date FROM test_order ORDER BY client_id, order_date DESC LIMIT 1) UNION ALL (SELECT (SELECT min(client_id) FROM test_order WHERE client_id > skip.client_id ) AS client_id, (SELECT max(order_date) FROM test_order WHERE client_id = ( SELECT min(client_id) FROM test_order WHERE client_id > skip.client_id ) ) AS order_date FROM skip WHERE skip.client_id IS NOT NULL) ) SELECT * FROM skip;
"Execution time: 865.889 ms"
查询结果
client_id; order_date 1;"2014-03-09 00:00:00" 2;"2014-03-10 00:00:00" 3;"2014-03-11 00:00:00" 4;"2014-03-12 00:00:00" 5;"2014-03-13 00:00:00" 6;"2014-03-14 00:00:00" 7;"2014-03-15 00:00:00" 8;"2014-03-16 00:00:00" 9;"2014-03-17 00:00:00" 10;"2014-03-18 00:00:00" 11;"2014-03-19 00:00:00" 12;"2014-03-20 00:00:00" 13;"2014-03-21 00:00:00" 14;"2014-03-22 00:00:00" 15;"2014-03-23 00:00:00" 16;"2014-03-24 00:00:00" 17;"2014-03-25 00:00:00" 18;"2014-03-26 00:00:00" 19;"2014-03-27 00:00:00" 20;"2014-03-28 00:00:00" 21;"2014-03-29 00:00:00" 22;"2014-03-30 00:00:00" 23;"2014-03-31 00:00:00" 24;"2014-04-01 00:00:00"
有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个
（比如说巡逻车辆ID，环卫车辆的ID，公交车，微公交的ID）。
另外有一张日志表B，每条记录中的ID是来自前面那张小表的，但不是每个ID都出现在这张日志表中，比如说一天可能只有几十个ID会出现在这个日志表的当天的数据中。
（比如车辆的行车轨迹数据，每秒上报轨迹，数据量就非常庞大）。
那么我怎么快速的找出今天没有出现的ID呢。
（哪些巡逻车辆没有出现在这个片区，是不是偷懒了？哪些环卫车辆没有出行，哪些公交或微公交没有出行）？
select id from A where id not in (select id from B where time between ? and ?);
这个QUERY会很慢，有什么优化方法呢。
当然，你还可以让车辆签到的方式来解决这个问题，但是总有未签到的，或者没有这种设计的时候，那么怎么解决呢？
-- A create table a(id int primary key, info text); -- B create table b(id int primary key, aid int, crt_time timestamp); create index b_aid on b(aid); -- a表插入1000条 insert into a select generate_series(1,1000), md5(random()::text); -- b表插入500万条，只包含aid的500个id。 insert into b select generate_series(1,5000000), generate_series(1,500), clock_timestamp(); 优化前： select * from a where id not in (select aid from b); 执行时间：大于1min 优化后： select * from a where id not in (with recursive skip as ( ( select min(aid) aid from b where aid is not null ) union all ( select (select min(aid) aid from b where b.aid > s.aid and b.aid is not null) from skip s where s.aid is not null ) -- 这里的where s.aid is not null 一定要加,否则就死循环了. ) select aid from skip where aid is not null); 执行时间：46 msec
生成树形结构
create table subregions (
id smallint primary key,
name text not null,
parent_id smallint null references subregions(id)
);
insert into subregions values
(1,'World',null),
(2,'Africa',1),
(5,'South America',419),
(9,'Oceania',1),
(11,'Western Africa',2),
(13,'Central America',419),
(14,'Eastern Africa',2),
(15,'Northern Africa',2),
(17,'Middle Africa',2),
(18,'Southern Africa',2),
(19,'Americas',1),
(21,'Northern America',19),
(29,'Caribbean',419),
(30,'Eastern Asia',142),
(34,'Southern Asia',142),
(35,'South-Eastern Asia',142),
(39,'Southern Europe',150),
(53,'Australia and New Zealand',9),
(54,'Melanesia',9),
(57,'Micronesia',9),
(61,'Polynesia',9),
(142,'Asia',1),
(143,'Central Asia',142),
(145,'Western Asia',142),
(150,'Europe',1),
(151,'Eastern Europe',150),
(154,'Northern Europe',150),
(155,'Western Europe',150),
(419,'Latin America and the Caribbean',19);
And you wanted to make a pretty tree like this:
World
Africa
Eastern Africa
Middle Africa
Northern Africa
Southern Africa
Western Africa
Americas
Latin America and the Caribbean
Caribbean
Central America
South America
Northern America
Asia
Central Asia
Eastern Asia
South-Eastern Asia
Southern Asia
Western Asia
Europe
Eastern Europe
Northern Europe
Southern Europe
Western Europe
Oceania
Australia and New Zealand
Melanesia
Micronesia
Polynesia
Here's how you'd do it:
with recursive my_expression as (
--start with the "anchor", i.e. all of the nodes whose parent_id is null:
select
id,
name as path,
name as tree,
0 as level
from subregions
where
parent_id is null
union all
--then the recursive part:
select
current.id as id,
previous.path || ' > ' || current.name as path,
repeat(' ', previous.level + 1) || current.name as tree,
previous.level + 1 as level
from subregions current
join my_expression as previous on current.parent_id = previous.id
)
select
tree
from my_expression
order by
path
路径间加入父节点和分割
select
path
from my_expression
order by
path
输出结果：
World
World > Africa
World > Africa > Eastern Africa
World > Africa > Middle Africa
World > Africa > Northern Africa
World > Africa > Southern Africa
World > Africa > Western Africa
World > Americas
World > Americas > Latin America and the Caribbean
World > Americas > Latin America and the Caribbean > Caribbean
World > Americas > Latin America and the Caribbean > Central America
World > Americas > Latin America and the Caribbean > South America
World > Americas > Northern America
World > Asia
World > Asia > Central Asia
World > Asia > Eastern Asia
World > Asia > South-Eastern Asia
World > Asia > Southern Asia
World > Asia > Western Asia
World > Europe
World > Europe > Eastern Europe
World > Europe > Northern Europe
World > Europe > Southern Europe
World > Europe > Western Europe
World > Oceania
World > Oceania > Australia and New Zealand
World > Oceania > Melanesia
World > Oceania > Micronesia
World > Oceania > Polynesia
过程化编程
PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间，然而缺点是某些表达式或SQL命令中的错误只有在其被执行到的时候才能发现。
由于PL/pgSQL在函数里为一个命令制定了执行计划，那么在本次会话中该计划将会被反复使用，这样做往往可以得到更好的性能，但是如果你动态修改了相关的数据库对象，那么就有可能产生问题，如：
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
-- 声明段
BEGIN
PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
在调用以上函数时，PERFORM语句的执行计划将引用my_function对象的OID。在此之后，如果你重建了my_function函数，那么populate函数将无法再找到原有my_function函数的OID。要解决该问题，可以选择重建populate函数，或者重新登录建立新的会话，以使PostgreSQL重新编译该函数。要想规避此类问题的发生，在重建my_function时可以使用CREATE OR REPLACE FUNCTION命令。
鉴于以上规则，在PL/pgSQL里直接出现的SQL命令必须在每次执行时均引用相同的表和字段，换句话说，不能将函数的参数用作SQL命令的表名或字段名。如果想绕开该限制，可以考虑使用PL/pgSQL中的EXECUTE语句动态地构造命令，由此换来的代价是每次执行时都要构造一个新的命令计划。
使用PL/pgSQL函数的一个非常重要的优势是可以提高程序的执行效率，由于原有的SQL调用不得不在客户端与服务器之间反复传递数据，这样不仅增加了进程间通讯所产生的开销，而且也会大大增加网络IO的开销。
PL/pgSQL的结构：
PL/pgSQL是一种块结构语言，函数定义的所有文本都必须在一个块内，其中块中的每个声明和每条语句都是以分号结束，如果某一子块在另外一个块内，那么该子块的END关键字后面必须以分号结束，不过对于函数体的最后一个END关键字，分号可以省略，如：
[ <<label>> ]
[ DECLARE declarations ]
BEGIN
statements
END [ label ];
在PL/pgSQL中有两种注释类型，双破折号 (--)表示单行注释。 /* */表示多行注释，该注释类型的规则等同于C语言中的多行注释。
在语句块前面的声明段中定义的变量在每次进入语句块(BEGIN)时都会将声明的变量初始化为它们的缺省值，而不是每次函数调用时初始化一次。如：
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是30
quantity := 50;
--
-- 创建一个子块
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是80
END;
RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
#执行该函数以进一步观察其执行的结果。
postgres=# select somefunc();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Quantity here is 50
somefunc
----------
50
(1 row)
最后需要说明的是，目前版本的PostgreSQL并不支持嵌套事务，函数中的事物总是由外层命令(函数的调用者)来控制的，它们本身无法开始或提交事务。
声明：
所有在块里使用的变量都必须在块的声明段里先进行声明，唯一的例外是FOR循环里的循环计数变量，该变量被自动声明为整型。变量声明的语法如下：
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
1). SQL中的数据类型均可作为PL/pgSQL变量的数据类型，如integer、varchar和char等。
2). 如果给出了DEFAULT子句，该变量在进入BEGIN块时将被初始化为该缺省值，否则被初始化为SQL空值。缺省值是在每次进入该块时进行计算的。因此，如果把now()赋予一个类型为timestamp的变量，那么该变量的缺省值将为函数实际调用时的时间，而不是函数预编译时的时间。
3). CONSTANT选项是为了避免该变量在进入BEGIN块后被重新赋值，以保证该变量为常量。
4). 如果声明了NOT NULL，那么赋予NULL数值给该变量将导致一个运行时错误。因此所有声明为NOT NULL的变量也必须在声明时定义一个非空的缺省值。
1. 函数参数的别名：
传递给函数的参数都是用$1、$2这样的标识符来表示的。为了增加可读性，我们可以为其声明别名。之后别名和数字标识符均可指向该参数值，见如下示例：
1). 在函数声明的同时给出参数变量名。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
2). 在声明段中为参数变量定义别名。
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
3). 对于输出参数而言，我们仍然可以遵守1)和2)中的规则。
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
4). 如果PL/pgSQL函数的返回类型为多态类型(anyelement或anyarray)，那么函数就会创建一个特殊的参数：$0。我们仍然可以为该变量设置别名。
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
2. 拷贝类型：
见如下形式的变量声明：
variable%TYPE
%TYPE表示一个变量或表字段的数据类型，PL/pgSQL允许通过该方式声明一个变量，其类型等同于variable或表字段的数据类型，见如下示例：
user_id users.user_id%TYPE;
在上面的例子中，变量user_id的数据类型等同于users表中user_id字段的类型。
通过使用%TYPE，一旦引用的变量类型今后发生改变，我们也无需修改该变量的类型声明。最后需要说明的是，我们可以在函数的参数和返回值中使用该方式的类型声明。
3. 行类型：
见如下形式的变量声明：
name table_name%ROWTYPE;
name composite_type_name;
table_name%ROWTYPE表示指定表的行类型，我们在创建一个表的时候，PostgreSQL也会随之创建出一个与之相应的复合类型，该类型名等同于表名，因此，我们可以通过以上两种方式来声明行类型的变量。由此方式声明的变量，可以保存SELECT返回结果中的一行。如果要访问变量中的某个域字段，可以使用点表示法，如rowvar.field，但是行类型的变量只能访问自定义字段，无法访问系统提供的隐含字段，如OID等。对于函数的参数，我们只能使用复合类型标识变量的数据类型。最后需要说明的是，推荐使用%ROWTYPE的声明方式，这样可以具有更好的可移植性，因为在 Oracle的PL/SQL中也存在相同的概念，其声明方式也为%ROWTYPE。见如下示例：
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
4. 记录类型：
见如下形式的变量声明：
name RECORD;
记录变量类似于行类型变量，但是它们没有预定义的结构，只能通过SELECT或FOR命令来获取实际的行结构，因此记录变量在被初始化之前无法访问，否则将引发运行时错误。
注：RECORD不是真正的数据类型，只是一个占位符。
基本语句：
1. 赋值：
PL/pgSQL中赋值语句的形式为： identIFier := expression，等号两端的变量和表达式的类型或者一致，或者可以通过PostgreSQL的转换规则进行转换，否则将会导致运行时错误，见如下示例：
user_id := 20;
tax := subtotal * 0.06;
2. SELECT INTO：
通过该语句可以为记录变量或行类型变量进行赋值，其表现形式为： SELECT INTO target select_expressions FROM ...，该赋值方式一次只能赋值一个变量。表达式中的target可以表示为是一个记录变量、行变量，或者是一组用逗号分隔的简单变量和记录/行字段的列表。select_expressions以及剩余部分和普通SQL一样。
如果将一行或者一个变量列表用做目标，那么选出的数值必需精确匹配目标的结构，否则就会产生运行时错误。如果目标是一个记录变量，那么它自动将自己构造成命令结果列的行类型。如果命令返回零行，目标被赋予空值。如果命令返回多行，那么将只有第一行被赋予目标，其它行将被忽略。在执行SELECT INTO语句之后，可以通过检查内置变量FOUND来判断本次赋值是否成功，如：
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
要测试一个记录/行结果是否为空，可以使用IS NULL条件进行判断，但是对于返回多条记录的情况则无法判断，如：
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id = 3;
IF users_rec.homepage IS NULL THEN
RETURN 'http://';
END IF;
END;
3. 执行一个没有结果的表达式或者命令：
在调用一个表达式或执行一个命令时，如果对其返回的结果不感兴趣，可以考虑使用PERFORM语句： PERFORM query，该语句将执行PERFORM之后的命令并忽略其返回的结果。其中query的写法和普通的SQL SELECT命令是一样的，只是把开头的关键字SELECT替换成PERFORM，如：
PERFORM create_mv('cs_session_page_requests_mv', my_query);
4. 执行动态命令：
如果在PL/pgSQL函数中操作的表或数据类型在每次调用该函数时都可能会发生变化，在这样的情况下，可以考虑使用PL/pgSQL提供的EXECUTE语句： EXECUTE command-string [ INTO target ]，其中command-string是用一段文本表示的表达式，它包含要执行的命令。而target是一个记录变量、行变量或者一组用逗号分隔的简单变量和记录/行域的列表。这里需要特别注意的是，该命令字符串将不会发生任何PL/pgSQL变量代换，变量的数值必需在构造命令字符串时插入到该字符串中。
和所有其它PL/pgSQL命令不同的是，一个由EXECUTE语句运行的命令在服务器内并不会只prepare和保存一次。相反，该语句在每次运行的时候，命令都会prepare一次。因此命令字符串可以在函数里动态的生成以便于对各种不同的表和字段进行操作，从而提高函数的灵活性。然而由此换来的却是性能上的折损。见如下示例：
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
控制结构：
1. 函数返回：
1). RETURN expression
该表达式用于终止当前的函数，然后再将expression的值返回给调用者。如果返回简单类型，那么可以使用任何表达式，同时表达式的类型也将被自动转换成函数的返回类型，就像我们在赋值中描述的那样。如果要返回一个复合类型的数值，则必须让表达式返回记录或者匹配的行变量。
2). RETURN NEXT expression
如果PL/pgSQL函数声明为返回SETOF sometype，其行记录是通过RETURN NEXT命令进行填充的，直到执行到不带参数的RETURN时才表示该函数结束。因此对于RETURN NEXT而言，它实际上并不从函数中返回，只是简单地把表达式的值保存起来，然后继续执行PL/pgSQL函数里的下一条语句。随着RETURN NEXT命令的迭代执行，结果集最终被建立起来。该类函数的调用方式如下：
SELECT * FROM some_func();
它被放在FROM子句中作为数据源使用。最后需要指出的是，如果结果集数量很大，那么通过该种方式来构建结果集将会导致极大的性能损失。
2. 条件：
在PL/pgSQL中有以下三种形式的条件语句。
1). IF-THEN
IF boolean-expression THEN
statements
END IF ;
2). IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
3). IF-THEN-ELSIF-ELSE
IF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSE
statements
END IF;
关于条件语句，这里就不在做过多的赘述了。
3. 循环：
1). LOOP
LOOP
statements
END LOOP [ label ];
LOOP定义一个无条件的循环，直到由EXIT或者RETURN语句终止。可选的label可以由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环。
2). EXIT
EXIT [ label ] [ WHEN expression ];
如果没有给出label，就退出最内层的循环，然后执行跟在END LOOP后面的语句。如果给出label，它必须是当前或更高层的嵌套循环块或语句块的标签。之后该命名块或循环就会终止，而控制则直接转到对应循环/块的END语句后面的语句上。
如果声明了WHEN，EXIT命令只有在expression为真时才被执行，否则将直接执行EXIT后面的语句。见如下示例：
LOOP
-- do something
EXIT WHEN count > 0;
END LOOP;
3). CONTINUE
CONTINUE [ label ] [ WHEN expression ];
如果没有给出label，CONTINUE就会跳到最内层循环的开始处，重新进行判断，以决定是否继续执行循环内的语句。如果指定label，则跳到该label所在的循环开始处。如果声明了WHEN，CONTINUE命令只有在expression为真时才被执行，否则将直接执行CONTINUE后面的语句。见如下示例：
LOOP
-- do something
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
END LOOP;
4). WHILE
[ <<label>> ]
WHILE expression LOOP
statements
END LOOP [ label ];
只要条件表达式为真，其块内的语句就会被循环执行。条件是在每次进入循环体时进行判断的。见如下示例：
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
--do something
END LOOP;
5). FOR
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP [ label ];
变量name自动被定义为integer类型，其作用域仅为FOR循环的块内。表示范围上下界的两个表达式只在进入循环时计算一次。每次迭代name值自增1，但如果声明了REVERSE，name变量在每次迭代中将自减1，见如下示例：
FOR i IN 1..10 LOOP
--do something
RAISE NOTICE 'i IS %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
--do something
END LOOP;
4. 遍历命令结果：
[ <<label>> ]
FOR record_or_row IN query LOOP
statements
END LOOP [ label ];
这是另外一种形式的FOR循环，在该循环中可以遍历命令的结果并操作相应的数据，见如下示例：
FOR rec IN SELECT * FROM some_table LOOP
PERFORM some_func(rec.one_col);
END LOOP;
PL/pgSQL还提供了另外一种遍历命令结果的方式，和上面的方式相比，唯一的差别是该方式将SELECT语句存于字符串文本中，然后再交由EXECUTE命令动态的执行。和前一种方式相比，该方式的灵活性更高，但是效率较低。
[ <<label>> ]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP [ label ];
5. 异常捕获：
在PL/pgSQL函数中，如果没有异常捕获，函数会在发生错误时直接退出，与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式：
[ <<label>> ]
[ DECLARE
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
WHEN condition [ OR condition ... ] THEN
handler_statements
END;
如果没有错误发生，只有BEGIN块中的statements会被正常执行，然而一旦这些语句中有任意一条发生错误，其后的语句都将被跳过，直接跳转到EXCEPTION块的开始处。此时系统将搜索异常条件列表，寻找匹配该异常的第一个条件，如果找到匹配，则执行相应的handler_statements，之后再执行END的下一条语句。如果没有找到匹配，该错误就会被继续向外抛出，其结果与没有EXCEPTION子句完全等同。如果此时handler_statements中的语句发生新错误，它将不能被该EXCEPTION子句捕获，而是继续向外传播，交由其外层的EXCEPTION子句捕获并处理。见如下示例：
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
当以上函数执行到y := x / 0语句时，将会引发一个异常错误，代码将跳转到EXCEPTION块的开始处，之后系统会寻找匹配的异常捕捉条件，此时division_by_zero完全匹配，这样该条件内的代码将会被继续执行。需要说明的是，RETURN语句中返回的x值为x := x + 1执行后的新值，但是在除零之前的update语句将会被回滚，BEGIN之前的insert语句将仍然生效。
游标：
1. 声明游标变量：
在PL/pgSQL中对游标的访问都是通过游标变量实现的，其数据类型为refcursor。 创建游标变量的方法有以下两种：
1). 和声明其他类型的变量一样，直接声明一个游标类型的变量即可。
2). 使用游标专有的声明语法，如：
name CURSOR [ ( arguments ) ] FOR query;
其中arguments为一组逗号分隔的name datatype列表，见如下示例：
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
在上面三个例子中，只有第一个是未绑定游标，剩下两个游标均已被绑定。
2. 打开游标：
游标在使用之前必须先被打开，在PL/pgSQL中有三种形式的OPEN语句，其中两种用于未绑定的游标变量，另外一种用于绑定的游标变量。
1). OPEN FOR:
其声明形式为：
OPEN unbound_cursor FOR query;
该形式只能用于未绑定的游标变量，其查询语句必须是SELECT，或其他返回记录行的语句，如EXPLAIN。在PostgreSQL中，该查询和普通的SQL命令平等对待，即先替换变量名，同时也将该查询的执行计划缓存起来，以供后用。见如下示例：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2). OPEN FOR EXECUTE
其声明形式为：
OPEN unbound_cursor FOR EXECUTE query-string;
和上面的形式一样，该形式也仅适用于未绑定的游标变量。EXECUTE将动态执行其后以文本形式表示的查询字符串。
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
3). 打开一个绑定的游标
其声明形式为：
OPEN bound_cursor [ ( argument_values ) ];
该形式仅适用于绑定的游标变量，只有当该变量在声明时包含接收参数，才能以传递参数的形式打开该游标，这些参数将被实际代入到游标声明的查询语句中，见如下示例：
OPEN curs2;
OPEN curs3(42);
3. 使用游标：
游标一旦打开，就可以按照以下方式进行读取。然而需要说明的是，游标的打开和读取必须在同一个事物内，因为在PostgreSQL中，如果事物结束，事物内打开的游标将会被隐含的关闭。
1). FETCH
其声明形式为：
FETCH cursor INTO target;
FETCH命令从游标中读取下一行记录的数据到目标中，其中目标可以是行变量、记录变量，或者是一组逗号分隔的普通变量的列表，读取成功与否，可通过PL/pgSQL内置变量FOUND来判断，其规则等同于SELECT INTO。见如下示例：
FETCH curs1 INTO rowvar; --rowvar为行变量
FETCH curs2 INTO foo, bar, baz;
2). CLOSE
其声明形式为：
CLOSE cursor;
关闭当前已经打开的游标，以释放其占有的系统资源，见如下示例：
CLOSE curs1;
错误和消息：
在PostgreSQL中可以利用RAISE语句报告信息和抛出错误，其声明形式为：
RAISE level 'format' [, expression [, ...]];
这里包含的级别有 DEBUG(向服务器日志写信息)、 LOG(向服务器日志写信息，优先级更高)、 INFO、 NOTICE和 WARNING(把信息写到服务器日志以及转发到客户端应用，优先级逐步升高)和 EXCEPTION抛出一个错误(通常退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志，还是两个均有，是由 log_min_messages和 client_min_messages这两个系统初始化参数控制的。
在format部分中，%表示为占位符，其实际值仅在RAISE命令执行时由后面的变量替换，如果要在format中表示%自身，可以使用%%的形式表示，见如下示例：
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; --v_job_id变量的值将替换format中的%。
DDL
PostgreSQL 9.3 将引入事件触发器, 与普通触发器不同的是, 事件触发器是数据库全局的触发器, 可以由DDL事件来触发.
例如可以用来实施DDL的操作审计，以及防止某些用户执行某些DDL，在某些表上面执行DDL等等。
Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.
事件触发器同样可以使用C, plpgsql或者其他的过程语言的函数来编写, 但是不能使用SQL语言函数来编写.
由于事件触发器涉及的权限较大, 例如能禁止DDL操作等, 所以只能使用超级用户创建事件触发器.
在创建事件触发器之前必须先创建触发器函数, 触发器函数的返回类型为event_trigger. (注意区分我们以前所熟悉的普通触发器函数的返回类型为trigger.)
事件触发器的语法
Command: CREATE EVENT TRIGGER
Description: define a new event trigger
Syntax:
CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE function_name()
语法解释 :
name : 触发器名称
event : 事件名称, 现在支持的事件为ddl_command_start 和 ddl_command_end.
支持触发事件触发器的DDL如下(包括select into) :
PostgreSQL: Documentation: devel: 40.2. Event Trigger Firing Matrix
但是触发事件中不包括对系统共享对象的CREATE, ALTER, DROP操作, 如 :
databases, roles, and tablespaces
同样对事件触发器本身的DDL操作也不会触发事件触发器.
The ddl_command_start event occurs just before the execution of a CREATE, ALTER, or DROP command.
As an exception, however, this event does not occur for DDL commands targeting shared objects - databases, roles, and tablespaces - or for command targeting event triggers themselves.
The event trigger mechanism does not support these object types.
ddl_command_start also occurs just before the execution of a SELECT INTO command, since this is equivalent to CREATE TABLE AS.
The ddl_command_end event occurs just after the execution of this same set of commands.
filter_variable目前只支持TAG
filter_value是http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html这里定义的DDL
function_name就是我们创建好的事件触发器函数.
以plpgsql函数语言为例讲解事件触发器函数的创建方法
PL/pgSQL can be used to define event triggers.
PostgreSQL requires that a procedure that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger.
When a PL/pgSQL function is called as a event trigger, several special variables are created automatically in the top-level block. They are:
TG_EVENT
Data type text; a string representing the event the trigger is fired for.
TG_TAG
Data type text; variable that contains the command tag for which the trigger is fired.
事件触发器函数的返回类型为event_trigger, 同时事件触发器的顶级块带入了两个特殊变量, TG_EVENT和TG_TAG.
TG_EVENT表示EVENT信息, 如现在支持的为ddl_command_start 和 ddl_command_end.
TG_TAG表示的是DDL信息, 信息在 http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html查询.
如果同一个事件上建立了多个事件触发器, 执行顺序按触发器名字的字母先后顺序来执行, 这个和普通触发器的触发规则是一样的.
如下 :
创建两个触发器函数, 返回event_trigger类型 :
CREATE OR REPLACE FUNCTION etgr1()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'this is etgr1, event:%, command:%', tg_event, tg_tag;
END;
$$;
CREATE OR REPLACE FUNCTION etgr2()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'this is etgr2, event:%, command:%', tg_event, tg_tag;
END;
$$;
创建事件触发器, 这里未使用WHEN, 也就是所有的DDL都触发这些事件触发器(除了前面提到的触发器本身的DDL和共享对象的DDL) :
CREATE EVENT TRIGGER b ON ddl_command_start EXECUTE PROCEDURE etgr1();
CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE etgr2();
同一个事件类型ddl_command_start下创建了2个事件触发器, 事件触发器的名称分别为a和b, 调用的先后顺序按字母顺序来, 如下 :
digoal=# create table digoal(id int);
NOTICE: this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE: this is etgr1, event:ddl_command_start, command:CREATE TABLE
CREATE TABLE
查询当前数据库中有哪些事件触发器 :
digoal=# select * from pg_event_trigger ;
evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
---------+-------------------+----------+---------+------------+---------
b | ddl_command_start | 10 | 16669 | O |
a | ddl_command_start | 10 | 16671 | O |
(2 rows)
evtowner是创建事件触发器的用户, 例如上面两个事件触发器我是用postgres用户创建的。
digoal=# select rolname from pg_roles where oid=10;
rolname
----------
postgres
(1 row)
evtfoid指事件触发器函数的oid,
digoal=# select proname from pg_proc where oid=16669;
proname
---------
etgr1
(1 row)
digoal=# select proname from pg_proc where oid=16671;
proname
---------
etgr2
(1 row)
事件触发器和DDL语句本身是在同一个事务中处理的, 所以任何事件触发器抛出异常的话, 整个事务都会回滚, 并且后续的操作也不会执行下去.
例如 :
创建事件触发器函数, 函数直接抛出异常.
digoal=# create or replace function abort1() returns event_trigger as $$
declare
begin
raise exception 'event:%, command:%. abort.', TG_EVENT, TG_TAG;
end;
$$ language plpgsql;
创建ddl_command_end 事件触发器
digoal=# create event trigger tg_abort1 on ddl_command_end execute procedure abort1();
CREATE EVENT TRIGGER
执行DDL语句, 如下, 在调用了a和b事件触发器后, 最后调用ddl_command_end的触发器, 抛出异常
digoal=# create table digoal1(id int);
NOTICE: this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE: this is etgr1, event:ddl_command_start, command:CREATE TABLE
ERROR: event:ddl_command_end, command:CREATE TABLE. abort.
异常导致表创建失败
digoal=# \d digoal1
Did not find any relation named "digoal1".
再创建1个事件触发器, 放在ddl_command_start 事件中
digoal=# create event trigger tg_abort2 on ddl_command_start execute procedure abort1();
CREATE EVENT TRIGGER
digoal=# create table digoal1(id int);
NOTICE: this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE: this is etgr1, event:ddl_command_start, command:CREATE TABLE
ERROR: event:ddl_command_start, command:CREATE TABLE. abort.
同样会导致DDL执行失败. 这就达到了禁止执行DDL的目的.
digoal=# create event trigger abort2 on ddl_command_start execute procedure abort1();
CREATE EVENT TRIGGER
digoal=# create table digoal1(id int);
NOTICE: this is etgr2, event:ddl_command_start, command:CREATE TABLE
ERROR: event:ddl_command_start, command:CREATE TABLE. abort.
digoal=# \d digoal1
Did not find any relation named "digoal1".
当前数据库中的事件触发器如下
digoal=# select * from pg_event_trigger ;
evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
-----------+-------------------+----------+---------+------------+---------
b | ddl_command_start | 10 | 16669 | O |
a | ddl_command_start | 10 | 16671 | O |
tg_abort1 | ddl_command_end | 10 | 16676 | O |
tg_abort2 | ddl_command_start | 10 | 16676 | O |
abort2 | ddl_command_start | 10 | 16676 | O |
(5 rows)
事件触发器应用举例
1. 禁止postgres用户在数据库digoal中执行CREATE TABLE和DROP TABLE命令.
首先把已有的事件触发器删除, 方便观看测试效果.
digoal=# drop event trigger tg_abort1;
DROP EVENT TRIGGER
digoal=# drop event trigger tg_abort2;
DROP EVENT TRIGGER
digoal=# drop event trigger abort2;
DROP EVENT TRIGGER
digoal=# drop event trigger a;
DROP EVENT TRIGGER
digoal=# drop event trigger b;
DROP EVENT TRIGGER
digoal=# select * from pg_event_trigger ;
evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
---------+----------+----------+---------+------------+---------
(0 rows)
创建触发器函数 :
CREATE OR REPLACE FUNCTION abort()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
if current_user = 'postgres' then
RAISE EXCEPTION 'event:%, command:%', tg_event, tg_tag;
end if;
END;
$$;
创建触发器 :
digoal=# create event trigger a on ddl_command_start when TAG IN ('CREATE TABLE', 'DROP TABLE') execute procedure abort();
CREATE EVENT TRIGGER
digoal=# select * from pg_event_trigger ;
evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
---------+-------------------+----------+---------+------------+-------------------------------
a | ddl_command_start | 10 | 16683 | O | {"CREATE TABLE","DROP TABLE"}
(1 row)
测试postgres用户是否可以使用create table和drop table .
digoal=# \c digoal postgres
You are now connected to database "digoal" as user "postgres".
无法新建表了
digoal=# create table new(id int);
ERROR: event:ddl_command_start, command:CREATE TABLE
digoal=# \d new
Did not find any relation named "new".
digoal=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | digoal | table | postgres
public | digoal1 | table | postgres
public | test | table | postgres
(3 rows)
无法删表了
digoal=# drop table digoal;
ERROR: event:ddl_command_start, command:DROP TABLE
digoal=# \d digoal
Table "public.digoal"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
测试其他用户是否会有影响
digoal=# \c digoal digoal
You are now connected to database "digoal" as user "digoal".
digoal=> create table tbl(id int);
CREATE TABLE
digoal=> drop table tbl;
DROP TABLE
未受到影响.
其他
1. 事件触发器还可以结合会话参数session_replication_role来使用, 例如仅针对replica角色生效, 其他不生效.
Command: ALTER EVENT TRIGGER
Description: change the definition of an event trigger
Syntax:
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO new_owner
ALTER EVENT TRIGGER name RENAME TO new_name
具体用法可参见trigger的用法介绍 :
http://blog.163.com/digoal@126/blog/static/1638770402013283547959/
http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/
2. 我们知道PostgreSQL没有像Oracle里面的DBA_OBJECTS表, 无法得知创建时间, ALTER时间.
使用事件触发器这个将会变成可能, 但是目前的事件触发器函数仅仅支持TG_EVENT和TG_TAG变量, 如果能加入TG_RELID, 那么就可以在DDL的时候记录这个事件到一个对象表中. 从而达到跟踪对象被执行DDL的时间的目的.
3. 事件触发器实际上是通过钩子实现的，例如 InvokeObjectPostCreateHook 在创建对象结束时调用。
src/backend/catalog/objectaccess.c
/*
* RunObjectPostCreateHook
*
* It is entrypoint of OAT_POST_CREATE event
*/
void
RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,
bool is_internal)
{
ObjectAccessPostCreate pc_arg;
/* caller should check, but just in case... */
Assert(object_access_hook != NULL);
memset(&pc_arg, 0, sizeof(ObjectAccessPostCreate));
pc_arg.is_internal = is_internal;
(*object_access_hook) (OAT_POST_CREATE,
classId, objectId, subId,
(void *) &pc_arg);
}
src/include/catalog/objectaccess.h
/* Core code uses these functions to call the hook (see macros below). */
extern void RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,
bool is_internal);
extern void RunObjectDropHook(Oid classId, Oid objectId, int subId,
int dropflags);
extern void RunObjectPostAlterHook(Oid classId, Oid objectId, int subId,
Oid auxiliaryId, bool is_internal);
extern bool RunNamespaceSearchHook(Oid objectId, bool ereport_on_volation);
extern void RunFunctionExecuteHook(Oid objectId);
......
/*
* The following macros are wrappers around the functions above; these should
* normally be used to invoke the hook in lieu of calling the above functions
* directly.
*/
#define InvokeObjectPostCreateHook(classId,objectId,subId) \
InvokeObjectPostCreateHookArg((classId),(objectId),(subId),false)
#define InvokeObjectPostCreateHookArg(classId,objectId,subId,is_internal) \
do { \
if (object_access_hook) \
RunObjectPostCreateHook((classId),(objectId),(subId), \
(is_internal)); \
} while(0)
......
在函数中执行DDL，同样被审查，因为HOOK不是语义层面的，而是执行层面的。
例如：
postgres=# create or replace function fe() returns event_trigger as $$
declare
begin
if current_user = 'digoal' then
raise exception 'can not execute ddl';
end if;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE fe();
CREATE EVENT TRIGGER
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create table tbl(id int);
ERROR: can not execute ddl
postgres=> do language plpgsql $$
postgres$> declare
postgres$> begin
postgres$> execute 'create table tbl (id int)';
postgres$> end;
postgres$> $$;
ERROR: can not execute ddl
CONTEXT: SQL statement "create table tbl (id int)"
PL/pgSQL function inline_code_block line 4 at EXECUTE statement 
950
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
