Shared Vindexes and Foreign Keys

Let us now look at creating the corder table that will contain orders placed by the customers. It will be beneficial to group the rows of the orders in the same shard as that of the customer that placed the orders. Doing things this way will allow for simpler join queries between customer and corder. There will also be transactional benefits: any transaction that also updates the customer row along with an order will be a single shard transaction.

To make this happen in Vitess, all you have to do is specify that corder.customer_id uses the hash vindex, which is the same one used by customer.customer_id.

This is one situation where a Primary Vindex conceptually differs from a traditional database Primary Key. Whereas a Primary Key makes a row unique, a Vitess Primary Vindex only yields a Unique value. But multiple rows with the same Primary Vindex value can exist.

In other words, the Primary Vindex column need not be the primary key, or unique within MySQL. This is convenient for the corder table because we want customers to place multiple orders. In this case, all orders placed by a customer will have the same customer_id. The Primary Vindex for those will yield the same keyspace id as that of the customer. Therefore, all the rows for that customer’s orders will end up in the same shard along with the customer row.

Since corder rows will need to have their own unique identifier, we also need to create a separate sequence for it in the product keyspace.

  1. create table corder_seq(id bigint, next_id bigint, cache bigint, primary key(id)) comment 'vitess_sequence';
  2. insert into corder_seq(id, next_id, cache) values(0, 1, 3);

VSchema:

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

We create the corder table as follows:

  1. create table corder(corder_id bigint, customer_id bigint, product_id bigint, oname varchar(128), primary key(corder_id));

VSchema:

  1.     "corder": {
  2.       "column_vindexes": [{
  3.           "column": "customer_id",
  4.           "name": "hash"
  5.         }],
  6.       "auto_increment": {
  7.         "column": "corder_id",
  8.         "sequence": "product.corder_seq"
  9.       }
  10.     }

Alternate VSchema DDL:

  1. alter vschema on customer.corder add vindex hash(customer_id);
  2. alter vschema add sequence product.corder_seq;
  3. alter vschema on customer.corder add auto_increment corder_id using product.corder_seq;

Inserting into corder yields the following results:

  1. mysql> insert into corder(customer_id, product_id, oname) values (1,1,'gift'),(1,2,'gift'),(2,1,'work'),(3,2,'personal'),(4,1,'personal');
  2. Query OK, 5 rows affected (0.03 sec)
  4. mysql> use `customer:-80`;
  5. Database changed
  6. mysql> select * from corder;
  7. +-----------+-------------+------------+----------+
  8. | corder_id | customer_id | product_id | oname    |
  9. +-----------+-------------+------------+----------+
  10. |         1 |           1 |          1 | gift     |
  11. |         2 |           1 |          2 | gift     |
  12. |         3 |           2 |          1 | work     |
  13. |         4 |           3 |          2 | personal |
  14. +-----------+-------------+------------+----------+
  15. 4 rows in set (0.00 sec)
  17. mysql> use `customer:80-`;
  18. Database changed
  19. mysql> select * from corder;
  20. +-----------+-------------+------------+----------+
  21. | corder_id | customer_id | product_id | oname    |
  22. +-----------+-------------+------------+----------+
  23. |         5 |           4 |          1 | personal |
  24. +-----------+-------------+------------+----------+
  25. 1 row in set (0.00 sec)

As expected, orders are created in the same shard as their customer. Selecting orders by their customer id goes to a single shard:

  1. mysql> select * from corder where customer_id=1;
  2. +-----------+-------------+------------+-------+
  3. | corder_id | customer_id | product_id | oname |
  4. +-----------+-------------+------------+-------+
  5. |         1 |           1 |          1 | gift  |
  6. |         2 |           1 |          2 | gift  |
  7. +-----------+-------------+------------+-------+
  8. 2 rows in set (0.00 sec)

Joining corder with customer also goes to a single shard. This is also referred to as a local join:

  1. mysql> select c.uname, o.oname, o.product_id from customer c join corder o on c.customer_id = o.customer_id where c.customer_id=1;
  2. +-------+-------+------------+
  3. | uname | oname | product_id |
  4. +-------+-------+------------+
  5. | alice | gift  |          1 |
  6. | alice | gift  |          2 |
  7. +-------+-------+------------+
  8. 2 rows in set (0.01 sec)

Performing the join without a customer_id constraint still results in a local join, but the query is scattered across all shards:

  1. mysql> select c.uname, o.oname, o.product_id from customer c join corder o on c.customer_id = o.customer_id;
  2. +---------+----------+------------+
  3. | uname   | oname    | product_id |
  4. +---------+----------+------------+
  5. | alice   | gift     |          1 |
  6. | alice   | gift     |          2 |
  7. | bob     | work     |          1 |
  8. | charlie | personal |          2 |
  9. | dan     | personal |          1 |
  10. +---------+----------+------------+
  11. 5 rows in set (0.00 sec)

However, adding a join with product results in a cross-shard join for the product part ot the query:

  1. mysql> select c.uname, o.oname, p.pname from customer c join corder o on c.customer_id = o.customer_id join product p on o.product_id = p.product_id;
  2. +---------+----------+----------+
  3. | uname   | oname    | pname    |
  4. +---------+----------+----------+
  5. | alice   | gift     | monitor  |
  6. | alice   | gift     | keyboard |
  7. | bob     | work     | monitor  |
  8. | charlie | personal | keyboard |
  9. | dan     | personal | monitor  |
  10. +---------+----------+----------+
  11. 5 rows in set (0.01 sec)

Although the underlying work performed by Vitess is not visible here, you can see it in the bottom right panel if using the demo app. Alternatively, you can also stream this information with the following command:

  1. curl localhost:12345/debug/querylog
  2. [verbose output not shown]

Foreign Keys

More generically stated: If a table has a foreign key into another table, then Vitess can ensure that the related rows live in the same shard by making them share a common Unique Vindex.

In cases where you choose to group rows based on their foreign key relationships, you have the option to enforce those constraints within each shard at the MySQL level. You can also configure cascade deletes as needed. However, overuse of foreign key constraints is generally discouraged in MySQL.

Foreign key constraints across shards or keyspaces are not supported in Vitess. For example, you cannot specify a foreign key between corder.product_id and product.product_id.

Many-to-Many relationships

In the case where a table has relationships with multiple other tables, you can only choose one of those relationships for shard grouping. All other relationships will end up being cross-shard, and will incur cross-shard penalties.

If a table has strong relationships with multiple other tables, and if performance becomes a challenge choosing either way, you can explore the VReplication Materialization feature that allows you to materialize a table both ways.

Enforcing Uniqueness

To enforce global uniqueness for a row in a sharded table, you have to have:

  • A Unique Vindex on the column
  • A Unique constraint at the MySQL level

A Primary Vindex coupled with a Primary Key constraint makes a row globally unique.

A Unique Vindex can also be specified for a non-unique column. In such cases, it is likely that you will be using that column in a where clause, and will require a secondary non-unique index on it at the MySQL level.