問題描述

我試圖了解 MyISAM 如何物理存儲其記錄以及在記錄插入和記錄刪除后如何維護其結構.我已閱讀以下鏈接:

• 第 10 章:存儲引擎"第196頁第7段說

  <塊引用>

  對于可變長度的記錄，格式更復雜.第一個字節包含描述記錄子類型的特殊代碼.后續字節的含義因每個子類型而異，但共同的主題是有一個字節序列，其中包含記錄的長度、塊中未使用的字節數、NULL 值指示符標志以及可能的指向如果記錄不適合先前創建的空間并且必須拆分，則記錄的延續.當一條記錄被刪除，并且要插入其位置的新記錄超過原始記錄的大小時，就會發生這種情況.可以通過研究storage/myisam/mi_dynrec.c中的switch語句in_mi_get_block_info()得到不同代碼含義的詳細信息.

  基于該段落，只有當要插入的新數據無法放入先前分配的塊時，舊記錄才會被鏈接數據覆蓋.這可能會導致許多臃腫的行.

  附加問題

  <塊引用>

  如果表被多次刪除和插入，是否會非常低效，因為記錄結構可能充滿溢出指針和未使用的空間?

  根據我之前的回答，會有很多塊

  • 空間塊
  • 記錄長度
  • 塊中未使用的字節數
  • NULL 值指示符標志
  • 如果記錄不適合先前創建的空間并且必須拆分，則可能是指向記錄延續的指針

  此類記錄鏈接將從插入過大數據的每一行的前面開始.這會很快使 MyISAM 表 .MYD 文件膨脹.

  建議

  MyISAM 的默認行格式是動態的.當一個表是動態的并且經歷了很多 INSERT、UPDATE 和 DELETE 時，這樣的表需要使用

  進行優化

  優化表mytable;

  還有一種選擇:將表格的行格式切換為固定.這樣，所有行的大小都相同.這是使行格式固定的方式:

  ALTER TABLE mytable ROW_FORMAT=Fixed;

  即使使用固定行格式，也必須花費時間來定位可用記錄，但時間將是 O(1) 搜索時間(通俗地說，無論定位可用記錄，都需要相同的時間表有多少行或有多少已刪除的行).您可以通過啟用 來繞過該步驟concurrent_insert 如下:

  將此添加到 my.cnf

  [mysqld]并發插入 = 2

  不需要重啟 MySQL.就跑

  mysql>SET GLOBAL concurrent_insert = 2;

  這將導致所有 INSERT 都轉到表的后面而不尋找可用空間.

  固定行表的優勢

  • INSERT、UPDATE 和 DELETE 會更快一些
  • SELECT 速度提高了 20-25%

  這是我關于 SELECT 因行格式固定而更快的一些帖子

  • 2012 年 5 月 3 日:InnoDB 和 MyISAM 哪個更快?
  • 2011 年 9 月 20 日:最佳MyISAM 和 InnoDB
  • 2011 年 5 月 10 日:在固定設備上使用 CHAR 與 VARCHAR 對性能有何影響?-size 字段?

  Fixed Row 表的缺點

  在大多數情況下，當您運行 ALTER TABLE mytable ROW_FORMAT=Fixed; 時，表可能會增長 80-100%..MYI 文件(MyISAM 表的索引頁)也將以相同的速度增長.

  結語

  如果您想要 MyISAM 表的速度并且可以使用更大的表，則需要我的替代建議.如果要為每個 MyISAM 表節省空間，請保留行格式(動態).您將不得不使用 OPTIMIZE TABLE mytable; 更頻繁地使用動態表來壓縮表.

  I am trying to understand how MyISAM physically store its records and how it maintains its structure after record insertion and record deletion. I have read the following link:

  • MyISAM Dynamic Data File Layout
  • MyISAM Record Structure

  I want to make sure if I understand it correctly, please correct me if it is not right.

  Fixed-sized record

  • Delete marker determines whether record is deleted or not deleted.
  • Record header holds which column of a row contains NULL value
  • The length of data is fixed.

  Variable-sized record

  • Delete marker is replaced with BLOCK_DELETED block type
  • Record header holds length of data and length of unused data
  • A single record can be seperated into multiple block connected by overflow pointer.

  Deletion

  • For variable-sized record, change block type to BLOCK_DELETED
  • Maintain double linked-list of all deleted record by having the previous pointer of the newly deleted record points to last deleted record. Then, the last deleted record's next pointer points to the newly deleted record.
  • For fixed-sized record, simply change delete marker as deleted. (unsure if they use double linked-list to connect all the deleted record with fixed-sized record)

  Insertion

  • If there is no unused space (deleted records), append the data at the end of the file
  • If there is unused space that fits the newly inserted record, write the new record there.
  • If there is unused space that is far bigger than newly inserted record, split into two records: the new record and the deleted record.
  • If there is unused space that is smaller than newly inserted record, write data there, have overflow pointer to points to the unfitted data at other block.

  Updating

  • What if users update existed data with longer data? Will MyISAM marked the record as deleted and find place that fits the new data or simply use overflow pointer to point to unfitted data?

  ---

  Recap the question again

  I want to make sure if I understand it correctly, please correct me if it is not right.

  Additional questions

  • Would it be very inefficient if the table has been deleted and inserted for many times since the record structure could potentially full of overflow pointers and unused space?
  解決方案

  The information you have in the question concerning MyISAM is right on target. However, I would like to address your two additional questions:

  LATEST QUESTION

  What if users update existed data with longer data? Will MyISAM marked the record as deleted and find place that fits the new data or simply use overflow pointer to point to unfitted data?

  According to the Book

  Chapter 10 : "Storage Engines" Page 196 Paragraph 7 says

  For records with variable length, the format is more complicated. The first byte contains a special code describing the subtype of the record. The meaning of the subsequent bytes varies with each subtype, but the common theme is that there is a sequence of bytes that contains the length of the record, the number of unused bytes in the block, NULL value indicator flags, and possibly a pointer to the continuation of the record if the record did not fit into the previously created space and had to be split up. This can happen when one record gets deleted, and a new one to be inserted into its place exceeds the original one is size. You can get the details of the meanings of different codes by studying the switch statement in_mi_get_block_info() in storage/myisam/mi_dynrec.c.

  Based on that paragraph, the old record gets overwritten with linkage data only if the new data to insert cannot fit in the previously allocated block. This can result in many bloated rows.

  ADDITIONAL QUESTION

  Would it be very inefficient if the table has been deleted and inserted for many times since the record structure could potentially full of overflow pointers and unused space?

  From my previous answer, there would be lots of blocks that have

  • block of space
  • the length of the record
  • the number of unused bytes in the block
  • NULL value indicator flags
  • possibly a pointer to the continuation of the record if the record did not fit into the previously created space and had to be split up

  Such record links would start in the front of every row that have oversized data being inserted. This can bloat a MyISAM tables .MYD file very quickly.

  SUGGESTIONS

  The default row format of a MyISAM is Dynamic. When a table is Dynamic and experiences lots of INSERTs, UPDATEs, and DELETEs, such a table would need to optimized with

  OPTIMIZE TABLE mytable;
  

  There is an alternative: switch the table's row format to Fixed. That way, all rows are the same size. This is how you make the row format Fixed:

  ALTER TABLE mytable ROW_FORMAT=Fixed;
  

  Even with a Fixed Row Format, time must be taken to locate an available record but the time would be O(1) search time (In layman's terms, it would take the same amount of time to locate an available record no matter how many rows the table has or how many deleted rows there are). You could bypass that step by enabling concurrent_insert as follows:

  Add this to my.cnf

  [mysqld]
  concurrent_insert = 2
  

  MySQL restart not required. Just run

  mysql> SET GLOBAL concurrent_insert = 2;
  

  This would cause all INSERTs to go to the back of the table without looking for free space.

  Advantage of Fixed Row tables

  • INSERTs, UPDATEs, and DELETEs would be somewhat faster
  • SELECT are 20-25% faster

  Here are some of my posts on SELECT being faster for Row Formats being Fixed

  • May 03, 2012 : Which is faster, InnoDB or MyISAM?
  • Sep 20, 2011 : Best of MyISAM and InnoDB
  • May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

  Disadvantage of Fixed Row tables

  In most cases, when you run ALTER TABLE mytable ROW_FORMAT=Fixed;, the table may grow 80-100%. The .MYI file (index pages for the MyISAM table) would also grow at the same rate.

  EPILOGUE

  If you want speed for MyISAM tables and can live with bigger tables, my alternate suggestions would be needed. If you want to conserve space for each MyISAM table, leave the row format as is (Dynamic). You will have to compress the table with OPTIMIZE TABLE mytable; more frequent with Dynamic tables.

  這篇關于了解 MyISAM 記錄結構的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

  【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！