High Performance MySQL [Electronic resources] نسخه متنی

2.5 The Storage Engines

Now it's time to look
at each of MySQL's storage engines in more detail.
Table 2-5 summarizes some of the high-level
characteristics of the handlers. The following sections provide some
basic highlights and background about each table handler as well as
any unusual characteristics and interesting features.

Before going further, it's worth noting that this
isn't an exhaustive discussion of
MySQL's storage engines. We assume that
you've read (or at least know where to find) the
information in the MySQL Reference Manual.

^[3] Prior to MySQL 4.0, InnoDB was available
in MySQL-Max only.

Most of MySQL's disk-based tables have some basic
things in common. Each database in MySQL is simply a subdirectory of
MySQL's data directory in the underlying
filesystem.^[4]
Whenever you create a table, MySQL stores the table definition in a
.frm file with the same name as the table.
Thus, when you create a table named MyTable, MySQL
stores the table definition in MyTable.frm.

^[4] In MySQL 5.0, the term
"database" will likely morph into
"schema."

To determine the type of a table, use the
SHOW TABLE
STATUS command. For example, to examine the user
table in the mysql database, you execute the
following:

mysql> SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
Name: user
Type: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Create_options: 
Comment: Users and global privileges
1 row in set (0.06 sec)

Notice that it's a MyISAM table. You might also
notice a lot of other information and statistics in the output.
Let's briefly look at what each line means:

Name

The table's name.

Type

The table's type. Again, in some versions of MySQL,
this may say "Engine" rather than
"Type."

Row_format

Dynamic, Fixed, or Compressed. Dynamic rows vary in length because
they contain variable-length fields such as
VARCHAR or BLOB. Fixed rows,
which are always the same size, are made up of fields that
don't vary in length, such as
CHAR and INTEGER. Compressed
rows exist only in compressed tables (see the later section
"Compressed MyISAM").

Rows

The number of rows in the table. For non-transactional tables, this
number is always accurate. For transactional tables, it is usually an
estimate.

Avg_row_length

How many bytes the average row contains.

Data_length

How much data (in bytes) the entire table contains.

Max_data_length

The maximum amount of data this table can hold. In a MyISAM table
with dynamic (variable length) rows, the index file for a table
(tablename.MYI) stores row locations using
32-bit pointers into the data file
(tablename.MYD). That means it can address only
up to 4 GB of space by default. See Section 2.5.1 for more details. For MyISAM
tables with fixed-length rows, the limit is just under 4.3 billion
rows.

Index_length

How much space is consumed by index data.

Data_free

The amount of space that has been allocated but is currently unused.

Auto_increment

The next AUTO_INCREMENT value.

Create_time

When the table was first created.

Update_time

When data in the table last changed.

Check_time

When the table was last checked using CHECK
TABLE or myisamchk.

Create_options

Any other options that were specified when the table was created.

Comment

The comments, if any, that were set when the table was
created.

2.5.1 MyISAM Tables

As MySQL's default
storage engine, MyISAM provides a good compromise between performance
and useful features. Versions of MySQL prior to 3.23 used the
Index Sequential Access Method (ISAM)
table format. In Version 3.23, ISAM tables were deprecated in favor
of MyISAM, an enhanced ISAM format.^[5] MyISAM tables don't
provide transactions or a very granular locking model, but they do
have full-text indexing (see Chapter 4),
compression, and more.

^[5] ISAM tables may
be used in MySQL 4.0 and 4.1. Presumably they'll
vanish sometime in the 5.x release cycle. If you're
still using ISAM tables, it's time to upgrade to
MyISAM!

2.5.1.1 Storage

In MyISAM
storage, there are typically two files: a data file and an index
file. The two files bear .MYD and
.MYI extensions, respectively. The MyISAM format
is platform-neutral, meaning you can copy the data and index files
from an Intel-based server to a Macintosh PowerBook or Sun SPARC
without any trouble.

MyISAM tables can contain either dynamic
or static (fixed-length) rows. MySQL decides which format to use
based on the table definition. The number of rows a MyISAM table can
hold is limited primarily by the available disk space on your
database server and the largest file your operating system will let
you create. Some (mostly older) operating systems have been known to
cut you off at 2 GB, so check your local documentation.

However, MyISAM files with variable-length rows, are set up by
default to handle only 4 GB of data, mainly for efficiency. The index
uses 32-bit pointers to the data records. To create a MyISAM table
that can hold more than 4 GB, you must specify values for the
MAX_ROWS and AVG_ROW_LENGTH
options that represent ballpark figures for the amount of space you
need:

CREATE TABLE mytable (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;

In the example, we've told MySQL to be prepared to
store at least 32 GB of data in the table. To find out what MySQL
decided to do, simply ask for the table status:

mysql> SHOW TABLE STATUS LIKE 'mytable' \G
*************************** 1. row ***************************
Name: mytable
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 98784247807
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2002-02-24 17:36:57
Update_time: 2002-02-24 17:36:57
Check_time: NULL
Create_options: max_rows=1000000000 avg_row_length=32
Comment: 
1 row in set (0.05 sec)

As you can see, MySQL remembers the create options exactly as
specified. And it chose a representation capable of holding 91 GB of
data!

2.5.1.2 Other stuff

As one of the oldest storage
engines included in MySQL, MyISAM tables have a number of features
that have been developed over time specifically to fill niche needs
uncovered through years of use:

Locking and concurrency

Locking in MyISAM tables is performed
at the table level. Readers obtain shared (read) locks on all tables
they need to read. Writers obtain exclusive (write) locks.

Automatic repair

If
MySQL is started with the --myisam-recover option,
the first time it opens a MyISAM table, it examines the table to
determine whether it was closed properly. If it was not (probably
because of a hardware problem or power outage), MySQL scans the table
for problems and repairs them. The downside, of course, is that your
application must wait while a table it needs is being repaired.

Manual repair

You can use the CHECK TABLE
mytable and REPAIR
TABLE mytable commands to check
a table for errors and repair them. The
myisamchk command-line tool can also be used to
check and repair tables when the server is offline.

Concurrency improvements

If a MyISAM table has no deleted rows, you can insert rows into the
table while select queries are running against it.

Index features

BLOB and
TEXT columns in a MyISAM table can be indexed.
MyISAM tables have a limit of 500 bytes on each key, however, so the
index uses only the first few hundred bytes of a
BLOB or TEXT field. MyISAM
tables also allow you to index columns that may contain NULL values.
You can find more information on MyISAM indexes in Chapter 4.

Delayed key writes

MyISAM tables marked with the
DELAY_KEY_WRITE create option
don't have index changes written to disk as they are
made. Instead, the changes are made to the in-memory key buffer only
and flushed to disk when the associated blocks are pruned from the
key buffer or when the table is closed. This can yield quite a
performance boost on heavily used tables that change frequently.

2.5.2 Compressed MyISAM Tables

For circumstances in which the data
never changes, such as CD-ROM- or DVD-ROM-based applications, or in
some embedded environments, MyISAM tables can be compressed (or
packed) using the myisampack utility. Compressed
tables can't be modified, but they generally take
far less space and are faster as a result. Having smaller tables
means fewer disk seeks are required to find records.

On relatively modern hardware, the overhead involved in decompressing
the data is insignificant for most applications. The individual rows
are compressed, so MySQL doesn't need to unpack an
entire table (or even a page) just to fetch a single row.

2.5.3 RAID MyISAM Tables

While
they're not really a separate table type, MyISAM
RAID tables do serve a particular niche. To use them, you need to
compile your own copy of MySQL from source or use the MySQL-Max
package. RAID tables are just like MyISAM tables except that the data
file is split into several data files. Despite the reference to RAID
in the name, these data files don't have to be
stored on separate disks, although it is easy to do so. Writes to the
table are striped across the data files, much like RAID-0 would do
across physical disks. This can be helpful in two circumstances. If
you have an operating system that limits file sizes to 2 or 4 GB but
you need larger tables, using RAID will get you past the limit. If
you're have an I/O bound table that is read from and
written to very frequently, you might achieve better performance by
storing each of the RAID files on a separate physical disk.

To create a RAID table, you must supply some additional options at
table-creation time:

CREATE TABLE mytable (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
) RAID_TYPE = STRIPED RAID_CHUNKS = 4 RAID_CHUNKSIZE = 16;

The RAID_TYPE option, while required, must be
STRIPED or RAID0, which are
synonymous. No other RAID algorithms are available. The
RAID_CHUNKS parameter tells MySQL how many data
files to break the table into. The RAID_CHUNKSIZE
option specifies how many kilobytes of data MySQL will write in each
file before moving to the next.

In the previous example, MySQL would create four subdirectories named
00, 01,
02, and 03 in which it
would store a file named mytable.MYD. When
writing data to the table, it would write 16 KB of data to one file
and then move to the next one. Once created, RAID tables are
transparent. You can use them just as you would normal MyISAM tables.

With the availability of inexpensive RAID controllers and the
software RAID features of some operating systems, there
isn't much need for using RAID tables in MySQL.
Also, it's important to realize that RAID tables
split only the data file, not the indexes. If you're
trying to overcome file size limits, keep an eye on the size of your
index files.

2.5.4 MyISAM Merge Tables

Merge
tables are the final variation of MyISAM tables that MySQL provides.
Where a RAID table is a single table split into smaller pieces, a
Merge table is the combination of several similar tables into one
virtual table.

This is particularly useful when MySQL is used in logging
applications. Imagine you store web server logs in MySQL. For ease of
management, you might create a table for each month. However, when it
comes time to generate annual statistics, it would be easier if all
the records were in a single table. Using Merge tables,
that's possible. You can create 12 normal MyISAM
tables, log_2004_01,
log_2004_02, ... log_2004_12,
and then a Merge table named log_2004.

Queries for a particular month can be run against the specific table
that holds the data. But queries that may need to cross month
boundaries can be run against the Merge table
log_2004 as if it was a table that contained all
the data in the underlying twelve tables.

The requirements for a Merge table are that the underlying tables
must:

Have exactly the same definition

Be MyISAM tables

Exist in the same database (this limitation is removed in MySQL
Versions 4.1.1 and higher, however)

Interestingly, it's possible for some underlying
tables to be compressed MyISAM tables. That means you can compress
tables as they get old (since they're no longer
being written to anyway), but still use them as part of a Merge
table. Just make sure to remove the table from the Merge table before
compressing it, then re-add it after it has been compressed.

Using the example table from earlier, let's create
several identical tables and a Merge table that aggregates them:

CREATE TABLE mytable0 (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
);
CREATE TABLE mytable1 (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
);
CREATE TABLE mytable2 (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
);
CREATE TABLE mytable (
a    INTEGER  NOT NULL PRIMARY KEY,
b    CHAR(18) NOT NULL
) TYPE = MERGE UNION = (mytable0, mytable1, mytable2) INSERT_METHOD = LAST;

The only difference between the Merge table and the underlying tables
is that it has a few extra options set at creation time. The type, of
course, is MERGE. The UNION
option specifies the tables that make up the Merge table. Order is
important if you plan to insert into the Merge table rather than the
underlying tables. The INSERT_METHOD option, which
can be NO, FIRST, or
LAST, tells MySQL how to handle inserts to the
Merge table. If the method is NO, inserts
aren't allowed. Otherwise, inserts will always go to
either the first or last of the underlying tables based on the value
of INSERT_METHOD.

The order of the tables is also important for unique-key lookups
because as soon as the record is found, MySQL stops looking. Thus,
the earlier in the list the table is, the better. In most logging
applications where you'll be doing searches on the
Merge table, it might make sense to put the tables in reverse
chronological order. The order is also important for making
ORDER BY as fast as possible
because the required merge-sort will be faster when the rows are
nearly in order already. If you don't specify
INSERT_METHOD, the default is
NO.

As with other tables, you can use SHOW
TABLE STATUS to get information
about a Merge table:

mysql> SHOW TABLE STATUS LIKE 'mytable' \G
*************************** 1. row ***************************
Name: mytable
Type: MRG_MyISAM
Row_format: Fixed
Rows: 2
Avg_row_length: 23
Data_length: 46
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options: 
Comment: 
1 row in set (0.01 sec)

Not all of the data is available. MySQL doesn't keep
track of the creation, update, and check times for merge tables. It
also doesn't store the create options that you might
expect. However, you can retrieve that information using
SHOW CREATE
TABLE:

mysql> SHOW CREATE TABLE mytable \G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`a` int(11) NOT NULL default '0',
`b` char(18) NOT NULL default ,
PRIMARY KEY  (`a`)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(mytable0,mytable1,mytable2)
1 row in set (0.00 sec)

This demonstrates that Merge tables really aren't
full-fledged tables. In fact, Merge tables have some important
limitations and surprising behavior:

REPLACE queries don't work on
them.

AUTO_INCREMENT columns aren't
updated on insert. They are updated if you insert directly into one
of the underlying tables.

DROP TABLE mytable will drop only the virtual
table, not the underlying tables. This may or may not be what
you'd expect.

2.5.5 InnoDB Tables

The InnoDB table handler is the newest
addition to the MySQL family. Developed by Heikki Tuuri of
Innobase Oy in
Helsinki, Finland, InnoDB was designed with transaction processing in
mind and modeled largely after Oracle.

2.5.5.1 Storage

The InnoDB
table handler breaks from MySQL tradition and stores all its data in
a series of one or more data files that are collectively known as a
tablespace.
A tablespace is essentially a black box that is completely managed by
InnoDB. If a tablespace if composed of several underlying files, you
can't choose or influence which of the underlying
files will contain the data for any particular database or table.

InnoDB can also use raw disk partitions in building its tablespace,
but that's not very common. Using disk partitions
makes it more difficult to back up InnoDB's data,
and the resulting performance boost is on the order of a few percent
on most operating systems.

As of MySQL 4.1, you have the option of slightly more MyISAM-like
storage for InnoDB. You can enable multiple tablespace support by
adding innodb_file_per_table to
my.cnf; this makes InnoDB create one tablespace
file per newly created InnoDB table. The filename will be of the form
tablename.ibd. In all other respects,
they're simply dynamically sized InnoDB tablespace
files. Each one just happens to contain data for only one specific
table.

2.5.5.2 Locking and concurrency

InnoDB uses MVCC to achieve very
high concurrency. InnoDB defaults to the repeatable read isolation
level, and as of MySQL Version 4.0.5, it implements all four levels:
read uncommitted, read committed, repeatable read, and serializable.

In an InnoDB transaction, You may explicitly obtain either exclusive
or shared locks on rows using the MySQL statements:
SELECT ...
FOR UPDATE and
SELECT ...
LOCK IN
SHARE MODE.

2.5.5.3 Special features

Besides its excellent concurrency,
InnoDB's next most popular feature is
referential integrity in the form of foreign
key constraints. This means that given the following schema:

CREATE TABLE master (
id         INTEGER NOT NULL PRIMARY KEY,
stuff      TEXT    NOT NULL
) TYPE = InnoDB;
CREATE TABLE detail (
master_id  INTEGER     NOT NULL,
detail1    VARCHAR(80) NOT NULL,
detail2    VARCHAR(20) NOT NULL,
INDEX      master_idx (master_id),
FOREIGN KEY (master_id) REFERENCES master(id)
) TYPE = InnoDB;

InnoDB doesn't allow you to insert add records to
the detail table until there is a corresponding record in the master
table. Attempting to do so yields an error:

mysql> INSERT INTO detail VALUES (10, 'blah', 'blah');
ERROR 1216: Cannot add a child row: a foreign key constraint fails

InnoDB also provides lightning fast record lookups for queries that
use a primary key. Its clustered index system (described in more
detail in Chapter 4) explains how it
works.

2.5.6 Heap (In-Memory) Tables

MySQL provides in-memory Heap tables
for applications in which you need incredibly fast access to data
that either never changes or doesn't need to persist
after a restart. Using a Heap table means that a query can complete
without even waiting for disk I/O. This makes sense for lookup or
mapping tables, such as area code to city/state name, or for caching
the results of periodically aggregated data.

2.5.6.1 Limitations

While Heap tables are very fast, they often don't
work well as replacements for disk-based tables. Until MySQL Version
4.1, Heap tables used only hash-based indexes rather than B-tree
indexes (which MyISAM uses). Hash indexes are suited to only a subset
of queries. Section 4.3.2 in Chapter 4 covers this in more detail.

2.5.7 Berkeley DB (BDB) Tables

MySQL's first
transaction-safe storage engine, BDB is built on top of the Berkeley
DB database library, which is now maintained and developed by
Sleepycat Software. In fact, the original work to integrate the
Berkeley DB technology with MySQL was performed jointly by MySQL AB
and Sleepycat Software. Other than transactions, the BDB table
handler's other main feature is that it uses
page-level locking to achieve higher concurrency than MyISAM tables.

Though BDB tables have been available in MySQL since Version 3.23,
they haven't proven very popular among users. Many
users looking for transactions in MySQL were also looking for
row-level locking or MVCC. Further dampening interest in BDB, by the
time the BDB code had stabilized, word of InnoDB began to circulate.
This prompted many users to hold out for the real thing and use
MyISAM tables a bit longer.

If nothing else, the inclusion of BDB tables in MySQL served as a
stepping stone in many ways. It prompted the MySQL developers to put
the transaction-handling infrastructure into MySQL, while at the same
time proving to the skeptics that MySQL wasn't a
toy.