选择mysql的表引擎

longtem

What if you were a racecar driver and could swap engines with the flip
of a switch instead of having to make a trip to the garage? The MySQL
database does something like this for developers; it gives you a choice
of database engines and an easy way to switch them.
The MySQL
stock engine is certainly adequate, but there are circumstances where
the other available engines may be better suited to the task at hand.
If you want, you can even bore out the cylinders and slap on a
four-barrel carburetor by building your own database engine using the
MySQL++ API. Let's look at how you choose the engine and how to change
between engines that are available to you.
Choose your engine
The
number of database engines available to you depends on how your
installation of MySQL was compiled. To add a new engine, MySQL must be
recompiled. The concept of compiling an application just to add a
feature may seem odd to Windows developers, but in the UNIX world, it's
the norm. By default, MySQL supports three database engines: ISAM,
MyISAM, and HEAP. Two other types, InnoDB and Berkley (BDB), are often
available as well.
ISAM
ISAM is
a well-defined, time-tested method of managing data tables, designed
with the idea that a database will be queried far more often than it
will be updated. As a result, ISAM performs very fast read operations
and is very easy on memory and storage resources. The two main
downsides of ISAM are that it doesn't support transactions and isn't
fault-tolerant: If your hard drive crashes, the data files will not be
recoverable. If you're using ISAM in a mission-critical application,
you’ll want to have a provision for constantly backing up all your live
data, something MySQL supports through its capable replication features.
MyISAM
MyISAM
is MySQL's extended ISAM format and default database engine. In
addition to providing a number of indexing and field management
functions not available in ISAM, MyISAM uses a table-locking mechanism
to optimize multiple simultaneous reads and writes. The trade-off is
that you need to run the OPTIMIZE TABLE command from time to time to
recover space wasted by the update algorithms. MyISAM also has a few
useful extensions such as the MyISAMChk utility to repair database
files and the MyISAMPack utility for recovering wasted space.
MyISAM,
with its emphasis on speedy read operations, is probably the major
reason MySQL is so popular for Web development, where the vast majority
of the data operations you’ll be carrying out are read operations. As a
result, most hosting and Internet Presence Provider (IPP) companies
will allow the use of only the MyISAM format.
HEAP
HEAP
allows for temporary tables that reside only in memory. Residing in
memory makes HEAP faster than ISAM or MyISAM, but the data it manages
is volatile and will be lost if it's not saved prior to shutdown. HEAP
also doesn’t waste as much space when rows are deleted. HEAP tables are
very useful in situations where you might use a nested SELECT statement
to select and manipulate data. Just remember to destroy the table after
you’re done with it. Let me repeat that: Don’t forget to destroy the
table after you’re done with it.
InnoDB and Berkley DB
The
InnoDB and Berkley DB (BDB) database engines are direct products of the
technology that makes MySQL so flexible, the MySQL++ API. Almost every
challenge you're likely to face when using MySQL stems directly from
the fact that the ISAM and MyISAM database engines aren't transactional
and lack foreign-key support. Although much slower than the ISAM and
MyISAM engines, InnoDB and BDB include the transactional and
foreign-key support missing from the former two choices. As such, if
your design requires either or both of these features, you’re actually
compelled to use one of these two choices.
If you’re feeling
particularly capable, you can create your own database engine using
MySQL++. The API provides all the functions you need for working with
fields, records, tables, databases, connections, security accounts, and
all of the other myriad functions that make up a DBMS such as MySQL.
Going too heavily into the API is beyond the scope of this article, but
it’s important to know that MySQL++ exists and that it’s the technology
behind MySQL’s swappable engines. Presumably, this model of plug-in
database engines could even be used to build a native XML provider for
MySQL. (Any MySQL++ developers reading this may consider that a
request.)
Flipping the switch
The
switch that makes all this flexibility possible is an extension MySQL
provides to ANSI SQL, the TYPE parameter. MySQL allows you to specify
database engines at the table level, so they are sometimes referred to
as table formats. The following sample code shows how to create
tables that use the MyISAM, ISAM, and HEAP engines, respectively.
Notice that the code to create each table is the same, with the
exception of the trailing TYPE parameter, which specifies the database
engine.

CREATE TABLE tblMyISAM (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=MyISAM
CREATE TABLE tblISAM (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=ISAM
CREATE TABLE tblHeap (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=Heap

You
can also use the ALTER TABLE command to move an existing table from one
engine to another. The following code shows the use of ALTER TABLE to
move a MyISAM table to InnoDB's engine:

ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB

MySQL
makes this happen with three steps. First, an exact copy of the table
is created. Next, any incoming data changes are queued, while the copy
is moved to the other engine. Finally, any queued data changes are
committed to the new table, and the original one is deleted.
An ALTER TABLE shortcut
If you’re simply upgrading your tables from ISAM to MyISAM, you can use the command mysql_convert_table_format instead of writing ALTER TABLE statements.
You can use the SHOW TABLE command
(which is another MySQL extension to the ANSI standard) to determine
which engine is managing a particular table. SHOW TABLE returns a
result set with multiple columns that you can query to get all kinds of
information: The name of the database engine is in the Type field. The
following sample code illustrates the use of SHOW TABLE:

SHOW TABLE STATUS FROM tblInnoDB

A SHOW TABLE alternative
You can use SHOW CREATE TABLE [TableName] to retrieve the same information that SHOW TABLE retrieves.
Finally, it's worth noting that if
you try to use an engine that isn't compiled into MySQL and activated,
MySQL won’t complain about it. It will instead quietly give you a table
in the default format (MyISAM) instead. There are plans to have MySQL
throw an error in addition to using the default table format, but for
now, you’ll want to check the table format using SHOW TABLE if you’re
not certain that a particular database engine is available.
More choices means better performance
Considering
the additional complications of recompiling and tracking which engines
are used for particular tables, why would you want to use any of the
nondefault database engines? The answer is simple: to tune the database
to meet your needs.
Sure, MyISAM is fast, but if
your logical design requires transactions, you’re free to use one of
the transaction-enabled engines. Further, since MySQL allows you to
apply database engines on the table level, you can take the performance
hit on only the tables that require transactions and leave the
nontransactional tables to be managed by the more lightweight MyISAM
engine. With MySQL, flexibility is the key.
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/18481/showart_1729918.html