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)