series

MySQL > select * from MySQL; select * from MySQL;

MySQL > select an index for MySQL > select an index for MySQL

Did you know that strings can be indexed in this way? MySQL series 7

MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL

I participated in the development of several projects. With the increase of business volume of each project, MySQL data increased day by day. For example, in one of the projects, the user footprint table was very crazy.

In this article I will look at the performance impact of DELETE and how to delete data in the correct posture.

In MySQL, Innodb storage engine tables have two parts, one is table structure, the other is table data.

/var/lib/mysql. FRM files exist in /var/lib/mysql before MySQL8.0, but not after MySQL8.0. This is because MySQL8.0 already allows table structure definitions to be placed in the data dictionary with the innodb_file_per_table parameter.

Table space

There are several types of tablespaces: system tablespaces, user tablespaces, and undo tablespaces.

System tablespaces: data dictionaries within MySQL, such as data in the INFORMATION_SCHEMA library.

User table Spaces: self-created table structure data

Undo space: Stores undo information for fast rollback.

Before MySQL8.0, the table structure was stored in the system tablespace. After MySQL5.6.6, innodb_file_per_table can be controlled with the parameter innodb_file_per_table.

When set to off, the table data is placed in the system table space, i.e. the MySQL data dictionary is put together.

When set to on, innoDB storage engine table data is stored in. Idb files.

Do you know where the table definitions are stored?

Create a new table evt_sms from kaka.

Guess where the evT_SMS table structure definition created is stored?

SELECT TABLE_NAME,TABLE_COMMENT FROM TABLES WHERE TABLE_TYPE=’BASE TABLE’;

Our custom table type isTABLE_TYPE.

This is to explain that if innodb_file_per_table is set to off, the table data will also be stored there.

Question: If data is stored in a shared tablespace and the table is deleted, will the tablespace be deleted?

The answer is no.

The innodb_file_per_table parameter is set to ON and where is the data stored?

In general, in var/lib/mysql, you’ll see the database you created, and when you go into the database, you’ll see a table for each IBD file.

This is where the data is stored.

conclusion

Remember to set innodb_file_per_table to ON at the beginning of the project. This is the right thing to do.

Ii. Data deletion process

By now you should know that the Innodb storage engine uses a B+ tree data structure, as shown below.

If you delete the record with primary key ID 4 now, Innodb will mark the record with ID 4 as deleted. If you insert a record with ID 4 later, this location may be reused, but the disk file size will not be reduced.

Implicit field

MVCC is implemented by two implicit fields, undo log, and Read View.

The delete flag mentioned above is the delete flag in the implicit field, that is, the record is updated or deleted. The deletion here does not mean that the record is really deleted, but changes the delete flag of the record to true.

inMVCC: I heard people were curious about my low-level implementationThis article also left a foreshadowing, database deletion is really deleted?

Question: What happens if you delete all data on a data page

As with a single piece of data, the entire page of data is reusable.

The reuse of records is limited to the data that meets the scope conditions. For example, the record with ID 4 deleted above will be reused if it is inserted with ID 4.

Here we need to talk about a new knowledge point page merge, if two adjacent data page utilization is very low, the system will merge these two data pages into one page, the other data page will be marked as reusable.

Question: What happens if you use delete to delete the entire table

The answer is that all data pages are marked for reuse, but the disk file size does not change.

Table file size does not change

After adding data, the table data has reached nearly 100W and the file size has reached 108M.

extension

What you should be able to see here is stopped, which is CTRL + Z, and what it’s doing is we’re in the MySQL window, but we don’t want to exit the MySQL window to check the size of the MySQL table file, and then we can execute this command to end the task.

After checking, you can return to the MySQL window by executing fg.

Question: How does Linux display the file unit as M

If you just run the ll command to view the file, you need to manually calculate the file size, which is very inconvenient.

Run the ll -h command to view the file size.

Delete data and check whether the disk file shrinks

In order to see the change of large file size, click click delete all the data in the table, and then look at the file size, still 108M. The file size is unchanged.

How to reduce disk files correctly

In section 3, we show that the file size does not change after 100W data is deleted, which is the effect of the void problem.

Question: How does the void come about?

By now you should know that the emptiness is caused by a lot of additions and deletions.

solution

You can create a new EVT_SMS_copy table and read data from EVt_SMS into EVt_SMs1 in ascending order of primary key ids.

This can solve the problem of disk file size not shrinking due to holes.

Question: Why can it be solved?

Because EVT_SMS_COPY is a new table, and the data is incremented by the primary key ID, the index is tight, and the data page utilization has reached its peak, thus causing the disk file to fail to shrink.

On dry

Run the alter table evt_SMS engine = Innodb command directly to achieve disk file shrinkage.

I need to talk to you about how different versions are handled.

Before MySQL5.5, this command did the same thing, except that evT_sms_copy was not created by itself.

If new data is added during command execution, it will cause data loss because DDL versions prior to MySQL5.5 were not Online. Therefore, no data changes can be made.

MySQL has been updated to version 8 now, if you are a new project use version 8 directly, do not use the old version before 5.6, ka ka has been using MySQL version 8.0 since 18.

MySQL5.6 has been optimized for DDL operations and introduced Online DDL.

Optimized execution process

  • Create a temporary file tmp_file to store the B+ tree of the table in the temporary file. If any operations are performed on the table, they are recorded in the Row log file.
  • After all data is flushed from the original table to the temporary file, the data in the temporary file is the same as the data in the original table.
  • Finally, replace table A’s data files with temporary files.

Origin of Online DDL

You can see that data updates are recorded in the Row log when the disk files are shrunk, meaning that the table can be added, deleted, or checked while the disk space is shrunk.

Pay attention to the point

In the process of shrinking disk files, the original data is scanned and temporary files are added. If your table is very large, it will consume IO and CPU.

Therefore, if you want to do this safely, you can use the open source GH-OST to do it.

conclusion

Alter table evt_sms engine=Innodb if you want to shrink the disk size of a table due to a large number of additions, deletions, and searches, you can run the alter table evt_sms engine=Innodb command to shrink the table space.

Practice is the only criterion for testing the truth of knowledge

Everyone should know that practice is the only criterion for testing the truth of knowledge, so let’s put the conclusions presented in this paper into practice.

  • To perform firstctrl + zThe MySQL task window is displayed
  • performll -hThe disk file size of table EVT_SMS is 108M
  • performfgReturn to the MySQL task window
  • Execute the commandalter table evt_sms engine=Innodb
  • To performctrl + z, the implementation ofll -hThe disk file size has reached 128K.

Alter table ect_sms engine = Innodb can reduce the void problem caused by a large number of tables being added, deleted, or changed. Finally, shrink the table space.

Vi. Development Suggestions

Do not use delete to delete data, but use soft delete, make a mark to delete.

In this way, the problem of void will not appear, and the data traceability will be convenient.

Each table must have three columns create_time, UPDATe_time, and delete_time.

Seven,

Here’s what we need to know from this article.

  • A table that has been added, deleted, or modified extensively will appear hollow
  • To eliminate the void, run alter table evt_SMS engine=Innodb
  • Deleting data using DELETE only makes a mark and does not actually delete the space
  • All the conclusions in this article are based on innodb_file_per_table = ON

“

Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.

“