Sequences
The sharded customer
table we created did not have an auto-increment column. The Vitess Sequence feature can be used to emulate the same behavior as MySQL’s auto-increment. A Vitess sequence is a single row unsharded tablet that keeps track of ids issued so far. Additionally, a configurable number of values can be cached by vttablet to minimize round trips into MySQL.
We will create the sequence table in the unsharded product
keyspace as follows:
create table customer_seq(id bigint, next_id bigint, cache bigint, primary key(id)) comment 'vitess_sequence';
insert into customer_seq(id, next_id, cache) values(0, 1, 3);
Note the special comment vitess_sequence
. This instructs vttablet that this is a special table.
The table needs to be pre-populated with a single row where:
id
must always be 0next_id
should be set to the next (starting) value of the sequencecache
is the number of values to cache before updating the table for the next value. This value should be set to a fairly large number like 1000. We have set the value to3
mainly to demonstrate how the feature works.
Since this is a special table, we have to inform the vschema by giving it a sequence
type.
"customer_seq": { "type": "sequence" }
Once setup this way, you can use the special select next
syntax to generate values from this sequence:
mysql> select next 2 values from customer_seq;
+---------+
| nextval |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> select next 1 values from customer_seq;
+---------+
| nextval |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
The construct returns the first of the N values generated.
However, this is insufficient to emulate MySQL’s auto-increment behavior. To achieve this, we have to inform the VSchema that the customer_id
column should use this sequence to generate values if no value is specified. This is done by adding the following section to the customer
table:
"auto_increment": {
"column": "customer_id",
"sequence": "product.customer_seq"
}
Alternate VSchema DDL:
alter vschema add sequence product.customer_seq;
alter vschema on customer.customer add auto_increment customer_id using product.customer_seq;
With this, you can insert into customer
without specifying the customer_id
:
mysql> insert into customer(uname) values('alice'),('bob'),('charlie'),('dan'),('eve');
Query OK, 5 rows affected (0.03 sec)
mysql> use `customer:-80`;
Database changed
mysql> select * from customer;
+-------------+---------+
| customer_id | uname |
+-------------+---------+
| 1 | alice |
| 2 | bob |
| 3 | charlie |
| 5 | eve |
+-------------+---------+
4 rows in set (0.00 sec)
mysql> use `customer:80-`;
Database changed
mysql> select * from customer;
+-------------+-------+
| customer_id | uname |
+-------------+-------+
| 4 | dan |
+-------------+-------+
1 row in set (0.00 sec)