delete Mysql MySQL delete MySQL MySQL MySQL MySQL MySQL InnoDBMyISAMMemory BMySQL MySQL 16KBMySQL MySQL B B MySQL MySQL MySQL ACID MySQL MySQL MySQL MySQL deletetruncatedrop drop > truncate >> DELETEDELETEDELETE from TABLE_NAME where xxxDELETE DMLtrigger InnoDB DELETEmysql delete ** DELETErollback segementcommit;delete from table_name,MyISAM InnoDB ;delete from table_name where xxx , InnoDBMyISAM;delete optimize table table_name InnoDBMyISAM deleteoptimize table SQLMcsjdemodemo2select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size from information_schema.tables where table_schema='csjdemo' AND table_name='demo2';Sizeoptimize table demo2sizedelete redoundo rollback) dropDrop table TablenameDDLTruncate drop table table_name InnoDB MyISAM; drop (constrain)(trigger)(index); /, invalid drop deletetruncatedroptruncateDDL rollback segment trigger truncate table table_name InnoDBMyISAM truncate tabledrop table creat,create table drop table;truncate auto_incrementMyISAMtruncateauto_increment1deleteauto_incrementInnoDBtruncateauto_increment1deleteauto_incrementdeleteMySQLauto_increment1InnoDB auto_incrementdelete auto_increment1auto_increment SELECT 1+MAX(ai_col) FROM t truncateDELETE DELETEDELETEInnoDBdeletedeleteInnoDBInnoDBtablespace > segmentinode > Extent >PageInnodbsegmentextentsegmentFREE_PAGE32page32page1extent1extent32MBextent32MB4extentMySQLdata pageMySQL CrashInnodbInnoDBUndo****MySQLinformation_schema****innodb_file_per_table=1table_name

delete Mysql MySQL delete

MySQL MySQL

MySQL MySQL

MySQL InnoDBMyISAMMemory B

MySQL MySQL 16KB

MySQL MySQL B B MySQL

MySQL MySQL ACID MySQL

MySQL MySQL

MySQL

deletetruncatedrop

drop > truncate >> DELETE

DELETE

DELETE from TABLE_NAME where xxx

DELETE DMLtrigger

InnoDB DELETEmysql delete **

DELETErollback segementcommit;

delete from table_name,MyISAM InnoDB ;

delete from table_name where xxx , InnoDBMyISAM;

delete optimize table table_name InnoDBMyISAM deleteoptimize table

SQLMcsjdemodemo2

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size    from information_schema.tables       where table_schema='csjdemo' AND table_name='demo2';

Size

optimize table demo2

size

delete redoundo rollback)

drop

Drop table Tablename

DDLTruncate

drop table table_name InnoDB MyISAM; drop (constrain)(trigger)(index); /, invalid

drop

deletetruncatedrop

truncate

DDL rollback segment trigger

truncate table table_name InnoDBMyISAM truncate tabledrop table creat,create table drop table;

truncate auto_increment

• MyISAMtruncateauto_increment1deleteauto_increment
• InnoDBtruncateauto_increment1deleteauto_incrementdeleteMySQLauto_increment1
• InnoDB auto_incrementdelete auto_increment1auto_increment SELECT 1+MAX(ai_col) FROM t

truncate

DELETE

DELETE

DELETE

InnoDBdeletedelete

InnoDB

InnoDB

tablespace > segmentinode > Extent >PageInnodbsegmentextentsegmentFREE_PAGE32page32page1extent1extent32MBextent32MB4extent

MySQLdata pageMySQL Crash

Innodb

InnoDBUndo

****MySQLinformation_schema

****innodb_file_per_table=1table_name.ibdtable_name.frm

**Undo**Undoflashbackundo

MySQL 8.0

mysqlCREATE TABLESPACE tablespace_name    ADD DATAFILE 'file_name'               #    USE LOGFILE GROUP logfile_group        #2logfile    [EXTENT_SIZE [=] extent_size]          #    [INITIAL_SIZE [=] initial_size]        #     [AUTOEXTEND_SIZE [=] autoextend_size]  #    [MAX_SIZE [=] max_size]                #size32G    [NODEGROUP [=] nodegroup_id]           #    [WAIT]    [COMMENT [=] comment_text]    ENGINE [=] engine_name

HDDSSD500Gvglvmountlv/hot_data /cold_data

SSDHDD

mysql#create tablespace tbs_data_hot add datafile '/hot_data/tbs_data_hot01.dbf' max_size 20G;#create table booking(id bigint not null primary key auto_increment,  ) tablespace tbs_data_hot;#create tablespace tbs_data_cold add datafile '/hot_data/tbs_data_cold01.dbf' max_size 20G;#create table payment_log(id bigint not null primary key auto_increment,  ) tablespace tbs_data_cold;#alter table payment_log tablespace tbs_data_hot;

Inndob

mysqlmysql> create table user(id bigint not null primary key auto_increment,     -> name varchar(20) not null default '' comment '',     -> age tinyint not null default 0 comment 'age',     -> gender char(1) not null default 'M'  comment '',    -> phone varchar(16) not null default '' comment '',    -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',    -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''    -> ) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '';Query OK, 0 rows affected (0.26 sec)

bash# ls -lh user1.ibd -rw-r----- 1 mysql mysql 96K Nov  6 12:48 user.ibd

innodb_file_per_table=1segmentextent32data page96KBextent64undo segment

shell# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000000, page type <Freshly Allocated Page>page offset 00000000, page type <Freshly Allocated Page>Total number of page: 6:      #Freshly Allocated Page: 2     #Insert Buffer Bitmap: 1       #File Space Header: 1          #B-tree Node: 1                #File Segment inode: 1         #inondeibdata1.ibdinode

mysqlmysql> DELIMITER $$mysql> CREATE PROCEDURE insert_user_data(num INTEGER)     -> BEGIN    ->     DECLARE v_i int unsigned DEFAULT 0;    -> set autocommit= 0;    -> WHILE v_i < num DO    ->    insert into user(`name`, age, gender, phone) values (CONCAT('lyn',v_i), mod(v_i,120), 'M', CONCAT('152',ROUND(RAND(1)*100000000)));    ->  SET v_i = v_i+1;    -> END WHILE;    -> commit;    -> END $$Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;#10wmysql> call insert_user_data(100000);Query OK, 0 rows affected (6.69 sec)

shell# ls -lh user.ibd-rw-r----- 1 mysql mysql 14M Nov 6 10:58 /data2/mysql/test/user.ibd# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0001>   #12.........................................................page offset 00000000, page type <Freshly Allocated Page>Total number of page: 896:Freshly Allocated Page: 493Insert Buffer Bitmap: 1File Space Header: 1B-tree Node: 400File Segment inode: 1

delete

mysqlmysql> select min(id),max(id),count(*) from user;+---------+---------+----------+| min(id) | max(id) | count(*) |+---------+---------+----------+|       1 |  100000 |   100000 |+---------+---------+----------+1 row in set (0.05 sec)#5000014MB7MBmysql> delete from user limit 50000;Query OK, 50000 rows affected (0.25 sec)#14MB# ls -lh /data2/mysql/test/user1.ibd -rw-r----- 1 mysql mysql 14M Nov  6 13:22 /data2/mysql/test/user.ibd## python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0001>........................................................page offset 00000000, page type <Freshly Allocated Page>Total number of page: 896:Freshly Allocated Page: 493Insert Buffer Bitmap: 1File Space Header: 1B-tree Node: 400File Segment inode: 1#MySQL

mysqlmysql> use information_schema;Database changedmysql> SELECT A.SPACE AS TBL_SPACEID, A.TABLE_ID, A.NAME AS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE,  B.INDEX_ID , B.NAME AS INDEX_NAME, PAGE_NO, B.TYPE AS INDEX_TYPE FROM INNODB_SYS_TABLES A LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID =B.TABLE_ID WHERE A.NAME = 'test/user1';+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+| TBL_SPACEID | TABLE_ID | TABLE_NAME | FILE_FORMAT | ROW_FORMAT | SPACE_TYPE | INDEX_ID | INDEX_NAME | PAGE_NO | INDEX_TYPE |+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+|        1283 |     1207 | test/user | Barracuda   | Dynamic    | Single     |     2236 | PRIMARY    |       3 |          3 |+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+1 row in set (0.01 sec)PAGE_NO = 3 B-treeroot page3INDEX_TYPE = 3 INDEX_TYPE0 = nonunique secondary index; 1 = automatically generated clustered index (GEN_CLUST_INDEX); 2 = unique nonclustered index; 3 = clustered index; 32 = full-text index;#

MySQLdelflag:Ndelflag:Ycommitpurgeinsertdeletedeleteinnblock

Innodb

100%50%""

100%Innodbpage1/16An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growthupdate

mysqlmysql> select table_schema,    ->        table_name,ENGINE,    ->        round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb,TABLE_ROWS,    ->        round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, round(DATA_FREE/DATA_LENGTH*100,2) free_ratio    -> from information_schema.TABLES where  TABLE_SCHEMA= 'test'    -> and TABLE_NAME= 'user';+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| table_schema | table_name | ENGINE | total_mb | TABLE_ROWS | data_mb | index_mb | free_mb | free_ratio |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| test         | user      | InnoDB |        4 |      50000 |       4 |        0 |       6 |     149.42 |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+1 row in set (0.00 sec)

data_free

InnoDBIODMLRDSDML

shell#InnoDBmysql> alter table user engine=InnoDB;Query OK, 0 rows affected (9.00 sec)Records: 0  Duplicates: 0  Warnings: 0##14MB10M# ls -lh /data2/mysql/test/user1.ibd -rw-r----- 1 mysql mysql 10M Nov 6 16:18 /data2/mysql/test/user.ibd

mysqlmysql> select table_schema,        table_name,ENGINE,        round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb,TABLE_ROWS,        round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, round(DATA_FREE/DATA_LENGTH*100,2) free_ratio from information_schema.TABLES where  TABLE_SCHEMA= 'test' and TABLE_NAME= 'user';+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| table_schema | table_name | ENGINE | total_mb | TABLE_ROWS | data_mb | index_mb | free_mb | free_ratio |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| test         | user      | InnoDB |        5 |      50000 |       5 |        0 |       2 |      44.29 |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+1 row in set (0.00 sec)

deleteSQL

SQL

mysql#100Wmysql> call insert_user_data(1000000);Query OK, 0 rows affected (35.99 sec)#mysql> alter table user add index idx_name(name), add index idx_phone(phone);Query OK, 0 rows affected (6.00 sec)Records: 0  Duplicates: 0  Warnings: 0#mysql> show index from user;+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| user  |          0 | PRIMARY   |            1 | id          | A         |      996757 |     NULL | NULL   |      | BTREE      |         |               || user  |          1 | idx_name  |            1 | name        | A         |      996757 |     NULL | NULL   |      | BTREE      |         |               || user  |          1 | idx_phone |            1 | phone       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)#mysql> flush status;Query OK, 0 rows affected (0.00 sec)#SQLmysql> select id, age ,phone from user where name like 'lyn12%';+--------+-----+-------------+| id     | age | phone       |+--------+-----+-------------+|    124 |   3 | 15240540354 ||   1231 |  30 | 15240540354 ||  12301 |  60 | 15240540354 |.............................| 129998 |  37 | 15240540354 || 129999 |  38 | 15240540354 || 130000 |  39 | 15240540354 |+--------+-----+-------------+11111 rows in set (0.03 sec)mysql> explain select id, age ,phone from user where name like 'lyn12%';+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                 |+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+|  1 | SIMPLE      | user  | range | idx_name      | idx_name | 82      | NULL | 22226 | Using index condition |+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+1 row in set (0.00 sec)#mysql> select * from information_schema.session_status where variable_name in('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');+-------------------+----------------+| VARIABLE_NAME     | VARIABLE_VALUE |+-------------------+----------------+| HANDLER_READ_NEXT | 11111          |    #| INNODB_DATA_READS | 7868409        |    #| INNODB_PAGES_READ | 7855239        |    #| LAST_QUERY_COST   | 10.499000      |    #SQLCOSTIO_COSTCPU_COST+-------------------+----------------+4 rows in set (0.00 sec)

SQL

mysql#50wmysql> delete from user limit 500000;Query OK, 500000 rows affected (3.70 sec)#mysql> analyze table user;+-----------+---------+----------+----------+| Table     | Op      | Msg_type | Msg_text |+-----------+---------+----------+----------+| test.user | analyze | status   | OK       |+-----------+---------+----------+----------+1 row in set (0.01 sec)#mysql> flush status;Query OK, 0 rows affected (0.01 sec)mysql> select id, age ,phone from user where name like 'lyn12%';Empty set (0.05 sec)mysql> explain select id, age ,phone from user where name like 'lyn12%';+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                 |+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+|  1 | SIMPLE      | user  | range | idx_name      | idx_name | 82      | NULL | 22226 | Using index condition |+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+1 row in set (0.00 sec)mysql> select * from information_schema.session_status where variable_name in('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');+-------------------+----------------+| VARIABLE_NAME     | VARIABLE_VALUE |+-------------------+----------------+| HANDLER_READ_NEXT | 0              || INNODB_DATA_READS | 7868409        || INNODB_PAGES_READ | 7855239        || LAST_QUERY_COST   | 10.499000      |+-------------------+----------------+4 rows in set (0.00 sec)

deletedelete

delete

serviceAPPuser-servicesearch-serviceproduct-servicelocation-serviceprice-serviceDMLdelete

mysql#create database mt_user charset utf8mb4;grant USAGE, SELECT, INSERT, UPDATE ON mt_user.*  to 'w_user'@'%' identified by 't$W*g@gaHTGi123456';flush privileges;

delete

MySQL4create_time

1. 7create_time
2. T +100:30create_time

mysql`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '01',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''#deleteupdateupdate user set is_deleted = 1 where user_id = 1213;#is_deletedselect id, age ,phone from user where is_deleted = 0 and name like 'lyn12%';

mysql#1. _bakCREATE TABLE `ota_order_bak` (  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '',  `order_id` varchar(255) DEFAULT NULL COMMENT 'id',  `ota_id` varchar(255) DEFAULT NULL COMMENT 'ota',  `check_in_date` varchar(255) DEFAULT NULL COMMENT '',  `check_out_date` varchar(255) DEFAULT NULL COMMENT '',  `hotel_id` varchar(255) DEFAULT NULL COMMENT 'ID',  `guest_name` varchar(255) DEFAULT NULL COMMENT '',  `purcharse_time` timestamp NULL DEFAULT NULL COMMENT '',  `create_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  `create_user` varchar(255) DEFAULT NULL,  `update_user` varchar(255) DEFAULT NULL,  `status` int(4) DEFAULT '1' COMMENT '  1   0 ',  `hotel_name` varchar(255) DEFAULT NULL,  `price` decimal(10,0) DEFAULT NULL,  `remark` longtext,  PRIMARY KEY (`id`),  KEY `IDX_order_id` (`order_id`) USING BTREE,  KEY `hotel_name` (`hotel_name`) USING BTREE,  KEY `ota_id` (`ota_id`) USING BTREE,  KEY `IDX_purcharse_time` (`purcharse_time`) USING BTREE,  KEY `IDX_create_time` (`create_time`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (to_days(create_time)) ( PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')), PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')), PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')), PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')), PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')), PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')), #2. create table tbl_p201808 as select * from ota_order where create_time between '2018-08-01 00:00:00' and '2018-08-31 23:59:59';#3. alter table ota_order_bak exchange partition p201808 with table tbl_p201808; #4. delete from ota_order where create_time between '2018-08-01 00:00:00' and '2018-08-31 23:59:59' limit 3000;

mysql#1. CREATE TABLE `ota_order_2020` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (to_days(create_time)) ( PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')), PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')), PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')), PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')), PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')), PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')), #2. 100WdataXGodataXjsoninsert into ota_order_2020 select * from ota_order where create_time between '2020-08-01 00:00:00' and '2020-08-31 23:59:59';#3. alter table ota_order rename to ota_order_bak;  alter table ota_order_2020 rename to ota_order;#4. insert into ota_order select * from ota_order_bak a where not exists (select 1 from ota_order b where a.id = b.id);#5. ota_order_bakdataX#6. #create table ota_order_mid like ota_order;#alter table ota_order exchange partition p201808 with table ota_order_mid; ##alter table ota_order_bak exchange partition p201808 with table ota_order_mid; 

SQL

InnoDBdeletedeleteSQL

CPUDML

MySQLDDL

ClickhouseClickhouseTTL

Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.(Email:[email protected])