InnoDB 5.1官方插件用户指南：安装与新功能详解

需积分: 10 29 浏览量更新于2024-08-02 收藏 647KB PDF 举报

InnoDB官方文档（1.0 Early Adopter Release）是针对MySQL 5.1的一个实用指南，发布于2009年11月27日，修订版本为477。这份文档由InnoDB引擎的开发商Innobase Oy和Oracle共同维护版权，强调了MySQL 5.1引入的“可插拔”存储引擎架构的重要性。这一创新使得用户可以在不依赖MySQL主版本发布周期的情况下，独立更新InnoDB的版本，包括修复bug和增加新特性，从而提供了更大的灵活性。该文档详尽地介绍了InnoDB Plugin 1.0.6的安装与卸载流程，以及它为MySQL 5.1带来的额外功能。特别值得注意的是，由于InnoDB Plugin引入了新的文件格式，用户在使用早期版本InnoDB创建的数据库时，需要注意一些限制。这意味着在升级到InnoDB Plugin之前，可能需要对旧数据库进行迁移或处理，以确保兼容性和数据完整性。对于那些希望充分利用InnoDB优势的数据库管理员和开发者来说，这份文档是一份不可或缺的参考资料，它不仅涵盖了技术细节，还提供了如何在实际操作中整合和管理InnoDBPlugin的实用建议。理解并遵循其中的指导，能够帮助用户更有效地优化数据库性能，提升系统的稳定性和扩展性。此外，虽然文档是英文版的，但对于熟练掌握英语的读者或者有在线翻译工具支持的人来说，理解和利用这些信息不会构成太大障碍。通过阅读和学习这份文档，用户不仅可以了解InnoDBPlugin的最新特性，还能加深对MySQL 5.1存储引擎体系结构的理解，这对于在现代IT环境中有效利用InnoDB进行高性能数据库设计和管理至关重要。

Fast Index Creation in the

InnoDB Storage Engine

the most-recently committed transactions prior to the creation of the index. The index thus does not contain any rows that

were deleted (and therefore marked for deletion) by transactions that completed before the CREATE INDEX or ALTER

TABLE began. Similarly, the index contains only current versions of every row, and none of the old versions of rows that

were updated by transactions that ran before the index was created.

Because a newly-created index contains only information about data current at the time the index was created, queries that

need to see data that was deleted or changed before the index was created cannot use the index. The only queries that could

be affected by this limitation are those executing in transactions that began before the creation of the index was begun. For

such queries, unpredictable results could occur. Newer queries can use the index.

2.5. Crash Recovery

No data is lost if the server crashes while an ALTER TABLE command is executing. Recovery, however, is different for

clustered indexes and secondary indexes.

If the server crashes while creating a secondary index, upon recovery, InnoDB drops any partially created indexes. All you

need to do to create the index is to re-run the ALTER TABLE or CREATE INDEX command.

However, when a crash occurs during the creation of a clustered index, recovery is somewhat more complicated, because

the data in the table must be copied to an entirely new clustered index. Remember that all InnoDB tables are stored as

clustered indexes. In the following discussion, we use the word table and clustered index interchangeably.

The InnoDB Plugin creates the new clustered index by copying the existing data from the original table to a temporary

table that has the desired index structure. Once the data is completely copied to this temporary table, the original table is

renamed with a different temporary table name. The temporary table comprising the new clustered index is then renamed

with the name of the original table, and the original table is then dropped from the database.

If a system crash occurs while creating a new clustered index, no data is lost, but users must complete the recovery process

using the temporary tables that exist during the process.

Users rarely re-create a clustered index or re-define primary keys on large tables. Because system crashes are uncommon

and the situation described here is rare, this manual does not provide information on recovering from this scenario. Instead,

please see the InnoDB web site: http://www.innodb.com/support/tips.

2.6. Limitations

You should be aware of the following considerations when creating or dropping indexes using the InnoDB Plugin:

If any of the indexed columns use the UTF-8 character encoding, MySQL will copy the table. This has been reported as

MySQL Bug #33650 [http://bugs.mysql.com/bug.php?id=33650].

Due to a limitation of MySQL, the table is copied, rather than using “Fast Index Creation” when you create an index on a

TEMPORARY TABLE. This has been reported as MySQL Bug #39833 [http://bugs.mysql.com/bug.php?id=39833].

The command ALTER IGNORE TABLE t ADD UNIQUE INDEX does not delete duplicate rows. This has been reported

as MySQL Bug #40344 [http://bugs.mysql.com/bug.php?id=40344]. The IGNORE keyword is ignored, and duplicates

cause failure of the operation with the following error message:

ERROR 23000: Duplicate entry '347' for key 'pl'

As noted above, a newly-created index contains only information about data current at the time the index was created.

Therefore, you should not run queries in a transaction that might use a secondary index that did not exist at the beginning

of the transaction. There is no way for InnoDB to access “old” data that is consistent with the rest of the data read by the

transaction. See the discussion of locking in Section 2.4, “Concurrency Considerations”.

Prior to InnoDB Plugin 1.0.4, unexpected results could occur if a query attempts to use an index created after the start

of the transaction containing the query. If an old transaction attempts to access a “too new” index, InnoDB Plugin 1.0.4

and later reports an error:

Chapter 3. InnoDB Data Compression

3.1. Background

Over the years, processors and cache memories have become much faster, but mass storage based on rotating magnetic

disks has not kept pace. While the storage capacity of disks has grown by about a factor of 1,000 in the past decade, random

seek times and data transfer rates are still severely limited by mechanical constraints. Therefore, many workloads are i/o-

bound. The idea of data compression is to pay a small cost in increased CPU utilization for the benefit of smaller databases

and reduced i/o to improve throughput, potentially significantly.

The ability to compress user data is an important new capability of the InnoDB Plugin. Compressed tables reduce the size

of the database on disk, resulting in fewer reads and writes needed to access the user data. For many InnoDB workloads

and many typical user tables (especially with read-intensive applications where sufficient memory is available to keep

frequently-used data in memory), compression not only significantly reduces the storage required for the database, but also

improves throughput by reducing the i/o workload, at a modest cost in processing overhead. The storage cost savings can

be important, but the reduction in i/o costs can be even more valuable.

3.2. Specifying Compression

The usual size of InnoDB data pages is 16K. Beginning with the InnoDB Plugin, you can use the attributes

ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE in the CREATE TABLE and ALTER TABLE commands to re-

quest InnoDB to compress each page to 1K, 2K, 4K, 8K, or 16K bytes.

(The term KEY_BLOCK_SIZE may be confusing. It does not refer to a “key” at all, but simply specifies the size of

compressed pages that will be used for the table. Likewise, in the InnoDB Plugin, compression is applicable to tables, not to

individual rows, so the option ROW_FORMAT really should be TABLE_FORMAT. Unfortunately, MySQL does not permit

a storage engine to add syntax to SQL statements, so the InnoDB Plugin simply re-uses the clauses originally defined

for MyISAM).

To create a compressed table, you might use a command like this:

CREATE TABLE name (column1 INT PRIMARY KEY) ENGINE=InnoDB

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

If KEY_BLOCK_SIZE not specified, the default compressed page size of 8K will be used. If KEY_BLOCK_SIZE is

specified, the attribute ROW_FORMAT=COMPRESSED may be omitted.

Setting KEY_BLOCK_SIZE=16 most often will not result in much compression, since the normal InnoDB page size is

16K. However, this setting may be useful for tables with many long BLOB, VARCHAR or TEXT columns, because such data

often do compress well, and might therefore require fewer “overflow” pages as described in Section 3.3.2.2, “ Compressing

BLOB, VARCHAR and TEXT columns ”.

Note that compression is specified on a table-by-table basis. All indexes of a table (including the clustered index) will be

compressed using the same page size, as specified on the CREATE TABLE or ALTER TABLE command. Table attributes

such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEX syntax, and are ignored if they are

specified (although you will see them in the output of the SHOW CREATE TABLE command).

3.2.1. Configuration Parameters for Compression

Compressed tables are stored in a format previous versions of InnoDB cannot process. Therefore, to preserve downward

compatibility of database files, compression can be specified only when the “Barracuda” data file format is enabled using

the configuration parameter innodb_file_format.

Furthermore, table compression is not available for the InnoDB system tablespace. The system tablespace (space 0, the

ibdata* files) may contain user data, but it also contains internal InnoDB system information, and therefore is never

compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces.

InnoDB Data Compression

To use compression, you must enable the “file per table” mode using the configuration param-

eter innodb_file_per_table and enable the “Barracuda” disk file format using the parameter

innodb_file_format. You can set these parameters in the MySQL option file my.cnf or my.ini, but both are

dynamic parameters that you can change with the SET command without shutting down the MySQL server, as noted in

Section 9.5, “Configuring the InnoDB Plugin”.

Specifying ROW_FORMAT=COMPRESSED or a KEY_BLOCK_SIZE in the CREATE TABLE or ALTER TABLE com-

mands if the “Barracuda” file format has not been enabled will produce these warnings that you can view with the SHOW

WARNINGS command:

Level Code Message

Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1.

Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.

Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

Note that these messages are only warnings, not errors, and the table will be created as if the options were not specified.

If you wish, you may enable InnoDB “strict mode” (see Section 8.5, “InnoDB Strict Mode”) to cause InnoDB to generate

an error, not a warning. In strict mode, the table would not be created if the current configuration does not permit using

compressed tables.

The “non-strict” behavior is intended to permit you to import a mysqldump file into a database that does not support

compressed tables even if the source database contained compressed tables. In that case the InnoDB Plugin will create the

table in ROW_FORMAT=COMPACT instead of preventing the operation.

Generally, however, when you import the dump file into a new database, you should be sure the server is run-

ning the InnoDB Plugin with the proper settings for the configuration parameters innodb_file_format and

innodb_file_per_table, if you want to have the tables re-created as they exist in the original database.

3.2.2. SQL Compression Syntax Warnings and Errors

The attribute KEY_BLOCK_SIZE is permitted only when ROW_FORMAT is specified as COMPRESSED or is omitted.

Specifying a KEY_BLOCK_SIZE with any other ROW_FORMAT will generate a warning that you can view with SHOW

WARNINGS. However, the table will be as non-compressed (the specified KEY_BLOCK_SIZE will be ignored).

Level Code Message

Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

If you are running in InnoDB strict mode, the combination of a KEY_BLOCK_SIZE with any ROW_FORMAT other than

COMPRESSED will generate an error, not a warning, and the table will not be created.

Table 3.1, “ Meaning of CREATE TABLE and ALTER TABLE options ” summarizes how the various options on CREATE

TABLE and ALTER TABLE are handled.

Table 3.1. Meaning of CREATE TABLE and ALTER TABLE options

Option Usage Description

ROW_FORMAT=

REDUNDANT

Storage format used prior to MySQL

5.0.3

Less efficient than ROW_FORMAT=COMPACT; for backward

compatibility

ROW_FORMAT=

COMPACT

Default storage format since MySQL

5.0.3

Stores a prefix of 768 bytes of long column values in the clustered

index page, with the remaining bytes stored in an overflow page

ROW_FORMAT=

DYNAMIC

Available only with innodb_file

_format=Barracuda

Will store values within the clustered index page if they fit; if not,

will store only a 20-byte pointer to an overflow page (no prefix)

InnoDB Data Compression

Option Usage Description

ROW_FORMAT=

COMPRESSED

Available only with innodb_file

_format=Barracuda

Compresses the table and indexes using zlib to de-

fault compressed page size of 8K bytes; implies

ROW_FORMAT=DYNAMIC

KEY_BLOCK_

SIZE=n

Available only with innodb_file

_format=Barracuda

Specifies compressed page size of 1, 2, 4, 8 or 16K bytes; implies

ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED

Table 3.2, “CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF” summarizes error condi-

tions that occur with certain combinations of configuration parameters and options on the CREATE TABLE or ALTER

TABLE commands, and how the options appear in the output of SHOW TABLE STATUS.

When InnoDB strict mode is OFF, InnoDB will create or alter the table, but may ignore certain settings, as shown below.

You can see the warning messages in the MySQL error log. When InnoDB strict mode is ON, these specified combinations

of options generate errors, and the table is not created or altered. You can see the full description of the error condition

with SHOW ERRORS. For example:

mysql> CREATE TABLE x (id INT PRIMARY KEY, c INT)

-> ENGINE=INNODB KEY_BLOCK_SIZE=33333;

ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)

mysql> SHOW ERRORS;

+-------+------+-------------------------------------------+

| Level | Code | Message |

+-------+------+-------------------------------------------+

| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333. |

| Error | 1005 | Can't create table 'test.x' (errno: 1478) |

+-------+------+-------------------------------------------+

2 rows in set (0.00 sec)

Table 3.2. CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

Syntax Warning or Error Condition Resulting ROW_FORMAT, as

shown in SHOW TABLE STATUS

ROW_FORMAT=REDUNDANT None REDUNDANT

ROW_FORMAT=COMPACT None COMPACT

ROW_FORMAT=COMPRESSED

or ROW_FORMAT=DYNAMIC

or KEY_BLOCK_SIZE is spec-

ified

Ignored unless you override the default

settings for innodb_file_format and

innodb_file_per_table

COMPACT

Invalid KEY_BLOCK_SIZE is

specified (not 1, 2, 4, 8 or 16)

KEY_BLOCK_SIZE is ignored the requested one, or COMPACT by

default

ROW_FORMAT=COMPRESSED

and valid KEY_BLOCK_SIZE

are specified

None; KEY_BLOCK_SIZE specified is used, not

the 8K default

COMPRESSED

KEY_BLOCK_SIZE is speci-

fied with REDUNDANT, COM-

PACT or DYNAMIC row format

KEY_BLOCK_SIZE is ignored REDUNDANT, COMPACT or DY-

NAMIC

ROW_FORMAT is not one of

REDUNDANT, COMPACT, DY-

NAMIC or COMPRESSED

Ignored if recognized by the MySQL parser. Other-

wise, an error will be issued.

COMPACT or N/A

When InnoDB strict mode is ON (innodb_strict_mode=1), the InnoDB Plugin will reject invalid ROW_FORMAT

or KEY_BLOCK_SIZE parameters. For compatibility with the built-in InnoDB in MySQL, InnoDB strict mode is not

enabled by default, and in this default non-strict mode, the InnoDB Plugin will issue warnings (not errors) for ignored

invalid parameters.

剩余85页未读，继续阅读

skyoverflow

粉丝: 17
资源: 9

InnoDB 5.1官方插件用户指南：安装与新功能详解

深入理解InnoDB存储引擎技术内幕

Percona Xtrabackup教程：InnoDB热备份工具

MySQL InnoDB高性能应用：实测与配置策略

Innodb核心内幕

innodb详细架构

MySQL5.6官方文档

MySQL5.7官方文档翻译版

MySQL官方中文文档

Percona Xtrabackup 2.4官方文档：数据库无锁备份工具

Percona XtraBackup 2.4官方文档：无缝备份MySQL服务器指南

最新资源