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:

    1. create table customer_seq(id bigint, next_id bigint, cache bigint, primary key(id)) comment 'vitess_sequence';
2. 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 0
    • next_id should be set to the next (starting) value of the sequence
    • cache 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 to 3 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.

    1. "customer_seq": { "type": "sequence" }

    Once setup this way, you can use the special select next syntax to generate values from this sequence:

    1. mysql> select next 2 values from customer_seq;
2. +---------+
3. | nextval |
4. +---------+
5. |       1 |
6. +---------+
7. 1 row in set (0.00 sec)

9. mysql> select next 1 values from customer_seq;
10. +---------+
11. | nextval |
12. +---------+
13. |       3 |
14. +---------+
15. 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:

    1. "auto_increment": {
2. "column": "customer_id",
3. "sequence": "product.customer_seq"
4. }

    Alternate VSchema DDL:

    1. alter vschema add sequence product.customer_seq;
2. 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:

    1. mysql> insert into customer(uname) values('alice'),('bob'),('charlie'),('dan'),('eve');
2. Query OK, 5 rows affected (0.03 sec)

4. mysql> use `customer:-80`;
5. Database changed
6. mysql> select * from customer;
7. +-------------+---------+
8. | customer_id | uname   |
9. +-------------+---------+
10. |           1 | alice   |
11. |           2 | bob     |
12. |           3 | charlie |
13. |           5 | eve     |
14. +-------------+---------+
15. 4 rows in set (0.00 sec)

17. mysql> use `customer:80-`;
18. Database changed
19. mysql> select * from customer;
20. +-------------+-------+
21. | customer_id | uname |
22. +-------------+-------+
23. |           4 | dan   |
24. +-------------+-------+
25. 1 row in set (0.00 sec)