Summary of the pitfalls of using primary keys and rowids in MySQL

Preface

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

• How to sense the existence of rowid;
• What is the relationship between rowid and primary key?
• What hidden dangers exist in the use of primary keys?
• How to understand potential rowid bottlenecks and debug validation.

This article will discuss these issues with you. The test environment is based on MySQL version 5.7.19.

Question 1: How to detect the existence of rowid

Let us illustrate this with a case.

I remember one day when I was counting the backup data, I wrote a SQL statement. When I saw the execution result, I found that the SQL statement was not complete. After completing the statistical work, I prepared 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 should not be so many records in one day. This is obviously wrong. What went wrong?

I looked at the SQL carefully and found that there was no group by. We randomly found 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 this part is not checked. From a grammatical point of view, this is allowed; but in later versions, such as version 5.7 and later, it is not supported. Therefore, 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 | 184 |

| 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 I am curious about the parsing logic. It seems that SQL parses the first line and then outputs the count(*) operation. Obviously, this is information that cannot be obtained from the execution plan.

If we change our 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.01 sec)

For verification, we can use _rowid to do preliminary verification.

InnoDB tables will generate an automatically growing primary key of 6 bytes if there is no default primary key. You can query it using select _rowid from table, as follows:

mysql> select _rowid from redis_backup_result limit 5;

+--------+

| _rowid |

+--------+

| 117 |

| 118 |

| 119 |

| 120 |

| 121 |

+--------+

5 rows in set (0.00 sec)

Then we can realize a preliminary idea.

mysql> select _rowid,count(*)from redis_backup_result;

+--------+----------+

| _rowid | count(*) |

+--------+----------+

| 117 | 41036 |

+--------+----------+

1 row in set (0.03 sec)

Then we can further improve it by using rownum. 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_result

r ,(select @rowno:=0) t limit 20;

+-------+--------+

| rowno | _rowid |

+-------+--------+

| 1 | 117 |

| 2 | 118 |

| 3 | 119 |

| 4 | 120 |

| 5 | 121 |

| 6 | 122 |

| 7 | 123 |

| 8 | 124 |

| 9 | 125 |

| 10 | 126 |

| 11 | 127 |

| 12 | 128 |

| 13 | 129 |

| 14 | 130 |

| 15 | 131 |

| 16 | 132 |

| 17 | 133 |

| 18 | 134 |

| 19 | 135 |

| 20 | 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)

From this case, we can clearly see that it is the record in row 1, and then the count(*) operation is performed.

Of course, our goal is to understand some of the relationships between rowid and primary key, so we also review the hidden dangers in the use of primary key.

Question 2: What is the relationship between rowid and primary key?

When studying the index specifications of MySQL development specifications, one key point was emphasized: it is recommended that each table have a primary key. Let’s briefly analyze why here?

In addition to the specifications, in terms of storage methods, in the InnoDB storage engine, tables are stored in the order of primary keys, which we call clustered index tables or index-organized tables (IOTs). The reference basis for the primary key in the table is as follows:

• Explicitly create a primary key.
• Determine whether there is a non-empty unique index in the table. If so, it is the primary key.
• If none of the above conditions are met, a 6-byte bigint unsigned value is generated.

From the above, we can see that MySQL has a maintenance mechanism for primary keys, and some common indexes will also have corresponding impacts. For example, unique indexes, non-unique indexes, covering indexes, etc. are all auxiliary indexes (secondary index, also called secondary index). From the perspective of storage, the secondary index column contains the primary key column by default. If the primary key is too long, the secondary index will also take up a lot of space.

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

This brings up the primary key performance issue that is very common in the industry. This is not a single problem and requires continuous transformation in the direction of MySQL to combine technical value and business value. I have seen auto-increment columns set up in many businesses, but in most cases, this auto-increment column has no actual business meaning. Although the primary key column guarantees the uniqueness of the ID, business developers cannot directly query based on the primary key auto-increment column, so they need to find new business attributes, add a series of unique indexes, non-unique indexes, etc., so there is a deviation between the specifications we adhere to and the way the business uses it.

From another perspective, our understanding of primary keys is biased. We cannot simply assume that primary keys must be integer types starting from 1. We need to look at them in conjunction with business scenarios. For example, our ID card is actually a good example. The ID number is divided into several sections for retrieval and maintenance. Or the serial number obtained when dining out has certain business attributes in it, which is a good reference for us to understand the use of the business.

Question 4: How to understand the potential bottleneck of rowid and debug and verify it

We know that rowid is only 6 bytes, so the maximum value is 2^48. So once row_id exceeds this value, it will still increase. Is there any hidden danger in this case?

Talking without practice is just empty talk. We can do a test to illustrate this.

1) We create a table test_inc without any index.

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 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 3159]

[New LWP 3158]

[New LWP 3157]

[New LWP 3156]

[New LWP 3155]

[New LWP 3154]

[New LWP 3153]

[New LWP 3152]

[New LWP 3151]

[New LWP 3150]

[New LWP 3149]

[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 some basic checks to get the table creation statement and ensure that the test is as expected.

mysql> show create table test_inc\G

*************************** 1. row ***************************

  Table: test_inc

Create Table: CREATE TABLE `test_inc` (

 `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

4) Insert some data so that rowid continues to increase.

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 and adjust it to 2^48

mysql> select power(2,48);

+-----------------+

| power(2,48) |

+-----------------+

|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 three lines of data 4, 5, and 6.

mysql> insert into test_inc values(4),(5),(6); 

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

7) Check the data results and find that rows 1 and 2 have been overwritten.

mysql> select * from test_inc;

+------+

|id|

+------+

| 4 |

| 5 |

| 6 |

| 3 |

+------+

4 rows in set (0.00 sec)

From this, we can see that after the rowid is incremented, there is still a usage bottleneck. Of course, the probability of this is very low. The value of the auto-increment column needs to reach 281 trillion, which is a very large number. From a functional point of view, it is more reasonable to throw an error of writing duplicate values.

With the primary key, the above bottleneck seems to no longer exist.

>>>> References

Rowid debugging refers to Ding Qi's blog

https://www.jb51.net/article/172262.htm

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.