Shulou(Shulou.com)06/01 Report--

MyISAM storage engine

MyISAM is based on the old (no longer available) ISAM storage engine, but has many useful extensions.

Each MyISAM table is stored on disk in three files. These files have names that begin with the table name and have an extension to indicate the file type.

The .frm file stores the table definition.

The data file has the extension .MYD (MYData).

The extension of the index file is .myi (MYIndex)

To make it clear that you want to use a MyISAM table, use the ENGINE option to indicate:

CREATE TABLE t (I INT) ENGINE = MYISAM

In MySQL 5.7, you usually need to use ENGINE to specify the MyISAM storage engine, because InnoDB is the default engine.

You can use the mysqlcheck client or the myisamchk utility to check or repair the MyISAM table. You can also use myisampack to compress MyISAM tables to reduce space.

Here are some features of the MyISAM storage engine:

1. All data values are first stored in low bytes. This makes the data machine and the operating system independent. The only requirement for binary portability is that the machine uses two tween signed integers and IEEE floating-point format. These requirements are widely used in mainstream machines. Binary compatibility may not be suitable for embedded systems with special processors.

two。 Storing low bytes of data first does not seriously affect speed; bytes in data rows are generally unjoined, and reading unjoined bytes in one direction does not take more resources than reading them in reverse. The code on the server that gets the column value does not seem to be running out of time compared to other code.

3. All numeric key values are stored in high bytes first to allow a higher index compression

4. Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files

5. The maximum number of rows in MyISAM table is 18440000000000000000.

6. The maximum number of indexes per MyISAM table is 64

7. The maximum number of columns per index is 16

8. The maximum key length is 1000 bytes. This can also be changed by compilation. For cases where the key length is more than 250bytes, a key block exceeding 1024 bytes is used.

9. When records are inserted in ordered order (as if you use an AUTO_INCREMENT column), the index tree is split so that the high node contains only one key. This improves the space utilization of the index tree.

10. Internal processing of one AUTO_INCREMEN column per table is supported. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10%). After the value at the top of the sequence is deleted, it can no longer be used. When the AUTO_INCREMENT column is defined as the last column of a multi-column index, a value deleted from the top of the sequence can be reused. The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamch.

11. When deleting and updating and inserting are mixed, dynamic-sized rows are less fragmented. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.

12. MyISAM supports concurrent insertion: if the table in the middle of the data file does not have a free block, you can INSERT new rows to the table while other threads read from the table. This is known as a concurrent operation. The emergence of a free block is the result of deleting a row or updating a dynamic length row with more data than the current content. When all free blocks are used up (filled), future inserts become concurrent. See Section 8.11.3, "concurrent insertion."

13. You can put data files and index files in different directories and use the DATA DIRECTORY and INDEX DIRECTORY options CREATE TABLE for higher speed, see Section 13.3.18, "CREATE TABLE Syntax".

14. BLOB and TEXT columns can be indexed.

15. The NULL value is allowed in the column of the index. This accounts for 0-1 bytes of each key.

16. Each character column can have a different character set, see Chapter 10.1: character set support.

17. Another flag in the MyISAM index file indicates whether the table is closed correctly. If you use the-- myisam-recover option to start the mysqld,MyISAM table to be checked automatically when it is opened, and if the table is improperly closed, the table is repaired.

18. If you run myisamchk with the-- update-state option, it marks the table as checked. Myisamchk-- fast only checks tables that do not have this flag.

19. Myisamchk-- analyze stores statistics for some keys, as well as for the entire key.

20. Myisampack can package BLOB and VARCHAR columns

MyISAM also supports the following features:

1. The true VARCHAR type is supported; VARCHAR columns start with a length stored in 2 bytes.

two。 Tables with VARCHAR can have fixed or dynamic record lengths.

3. VARCHAR and CHAR columns can be up to 64KB.

4. A messed up calculated index pair can be used for UNIQUE. This allows you to have UNIQUE on the merge of any column in the table. Still, you can't search on a UNIQUE computed index.

MyISAM startup options:

The following options for mysqld can be used to change the behavior of the MyISAM table. For additional information, see Section 5.1.4, "Server Command options."

1.-- myisam-recover-options=mode

Set to crash MyISAM automatic recovery mode

2.-- delay-key-write=ALL

Do not flush the key buffer between writes to any MyISAM table.

Note: if you want to do this. When the MyISAM table is in use, you should not use another program to access it (such as from another MySQL server or using myisamchk). Doing so causes the index to be corrupted. For tables that use-- delay-key-write,-- external-locking will not take effect

The following system variables affect the behavior of the MyISAM table. For additional information, see Section 5.1.5, "Server system variables."

1. Bulk_insert_buffer_size

The size of the tree buffer used in block insertion optimization. Note: this is a limitation of per thread.

2. Myisam_max_sort_file_size

The maximum size of temporary files that MySQL is allowed to use when recreating the MyISAM index (during table repair, table change, or data INFILE loading). If the file size is greater than this value, the key cache is used to create the index, which is slow. The value is given in bytes.

3. Myisam_sort_buffer_size

Sets the buffer size used when recovering the table.

If you start mysqld with the-- myisam-recover option, automatic recovery is activated. In this case, when the server opens a MyISAM table, the server checks to see if the table is marked as crashing, or if the table's open count variable is not 0 and you are running the server with-- skip-external-locking. If any of these conditions are true, the following occurs:

1. The watch was checked wrong.

two。 If the server finds an error, it tries to fix the quick table (sort without recreating the data file).

3. If the repair fails because of an error in the data file (for example, a duplicate key error), the server attempts to repair again, this time rebuilding the data file.

4. If the repair still fails, the server retries the repair with the old repair option method (line by line, without sorting). This method should be able to fix any type of error and require very low disk space.

If the restore fails to recover all lines from the previously completed statement, and you cannot specify FORCE in the-- myisam-recover option value, the automatic repair terminates and an error message is written in the error log:

Error: Couldn't repair table: test.g00pages

If you specify FORCE, instead, a warning like this is given:

Warning: Found 344 of 354 rows when repairing. / test/g00pages

Note: if the automatic recovery value includes BACKUP, the recovery process creates the file and names it in the form of tbl_name-datetime.BAK. You should have a cron script that automatically moves these files from the database directory to the backup media.

The space required by the key

1. The MyISAM table uses a B-tree index. You can roughly calculate the size of the index file as (key_length+4) / 0.67, plus the sum of all the keys. The above estimate is the worst-case scenario when all keys are inserted in sorted order and the table does not have any compressed keys.

two。 The string index is spatially compressed. If the first string index part is a string, it is also compressed with a prefix. If the string column has a lot of drag space, or if the string column is a VARCHAR column that is never full-length, space compression makes the index file smaller than the worst-case value. Prefix compression is used on keys that start with a string. Prefix compression is helpful if there are many strings with the same prefix.

3. In the MyISAM table, you can also compress numbers with a prefix by specifying PACK_KEYS=1 when creating the table. This method is helpful if you have many integer keys with the same prefix when numbers are stored first in high bytes.

Storage format of MyISAM table

MyISAM supports three different storage formats. Depending on the type of column used, two of them, fixed and dynamic formats, are automatically selected. The third compression format can only be created using the myisampack utility (see section 4.6.5, "myisampack generates a compressed read-only MyISAM table").

When you CREATE or ALTER a table without BLOB or TEXT columns, you can use the ROW_FORMAT table option to force the table to be FIXED or DYNAMIC. This can cause CHAR and VARCHAR columns to become CHAR due to FIXED format, or VARCHAR due to DYNAMIC format.

For information about ROW_FORMAT, see Section 13.1.18, "creating Table Syntax."

You can use myisamchk-unpack to extract (unpack) the compressed MyISAM table. For more information on "myisamchk-MyISAM Table-Maintenance Utility", see Section 4.6.3.

Static (fixed length) table characteristics

1. Static format is the default storage format for MyISAM tables. This format is used when the table does not contain variable length columns (VARCHAR, BLOB, or TEXT). Each line is stored in a fixed number of bytes.

two。 Among the three MyISAM storage formats, the static format is the simplest and most secure (the least corrupted). Because rows in the data file can be easily found on disk, it is also the fastest on-disk format: when looking up a row by the line number in the index, multiply the line length by the line number. Similarly, when scanning a table, it is easy to read a certain number of records with each disk read operation.

3. If your computer crashes when a MySQL server writes a fixed-format MyISAM file, security is obvious. In this case, myisamchk can easily determine the start and end of each line, so it can usually recycle all rows except for partial writes (all rows that are not written in the buffer). MyISAM table indexes can always be rebuilt based on data rows.

Note: the fixed length row format applies only to tables that do not have BLOB or TEXT columns. Creating a table with columns such as BLOB or TEXT using an explicit ROW_FORMAT clause does not raise an error or warning; the format specification is ignored.

General characteristics of static format tables:

1. The space of the CHAR and VARCHAR columns is filled to the specified column width, although the column type has not been changed. The BINARY and VARBINARY columns are populated to the column width with 0x00 bytes.

2.The NULL column needs extra space in the row to record whether their value is NULL. Each NULL column requires an extra bit, rounded to the nearest byte.

3. Very fast.

4. Easy to cache.

5. It is easy to rebuild after a crash because the data row is in a fixed location.

6. There is no need for reorganization unless you delete a large number of rows and want to return available disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk-r.

7. Usually requires more disk space than dynamic format tables.

8. Calculate the expected length in bytes for static size rows using the following expression:

Row length = 1

+ (sum of column lengths)

+ (number of NULL columns + delete_flag + 7) / 8

+ (number of variable-length columns)

For tables with static row format, delete_flag is 1. Static tables use bits in row records, flags that indicate whether rows have been deleted. Delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header.

Dynamic table characteristics

If a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXTDynamic), or if a table is created with the ROW_FORMAT=DYNAMIC option, the dynamic storage format is used.

The dynamic format is slightly more complex than the static format because each line has a header indicating how long the line is. When a record becomes longer as a result of the update, the record can also end at more than one location.

You can use OPTIMIZE TABLE or myisamchk-r to defragment a table. If there are frequently accessed or changed fixed-length columns in a table, and there are also some variable-length columns in the table, it may be a good idea to move these variable-length columns to other tables to avoid fragmentation.

General characteristics of dynamic format tables:

1. Except for columns less than 4 in length, all string columns are dynamic.

two。 Each line is preceded by a bitmap indicating which columns contain empty strings (for string columns) or 0 (for numeric columns). This does not include columns that contain NULL values. If the length of the string column after the trailing space is 0, or if the value of the numeric column is 0, it is marked in the bitmap and not saved to disk. The non-empty string is saved as a length byte plus the contents of the string.

3. The NULL column needs extra space in the row to record whether their value is NULL. Each NULL column requires an extra bit, rounded to the nearest byte.

4. Usually requires less disk space than a fixed-length table

5. Each row uses only the space you need. However, if the row becomes larger, it is split into the desired fragments, resulting in row fragments. For example, if you update a row with information that extends its length, the row becomes fragmented. In this case, you may need to run OPTIMIZE TABLE or myisamchk-r from time to time to improve performance. Use myisamchk-ei to get table statistics.

6. It is more difficult than a static format table to rebuild after a crash, because rows may be split into many fragments and links (fragments) and may be lost during reconstruction.

7. Use the following expression to calculate the expected row length of a dynamic size row:

Row length = 3

+ (number of columns + 7) / 8

+ (number of char columns)

+ (packed size of numeric columns)

+ (length of strings)

+ (number of NULL columns + 7) / 8

8. An additional 6 bytes are required for each link. When an update causes the expansion of a record, a dynamic record is linked. Each new link is at least 20 bytes, so the next expansion may be in the same link. If not, another link will be established. You can use myisamchk-ed to find out the number of links. All links can be removed using OPTIMIZE TABLE or myisamchk-r.

Compression table characteristics

The compressed storage format is a read-only format created by the myisampack tool.

Compressed tables can be decompressed with myisamchk.

The compression table has the following characteristics:

1. Compressed tables take up very little disk space. This minimizes disk usage, which is useful when using slow disks such as CD-ROM.

two。 Each record is compressed separately, so there is only a very small visit expense. Depending on the largest record in the table, the header of a record occupies 1 to 3 bytes in each table. Each column is compressed differently. Usually each column has a different Huffman tree. Some types of compression are as follows:

2.1 suffix space compression.

2.2 prefix space compression.

2.3 the number of zero values is stored in one bit.

2.4 if the value in an integer column has a small range, the column is stored with the smallest possible type. For example, a BIGINT column (8 bytes) can be stored as a TINYINT column (1 byte) if all its values are in the range of-128to127s.

2.5 if a column has only a small set of possible values, the type of the column is converted to ENUM.

2.6 A column can use any merge of the previous compression type.

3. Can handle fixed-length or dynamic-length records.

Be careful

Although the compressed table is read-only, so rows cannot be updated or added to the table, the DDL (data definition language) operation is still valid. For example, you can still use drop to drop the table and truncate the table to empty it.

MyISAM table problem

The file format used by MySQL to store data has been widely tested, but there is always an environment that causes the data table to become corrupted.

Damaged MyISAM table

Even if the MyISAM table format is very reliable (all changes made by the SQL statement to the table are written down before the statement returns), you can still get a corrupted table if any of the following events occur:

1. The mysqld process was killed during writing.

two。 An unexpected computer shutdown occurs (for example, the computer is turned off).

3. Hardware failure.

4. Use an external program, such as myisamchk, to modify the table being modified by the server.

5. Bug in MySQL or MyISAM code.

Typical symptoms of table damage are:

1. The following error occurs when querying data from a table:

Incorrect key file for table: '...'. Try to repair it

two。 The query could not find a row in the table or returned incomplete results.

You can use the CHECK TABLE statement to check the health of the MyISAM table and use REPAIR TABLE to repair the damaged MyISAM table. You can also use the myisamchk command to check or repair tables when mysqld is not running.

See section 13.7.2.2 "CHECK TABLE Syntax", section 13.7.2.5 "REPAIR TABLE Syntax", section 4.6.3, "myisamchk-MyISAM Table-Maintenance Utility".

If the table is frequently corrupted, you should try to determine the cause of this event. The most important thing is to know whether the table was damaged by a server crash. Verify by looking for the most recent restarted mysqld message in the error log. If there is such a message, the table corruption is most likely caused by a server crash. Otherwise, the damage may occur during normal operation. This is a bug. You should try to create a reproducible test case to demonstrate the problem.

See Section B.5.3.3 "What to Do If MySQL Keeps Crashing" and Section 28.5 "Debugging and Porting MySQL".

Problems with incorrect closing of the table

Each MyISAM index file (.MYI file) has a counter in the header that can be used to check that the table has been properly closed. If you get the following warning from CHECK TABLE or myisamchk, this counter is out of sync:

Clients are using or haven't closed the table properly

This warning does not necessarily mean that the table is corrupted, but at least the table should be checked.

The counter works as follows:

1. The first time you update a table in MySQL, the counter in the index file header is incremented.

two。 Counters will not change in future updates.

3. When the last instance of the table is closed (because of an operation FLUSH TABLE or because there is no space in the table buffer), the counter is minus one if the table has been updated at any point.

4. When the table is repaired, or checked, it is found to be good, and the counter is reset to zero.

5. In order to avoid the problem of interacting with other processes that may check the table, if the counter is zero, the counter is not reduced by one when it is turned off.

In other words, the counter will become incorrect only if:

1. Copy the MyISAM table without first LOCK TABLES and FLUSH TABLES

2. MySQL crashes between an update and a final shutdown (note that the table may still be intact, because MySQL always issues a write operation for everything between each statement)

3. While a table is used by mysqld, it is modified by myisamchk-- recover or myisamchk-- update-state.

4. Multiple mysqld servers are using the table, and one server performs a repair or check table on the table while it is being used by another server. In this architecture, it is safe to use CHECK TABLE, although you may get warnings from other servers. However, REPAIR TABLE should be avoided, because when one server replaces the old one with a new data file, other servers do not know this.

In general, it is a bad idea to share a data directory among multiple servers.

See Section 5. 6 "Running Multiple MySQL Instances on One Machine" for more discussion.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.