Tài liệu MySQL Administrator''''s Bible- P10 doc

Storage Engines 11

Once this is done you can SELECT from the FEDERATED table and see the rows in the remote

table.

Limitations of FEDERATED tables

Although FEDERATED tables can be extremely useful they do have some limitations. FED￾ERATED tables are non-transactional in nature regardless of the support that the remote table

might have for transactions. In addition, indexes are not always handled the same for the

FEDERATED table as they are for the remote table. FEDERATED tables do not support index

prefixes on CHAR, VARCHAR, TEXT, or BLOB columns. There are instances where indexes are not

used as expected.

Though it is a fairly minor issue, FEDERATED tables do not support the use of ALTER TABLE.

To change the table definition of a FEDERATED table you must drop the table and re-create it.

Even with these limitations the FEDERATED engine can be very useful when remote data access

is needed.

NDB storage engine

The NDB storage engine is used to build a MySQL Cluster. MySQL Cluster is a shared-nothing,

highly available system. MySQL Cluster is designed to avoid a single point of failure. A cluster

will contain one or more SQL nodes (traditional MySQL Servers), one or more data nodes, and

one or more management servers. For more information about MySQL Cluster, see Chapter 22.

Feature summary:

■ Built to provide high availability with redundant sets of data for node failure scenarios.

■ Transactional support.

■ No foreign key support.

■ Row-level locking.

■ Operates either entirely in memory or supports on-disk storage of data columns. Indexes

must always be stored in memory.

Beginning with MySQL Server version 5.1.24 the standard binaries built by Sun Microsystems

will not include the NDB storage engine. Though it is still available with the source code (for

now), the code for the NDB storage engine will not be updated to keep pace with changes in the

NDB storage engine. If you need to use the NDB storage engine, use the MySQL Cluster server

instead of MySQL Server.

Archive storage engine

The Archive storage engine is a good option for those needing to store data long term or

perform data auditing on application operations. For quite some time MySQL has offered an

option of using compressed MyISAM tables. There are performance benefits to using compressed

417

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

MyISAM tables for read operations, but there are several problems with MyISAM compressed

tables:

■ Compressing (or packing) a MyISAM table requires that the database be taken offline while

the table is compressed. This is done through the use of the of the myisampack utility from

the shell prompt.

■ Compressed MyISAM tables are read-only. You cannot INSERT, DELETE, or UPDATE on a

compressed table.

Archive tables are, in several ways, an improvement over these older MyISAM packed tables.

The designers of the Archive storage engine were definitely looking at what was good about

compressed MyISAM tables and improving on the areas of weaknesses of compressed MyISAM

tables.

Feature summary:

■ Non-transactional.

■ Archive tables allow INSERT operations on the table. However, Archive tables do not

allow UPDATE or DELETE operations. For data auditing purposes this is ideal because

auditing requirements specify that once data has been created it cannot be changed in

any manner.

■ Very good data compression factor. The compression factor on an Archive table is higher

than a packed MyISAM table.

■ For reading data, much like the InnoDB engine, the Archive engine uses a snapshot read.

This ensures that read operations do not block write operations.

■ The Archive engine uses row-level locking.

■ The Archive storage engine supports only one index. However, in tests, the Archive stor￾age engine performs better with read queries than MyISAM packed tables, even when the

packed tables have indexes the Archive tables do not.

Archive tables use up to four files during operation. All files begin with a filename of the table

name. The table definition file has a suffix of .frm. The data file has a suffix of .ARZ. The file

with metadata information for the table (if present) has a suffix of .ARM. During table optimiza￾tion operations it is also possible that there is a file with a suffix of .ARN. These files will all be

located in the directory of the database in which the tables are located.

The following shows a modified version of the rental table from the sakila database.

Changes were made because Archive tables do not support foreign keys and the rental table

had more than one index:

mysql> CREATE TABLE `rental_archive` (

-> `rental_id` int(11) NOT NULL AUTO_INCREMENT,

-> `rental_date` datetime NOT NULL,

-> `inventory_id` mediumint(8) unsigned NOT NULL,

-> `customer_id` smallint(5) unsigned NOT NULL,

418

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Storage Engines 11

-> `return_date` datetime DEFAULT NULL,

-> `staff_id` tinyint(3) unsigned NOT NULL,

-> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

-> PRIMARY KEY (`rental_id`)

-> ) ENGINE=Archive DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

This creates an archival version of the table. Now to demonstrate the ability to perform INSERT

statements but not UPDATE or DELETE statements on an Archive table:

mysql> INSERT INTO rental_archive

-> (rental_date, inventory_id, customer_id,

-> return_date, staff_id) values (NOW(),’1’,’23’,NOW(),’1’);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT rental_id, rental_date, inventory_id, customer_id

-> return_date, staff_id, last_update

-> FROM rental_archive\G

*************************** 1. row ***************************

rental_id: 1

rental_date: 2009-01-16 16:44:41

inventory_id: 1

customer_id: 23

return_date: 2009-01-16 16:44:41

staff_id: 1

last_update: 2009-01-16 16:44:41

1 row in set (0.00 sec)

mysql> UPDATE rental SET rental_date=NOW() WHERE rental_id=’1’;

ERROR 1031 (HY000): Table storage engine for ’rental_archive’ doesn’t

have this option

mysql> DELETE FROM rental_archive WHERE rental_id=’1’;

ERROR 1031 (HY000): Table storage engine for ’rental_archive’ doesn’t

have this option

In a nutshell, what is demonstrated here, along with the data compression capabilities, are the

two primary reasons for using Archive tables.

Blackhole storage engine

The Blackhole storage engine does not actually store data in tables as with other storage engines.

This might seem to be very counter-intuitive. It is a storage engine that does not store data. But

there are uses for this setup.

A master server may have an extremely high-write table whose data is only ever used on a

slave. For example, session information including where a user clicked may be used in reporting

queries, but are never queried on the master server. In this case, the table on the master can be

created with ENGINE=BLACKHOLE and the table on the slave can be modified to use any storage

419

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

engine. In this way, data updates on the master happen instantaneously, because INSERT,

UPDATE, and DELETE statements return immediately as successful (so long as they are valid

DML statements according to the table schema). This is a frequently used method of having a

high-write table cause almost no extra I/O on the master (the binary logs are still written to).

Something quite common with large setups is the use of multiple slaves from a master server. If

you have too many slaves this can cause load issues on the master server. You can implement a

relay slave that acts as an intermediary to reduce this load. We discuss this in detail in Chapter

16. This relay slave can be configured using the Blackhole storage engine for all tables being

replicated. Even though the Blackhole tables do not store any data changes, the binary logs are

still written if logging is enabled. The relay slave operates much more efficiently because of the

minimization of I/O activity.

CSV storage engine

The CSV (Comma Separated Value) storage engine is an engine of a decidedly different nature.

A CSV data file is simply a text file that can be manipulated with a simple text editor or

command-line text tools if needed. One of the primary uses for CSV is for data exchange and

fast importing.

Feature summary:

■ Plain text data file in CSV format

■ Can have an instantaneous import time

■ Easily imported into programs such as Microsoft Excel

■ Table-level locking

■ No foreign key support

■ Non-transactional

■ Trivial backups and restores (copy the files)

■ Does not support indexing or partitions

Three files are created with any CSV table: an .frm file, which contains the table format; a .CSM

file, which contains metadata; and the .CSV file, which contains the data. Each of these files has

a prefix that consists of the table name.

Here is a brief example from a snippet of a data file of a three-field CSV table:

"1","Overstreet","Joe"

"2","Beal", "Sally"

"3","Murphy","Ashton"

"4","McGhee", "Sam"

420

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Storage Engines 11

Want an instantaneous import time? Just have the data that you want to be imported

stored in the CSV format in an export file. Create a new CSV table with the desired

fields and then just copy the export file to the directory of the database. Then move the file to

table_name.CSV. Issue a FLUSH TABLES command and the data is instantly available.

Working with Storage Engines

A number of commands are used specifically to work with any storage engine. These commands

are extremely useful in the day-to-day work of a system administrator.

CREATE TABLE

The CREATE TABLE statement is used to create a database table. You can specify the storage

engine that you want to use for the table by using the ENGINE clause or the server will use the

default storage engine.

For example, to create an InnoDB table:

CREATE TABLE innodb_example (

id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) NOT NULL

) ENGINE = InnoDB;

ALTER TABLE

The ALTER TABLE command is used to modify previously created tables. The full syntax for the

command is covered in Chapter 4. If you need to change a table type from one storage engine

to another the ALTER TABLE command makes this simple. If you executed the previous CREATE

TABLE without specifying an ENGINE clause it would create a MyISAM table by default:

CREATE TABLE innodb_example (

id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) NOT NULL

);

After you create the table you realize it needed to be an InnoDB table. To change this you sim￾ply run the ALTER TABLE command:

mysql> ALTER TABLE innodb_example ENGINE=InnoDB;

That is all there is to it!

421

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.