CODEWITHSUNDEEP
mysqlnode.jsuuidknex.js
shotasenga
shotasenga

Reputation: 979

Efficient/Scalable way to store and retrieve UUID as binary on MySQL

I am using UUID as the PK (BINARY(16)) for the tables on MySQL. Which option is better to convert the binary to/from UUID string?

The first option is using MySQL built-in functions UUID_TO_BIN and BIN_TO_UUID. This is the easiest to handle UUID/binary convert. But I guess it could cause performance problems at some point.

The second option is to run the convert on the application layer (Node.js). This way, the performance will not be a big problem since web servers are horizontally scalable. However, the code could get a bit messy.

From my research, Postgres handles UUID very well. Does MySQL perform well as same as Postgres and no need to worry about performance?

My code for each method is something like below.

MySQL

INSERT INTO mytable SET id = UUID_TO_BIN("709fe2ce-be43-11ea-a4e2-784f439a6382", 1);

SELECT BIN_TO_UUID(id, 1) as id FROM mytable WHERE id = UUID_TO_BIN("709fe2ce-be43-11ea-a4e2-784f439a6382", 1);

Application (Node.js with knex)

async function add(data: DataType): Promise<void> {
  await knex("mytable").insert({
    ...data,
    id: toBinaryUUID(data.uuid),
  });
}

async function find(uuid: string): Promise<DataType | undefined> {
  const query = knex
    .select("id")
    .from("mytable")
    .where("id", toBinaryUUID(uuid));

  const record = await query.first();

  if (record) {
    record.id = fromBinaryUUID(record.id);
  }

  return record;
}

(I use odo-network/binary-uuid as the converting functions.)

function fromBinaryUUID(buf: Buffer): string {
  return [
    buf.toString("hex", 4, 8),
    buf.toString("hex", 2, 4),
    buf.toString("hex", 0, 2),
    buf.toString("hex", 8, 10),
    buf.toString("hex", 10, 16),
  ].join("-");
}

function toBinaryUUID(uuid: string): Buffer {
  const buf = Buffer.from(uuid.replace(/-/g, ""), "hex");
  return Buffer.concat([
    buf.slice(6, 8),
    buf.slice(4, 6),
    buf.slice(0, 4),
    buf.slice(8, 16),
  ]);
}

Upvotes: 1

Views: 2705

Answers (1)

Rick James
Rick James

Reputation: 142278

In MySQL 8, those functions should be used.

  • 16 bytes is better than 36 characters (space, etc)
  • The functions rearrange the bits so that uuids that were created about the same time are "close" to each other in the table. This provides a temporal locality of reference, thereby speeding up a significant class of queries.
  • Hand-rolled conversion routines could, but probably don't, rearrange the bits.
  • Only Type 1 uuids benefit from the rearrangement, but that is the type that MySQL uses.
  • If your uuids come from elsewhere, the rearrangement does not hurt, but may not help.
  • If you are not using MySQL 8.0, those routines are not built-in, but can be found in the link below.
  • With MySQL 8.0, include the swap_flag (second argument) as 1.

More discussion: http://mysql.rjweb.org/doc.php/uuid

Upvotes: 4

Related Questions