MySQL performance optimization GUID primary key

Because the system uses a large number of GUIDs as primary keys, the data file is large and the data query speed is slow. The intent is converted to an int primary key. First look at the results of the next single table test GUID and int as the primary key respectively.

The test conditions are as follows:

AMD e1-1200 APU 1.4G 2GRam 100Lan, 250GB HD

Mysql 5.5,innodb_buffer_pool_size = 512M

Test table order_dish_history:60 fields, a total of 5 indexes, 4 GUID index, 1 timestamp+pk indexes for table partitioning, the other fields two tables are identical; table is divided into 12 partitions one partition per month, 100,000 data per partition

GUID table and int table information

100,000 data:

SELECT Sql_no_cache sum (dish_num), sum (Dish_money), sum (Dish_price),
AVG (Dish_num), avg (Dish_money), avg (Dish_price),
Max (Dish_num), Max (Dish_money), Max (Dish_price)
From Order_dish_history_int WHERE business_date between ' 2014-01-01 ' and ' 2014-01-02 '
GROUP by dish_id

500,000 data:

Read 5 sub-table data, where business_date between ' 2014-01-01 ' and ' 2014-05-02 '

1 million data:

Read 10 sub-table data, where business_date between ' 2014-01-01 ' and ' 2014-10-02 '

You can see the INT key in the data volume, the query speed and the memory requirements of the machine obviously wins.

Make changes to the data type of the table structure

Data types are changed to Tinyint,mediumint unsigned and so on depending on the data value range.

For distributed linkage, a federated primary key is used at the headquarters. For example, you can take the unique identity of the branch shop_id + branch The primary key of the table

MySQL performance optimization GUID primary key