Comparison of test GUID and int self-increment primary key performance in MySQL summary applicable scenario "turn"

one. Create the following three data tables: INT primary key self-increment table, GUID primary key table, relational table associated with the above two tables tbl_test_relation

CREATE TABLE ' tbl_test_int ' (
' id ' INT (one) not NULL auto_increment,
' name ' VARCHAR (+) null DEFAULT null,
' comment ' VARCHAR (+) null DEFAULT null,
PRIMARY KEY (' id ')
)
comment= ' test int primary key performance '
collate= ' utf8_general_ci ';


CREATE TABLE ' tbl_test_measure ' (
' Code ' CHAR (*) is not NULL,
' DeviceID ' INT (+) null DEFAULT null,
' value ' INT (+) null DEFAULT null,
' value2 ' INT (+) null DEFAULT null,
PRIMARY KEY (' code ')
)
comment= ' Test guid as unique ';

CREATE TABLE ' tbl_test_relation ' (
' id ' INT (one) not NULL auto_increment,
' Intvalue ' INT (one) not NULL DEFAULT ' 0 ',
' Codevalue ' CHAR (+) not NULL DEFAULT ' 0 ',
PRIMARY KEY (' id ')
)
collate= ' utf8_general_ci ';

two. Inserting data into the data table

Insert into Tbl_test_int (name,comment) Values
(' testmy ', ' Haha, hehe ');

Insert, 1 million data time, 30 minutes or so

Insert into Tbl_test_measure (code,deviceid,value,value2) VALUES (GUID. NewGuid (), ();

Insert 1 million data for 2,845 seconds


Insert into Tbl_test_relation (intvalue,codevalue)
(select a.ID as ID, b.code as code from Tbl_test_int A, tbl_test_measure b where a.id = B.deviceid
and a.ID < 100000 and a.id > 40000)
and then insert it into the relational table for about 30 seconds.


three. Query for performance separately

Select a.ID, A.codevalue, b.name from Tbl_test_relation A, tbl_test_int b where a.intvalue = b.ID limit 10000,800 xx;
/* Affected rows:0 has found records: 79,900 Warning: 0 Duration 1 query:0.110 sec. (+ 0.967 sec. Network) */

Select a.ID, A.codevalue, B.value2,b.value from Tbl_test_relation A, tbl_test_measure b where a.codevalue = B.code Limit 10000,80000;
/* Affected rows:0 has found records: 79,900 Warning: 0 Duration 1 query:0.234 sec. (+ 1.997 sec. Network) */

Time difference is about one times

Selecta.ID,a.Codevalue,b.name fromtbl_test_relationa,Tbl_test_intbwherea.Intvalue=b.IDLimit9000,90000; / * Affected rows:0 has found records: 80,900 Warning: 0 Duration 1 query:0.094 sec. (+ 0.998 sec. Network) */* Error while updating query history: Failed to S ET data for ' 1 ' * /Selecta.ID,a.Codevalue,b.value2 fromtbl_test_relationa,tbl_test_measurebwherea.Codevalue=b.CodeLimit9000,90000; / * Affected rows:0 has found records: 80,900 Warning: 0 Duration 1 query:0.203 sec. (+ 1.856 sec. Network) */* Error while updating query history: Failed to S ET data for ' 1 ' * /

Four. Test summary

1.GUID can ensure data uniqueness, data security, data portability, suitable for the same table in the distributed database, storage in different libraries. But the Select, Update, and Insert are relative to int, which is about half as slow in millions data.
2.ID is unique with int self-increment, which can improve the performance of data operations, but it needs to be aware of its unique operation

Comparison of test GUID and int self-increment primary key performance in MySQL summary applicable scenario "turn"