7. Clean up the fragments
For Innodb tables, you can organize fragments by executing the following statements to improve indexing speed:
ALTER TABLE tbl_name ENGINE = Innodb;
This is actually a NULL operation. On the surface, it seems that it does nothing, but it actually reorganizes the fragments.
For myisam tables, run myisamchk in recovery mode in order to combine fragmented records and eliminate space wasted due to deleting or updating records:
shell> myisamchk -r tbl_name
You can optimize tables using the same way that SQL's OPTIMIZE TABLE statement uses, which can fix tables and analyze key values, and can sort the index tree to find key values faster.
8. Establish a table inspection plan
Run a crontab and check all myisam forms regularly every day.
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
9. Obtain the information of the table
myisamchk -d tbl_name: Run myisamchk in "description mode" to generate a table description
myisamchk -d -v tbl_name: To generate more information about what myisamchk is doing, plus -v tells it to run in verbose mode.
myisamchk -eis tbl_name: Show only the most important information of the table. This operation is slow because the entire table must be read.
myisamchk -eiv tbl_name: This is similar to -eis, just telling you what you are doing.
The information generated is explained
MyISAM file
ISAM (index) file name.
File-version
ISAM format version. Currently always 2.
Creation time
The time when the data file was created.
Recover time
The time when the index/data file was last rebuilt.
Data records
How many records are there in the table.
Deleted blocks
How many deleted blocks still retain space. You can optimize the table to minimize this space. See Chapter 7: Optimization.
Datafile parts
For dynamic record formats, this indicates how many data blocks are there. This is the same as Data records for an optimized table without fragmentation.
Deleted data
How many bytes of deleted data that cannot be recycled. You can optimize the table to minimize this space. See Chapter 7: Optimization.
Datafile pointer
The size of the data file pointer, in bytes. It is usually 2, 3, 4, or 5 bytes. Most tables are managed with 2 bytes, but at present this cannot be controlled from MySQL.
For fixed tables, this is a record address. For dynamic tables, this is a byte address.
Keyfile pointer
The size of the index file pointer, in bytes. It is usually 1, 2, or 3 bytes. Most tables are managed with 2 bytes, but it is automatically calculated by MySQL.
It is always a block address.
Max datafile length
How long can the table's data file (.MYD file) be, in bytes.
Max keyfile length
How long can the key-value file (.MYI file) of the table be, in bytes.
Recordlength
How much space each record takes up, in bytes.
Record format
Format for storing table rows. The above example uses Fixed length. Other possible values are Compressed and Packed.
table description
A list of all key values in the table. For each key, give some underlying information:
Key
The number of this key.
Start
The index part starts from where the record is.
Len
How long is the index part? For compact numbers, this should always be the full length of the column. For strings, it can be shorter than the full length of the indexed column.
Because you might index the prefix to the string column.
Index
unique or multiple (multiple). Indicates whether a value can exist multiple times in the index.
Type
What data type does this index part have? This is an ISAM data type with packed, stripped or empty options.
Root
The address of the root index block.
Blocksize
The size of each index block. The default is 1024, but when building MySQL from source, the value can be changed at compile time.
Rec/key
This is the statistics used by the optimizer. It tells how many records are there for each value of the key. The unique key always has a 1 value.
After a table is loaded (or changed greatly), it can be updated with myisamchk -a. If it is not updated at all, give a default value of 30.
In the table in the example above, the 9th key has two table description rows. This means it is a multi-key with 2 parts.
Keyblocks used
What is the percentage used by the key block. When the table used in the example is just reorganized with myisamchk, the value is very high (very close to the theoretical maximum).
Packed
MySQL attempts to compress keys with a common suffix. This can only be used for keys in the CHAR/VARCHAR/DECIMAL column. For a long string similar to the left part,
It can significantly reduce the use space. In the third example above, the fourth key is 10 characters long, which can reduce space by 60%.
Max levels
How deep is the B-tree for this key? Large tables with long keys have higher values.
Records
How many rows are there in the table.
Average record length. For tables with fixed length records, this is the exact record length because all records have the same length.
Packed
MySQL removes spaces from the end of the string. Packed values indicate the percentage of savings achieved by doing so.
Recordspace used
The percentage of data files being used.
Empty space
Percentage of data files not being used.
Blocks/Record
The average number of blocks per record (i.e., how many connections a fragment record consists of). For fixed format tables, this is always 1. This value should be kept as close to 1.0 as possible.
If it gets too big, you can reorganize the table. See Chapter 7: Optimization.
Recordblocks
How many blocks (links) are used. For fixed format, it is the same as the number of records.
Deleteblocks
How many blocks (links) are deleted.
Recorddata
How many bytes are used in the data file.
Deleted data
How many bytes are deleted (not used) in the data file.
Lost space
If a record is updated to a shorter length, some space is lost. This is the sum of all such losses, in bytes.
Linkdata
When using dynamic table format, record fragments are connected with pointers (4 to 7 bytes each). Linkdata refers to the sum of memory used by such a pointer.