Detailed explanation of performance measurement comparison of MySQL-uuid as primary key and int as primary key

By chance, I learned that mysqlthe primary key type uses varchar to store UUIDQueryThe performance is not as good as int type as the primary key. Searching a lot of information on the Internet is only theoretical. Therefore, I wrote the code myself for actual testing. The following results are for reference only and are not authoritative.

The fields of the three tables, except that the primary key ID uses varchar, bigint and auto-increase bigint respectively, the other three fields are all varchar 36 bits

Database: mysql5.5

Table type: InnoDB

Data volume: 100W items

The first case:

The primary key uses uuid 32 bits.

Run query statement 1: SELECT COUNT(id) FROM test_varchar;

Run query statement 2: SELECT * FROM test_varchar WHERE vname='00004629-b052-11e1-96aa-002655b28d7b ';

Run query statement 3: SELECT * FROM test_varchar WHERE id='00004599b05211e196aa002655b28d7b';

The average time consumed by statement 1 is: 2.7 seconds;

The time consumed by statement 2 The average time is: 3 seconds;

The average time consumed by statement 3 is: 0 seconds; (Multi-party test, as long as there is a primary key ID in the condition, the query speed will display 000 in milliseconds. The ID values ​​of the test include the first one hundred There are also more than 900,000 items. The query time is exactly the same, and the millisecond level is 000)

Second case:

The primary key is used. bigint, use uuid_short() to generate data, the data is a sequence of pure numbers (22461015967875697). (It is equivalent to automatic growth, except that the fixed base value is larger.)

Run query statement 1: SELECT COUNT(id) FROM test_long;

Run query statement 2: SELECT * FROM test_long WHERE vname='d7f28a24-b053-11e1-96aa-002655b28d7b';

Run query statement 3: SELECT * FROM test_long WHERE id='22461015967875702';

The average time consumed by statement 1 is : 1.2 seconds;

The average time consumed by statement 2 is: 1.40 seconds; The average time consumed by

statement 3 is: 0 seconds; (Multi-party test, as long as there is a primary key ID in the condition, the query speed is milliseconds Levels all display 000. The tested ID values ​​range from the first 100 to the last 900,000. The query times are exactly the same, and the millisecond level is all 000)

Third case:

Run query statement 1: SELECT COUNT(id) FROM test_int;

Run query statement 2: SELECT * FROM test_int WHERE vname='c80f8427-b059-11e1-96aa-002655b28d7b ';

Run query statement 3: SELECT * FROM test_int WHERE id=900000;

The primary key uses the automatic growth that comes with mysql, and the data is pure numbers (1, 2, 3, 4, 5...).

The average time consumed by query statement 1 is: 1.07 seconds;

The average time consumed by query statement 2 is: 1.31 seconds;

The average time consumed by query statement 3 is: 0 seconds ;(Multi-party test, as long as there is a primary key ID in the condition, the query speed in milliseconds will show 000. The tested ID values ​​range from the first 100 to more than 900,000. The query time is exactly the same, and the milliseconds will all be 000 )

Summary: It can be seen that the automatic growth performance of mysql InnoDB primary key has higher performance.

The author says to himself: In normal project development, those with ID in the conditions of the SQL statement account for the majority, and those without ID account for the minority. Although the above test shows that as long as there is a primary key ID in the conditional statement, the query time is exactly the same regardless of the primary key type. However, you cannot guarantee that all sql statements in your project have IDs in the conditions, so... I believe you will already understand which type of primary key should be used.

Database: mysql5.5

##Table type: MyISAM

Data volume: 100W

In order to write fewer words and save time, the tables and sql statements used in this test are the same as above. Only Record the elapsed time.

The first case:

The primary key uses uuid 32 bits.

The average time consumed by statement 1 is: 0 seconds;

The average time consumed by statement 2 is: 0.53 seconds;

The average time consumed by statement 3 is: 0 seconds ;(Multi-party test, as long as there is a primary key ID in the condition, the query speed in milliseconds will show 000. The tested ID values ​​range from the first 100 to more than 900,000. The query time is exactly the same, and the milliseconds will all be 000 )

Second case:

The primary key uses bigint, and uuid_short() is used to generate data. The data is a sequence of pure numbers (22461015967875697 ). (It is equivalent to automatic growth, except that the fixed base value is larger.)

The average consumption time of statement 1 is: 0 seconds;

The average consumption time of statement 2 is: 0.51 seconds;

The average consumption time of statement 3 is: 0 seconds; (Multi-party test, as long as there is a primary key ID in the condition, the query speed will display 000 in milliseconds. The tested ID values ​​range from the first 100 to more than 900,000) The query time is exactly the same, and the millisecond level is 000)

The third case:

The primary key uses the automatic growth that comes with mysql , the data is pure numbers (1, 2, 3, 4, 5...).

The average time consumed by statement 1 is: 0 seconds;

The average time consumed by statement 2 is: 0.48 seconds;

The average time consumed by statement 3 is: 0 seconds; (Many parties In the test, as long as there is a primary key ID in the condition, the query speed in milliseconds is 000. The ID values ​​tested include the first one hundred and the last 900,000. The query time is exactly the same, and the millisecond level is 000)##.

#Summary: It can be seen that the automatic growth performance of mysql MyISAM primary key has a slight advantage over others. The test data is 1 million. If it is 1000W 100 million, I think this advantage will be greater. If you also have foreign key related queries, this advantage will be even more obvious. Of course, if the system you design has a data volume that does not exceed 1 million, it doesn’t matter what primary key type you use. My test computer is a laptop. If it is a professional server, it is estimated to be 1 million. These tests of mysql MyISAM cannot measure the time difference at all.

The above is the detailed content of Detailed explanation of performance measurement comparison of MySQL-uuid as primary key and int as primary key. For more information, please follow other related articles on the PHP Chinese website!