Primary key design for MySQL cluster database table

Use of MySQL database, without exception in the design will encounter the primary key selection, generally in the following three choose one or more, self-growth columns, UUID and Uuid_short, the focus of the characteristics of the primary key, presumably everyone knows very well, I will not elaborate, in the InnoDB engine , choose which kind of primary key is better, the net also has many posts to have the description, basically is the suggestion is the self-growth column or with the UUID as the logical primary key use together, but if is the Ndbcluster engine?

For this purpose I did a special test, the environment for 4 of logistics machines (2c,8g memory) Do the data node, noofreplicas=2, first build three tables.

CREATE TABLE ' Int_key ' (
' id ' int (ten) unsigned not NULL auto_increment,
' CNAME ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=ndbcluster auto_increment=1 DEFAULT charset=latin1;

/*table structure for Table ' uuid_key ' */

CREATE TABLE ' Uuid_key ' (
' UUID ' varchar (+) not NULL,
' CNAME ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' uuid ')
) Engine=ndbcluster DEFAULT charset=latin1;

/*table structure for Table ' uuid_short_key ' */

CREATE TABLE ' Uuid_short_key ' (
' Id_short ' bigint () unsigned not NULL,
' CNAME ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' Id_short ')
) Engine=ndbcluster DEFAULT charset=latin1;

Build three stored procedure loops insert 10 million records respectively

INSERT into Int_key (CNAME) VALUES (UUID ());

INSERT into Uuid_key (uuid, CNAME) VALUES (REPLACE (UUID (), '-', '), uuid ());

INSERT into Uuid_short_key (Id_short, CNAME) VALUES (Uuid_short (), UUID ());

These three kinds of primary key tables in the data insertion time is different, the Uuid_short_key table is the fastest, first inserted, it finished, Uuid_key table inserted 9.86 million records, almost, but Int_key table is slow, only inserted 6.51 million records, It is therefore considered that the table of the self-growing column primary key does not dominate in scenarios where big data is high and concurrent inserts.

The speed of the query is then tested:

SELECT COUNT (0) from Int_key;
SELECT COUNT (0) from Uuid_short_key;
SELECT COUNT (0) from Uuid_key;

Statistics Record count is very fast, millisecond returns results

SELECT * FROM Int_key WHERE id = 418606;
SELECT * from Uuid_short_key WHERE id_short = 23786248796730416;
SELECT * from Uuid_key WHERE ' uuid ' = ' 666cd86c9bbc11e4a819005056a877bb ';

Query by primary key is also very fast, the millisecond level returns the result

SELECT * from int_key WHERE cname = ' 34007ECB-9B06-11E4-A819-005056A877BB ';
SELECT * from uuid_short_key WHERE cname = ' 321C7290-9B89-11E4-A819-005056A877BB ';
SELECT * from uuid_key WHERE cname = ' 666CD882-9BBC-11E4-A819-005056A877BB ';

An non-primary key and no index field query test, I have checked each SQL 20 times, 20 times the total time spent, the first is 96.12 seconds, the second is 96.1 seconds, the third is 101.14 seconds, you can see the first two tables query time is basically consistent, and the last one to slow down, may be related to the space occupied by the UUID more than the numeric field.

From the test results, in the MySQL cluster based on the Ndbcluster engine, using uuid_short as the table's primary key, both in reading and writing are the best choice, the above only represents the test results feedback point of view, for your reference!

Primary key design for MySQL cluster database table