Oracle 11G RAC One Node’s Instance Arise ORA-01105 ORA-01606

生产环境,Oracle Linux 6.4 RAC 11.2.4.0,今天出现了grid集群因为OCR磁盘组不能访问而不能通过命令来显示集群状态信息,在手动mount OCR磁盘组后,准备重启节点2时出现了ORA-01105,ORA-01606错误,具体信息如下:

SQL> startup
ORACLE instance started.

Total System Global Area 5.3447E+10 bytes
Fixed Size                  2265864 bytes
Variable Size            1.3019E+10 bytes
Database Buffers         4.0265E+10 bytes
Redo Buffers              160698368 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: parameter not identical to that of another mounted instance

查看错误详细说明,根据错误描述可知是由于2号实例与1号实例由于某些参数设置一样所导致的

[oracle@db2 dbs]$ oerr ora 1105
01105, 00000, "mount is incompatible with mounts by other instances"
// *Cause:  An attempt to mount the database discovered that another instance
//         mounted a database by the same name, but the mount is not
//         compatible. Additional errors are reported explaining why.
// *Action: See accompanying errors.
[oracle@db2 dbs]$ oerr ora 1606
01606, 00000, "parameter not identical to that of another mounted instance"
// *Cause:  A parameter was different on two instances.
// *Action: Modify the initialization parameter and restart.

使用spfile文件来创建文本格式的pfile文件

SQL> create pfile='rlcs.ora' from spfile;

File created.

[oracle@db2 dbs]$ cat rlcs.ora

*._serial_direct_read='AUTO'
*._swrf_mmon_flush=TRUE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/RL/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/rl/controlfile/current.260.926786537','+ARCH/rl/controlfile/current.256.926786537'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+data/rl/','+data/rldg/'
*.db_name='RL'
*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RLZYXDB)'
*.fal_server='yb_st'
RL1.instance_number=1
RL2.instance_number=2
*.listener_networks=''
*.log_archive_config='dg_config=(rl,rldg)'
*.log_archive_dest_1='location=+ARCH  valid_for=(all_logfiles,all_roles) db_unique_name=rl'
*.log_archive_dest_2='service=yb_st valid_for=(online_logfiles,primary_role) db_unique_name=rldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arch'
*.log_file_name_convert='+data/rl/','+data/rldg/','+arch/rl/','+arch/rldg/'
*.open_cursors=300
*.parallel_adaptive_multi_user=TRUE
*.parallel_degree_policy='AUTO'
*.parallel_force_local=FALSE
*.pga_aggregate_target=21474836480
*.processes=2000
*.remote_listener='scan-ip:1521'
*.remote_login_passwordfile='exclusive'
*.service_names='rl'
*.sessions=2205
*.sga_max_size=53687091200
*.sga_target=53687091200
*.standby_file_management='manual'
RLZY2.thread=2
RLZY1.thread=1
*.undo_retention=7200
RLZY2.undo_tablespace='UNDOTBS2'
RLZY1.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/rmanbak/utl'
从文本参数文件看不出来有什么参数是两个实例不一致的。

通过执行下面的语句来查看2号实例与gc相关的参数
SQL> set linesize 333
SQL> col name for a35
SQL> col description for a66
SQL> col value for a30
SQL> SELECT   i.ksppinm name,  
  2     i.ksppdesc description,  
  3     CV.ksppstvl VALUE
  4  FROM   sys.x$ksppi i, sys.x$ksppcv CV  
  5     WHERE   i.inst_id = USERENV ('Instance')  
  6     AND CV.inst_id = USERENV ('Instance')  
  7     AND i.indx = CV.indx  
  8     AND i.ksppinm LIKE '/_gc%' ESCAPE '/'  
  9  ORDER BY   REPLACE (i.ksppinm, '_', '');  

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_affinity_locking                if TRUE, enable object affinity                                    TRUE
_gc_affinity_locks                  if TRUE, get affinity locks                                        TRUE
_gc_affinity_ratio                  dynamic object affinity ratio                                      50
_gc_async_memcpy                    if TRUE, use async memcpy                                          FALSE
_gc_bypass_readers                  if TRUE, modifications bypass readers                              TRUE
_gc_check_bscn                      if TRUE, check for stale blocks                                    TRUE
_gc_coalesce_recovery_reads         if TRUE, coalesce recovery reads                                   TRUE
_gc_cpu_time                        if TRUE, record the gc cpu time                                    FALSE
_gc_cr_server_read_wait             if TRUE, cr server waits for a read to complete                    TRUE
_gc_defer_ping_index_only           if TRUE, restrict deferred ping to index blocks only               TRUE
_gc_defer_time                      how long to defer pings for hot buffers in milliseconds            0

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_delta_push_compression          if delta >= K bytes, compress before push                          3072
_gc_delta_push_max_level            max delta level for delta push                                     100
_gc_delta_push_objects              objects which use delta push                                       0
_gc_disable_s_lock_brr_ping_check   if TRUE, disable S lock BRR ping check for lost write protect      TRUE
_gc_down_convert_after_keep         if TRUE, down-convert lock after recovery                          TRUE
_gc_element_percent                 global cache element percent                                       110
_gc_escalate_bid                    if TRUE, escalates create a bid                                    TRUE
_gc_fg_merge                        if TRUE, merge pi buffers in the foreground                        TRUE
_gc_flush_during_affinity           if TRUE, flush during affinity                                     TRUE
_gc_fusion_compression              compress fusion blocks if there is free space                      1024
_gc_global_checkpoint_scn           if TRUE, enable global checkpoint scn                              TRUE

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_global_cpu                      global cpu checks                                                  TRUE
_gc_global_lru                      turn global lru off, make it automatic, or turn it on              AUTO
_gc_global_lru_touch_count          global lru touch count                                             5
_gc_global_lru_touch_time           global lru touch time in seconds                                   60
_gc_integrity_checks                set the integrity check level                                      1
_gc_keep_recovery_buffers           if TRUE, make single instance crash recovery buffers current       TRUE
_gc_latches                         number of latches per LMS process                                  8
_gc_log_flush                       if TRUE, flush redo log before a current block transfer            TRUE
_gc_long_query_threshold            threshold for long running query                                   0
_gc_max_downcvt                     maximum downconverts to process at one time                        256
_gc_maximum_bids                    maximum number of bids which can be prepared                       0

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_no_fairness_for_clones          if TRUE, no fairness if we serve a clone                           TRUE
_gc_object_queue_max_length         maximum length for an object queue                                 0
_gc_override_force_cr               if TRUE, try to override force-cr requests                         TRUE
_gc_persistent_read_mostly          if TRUE, enable persistent read-mostly locking                     TRUE
_gc_policy_minimum                  dynamic object policy minimum activity per minute                  1500
_gc_policy_time                     how often to make object policy decisions in minutes               10
_gc_read_mostly_flush_check         if TRUE, optimize flushes for read mostly objects                  FALSE
_gc_read_mostly_locking             if TRUE, enable read-mostly locking                                FALSE
_gcr_enable_high_cpu_kill           if TRUE, GCR may kill foregrounds under high load                  FALSE
_gcr_enable_high_cpu_rm             if TRUE, GCR may enable a RM plan under high load                  FALSE
_gcr_enable_high_cpu_rt             if TRUE, GCR may boost bg priority under high load                 FALSE

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gcr_high_cpu_threshold             minimum amount of CPU process must consume to be kill target       10
_gcr_use_css                        if FALSE, GCR wont register with CSS nor use any CSS feature       TRUE
_gc_sanity_check_cr_buffers         if TRUE, sanity check CR buffers                                   FALSE
_gcs_disable_remote_handles         disable remote client/shadow handles                               FALSE
_gcs_disable_skip_close_remastering if TRUE, disable skip close optimization in remastering            FALSE
_gc_serve_high_pi_as_current        if TRUE, use a higher clone scn when serving a pi                  TRUE
_gcs_fast_reconfig                  if TRUE, enable fast reconfiguration for gcs locks                 TRUE
_gcs_latches                        number of gcs resource hash latches to be allocated per LMS proces 64
                                    s

_gcs_pkey_history                   number of pkey remastering history                                 4000

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gcs_process_in_recovery            if TRUE, process gcs requests during instance recovery             TRUE
_gcs_resources                      number of gcs resources to be allocated
_gcs_res_per_bucket                 number of gcs resource per hash bucket                             4
_gcs_shadow_locks                   number of pcm shadow locks to be allocated
_gc_statistics                      if TRUE, kcl statistics are maintained                             TRUE
_gcs_testing                        GCS testing parameter                                              0
_gc_transfer_ratio                  dynamic object read-mostly transfer ratio                          2
_gc_undo_affinity                   if TRUE, enable dynamic undo affinity                              TRUE
_gc_undo_block_disk_reads           if TRUE, enable undo block disk reads                              TRUE
_gc_use_cr                          if TRUE, allow CR pins on PI and WRITING buffers                   TRUE
_gc_vector_read                     if TRUE, vector read current buffers                               TRUE

64 rows selected.

查看1号实例与gc相关的参数

SQL> set linesize 333
SQL> col name for a35
SQL> col description for a66
SQL> col value for a30
SQL> SELECT   i.ksppinm name,  
  2     i.ksppdesc description,  
  3     CV.ksppstvl VALUE
  4  FROM   sys.x$ksppi i, sys.x$ksppcv CV  
  5     WHERE   i.inst_id = USERENV ('Instance')  
  6     AND CV.inst_id = USERENV ('Instance')  
  7     AND i.indx = CV.indx  
  8     AND i.ksppinm LIKE '/_gc%' ESCAPE '/'  
  9  ORDER BY   REPLACE (i.ksppinm, '_', '');  

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_affinity_locking                if TRUE, enable object affinity                                    TRUE
_gc_affinity_locks                  if TRUE, get affinity locks                                        TRUE
_gc_affinity_ratio                  dynamic object affinity ratio                                      50
_gc_async_memcpy                    if TRUE, use async memcpy                                          FALSE
_gc_bypass_readers                  if TRUE, modifications bypass readers                              TRUE
_gc_check_bscn                      if TRUE, check for stale blocks                                    TRUE
_gc_coalesce_recovery_reads         if TRUE, coalesce recovery reads                                   TRUE
_gc_cpu_time                        if TRUE, record the gc cpu time                                    FALSE
_gc_cr_server_read_wait             if TRUE, cr server waits for a read to complete                    TRUE
_gc_defer_ping_index_only           if TRUE, restrict deferred ping to index blocks only               TRUE
_gc_defer_time                      how long to defer pings for hot buffers in milliseconds            0

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_delta_push_compression          if delta >= K bytes, compress before push                          3072
_gc_delta_push_max_level            max delta level for delta push                                     100
_gc_delta_push_objects              objects which use delta push                                       0
_gc_disable_s_lock_brr_ping_check   if TRUE, disable S lock BRR ping check for lost write protect      TRUE
_gc_down_convert_after_keep         if TRUE, down-convert lock after recovery                          TRUE
_gc_element_percent                 global cache element percent                                       110
_gc_escalate_bid                    if TRUE, escalates create a bid                                    TRUE
_gc_fg_merge                        if TRUE, merge pi buffers in the foreground                        TRUE
_gc_flush_during_affinity           if TRUE, flush during affinity                                     TRUE
_gc_fusion_compression              compress fusion blocks if there is free space                      1024
_gc_global_checkpoint_scn           if TRUE, enable global checkpoint scn                              TRUE

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_global_cpu                      global cpu checks                                                  TRUE
_gc_global_lru                      turn global lru off, make it automatic, or turn it on              AUTO
_gc_global_lru_touch_count          global lru touch count                                             5
_gc_global_lru_touch_time           global lru touch time in seconds                                   60
_gc_integrity_checks                set the integrity check level                                      1
_gc_keep_recovery_buffers           if TRUE, make single instance crash recovery buffers current       TRUE
_gc_latches                         number of latches per LMS process                                  8
_gc_log_flush                       if TRUE, flush redo log before a current block transfer            TRUE
_gc_long_query_threshold            threshold for long running query                                   0
_gc_max_downcvt                     maximum downconverts to process at one time                        256
_gc_maximum_bids                    maximum number of bids which can be prepared                       0

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gc_no_fairness_for_clones          if TRUE, no fairness if we serve a clone                           TRUE
_gc_object_queue_max_length         maximum length for an object queue                                 0
_gc_override_force_cr               if TRUE, try to override force-cr requests                         TRUE
_gc_persistent_read_mostly          if TRUE, enable persistent read-mostly locking                     TRUE
_gc_policy_minimum                  dynamic object policy minimum activity per minute                  1500
_gc_policy_time                     how often to make object policy decisions in minutes               10
_gc_read_mostly_flush_check         if TRUE, optimize flushes for read mostly objects                  FALSE
_gc_read_mostly_locking             if TRUE, enable read-mostly locking                                TRUE
_gcr_enable_high_cpu_kill           if TRUE, GCR may kill foregrounds under high load                  FALSE
_gcr_enable_high_cpu_rm             if TRUE, GCR may enable a RM plan under high load                  FALSE
_gcr_enable_high_cpu_rt             if TRUE, GCR may boost bg priority under high load                 FALSE

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gcr_high_cpu_threshold             minimum amount of CPU process must consume to be kill target       10
_gcr_use_css                        if FALSE, GCR wont register with CSS nor use any CSS feature       TRUE
_gc_sanity_check_cr_buffers         if TRUE, sanity check CR buffers                                   FALSE
_gcs_disable_remote_handles         disable remote client/shadow handles                               FALSE
_gcs_disable_skip_close_remastering if TRUE, disable skip close optimization in remastering            FALSE
_gc_serve_high_pi_as_current        if TRUE, use a higher clone scn when serving a pi                  TRUE
_gcs_fast_reconfig                  if TRUE, enable fast reconfiguration for gcs locks                 TRUE
_gcs_latches                        number of gcs resource hash latches to be allocated per LMS proces 64
                                    s

_gcs_pkey_history                   number of pkey remastering history                                 4000

NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_gcs_process_in_recovery            if TRUE, process gcs requests during instance recovery             TRUE
_gcs_resources                      number of gcs resources to be allocated
_gcs_res_per_bucket                 number of gcs resource per hash bucket                             4
_gcs_shadow_locks                   number of pcm shadow locks to be allocated
_gc_statistics                      if TRUE, kcl statistics are maintained                             TRUE
_gcs_testing                        GCS testing parameter                                              0
_gc_transfer_ratio                  dynamic object read-mostly transfer ratio                          2
_gc_undo_affinity                   if TRUE, enable dynamic undo affinity                              TRUE
_gc_undo_block_disk_reads           if TRUE, enable undo block disk reads                              TRUE
_gc_use_cr                          if TRUE, allow CR pins on PI and WRITING buffers                   TRUE
_gc_vector_read                     if TRUE, vector read current buffers                               TRUE

64 rows selected.

通过对比发现_gc_read_mostly_locking参数在1号实例中为true,2号实例为false

将所有实例中的_gc_read_mostly_locking参数设置为true

SQL> alter system set "_gc_read_mostly_locking"=true scope=spfile sid='*';

System altered.

重启2号实例成功

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5.3447E+10 bytes
Fixed Size                  2265864 bytes
Variable Size            1.3019E+10 bytes
Database Buffers         4.0265E+10 bytes
Redo Buffers              160698368 bytes
Database mounted.
Database opened.

原因是因为之前有人修改过_gc_read_mostly_locking隐藏参数,只是只修改了1号实例。

mysqld –skip-grant-tables

mysqld的–skip-grant-tables选项
这个选项会导致不使用权限系统来启动服务器,它将让任何用户可以访问服务器并且不受限制的访问所有数据库。在不使用授权表启动服务器后可以通过shell来执行mysqladmin flush-privileges或mysqladmin reload命令或者在连接到服务器后执行flush privileges语句来让正在运行的服务器再次使用授权表。

使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL. SUCCESS! 


[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

现在可以执行mysqladin flush-privileges命令让正在运行的服务器再次使用授权表

[mysql@localhost ~]$ mysqladmin  flush-privileges

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

再次使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS! 

[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

现在可以执行mysqladin reload命令让正在运行的服务器再次使用授权表

mysql@localhost ~]$ mysqladmin reload

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

再次使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL. SUCCESS! 


[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

现在可以执行flush privileges语句让正在运行的服务器再次使用授权表

mysql> flush privileges;
Query OK, 0 rows affected (0.12 sec)

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

–skip-grant-tables选项也可以在选项文件my.cnf中进行设置。这个选项还会导致服务器在启动过程中禁止加载用户定义函数(udf),调度事件和安装插件语句中安装的插件。为了以任何方式来加载插件,使用–plugin-load选项。–skip-grant-tables选项也会导致disabled_storage_engines系统变量失效。

flush privileges语句可以在服务器启动后通过执行其它操作来隐式执行。例如在升级过程中mysql_upgrade程序就会刷新权限。

DM并行查询

倘若没有并行查询技术,一个串行执行的查询语句只能利用CPU或者磁盘设备中的一个,而不能利用整个计算机的处理能力。并行查询技术的出现,使得单个SQL语句能利用多个CPU和磁盘设备的处理能力。其优势在于可以通过多个线程来处理查询任务,从而提高查询的效率。

达梦数据库为具有多个CPU的数据库服务器提供并行查询的功能,以优化查询任务的性能。数据库服务器只有具有多个CPU,才能使用并行执行查询操作,来提高查询任务的速度。

达梦数据库通过三个步骤来完成并行查询:首先,确定并行任务数;其次,确定并行工作线程数;最后,执行查询。并行查询相关参数见下表:
参数名 缺省值 属性 说明
MAX_PARALLEL_DEGREE 1 动态,会话级 用来设置默认并行任务个数。取值范围:1~128。缺省值1,表示无并行任务。当PARALLEL_POLICY值为1时该参数值才有效。

parallel_policy 0 静态 用来设置并行策略。取值范围:0、1和2,缺省为0。其中,0表示不支持并行;1表示自动并行模式;2表示手动并行模式。

PARALLEL_THRD_NUM 10 静态 用来设置并行工作线程个数。取值范围:1~1024。

当开启自动并行(PARALLEL_POLICY=1)时,参数MAX_PARALLEL_DEGREE生效,控制并行查询最多使用的线程数。MAX_PARALLEL_DEGREE缺省值为1,表示不并行。此时若指定参数对应的HINT “PARALLEL”,则使用HINT值;

当开启手动并行(PARALLEL_POLICY=2)时,参数MAX_PARALLEL_DEGREE失效,用户需要在语句中使用此参数对应的HINT “PARALLEL”指定语句的并行度,否则不并行。

1.在INI参数中设置默认值
INI参数MAX_PARALLEL_DEGREE设置最大并行任务个数。取值范围:1~128。缺省值1,表示无并行任务,此参数仅在PARALLEL_POLICY值为1时才有效。
例如,在INI参数中将MAX_PARALLEL_DEGREE设置为3的格式如下:
MAX_PARALLEL_DEGREE 3

先查看max_parallel_degree的缺省值

SQL> select sf_get_para_value(1,'MAX_PARALLEL_DEGREE');

LINEID     SF_GET_PARA_VALUE(1,'MAX_PARALLEL_DEGREE')
---------- ------------------------------------------
1          1

used time: 150.207(ms). Execute id is 197.

下面的查询将查看dm.ini文件中设置的max_parallel_degree参数值

SQL> select * from v$dm_ini where para_name='MAX_PARALLEL_DEGREE';

LINEID     PARA_NAME           PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION                      PARA_TYPE
---------- ------------------- ---------- --------- --------- ------- ---------- ---------- -------------------------------- ---------
1          MAX_PARALLEL_DEGREE 1          1         128       N       1          1          Maximum degree of parallel query SESSION

used time: 50.228(ms). Execute id is 198.

下面的查询将查看内存中的max_parallel_degree参数值

SQL> select * from v$parameter where name='MAX_PARALLEL_DEGREE';

LINEID     ID          NAME                TYPE    VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ------------------- ------- ----- --------- ---------- --------------------------------
1          274         MAX_PARALLEL_DEGREE SESSION 1     1         1          Maximum degree of parallel query

used time: 7.440(ms). Execute id is 199.

现在执行下面的命令来同时修改内存与dm.ini文件中的max_parallel_degree参数为3

SQL> call sp_set_para_value(1,'MAX_PARALLEL_DEGREE',3);
DMSQL executed successfully
used time: 7.183(ms). Execute id is 200.

执行下面的查询可以看到max_parallel_degree参数修改为3了

SQL> select sf_get_para_value(1,'MAX_PARALLEL_DEGREE');

LINEID     SF_GET_PARA_VALUE(1,'MAX_PARALLEL_DEGREE')
---------- ------------------------------------------
1          3

used time: 5.544(ms). Execute id is 201.

从查询返回的sess_value与file_value都为3可以确定max_parallel_degree在dm.ini文件中已经被修改了

SQL> select * from v$dm_ini where para_name='MAX_PARALLEL_DEGREE';

LINEID     PARA_NAME           PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION                      PARA_TYPE
---------- ------------------- ---------- --------- --------- ------- ---------- ---------- -------------------------------- ---------
1          MAX_PARALLEL_DEGREE 3          1         128       N       3          3          Maximum degree of parallel query SESSION

used time: 6.910(ms). Execute id is 202.

从查询返回的sys_value为3可以在内存中max_parallel_degree也已经被修改了。

SQL> select * from v$parameter where name='MAX_PARALLEL_DEGREE';

LINEID     ID          NAME                TYPE    VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ------------------- ------- ----- --------- ---------- --------------------------------
1          274         MAX_PARALLEL_DEGREE SESSION 3     3         3          Maximum degree of parallel query

used time: 6.335(ms). Execute id is 203.

然后,使用一般的SQL语句查询即可执行并行查询,不需要使用HINT。如:

SQL> explain SELECT * FROM SYSOBJECTS;

1   #NSET2: [0, 1694, 396]
2     #LOCAL COLLECT: [0, 1694, 396]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [0, 1694, 396]; exp_num(17), is_atom(FALSE)
4         #CSCN2: [0, 1694, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

used time: 0.951(ms). Execute id is 0.

执行计划中的LOCAL COLLECT 代表:本地并行下数据收集处理,代替LOCAL GATHER。

2.在SQL语句中使用“PARALLEL”关键字特别指定
当PARALLEL_POLICY=2时,需要在SQL语句中通过“PARALLEL”HINT指定并行度,否则不并行。若PARALLEL_POLICY=1,则SQL语句中使用的“PARALLEL”HINT总是优先于MAX_PARALLEL_DEGREE参数设置。“PARALLEL”关键字的用法是在数据查询语句的SELECT关键字后,增加HINT子句来实现。

HINT语法格式如下:
/*+ PARALLEL([< 表名>] < 并行任务个数>) */

例如,下面的例子中,即使已经设置了MAX_PARALLEL_DEGREE默认值3,但实际使用的为PARALLEL指定的任务个数4:

SQL> explain SELECT /*+ PARALLEL(4) */ * FROM SYSOBJECTS;

1   #NSET2: [0, 1694, 396]
2     #LOCAL COLLECT: [0, 1694, 396]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [0, 1694, 396]; exp_num(17), is_atom(FALSE)
4         #CSCN2: [0, 1694, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

used time: 0.967(ms). Execute id is 0.

另外,每个语句中仅能设置一次并行任务个数,如果设置了多次,则以最后一次设置为准,而且任务个数在全语句中生效。

例如,下面的例子中,使用的并行任务个数为2。

SQL> call sp_set_para_value(1,'MAX_PARALLEL_DEGREE',1);
DMSQL executed successfully
used time: 6.554(ms). Execute id is 211.
SQL> select sf_get_para_value(1,'MAX_PARALLEL_DEGREE');

LINEID     SF_GET_PARA_VALUE(1,'MAX_PARALLEL_DEGREE')
---------- ------------------------------------------
1          1

used time: 5.569(ms). Execute id is 212.

SQL> explain SELECT /*+ PARALLEL(1) *//*+ PARALLEL(2) */ * FROM SYSOBJECTS;

1   #NSET2: [0, 1694, 396]
2     #LOCAL COLLECT: [0, 1694, 396]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [0, 1694, 396]; exp_num(17), is_atom(FALSE)
4         #CSCN2: [0, 1694, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

used time: 1.067(ms). Execute id is 0.

这种方式能够为单条查询语句设置额外的并行任务个数,以此来提高某些特殊查询任务的性能。

在执行并行查询任务之前,您需要指定完成该任务的并行工作线程数。值得注意的是,实际使用的线程数并非总是等于并行工作线程数。并行工作线程数是在INI参数中设定的,实际使用并行工作线程数是根据系统的实际状况确定的。
1. 并行工作线程数,在INI参数中设定
首先,使用PARALLEL_POLICY参数来设置并行策略。取值范围:0、1和2,默认值0。其中,0表示不支持并行;1表示自动并行模式;2表示手动并行模式。

当开启本地并行(PARALLEL_POLICY>0)时,使用PARALLEL_THRD_NUM指定本地并行查询使用的线程数,取值范围为1~1024,缺省值为10。需要注意的是,若PARALLEL_POLICY=1,如果PARALLEL_THRD_NUM=1, 则按照CPU个数创建并行线程。

例如,设置并行策略PARALLEL_POLICY为2,即手动设置并行工作线程数;同时,设置并行工作线程数PARALLEL_THRD_NUM为4个。

SQL> call sp_set_para_value(2,'PARALLEL_POLICY',2);
DMSQL executed successfully
used time: 6.942(ms). Execute id is 223.
SQL> call sp_set_para_value(2,'PARALLEL_THRD_NUM',4);
DMSQL executed successfully
used time: 6.871(ms). Execute id is 224.

当然,并非所有的查询都适合使用并行查询。大量占用CPU 周期的查询最适合采用并行查询的功能。例如,大型表的连接查询、大量数据的聚合和大型结果集的排序等都很适合采用并行查询。对于简单查询(常用于事务处理应用程序)而言,执行并行查询所需的额外协调工作会大于潜在的性能提升。所以,数据库管理员在确定是否需要使用并行策略的时候,需要慎重。

2. 实际使用的线程数,达梦数据库会根据每个并行查询操作自动检测
实际使用线程数是数据库在查询计划执行时初始化的时候确定的。也就是说,这不需要用户去干预,而是系统根据并行任务数和实际空闲的并行工作线程数来确定的。此操作所依据的条件如下:首先,检测达梦数据库是否运行在具有多个CPU的计算机上。只有具有多个CPU 的计算机才能使用并行查询。这是一个硬性的限制条件。其次,检测可用的空闲工作线程是否足够。并行查询到底采用多少线程数,除了跟操作的复杂程度相关外,还跟当时的服务器状态相关,如是否有足够的可用的空闲工作线程数量等。每个并行查询操作都要求一定的工作线程数量才能够执行;而且执行并行计划比执行串行计划需要更多的线程,所需要的线程数量也会随着任务个数的提高而增加。当无法满足特定并行查询执行的线程要求时,数据库引擎就会自动减少任务个数,甚至会放弃并行查询而改为串行计划。所以,即使同一个操作在不同时候可能会采用不同的线程数。

例如,即使设置并行工作线程数为4。而实际使用的线程数可能只有3个,或者更少。

使用手动并行模式时,只需要在INI参数中设置好如下2个参数,然后执行并行SQL查询语句时,需手动指定当前并行任务个数。若不指定,将不使用并行。设置的2个参数如下:
PARALLEL_POLICY 2
PARALLEL_THRD_NUM 4

使用自动并行模式时,一般指定如下三个参数:
MAX_PARALLEL_DEGREE 3
PARALLEL_POLICY 1
PARALLEL_THRD_NUM 10
另外,当PARALLEL_POLICY为0时,即使有并行任务,也不支持并行。

然后,执行语法格式类似“SELECT * FROM SYSOBJECTS;”的并行SQL语句即可,本条语句使用默认并行任务数3。

当然,如果单条查询语句不想使用默认并行任务数,可以通过在SQL语句中增加HINT,通过“PARALLEL”关键字来特别指定。此时,执行的并行SQL语句格式为“SELECT /*+ PARALLEL(SYSOBJECTS 4) */ * FROM SYSOBJECTS;”,本条语句使用的并行任务数为4。

DM7闪回与闪回查询

闪回
当用户操作不慎导致错误的删改数据时,非常希望有一种简单快捷的方式可以恢复数据。闪回技术,就是为了用户可以迅速处理这种数据逻辑损坏的情况而产生的。

闪回技术主要是通过回滚段存储的UNDO记录来完成历史记录的还原。设置ENABLE_FLASHBACK为1后,开启闪回功能。DM会保留回滚段一段时间,回滚段保留的时间代表着可以闪回的时间长度。由UNDO_RETENTION参数指定。

SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK');

LINEID     SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK')
---------- ---------------------------------------
1          0

used time: 204.313(ms). Execute id is 62.
SQL> select sf_get_para_value(1,'UNDO_RETENTION');

LINEID     SF_GET_PARA_VALUE(1,'UNDO_RETENTION')
---------- -------------------------------------
1          90

used time: 6.236(ms). Execute id is 63.

下面修改动态参数ENABLE_FLASHBACK,scope=1同时修改内存和dm.ini文件

SQL> call sp_set_para_value(1,'ENABLE_FLASHBACK',1);
DMSQL executed successfully
used time: 13.216(ms). Execute id is 64.
SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK');

LINEID     SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK')
---------- ---------------------------------------
1          1

used time: 5.703(ms). Execute id is 65.

回滚段保留的时间缺省值为90秒,我们要修改它为1天

SQL> select sf_get_para_value(1,'UNDO_RETENTION');

LINEID     SF_GET_PARA_VALUE(1,'UNDO_RETENTION')
---------- -------------------------------------
1          90

used time: 6.236(ms). Execute id is 63.

SQL> call sp_set_para_value(1,'UNDO_RETENTION',86400);                            
DMSQL executed successfully
used time: 7.155(ms). Execute id is 74.
SQL> select sf_get_para_value(1,'UNDO_RETENTION');

LINEID     SF_GET_PARA_VALUE(1,'UNDO_RETENTION')
---------- -------------------------------------
1          86400

used time: 5.877(ms). Execute id is 75.

开启闪回功能后,DM会在内存中记录下每个事务的起始时间和提交时间。通过用户指定的时刻,查询到该时刻的事务号,结合当前记录和回滚段中的UNDO记录,就可以还原出特定事务号的记录。即指定时刻的记录状态。从而完成闪回查询。闪回查询功能完全依赖于回滚段管理,对于DROP等误操作不能恢复。闪回特性可应用在以下方面:
1. 自我维护过程中的修复:当一些重要的记录被意外删除,用户可以向后移动到一个时间点,查看丢失的行并把它们重新插入现在的表内恢复;

2. 用于分析数据变化:可以对同一张表的不同闪回时刻进行链接查询,以此查看变化的数据。

闪回查询
当系统INI参数ENABLE_FLASHBACK置为1时,闪回功能开启,可以进行闪回查询。MPP环境不支持闪回查询。

SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK');

LINEID     SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK')
---------- ---------------------------------------
1          1

used time: 5.703(ms). Execute id is 65.

[root@shard1 jydm]# strings dm.ini | grep flash
ENABLE_FLASHBACK            =  1                    #Whether to enable flashback function

闪回查询子句
闪回查询子句的语法,是在数据查询语句(参考第4章)的基础上,为FROM子句增加了闪回查询子句。
语法格式
< 闪回查询子句>::=WHEN |

参数
1.time_exp 一个日期表达式,一般用字符串方式表示
2.trxid 指定事务ID号

语句功能
用户通过闪回查询子句,可以得到指定表过去某时刻的结果集。指定条件可以为时刻,或事务号

使用说明
1.闪回查询只支持普通表(包括加密表与压缩表)、临时表和堆表,不支持水平分区表、垂直分区表、列存储表、外部表与视图;

2.闪回查询中trxid的值,一般需要由闪回版本查询(见下节)的伪列来确定。实际使用中多采用指定时刻的方式。

例1闪回查询特定时刻的PERSON_TYPE表。
查询PERSON_TYPE表。

SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表

used time: 15.321(ms). Execute id is 78.

在插入数据之前记录时间,在闪回查询时使用

SQL> select sysdate;

LINEID     SYSDATE                    
---------- ---------------------------
1          2019-12-01 23:34:12.761683

used time: 0.480(ms). Execute id is 79.

SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('防损员');
affect rows 1

used time: 0.615(ms). Execute id is 80.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('保洁员');
affect rows 1

used time: 0.562(ms). Execute id is 81.
SQL> commit;
executed successfully
used time: 16.237(ms). Execute id is 82.



SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          7             防损员
6          8             保洁员

6 rows got

used time: 0.626(ms). Execute id is 83.

使用闪回查询取得2019-12-01 23:34:12时刻的数据。此时刻在插入数据的操作之前,可见此时的结果集不应该有2019-12-01 23:34:12时刻以后插入的数据。

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23:34:12';

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表

used time: 1.416(ms). Execute id is 84.

在2019-12-01 23:39:26时刻删除数据,并提交。

SQL> select sysdate;

LINEID     SYSDATE                    
---------- ---------------------------
1          2019-12-01 23:39:26.865328

used time: 0.580(ms). Execute id is 85.

SQL> DELETE FROM PERSON.PERSON_TYPE WHERE PERSON_TYPEID > 5;
affect rows 2

used time: 1.797(ms). Execute id is 86.
SQL> commit;
executed successfully
used time: 19.834(ms). Execute id is 87.

SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          5             防损员
used time: 0.691(ms). Execute id is 88.

使用闪回查询得到删除前的数据。

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23:39:26';

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          5             防损员
6          6             保洁员

6 rows got

used time: 1.262(ms). Execute id is 89.

闪回查询指定TRXID的PERSON_TYPE表。
要获得TRXID信息,需要通过闪回版本查询的伪列VERSIONS_ENDTRXID。

在2019-12-01 23:45:27 时刻修改数据,并提交。

SQL> select sysdate;

LINEID     SYSDATE                    
---------- ---------------------------
1          2019-12-01 23:48:27.983996

used time: 0.509(ms). Execute id is 90.

SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             防损员
6          10            保洁员

6 rows got

used time: 0.515(ms). Execute id is 97.

SQL> UPDATE PERSON.PERSON_TYPE SET NAME='保安员' WHERE PERSON_TYPEID=9;
affect rows 1

used time: 1.460(ms). Execute id is 99.
SQL> commit;
executed successfully
used time: 16.640(ms). Execute id is 100.
SQL> UPDATE PERSON.PERSON_TYPE SET NAME='收银员' WHERE PERSON_TYPEID=9;
affect rows 1

used time: 1.851(ms). Execute id is 101.
SQL> commit;
executed successfully
used time: 16.781(ms). Execute id is 102.

SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             收银员
6          10            保洁员

6 rows got

used time: 0.516(ms). Execute id is 103.

进行闪回版本查询,确定TRXID。

SQL> SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-01 23:48:27.983996' AND SYSDATE;

LINEID     VERSIONS_ENDTRXID    NAME    
---------- -------------------- --------
1          NULL                 采购经理
2          NULL                 采购代表
3          NULL                 销售经理
4          NULL                 销售代表
5          NULL                 收银员
6          NULL                 保洁员
7          749195               保安员

7 rows got

used time: 1.384(ms). Execute id is 104.

根据TRXID确定版本。

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749195;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             保安员
6          10            保洁员

6 rows got

used time: 1.261(ms). Execute id is 105.

第二次更新的事务ID为749195,那么第一次更新的事务ID为749194

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749194;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             防损员
6          10            保洁员

6 rows got

闪回版本查询
语法格式
< 闪回版本查询子句>::=VERSIONS BETWEEN |

参数
1.time_exp 日期表达式,一般用字符串方式表示。time_exp1表示起始时间,time_exp2表示结束时间

2. trxid 指定事务ID号,整数表示。trxid1表示起始trxid,trxid2表示结束trxid

使用说明
1.闪回版本查询支持普通表(包括加密表与压缩表)、临时表和堆表,不支持水平分区表、垂直分区表、列存储表、外部表与视图;

2.支持伪列,作为闪回版本查询的辅助信息。
伪列 说明
VERSIONS_START{TRXID|TIME} 起始TRXID或时间戳
VERSIONS_END{TRXID|TIME} 提交TRXID或时间戳。如果该值为NULL,表示行版本仍然是当前版本
VERSIONS_OPERATION 在行上的操作(I=Insert,D=Delete,U=Update)

语句功能
用户通过闪回版本查询子句,可以得到指定表过去某个时间段内,事务导致记录变化的全部记录。指定条件可以为时刻,或事务号。

例1 闪回版本查询指定时间段内,PERSON_TYPE表的记录变化

SQL> select sysdate;

LINEID     SYSDATE                    
---------- ---------------------------
1          2019-12-02 00:00:18.221877

used time: 0.662(ms). Execute id is 107.

在2019-12-02 00:00:18时刻修改数据,并提交。

SQL> UPDATE PERSON.PERSON_TYPE SET NAME='打字员' WHERE PERSON_TYPEID=9;
affect rows 1

used time: 1.758(ms). Execute id is 110.
SQL> commit;
executed successfully
used time: 16.964(ms). Execute id is 111.
SQL> 
SQL> UPDATE PERSON.PERSON_TYPE SET NAME='卫生员' WHERE PERSON_TYPEID=9;
affect rows 1

used time: 1.268(ms). Execute id is 112.
SQL> commit;
executed successfully
used time: 15.983(ms). Execute id is 113.
SQL> SELECT * FROM PERSON.PERSON_TYPE;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             卫生员
6          10            保洁员

6 rows got

used time: 0.669(ms). Execute id is 114.

进行闪回版本查询,获得指定时间段内变化的记录。

SQL> SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-02 00:00:17' AND SYSDATE;

LINEID     VERSIONS_ENDTRXID    NAME    
---------- -------------------- --------
1          NULL                 采购经理
2          NULL                 采购代表
3          NULL                 销售经理
4          NULL                 销售代表
5          NULL                 卫生员
6          NULL                 保洁员
7          749197               打字员

7 rows got

used time: 1.412(ms). Execute id is 115.

第二次更新的事务ID为749197

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749197;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             打字员
6          10            保洁员

6 rows got

used time: 1.371(ms). Execute id is 120.

第一次更新的事务ID为749196

SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749196;

LINEID     PERSON_TYPEID NAME    
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             收银员
6          10            保洁员

6 rows got

used time: 0.585(ms). Execute id is 121.

闪回事务查询
闪回事务查询提供系统视图V$FLASHBACK_TRX_INFO供用户查看在事务级对数据库所做的更改。根据视图信息,可以确定如何还原指定事务或指定时间段内的修改。

使用说明
系统视图名为V$FLASHBACK_TRX_INFO,定义如下所示。

SQL> desc V$FLASHBACK_TRX_INFO

LINEID     NAME             TYPE$         NULLABLE
---------- ---------------- ------------- --------
1          START_TRXID      BIGINT        Y        事务中第一个DML的TRXID
2          START_TIMESTAMP  DATETIME(6)   Y        事务中第一个DML的时间戳
3          COMMIT_TRXID     BIGINT        Y        提交事务的TRXID
4          COMMIT_TIMESTAMP DATETIME(6)   Y        提交事务时的时间戳
5          LOGIN_USER       VARCHAR(256)  Y        拥有事务的用户
6          UNDO_CHANGE#     INTEGER       Y        记录修改顺序序号
7          OPERATION        CHAR(1)       Y        DML操作类型 D:删除;U:修改;I:插入;N:更新插入(专门针对CLUSTER PRIMARY KEY的插入);C:事务提交;P:预提交记录;O:default
8          TABLE_NAME       VARCHAR(256)  Y        DML 修改的表     
9          TABLE_OWNER      VARCHAR(256)  Y        DML修改表的拥有者      
10         ROW_ID           BIGINT        Y        DML修改行的ROWID
11         UNDO_SQL         VARCHAR(3900) Y        撤销DML操作的SQL语句

11 rows got

used time: 88.801(ms). Execute id is 122.

查询指定时间之后的事务信息,可为闪回查询操作提供参考

SQL> SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > '2019-12-01 23:13:28';

LINEID     START_TRXID          START_TIMESTAMP             COMMIT_TRXID         COMMIT_TIMESTAMP            LOGIN_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID               UNDO_SQL
---------- -------------------- --------------------------- -------------------- --------------------------- ---------- ------------ --------- ---------- ----------- -------------------- --------
1          749189               2019-12-01 23:13:28.000000  749190               2019-12-01 23:33:57.588000  SYSDBA     16           C         NULL       NULL        NULL                 NULL

used time: 1.437(ms). Execute id is 126.

mysqldump+mysqlbinlog执行备份与还原

服务器的二进制日志文件由用来描述修改数据库内容的事件组成。服务器以二进制方式来写这些文件。为了以文本格式来显示这些内容,可以使用mysqlbinlog工具。也可以使用mysqlbinlog来显示在复制环境中由从从slave服务器所写入中relay日志文件中的内容,因为其格式与二进制日志文件格式一样。
mysqlbinlog的使用语法如下:
Usage: mysqlbinlog [options] log-files

下面的命令用来显示名为binlog.000001的二进制日志文件的内容:

[mysql@localhost ~]$ mysqlbinlog  /mysqldata/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191115 15:39:01 server id 1  end_log_pos 123 CRC32 0x2d9d7b4f  Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU97nS0=
'/*!*/;
# at 123
#191115 15:39:01 server id 1  end_log_pos 154 CRC32 0x42dcd61c  Previous-GTIDs
# [empty]
# at 154
#191115 15:51:15 server id 1  end_log_pos 219 CRC32 0x5bc0b021  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#191115 15:51:15 server id 1  end_log_pos 308 CRC32 0x7261eacb  Query   thread_id=2     exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1573804275/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gb2312 *//*!*/;
SET @@session.character_set_client=24,@@session.collation_connection=24,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
truncate table person
/*!*/;
# at 308
#191115 15:51:38 server id 1  end_log_pos 373 CRC32 0x6d2e39aa  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 373
#191115 15:51:38 server id 1  end_log_pos 454 CRC32 0x7871c2ea  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
BEGIN
/*!*/;
# at 454
# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar
SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
insert into person(last_name,first_name,birth,death) values('yong','jing','1985-02-28',null)
/*!*/;
# at 654
#191115 15:51:38 server id 1  end_log_pos 736 CRC32 0xc5450308  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
COMMIT
/*!*/;
# at 736
#191115 15:51:45 server id 1  end_log_pos 801 CRC32 0xc2c892b8  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 801
#191115 15:51:45 server id 1  end_log_pos 882 CRC32 0x51a9cd5c  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
BEGIN
/*!*/;
# at 882
# at 914
#191115 15:51:45 server id 1  end_log_pos 914 CRC32 0x40a98fae  Intvar
SET INSERT_ID=2/*!*/;
#191115 15:51:45 server id 1  end_log_pos 1082 CRC32 0x3396c40d         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
insert into person(last_name,first_name,birth,death) values('yan','huang','1990-08-25',null)
/*!*/;
# at 1082
#191115 15:51:45 server id 1  end_log_pos 1164 CRC32 0xf6f6efad         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
COMMIT
/*!*/;
# at 1164
#191115 15:51:53 server id 1  end_log_pos 1229 CRC32 0x55b50dbe         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1229
#191115 15:51:53 server id 1  end_log_pos 1310 CRC32 0xd0f6a335         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
BEGIN
/*!*/;
# at 1310
# at 1342
#191115 15:51:53 server id 1  end_log_pos 1342 CRC32 0xfad94baf         Intvar
SET INSERT_ID=3/*!*/;
#191115 15:51:53 server id 1  end_log_pos 1508 CRC32 0x26c5b3bb         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
insert into person(last_name,first_name,birth,death) values('yali','ye','1994-12-23',null)
/*!*/;
# at 1508
#191115 15:51:53 server id 1  end_log_pos 1590 CRC32 0xbb6a2b4c         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面输出了binlog.000001二进制日志文件中所包含的内容。对于基于语句的日志,事件信息包括SQL语句,执行语句的服务器ID,语句被执行的时间戳,执行时间等。对于基于行记录的日志,事件信息指示行的改变而不是SQL语句。

# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2
exec_time=0     error_code=0 SET TIMESTAMP=1573804298/*!*/;

第一行,at后面的数字指示事件在二进制日志文件中的偏移量或开始位置。

第二行是以日期和时间开始指示语句开始执行的时间。对于复制来说,这个时间戳将传播到从属服务器。server id是事件起源服务器的server_id值。end_log_pos指示下一个事件开始的位置(它是当前事件的终止位置+1)。thread_id那个线程来执行这个事件。exec_time是在主服务器上执行事件所花费的时间。在从属服务器上,它是从属服务器上执行结束时间减去主服务器上的执行开始时间的差值。这种差值可以作为一种指示来表示复制进程落后于主服务器多长时间。error_code指示执行事件的结果。零意味着没有出现错误。

mysqlbinglog的输出可以用来重新执行日志文件中的语句(例如,通过使用mysql工具)。这在服务器崩溃时用来恢复是很有用的。

正常来说,使用mysqlbinlog直接读取二进制日志文件并应用它们到本地MySQL服务器。它也可以通过使用–read-from-remote-server选项来从远程服务器上读取二进制日志文件。为了读取远程二进制日志文件,连接参数选项可以被指定用来指示如何连接服务器。这些选项有–host,–password,–port,–protocol,–socket和–user,除非使用了–read-from-remote-server选项否则它们会被忽略。

当对一个大的二进制日志文件执行mysqlbinlog时,要小心注意文件系统是否有足够的空间来存储结果文件。为了配置目录给mysqlbinlog临时使用存储文件,使用TMPDIR环境变量。

使用mysqlbinlog备份二进制日志文件
缺省情况下,mysqlbinlog读取二进制日志文件并以文本格式来显示它的内容。这能让你更容易使用文件来检查发生的事件和重新执行它们(例如,通过使用输出作为mysql的输入)。mysqlbinlog可以直接从本地文件系统中读取日志文件或者使用–read-from-remote-server选项来连接远程服务器并从远程服务器上读取二进制日志文件。mysqlbinlog以文本格式将内容输出到标准输出,或者如果指定了–result-file=file_name选项会将内容写入文件。

mysqlbinlog可以读了二进制日志文件并将其包含的内容以二进制格式而不是文本格式写入新文件。这种能力可以让你以原来的格式来备份二进制日志文件。mysqlbinlog可以生成静态备份,在备份一组日志文件时当备份完最后的文件时而停止。它也可以生成一种连续(live)备份,当备份到最后的日志文件时仍然保持对服务器的连接并当生成新的事件时继续复制新的事件。在连续备份操作时,mysqlbinlog会运行到连接中断为止(比如,服务器退出)或mysqlbinlog被强制中断为止。当连接中断,mysqlbinlog不会进行等待并重新进行连接,不像从属复制服务器那样。为了在服务器重启之后继续一个live备份,必须重新启动mysqlbinlog。

二进制日志文件备份要求在调用mysqlbinlog时最少要使用两个选项:
.–read-from-remote-server(或-R)选项来告诉mysqlbinlog连接到一个服务器并读取它的二进制日志文件(这类似于一个从属复制服务器连接到它的主服务器).

.–raw选项告诉mysqlbinlog以原始(二进制)格式输出,而不是文本格式。

与–read-from-remote-server一起通常还指定其它选项:–host指示服务器运行在哪里,并且可能需要指定连接选项–user和password。

与–raw联合使用的几个其它选项:.–stop-never:在读取到最后日志文件后保持对服务器的连接并继续读取新的事件。

.–stop-never-slave-server-id=id:当–stop-never被使用时mysqlbinlog报告的服务器ID,缺省值65535。这可以避免与从属服务器或其它的mysqlbinlog进程的ID冲突。

.–result-file:输出文件名的前缀

为了使用mysqlbinlog来备份服务器的二进制日志文件,你必须指定在服务器上真实存在的文件名。如果你不知道文件名,连接到服务器并使用show binary logs语句来查看当前的日志文件名。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.00 sec)

使用这些信息可以使用mysqlbinlog来备份二进制日志文件到当前目录:
为了对binlog.000130到binlog.000132的日志文件进行静态备份,使用以下命令:
mysqlbinlog –read-from-remote-server –host=host_name –raw
binlog.000130 binlog.000131 binlog.000132

mysqlbinlog –read-from-remote-server –host=host_name –raw
–to-last-log binlog.000130

第一个命令显式指定每个文件名。第二个只指定了第一个日志文件并使用了–to-last-log来读取到最后一个日志文件。在这些命令之间的差异是在mysqlbinlog到达binlog.000132的末尾之前如果服务器打开了binlog.000133文件,第一个命令将不会读取,但第二个命令会读取。

为了进行live备份mysqlbinlog从binlog.000130开始备份现有的日志文件,然后保持对服务器的连接来复制生成的新事件:
mysqlbinlog –read-from-remote-server –host=host_name –raw
–stop-never binlog.000130

使用–stop-never选项,不需要指定–to-last-log来读取最后的日志文件因为这个选项是隐含的

输出文件名
在没有使用–raw选项时,mysqlbinlog会生成文本格式的输出,如果指定–result-file选项,指定将所有输出写入一个文件中。使用–raw选项时,mysqlbinlog会将服务器的每个日志文件转换成一个二进制输出文件。缺省情况下,mysqlbinlog会在当前目录中生成与源日志文件同名的文件。为了修改输出文件名,使用–result-file选项。与–raw联合使用,–result-file选项值将作为前缀来命名输出文件名。

现在对远程服务器中的binlog.000001日志文件进行备份

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw binlog.000001 --result-file=jy_
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql    2530 11月 22 10:24 jy_binlog.000001

可以看到备份的日志文件为以jy_为前缀,其文件名为jy_binlog.000001

使用mysqldump与mysqlbinlog执行备份与还原操作
下面将介绍一个简单的例子显示如何使用mysqldump与mysqlbinlog一起来备份MySQL服务器的数据和二进制日志文件以及在数据丢失时如何使用备份来还原数据。

现在主机上的MySQL服务器的第一个二进制日志文件为binlog.000001

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.01 sec)

使用mysqlbinlog来对二进制日志文件执行连续备份:

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw  --stop-never binlog.000001

[mysql@localhost ~]$ ls -lrt

-rw-r-----. 1 mysql mysql    2530 11月 22 10:38 binlog.000001

创建了一个名为t的测试表并插入了三行记录

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

使用mysqldump来创建一个dump文件作为对MySQL服务器的数据快照。使用–all-databases,–events和–routines来备份所有的数据,–master-data=2用来指示在dump文件中包括当前的二进制日志文件。

[mysql@localhost ~]$ mysqldump --host=192.168.1.250 --port=3306 -uroot -pabcd --all-databases --events --routines --master-data=2> dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 3290497 11月 22 10:51 dump_mysql.sql

现在删除mysql库中的表t

mysql> drop table t;
Query OK, 0 rows affected (0.18 sec)

mysql> desc t;
ERROR 1146 (42S02): Table 'mysql.t' doesn't exist

现在假设mysql库中的表t丢失了,使用最近的dump文件来还原数据:

[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql

还原数据后mysql.t表就恢复了

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| date  | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

现在向mysql.t表中插入一条记录并删除这条记录,然后使用备份的二进制日志文件来重新执行事件来恢复这条记录

mysql> insert into t value(4,'wenyao',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from t where id=4;
Query OK, 1 row affected (0.13 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.01 sec)

我们需要找到插入这条记录在日志文件中的开始与结束的位置

# at 3306211
#191122 11:04:34 server id 1  end_log_pos 3306323 CRC32 0x88f89864      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574391874/*!*/;
insert into t value(4,'wenyao',NULL)
/*!*/;
# at 3306323
#191122 11:04:34 server id 1  end_log_pos 3306354 CRC32 0x966500de      Xid = 1041
COMMIT/*!*/;
# at 3306354
#191122 11:07:26 server id 1  end_log_pos 3306419 CRC32 0x1f3e6e28      Anonymous_GTID  last_committed=160      sequence_number=161     rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3306419
#191122 11:07:26 server id 1  end_log_pos 3306500 CRC32 0x883ecef4      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
BEGIN
/*!*/;
# at 3306500
#191122 11:07:26 server id 1  end_log_pos 3306600 CRC32 0xecae0a57      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
delete from t where id=4

从上面的日志文件内容可以看到插入的开始位置为3306211,结束位置为3306323

现在当前备份的二进制日志文件名为binlog.000001,重新执行事件的命令如下:

[mysql@localhost ~]$ mysqlbinlog --start-position=3306211 --stop-position=3306323 binlog.000001 | mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql
mysql: [Warning] Using a password on the command line interface can be insecure.

现在检查mysql.t表中的记录,可以看到被删除的这条记录恢复了。

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

设置msyqlbinlog 服务器ID
在使用–read-from-remote-server选项来调用mysqlbinlog时,mysqlbinlog会连接到一个MySQL服务器,指定了一个服务器ID来标识它并且从该服务器获取所需要的二进制日志文件。可以使用mysqlbinlog以以下几种方式来从服务器中获取日志文件:
.对文件集指定显式的名字。对每个文件,mysqlbinlog会执行连接操作并执行binlog dump命令。服务器会发送文件并断开连接。每个文件都有一个连接。

.指定开始文件与–to-last-log选项,mysqlbinlog会执行连接并对所有的日志文件执行binlog dump命令。服务器会发送所有日志文件并断开连接

.指定开始文件与–stop-never选项(隐式实现–to-last-log选项的功能),mysqlbinlog会执行连接并对所有日志文件执行binlog dump命令。服务器会发送所有日志文件,但在发送最后一个日志文件后不会断开与服务器的连接。

只有使用–read-from-remote-server选项时,mysqlbinlog使用一个为0的server ID进行连接,它将告诉服务器在发送所请求的日志文件后断开连接。

使用–read-from-remote-server与–stop-never选项时,mysqlbinlog将使用一个非0的server ID进行连接,因此在最后的日志文件发送之后服务器不会断开连接。缺省的server ID为65535,但这个可以通过使用–stop-never-slave-server-id选项来修改。

因此,对于使用前两种方式来获取日志文件人,因为mysqlbinlog指定的server ID为0,所有服务器会断开连接,如果–stop-never选项被指定因为mysqlbinlog指定一个非0的server ID,所以服务器将不会断开连接。

 

mysql_config_editor 配置工具

mysql_config_editor工具能让你在一种加密的登录路径文件.mylogin.cnf中存储审核身份信息。在Windows中这个文件存储在%APPDATA%\MySQL目录中,在非Windows平台上存储在当前用户的home目录中。这种文件可以被MySQL客户端程序读取来获得连接MySQL服务器的审核身份信息。

没有加密的.mylogin.cnf登录路径文件由选项组组成。类似于其它的选项文件。在.mylogin.cnf文件中的每个选项组叫作”login path”登录路径,它是只允许特定选项的组:host,user,password,port和socket。可以把一个登录路径选项组认为是一组选项来指定使用那个用户来连接那个MySQL服务器的信息。下面是没有加密的登录路径信息的
例子:

[client]
user = mydefaultname
password = mydefaultpass
host = 127.0.0.1
[mypath]
user = myothername
password = myotherpass
host = localhost

当调用一个客户端程序连接MySQL服务器时,客户端使用.mylogin.cnf并结合其它的选项文件。它的优先级比其它的选项文件要高,但比在客户端命令行中显式指定的要低。

为了指定一个替代的登录路径文件名,设置MYSQL_TEST_LOGIN_FILE环境变量。这种变量通过mysql_config_editor,通过标准的MySQL客户端(mysql,mysqladmin等)工具和mysql-test-run.pl测试工具所识别。

程序以以下方式使用登录路径文件中的选项组:
.mysql_config_editor在你没有通过–login-path=name选项来显式指定登录路径时缺省情况下会使用client选项组。

.在没有使用–login-path选项的情况下,客户端程序将像从其它选项文件中读取信息一样从登录路径文件中读取选项组。比如:
shell>mysql
缺省情况下,mysql客户端程序将从其它的选项文件中读取[client]和[mysql]选项组,因此也会从登录路径文件中读取这些信息。

.使用–login-path选项,客户端程序额外从登录路径文件中讯取命名的登录路径。仍然与读取其它选项文件中的选项组一样。比如:
shell>mysql –login-path=mypath

mysql客户端程序将从其它选项文件中读取[client]和[mysql]选项组信息和从登录路径文件中读取[client]和[mysql]选项组信息。

.即使当–no-defaults选项被使用,客户端程序也会读取登录路径文件。这允许使用一种安全的方式来指定密码而不而在命令行中指定。

mysql_config_editor会对.mylogin.cnf文件进行加密因此它不能以明文方式被读取,并且当客户端程序解密时,它的内容只在内存中使用。通过这种方式,密码可以以非明文格式存储在文件中并且在以后的命令行或环境变量需要使用时不需要提供输入密码。mysql_config_editor提供了一个print命令来显示登录路径文件的内容,但即使在这种情况下,密码值也会被隐藏,这样就不会以其他用户可以看到的方式出现

通过mysql_config_editor加密阻止密码以明文方式出现在.mylogin.cnf文件中并通过阻止无意暴露密码提供了一种安全措施。例如,如果你在屏幕上以非加密方式来显示my.cnf选项文件中的信息时,它包含的任何密码对于任何人都是可见的。使用.mylogin.cnf文件不是这种情况。但是使用的加密不会阻止一个有决心的攻击者,你不应该认为它是不可攻破的。如果用户能够获得您机器上的系统管理权限来访问您的文件,那么他可以轻松地解密.mylogin.cnf文件

登录路径文件必须对当前用户可读和可写并且对其它用户来说不可以访问。否则,mysql_config_editor会忽略它,并且客户端程序不会使用它。

mysql_config_editor语法:

shell>mysql_config_editor [program options] [command [command options]]

如果登录路径文件不存在,mysql_config_editor会创建它。

mysql_config_editor命令有以下参数选项:
.program_options由通用的mysql_config_editor选项组成。

.command指示对.mylogin.cnf登录路径文件执行的操作。例如,set将写一个登录路径到文件中,remove将删除一个登录路径,print显示登录路径内容。

.command_options指示任何指定给命令的额外选项,比如登录路径名和登录路径所使用的值。

命令名在程序参数集中的位置很重要。例如,这些命令行具有相同的参数,但产生不同的结果:

[mysql@localhost ~]$ mysql_config_editor --help set
mysql_config_editor Ver 1.0 Distrib 5.7.26, for Linux on x86_64
Copyright (c) 2012, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  -?, --help          Display this help and exit.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE

Where command can be any one of the following :
       set [command options]     Sets user name/password/host name/socket/port
                                 for a given login path (section).
       remove [command options]  Remove a login path from the login file.
       print [command options]   Print all the options for a specified
                                 login path.
       reset [command options]   Deletes the contents of the login file.
       help                      Display this usage/help information.

[mysql@localhost ~]$ mysql_config_editor set --help
mysql_config_editor Ver 1.0 Distrib 5.7.26, for Linux on x86_64
Copyright (c) 2012, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL Configuration Utility.

Description: Write a login path to the login file.
Usage: mysql_config_editor [program options] [set [command options]]
  -?, --help          Display this help and exit.
  -h, --host=name     Host name to be entered into the login file.
  -G, --login-path=name 
                      Name of the login path to use in the login file. (Default
                      : client)
  -p, --password      Prompt for password to be entered into the login file.
  -u, --user=name     User name to be entered into the login file.
  -S, --socket=name   Socket path to be entered into login file.
  -P, --port=name     Port number to be entered into login file.
  -w, --warn          Warn and ask for confirmation if set command attempts to
                      overwrite an existing login path (enabled by default).
                      (Defaults to on; use --skip-warn to disable.)

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
host                              (No default value)
login-path                        client
user                              (No default value)
socket                            (No default value)
port                              (No default value)
warn                              TRUE

第一个命令行显示了通用的mysql_config_editor的帮助信息并且忽略了set命令。第二个命令行是显示了set命令的特定帮助信息。

假设想要建立一个client登录路径来定义你的缺省连接参数和额外的remote登录路径来连接远程服务器。想要记录以下信息:
.缺省情况,连接到本地服务器的用户名与密码为root和xxzx7817600
.连接到远程服务器的用户名与密码为root和123456

为了在.mylogin.cnf文件中设置登录路径,使用下面的set命令。每个命令单独一行执行并且根据提示输入相关的密码:

[mysql@localhost ~]$ mysql_config_editor set --login-path=client --host=192.168.1.250 --user=root --password
Enter password: 
[mysql@localhost ~]$ mysql_config_editor set --login-path=remote --host=192.168.1.251 --user=root --password --port=33306
Enter password: 
[mysql@localhost ~]$ 

mysql_config_editor缺省情况下使用client登录路径,因此–login-path=client选项从第一个命令中可以被忽略而不会产生影响。

为了查询mysql_config_editor写入.mylogin.cnf文件的内容,执行print命令:

[mysql@localhost ~]$ mysql_config_editor print --all
[client]
user = root
password = *****
host = 192.168.1.250
[remote]
user = root
password = *****
host = 192.168.1.251
port = 33306

print命令以一组行集合来显示每个登录路径,在方括号中的选项组头指示了登录路径名,接着是登录路径的选项值。密码值以星号出现不是以明文来显示。

如果你在执行print命令时不指定–all选项来显示所有的登录路径或不使用–login-path=name来显示指定的登录路径,如果存会client登录路径,那么缺省情况下print命令只会显示client登录路径。

[mysql@localhost ~]$ mysql_config_editor print
[client]
user = root
password = *****
host = 192.168.1.250

通过上面的例子可以看到一个登录路径文件可以包含多个登录路径。使用这种方式,mysql_config_editor可以简单地多个个性化的登录路径来连接到不同的MySQL服务器或者使用不同的账号连接到指定的服务器。这些登录路径都可以在调用客户端程序时通过使用–login-path选项来使用。例如,为了连接到远程服务器,执行以下命令:

[mysql@localhost ~]$ mysql --login-path=remote  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56674
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

上面的命令mysql从其它选项文件中读取[client]和[mysql]选项组并且从登录路径文件中读取[mysql]和[remote]选项组信息。

为了连接到本地服务器,执行以下命令

[mysql@localhost ~]$ mysql --login-path=client
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

因为缺省情况下mysql读取登录路径文件中的client和mysql登录路径,在这种情况下–login-path选项不会增加其它登录路径。因此上面的命令等价下面的命令:

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

从登录路径文件读取的选项优先于从其他选项文件读取的选项。从登录路径文件中稍后出现的登录路径组读取的选项优先于从文件中较早出现的登录路径组读取的选项。

mysql_config_editor向登录路径文件增加登录路径的顺序就是创建它们的顺序,因此应该先创建更多的通用登录路径,后创建特定登录路径。如果想要在登录路径文件中移动一个登录路径,可以先删除它,然后再重新创建它。例如一个client登录路径很通用,因为所有的客户端程序将会读取它,而mysqldump登录路径只能由mysqldump程序来读取。后指定的选项会覆盖先指定的选项,因此以client,mysqldump顺序来创建登录路径,mysqldump程序能让mysqldump的特定选项覆盖client的选项。

在使用mysql_config_editor的set命令来创建一个登录路径时不需要指定所有可能选项值(主机名,用户名,密码,端口号,socket)。只有指定的值会被写入登录路径。任何丢失而在调用客户端程序连接服务器时所需要的选项可以在其它选项文件或命令行中批定。任何在命令行中指定的选项值会覆盖在登录路径文件或其它选项文件中所指定的选项值。例如,如果在remote登录路径中指定了端口号33306,现在假设远程服务器端口变为3306了,那么连接服务器命令如下:

[mysql@localhost ~]$ mysql --login-path=remote -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56674
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

命令行指定的端口号覆盖登录路径中指定的端口号

可以使用remove命令来删除登录路径或登录路径中的某些选项删除remote登录路径中的–port选项

[mysql@localhost ~]$ mysql_config_editor print --login-path=remote
[remote]
user = root
password = *****
host = 192.168.1.250
port = 33306
[mysql@localhost ~]$ mysql_config_editor remove --login-path=remote --port
[mysql@localhost ~]$ mysql_config_editor print --login-path=remote
[remote]
user = root
password = *****
host = 192.168.1.250

删除删除remote登录路径

[mysql@localhost ~]$ mysql_config_editor remove --login-path=remote
[mysql@localhost ~]$ mysql_config_editor print --login-path=remote

达梦列存储表(HUGE Table)

达梦数据库中,表的数据存储方式分为行存储和列存储。行存储是以记录为单位进行存储的,数据页面中存储的是完整的若干条记录;列存储是以列为单位进行存储的,每一个列的所有行数据都存储在一起,而且一个指定的页面中存储的都是某一个列的连续数据。

Huge File System(检查HFS)是达梦数据库实现的,针对海量数据进行分析的一种高效、简单的列存储机制。列存储表(也称为HUGE表)就是建立在HFS存储机制上的一种表。

HUGE表是建立在自己特有的表空间HTS(HUGE TABLESPACE,即HUGE表空间)上的。最多可创建32767个HUGE表空间,其相关信息存储在动态视图V$HUGE_TABLESPACE中。

这个表空间与普通的表空间不同。普通的表空间,数据是通过段、簇、页来管理的,并且以固定大小(4K、8K、16K、32K)的页面为管理单位;而HUGE表空间是通过HFS存储机制来管理的,它相当于一个文件系统。创建一个HTS,其实就是创建一个空的文件目录(系统中有一个默认HTS,目录名为HMAIN)。在创建一个HUGE表并插入数据时,数据库会在指定的HTS表空间目录下创建一系列的目录及文件。

HUGE表的存储方式有以下几个优点:
1. 同一个列的数据都是连续存储的,可以加快某一个列的数据查询速度;
2. 连续存储的列数据,具有更大的压缩单元和数据相似性,可以获得远优于行存储的压缩效率,压缩的单位是区
3. 条件扫描借助数据区的统计信息进行精确过滤,可以进一步减少IO,提高扫描效率;
4. 允许建立二级索引;
5. 支持以ALTER TABLE的方式添加或者删除PK和UNIQUE约束。

DM支持两种类型的HUGE表:非事务型HUGE表和事务型HUGE表,下面分别进行介绍。
非事务型HUGE表
对非事务型HUGE表的增、删、改是直接对HUGE表进行写操作的,不写UNDO日志,不通过BUFFER缓存,直接操纵文件,速度快,但也因此导致不支持事务。另外,非事务型HUGE表中的ROWID是不固定的。

当非事务型HUGE表在操作过程中出现系统崩溃或者断电等问题时,因为修改时采取的是直接写的策略,所以有可能会出现数据不一致的问题。为了保证数据的一致性,在操作时可以适当地做一些日志来保证数据的完整性,完整性保证策略主要是通过数据的镜像来实现的,镜像的不同程度可以实现不同程度的完整性恢复。镜像文件是放在表目录中的以.mir为扩展名的文件。DM提供三种方案:
1. LOG NONE:不做镜像。相当于不做数据一致性的保证,如果出错只能手动通过系统函数来修复表数据,当然速度是最快的,不需要额外的IO,这种选项如果用户明确知道自己的环境不会出现问题可以采用,效率最高。

2. LOG LAST:做部分镜像。但是在任何时候都只对当前操作的区做镜像,如果当前区的操作完成了,那么这个镜像也就失效了,可能会被下一个被操作区覆盖,这样做的好处是镜像文件不会太大,同时也可以保证数据是完整的。但有可能遇到的问题是:一次操作很多的情况下,有可能一部分数据已经完成,另一部分数据还没有来得及做的问题。如果用户能接受这个问题的话这个选择不失为最佳选择,这也是系统默认的选择

3. LOG ALL:全部做镜像。在操作过程中,所有被修改的区都会被记录下来,当一次操作修改的数据过多时,镜像文件有可能会很大,但好处是,能够保证操作完整性。比如,在操作过程中失败了,那么这个操作会完整的撤消,不存在上面一部分修改部分还没修改的问题。

AUX辅助表
对于每个HUGE表,相应地配备一个AUX辅助表来管理其数据。因为在HUGE表文件中只存储了数据,辅助表用来管理以及辅助系统用户操作这些数据,AUX辅助表是在创建HUGE表时系统自动创建的,表名为“表名$AUX”,如果该HUGE表为分区表,则辅助表名为“子表名$AUX”。辅助表的表名长度不能大于128个字节。AUX辅助表中每一条记录对应文件中的一个数据区,包括下面15列:
1.COLID:表示当前这条记录对应的区所在的列的列ID号;
2.SEC_ID:表示当前这个记录对应的区的区ID号,每一个区都有一个ID号,并且唯一;
3.FILE_ID:表示这个区的数据所在的文件号;
4.OFFSET:表示这个区的数据在文件中的偏移位置,4K对齐;
5.COUNT:表示这个区中存储的数据总数(有可能包括被删除的数据);
6.ACOUNT:表示这个区中存储的实际数据行数;
7.N_LEN:表示这个区中存储的数据在文件中的长度,4K对齐的;
8.N_NULL:表示这个区中的数据中包括的NULL值的行数;
9.N_DIST:表示这个区中所有数据互不相同的行数;
10.CPR_FLAG:表示这个区是否压缩;
11.ENC_FLAG:表示这个区是否加密;
12.CHKSUM:用来较验的,该功能暂未启用;
13.MAX_VAL:表示这个区中的最大值,精确值;
14.MIN_VAL:表示这个区中的最小值,精确值;
15.SUM_VAL:表示这个区中所有值的和,精确值。
前面7列是用来控制数据存取的,根据这些信息就可以知道这个区的具体存储位置、长度及基本信息。后面8列都是用来对这个区进行统计分析的。其中,COLID和SEC_ID的组合键为辅助表的聚集关键字。

事务型HUGE表
非事务型HUGE表在进行增、删、改时直接对HUGE表进行写操作,每次写操作需要至少对一个区进行IO,导致IO量较大,且并发性能不高。为此,DM推出了事务型HUGE表,通过增加RAUX、DAUX和UAUX行辅助表,减少了事务型HUGE表增、删、改操作的IO,
提高效率,同时提高并行性能。事务型HUGE表支持UNDO日志,实现了事务特性。

RAUX行辅助表
RAUX行辅助表存放最后一个数据区(不够存满一个数据区)的数据,表名为“HUGE表名$RAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$RAUX”。辅助表的表名长度不能大于128个字节。

RAUX行辅助表中内容对应于HUGE表中的最后一部分记录(不够存满一个数据区的)。RAUX表与HUGE表结构相同,不论数据在那个表中,每一行数据的ROWID固定不变。

DAUX行辅助表
DAUX行辅助表记录HUGE表数据文件中被删除的数据,表名为“HUGE表名$DAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$DAUX”。辅助表的表名长度不能大于128个字节。

UAUX行辅助表
UAUX行辅助表记录HUGE表被更新的数据的新值,表名为“HUGE表名$UAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$UAUX”。辅助表的表名长度不能大于128个字节。

创建HUGE表
当用户确定自己要使用HUGE表的时候,首先需要在模式中创建新表,创建一个HUGE表需要有CREATE TABLE数据库权限,要想在其他用户的模式中创建新表需要有CREATE ANY TABLE 数据库权限。

但是创建一个HUGE表时,如果不使用默认的表空间,则必须要先创建一个HUGE TABLESPACE(HTS),创建HTS语法如下:
CREATE HUGE TABLESPACE < 表空间名> PATH < 表空间路径>;
参数说明:
1. < 表空间名> 表空间的名称,表空间名称最大长度128字节;
2. < 表空间路径> 指明新生成的表空间在操作系统下的路径。
示例如下:

SQL> CREATE HUGE TABLESPACE HTS_NAME PATH '/dm_home/dmdba/dmdbms/data/jydm/htsspace';
executed successfully
used time: 183.066(ms). Execute id is 8.
SQL> select * from v$huge_tablespace;

LINEID     ID          NAME     PATHNAME                                
---------- ----------- -------- ----------------------------------------
1          0           HMAIN    /dm_home/dmdba/dmdbms/data/jydm/HMAIN
2          1           HTS_NAME /dm_home/dmdba/dmdbms/data/jydm/htsspace

used time: 0.723(ms). Execute id is 9.

在创建HUGE表时,根据WITH|WITHOUT DELTA区分创建非事务型HUGE表还是事务型HUGE表。指定WITH DELTA,创建事务型HUGE表;指定WITHOUT DELTA,则创建非事务型HUGE表,缺省为WITHOUT DELTA。

例如,创建非事务型HUGE表T1

SQL> CREATE HUGE TABLE T1 (A INT, B INT) STORAGE(WITHOUT DELTA) tablespace HTS_NAME;
executed successfully
used time: 49.058(ms). Execute id is 13.
SQL> desc t1;

LINEID     NAME TYPE$   NULLABLE
---------- ---- ------- --------
1          A    INTEGER Y
2          B    INTEGER Y

used time: 14.603(ms). Execute id is 14.



SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T1';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T1         HTS_NAME

used time: 57.166(ms). Execute id is 17.

创建事务型HUGE表T2。

SQL> CREATE HUGE TABLE T2 (A INT, B INT) STORAGE(WITH DELTA) tablespace hts_name;
executed successfully
used time: 37.888(ms). Execute id is 18.
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T2';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T2         HTS_NAME

used time: 27.566(ms). Execute id is 19.


[root@shard1 htsspace]# ls -lrt
总用量 0
drwxr-xr-x 4 dmdba dinstall 34 11月 13 01:07 SCH150994945
[root@shard1 htsspace]# cd SCH150994945
[root@shard1 SCH150994945]# ls
TAB1425  TAB1427
[root@shard1 SCH150994945]# ls -lrt
总用量 0
drwxr-xr-x 2 dmdba dinstall 6 11月 13 01:01 TAB1425
drwxr-xr-x 2 dmdba dinstall 6 11月 13 01:07 TAB1427

需要注意的是,当指定创建事务型HUGE表时,指定HUGE表镜像文件方案的选项LOG NONE|LOG LAST|LOG ALL失效。另外,在创建表HUGE表时,可以指定表的存储属性,存储属性包括如下几个方面:

另外,在创建表HUGE表时,可以指定表的存储属性,存储属性包括如下几个方面:
1. 区大小(一个区的数据行数)
区大小可以通过设置表的存储属性来指定,区的大小必须是2的多少次方,如果不是则向上对齐。取值范围:1024行~1024*1024行。默认值为65536行。例如,创建HUGE表test,指定区的大小为2048,其它默认。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE(SECTION (2048));

例如,创建HUGE表test,所有列都采用默认值。
CREATE HUGE TABLE test(name VARCHAR, sno INT);

2. 是否记录区统计信息,即在插入时是否记下其最大值最小值
关于这一点有一个原则,如果这个列经常用作查询条件,并且数据不是很均匀,或者基本是有序的,那么做统计信息是非常有用的,反之则可以不做统计。缺省情况下,为记录区统计信息。如果想不记录,可通过设置STAT NONE实现。例如,创建HUGE表test,通过列存储属性指定统计信息属性(不记录区统计信息)。
CREATE HUGE TABLE test(name VARCHAR STORAGE (STAT NONE), sno INT);

又如,创建HUGE表test,通过表存储属性指定统计信息属性(不记录区统计信息)。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE (STAT NONE);

3. 所属的表空间
创建HUGE表,需要通过存储属性指定其所在的表空间,不指定则存储于默认表空间HMAIN中。HUGE表指定的表空间只能是HTS表空间,例如HTS_NAME为已指定HTS表空间。

SQL> CREATE HUGE TABLE t3 (name VARCHAR, sno INT) STORAGE (ON HTS_NAME);
executed successfully
used time: 31.424(ms). Execute id is 25.

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T3';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T3         HTS_NAME

used time: 14.911(ms). Execute id is 27.

还可以在创建语句中通过tablespace选项来指定表空间

SQL> CREATE HUGE TABLE T2 (A INT, B INT) STORAGE(WITH DELTA) tablespace hts_name;
executed successfully
used time: 37.888(ms). Execute id is 18.
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T2';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T2         HTS_NAME

used time: 27.566(ms). Execute id is 19.

4. 文件大小
创建HUGE表时还可以指定单个文件的大小,通过表的存储属性来指定,取值范围为16M~1024*1024M。不指定则默认为64M。文件大小必须是2的多少次方,如果不是则向上对齐。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE (filesize(64));

5. 指定压缩级别
为特定列指定压缩级别,取值范围0~10,分别代表不同的算法和级别。有两种压缩算法:SNAPPY和ZIP。10采用SNAPPY算法轻量级方式压缩。2~9采用ZIP算法压缩,2~9代表压缩级别,值越小表示压缩比越低、压缩速率越快;值越大表示压缩比越高、压缩速度越慢。0和1为快捷使用,默认值为0。0等价于LEVEL 2;1等价于LEVEL 9。

例如,创建HUGE表test,指定sno列按照最大压缩比压缩。
CREATE HUGE TABLE test(name VARCHAR, sno INT) COMPRESS LEVEL 1 (sno);

下面是一个综合的创建HUGE表的例子:

SQL> CREATE HUGE TABLE orders
2   (
3   o_orderkey INT,
4   o_custkey INT,
5   o_orderstatus CHAR(1),
6   o_totalprice FLOAT,
7   o_orderdate DATE,
8   o_orderpriority CHAR(15),
9   o_clerk CHAR(15),
10  o_shippriority INT,
11  o_comment VARCHAR(79) STORAGE(stat none)
12  )
13  STORAGE(section(65536) ,filesize(64), with delta,on HTS_NAME) 
14  COMPRESS LEVEL 9 FOR 'QUERY HIGH' (o_comment);
executed successfully
used time: 32.537(ms). Execute id is 28.

这个例子创建了一个名为ORDERS的事务型HUGE表,ORDERS表的区大小为65536行,文件大小为64M,指定所在的表空间为HTS_NAME,o_comment列指定的区大小为不做统计信息,其它列(默认)都做统计信息,指定列o_comment列压缩类型为查询高压缩率,压缩级别为9。

HUGE表使用说明
HUGE表与普通行表一样,可以进行增、删、改操作,操作方式也是一样的。但HUGE表的删除与更新操作的效率会比行表低一些,并发操作性能也会比行表差一些,因此在HUGE中不宜做频繁的删除及更新操作。总之,HUGE表比较适合做分析型表的存储。另外,使用HUGE表时应注意存在以下一些限制:
1.建HUGE表时仅支持定义NULL、NOT NULL、UNIQUE约束以及PRIMARY KEY,后两种约束也可以通过ALTER TABLE的方式添加,但这两种约束不检查唯一性;
2.HUGE不允许建立聚簇索引,允许建立二级索引,不支持建位图索引,其中UNIQUE索引不检查唯一性;
3.不支持SPACE LIMIT(空间限制);
4.不支持建立全文索引;
5.不支持使用自定义类型;
6.不支持引用约束;
7.不支持IDENTITY自增列;
8.不支持大字段列;
9.不支持建触发器;
10.不允许垂直分区;
11.不支持游标的修改操作;
12.PK和UNIQUE约束不检查唯一性,对应的索引都为虚索引;UNIQUE索引也不检查唯一性,为实索引,索引标记中
不包含唯一性标记,即和普通二级索引相同;
13.不允许对分区子表设置SECTION和WITH/WITHOUT DELTA;
14.当事务型HUGE表进行了较多增删改操作时,应对其进行数据重整操作,以提高性能。

查看有关HUGE表的信息
1.表定义
对一个HUGE表,用户可以通过CALL SP_TABLEDEF(‘SYSDBA’, ‘ORDERS’);得到这个表的定义语句,可以具体了解表的各个列的数据类型信息、存储属性等,还可以查看在这个表上是否有压缩等等。

SQL> CALL SP_TABLEDEF('SYSDBA', 'ORDERS');

LINEID     COLUMN_VALUE                                                                                                                                                                                                                   
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1          CREATE HUGE TABLE "SYSDBA"."ORDERS"  (  "O_ORDERKEY" INT,  "O_CUSTKEY" INT,  "O_ORDERSTATUS" CHAR(1),  "O_TOTALPRICE" FLOAT,  "O_ORDERDATE" DATE,  "O_ORDERPRIORITY" CHAR(15),  "O_CLERK" CHAR(15),  "O_SHIPPRIORITY" INT,  "O_COMMENT" VARCHAR(79) STORAGE(STAT NONE)) STORAGE(STAT ASYNCHRONOUS, WITH DELTA, SECTION(65536), FILESIZE(64), ON "HTS_NAME")    COMPRESS (     "O_COMMENT" LEVEL 9 ) LOG LAST ;

used time: 2.205(ms). Execute id is 31.

SQL> CALL SP_TABLEDEF('SYSDBA', 'T1');

LINEID     COLUMN_VALUE                                                                                                                          
---------- --------------------------------------------------------------------------------------------------------------------------------------
1          CREATE HUGE TABLE "SYSDBA"."T1"  (  "A" INT,  "B" INT) STORAGE(WITHOUT DELTA, SECTION(65536), FILESIZE(64), ON "HTS_NAME")  LOG LAST ;

used time: 1.063(ms). Execute id is 32.

2. 数据存储情况
HUGE表有一个很好的特点就是有AUX辅助表,其中用户可以利用的信息很多,因为每一条记录对应一个区,所以可以查看每一个区的存储情况,每一个列的存储情况及每一个列中具有相同区ID的所有数据的情况等,还包括了很精确的统计信息,用户可以通过观察AUX辅助表中的信息对表进行一些相应的操作。

非事务型huge表

SQL> insert into t1 values(2,2);
affect rows 1

used time: 33.110(ms). Execute id is 35.
SQL> commit;
executed successfully
used time: 16.240(ms). Execute id is 36.

SQL> select * from T1$AUX;

LINEID     COLID       SEC_ID      FILE_ID     OFFSET               COUNT       ACOUNT      N_LEN       N_NULL      N_DIST      CPR_FLAG ENC_FLAG CHKSUM      MAX_VAL    MIN_VAL    SUM_VAL           
---------- ----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- -------- -------- ----------- ---------- ---------- ------------------
1          0           0           0           4096                 2           2           270336      0           0           N        N        0           0x02000000 0x01000000 0x0300000000000000
2          1           0           0           4096                 2           2           270336      0           0           N        N        0           0x02000000 0x01000000 0x0300000000000000

used time: 1.079(ms). Execute id is 44.
SQL> 

事务刑huge表

SQL> insert into orders values(1,1,'F',50.5,sysdate,'1','1',1,'1');
affect rows 1

used time: 33.758(ms). Execute id is 46.
SQL> insert into orders values(2,2,'F',134.2,sysdate,'2','2',2,'2');
affect rows 1

used time: 1.499(ms). Execute id is 47.
SQL> commit;
executed successfully
used time: 21.297(ms). Execute id is 48.

在插入数据后RAUX辅助表中有数据,DAUX与UAUX辅助表中没有数据

SQL> select * from orders$raux;

LINEID     O_ORDERKEY  O_CUSTKEY   O_ORDERSTATUS O_TOTALPRICE              O_ORDERDATE O_ORDERPRIORITY O_CLERK         O_SHIPPRIORITY O_COMMENT
---------- ----------- ----------- ------------- ------------------------- ----------- --------------- --------------- -------------- ---------
1          1           1           F             5.050000000000000E+01     2019-11-14  1               1               1              1
2          2           2           F             1.342000000000000E+02     2019-11-14  2               2               2              2

used time: 1.197(ms). Execute id is 50.


SQL> select * from orders$daux;
no rows

used time: 0.961(ms). Execute id is 51.
SQL> select * from orders$uaux;
no rows

used time: 1.007(ms). Execute id is 52.

当更新orders表中的数据后,UAUX辅助表中会记录相关数据

SQL> update orders set O_ORDERSTATUS='D' where O_ORDERKEY=2;
affect rows 1

used time: 2.416(ms). Execute id is 53.
SQL> commit;
executed successfully
used time: 29.715(ms). Execute id is 54.
SQL> select * from orders$uaux;

LINEID     COLID       DTA_ROWID            VALUE
---------- ----------- -------------------- -----
1          2           2                    0x44

used time: 0.726(ms). Execute id is 55.

当删除数据后,DAUX辅助表中会记录相关数据

SQL> delete from orders where O_ORDERKEY=2;
affect rows 1

used time: 2.128(ms). Execute id is 57.
SQL> commit;
executed successfully
used time: 19.066(ms). Execute id is 58.
SQL> select * from orders$daux;

LINEID     START_ID             COUNT       INFO      
---------- -------------------- ----------- ----------
1          2                    1           NULL

used time: 0.590(ms). Execute id is 59.

ORA-00600 qosdExpStatRead expcnt mismatch

Oracle 12.2.0.1最近出现了ORA–0600错误,其详细信息如下:

ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168
ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168

根据Oracle官方给出的检查SQL语句,其执行结果如下:

引起访问题的原因是由于sys.exp_obj$.exp_cnt与sys.exp_stat$的行数据不匹配所造成的

解决方案
1.备份数据库涉及到修改数据字典

2. 修复问题

SQL>update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn=124789;

SQL>commit;

3. 修改后,执行如下SQL确认是否还有记录,如果没有记录,说明已经修改成功,提交。

SQL>With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT<>b.CNT;

修复后,通过一天的观察没有出现该问题了。

PostgreSQL使用表继承实现分区表

PostgreSQL9.6支持基本表的分区。这部分将描述为什么以及如何来实现表分区作为你数据库设计的一部分。

概述
分区指的是将逻辑上一的一个大表分成多个小的物理上的片(子表),分区可以提供以下好处:
.在某些情况下查询性能能够显著提升,特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

.当查询或更新访问一个分区的大部分行时,可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问,这样可以改善性能。

.如果需求计划使用划分设计,可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

.很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时,分区所带来的好处是非常值得的。一个表何种情况下会从分区中获益取决于应用,一个经验法则是当表的尺寸超过了数据库服务器物理内存时,分区会为表带来好处。

当前,PostgreSQL支持通过表继承来实现分区。每个分区必须被创建为单个父表的子表。父表它本身正常来说是空的;它存在仅仅是代表整个数据库。在试图设置分区之前应该要先熟悉表继承。

在PostgreSQL中可以实现下列形式的分区:

范围分区
表被根据一个关键列或一组列划分为”范围”分区,不同的分区的范围之间没有重叠。例如,我们可以根据日期范围划分分区,或者根据特定业务对象的标识符划分分区。

列表分区
通过显式地列出每一个分区中出现的键值来划分表。

实现分区
要建立一个分区表,可以这样做:
1.创建一个”主”表,所有的分区都将继承它。
这个表将不包含数据。不要对这个表定义任何检查约束,除非你打算将这些约束应用到所有的分区。同样也不需要定义任何索引或者唯一约束。

2.创建一些继承于主表的”子”表。通常,这些表不会在从主表继承的列集中增加任何列。
们将这些子表认为是分区,尽管它们在各方面来看普通的PostgreSQL表(或者可能是外部表)。

3.为分区表增加表约束以定义每个分区中允许的键值。
典型的例子是:

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

要确保这些约束能够保证在不同分区所允许的键值之间不存在重叠。设置范围约束时一种常见的错误是:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

这是错误的,因为键值200并没有被清楚地分配到某一个分区。注意在语法上范围划分和列表划分没有区别,这些术语只是为了描述方便而存在。

4.对于每一个分区,在关键列上创建一个索引,并创建其他我们所需要的索引(关键索引并不是严格必要的,但是在大部分情况下它都是有用的。如果我们希望键值是唯一的,则我们还要为每一个分区创建一个唯一或者主键约束。)

5.还可以有选择地定义一个触发器或者规则将插入到主表上的数据重定向到合适的分区上。

6.确保在postgresql.conf中constraint_exclusion配置参数没有被禁用。如果它被禁用,查询将不会被按照期望的方式优化。

例如,假设我们正在为一个大型的冰淇淋公司构建一个数据库。该公司测量每天在每一个区域的最高气温以及冰淇淋销售。在概念上,我们想要一个这样的表:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

insert into measurement values(1,date '2008-02-01',1,1);

由于该表的主要用途是为管理层提供在线报告,我们知道大部分查询将只会访问上周、上月或者上季度的数据。为了减少需要保存的旧数据的量,我们决定只保留最近3年的数据。在每一个月的开始,我们将删除最老的一个月的数据。

在这种情况下,我们可以使用分区来帮助我们满足对于测量表的所有不同需求。按照上面所勾勒的步骤,分区可以这样来建立:

1.主表是measurement表,完全按照以上的方式声明。

jydb=# CREATE TABLE measurement (
jydb(#     city_id         int not null,
jydb(#     logdate         date not null,
jydb(#     peaktemp        int,
jydb(#     unitsales       int
jydb(# );
CREATE TABLE

2.下一步我们为每一个活动月创建一个分区:

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

每一个分区自身都是完整的表,但是它们的定义都是从measurement表继承而来。

这解决了我们的一个问题:删除旧数据。每个月,我们所需要做的是在最旧的子表上执行一个DROP TABLE命令并为新一个月的数据创建一个新的子表。

3.我们必须提供不重叠的表约束。和前面简单地创建分区表不同,实际的表创建脚本应该是:

jydb=# CREATE TABLE measurement_y2006m02 (
jydb(#     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2006m03 (
jydb(#     CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m02 (
jydb(#     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE

4.我们可能在关键列上也需要索引:

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

在这里我们选择不增加更多的索引。

5.我们希望我们的应用能够使用INSERT INTO measurement …并且数据将被重定向到合适的分区表。我们可以通过为主表附加一个合适的触发器函数来实现这一点。如果数据将只被增加到最后一个分区,我们可以使用一个非常简单的触发器函数:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

完成函数创建后,我们创建一个调用该触发器函数的触发器:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

我们必须在每个月重新定义触发器函数,这样它才会总是指向当前分区。而触发器的定义则不需要被更新。

我们也可能希望插入数据时服务器会自动地定位应该加入数据的分区。我们可以通过一个更复杂的触发器函数来实现之,例如:

jydb=# CREATE OR REPLACE FUNCTION measurement_insert_trigger()
jydb-# RETURNS TRIGGER AS $$
jydb$# BEGIN
jydb$#
jydb$#     IF ( NEW.logdate >= DATE '2006-03-01' AND
jydb$#             NEW.logdate < DATE '2006-04-01' ) THEN
jydb$#         INSERT INTO measurement_y2006m03 VALUES (NEW.*);
jydb$#     ELSIF ( NEW.logdate >= DATE '2008-02-01' AND
jydb$#             NEW.logdate < DATE '2008-03-01' ) THEN
jydb$#         INSERT INTO measurement_y2008m02 VALUES (NEW.*);
jydb$#     ELSE
jydb$#         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
jydb$#     END IF;
jydb$#     RETURN NULL;
jydb$# END;
jydb$# $$
jydb-# LANGUAGE plpgsql;
CREATE FUNCTION



jydb=# CREATE TRIGGER insert_measurement_trigger
jydb-#     BEFORE INSERT ON measurement
jydb-#     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
CREATE TRIGGER
jydb=# insert into measurement values(1,date '2006-03-03',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-03',1,1);
INSERT 0 0
jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
(2 rows)

jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(5 rows)

jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(7 rows)

触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的分区的CHECK约束。当该函数比单月形式更加复杂时,并不需要频繁地更新它,因为可以在需要的时候提前加入分支。

注意: 在实践中,如果大部分插入都会进入最新的分区,最好先检查它。为了简洁,我们为触发器的检查采用了和本例中其他部分一致的顺序。

如我们所见,一个复杂的分区模式可能需要大量的DDL。在上面的例子中,我们需要每月创建一个新分区,所以最好能够编写一个脚本自动地生成所需的DDL。

管理分区
通常当初始定义的表倾向于动态变化时,一组分区会被创建。删除旧的分区并周期性地为新数据增加新分区是很常见的。划分的一个最重要的优点是可以通过操纵分区结构来使得这种痛苦的任务几乎是自发地完成,而不需要去物理地移除大量的数据。

移除旧数据的最简单的选项是直接删除不再需要的分区:

jydb=# DROP TABLE measurement_y2006m02;
DROP TABLE

这可以非常快地删除百万级别的记录,因为它不需要逐一地删除记录。

另一个经常使用的选项是将分区从被划分的表中移除,但是把它作为一个独立的表保留下来:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

这允许在数据被删除前执行更进一步的操作。例如,这是一个很有用的时机通过COPY、pg_dump或类似的工具来备份数据。这也是进行数据聚集、执行其他数据操作或运行报表的好时机。

相似地我们也可以增加新分区来处理新数据。我们可以在被划分的表中创建一个新的空分区:

jydb=# CREATE TABLE measurement_y2008m02 (
jydb(#     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE

作为一种选择方案,有时创建一个在分区结构之外的新表更方便,并且在以后才将它作为一个合适的分区。这使得数据可以在出现于分区表中之前被载入、检查和转换:

jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
jydb=# ALTER TABLE measurement_y2008m03 ADD CONSTRAINT y2008m03
jydb-#    CHECK ( logdate >= DATE '2008-03-01' AND logdate < DATE '2008-04-01' );
ALTER TABLE
jydb=# ALTER TABLE measurement_y2008m03 INHERIT measurement;
ALTER TABLE

分区与约束排除
约束排除是一种查询优化技术,它可以为按照以上方式定义的分区表提高性能。例如:

jydb=# SET constraint_exclusion = on;
SET
jydb=# SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
 count
-------
     3
(1 row)

如果没有约束排除,上述查询将扫描measurement表的每一个分区。在启用约束排除后,规划器将检查每一个分区的约束来确定该分区需不需要被扫描,因为分区中可能不包含满足查询WHERE子句的行。如果规划器能够证实这一点,则它将会把该分区排除在查询计划之外。

可以使用EXPLAIN命令来显示开启了constraint_exclusion的计划和没有开启该选项的计划之间的区别。一个典型的未优化的计划是:

jydb=# SET constraint_exclusion = off;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=107.47..107.48 rows=1 width=8)
   ->  Append  (cost=0.00..102.69 rows=1913 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(10 rows)

其中的某些或者全部分区将会使用索引扫描而不是全表顺序扫描,但是关键在于根本不需要扫描旧分区来回答这个查询。当我们开启约束排除后,对于同一个查询我们会得到一个更加廉价的计划:

jydb=# SET constraint_exclusion = on;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=72.80..72.81 rows=1 width=8)
   ->  Append  (cost=0.00..69.56 rows=1296 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(8 rows)

注意约束排除只由CHECK约束驱动,而非索引的存在。因此,没有必要在关键列上定义索引。是否在给定分区上定义索引取决于我们希望查询经常扫描表的大部分还是小部分。在后一种情况中索引将会发挥作用。

constraint_exclusion的默认(也是推荐)设置实际上既不是on也不是off,而是一个被称为partition的中间设置,这使得该技术只被应用于将要在分区表上工作的查询。设置on将使得规划器在所有的查询中检查CHECK约束,即使简单查询不会从中受益。

替代的分区方法
另一种将插入数据重定向到合适的分区的方法是在主表上建立规则而不是触发器,例如:

jydb=# CREATE RULE measurement_insert_y2006m03 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2006m03 VALUES (NEW.*);
CREATE RULE
jydb=# CREATE RULE measurement_insert_y2008m02 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2008m02 VALUES (NEW.*);
CREATE RULE


jydb=# insert into measurement values(1,date '2006-03-02',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-02',1,1);
INSERT 0 0
jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(5 rows)

jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
(1 row)

jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(4 rows)

一个规则比一个触发器具有明显更高的负荷,但是该负荷是由每个查询承担而不是每一个行,因此这种方法可能对于批量插入的情况有益。但是,在大部分情况下触发器方法能提供更好的性能。

注意COPY会忽略规则。如果希望使用COPY来插入数据,我们将希望将数据复制到正确的分区表而不是主表。COPY会引发触发器,因此如果使用触发器方法就可以正常地使用它。

规则方法的另一个缺点是如果一组规则没有覆盖被插入的数据,则该数据将被插入到主表中而不会发出任何错误。

分区也可以使用一个UNION ALL视图来组织。例如:

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

但是,如果要增加或者删除单独的分区,就需要重新地创建视图。在实践中,相对于使用继承,这种方法很少被推荐。

警告
下面的警告适用于分区表:
.没有自动的方法来验证所有的CHECK约束是互斥的。创建代码来生成分区并创建或修改相关对象比手工写命令要更安全。

.这里展示的模式都假设分区的关键列从不改变,或者是其改变不足以导致它被移到另一个分区。一个尝试将行移到另一个分区的UPDATE会失败,因为CHECK约束的存在。如果我们需要处理这类情况,我们可以在分区表上放置合适的更新触发器,但是它会使得结构的管理更加复杂。

.如果我们在使用手工的VACUUM或ANALYZE命令,别忘了需要在每一个分区上都运行一次。以下的命令:
ANALYZE measurement;
只会处理主表。

.带有ON CONFLICT子句的INSERT 语句不太可能按照预期的方式工作,因为ON CONFLICT动作 只有在指定的目标关系(而非它的子关系)上有唯一违背的情况下才会被采用。

下面的警告适用于约束排除:
.只有在查询的WHERE子句包含常量(或者外部提供的参数)时,约束排除才会起效。例如,一个与非不变函数(例如CURRENT_TIMESTAMP)的比较不能被优化,因为规划器不知道该函数的值在运行时会落到哪个分区内。

.保持分区约束简单,否则规划器可能没有办法验证无需访问的分区。按前面的例子所示,为列表分区使用简单相等条件或者为范围分区使用简单范围测试。一个好的经验法则是分区约束应该只包含使用B-tree索引操作符的比较,比较的双方应该是分区列和常量。

.在约束排除期间,主表所有的分区上的所有约束都会被检查,所以大量的分区将会显著地增加查询规划时间。使用这些技术的分区在大约最多100个分区的情况下工作得很好,但是不要尝试使用成千个分区。

PostgreSQL 表继承

PostgreSQL实现了表继承,这对数据库设计者来说是一种有用的工具(SQL:1999及其后的版本定义了一种类型继承特性,但和这里介绍的继承有很大的不同)。让我们从一个例子开始:假设我们要为城市建立一个数据模型。每一个州有很多城市,但是只有一个首府。我们希望能够快速地检索任何特定州的首府城市。这可以通过创建两个表来实现:一个用于州首府,另一个用于不是首府的城市。然而,当我们想要查看一个城市的数据(不管它是不是一个首府)时会发生什么?继承特性将有助于解决这个问题。我们可以将capitals表定义为继承自cities表:

jydb=# CREATE TABLE cities (
jydb(# name text,
jydb(# population float,
jydb(# altitude int -- in feet
jydb(# );
CREATE TABLE
jydb=# CREATE TABLE capitals (
jydb(# state char(2)
jydb(# ) INHERITS (cities);
CREATE TABLE


jydb=# insert into cities values('Las Vegas',600,2174);
INSERT 0 1
jydb=# insert into cities values('Mariposa',500,1953);
INSERT 0 1
jydb=# insert into cities values('Madison',450,845);
INSERT 0 1
jydb=# insert into capitals values('Houston',400,745,'LA');
INSERT 0 1


jydb=# select * from cities;
   name    | population | altitude
-----------+------------+----------
 Las Vegas |        600 |     2174
 Mariposa  |        500 |     1953
 Madison   |        450 |      845
 Houston   |        400 |      745
(4 rows)

jydb=# select * from capitals;
  name   | population | altitude | state
---------+------------+----------+-------
 Houston |        400 |      745 | LA
(1 row)

在这种情况下,capitals表继承了它父表cities的所有列。州首府还有一个额外的列state用来表示它所属的州。

在PostgreSQL中,一个表可以从0个或者多个其他表继承,而对一个表的查询则可以引用一个表的所有行或者该表的所有行加上它所有的后代表。默认情况是后一种行为。例如,下面的查询将查找所有海拔高于500尺的城市的名称,包括州首府:

jydb=# SELECT name, altitude FROM cities WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
 Houston   |      745
(4 rows)

在另一方面,下面的查询将找到海拔超过500尺且不是州首府的所有城市:

jydb=# SELECT name, altitude FROM ONLY cities WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)

这里的ONLY关键词指示查询只被应用于cities上,而其他在继承层次中位于cities之下的其他表都不会被该查询涉及。很多我们已经讨论过的命令(如SELECT、UPDATE和DELETE)都支持ONLY关键词。

我们也可以在表名后写上一个*来显式地将后代表包括在查询范围内:

jydb=# SELECT name, altitude FROM cities* WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
 Houston   |      745
(4 rows)

*并不是必须的,因为它对应的行为是默认的(除非改变sql_inheritance配置选项的设置)。但是书写*有助于强调会有附加表被搜索。

在某些情况下,我们可能希望知道一个特定行来自于哪个表。每个表中的系统列tableoid可以告诉我们行来自于哪个表:

jydb=# SELECT c.tableoid, c.name, c.altitude FROM cities c WHERE c.altitude > 500;
 tableoid |   name    | altitude
----------+-----------+----------
    24653 | Las Vegas |     2174
    24653 | Mariposa  |     1953
    24653 | Madison   |      845
    24659 | Houston   |      745
(4 rows)

(如果重新生成这个结果,可能会得到不同的OID数字。)通过与pg_class进行连接可以看到实际的表名:

jydb=# SELECT p.relname, c.name, c.altitude
jydb-# FROM cities c, pg_class p
jydb-# WHERE c.altitude > 500 AND c.tableoid = p.oid;
 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 cities   | Madison   |      845
 capitals | Houston   |      745
(4 rows)

另一种得到同样效果的方法是使用regclass伪类型, 它将象征性地打印出表的 OID:

jydb=# SELECT c.tableoid::regclass, c.name, c.altitude
jydb-# FROM cities c
jydb-# WHERE c.altitude > 500;
 tableoid |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 cities   | Madison   |      845
 capitals | Houston   |      745
(4 rows)

继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。在我们的例子中,下面的INSERT语句将会失败:

jydb=# INSERT INTO cities (name, population, altitude, state) VALUES (’Albany’, NULL, NULL, ’NY’);
ERROR:  column "state" of relation "cities" does not exist
LINE 1: INSERT INTO cities (name, population, altitude, state) VALUE...

^

我们也许希望数据能被以某种方式被引入到capitals表中,但是这不会发生:INSERT总是向指定的表中插入。在某些情况下,可以通过使用一个规则(见第 39 章)来将插入动作重定向。但是这对上面的情况并没有帮助,因为cities表根本就不包含state列,因而这个命令将在触发规则之前就被拒绝。

父表上的所有检查约束和非空约束都将自动被它的后代所继承。其他类型的约束(唯一、主键和外键约束)则不会被继承。

一个表可以从多个父表继承,在这种情况下它拥有父表们所定义的列的并集。任何定义在子表上的列也会被加入到其中。如果在这个集合中出现重名列,那么这些列将被”合并”,这样在子表中只会有一个这样的列。重名列能被合并的前提是这些列必须具有相同的数据类型,否则会导致错误。可继承的检查约束和非空约束以类似的方式合并。因此,例如,如果任何列定义被标记为not-null,则合并列将被标记为not-null。如果检查约束具有相同的名称,则合并它们;如果条件不同,则合并将失败。

表继承通常是在子表被创建时建立,使用CREATE TABLE语句的INHERITS子句。一个已经被创建的表也可以另外一种方式增加一个新的父亲关系,使用ALTER TABLE的INHERIT变体。要这样做,新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同的检查约束和检查表达式。相似地,一个继承链接也可以使用ALTER TABLE的 NO INHERIT变体从一个子表中移除。动态增加和移除继承链接可以用于实现表划分

一种创建一个未来将被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。这将创建一个和源表具有相同列的新表。如果源表上定义有任何CHECK约束,LIKE的INCLUDING CONSTRAINTS选项可以用来让新的子表也包含和父表相同的约束。

当有任何一个子表存在时,父表不能被删除。当子表的列或者检查约束继承于父表时,它们也不能被删除或修改。如果希望移除一个表和它的所有后代,一种简单的方法是使用CASCADE选项删除父表

ALTER TABLE将会把列的数据定义或检查约束上的任何变化沿着继承层次向下传播。同样,删除被其他表依赖的列只能使用CASCADE选项。ALTER TABLE对于重名列的合并和拒绝遵循与CREATE TABLE同样的规则。

继承查询只对父表执行访问权限检查。因此,例如,对cities表授予update权限会意味着通过cities访问capitals表时也能更新capitals表。这体现了子表中的数据也在父表中。但是capitals表在没有额外地授权情况下不能被直接更新。以类似的方式,父表的行安全策略在执行继承查询时会应用到子表的行记录。子表的策略(如果有的话)仅当它是查询中显式命名的表时才应用;在这种情况下,任何附加到其父级的策略都将被忽略。

外部表也可以是继承层次 中的一部分,即可以作为父表也可以作为子表,就像常规表一样。如果一个外部表是继承层次的一部分,那么任何不被该外部表支持的操作也不被整个层次所支持。