A brief analysis of whether MySQL primary key uses numbers or uuids for faster query

In actual development, the primary key of MySQL cannot be repeated, and the primary key may be automatically incremented. In order to prevent the duplication of the primary key, an algorithm such as the snowflake algorithm may be adopted. Both types of primary keys are saved as number types, but in actual development, uuid may be generated as the primary key. So the question is, which primary key is more efficient?

The following is verified by testing:

1. First, we create a table, use a stored procedure to generate 1 million pieces of data, and then analyze them:

Create the table:

CREATE TABLE `my_tables` (
`id` VARCHAR(32) NOT NULL ,
`name` varchar(32) DEFAULT NULL,
`age` int(32) DEFAULT NULL,
`time` varchar(32) DEFAULT NULL,
`pwd` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Create a stored procedure:

DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
DECLARE n int DEFAULT 1;
loopname:LOOP
INSERT INTO `my_tables`(`name`,`age`,`time`,`pwd`) VALUES ('张三', 18, '0:0:0:0:0:0:0:1', '369');
SET n=n+1;
IF n=1000000 THEN
LEAVE loopname;
END IF;
END LOOP loopname;
END;

Generate data:

CALL my_insert();

The first method uses a primary key of type number.

The following figure is obtained by explaining the analysis

2. Next, continue to create another table and generate data

CREATE TABLE `my_tables2` (
`id` VARCHAR(32) NOT NULL ,
`name` varchar(32) DEFAULT NULL,
`age` int(32) DEFAULT NULL,
`time` varchar(32) DEFAULT NULL,
`pwd` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Note: The primary key of this table is of type varchar.

Create a stored procedure and generate data:

DROP PROCEDURE IF EXISTS proc_insert;
DELIMITER $
CREATE PROCEDURE pro_insert2()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000000 DO
INSERT INTO `my_tables2`(id,`name`,`age`,`time`,`pwd`) VALUES (i,'张三', 18, '0:0:0:0:0:0:0:1', '369');
SET i = i+1;
END WHILE;
END $;

CALL pro_insert2();

The following figure shows the result of the second query using the primary key

If you don’t understand explain, you can go to the blog https://blog.csdn.net/why15732625998/article/details/80388236

The analysis shows that the difference in query efficiency between using uuid or numbers as primary keys is not as big as expected, and can be almost ignored. Only key_len is slightly different.

key_len: indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query.不損失精確性的情況下，長度越短越好. The value displayed by key_len is the maximum possible length of the index field, not the actual length used. That is, key_len is calculated based on the table definition, not retrieved from the table.

This concludes this article on whether it is faster to use numbers or uuids for MySQL primary keys. For more information on whether to use numbers or uuids for MySQL primary keys, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!