Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the advantages of using uuid instead of self-increasing ID in mysql. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Using uuid instead of self-increasing ID

Advantages:

1. There will be no primary key conflicts during data migration.

2. When using the master and master synchronization of the distributed architecture, there is no need to configure auto_increment_offset/auto_increment_increment to ensure that it is relatively easy to build.

Disadvantages:

1. Uuid takes up more storage space than bigint.

2. When the amount of data reaches more than 500w, the performance is obviously not as fast as bigint.

In what ways do you compare performance? )

Single query

Range query

Range statistics

insert,

Update

Backup and restore.

# # #

# here we only compare the efficiency of a single query

# # #

Environment: single desktop

Ubuntu 14.04 64-bit

Cpu cores=2

Mem=7935M

Test steps:

1. Create tables with id,uuid as the primary key.

2. Insert 100w records respectively (write stored procedures to generate test data, harvest: need to turn off automatic submission, greatly improve efficiency).

3. Execute a single query with id and uuid respectively.

4. Compare query time

The final result is completed in less than 0 seconds.

The script is attached.

# #

DELIMITER $$

USE `test` $$

DROP PROCEDURE IF EXISTS `paired product` uuid` $$

CREATE DEFINER= `root` @ `% `PROCEDURE `paired productuuid` (IN n BIGINT)

BEGIN

DECLARE i INT DEFAULT 1

SET autocommit = 0

WHILE

(i truncate table tmp_id_uuid

Query OK, 0 rows affected (0.14 sec)

Mysql > select count (*) from tmp_id_uuid

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Mysql > call p_product_uuid (100000)

Query OK, 0 rows affected (5.16 sec)

Mysql > select count (*) from tmp_id_uuid

+-+

| | count (*) |

+-+

| | 100000 |

+-+

1 row in set (0.04 sec)

Mysql > truncate table tmp_id_uuid

Query OK, 0 rows affected (0.15 sec)

Mysql > call p_product_uuid (1000000)

Query OK, 0 rows affected (43.03 sec)

Mysql > create table tmp2_id_uuid as select * from tmp_id_uuid

Query OK, 1000000 rows affected (18.07 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

Mysql > select count (*) from tmp2_id_uuid

+-+

| | count (*) |

+-+

| | 1000000 |

+-+

1 row in set (0.31 sec)

Mysql > alter table tmp_id_uuid add primary key (id)

Query OK, 0 rows affected (24.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table tmp2_id_uuid add primary key (uuid)

Query OK, 0 rows affected (25.94 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql >

Mysql >

Mysql > flush tables

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tmp_id_uuid where id=500000

+-- +

| | id | uuid |

+-- +

| | 500000 | e3332083-c743-11e6-bc1e-00e066731e45 |

+-- +

1 row in set (0.00 sec)

Mysql > flush tables

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tmp2_id_uuid where uuid='e3332083-c743-11e6Mui bc1eMei00e066731e45'

+-- +

| | id | uuid |

+-- +

| | 500000 | e3332083-c743-11e6-bc1e-00e066731e45 |

+-- +

1 row in set (0.00 sec)

Mysql >

Mysql >

The above is all the content of this article entitled "what are the advantages of using uuid instead of self-increasing ID in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.