2020年03月_foucus、

原创 PostgreSQL 为什么使用了GIN索引还是慢？

PostgreSQL中的gin索引常常被用来需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索等。但有时尽管是多值类型，我们使用gin索引查询时，却会发生查询很慢的情况。例子：创建表并在数组类型的列上创建gin索引。bill=# create table t1(id int, info int[]); CREATE TABLEbill=# insert into t1...

2020-03-31 17:37:31 2596

原创 oracle迁移mysql自增序列问题

问题说明：今天从oracle迁移数据到mysql碰到个需求：原先的主键字段需要改成mysql中的自增字段，而且原先数据的值不能变，以后新插入的值从原先数据最大的值开始自增。解决办法：构建环境：mysql> CREATE TABLE test -> ( -> id INT UNSIGNED NOT NULL PRIMARY KEY, -> ...

2020-03-31 15:59:54 2225

原创 mysql小版本升级

1、备份mysql旧版目录：cp -R mysql mysql-5.7.222、备份mysql数据：./mysqldump -uroot -p123456 oprt > /home/mysqldba/oprt.sql3、停旧进程:新建data目录，移旧版本的data数据文件至该目录下（方便以后升级mysql小版本）；更改旧版本的my.cnf文件（datadir指向新data目录），...

2020-03-31 15:57:41 3186

原创 mysql5.7安装手册

1、解压压缩包在/usr/local目录下解压压缩包并改名[root@oracle local]# tar -zxvf /home/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz[root@oracle local]# mv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql/2、创建用户及组groupadd mys...

2020-03-30 17:42:54 1436

原创一文彻底读懂PostgreSQL事务隔离级别

SQL标准定义了四种隔离级别。隔离级别从低到高分别是：Read uncommitted 、Read committed 、Repeatable read 、Serializable。在各个隔离级别上被禁止出现的现象是：脏读：一个事务读取了另一个并行未提交事务写入的数据。不可重复读：一个事务重新读取之前读取过的数据，发现该数据已经被另一个事务（在初始读之后提交）修改。幻读：一个事务重新执行一...

2020-03-30 14:09:31 4051 1

原创 PostgreSQL HAVING和WHERE子句区别详解

我们在写SQL时常常会使用where和having子句来进行数据的过滤。但是这两者具体区别在哪呢？PostgreSQL中和其它关系型数据库一样也支持聚合函数，一个聚合函数是指从多个输入行中计算出一个结果。而对于where和having子句最大的区别在于：Where中不能使用聚合函数，而having中可以使用聚合函数。那么为什么会有这种限制呢？我们来看个例子：–找出所有记录中id最大的记录：...

2020-03-30 10:23:18 3235

原创 uxdb优炫数据库安装配置手册

1、上传安装包：上传目录：/home/setup/youxuandb检查安装环境的信息[root@isdtest dmdba]# uname -aLinux isdtest 4.14.0-115.5.1.el7a.06.aarch64 #1 SMP Tue Jun 18 10:34:55 CST 2019 aarch64 aarch64 aarch64 GNU/Linux[root@i...

2020-03-27 16:29:23 5936 2

原创 kingbase人大金仓数据库安装配置手册

1、上传安装包安装包和license文件上传到/home/setup/kingbase目录2、操作系统配置关闭防火墙中标麒麟:systemctl stop firewalldsystemctl disabled firewalld银河麒麟：service iptables stop3、安装数据库创建数据库安装用户：groupadd kingbaseuseradd kingb...

2020-03-27 16:21:00 21996 8

原创 PostgreSQL 管道函数(Pipelined Table Function)——兼容oracle

1、概述在oracle中管道函数是用来返回行集合的，我们可以像查询物理表一样查询它或者将它的值赋给其它集合变量。oracle中的管道函数有以下几个特点：管道函数为并行执行，在普通的函数中使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数。关键字PIPELINED...

2020-03-26 15:28:55 2672

原创 DM达梦数据库作业管理详解

1、什么是作业管理？在数据库的日常运维工作中，有许多日常工作都是固定不变的。例如，定期备份数据库，定期生成数据统计报表等等。这些工作既单调又费时，如果这些重复任务能够自动化完成，那就可以节省大量的时间。DM 的作业系统为用户提供了创建作业，并对作业进行调度执行以完成相应管理任务的功能。可以让这些重复的数据库任务自动完成，实现日常工作自动化。作业系统大致包含作业、警报和操作员三部分。用户需...

2020-03-25 16:08:42 2522

原创 DM达梦数据库存储过程和触发器

1、存储过程在dm中用户可以使用 DMSQL 程序语言创建存储过程。这些过程像普通的过程或函数一样，有输入、输出参数和返回值，它们与表和视图等数据库对象一样被存储在数据库中，供用户随时调用。写匿名块：Declare (可选项）Begin Body;ExceptionEnd;如何写存储过程。函数就是带返回值的存储过程。案例：根据员工的编号找出员工所在部门？CREATE ...

2020-03-25 11:12:03 4647 1

原创 DM达梦数据库对象管理——视图、序列、同义词、索引

1、视图dm中视图分为普通视图和物化视图。其中普通视图不占磁盘空间。1.1、创建语法SQL> create view () as select () from () where ();例子：SQL> create view v1 as select * from dmhr.employee;1.2、视图查看SQL> select view_name,text ...

2020-03-25 10:55:06 2785

原创 DM达梦数据库对象管理——表

达梦支持哪些类型的表？和oracle和相似，在oracle中支持的表类型几乎在dm中都存在，但不同的是，oracle中默认是堆表，而在dm中默认是索引组织表，当然这个可以在初始化实例的时候修改。此外，dm中还支持临时表，分区表，外部表等。如何去规划表？命名：字母开头 a-z,0-9,$#_数据类型：int char varchar date clob blob number等等存储位置：...

2020-03-24 16:21:11 3042

原创 DM达梦数据库用户管理

1、dm数据库用户有哪些SQL> select username from dba_users;1、sys -----达梦数据库内置管理用户，不能登录数据库，数据库使用的大部分的数据字典和动态性能视图sys。2、Sysdba -----数据库的管理员3、Sysauditor—审计用户4、Syssso—安全用户5、Sysdbo—数据操作员（安全版，四权分立）如何规划用户？用...

2020-03-24 11:31:10 2834

原创 DM达梦数据库表空间管理

1、DM表空间介绍达梦数据库的系统表空间有哪些？select tablespace_name from dba_tablespaces;select name from v$tablespace;SQL> select name from v$tablespace;LINEID NAME ---------- ------1 SYSTEM2 ...

2020-03-23 17:37:02 2001

原创 DM达梦数据库体系结构详解

1、DM逻辑结构dm中和oracle一样，也分为实例和数据库两个概念，而且这两者的概念也基本一致。唯一的区别是：达梦是单进程，而oracle是多进程的。实例：共享内存+后台的进程或线程。数据库：存放到磁盘的文件。一般是一个db对应一个实例，但是DSC(DM共享集群）多个实例对一个数据库。dm的服务器组成：客户端+服务器（实例+数据库）客户端不能直接去访问数据库，客户端把请求交给...

2020-03-23 16:30:58 4922

原创 DM达梦数据库实例管理

1、数据库的状态DM数据库包含以下几种状态：Open:打开状态，可以访问数据文件Mount：配置状态，不能访问数据文件，可以修改归档状态Suspend：挂起状态，跟OPEN类似，没法写入磁盘Shutdown：关闭需要注意的是：OPEN 状态与 MOUNT 和 SUSPEND 能相互转换，但是 MOUNT 和 SUSPEND 之间不能相互转换。2、启动过程DM数据库的启动即指从s...

2020-03-23 15:36:58 1969

原创 select也要小心——PostgreSQL security invoker函数带来的安全隐患

pg中创建函数时可以指定权限检测，有两个可选参数：SECURITY INVOKER和SECURITY DEFINER。SECURITY INVOKER：表示要用调用该函数的用户的特权来执行它。这是默认值。SECURITY DEFINER：指定要用拥有该函数的用户的特权来执行该函数。比如我们使用普通用户创建了一个调用者权限的函数，那么当超级用户调用这个函数时，就会以超级用户的权限来执行，这就会...

2020-03-20 16:57:30 2343

原创 PostgreSQL中in、exists、= any性能对比

在SQL中我们可以使用in、exists语句来判断对象是否存在某个子查询中，在pg中除了支持这两种语法，还支持= any的语法，例如下面三个SQL的意思就是一样的：select * from tbl where id in (select id from t); select * from tbl where exists (select 1 from t where t.id=tbl...

2020-03-20 15:09:00 14046 2

原创 PostgreSQL被除数为0处理方法

0不能作为被除数这是数学中的常识，当在数据库中除以0时，应该如何处理呢？在oracle和pg中是不允许被除数为0的。oracle：SQL> select 1/0 from dual;select 1/0 from dual *ERROR at line 1:ORA-01476: divisor is equal to zeroPostgreSQL：bill@...

2020-03-19 11:30:38 8863

原创 PostgreSQL多表插入(兼容oracle insert all)

我们想把一个查询语句返回的结果集插入到多个目标表中。例如，希望把A表的数据分别插入到 B 表、C 表和 D 表。这3个表与 A 表的结构相同(相同的列和数据类型)，并且当前不含任何数据。解决办法就是把查询结果插入到多个目标表中。在oracle中我们可以使用insert all或者insert first语句，两者语法基本一致，区别在于：insert first：对于每一行数据，只插入到第一...

2020-03-18 17:20:02 4403

原创 PostgreSQL窗口查询优化

我们在实际应用中，常常会碰到这样的场景：分组取top值，比如取某一部门收入前10的人。一般我们都会用到窗口函数去解决这类问题，同样在pg中也支持窗口函数。例子：创建测试表，生成10000个分组，1000万条记录。bill=# create table tbl(c1 int, c2 int, c3 int); CREATE TABLE bill=# create index idx...

2020-03-18 15:05:38 2478

原创 PostgreSQL视图攻击

我们常常使用视图来限制用户只能访问部分数据，来保证数据安全。但是在pg中某些情况下即使使用了视图仍然难以避免数据的安全。例子：创建测试表，插入数据：postgres@postgres=>create table t1(id int,info text);CREATE TABLEpostgres@postgres=>insert into t1 select generate_...

2020-03-12 16:29:08 1536

原创 PostgreSQL大小写不敏感排序

pg12开始支持不区分大小写，或者区分大小写的排序的collate。语法：CREATE COLLATION [ IF NOT EXISTS ] name ( [ LOCALE = locale, ] [ LC_COLLATE = lc_collate, ] [ LC_CTYPE = lc_ctype, ] [ PROVIDER = provider, ] ...

2020-03-10 17:43:55 2108

原创 PostgreSQL returning子句

在数据库中当我们update了一行记录后，如果我们将来想要查看当前记录更新前是哪条记录该怎么办呢？pg中支持retuning子句，可以实现相应的功能。目前PostgreSQL支持insert,delete,update的returning。insert returning 返回的是新插入的值。delete returning 返回的是被删除的值。update returning 返回的是更...

2020-03-06 17:35:59 3874

原创 PostgreSQL批量操作

批量操作可以减少数据库与应用程序的交互次数，提高数据处理的吞吐量。那么在pg中如何实现批量插入、更新、删除操作呢？1、批量插入bill@bill=>create table tbl1(id int,info text,ctr_time timestamp);CREATE TABLE–方法一使用最基本的values(),(),()…的方法bill@bill=>insert ...

2020-03-06 15:47:40 2753

原创 PostgreSQL的OGG——Bucardo搭建手顺

1、搭建前准备bucardo是PostgreSQL数据库中实现双向同步的软件，可以实现PostgreSQL数据库的双master的方案，不过bucardo中的同步都是异步的，它是通过触发器记录变化，程序是perl写的.bucardo可以实现postgresql的多主复制、主从同步，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步。...

2020-03-06 10:49:20 3388

原创 PostgreSQL merge join扫描弊端与优化

PostgreSQL 支持三种JOIN方法，它们分别是nestloop, hash, merge join。其中merge join扫描方法大致为：先按照join key对两表排序，然后按JOIN KEY顺序扫描，两个JOIN对象都只扫描一次。当其中一个表上存在过滤条件时，自然是只需要对满足条件的数据进行排序即可。但是另一张表呢？正常来说，因为只取两表关联的数据，所以另一张表也只需要对应的满足条...

2020-03-06 10:42:34 2538

原创 PostgreSQL修改数据库timezone

timezone 是客户端设定的会话级别的时区参数。如果客户端没有指定，则以数据库设置的timezone参数或db，user级别参数为准。例子：查看当前timezonebill@bill=>show timezone; TimeZone ---------- PRC(1 row)bill@bill=>select now(); now ...

2020-03-05 17:03:11 2756

原创 PostgreSQL禁用索引扫描的方法

某些情况下，索引扫描可能并不适合我们当前的需要，但是优化器却选择了索引扫描，这时我们该如何避免使用索引扫描呢？比较常见的做法有：1、hint在oracle中使用hint的方式十分常见，在pg中也支持hint的方法，但是需要安装pg_hint插件，方法见：PostgreSQL hint用法(兼容oracle)2、优化器开关我们都知道在pg中有很多优化器开关，例如：#enable_bitm...

2020-03-05 14:28:21 3912

Focus on PostgreSQL