Oracle 5.0, Справочное руководство

Страница: 1242 / 3232

Space Needed for Keys
1222
If the recovery wouldn't be able to recover all rows from previously completed statements and you
didn't specify
FORCE
in the value of the
--myisam-recover
[415] option, automatic repair aborts with
an error message in the error log:
Error: Couldn't repair table: test.g00pages
If you specify
FORCE
, a warning like this is written instead:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if the automatic recovery value includes
BACKUP
, the recovery process creates files with
names of the form
tbl_name-datetime.BAK
. You should have a
cron
script that automatically
moves these files from the database directories to backup media.
14.1.2. Space Needed for Keys
MyISAM
tables use B-tree indexes. You can roughly calculate the size for the index file as
(key4)/0.67
, summed over all keys. This is for the worst case when all keys are inserted
in sorted order and the table doesn't have any compressed keys.
String indexes are space compressed. If the first index part is a string, it is also prefix compressed.
Space compression makes the index file smaller than the worst-case figure if a string column has a lot
of trailing space or is a
VARCHAR
column that is not always used to the full length. Prefix compression
is used on keys that start with a string. Prefix compression helps if there are many strings with an
identical prefix.
In
MyISAM
tables, you can also prefix compress numbers by specifying the
PACK_KEYS=1
table option
when you create the table. Numbers are stored with the high byte first, so this helps when you have
many integer keys that have an identical prefix.
14.1.3.
MyISAM
Table Storage Formats
MyISAM
supports three different storage formats. Two of them, fixed and dynamic format, are chosen
automatically depending on the type of columns you are using. The third, compressed format, can be
created only with the
myisampack
utility (see Section 4.6.5, “
myisampack
— Generate Compressed,
Read-Only MyISAM Tables” ).
When you use
CREATE TABLE
or
ALTER TABLE
for a table that has no
BLOB
or
TEXT
columns, you
can force the table format to
FIXED
or
DYNAMIC
with the
ROW_FORMAT
table option.
See Section 13.1.10, “
CREATE TABLE
Syntax” , for information about
ROW_FORMAT
.
You can decompress (unpack) compressed
MyISAM
tables using
myisamchk --unpack [325]
; see
Section 4.6.3, “
myisamchk
— MyISAM Table-Maintenance Utility” , for more information.
14.1.3.1. Static (Fixed-Length) Table Characteristics
Static format is the default for
MyISAM
tables. It is used when the table contains no variable-length
columns (
VARCHAR
,
VARBINARY
,
BLOB
, or
TEXT
). Each row is stored using a fixed number of bytes.
Of the three
MyISAM
storage formats, static format is the simplest and most secure (least subject to
corruption). It is also the fastest of the on-disk formats due to the ease with which rows in the data file
can be found on disk: To look up a row based on a row number in the index, multiply the row number
by the row length to calculate the row position. Also, when scanning a table, it is very easy to read a
constant number of rows with each disk read operation.
The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format
MyISAM
file. In this case,
myisamchk
can easily determine where each row starts and ends, so it can
usually reclaim all rows except the partially written one. Note that
MyISAM
table indexes can always be
reconstructed based on the data rows.