mysql 序列与pg序列的比较

mysql序列（这里只谈innodb引擎）：

  1. 在使用mysql的AUTO_INCREMENT时，使用AUTO_INCREMENT的字段必须建有索引，也可以为索引的一部分。当没有索引时会报错：          由于mysql是索引组织表，数据存放在主键中，又由于自增列字段内容长度较小，建议选用自增字段为主键。
  2. 对于mysql来说AUTO_INCREMENT 的加锁模式是由参数innodb_autoinc_lock_mode决定的，不同的加锁模式加上的不同的insert方式对mysql产生不同的影响，insert的类型包括：Simple inserts、Bulk inserts、Mixed-mode inserts三种。innodb_autoinc_lock_mode的取值有0，1,2三种。
    1. innodb_autoinc_lock_mode=0时：
      • In this lock mode, all “INSERT-like” statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns。在这种锁模式下，所有的insert类型的语句，对于AUTO_INCREMENT列均加有特殊的表锁AUTO-INC锁
      • 这种锁模式在语句结束时结束，并非在事务结束时结束。这样可以保证每个auto-increment是在可预测，可重复的模式情况下赋值，也可以保证对于任何insert类语句，每个auto-increment列的赋值都是连续的。
      • 虽然这种锁模式下，可以保证auto-increment的连续性，但由于加的是表锁，所以对并发性影响较大。
    2. innodb_autoinc_lock_mode =1 时：
      • 在该模式下，bulk inserts添加的 AUTO-INC表锁，并一直持续到语句结束。这是适用于 INSERT ... SELECT,REPLACE ... SELECT和 LOAD DATA语句。
      • 对于事前知道插入多少行的Simple inserts，则可以避免AUTO-INC表锁，它加的是轻量级的锁mutex ，只会在自增列值分配时存在，不会等到语句执行结束。
      • 对于Mixed-mode inserts，innodb会分配比插入的行更多的auto-increment值，但分配的值是连续的。
    3. innodb_autoinc_lock_mode =2 时：
      • 在这种锁模式下，对于所有insert 类型的语句都不会加AUTO-INC表锁，这是最快和最具伸缩性的模式。但是当在binlog是statement格式下时，这种模式是不安全的。
      • 在这种锁模式下，auto-increment值可以保证是唯一和单调的递增的，即使存在并发的insert类型操作时，auto-increment值不连续的情况。

　　3、（1）当在statement-based复制下时，innodb_autoinc_lock_mode为0或1，均可以保证主从序列的一致性。当配置的innodb_autoinc_lock_mode=2或是主从不一样时，则主从序列值不能保证一致。

　　　　（2）如果使用的是row-based 或 mixed-format复制，则auto-increment的锁模式都是安全的，因为基于row-based的复制是不受sql语句执行顺序的影响的。

　　　　（3）如果在事务中含有auto-increment的分配语句回滚了，那分配的auto-increment值也就消失了，不能再被使用。auto-increment值是不会被回滚的。

　　　　（4）当在AUTO_INCREMENT列插入的值是NULL或0时，AUTO_INCREMENT都会分配一个新值。

　　　　（5）当插入的值是负值时，也可以正常插入，由于mysql是索引组织表，插入的负值会插在其他正值的前面.

　　　　（6）当做bulk inserts操作，且innodb_autoinc_lock_mode为0或1此时AUTO_INCREMENT值不会产生间隙，因为此时添加的是表锁。innodb_autoinc_lock_mode=2时，则可能产生间隙。

　　　　（7）对于mixed-mode inserts，innodb_autoinc_lock_mode不同的值会有不同的情况：

　　　　下面是创建的测试表：

　　该表最新自动产生的序列值是100：

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
      • 当innodb_autoinc_lock_mode=0时： 
        mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

        后面自动分配的将是103，因为auto-increment值一次分配一个，而不是分配了所有的值。无论是否并发，结果均如此。

      • 当innodb_autoinc_lock_mode=1时： 
        mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

        后面自动分配的将是105，不是103，这是因为在语句执行时，就已将4个自增值分配了，而不仅仅是使用到的两个值。无论是否并发，结果均如此。

      • 当innodb_autoinc_lock_mode=2时： 
        mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

        在分配时，x和y将是惟一的，而且比前面的值要大，然后这里的x和y值将受到并发insert操作的影响。假如当前序列分配的值是4，则x将为5，与后面的5值相同，报错，后面的(5,c)将插入失败。

　　　　　　　　　

pg序列：

　　下面是pg创建序列的语法：

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

序列中各参数具体含义请参考官方文档，这里主要讲解几个参数：

　　cache：

　　cache是指预分配几个序列值，放在内存中，以方便序列的快速分配。没有指定时，默认值是1.当该值设置大于1时，多个session同时做高并发insert时，每个session都会预分配cache个序列值，但不见得每个预分配的序列值会被使用，所以会出现序列值不连续的情况。不过pg序列已专门做过优化，cache加大效果不大，后面会有介绍。

　　CYCLE：

　　这个参数用于，当序列值递增至最大值或递减（当increment为负值时）至最小值时，是否循环从设定的最大值或最小值开始。pg序列默认的最大值是：9223372036854775807，pg序列默认是基于bigint类型创建的。当超过该值并且没有设定CYCLE时，会报错。默认是NO CYCLE.

 

　　另外需要注意的时，在使用setval重设序列值时，其他session将不会受影响，直到cache预分配的值使用完毕后。

 

测试CYCLE对序列的影响：

　　创建一个序列：

　　选项均使用默认值。看到is_cycled是f，默认是NO CYCLE的。　

swrd=# create sequence seq1;
CREATE SEQUENCE
swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

将该序列修改成递减，并将当前值修改5.

swrd=# alter sequence seq1 increment -1;
ALTER SEQUENCE
swrd=# select setval('seq1',5);
 setval
--------
      5
(1 row)

swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 5
 start_value   | bigint  | 1
 increment_by  | bigint  | -1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | t

下面不断的取下一个序列值：

swrd=# select nextval('seq1');
 nextval
---------
       4
(1 row)

swrd=# select nextval('seq1');
 nextval
---------
       3
(1 row)

swrd=# select nextval('seq1');
 nextval
---------
       2
(1 row)

swrd=# select nextval('seq1');
 nextval
---------
       1
(1 row)

swrd=# select nextval('seq1');
ERROR:  nextval: reached minimum value of sequence "seq1" (1)

当取到1时，由于不是循环的，最后报错，达到默认的最小值。

现将序列修改为CYCLE，然后取下一个值：

swrd=# alter sequence seq1 cycle;
ALTER SEQUENCE
swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | -1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | t
 is_called     | boolean | t

swrd=# select nextval('seq1');
       nextval
---------------------
 9223372036854775807
(1 row)

swrd=# select nextval('seq1');
       nextval
---------------------
 9223372036854775806
(1 row)

设定循环后，可以继续往下取值，不会报错。递增的情况也与此类似。所以对数据库序列的定期巡检是有必要的，对于快达到最小或最大值的序列要及时处理，以免影响业务访问。

 

sequence与serial：

　　serial并不是pg的数据类型，但它可以用来方便的创建序列。

　　下面的两种方式是等价的：

　　使用serial：

swrd=# create table test_serial(id serial);
CREATE TABLE
swrd=# \d test_serial
                          Table "swrd.test_serial"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('test_serial_id_seq'::regclass)

swrd=# \d test_serial_id_seq
      Sequence "swrd.test_serial_id_seq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | test_serial_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: swrd.test_serial.id

　　使用sequence：

swrd=# create sequence test_seq_id ;
CREATE SEQUENCE
swrd=# create table test_seq(id int not null default nextval('test_seq_id'));
CREATE TABLE         
swrd=# alter sequence test_seq_id  owned by test_seq.id;
ALTER SEQUENCE
swrd=# \d test_seq
                        Table "swrd.test_seq"
 Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('test_seq_id'::regclass)

swrd=# \d test_seq_id
          Sequence "swrd.test_seq_id"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | test_seq_id
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: swrd.test_seq.id

注意：

　　1、在创建外部表时，当主表存在含有序列的列，则外部表创建时，可以使用sequence创建序列，但不能使用serial创建。这个问题会在另一篇博客中讲解。

　　2、当使用serial创建的id列，删除时，创建时对应的序列也会一并删除，但单独使用sequence创建的序列则不会。

　　3、但不论是使用哪种方式创建的序列，当删除序列时，都会提示存在依赖关系，提示使用CASCADE一并将依赖对象删除。要想不删除依赖的表，可以将default值去掉。然后单独删除序列。

 

测试cache对序列的影响：

这里创建两个序列一个cache使用默认值1，另一个序列将cache值设为100.

postgres=# create sequence seq1;
CREATE SEQUENCE
postgres=# create sequence cached_seq cache 100;
CREATE SEQUENCE

测试10、100个并发时的情况：

1.sql文件中的内容是：select nextval('seq1')，2.sql文件中的内容是：select nextval('cached_seq')

10个并发时：

postgres@db-> pgbench -n -c 10 -j 10 -T 2 -f 1.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 2 s
number of transactions actually processed: 160990
latency average: 0.124 ms
tps = 80482.444739 (including connections establishing)
tps = 87140.735942 (excluding connections establishing)
postgres@db-192-168-173-43-> pgbench -n -c 10 -j 10 -T 2 -f 2.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 2 s
number of transactions actually processed: 168619
latency average: 0.119 ms
tps = 84295.675509 (including connections establishing)
tps = 90538.479485 (excluding connections establishing)

10个并发时，cache使用默认值tps为87140.735942，cache值为100的tps是：90538.479485，两者相差不大。

 

100个并发时：

postgres@db-> pgbench -n -c 100 -j 100 -T 2 -f 1.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
duration: 2 s
number of transactions actually processed: 136312
latency average: 1.467 ms
tps = 63938.069494 (including connections establishing)
tps = 161793.078271 (excluding connections establishing)
postgres@db-> pgbench -n -c 100 -j 100 -T 2 -f 2.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
duration: 2 s
number of transactions actually processed: 162665
latency average: 1.230 ms
tps = 80642.482598 (including connections establishing)
tps = 163479.402068 (excluding connections establishing)

100个并发时，默认cache值时的tps为：161793.078271，cache为100的tps为：163479.402068，两者相差更小了。

 

注：

　　1、按照对cache选项的理解，cache越大，性能越高才对，但测试发现cache对性能影响不大。

　　2、既然cache对序列的性能提高不大，那尽量cache使用默认值1，以避免出现序列值的不连续性。

　　3、pg序列的如此高效性，接近redis的INCRs。具体可参考Don’t Assume PostgreSQL is Slow。

 

pg序列可以这么快及cache作用不大的原因：

在pg源码src/backend/commands/sequence.c中可以看到：

/*
 * We don't want to log each fetching of a value from a sequence,
 * so we pre-log a few fetches in advance. In the event of
 * crash we can lose (skip over) as many values as we pre-logged.
 */
#define SEQ_LOG_VALS    32
        /*
         * Decide whether we should emit a WAL log record.  If so, force up the
         * fetch count to grab SEQ_LOG_VALS more values than we actually need to
         * cache.  (These will then be usable without logging.)
         *
         * If this is the first nextval after a checkpoint, we must force a new
         * WAL record to be written anyway, else replay starting from the
         * checkpoint would fail to advance the sequence past the logged values.
         * In this case we may as well fetch extra values.
         */
        if (log < fetch || !seq->is_called)
        {
                /* forced log to satisfy local demand for values */
                fetch = log = fetch + SEQ_LOG_VALS;
                logit = true;
        }
        else
        {
                XLogRecPtr      redoptr = GetRedoRecPtr();

                if (PageGetLSN(page) <= redoptr)
                {
                        /* last update of seq was before checkpoint */
                        fetch = log = fetch + SEQ_LOG_VALS;
                        logit = true;
                }
        }
 /*
                 * We don't log the current state of the tuple, but rather the state
                 * as it would appear after "log" more fetches.  This lets us skip
                 * that many future WAL records, at the cost that we lose those
                 * sequence values if we crash.
                 */
                XLogBeginInsert();
                XLogRegisterBuffer(0, buf, REGBUF_WILL_INIT);

                /* set values that will be saved in xlog */
                seq->last_value = next;
                seq->is_called = true;
                seq->log_cnt = 0;

                xlrec.node = seqrel->rd_node;

                XLogRegisterData((char *) &xlrec, sizeof(xl_seq_rec));
                XLogRegisterData((char *) seqtuple.t_data, seqtuple.t_len);

                recptr = XLogInsert(RM_SEQ_ID, XLOG_SEQ_LOG);

                PageSetLSN(page, recptr);
}
        /* Now update sequence tuple to the intended final state */
        seq->last_value = last;         /* last fetched number */
        seq->is_called = true;
        seq->log_cnt = log;                     /* how much is logged */

可以看到pg中并不是每产生一个序列值就做一次记录wal log的操作，而是每产生32个序列值后才会记一次wal log record。所以这相当于已对序列做了缓存。

其中log_cnt扮演者计数器的角色，当该值减为0时，则会产生一个记录wal log的标志位。该计数器对应的是序列属性中的log_cnt。

swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 9223372036854775806
 start_value   | bigint  | 1
 increment_by  | bigint  | -1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 31
 is_cycled     | boolean | t
 is_called     | boolean | t

每产生一个序列值该值就减1。但当服务器意外宕机时，则会丢掉代码中缓存的序列值。

下面做个测试：

当前序列seq的值为15：

swrd=# select nextval('seq1');
 nextval
---------
      15
(1 row)

swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 15
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 32
 is_cycled     | boolean | t
 is_called     | boolean | t

它的下个值应为16，而且还有32个值没有分配，此时模拟数据库意外宕机，将数据库的进程强制杀掉：

swrd=# \d seq1
             Sequence "swrd.seq1"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | seq1
 last_value    | bigint  | 47
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | t
 is_called     | boolean | t

swrd=# select nextval('seq1');
 nextval
---------
      48
(1 row)

强制杀掉pg后，再启动发现当前序列的值是47，及缓存的32加上之前的序列值15，再执行nextval，下一个序列值是48.

 

 

 pg迁移时的序列：

当进行pg迁移时可能存在序列字段恰好是主键的表的情况，迁移完成后，一定要检查有无这种情况，将序列值设置为主键值的max值，如果产生的序列值小于主键max值的情况，则数据将插入不进去。

 

pg与mysql序列比较：

　　1、pg的序列属性是基于单表，而mysql则是基于整个实例的。

　　2、pg的序列不必建有主键，而mysql多为建有主键索引，所以不存在重复的情况，而pg则会存在。

　　3、pg的序列高并发时效率非常高，不会出现锁表的情况，而mysql则要视insert的类型、参数innodb_autoinc_lock_mode的值和binlog的格式设定情况来看。

　　4、pg中可以限定序列值达到限定的最大值或最小值时CYCLE使用，mysql则可以通过修改字段类型为unsigned或是将字段类型更改为更大范围的类型，没有看到CYCLE的用法。

　　5、pg中序列值可递减，而mysql中序列值开始值和增加值均必须为正数，范围均为1 .. 65535。由两个参数控制：auto_increment_offset控制从哪个值开始，auto_increment_increment：控制每次递增的量。

　　6、pg的序列与mysql的序列均不能回滚。

　　7、pg的序列字段不一定要创建索引，但mysql则必须创建。

 

参考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

https://www.postgresql.org/docs/9.4/static/sql-createsequence.html

https://www.postgresql.org/docs/9.4/static/sql-altersequence.html

https://www.postgresql.org/docs/9.4/static/functions-sequence.html

https://www.postgresql.org/docs/9.4/static/datatype-numeric.html

http://blog.chinaunix.net/uid-20726500-id-4727557.html?spm=5176.100239.blogcont.6.DBlloS

src/backend/commands/sequence.c

https://yq.aliyun.com/articles/6053?do=login

 

posted on 2016-06-07 18:49  Still water run deep  阅读(1812)  评论(0)  编辑  收藏  举报

导航