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

This article mainly shows you "what are the traps for the use of primary keys and rowid in MySQL", the content is simple and clear, and I hope it can help you solve your doubts. Let the editor lead you to study and learn about "what are the traps for the use of primary keys and rowid in MySQL".

Preface

You may have heard the concept of rowid in MySQL, but it is difficult to test the practice, and there will inevitably be some confusion, such as:

How to feel the presence of rowid

What is the relationship between rowid and primary key

What are the hidden dangers in the use of primary keys

How to understand potential bottlenecks in rowid and debug verification.

This article will discuss these issues with you, and the testing environment is based on MySQL version 5.7.19.

Question 1. How to feel the existence of rowid

We might as well illustrate it through a case.

I remember one day when I was counting the backup data, I wrote a SQL. When I saw the execution result, I found that the SQL statement was not complete. After I finished the statistical work, I was going to analyze this SQL statement.

Mysql > select backup_date, count (*) piece_no from redis_backup_result;+-+-+ | backup_date | piece_no | +-+-+ | 2018-08-14 | 40906 | +-+-+ 1 row in set (0.03 sec)

According to the characteristics of the business, there must not be so many records in one day, which is obviously wrong. What went wrong?

I took a closer look at SQL and found that there was no group by, and we randomly found out 10 pieces of data.

Mysql > select backup_date from redis_backup_result limit 10 backup_date | +-+ | 2018-08-14 | | 2018-08-14 | | 2018-08-14 | | 2018-08-15 | | 2018-08-15 | | 2018-08-15 | | 2018-08-15 | | 2018-08-15 | | 2018-08-15 | 2018-08-15 | +-+ 10 rows in set (0.00 sec) |

In earlier versions, the database parameter sql_mode is empty by default and does not validate this part, which is allowed from a syntax point of view, but it is not supported after a higher version, such as version 5.7, so the solution is very simple. After adding group by, the result is as expected.

Mysql > select backup_date, count (*) piece_no from redis_backup_result group by backup_date +-+-+ | backup_date | piece_no | +-+-+ | 2018-08-14 | 3 | 2018-08-15 | 121 | 2018-08-16 | 2018 | 08-17 | 3284 | | 2018-08-18 | 7272 | 2018-08-19 | 7272 | | 2018-08-20 | 7272 | | 2018-08-21 | | | 7272 | | 2018-08-22 | 8226 | +-+-+ 9 rows in set (0.06 sec) |

But curious about the logic of parsing, it seems that SQL parses the first line and then outputs the operation of count (*), which is obviously not available from the execution plan.

If we change our way of thinking, we can see that this table has more than 40,000 records.

Mysql > select count (*) from redis_backup_result;+-+ | count (*) | +-+ | 40944 | +-+ 1 row in set (0.01sec)

In order to verify, we can use the method of _ rowid to do preliminary verification.

An auto-growing primary key with 6 bytes of space is generated in the InnoDB table without a default primary key, which can be queried with select _ rowid from table, as follows:

Mysql > select _ rowid from redis_backup_result limit 5 rows in set + | _ rowid | +-+ | 117,118,119,120,121 | +-+ 5 rows in set (0.00 sec)

Then a preliminary train of thought can be realized.

Mysql > select _ rowid,count (*) from redis_backup_result;+-+-+ | _ rowid | count (*) | +-+-+ | 41036 | +-+-+ 1 row in set (0.03 sec)

Then continue to sublimate a little, with the help of rownum to achieve, of course, this feature is not natively supported in MySQL, and needs to be implemented indirectly.

Mysql > SELECT @ rowno:=@rowno+1 as rowno,r._rowid from redis_backup_resultr, (select @ rowno:=0) t limit 20 +-+-+ | rowno | _ rowid | +-+-+ | 1 | 117 | 2 | 118 | 3 | 119 | 4 | 5 | 121 | 6 | 6 | 122 | 7 | 8 | 124 | 9 | 125 | 10 | 126 | 11 | 12 | 12 | 12 | 129 | 14 | 14 | 15 | 131 | 16 | 132 | 17 | 133 | 18 | 134 | 19 | 20 | 136 | 136 | + | -+-+ 20 rows in set (0.00 sec)

Write a complete statement, as follows:

Mysql > SELECT @ rowno:=@rowno+1 as rowno,r._rowid, backup_date,count (*) from redis_backup_result r, (select @ rowno:=0) t +-+ | rowno | _ rowid | backup_date | count (*) | +-+ | 1 | 117 | 2018-08-14 | 41061 | +-- -+ 1 row in set (0.02 sec)

Through this case, you can obviously find that it is the first line of the record, and then do the operation of count (*).

Of course, our goal is to master some of the relationships between rowid and primary keys, so we also review the hidden problems in the use of primary keys.

Question 2. What is the relationship between rowid and primary key

When learning the index specification of the MySQL development specification, one important point was emphasized: every table is recommended to have a primary key. Let's briefly analyze why here.

In addition to the specification, in terms of storage method, in the InnoDB storage engine, tables are stored in the order of primary keys, which we call clustered index tables or index organization tables (IOT). The references of primary keys in tables are as follows:

Explicitly create the primary key Primary key.

Determines whether there is a non-empty unique index in the table, and if so, the primary key.

If none of the above conditions are met, a 6-byte bigint unsigned value is generated.

As can be seen from the above, MySQL has a maintenance mechanism for the primary key, and some common indexes will also have a corresponding impact, such as unique index, non-unique index, overlay index, etc. are all secondary indexes (secondary index, also known as secondary index). From a storage point of view, the secondary index column contains the primary key column by default, if the primary key is too long, it will also make the secondary index take up a lot of space.

Question 3. What are the hidden dangers in the use of primary keys?

This leads to a very common primary key performance problem in the industry, which is not a single problem, but requires continuous transformation in the direction of MySQL, combining technical value with business value. I see that self-increment columns are set up in many businesses, but in most cases, such self-increment columns have no actual business meaning. Although the primary key column ensures the uniqueness of ID, business developers cannot query directly according to the primary key self-increment column, so they need to find new business attributes, add a series of unique indexes, non-unique indexes, and so on. As a result, there is a deviation between the specification we adhere to and the way the business is used.

From another dimension, our understanding of the primary key is biased. We cannot simply think that the primary key must be an integer type starting from 1. We need to look at it in combination with the business scenario. For example, our ID card is actually a good example, dividing the certificate number into several sections, preferring to retrieve and maintain. Or the order number obtained when eating out, it all has certain business attributes in it, which is a good reference for us to understand the use of business.

Question 4. How to understand and debug the potential bottleneck of rowid

We know that rowid is only 6 bytes, so the maximum value is 2 ^ 48, so once the row_id exceeds this value, it will still be incremented, whether there is a hidden danger in this case.

Just talking but not practicing fake tricks, we can do a test to explain.

1) We create a table test_inc that does not contain any indexes.

Create table test_inc (id int) engine=innodb

2) get the corresponding process number through ps-ef | grep mysql, and use gdb to start debugging the configuration. Remember! This should be your own test environment.

[root@dev01 mysql] # gdb-p 3132-ex'p dict_sys- > row_id=1'-batch [New LWP 3192] [New LWP 3160] [New LWP 3158] [New LWP 3158] [New LWP 3157] [New LWP 3156] [New LWP 3155] [New LWP 3154] [New LWP 3153] [New LWP 3152] [New LWP 3152] [New LWP 3150] [New LWP 3148] [New LWP 3147] [New LWP 3144] [New LWP 3143] [New LWP 3142] [New LWP 3141] ] [New LWP 3140] [New LWP 3139] [New LWP 3138] [New LWP 3137] [New LWP 3136] [New LWP 3135] [New LWP 3134] [New LWP 3133] [Thread debugging using libthread_db enabled] 0x00000031ed8df283 in poll () from / lib64/libc.so.6 $1 = 1

3) We do the basic test and get the table-building statement to ensure that the test is as expected.

Mysql > show create table test_inc\ gateway * 1. Row * * Table: test_incCreate Table: CREATE TABLE `test_ inc` (`id` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

4) insert some data to make rowid continue to increase itself.

Mysql > insert into test_inc values (1), (2), (3); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0

5) We reset the rowid to 2 ^ 48

Mysql > select power (2p48); +-+ | power (2p48) | +-+ | 281474976710656 | +-+ 1 row in set (0.00 sec) [root@dev01 mysql] # gdb-p 3132-ex'p dict_sys- > row_id=281474976710656'-batch. [Thread debugging using libthread_db enabled] 0x00000031ed8df283 in poll () from / lib64/libc.so.6 $1 = 281474976710656

6) continue to write some data, for example, we write 4, 5, 5 and 6 three rows of data.

Mysql > insert into test_inc values (4), (5), (6); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0

7) looking at the results of the data, it is found that two lines have been overwritten.

Mysql > select * from test_inc;+-+ | id | +-+ | 4 | | 5 | 6 | | 3 | +-+ 4 rows in set (0.00 sec)

As a result, we can see that there is still a bottleneck in the use of rowid since it is incremented. Of course, this probability is very low. It is necessary to increase the value of the column to 281 trillion, which is quite a large number. Functionally, it is more reasonable to throw an error that writes duplicate values.

With the primary key, the above bottleneck does not seem to exist.

The above is all the contents of this article entitled "what are the traps for the use of primary keys and rowid in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow 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.