Oracle 5.0, Справочное руководство

Страница: 1319 / 3232

Limits on
InnoDB
Tables
1299
MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in
the right way, you can try using
ANALYZE TABLE
. In the few cases that
ANALYZE TABLE
does
not produce values good enough for your particular tables, you can use
FORCE INDEX
with your
queries to force the use of a particular index, or set the
max_seeks_for_key
[471] system variable
to ensure that MySQL prefers index lookups over table scans. See Section 5.1.4, “Server System
Variables” , and Section C.5.6, “Optimizer-Related Issues” .
•
SHOW TABLE STATUS
does not give accurate statistics on
InnoDB
tables, except for the physical
size reserved by the table. The row count is only a rough estimate used in SQL optimization.
•
InnoDB
does not keep an internal count of rows in a table because concurrent transactions might
“see” different numbers of rows at the same time. To process a
SELECT COUNT(*) FROM t
statement,
InnoDB
must scan an index of the table, which takes some time if the index is not
entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a
good solution. To get a fast count, you have to use a counter table you create yourself and let your
application update it according to the inserts and deletes it does. If an approximate row count is
sufficient,
SHOW TABLE STATUS
can be used. See Section 14.2.12.1, “
InnoDB
Performance Tuning
Tips” .
• On Windows,
InnoDB
always stores database and table names internally in lowercase. To move
databases in a binary format from Unix to Windows or from Windows to Unix, create all databases
and tables using lowercase names.
• An
AUTO_INCREMENT
column
ai_col
must be defined as part of an index such that it is possible
to perform the equivalent of an indexed
SELECT MAX(ai_col)
lookup on the table to obtain the
maximum column value. Typically, this is achieved by making the column the first column of some
table index.
• In MySQL 5.0 before MySQL 5.0.3,
InnoDB
does not support the
AUTO_INCREMENT
table option for
setting the initial sequence value in a
CREATE TABLE
or
ALTER TABLE
statement. To set the value
with
InnoDB
, insert a dummy row with a value one less and delete that dummy row, or insert the first
row with an explicit value specified.
• While initializing a previously specified
AUTO_INCREMENT
column on a table,
InnoDB
sets an
exclusive lock on the end of the index associated with the
AUTO_INCREMENT
column. While
accessing the auto-increment counter,
InnoDB
uses a specific
AUTO-INC
table lock mode where
the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction.
Other clients cannot insert into the table while the
AUTO-INC
table lock is held. See Section 14.2.3.3,
“
AUTO_INCREMENT
Handling in
InnoDB
” .
• When you restart the MySQL server,
InnoDB
may reuse an old value that was generated for an
AUTO_INCREMENT
column but never stored (that is, a value that was generated during an old
transaction that was rolled back).
• When an
AUTO_INCREMENT
column runs out of values,
InnoDB
wraps a
BIGINT
to
-9223372036854775808
and
BIGINT UNSIGNED
to
1
. However,
BIGINT
values have 64 bits, so
if you were to insert one million rows per second, it would still take nearly three hundred thousand
years before
BIGINT
reached its upper bound. With all other integer type columns, a duplicate-key
error results. This is general MySQL behavior, similar to how
MyISAM
works.
•
DELETE FROM tbl_name
does not regenerate the table but instead deletes all rows, one by one.
• Under some conditions,
TRUNCATE tbl_name
for an
InnoDB
table is mapped to
DELETE FROM
tbl_name
and does not reset the
AUTO_INCREMENT
counter. See Section 13.1.21, “
TRUNCATE
TABLE
Syntax” .
• The
LOAD TABLE FROM MASTER
statement for setting up replication slave servers does not work
for
InnoDB
tables. A workaround is to alter the table to
MyISAM
on the master, then do the load,
and after that alter the master table back to
InnoDB
. Do not do this if the tables use
InnoDB
-specific
features such as foreign keys.