Shulou(Shulou.com)05/31 Report--

Today, I would like to share with you why MySQL can not use uuid as the primary key of the relevant knowledge, detailed content, clear logic, I believe that most people still know too much about this knowledge, so share this article for your reference, I hope you can learn something after reading this article, let's take a look at it.

Preface

When designing tables in mysql, mysql officially recommends not to use uuid or Snowflake id that is discontinuous and unique (long is unique and incremented on a single machine), but to recommend a continuous and self-increasing primary key id. The official recommendation is auto_increment, so why not use uuid? what are the disadvantages of using uuid?

1. Mysql and program examples

1.1. To illustrate this, let's first set up three tables

They are user_auto_key,user_uuid,user_random_key, which represents the auto-growing primary key, and uuid as the primary key.

Random key as the primary key, the rest of us remain completely the same.

According to the control variable method, we only generate the primary key of each table using a different strategy, while the other fields are exactly the same, and then test the table insertion speed and query speed:

Note: the random key here actually refers to the discontinuous, non-repetitive and irregular id calculated by the snowflake algorithm: a string of 18-bit long values.

1.2. You can't just have the theory, just go to the program and use spring's jdbcTemplate to achieve additional inspection and testing:

Technical framework: springboot+jdbcTemplate+junit+hutool, the principle of the program is to connect their own test database, and then write the same amount of data in the same environment to analyze the time of insert insertion to synthesize its efficiency. In order to achieve the most real effect, all data are randomly generated, such as names, mailboxes and addresses.

Package com.wyq.mysqldemo;import cn.hutool.core.collection.CollectionUtil;import com.wyq.mysqldemo.databaseobject.UserKeyAuto;import com.wyq.mysqldemo.databaseobject.UserKeyRandom;import com.wyq.mysqldemo.databaseobject.UserKeyUUID;import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;import com.wyq.mysqldemo.util.JdbcTemplateService;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest Import org.springframework.util.StopWatch;import java.util.List;@SpringBootTestclass MysqlDemoApplicationTests {@ Autowired private JdbcTemplateService jdbcTemplateService; @ Autowired private AutoKeyTableService autoKeyTableService; @ Autowired private UUIDKeyTableService uuidKeyTableService; @ Autowired private RandomKeyTableService randomKeyTableService; @ Test void testDBTime () {StopWatch stopwatch = new StopWatch ("time consumed to execute sql") / * auto_increment key task * / final String insertSql = "INSERT INTO user_key_auto (user_id,user_name,sex,address,city,email,state) VALUES (?,?)"; List insertData = autoKeyTableService.getInsertData (); stopwatch.start ("automatic generation of key task starts"); long start1 = System.currentTimeMillis () If (CollectionUtil.isNotEmpty (insertData)) {boolean insertResult = jdbcTemplateService.insert (insertSql, insertData, false); System.out.println (insertResult);} long end1 = System.currentTimeMillis (); System.out.println ("time consumed by auto key:" (end1-start1)); stopwatch.stop () / * * uudID's key * / final String insertSql2 = "INSERT INTO user_uuid (id,user_id,user_name,sex,address,city,email,state) VALUES (?,?)"; List insertData2 = uuidKeyTableService.getInsertData (); stopwatch.start ("UUID's key task starts"); long begin = System.currentTimeMillis () If (CollectionUtil.isNotEmpty (insertData)) {boolean insertResult = jdbcTemplateService.insert (insertSql2, insertData2, true); System.out.println (insertResult);} long over = System.currentTimeMillis (); System.out.println ("time consumed by UUID key:" (over-begin)); stopwatch.stop () / * * Random longs key * / final String insertSql3 = "INSERT INTO user_random_key (id,user_id,user_name,sex,address,city,email,state) VALUES (?)"; List insertData3 = randomKeyTableService.getInsertData (); stopwatch.start ("Random longs key task starts"); Long start = System.currentTimeMillis () If (CollectionUtil.isNotEmpty (insertData)) {boolean insertResult = jdbcTemplateService.insert (insertSql3, insertData3, true); System.out.println (insertResult);} Long end = System.currentTimeMillis (); System.out.println ("Random key task elapsed time: + (end-start)); stopwatch.stop (); String result = stopwatch.prettyPrint () System.out.println (result);}

1.3. Program write result

It can be seen that when the amount of data is about 100W, the insertion efficiency of uuid ranks the bottom, and the data of 130W is added in the later order, and the time of uudi decreases in a straight line.

The overall efficiency of time consumption can be ranked as follows: auto_key > random_key > uuid,uuid, the efficiency is the lowest, in the case of a large amount of data, the efficiency plummets. So why did this happen? With doubt, let's discuss this question:

2. Comparison of index structure between using uuid and self-increasing id

2.1. Use the internal structure of self-increasing id

The values of the self-increasing primary key are sequential, so Innodb stores each record after one record. When the maximum fill factor of the page is reached (the default maximum fill factor of innodb is 15tick 16 of the page size, the space of 1max 16 will be reserved for later modification):

The next record of ① will be written to a new page. Once the data is loaded in this order, the primary key page will fill up with almost sequential records, increasing the maximum filling ratio of the page without page waste.

The newly inserted row of ② must be on the next row of the original most big data row. Mysql positioning and addressing is very fast, so there is no extra consumption to calculate the location of the new row.

③ reduces page fragmentation and fragmentation

2.2. The internal structure of the index using uuid

Because uuid is irregular relative to sequential self-incrementing id, the value of the new row does not have to be larger than that of the previous primary key, so innodb cannot always insert the new row at the end of the index, but needs to find a new appropriate location for the new row to allocate new space.

This process requires a lot of additional operations, and the lack of order of the data will lead to scattered data distribution, which will lead to the following problems:

The target page written by ① is likely to have been flushed to disk and removed from the cache, or has not been loaded into the cache. Innodb has to find and read the target page from disk into memory before inserting, which will result in a large number of random IO.

Because ② writes are out of order, innodb has to do page splitting frequently to allocate space for new rows. Page splitting results in moving a large amount of data, requiring at least three pages to be modified at a time.

Due to frequent page splits in ③, pages become sparse and filled irregularly, resulting in data fragmentation.

After loading random values (uuid and Snowflake id) into the clustered index (the default index type of innodb), it is sometimes necessary to do an OPTIMEIZE TABLE to rebuild the table and optimize the page fill, which will take some time.

Conclusion: when using innodb, you should insert as much as possible according to the self-increasing order of the primary key, and use the monotonously increased value of the cluster key to insert new rows as much as possible.

2.3. Disadvantages of using self-increasing id

So there is no harm in using self-increasing id? No, self-adding id also has the following problems:

① once others crawl your database, they can get your business growth information according to the self-added id of the database, and it is easy to analyze your business situation.

② for high concurrent loads, innodb will cause obvious lock contention when pressing the primary key to insert, and the upper bound of the primary key will become the focus of competition, because all insertions take place here, and concurrent insertions will lead to gap lock competition.

③ Auto_Increment lock mechanism will cause self-increasing lock snatching, and there is a certain performance loss.

Attachment: lock contention in Auto_increment. If you want to improve, you need to tune the configuration of innodb_autoinc_lock_mode.

These are all the contents of the article "Why MySQL can't use uuid as the primary key". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.