InnoDB
Table and Index Structures
1277
A version of
InnoDB
built for one page size cannot use data files or log files
from a version built for a different page size.
14.2.9.3. Insert Buffering
It is a common situation in database applications that the primary key is a unique identifier and new
rows are inserted in the ascending order of the primary key. Thus, insertions into the clustered index do
not require random reads from a disk.
On the other hand, secondary indexes are usually nonunique, and insertions into secondary indexes
happen in a relatively random order. This would cause a lot of random disk I/O operations without a
special mechanism used in
InnoDB
.
If an index record should be inserted into a nonunique secondary index,
InnoDB
checks whether
the secondary index page is in the buffer pool. If that is the case,
InnoDB
does the insertion directly
to the index page. If the index page is not found in the buffer pool,
InnoDB
inserts the record to a
special insert buffer structure. The insert buffer is kept so small that it fits entirely in the buffer pool, and
insertions can be done very fast.
Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is
possible to merge several insertions into the same page of the index tree, saving disk I/O operations. It
has been measured that the insert buffer can speed up insertions into a table up to 15 times.
The insert buffer merging may continue to happen after the inserting transaction has been committed.
In fact, it may continue to happen after a server shutdown and restart (see
Section 14.2.5.2, “Forcing
InnoDB
Recovery”
).
Insert buffer merging may take many hours when many secondary indexes must be updated and
many rows have been inserted. During this time, disk I/O will be increased, which can cause significant
slowdown on disk-bound queries. Another significant background I/O operation is the purge thread (see
Section 14.2.8, “
InnoDB
Multi-Versioning”
).
14.2.9.4. Adaptive Hash Indexes
If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash
indexes.
InnoDB
has a mechanism that monitors index searches made to the indexes defined for a
table. If
InnoDB
notices that queries could benefit from building a hash index, it does so automatically.
The hash index is always built based on an existing B-tree index on the table.
InnoDB
can build a hash
index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches
that
InnoDB
observes for the B-tree index. A hash index can be partial: It is not required that the whole
B-tree index is cached in the buffer pool.
InnoDB
builds hash indexes on demand for those pages of
the index that are often accessed.
In a sense,
InnoDB
tailors itself through the adaptive hash index mechanism to ample main memory,
coming closer to the architecture of main-memory databases.
14.2.9.5. Physical Row Structure
The physical row structure for an
InnoDB
table depends on the MySQL version and the optional
ROW_FORMAT
option used when the table was created. For
InnoDB
tables in MySQL 5.0.3 and earlier,
only the
REDUNDANT
row format was available. For MySQL 5.0.3 and later, the default is to use the
COMPACT
row format, but you can use the
REDUNDANT
format to retain compatibility with older versions
of
InnoDB
tables. To check the row format of an
InnoDB
table use
SHOW TABLE STATUS
.
The compact row format decreases row storage space by about 20% at the cost of increasing CPU
use for some operations. If your workload is a typical one that is limited by cache hit rates and disk
speed, compact format is likely to be faster. If the workload is a rare case that is limited by CPU speed,
compact format might be slower.
Rows in
InnoDB
tables that use
REDUNDANT
row format have the following characteristics:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...