本篇內(nèi)容主要講解“MySQL的碎片有哪些”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL的碎片有哪些”吧!
在寧都等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站制作、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),成都全網(wǎng)營(yíng)銷推廣,成都外貿(mào)網(wǎng)站建設(shè),寧都網(wǎng)站建設(shè)費(fèi)用合理。
MySQL 中 insert 與 update 都可能導(dǎo)致頁(yè)分裂，這樣就存在碎片。
對(duì)于大量的UPDATE，也會(huì)產(chǎn)生文件碎片化 ， Innodb的最小物理存儲(chǔ)分配單位是頁(yè)(page)，而UPDATE也可能導(dǎo)致頁(yè)分裂（page split），頻繁的頁(yè)分裂，頁(yè)會(huì)變得稀疏，并且被不規(guī)則的填充，所以最終數(shù)據(jù)會(huì)有碎片。
delete 語(yǔ)句實(shí)際上只是給數(shù)據(jù)打個(gè)標(biāo)記，并且記錄到一個(gè)鏈表中，這樣就形成了留白空間。
在InnoDB中，刪除一些行，這些行只是被標(biāo)記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒(méi)有真的被釋放回收。InnoDB的Purge線程會(huì)異步的來(lái)清理這些沒(méi)用的索引鍵和行。
當(dāng)執(zhí)行插入操作時(shí)，MySQL會(huì)嘗試使用空白空間，但如果某個(gè)空白空間一直沒(méi)有被大小合適的數(shù)據(jù)占用，仍然無(wú)法將其徹底占用，就形成了碎片；
總結(jié)：
truncate table其實(shí)有點(diǎn)類似于drop table 然后creat,只不過(guò)這個(gè)create table 的過(guò)程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近drop table的速度;
drop ，truncate 立刻釋放磁盤空間 ，不管是 Innodb和MyISAM ;
delete from table_name刪除表的全部數(shù)據(jù),對(duì)于MyISAM 會(huì)立刻釋放磁盤空間 （應(yīng)該是做了特別處理，也比較合理）,InnoDB 不會(huì)釋放磁盤空間;
對(duì)于 delete from table_name where xxx; 帶條件的刪除, 不管是innodb還是MyISAM都不會(huì)釋放磁盤空間;
delete操作以后使用optimize table table_name 會(huì)立刻釋放磁盤空間。不管是innodb還是myisam 。所以要想達(dá)到釋放磁盤空間的目的，delete以后執(zhí)行optimize table 操作。
delete from表以后雖然未釋放磁盤空間，但是下次插入數(shù)據(jù)的時(shí)候，仍然可以使用這部分空間。
表的增刪改操作，可能會(huì)造成數(shù)據(jù)空洞的，當(dāng)對(duì)表進(jìn)行大量的增刪改操作后，數(shù)據(jù)空洞存在的可能性比較大。
MySQL刪除數(shù)據(jù)幾種情況以及是否釋放磁盤空間:
當(dāng)MySQL對(duì)數(shù)據(jù)進(jìn)行掃描時(shí)，它掃描的對(duì)象實(shí)際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分；
MySQL數(shù)據(jù)庫(kù)中的表在進(jìn)行了多次delete、update和insert后，表空間會(huì)出現(xiàn)碎片。定期進(jìn)行表空間整理，消除碎片可以提高訪問(wèn)表空間的性能。
這種碎片不僅額外增加了存儲(chǔ)代價(jià)，同時(shí)也因?yàn)閿?shù)據(jù)碎片化降低了表的掃描效率。
碎片若不整理，那么可能會(huì)長(zhǎng)期占據(jù)磁盤空間，導(dǎo)致磁盤使用率越來(lái)越高。
修復(fù)問(wèn)題的前提是要先找到問(wèn)題，這樣才能對(duì)癥下藥。
查看數(shù)據(jù)庫(kù)中每個(gè)存在碎片的表
重慶分公司
重慶分公司
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ù)表，會(huì)造成大量的Data_free 頻繁 刪除記錄 或修改有可變長(zhǎng)度字段的表。
找到碎片化最嚴(yán)重的表
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
官方文檔參考
alter table tb_test engine=innodb; （本質(zhì)上是 recreate）
optimize table tb_test； （本質(zhì)上是 recreate，但是在不同創(chuàng)建下會(huì)有區(qū)別）
ALTER TABLE tablename FORCE (在InnoDB表中等價(jià)于 alter table tb_test engine=innodb; )
mysqlcheck 批量表空間優(yōu)化
gh-ost/pt-osc
pt-online-schema-change （本質(zhì)上也是 先備份舊表數(shù)據(jù)，然后 truncate 舊表）
這其實(shí)是一個(gè)NULL操作,表面上看什么也不做,實(shí)際上重新整理碎片了.當(dāng)執(zhí)行優(yōu)化操作時(shí),實(shí)際執(zhí)行的是一個(gè)空的 ALTER 命令,但是這個(gè)命令也會(huì)起到優(yōu)化的作用,它會(huì)重建整個(gè)表,刪掉未使用的空白空間.
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 開(kāi)始采用 Inplace 方式重建表，Alter 期間，支持 DML 查詢和更新操作，語(yǔ)句為 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作，是因?yàn)閿?shù)據(jù)拷貝期間會(huì)將 DML 更新操作記錄到 Row log 中。 重建過(guò)程中最耗時(shí)的就是拷貝數(shù)據(jù)的過(guò)程，這個(gè)過(guò)程中支持 DML 查詢和更新操作，對(duì)于整個(gè) DDL 來(lái)說(shuō)，鎖時(shí)間很短，就可以近似認(rèn)為是 Online DDL。 執(zhí)行過(guò)程： 1、獲取 MDL（Meta Data Lock）寫鎖，innodb 內(nèi)部創(chuàng)建與原表結(jié)構(gòu)相同的臨時(shí)文件 2、拷貝數(shù)據(jù)之前，MDL 寫鎖退化成 MDL 讀鎖，支持 DML 更新操作 3、根據(jù)主鍵遞增順序，將一行一行的數(shù)據(jù)讀出并寫入到臨時(shí)文件，直至全部寫入完成。并且，會(huì)將拷貝期間的 DML 更新操作記錄到 Row log 中 4、上鎖，再將 Row log 中的數(shù)據(jù)應(yīng)用到臨時(shí)文件 5、互換原表和臨時(shí)表表名 6、刪除臨時(shí)表
OPTIMIZE TABLE語(yǔ)句可以重新組織表、索引的物理存儲(chǔ)，減少存儲(chǔ)空間，提高訪問(wèn)的I/O效率。類似于碎片整理功能。
MySQL可以通過(guò)optimize table語(yǔ)句釋放表空間，重組表數(shù)據(jù)和索引的物理頁(yè)，減少表所占空間和優(yōu)化讀寫性能
使用語(yǔ)法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_n說(shuō)ame [, tbl_name] …
對(duì)于主從架構(gòu)， LOCAL 參數(shù)可以讓這個(gè)過(guò)程不寫入 binlog ,這樣在主庫(kù)上執(zhí)行時(shí)就不會(huì)同步給從庫(kù)了
默認(rèn)情況下，MySQL將OPTIMIZE TABLE語(yǔ)句寫入二進(jìn)制日志，以便它們復(fù)制到slave服務(wù)器。如果不想寫二進(jìn)制日志，使用命令時(shí)加上NO_WRITE_To_BINLOG或LOCAL關(guān)鍵字即可。
使用這個(gè)語(yǔ)句需要具有對(duì)目標(biāo)表的SELECT、INSERT權(quán)限。
注意：
需要有足夠的空間才能進(jìn)行OPTIMIZE TABLE。 (剩余空間必須 > 被 OPTIMIZE 的表的大小)
OPTIMIZE 只對(duì)獨(dú)立表空間(innodb_file_per_table=1)才有用，對(duì)共享表空間不起作用。
對(duì)于共享表空間，如果需要瘦身： 必須將數(shù)據(jù)導(dǎo)出，刪除ibdata1，然后將 innodb_file_per_table 設(shè)置為獨(dú)立表空間， 然后將數(shù)據(jù)導(dǎo)入進(jìn)來(lái)。
對(duì)于InnoDB的表，OPTIMIZE TABLE 的工作原理如下
對(duì)于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者這樣翻譯：在InnoDB表中等價(jià) ALTER TABLE … FORCE），它重建表以更新索引統(tǒng)計(jì)信息并釋放聚簇索引中未使用的空間。
當(dāng)您在InnoDB表上運(yùn)行時(shí)，它會(huì)顯示在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 | +----------+----------+----------+---------------------------------------+ # 但這個(gè)提示語(yǔ)可以忽略，從嚴(yán)格的意義講，說(shuō)InnoDB不支持optimize table，其實(shí)不太準(zhǔn)確。 因?yàn)?nbsp;MYSQL的文檔說(shuō)明了，當(dāng)INNODB 的表，MYSQL會(huì)以 ALTER TABLE force + analyze 去執(zhí)行這個(gè)命令(相當(dāng)于做了recreate和analyze)。 所以最終還是會(huì)看到 OK 的狀態(tài)。 # https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me
對(duì)于MYISAM表，OPTIMIZE TABLE 的工作原理：
1. 如果表已刪除或分隔行，就修復(fù)該表。
2. 如果索引頁(yè)沒(méi)有排序，就排序它們。
3. 如果表的統(tǒng)計(jì)信息不是最新的(而且修復(fù)不能通過(guò)對(duì)索引進(jìn)行排序)，就更新它們。
**執(zhí)行時(shí)也可以發(fā)現(xiàn)報(bào)錯(cuò)： Temporary file write failure. **
建議參考這片文章：
Mysql optimize table 時(shí)報(bào)錯(cuò) Temporary file write failure. 的解決方案
optimize 語(yǔ)句的官網(wǎng)介紹
如果您已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對(duì)含有可變長(zhǎng)度行的表（含有VARCHAR, BLOB或TEXT列的表）進(jìn)行了很多更改，則應(yīng)使用 OPTIMIZE TABLE。
被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會(huì)重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來(lái)重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。
在多數(shù)的設(shè)置中，您根本不需要運(yùn)行OPTIMIZE TABLE。即使您對(duì)可變長(zhǎng)度的行進(jìn)行了大量的更新，您也不需要經(jīng)常運(yùn)行，每周一次或每月一次 即可，只對(duì)特定的表運(yùn)行。
Mysql 5.6 之前 在OPTIMIZE TABLE運(yùn)行過(guò)程中，MySQL會(huì)鎖定表，5.6之后有了 Online DDL 則大大減少了鎖表時(shí)間。
alter table tb_test engine = innodb；
（也就是 recreate）MySQL 5.5以前用Offline的方式重建表，5.6以后用Online的方式重建表；
analyze table tb_test ；
重新統(tǒng)計(jì)表的索引信息，不會(huì)修改數(shù)據(jù)，不會(huì)重建表，整個(gè)過(guò)程加MDL讀
optimize table tb_test ；
是 alter table xxx = innodb； + analyze table xxx; 的過(guò)程。
OPTIMIZE TABLE 還是ALTER TABLE xxxx ENGINE= INNODB 基本上是一樣的。但是在有些情況下，ALTER TABLE xxxx ENGINE= INNODB更好。
例如: old_alter_table 系統(tǒng)變量沒(méi)有啟用等等。
另外： 對(duì)于MyISAM類型表，使用ALTER TABLE xxxx ENGINE= INNODB 是明顯要優(yōu)于 OPTIMIZE TABLE這種方法的。
MySQL官方建議不要經(jīng)常(每小時(shí)或每天)進(jìn)行碎片整理，一般根據(jù)實(shí)際情況，只需要每周或者每月整理一次即可(我們現(xiàn)在是每月凌晨4點(diǎn)清理mysql所有實(shí)例下的表碎片)
到此，相信大家對(duì)“MySQL的碎片有哪些”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
