日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識，其中主要整理了解決delete大量數(shù)據(jù)后空間不釋放的相關(guān)問題，MySQL 中 insert 與 update 都可能導(dǎo)致頁分裂，這樣就存在碎片，下面一起來看一下，希望對大家有幫助。

MySQL 的幾種碎片整理方案總結(jié)（解決delete大量數(shù)據(jù)后空間不釋放的問題）

1.背景知識?

1.1 為什么會有碎片?

MySQL 中 insert 與 update 都可能導(dǎo)致頁分裂，這樣就存在碎片。

對于大量的UPDATE，也會產(chǎn)生文件碎片化 ， Innodb的最小物理存儲分配單位是頁(page)，而UPDATE也可能導(dǎo)致頁分裂（page split），頻繁的頁分裂，頁會變得稀疏，并且被不規(guī)則的填充，所以最終數(shù)據(jù)會有碎片。

delete 語句實際上只是給數(shù)據(jù)打個標記，并且記錄到一個鏈表中，這樣就形成了留白空間。

在InnoDB中，刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。InnoDB的Purge線程會異步的來清理這些沒用的索引鍵和行。

當執(zhí)行插入操作時，MySQL會嘗試使用空白空間，但如果某個空白空間一直沒有被大小合適的數(shù)據(jù)占用，仍然無法將其徹底占用，就形成了碎片；

總結(jié)：

truncate table其實有點類似于drop table 然后creat,只不過這個create table 的過程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近drop table的速度;

drop ，truncate 立刻釋放磁盤空間 ，不管是 Innodb和MyISAM ;

delete from table_name刪除表的全部數(shù)據(jù),對于MyISAM 會立刻釋放磁盤空間 （應(yīng)該是做了特別處理，也比較合理）,InnoDB 不會釋放磁盤空間;

對于 delete from table_name where xxx; 帶條件的刪除, 不管是innodb還是MyISAM都不會釋放磁盤空間;

delete操作以后使用optimize table table_name 會立刻釋放磁盤空間。不管是innodb還是myisam 。所以要想達到釋放磁盤空間的目的，delete以后執(zhí)行optimize table 操作。

delete from表以后雖然未釋放磁盤空間，但是下次插入數(shù)據(jù)的時候，仍然可以使用這部分空間。

表的增刪改操作，可能會造成數(shù)據(jù)空洞的，當對表進行大量的增刪改操作后，數(shù)據(jù)空洞存在的可能性比較大。

MySQL刪除數(shù)據(jù)幾種情況以及是否釋放磁盤空間:

1.2 碎片帶來的問題

當MySQL對數(shù)據(jù)進行掃描時，它掃描的對象實際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分；

MySQL數(shù)據(jù)庫中的表在進行了多次delete、update和insert后，表空間會出現(xiàn)碎片。定期進行表空間整理，消除碎片可以提高訪問表空間的性能。

這種碎片不僅額外增加了存儲代價，同時也因為數(shù)據(jù)碎片化降低了表的掃描效率。

碎片若不整理，那么可能會長期占據(jù)磁盤空間，導(dǎo)致磁盤使用率越來越高。

2. 如何清理碎片？

修復(fù)問題的前提是要先找到問題，這樣才能對癥下藥。

2.1. 查看表的碎片情況

查看數(shù)據(jù)庫中每個存在碎片的表

mysql> select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';
+-----------------------------------------------------------+-----------+--------+
| concat('optimize table ',table_schema,'.',table_name,';') | DATA_FREE | ENGINE |
+-----------------------------------------------------------+-----------+--------+
| optimize table abc.t_user_answer;                         |   2097152 | InnoDB |
| optimize table mysql.time_zone_transition;                |   4194304 | InnoDB |
| optimize table mysql.time_zone_transition_type;           |   4194304 | InnoDB |
| optimize table mysql.user;                                |   4194304 | InnoDB |
。。。。

查看指定表的碎片情況

mysql> show table status like 't_user'\G
*************************** 1. row ***************************
           Name: t_user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4333
 Avg_row_length: 589
    Data_length: 2555904
Max_data_length: 0
   Index_length: 2719744
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-11-19 10:13:31
    Update_time: 2022-04-20 14:28:42
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Data_free: 4194304 就代表碎片的byte數(shù)。如果經(jīng)常刪改數(shù)據(jù)表，會造成大量的Data_free 頻繁 刪除記錄 或修改有可變長度字段的表。

找到碎片化最嚴重的表

SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, 'M') as data_free
FROM `information_schema`.tables
WHERE data_free > 3 * 1024 * 1024
    AND ENGINE = 'innodb'
ORDER BY data_free DESC

2.2 清理碎片(回收空間)的方法

官方文檔參考

通常有這幾種做法

alter table tb_test engine=innodb; （本質(zhì)上是 recreate）

optimize table tb_test； （本質(zhì)上是 recreate，但是在不同創(chuàng)建下會有區(qū)別）

ALTER TABLE tablename FORCE (在InnoDB表中等價于 alter table tb_test engine=innodb; )

mysqlcheck 批量表空間優(yōu)化

gh-ost/pt-osc

pt-online-schema-change （本質(zhì)上也是 先備份舊表數(shù)據(jù)，然后 truncate 舊表）

1. alter table tb_test engine=innodb 原理介紹

這其實是一個NULL操作,表面上看什么也不做,實際上重新整理碎片了.當執(zhí)行優(yōu)化操作時,實際執(zhí)行的是一個空的 ALTER 命令,但是這個命令也會起到優(yōu)化的作用,它會重建整個表,刪掉未使用的空白空間.

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

MySQL5.6 開始采用 Inplace 方式重建表，Alter 期間，支持 DML 查詢和更新操作，語句為 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作，是因為數(shù)據(jù)拷貝期間會將 DML 更新操作記錄到 Row log 中。 

重建過程中最耗時的就是拷貝數(shù)據(jù)的過程，這個過程中支持 DML 查詢和更新操作，對于整個 DDL 來說，鎖時間很短，就可以近似認為是 Online DDL。 

執(zhí)行過程： 

1、獲取 MDL（Meta Data Lock）寫鎖，innodb 內(nèi)部創(chuàng)建與原表結(jié)構(gòu)相同的臨時文件 

2、拷貝數(shù)據(jù)之前，MDL 寫鎖退化成 MDL 讀鎖，支持 DML 更新操作 

3、根據(jù)主鍵遞增順序，將一行一行的數(shù)據(jù)讀出并寫入到臨時文件，直至全部寫入完成。并且，會將拷貝期間的 DML 更新操作記錄到 Row log 中 

4、上鎖，再將 Row log 中的數(shù)據(jù)應(yīng)用到臨時文件 

5、互換原表和臨時表表名 

6、刪除臨時表

2. optimize table xxx；

OPTIMIZE TABLE語句可以重新組織表、索引的物理存儲，減少存儲空間，提高訪問的I/O效率。類似于碎片整理功能。

MySQL可以通過optimize table語句釋放表空間，重組表數(shù)據(jù)和索引的物理頁，減少表所占空間和優(yōu)化讀寫性能

使用語法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_n說ame [, tbl_name] …

對于主從架構(gòu)， LOCAL 參數(shù)可以讓這個過程不寫入 binlog ,這樣在主庫上執(zhí)行時就不會同步給從庫了

默認情況下，MySQL將OPTIMIZE TABLE語句寫入二進制日志，以便它們復(fù)制到slave服務(wù)器。如果不想寫二進制日志，使用命令時加上NO_WRITE_To_BINLOG或LOCAL關(guān)鍵字即可。

使用這個語句需要具有對目標表的SELECT、INSERT權(quán)限。

注意：

需要有足夠的空間才能進行OPTIMIZE TABLE。 (剩余空間必須 > 被 OPTIMIZE 的表的大小)

OPTIMIZE 只對獨立表空間(innodb_file_per_table=1)才有用，對共享表空間不起作用。

對于共享表空間，如果需要瘦身： 必須將數(shù)據(jù)導(dǎo)出，刪除ibdata1，然后將 innodb_file_per_table 設(shè)置為獨立表空間， 然后將數(shù)據(jù)導(dǎo)入進來。

對于InnoDB的表，OPTIMIZE TABLE 的工作原理如下

對于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者這樣翻譯：在InnoDB表中等價 ALTER TABLE … FORCE），它重建表以更新索引統(tǒng)計信息并釋放聚簇索引中未使用的空間。

當您在InnoDB表上運行時，它會顯示在OPTIMIZE TABLE的輸出中，如下所示：

mysql> OPTIMIZE TABLE foo; 
+----------+----------+----------+---------------------------------------+ 
| Table    | Op       | Msg_type | Msg_text                                                          | 
+----------+----------+----------+---------------------------------------+ 
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead | 
| test.foo | optimize | status   | OK                                                                | 
+----------+----------+----------+---------------------------------------+ 
 
# 但這個提示語可以忽略，從嚴格的意義講，說InnoDB不支持optimize table，其實不太準確。 因為 MYSQL的文檔說明了，當INNODB 的表，MYSQL會以 ALTER TABLE force  +  analyze 去執(zhí)行這個命令(相當于做了recreate和analyze)。 所以最終還是會看到 OK 的狀態(tài)。 
# https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me

對于MYISAM表，OPTIMIZE TABLE 的工作原理：

如果表已刪除或分隔行，就修復(fù)該表。

如果索引頁沒有排序，就排序它們。

如果表的統(tǒng)計信息不是最新的(而且修復(fù)不能通過對索引進行排序)，就更新它們。

**執(zhí)行時也可以發(fā)現(xiàn)報錯： Temporary file write failure. **

optimize 語句的官網(wǎng)介紹

如果您已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對含有可變長度行的表（含有VARCHAR, BLOB或TEXT列的表）進行了很多更改，則應(yīng)使用 OPTIMIZE TABLE。

被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。

在多數(shù)的設(shè)置中，您根本不需要運行OPTIMIZE TABLE。即使您對可變長度的行進行了大量的更新，您也不需要經(jīng)常運行，每周一次或每月一次 即可，只對特定的表運行。