Почему MySQL не рекомендует удалять данные для удаления

Java база данных

Есть чувства, есть галантерейные товары, поиск в WeChat【Третий принц Ао Бин] Обратите внимание на этого другого программиста.

эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.

предисловие

Есть несколько систем, за которые я отвечаю.С ростом объемов бизнеса данные, хранящиеся в MySQL, быстро увеличиваются.Я думал, что нынешняя сторона бизнеса не будет говорить о боевых искусствах, а будет заниматься скрытными атаками.Быстро, очень быстро, он достиг уровня 100 миллионов. Я был неосторожен, и не было вспышки. Это привело к тому, что SQL таблицы соединений стал очень медленным, а время отклика правильного интерфейса приложения стало больше, что повлияло на пользователя опыт.

После того, как я нашел деловую сторону, я раскритиковал их за то, что они сказали им говорить о боевых искусствах, и быстро извинился передо мной, и это дело было закончено.Когда я ушел, я сказал им, чтобы они не делали этого в следующий раз, сок из мышиного хвоста и отразить это.

После ругани и ругани, дела еще предстоит решить.Когда я проанализировал причины, я обнаружил, что объем данных некоторых таблиц быстро увеличивался, и соответствующий SQL сканировал много недействительных данных, из-за чего SQL тормозил. подтверждение, все эти большие таблицы - проточная вода. , записи и данные типа журнала должны храниться только в течение 1-3 месяцев. В это время необходимо очистить данные таблицы, чтобы добиться потери веса. Как правило, метод вставки + удаления используется для его очистки.

В этой статье я объясню, почему не рекомендуется удалять данные с точки зрения распределения пространства хранения InnoDB, влияние удаления на производительность и предложения по оптимизации.

Архитектура хранения InnoDB

Как видно из этого рисунка, структура хранилища InnoDB в основном состоит из двух частей: логической структуры хранения и физической структуры хранения.

Логически он состоит из табличного пространства табличное пространство —> сегмент сегмент или индексный дескриптор —> область Экстент —> страница данных Страница.Логической единицей управления Innodb является сегмент, а минимальной единицей выделения пространства является экстент.Каждый сегмент будет выделен из табличного пространства FREE_PAGE 32 страниц, когда 32 страниц недостаточно, он будет расширен по следующим принципам: если текущий экстент меньше 1 экстента, он будет расширен до 1 экстента, когда табличное пространство меньше 32 МБ, будет расширен один экстент. расширяться за раз; табличное пространство больше 32 МБ, 4 экстента каждый раз.

Физически он в основном состоит из файлов системных пользовательских данных и файлов журналов.Файлы данных в основном хранят данные словаря MySQL и пользовательские данные.Файлы журналов записывают записи об изменениях страницы данных и используются для восстановления MySQL Crash.

Табличное пространство Innodb

Хранилище InnoDB включает три типа табличных пространств: системное табличное пространство, пользовательское табличное пространство и табличное пространство отмены.

**Системное табличное пространство:** В основном оно хранит данные словаря данных внутри MySQL, например данные в information_schema.

**Пользовательское табличное пространство:** Если включен параметр innodb_file_per_table=1, таблица данных сохраняется независимо от системного табличного пространства в файле данных с помощью команды table_name.ibd, а информация о структуре сохраняется в файле table_name.frm.

**Табличное пространство отмены: **Хранение информации об отмене, такой как согласованное чтение моментального снимка и флэшбэк, оба используют информацию об отмене.

Начиная с MySQL 8.0 разрешены пользовательские табличные пространства.Конкретный синтаксис выглядит следующим образом:

CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'               #数据文件名
    USE LOGFILE GROUP logfile_group        #自定义日志文件组，一般每组2个logfile。
    [EXTENT_SIZE [=] extent_size]          #区大小
    [INITIAL_SIZE [=] initial_size]        #初始化大小 
    [AUTOEXTEND_SIZE [=] autoextend_size]  #自动扩宽尺寸
    [MAX_SIZE [=] max_size]                #单个文件最大size，最大是32G。
    [NODEGROUP [=] nodegroup_id]           #节点组
    [WAIT]
    [COMMENT [=] comment_text]
    ENGINE [=] engine_name

Преимущество этого заключается в том, что данные можно отделить от горячих и холодных, а для хранения используются жесткие диски и твердотельные накопители, что позволяет не только обеспечить эффективный доступ к данным, но и сократить расходы.Например, можно добавить два жестких диска 500G и Можно создать группу томов vg для разделения логического тома lv, создать каталог данных и смонтировать соответствующий lv, предполагая, что два разделенных каталога — это /hot_data и /cold_data.

Таким образом, основные бизнес-таблицы, такие как таблица пользователей и таблица заказов, могут храниться на высокопроизводительном SSD-диске, а некоторые журналы и расходомеры могут храниться на обычном жестком диске.Основные этапы работы следующие:

#创建热数据表空间
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

Создайте пустую таблицу для просмотра изменений пространства

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

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

При установке параметра innodb_file_per_table=1 сегмент будет автоматически создан при создании таблицы, и одновременно будет выделен экстент, который содержит 32 страницы данных для хранения данных Размер по умолчанию пустой таблицы, созданной в таким образом, 96 КБ, и 64 соединения будут применены после того, как экстент будет израсходован.Страницы, так что для некоторых небольших таблиц или сегментов отмены в начале может быть запрошено меньше места, что экономит накладные расходы на дисковую емкость.

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page 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         #文件端inonde，如果是在ibdata1.ibd上会有多个inode。

Пространственное изменение после вставки данных

mysql> 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 ;

#插入10w数据
mysql> call insert_user_data(100000);
Query OK, 0 rows affected (6.69 sec)

# 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.ibd
page 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>   #增加了一个非叶子节点，树的高度从1变为2.
........................................................
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 896:
Freshly Allocated Page: 493
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 400
File Segment inode: 1

Пробел меняется после удаления данных

mysql> select min(id),max(id),count(*) from user;
+---------+---------+----------+
| min(id) | max(id) | count(*) |
+---------+---------+----------+
|       1 |  100000 |   100000 |
+---------+---------+----------+
1 row in set (0.05 sec)
#删除50000条数据，理论上空间应该从14MB变长7MB左右。
mysql> 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.ibd
page 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: 493
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 400
File Segment inode: 1
#在MySQL内部是标记删除，

mysql> use information_schema;

Database changed
mysql> 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-tree的root page是3号页，INDEX_TYPE = 3是聚集索引。 INDEX_TYPE取值如下：
0 = nonunique secondary index; 
1 = automatically generated clustered index (GEN_CLUST_INDEX); 
2 = unique nonclustered index; 
3 = clustered index; 
32 = full-text index;
#收缩空间再后进行观察

MySQL на самом деле не будет удалять пространство внутри, а удалит метку, то есть изменит delflag:N на delflag:Y, и он будет очищен в списке удаления после фиксации.Если в следующий раз будет вставлена ​​более крупная запись, пробел после delete не будет повторно использоваться, если вставленная запись меньше или равна удаляемой записи, она будет использована повторно.Это содержимое можно проанализировать с помощью инструмента innblock от Zhishutang.

Фрагментация в Иннодбе

образование мусора

Мы знаем, что данные, хранящиеся в файловой системе, не всегда могут использовать 100% выделенного для них физического пространства, удаление данных оставит некоторые «дыры» на странице, или случайная запись (нелинейное увеличение кластеризованного индекса) вызовет разделение страниц, разделение страницы приводит к менее чем 50% пространства, используемого страницей. Кроме того, добавление, удаление и изменение таблицы вызовет случайные добавления, удаления и изменения соответствующих значений вторичного индекса, а также вызовет некоторые «дыры» в страницах данных в структуре индекса. , хотя эти дыры могут быть использованы повторно, это в конечном итоге приведет к тому, что часть физического пространства не будет использоваться, то есть мусор.

В то же время, даже если коэффициент заполнения установлен на 100 %, Innodb будет активно оставлять 1/16 пространства страницы для зарезервированного использования (значение innodb_fill_factor, равное 100, оставляет 1/16 пространства на страницах кластеризованного индекса свободным для использования в будущем). рост индекса), чтобы предотвратить переполнение строки, вызванное обновлением.

mysql> 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 — это количество выделенных неиспользуемых байтов, что не означает, что это полностью фрагментированное пространство.

Переработка мусора

Для таблиц InnoDB можно использовать следующие команды для восстановления фрагментов и освобождения места.Это операция ввода-вывода со случайным чтением, которая отнимает много времени и блокирует обычную операцию DML в таблице.Также требуется больше места на диске.Для RDS Для например, это может привести к мгновенному заполнению дискового пространства, мгновенной блокировке экземпляра, и приложение не может выполнять операции DML, поэтому его выполнение в онлайн-среде запрещено.

#执行InnoDB的碎片回收
mysql> alter table user engine=InnoDB;
Query OK, 0 rows affected (9.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

##执行完之后，数据文件大小从14MB降低到10M。
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 10M Nov 6 16:18 /data2/mysql/test/user.ibd

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

Влияние удаления на SQL

Выполнение SQL перед удалением

#插入100W数据
mysql> 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)

#执行SQL语句
mysql> 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      |    #SQL语句的成本COST，主要包括IO_COST和CPU_COST。
+-------------------+----------------+
4 rows in set (0.00 sec)

Выполнение SQL после удаления

#删除50w数据
mysql> 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)

Статистический анализ результатов

действовать COST физические показатели логические чтения Строки сканирования вернуть количество строк время исполнения
Инициализировать вставку 100 Вт 10.499000 7868409 7855239 22226 11111 30ms
100 Вт случайным образом удалить 50 Вт 10.499000 7868409 7855239 22226 0 50ms

Это также показывает, что для обычных больших таблиц нереально использовать удаление данных для уменьшения размера таблицы, поэтому не используйте удаление для удаления данных в любое время, для удаления следует использовать элегантную отметку.

удалить предложения по оптимизации

Управление разрешениями бизнес-аккаунта

Для большой системы необходимо разделить подсистемы в соответствии с бизнес-характеристиками.Каждая подсистема может рассматриваться как служба, такая как приложение Meituan, которое имеет множество служб.Основные службы включают пользовательскую службу пользовательскую службу и поисковый сервис поисковый сервис, товарный продукт-сервис, локационный сервис локационный сервис, прайс-сервис прайс-сервис и др. Каждая служба соответствует базе данных, создайте отдельную учетную запись для базы данных и предоставьте только разрешения DML и не разрешайте удаление, а также запретите доступ между базами данных.

#创建用户数据库并授权
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;

удалить, чтобы отметить удаление

В спецификации моделирования базы данных MySQL есть 4 общедоступных поля, которые в основном необходимы для каждой таблицы.В то же время создание индекса в столбце create_time имеет два преимущества:

  1. Некоторые бизнес-сценарии запросов будут иметь период времени по умолчанию, например 7 дней или один месяц, который фильтруется по create_time, а сканирование индекса выполняется быстрее.
  2. Некоторые основные бизнес-таблицы необходимо извлекать из хранилища данных способом T+1. Например, данные за предыдущий день извлекаются в 00:30 каждую ночь, что фильтруется по create_time.
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否逻辑删除：0：未删除，1：已删除',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'

#有了删除标记，业务接口的delete操作就可以转换为update
update user set is_deleted = 1 where user_id = 1213;

#查询的时候需要带上is_deleted过滤
select id, age ,phone from user where is_deleted = 0 and name like 'lyn12%';

Способ подачи данных

Общие методы архивирования данных

#1. 创建归档表，一般在原表名后面添加_bak。
CREATE 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=utf8
PARTITION 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')), 
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')), 
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')), 
PARTITION p201904 VALUES LESS THAN (to_days('2019-05-01')), 
PARTITION p201905 VALUES LESS THAN (to_days('2019-06-01')), 
PARTITION p201906 VALUES LESS THAN (to_days('2019-07-01')), 
PARTITION p201907 VALUES LESS THAN (to_days('2019-08-01')), 
PARTITION p201908 VALUES LESS THAN (to_days('2019-09-01')), 
PARTITION p201909 VALUES LESS THAN (to_days('2019-10-01')), 
PARTITION p201910 VALUES LESS THAN (to_days('2019-11-01')), 
PARTITION p201911 VALUES LESS THAN (to_days('2019-12-01')), 
PARTITION p201912 VALUES LESS THAN (to_days('2020-01-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;

Оптимизированный способ подачи

#1. 创建中间表
CREATE TABLE `ota_order_2020` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION 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')), 
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')), 
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')), 
PARTITION p201904 VALUES LESS THAN (to_days('2019-05-01')), 
PARTITION p201905 VALUES LESS THAN (to_days('2019-06-01')), 
PARTITION p201906 VALUES LESS THAN (to_days('2019-07-01')), 
PARTITION p201907 VALUES LESS THAN (to_days('2019-08-01')), 
PARTITION p201908 VALUES LESS THAN (to_days('2019-09-01')), 
PARTITION p201909 VALUES LESS THAN (to_days('2019-10-01')), 
PARTITION p201910 VALUES LESS THAN (to_days('2019-11-01')), 
PARTITION p201911 VALUES LESS THAN (to_days('2019-12-01')), 
PARTITION p201912 VALUES LESS THAN (to_days('2020-01-01')));

#2. 插入原表中有效的数据，如果数据量在100W左右可以在业务低峰期直接插入，如果比较大，建议采用dataX来做，可以控制频率和大小，之前我这边用Go封装了dataX可以实现自动生成json文件，自定义大小去执行。
insert 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_bak改造成分区表，如果表比较大不建议直接改造，可以先创建好分区表，通过dataX把导入进去即可。

#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.

Суммировать

Из распределения пространства хранения InnoDB и влияния удаления на производительность мы видим, что физическое удаление удаления не может освободить место на диске, но также генерирует большое количество фрагментов, что приводит к частым разбиениям индекса и влияет на стабильность планов выполнения SQL. ;

В то же время, когда фрагмент перерабатывается, он будет потреблять много ресурсов ЦП и дискового пространства, влияя на нормальную работу DML над таблицей.

На уровне бизнес-кода удаление логической метки должно быть выполнено, чтобы избежать физического удаления; для выполнения требований к архивированию данных это может быть достигнуто с помощью функции таблицы разделов MySQL, которая представляет собой все операции DDL без фрагментации.

Еще одним лучшим решением является Clickhouse, Clickhouse можно использовать для хранения таблиц данных с жизненным циклом и использовать функцию TTL для автоматической очистки недействительных данных.

Я Ао Бин,Чем больше вы знаете, тем больше вы не знаете, спасибо за ваши таланты:как,собиратьиКомментарий, увидимся в следующий раз!

Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.

Категории