MyISAM Table Storage Formats




本文主要了解 Static (Fixed-Length) TABLE 和 Dynamic Table 以及對空格的處理。


http://dev.MySQL.com/doc/refman/5.7/en/myisam-table-formats.html
1.創建和更改 MyISAM Table Storage Formats 語法
## table has no BLOB or TEXT ##
Create Table Engine
Create Table table_name ROW_FORMAT=FIXED;
Change Table Engine
Alter Table table_name ROW_FORMAT=DYNAMIC;




2.案例
2.1 創建 FIXED TABLE OF MyISAM Engine 并插入帶有空格的字符，觀察 FIXED TABLE OF MyISAM Engine 是如何處理空格的。
mysql> create table myisam_char(name char(10)) engine=myisam ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.04 sec)


mysql> insert into myisam_char values('abcde'),('abcde  '),('  abcde'),('  abcde  ');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select name,length(name) from myisam_char;
+---------+--------------+
| name    | length(name) |
+---------+--------------+
| abcde   |            5 |
| abcde   |            5 |
|   abcde |            7 |
|   abcde |            7 |
+---------+--------------+
4 rows in set (0.00 sec)
## FIXED TABLE OF MyISAM Engine 中字段都是非變長字段，數據會按照列的寬度定義補足空格，但是應用訪問的時候并不會得到這些空格，這些空格在返回給應用之前已經去掉。
## 所以，開發需求本來就要保存字符后的空格，那就需要注意了。


mysql> show table status fromtest1 like 'myisam_char';
+-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+------------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options   | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+------------------+---------+
| myisam_char | MyISAM |      10 | Fixed      |    4 |             11 |          44 | 3096224743817215 |         1024 |         0 |           NULL | 2016-03-22 16:09:26 | 2016-03-22 16:09:35 | NULL       | latin1_swedish_ci |     NULL | row_format=FIXED|         |
+-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+------------------+---------+
1 row in set (0.00 sec)
## SHOW TABLE STATUS 語法直接查看 MyISAM Table Storage Formats


2.2 更改 MyISAM Table Storage Formats
mysql> alter table myisam_char engine=myisam ROW_FORMAT=DYNAMIC;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> show table status from test1 like 'myisam_char';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options     | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+---------+
| myisam_char | MyISAM |      10 | Dynamic    |    4 |             20 |          80 | 281474976710655 |         1024 |         0 |           NULL | 2016-03-22 16:21:44 | 2016-03-22 16:21:44 | NULL       | latin1_swedish_ci |     NULL | row_format=DYNAMIC |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+---------+
1 row in set (0.00 sec)


mysql> insert into myisam_char values('ABCDE'),('ABCDE  '),('  ABCDE'),('  ABCDE  ');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select name,length(name) from myisam_char;
+---------+--------------+
| name    | length(name) |
+---------+--------------+
| abcde   |            5 |
| abcde   |            5 |
|   abcde |            7 |
|   abcde |            7 |
| ABCDE   |            5 |
| ABCDE   |            5 |
|   ABCDE |            7 |
|   ABCDE |            7 |
+---------+--------------+
8 rows in set (0.00 sec)


3.Static (Fixed-Length) 和 Dynamic Table 特點
3.1 Static (Fixed-Length) 特點
Static-format tables have these characteristics:


CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.


Very quick.


Easy to cache.


Easy to reconstruct after a crash, because rows are located in fixed positions.


Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.


Usually require more disk space than dynamic-format tables.
## 這種存儲方式的優點存儲非常迅速，容易緩存，出現故障容易恢復因為行位于固定的位置；缺點是占用的空間比動態表多。


3.2 Dynamic Table 特點


Dynamic-format tables have these characteristics:


All string columns are dynamic except those with a length less than four.


Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). This does not include columns that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Nonempty strings are saved as a length byte plus the string contents.


Much less disk space usually is required than for fixed-length tables.


Each row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run OPTIMIZE TABLE or myisamchk -r from time to time to improve performance. Use myisamchk -ei to obtain table statistics.


More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.


The expected row length for dynamic-sized rows is calculated using the following expression:


3
+ (number of columns + 7) / 8
+ (number of char columns)
+ (packed size of numeric columns)
+ (length of strings)
+ (number of NULL columns + 7) / 8
There is a penalty of 6 bytes for each link. A dynamic row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk -ed. All links may be removed with OPTIMIZE TABLE or myisamchk -r.
## 這種存儲方式比靜態表占用空間少；但是，頻繁的更新和刪除記錄會生產鎖片。所以，需要定期執行 OPTIMIZE TABLE or myisamchk -r 命令來改善性能；出現故障時恢復比較困難。


########################################################################################
版權所有，文章允許轉載，但必須以鏈接方式注明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文鏈接： http://blog.itpub.net/26442936/viewspace-2062237/
########################################################################################

當前文章：MyISAMTableStorageFormats
網頁URL：http://www.hntjjpw.com/article4/goigoe.html

成都網站建設公司_創新互聯，為您提供ChatGPT、小程序開發、企業建站、虛擬主機、品牌網站設計、網站內鏈

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯