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

This article introduces the relevant knowledge of "Why not use delete when mysql deletes data". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The data volume of some tables is growing rapidly, and the corresponding SQL scans a lot of invalid data, causing SQL to slow down. After confirmation, these large tables are pipelined, recorded, and log data, which only need to be retained for 1 to 3 months. At this time, you need to clean up the table to achieve slimming.

In this article, I will explain why delete is not recommended to delete data in terms of InnoDB storage space distribution, the impact of delete on performance, and optimization recommendations.

InnoDB storage architecture

As can be seen from this diagram, the InnoDB storage structure mainly consists of two parts: logical storage structure and physical storage structure.

Logically, it is composed of tablespace tablespace-> segment segment or inode-> zone Extent-- > data page Page. The logical management unit of Innodb is segment, and the smallest unit of space allocation is extent. Each segment allocates 32 page from tablespace FREE_PAGE. When the 32 page is insufficient, it will be extended according to the following principle: if the current extent is less than 1 extent;, when the tablespace is less than 32MB, extend one extent at a time The tablespace is larger than 32MB, extending 4 extent at a time.

Physically, it is mainly composed of system user data files and log files, the data files mainly store MySQL dictionary data and user data, and the log files record the changes of data page, which are used for MySQL Crash recovery.

Innodb tablespace

InnoDB storage includes three types of tablespaces: system tablespaces, user tablespaces and Undo tablespaces.

System tablespace: mainly stores data dictionary data within MySQL, such as data under information_schema.

User tablespace: when innodb_file_per_table=1 is turned on, the data table is stored independently from the system tablespace in the data file with the table_name.ibd command, and the structure information is stored in the table_name.frm file.

Undo tablespaces: store Undo information, such as snapshot consistent reads and flashback, using undo information.

Starting with MySQL 8.0, users are allowed to customize tablespaces, with the following syntax:

CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' # data file name USE LOGFILE GROUP logfile_group # Custom log filegroup, usually 2 logfile per group. [EXTENT_SIZE [=] extent_size] # Zone size [INITIAL_SIZE [=] initial_size] # initialization size [AUTOEXTEND_SIZE [=] autoextend_size] # automatic widening size [MAX_SIZE [=] max_size] # maximum size of a single file, the maximum is 32G. [NODEGROUP [=] nodegroup_id] # Node group [WAIT] [COMMENT [=] comment_text] ENGINE [=] engine_name

This advantage is that you can separate hot and cold data, using HDD and SSD to store data, which can not only achieve efficient data access, but also save costs. For example, you can add two 500G hard disks, create a volume group vg, divide a logical volume lv, create a data directory and mount the corresponding lv, assuming that the two directories are / hot_data and / cold_data respectively.

In this way, the core business tables such as user tables and order tables can be stored on a high-performance SSD disk, and some logs and pipelining tables can be stored on a normal HDD. The main steps are as follows:

# create hot data tablespace create tablespace tbs_data_hot add datafile'/ hot_data/tbs_data_hot01.dbf' max_size 20G * create core business tables to be stored in hot data tablespace create table booking (id bigint not null primary key auto_increment, … ) tablespace tbs_data_hot;# create cold data table space create tablespace tbs_data_cold add datafile'/ hot_data/tbs_data_cold01.dbf' max_size 20G position # create log, pipelined, backup type tables stored in cold data table space create table payment_log (id bigint not null primary key auto_increment,... ) tablespace tbs_data_cold;# can move tables to another tablespace alter table payment_log tablespace tbs_data_hot Inndob storage distribution creates an empty table to view spatial changes mysql > create table user (id bigint not null primary key auto_increment,-> name varchar (20) not null default''comment' name',-> age tinyint not null default 0 comment 'age',-> gender char (1) not null default' M' comment 'gender',-> phone varchar (16) not null default''comment' phone number' -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time'->) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'user information table' Query OK, 0 rows affected (0.26 sec) # ls-lh user1.ibd-rw-r- 1 mysql mysql 96K Nov 6 12:48 user.ibd

When you set the parameter innodb_file_per_table=1, a segment is automatically created when the table is created, and an extent containing 32 data page is assigned to store data. The default size of the created empty table is 96KB, and 64 connection pages will be applied for after the extent is used. In this way, for some small tables, or undo segment, you can apply for less space at the beginning to save disk capacity.

# python2 py_innodb_page_info.py-v / data2/mysql/test/user.ibdpage offset 00000000, page type page offset 00000001, page type page offset 00000002, page type page offset 00000003, page type, page level page offset 00000000, page type page offset 00000000 Page type Total number of page: 6: # Total number of pages allocated Freshly Allocated Page: 2 # available data page Insert Buffer Bitmap: 1 # insert buffer page File Space Header: 1 # filespace header B-tree Node: 1 # data page File Segment inode: 1 # file side inonde If it is on ibdata1.ibd, there will be multiple inode. Spatial changes after data insertion: mysql > DELIMITER $$mysql > CREATE PROCEDURE insert_user_data (num INTEGER)-> BEGIN-> DECLARE Veteri int unsigned DEFAULT 0;-> set autocommit= 0;-> WHILE Viteri

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.