13518219792
使用 bcc 工具观测 MySQL：1）dbstat功能：将 MySQL/PostgreSQL 的查询延迟汇总为直方图
清水河网站建设公司创新互联建站,清水河网站设计制作，有大型网站制作公司丰富经验。已为清水河1000+提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的清水河做网站的公司定做！
语法：
dbstat [-h] [-v] [-p [PID [PID ...]]] [-m THRESHOLD] [-u] [-i INTERVAL] {mysql,postgres}
选项：
{mysql,postgres} # 观测哪种数据库-h, --help # 显示帮助然后退出-v, --verbose # 显示BPF程序-p [PID [PID ...]], --pid [PID [PID ...]] # 要观测的进程号，空格分隔-m THRESHOLD, --threshold THRESHOLD # 只统计查询延迟比此阈值高的-u, --microseconds # 以微秒为时间单位来显示延迟(默认单位：毫秒)-i INTERVAL, --interval INTERVAL # 打印摘要的时间间隔(单位：秒)
示例：
# 使用 sysbench 在被观测数据库上执行 select[root@liuan tools]# dbstat mysql -p `pidof mysqld` -uTracing database queries for pids 3350 slower than 0 ms...^C[14:42:26] query latency (us)
2）dbslower
功能：跟踪 MySQL/PostgreSQL 的查询时间高于阈值
语法：
dbslower [-h] [-v] [-p [PID [PID ...]]] [-x PATH] [-m THRESHOLD] {mysql,postgres}
参数：
{mysql,postgres} # 观测哪种数据库 -h, --help # 显示帮助然后退出 -v, --verbose # 显示BPF程序 -p [PID [PID ...]], --pid [PID [PID ...]] # 要观测的进程号，空格分隔 -m THRESHOLD, --threshold THRESHOLD # 只统计查询延迟比此阈值高的 -x PATH, --exe PATH # 数据库二进制文件的位置
示例：
# 使用sysbench在被观测数据库上执行update_index [root@liuan tools]# dbslower mysql -p `pidof mysqld` -m 2 Tracing database queries for pids 3350 slower than 2 ms... TIME(s) PID MS QUERY 1.765087 3350 2.996 UPDATE sbtest1 SET k=k+1 WHERE id=963 3.187147 3350 2.069 UPDATE sbtest1 SET k=k+1 WHERE id=628 5.945987 3350 2.171 UPDATE sbtest1 SET k=k+1 WHERE id=325 7.771761 3350 3.853 UPDATE sbtest1 SET k=k+1 WHERE id=5955. 使用限制
bcc 基于 eBPF 开发（需要 Linux 3.15 及更高版本）。bcc 使用的大部分内容都需要 Linux 4.1 及更高版本。
"bcc.usdt.USDTException: failed to enable probe 'query__start'; a possible cause can be that the probe requires a pid to enable" 需要 MySQL 具备 Dtrace tracepoint。
mongodb很多企业都在用，是非常成熟的技术，没有不稳定的说法，是非关系库
postgresql我没见到企业用过，是关系库
两者适用场景不一样，但都很稳定
mongodb不适用的场景如下：
1）要求高度事务性的系统。
2）传统的商业智能应用。
3）复杂的跨文档（表）级联查询
1. 检查约束
检查约束是最常见的约束类型。它允许你声明在某个字段里的数值必须使一个布尔表达式为真。比如，要强制一个正数的产品价格，你可以用：
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price 0) );
如你所见，约束定义在数据类型之后，就好像缺省值定义一样。缺省值和约束可以按任意顺序排列。一个检查约束由一个关键字 CHECK 后面跟一个放在圆括弧里的表达式组成。检查约束表达式应该包含受约束的字段，否则这个约束就没什么意义了。
你还可以给这个约束取一个独立的名字。这样就可以令错误信息更清晰，并且在你需要修改它的时候引用这个名字。语法是：
CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price 0) );
因此，要声明一个命名约束，使用关键字 CONSTRAINT 后面跟一个标识符(作为名字)，然后再跟约束定义。如果你不用这个方法声明约束，那么系统会自动为你选择一个名字。
一个检查约束也可以引用多个字段。假设你存储一个正常价格和一个折扣价，并且你想保证折扣价比正常价低。
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price 0), discounted_price numeric CHECK (discounted_price 0), CHECK (price discounted_price) );
头两个约束看上去很面熟。第三个使用了一个新的语法。它没有附着在某个字段上，而是在逗号分隔的字段列表中以一个独立行的形式出现。字段定义和约束定义可以按照任意顺序列出。
我们称头两个约束是"字段约束"，而第三个约束是"表约束"(和字段定义分开写)。字段约束也可以写成表约束，而反过来很可能不行，因为系统假设字段约束只引用它所从属的字段。PostgreSQL 并不强制这条规则，但是如果你希望自己的表定义可以和其它数据库系统兼容，那么你最好还是遵循这条规则。上面的例子也可以这么写：
CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price 0), discounted_price numeric, CHECK (discounted_price 0), CHECK (price discounted_price) ); 或者是
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price 0), discounted_price numeric, CHECK (discounted_price 0 AND price discounted_price) );
这只是风格的不同。
和字段约束一样，我们也可以给表约束赋予名称，方法也相同：
CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price 0), discounted_price numeric, CHECK (discounted_price 0), CONSTRAINT valid_discount CHECK (price discounted_price) );
我们还要注意的是，当约束表达式计算结果为 NULL 的时候，检查约束会被认为是满足条件的。因为大多数表达式在含有 NULL 操作数的时候结果都是 NULL ，所以这些约束不能阻止字段值为 NULL 。要确保一个字段值不为 NULL ，可以使用下一节介绍的非空约束。
2. 非空约束
非空约束只是简单地声明一个字段必须不能是 NULL 。下面是一个例子：
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );
一个非空约束总是写成一个字段约束。非空约束在功能上等效于创建一个检查约束 CHECK (column_name IS NOT NULL) ，但在 PostgreSQL 里，创建一个明确的非空约束效率更高。缺点是你不能给它一个明确的名字。
当然，一个字段可以有多个约束。只要一个接着一个写就可以了：
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price 0) );
它们的顺序无所谓。顺序并不影响约束检查的顺序。
NOT NULL 约束有个相反的约束：NULL 约束。它并不意味着该字段必须是空，因为这样的字段也没用。它只是定义了该字段可以为空的这个缺省行为。在 SQL 标准里没有定义 NULL 约束，因此不应该在可移植的应用中使用它。在 PostgreSQL 里面增加这个约束只是为了和其它数据库系统兼容。不过，有些用户喜欢它，因为这个约束可以让他们很容易在脚本文件里切换约束。比如，你可以从下面这样开始
CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );
然后在需要的时候插入 NOT 关键字。
【提示】在大多数数据库设计里，主要的字段都应该标记为非空。
3. 唯一约束
唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。它的语法是：
CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );
上面是写成字段约束，下面这个则写成表约束：
CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) );
如果一个唯一约束引用一组字段，那么这些字段用逗号分隔列出：
CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );
这样就声明了特定字段值的组合在整个表范围内是唯一的。但是这些字段中的某个单独值可以不必是(并且通常也确实不是)唯一的。
你也可以给唯一约束赋予一个自己定义的名字，方法与前面相同：
CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric );
通常，如果包含在唯一约束中的那几个字段在表中有多个相同的行，就违反了唯一约束。但是在这种比较中，NULL 被认为是不相等的。这就意味着，在多字段唯一约束的情况下，如果在至少一个字段上出现 NULL ，那么我们还是可以存储同样的这种数据行。这种行为遵循 SQL 标准，但是我们听说其它 SQL 数据库可能不遵循这个标准。因此如果你要开发可移植的程序，那么最好仔细些。
4. 主键
从技术上讲，主键约束只是唯一约束和非空约束的组合。所以，下面两个表定义是等价的：
CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric ); CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );
主键也可以约束多于一个字段；其语法类似于唯一约束：
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );
主键表示一个或多个字段的组合可以用于唯一标识表中的数据行。这是定义一个主键的直接结果。请注意：一个唯一约束实际上并不能提供一个唯一标识，因为它不排除 NULL 。这个功能对文档目的和客户应用都很有用。比如，一个可以修改行数值的 GUI 应用可能需要知道一个表的主键才能唯一地标识每一行。
一个表最多可以有一个主键(但是它可以有多个唯一和非空约束)。关系型数据库理论告诉我们，每个表都必须有一个主键。PostgreSQL 并不强制这个规则，但我们最好还是遵循它。
5. 外键
外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中出现的数值。我们把这个行为称为两个相关表之间的参照完整性。
假设你有个产品表，我们可能使用了好几次：
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );
假设你有一个存储这些产品的订单的表。我们想保证订单表只包含实际存在的产品。因此我们在订单表中定义一个外键约束引用产品表：
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer );
现在，我们不能创建任何其 product_no 没有在产品表中出现的订单。
在这种情况下我们把订单表叫做引用表，而产品表叫做被引用表。同样，也有引用字段和被引用字段。
你也可以把上面的命令简写成
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products, quantity integer );
因为如果缺少字段列表的话，就会引用被引用表的主键。
一个外键也可以约束和引用一组字段。同样，也需要写成表约束的形式。下面是一个捏造出来的语法例子：
CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );
当然，被约束的字段数目和类型需要和被引用字段数目和类型一致。
和平常一样，你也可以给外键约束赋予自定义的名字。
一个表可以包含多于一个外键约束。这个特性用于实现表之间的多对多关系。比如你有关于产品和订单的表，但现在你想允许一个订单可以包含多种产品(上面那个结构是不允许这么做的)，你可以使用这样的结构：
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );
注意最后的表的主键和外键是重叠的。
我们知道外键不允许创建和任何产品都无关的订单。但是如果一个订单创建之后其引用的产品被删除了怎么办？SQL 也允许你处理这个问题。简单说，我们有几种选择：
不允许删除一个被引用的产品
同时也删除订单
其它的?
为了说明这个问题，我们对上面的多对多关系制定下面的策略：如果有人想删除一种仍然被某个订单引用的产品(通过 order_items)，那么就不允许这么做。如果有人删除了一个订单，那么订单项也被删除。
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );
限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。NO ACTION 的意思是如果在检查约束的时候还存在任何引用行，则抛出错误；如果你不声明任何东西，那么它就是缺省的行为。这两个选择的实际区别是：NO ACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。CASCADE 声明在删除一个被引用的行的时候，所有引用它的行也会被自动删除掉。在外键字段上的动作还有两个选项：SET NULL 和 SET DEFAULT ，它们导致在被引用行删除的时候，将引用它们的字段分别设置为 NULL 和缺省值。请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SET DEFAULT ，但是缺省值并不能满足外键，那么该动作就会失败。
与 ON DELETE 类似的还有 ON UPDATE 选项，它是在被引用字段修改(更新)的时候调用的，可用的动作是一样的。
有关更新和删除数据的更多信息可以在章6里找到。
最后，我们应该说明的是，一个外键必须要么引用一个主键，要么引用一个唯一约束。如果外键引用了一个唯一约束，那么在如何匹配 NULL 这个问题上还有一些其它的可能性。这些东西都在 CREATE TABLE 中解释。
如果返回一个 数字或者字符 比较简单，那么多行多列怎么办呢，分为以下几种情况【东西很多，这里只做简单列举】
返回多行单列
又分为几种方式
1. return next，用在 for 循环中
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_name varchar;BEGINforv_namein( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) ) loop
RETURNNEXT v_name;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 循环外还有个 return
2. 需要实现声明 v_name
2. return query，无需 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) );
return;END;$$LANGUAGE PLPGSQL;
注意：如果 返回类型为 setof，最好用如下方法
RETURNQUERYEXECUTESQL
不要这么用
executesqlinto out;returnout;
返回多行多列
也有多种方式
1. 使用 return next 和 setof record ，需要 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD; BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
RETURNNEXT v_rec;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 读取表的整行数据时才能用 record
2. 如果读取的数据不是整行，需要自定义 复合数据类型，否则会报如下错误
ERROR: acolumndefinition listisrequiredforfunctions returning "record"
定义复合类型 ，示例如下
createtype myout2as (
road_num int,
freq bigint);createorreplacefunctiontest(cartext, time1text, time2text)returnssetof myout2as $$declare
array1 text[];
array2 text[];
len1 integer;
len2 integer;
x integer;
y integer;
road_str text;
car_str text;
sql text;
i myout2;
begin-- vin 号拼接selectregexp_split_to_array(car,',')into array2;
selectarray_length(array2,1)into len2;
car_str :='';
y :=1;
whiley= len2 loop
car_str :=car_str||quote_literal(array2[y])||',';
y :=y+1;
end loop;
-- sql 拼接sql :='select road_number, sum(frequency) from heat_map where date_key = '''|| time1
||'-01'' and date_key ='''|| time2
||'-20'' and vin in ('||rtrim(car_str,',')
||')group by road_number;';
--execute sql into out;foriinexecute sql loop
returnnext i;
end loop;
return;end$$ language plpgsql;
在执行时可能会报如下错误
ERROR:set-valuedfunctioncalledincontext that cannot accept aset
解决方法
select funcname(arg);--改为select*fromfuncname(arg);
2. return query，无需 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) );
return;END;
$$
LANGUAGE PLPGSQL;
3. 使用 out 输出参数
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger,out o_idinteger,out o_namevarchar)
RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
o_id := v_rec.id;
o_name := v_rec.name;
RETURNNEXT ;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
总结 - return next return query
我们可以看到上面无论是单列多行还是多列多行，都用到了 return next 和 return query 方法
在 plpgsql 中，如果存储过程返回 setof sometype，则返回值必须在 return next 或者 return query 中声明，然后有一个不带参数的 retrun 命令，告诉函数执行完毕；【setof 就意味着 多行】
用法如下
RETURNNEXT expression;RETURN QUERY query;RETURNQUERYEXECUTEcommand-string[ USING expression [, ... ]];
return next 可以用于标量和复合类型数据；
return query 命令将查询到的一条结果追加到函数的结果集中；
二者在单一集合返回函数中自由混合，在这种情况下，结果将被级联。【有待研究】
return query execute 是 return query 的变形，它指定 sql 将被动态执行；
returnqueryselectroad_number,sum(frequency)fromheat_mapgroupbyroad_number;--这样可以sql :='select road_number, sum(frequency) from heat_map group by road_number';returnquery sql;--这样不行
参考资料：
自定义类型并返回数组
return next return query
function返回多列多行
返回结果集多列和单列的例子
PostgreSQL存储过程(1)-基于SQL的存储过程
动态SQL
postgresql, pgadmin error RETURN cannot have a parameter in function returning set
存储过程输出参数
PostgreSQL存储过程(3)-流程控制语句
很少有空间或者服务器支持oracle的，因为oracle软件本身就要花很多钱买！可以考虑用跟oracle类似功能的postgresql的服务器空间替代。postgresql的空间也只是国外有，国内几乎找不到。
一个触发器是一种声明，告诉数据库应该在执行特定的操作的时候执行特定的函数。 触发器可以定义在一个INSERT，UPDATE， 或者 DELETE 命令之前或者之后执行，要么是对每个被修改的行一次， 要么是每条 SQL 一次。 如果发生触发器事件，那么将在合适的时刻调用触发器的函数以处理该事件。
触发器函数必须在创建触发器之前，作为一个没有参数并且返回trigger类型的函数定义。 （触发器函数通过特殊的 TriggerData 结构接收其输入，而不是用普通函数参数那种形式。）
一旦创建了一个合适的触发器函数，触发器就用 CREATE TRIGGER 创建。同一个触发器函数可以用于多个触发器。
PostgreSQL 提供按行触发的触发器和按语句触发的触发器。在按行触发的触发器里， 触发器函数是为触发触发器的语句影响的每一行执行一次。相比之下，一个按语句触发的触发器是在每执行一次合适的语句执行一次的， 而不管影响的行数。特别是，一个影响零行的语句将仍然导致任何适用的按语句触发的触发器的执行。 这两种类型的触发器有时候分别叫做行级别的触发器和语句级别的触发器。
触发器还通常分成 before 触发器和 after 触发器。 语句级别的 "before" 触发器通常在语句开始做任何事情之前触发， 而语句级别的 "after" 触发器在语句的最后触发。 行级别的 "before" 触发器在对特定行进行操作的时候马上触发， 而行级别的 "after" 触发器在语句结束的时候触发（但是在任何语句级别的 "after" 触发器之前）。
按语句触发的触发器应该总是返回 NULL。 如果必要，按行触发的触发器函数可以给调用它的执行者返回一表数据行（一个类型为 HeapTuple 的数值）， 那些在操作之前触发的触发器有以下选择：
它可以返回 NULL 以忽略对当前行的操作。 这就指示执行器不要执行调用该触发器的行级别操作（对特定行的插入或者更改））。
只用于INSERT和UPDATE触发器： 返回的行将成为被插入的行或者是成为将要更新的行。 这样就允许触发器函数修改被插入或者更新的行。
一个无意导致任何这类行为的在操作之前触发的行级触发器必须仔细返回那个被当作新行传进来的同一行 （也就是说，对于 INSERT 和 UPDATE 触发器而言，是 NEW 行， 对于 DELETE 触发器而言，是 OLD 行）。
对于在操作之后触发的行级别的触发器，其返回值会被忽略，因此他们可以返回NULL。
如果多于一个触发器为同样的事件定义在同样的关系上， 触发器将按照由名字的字母顺序排序的顺序触发。 如果是事件之前触发的触发器，每个触发器返回的可能已经被修改过的行成为下一个触发器的输入。 如果任何事件之前触发的触发器返回 NULL 指针， 那么对该行的操作将被丢弃并且随后的触发器不会被触发。
通常，行的 before 触发器用于检查或修改将要插入或者更新的数据。 比如，一个 before 触发器可以用于把当前时间插入一个 timestamp 字段， 或者跟踪该行的两个元素是一致的。行的 after 触发器多数用于填充或者更新其它表， 或者对其它表进行一致性检查。这么区分工作的原因是， after 触发器肯定可以看到该行的最后数值， 而 before 触发器不能；还可能有其它的 before 触发器在其后触发。 如果你没有具体的原因定义触发器是 before 还是 after，那么 before 触发器的效率高些， 因为操作相关的信息不必保存到语句的结尾。
如果一个触发器函数执行 SQL 命令，然后这些命令可能再次触发触发器。 这就是所谓的级联触发器。对级联触发器的级联深度没有明确的限制。 有可能出现级联触发器导致同一个触发器的递归调用的情况； 比如，一个 INSERT 触发器可能执行一个命令， 把一个额外的行插入同一个表中，导致 INSERT 触发器再次激发。 避免这样的无穷递归的问题是触发器程序员的责任。
在定义一个触发器的时候，我们可以声明一些参数。 在触发器定义里面包含参数的目的是允许类似需求的不同触发器调用同一个函数。 比如，我们可能有一个通用的触发器函数， 接受两个字段名字，把当前用户放在第一个，而当前时间戳在第二个。 只要我们写得恰当，那么这个触发器函数就可以和触发它的特定表无关。 这样同一个函数就可以用于有着合适字段的任何表的 INSERT 事件，实现自动跟踪交易表中的记录创建之类的问题。如果定义成一个 UPDATE 触发器，我们还可以用它跟踪最后更新的事件。
每种支持触发器的编程语言都有自己的方法让触发器函数得到输入数据。 这些输入数据包括触发器事件的类型（比如，INSERT 或者 UPDATE）以及所有在 CREATE TRIGGER 里面列出的参数。 对于低层次的触发器，输入数据也包括 INSERT 和 UPDATE 触发器的 NEW 行，和/或 UPDATE 和 DELETE 触发器的 OLD 行。 语句级别的触发器目前没有任何方法检查改语句修改的独立行。
