Author: jdrussel
Date: 2011-04-01 22:01:23 +0200 (Fri, 01 Apr 2011)
New Revision: 25681
Log:
Included the FIC section from the InnoDB doc in a semi-sensible place early in the SE::InnoDB section.
I'll probably rearrange the "Creating and Using InnoDB Tables" later since it has some non-table stuff
and doesn't highlight indexes in its subheads.
Added:
trunk/refman-5.6/innodb-create-index.xml
Modified:
trunk/refman-5.6/se-innodb-core.xml
Added: trunk/refman-5.6/innodb-create-index.xml
===================================================================
--- trunk/refman-5.6/innodb-create-index.xml (rev 0)
+++ trunk/refman-5.6/innodb-create-index.xml 2011-04-01 20:01:23 UTC (rev 25681)
Changed blocks: 1, Lines Added: 530, Lines Deleted: 0; 20957 bytes
@@ -0,0 +1,530 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % innodb.entities SYSTEM "innodb-entities.ent">
+]>
+<section id="innodb-create-index">
+
+ <title>Fast Index Creation in the InnoDB Storage Engine</title>
+
+ <para>
+ In MySQL 5.5 and higher, or in MySQL 5.1 with the InnoDB Plugin,
+ creating and dropping <link linkend="glos_secondary_index">secondary
+ indexes</link> does not copy the contents of the entire table,
+ making this operation much more efficient than with prior releases.
+ </para>
+
+ <section id="innodb-create-index-overview">
+
+ <title>Overview of Fast Index Creation</title>
+
+ <indexterm>
+ <primary><literal role="stmt">CREATE INDEX</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal role="stmt">DROP INDEX</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>overview</secondary>
+ </indexterm>
+
+<!-- JDR: here's the question: how much of this can be folded under CREATE INDEX or Optimization now that it will be the normal behaviour? -->
+
+<!-- JDR: is the "far from optimal" assertion true given change buffering? perhaps that's more detail than necessary -->
+
+ <para>
+ With MySQL 5.5 and higher, or MySQL 5.1 with the InnoDB Plugin,
+ creating and dropping
+ <link linkend="glos_secondary_index">secondary indexes</link> for
+ InnoDB tables is much faster than before. Historically, adding or
+ dropping an index on a table with existing data could be very
+ slow. The <literal role="stmt">CREATE INDEX</literal> and
+ <literal role="stmt">DROP INDEX</literal> statements worked by
+ creating a new, empty table defined with the requested set of
+ indexes, then copying the existing rows to the new table
+ one-by-one, updating the indexes as the rows are inserted. After
+ all rows from the original table were copied, the old table was
+ dropped and the copy was renamed with the name of the original
+ table.
+ </para>
+
+ <para>
+ The performance speedup for fast index creation applies to
+ secondary indexes, not to the primary key index. The rows of an
+ InnoDB table are stored in a
+ <link
+ linkend="glos_clustered_index">clustered index</link>
+ organized based on the <link linkend="glos_primary_key">primary
+ key</link>, forming what some database systems call an
+ <quote>index-organized table</quote>. Because the table structure
+ is so closely tied to the primary key, redefining the primary key
+ still requires copying the data.
+ </para>
+
+<!-- JDR: This tuning tip bears repeating in the Optimization chapter. -->
+
+ <para>
+ This new mechanism also means that you can generally speed the
+ overall process of creating and loading an indexed table by
+ creating the table with only the clustered index, and adding the
+ secondary indexes after the data is loaded.
+ </para>
+
+ <para>
+ Although no syntax changes are required in the
+ <literal role="stmt">CREATE INDEX</literal> or
+ <literal role="stmt">DROP INDEX</literal> commands, some factors
+ affect the performance, space usage, and semantics of this
+ operation (see <xref linkend="innodb-create-index-limitations"/>).
+ </para>
+
+ </section>
+
+ <section id="innodb-create-index-examples">
+
+<!-- JDR: compare and contrast these examples with the one(s) under CREATE INDEX and DROP INDEX -->
+
+ <title>Examples of Fast Index Creation</title>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>examples</secondary>
+ </indexterm>
+
+ <para>
+ It is possible to create multiple indexes on a table with one
+ <literal role="stmt">ALTER TABLE</literal> statement. This is
+ relatively efficient, because the clustered index of the table
+ needs to be scanned only once (although the data is sorted
+ separately for each new index). For example:
+ </para>
+
+<programlisting>
+CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;
+INSERT INTO T1 VALUES (1,2,'a'), (2,3,'b'), (3,2,'c'), (4,3,'d'), (5,2,'e');
+COMMIT;
+ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);
+</programlisting>
+
+ <para>
+ The above statements create table <literal>T1</literal> with the
+ clustered index (primary key) on column <literal>A</literal>,
+ insert several rows, and then build two new indexes on columns
+ <literal>B</literal> and <literal>C</literal>. If there were many
+ rows inserted into <literal>T1</literal> before the
+ <literal role="stmt">ALTER TABLE</literal> statement, this
+ approach is much more efficient than creating all the secondary
+ indexes before loading the data.
+ </para>
+
+<!-- JDR: Another good tuning tip. I think this section would get better visibility if moved to the Optimization chapter. -->
+
+ <para>
+ You can also create the indexes one at a time, but then the
+ clustered index of the table is scanned (as well as sorted) once
+ for each <literal role="stmt">CREATE INDEX</literal> statement.
+ Thus, the following statements are not as efficient as the
+ <literal role="stmt">ALTER TABLE</literal> statement above, even
+ though neither requires recreating the clustered index for table
+ <literal>T1</literal>.
+ </para>
+
+<programlisting>
+CREATE INDEX B ON T1 (B);
+CREATE UNIQUE INDEX C ON T1 (C);
+</programlisting>
+
+ <para>
+ Dropping InnoDB secondary indexes also does not require any
+ copying of table data. You can equally quickly drop multiple
+ indexes with a single <literal role="stmt">ALTER TABLE</literal>
+ statement or multiple <literal role="stmt">DROP INDEX</literal>
+ statements:
+ </para>
+
+<programlisting>
+ALTER TABLE T1 DROP INDEX B, DROP INDEX C;
+</programlisting>
+
+ <para>
+ or:
+ </para>
+
+<programlisting>
+DROP INDEX B ON T1;
+DROP INDEX C ON T1;
+</programlisting>
+
+ <para>
+ Restructuring the clustered index in InnoDB always requires
+ copying the data in the table. For example, if you create a table
+ without a primary key, InnoDB chooses one for you, which may be
+ the first <literal>UNIQUE</literal> key defined on <literal>NOT
+ NULL</literal> columns, or a system-generated key. Defining a
+ <literal>PRIMARY KEY</literal> later causes the data to be copied,
+ as in the following example:
+ </para>
+
+<programlisting>CREATE TABLE T2 (A INT, B INT) ENGINE=InnoDB;
+INSERT INTO T2 VALUES (NULL, 1);
+ALTER TABLE T2 ADD PRIMARY KEY (B);
+</programlisting>
+
+<!-- JDR: Define primary key via CREATE TABLE, not ALTER TABLE. Another good tuning tip. -->
+
+ <para>
+ When you create a <literal>UNIQUE</literal> or <literal>PRIMARY
+ KEY</literal> index, InnoDB must do some extra work. For
+ <literal>UNIQUE</literal> indexes, InnoDB checks that the table
+ contains no duplicate values for the key. For a <literal>PRIMARY
+ KEY</literal> index, InnoDB also checks that none of the
+ <literal>PRIMARY KEY</literal> columns contains a
+ <literal>NULL</literal>. It is best to define the primary key when
+ you create a table, so you need not rebuild the table later.
+ </para>
+
+ </section>
+
+ <section id="innodb-create-index-implementation">
+
+<!-- JDR: don't know if all this implementation detail is really needed in the manual anymore -->
+
+ <title>Implementation Details of Fast Index Creation</title>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>implementation</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>Indexes</primary>
+ <secondary>creating and dropping</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>Indexes</primary>
+ <secondary>primary (clustered) and secondary</secondary>
+ </indexterm>
+
+ <para>
+ InnoDB has two types of indexes: the clustered index and secondary
+ indexes. Since the clustered index contains the data values in its
+ B-tree nodes, adding or dropping a clustered index does involve
+ copying the data, and creating a new copy of the table. A
+ secondary index, however, contains only the index key and the
+ value of the primary key. This type of index can be created or
+ dropped without copying the data in the clustered index. Because
+ each secondary index contains copies of the primary key values
+ (used to access the clustered index when needed), when you change
+ the definition of the primary key, all secondary indexes are
+ recreated as well.
+ </para>
+
+ <para>
+ Dropping a secondary index is simple. Only the internal InnoDB
+ system tables and the MySQL data dictionary tables are updated to
+ reflect the fact that the index no longer exists. InnoDB returns
+ the storage used for the index to the tablespace that contained
+ it, so that new indexes or additional table rows can use the
+ space.
+ </para>
+
+ <para>
+ To add a secondary index to an existing table, InnoDB scans the
+ table, and sorts the rows using memory buffers and temporary files
+ in order by the values of the secondary index key columns. The
+ B-tree is then built in key-value order, which is more efficient
+ than inserting rows into an index in random order. Because the
+ B-tree nodes are split when they fill, building the index in this
+ way results in a higher fill-factor for the index, making it more
+ efficient for subsequent access.
+ </para>
+
+ </section>
+
+ <section id="innodb-create-index-concurrency">
+
+<!-- JDR: seems like now this would be a usage note for CREATE INDEX, DROP INDEX, ALTER TABLE -->
+
+ <title>Concurrency Considerations for Fast Index Creation</title>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>concurrency</secondary>
+ </indexterm>
+
+ <para>
+ While an InnoDB secondary index is being created or dropped, the
+ table is locked in shared mode. Any writes to the table are
+ blocked, but the data in the table can be read. When you alter the
+ clustered index of a table, the table is locked in exclusive mode,
+ because the data must be copied. Thus, during the creation of a
+ new clustered index, all operations on the table are blocked.
+ </para>
+
+ <para>
+ A <literal role="stmt">CREATE INDEX</literal> or
+ <literal role="stmt">ALTER TABLE</literal> statement for an InnoDB
+ table always waits for currently executing transactions that are
+ accessing the table to commit or roll back.
+ <literal role="stmt">ALTER TABLE</literal> statements that
+ redefine an InnoDB primary key wait for all
+ <literal>SELECT</literal> statements that access the table to
+ complete, or their containing transactions to commit. No
+ transactions whose execution spans the creation of the index can
+ be accessing the table, because the original table is dropped when
+ the clustered index is restructured.
+ </para>
+
+ <para>
+ Once a <literal role="stmt">CREATE INDEX</literal> or
+ <literal role="stmt">ALTER TABLE</literal> statement that creates
+ an InnoDB secondary index begins executing, queries can access the
+ table for read access, but cannot update the table. If an
+ <literal role="stmt">ALTER TABLE</literal> statement is changing
+ the clustered index for an InnoDB table, all queries wait until
+ the operation completes.
+ </para>
+
+ <para>
+ A newly-created InnoDB secondary index contains only the committed
+ data in the table at the time the <literal role="stmt">CREATE
+ INDEX</literal> or <literal role="stmt">ALTER TABLE</literal>
+ statement begins to execute. It does not contain any uncommitted
+ values, old versions of values, or values marked for deletion but
+ not yet removed from the old index.
+ </para>
+
+<!-- JDR: not clear if "unpredictable results could occur" if we didn't do certain things that we actually do, or if this really is an issue that users could encounter -->
+
+ <para>
+ 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.
+ </para>
+
+ </section>
+
+ <section id="innodb-create-index-recovery">
+
+<!-- JDR: might be better served in a centralized topic for crash recovery -->
+
+ <title>How Crash Recovery Works with Fast Index Creation</title>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>crash recovery</secondary>
+ </indexterm>
+
+ <para>
+ Although no data is lost if the server crashes while an
+ <literal role="stmt">ALTER TABLE</literal> statement is executing,
+ the <link linkend="glos_crash_recovery">crash recovery</link>
+ process is different for
+ <link linkend="glos_clustered_index">clustered indexes</link> and
+ <link linkend="glos_secondary_index">secondary indexes</link>.
+ </para>
+
+ <para>
+ If the server crashes while creating an InnoDB secondary index,
+ upon recovery, MySQL drops any partially created indexes. You must
+ re-run the <literal role="stmt">ALTER TABLE</literal> or
+ <literal role="stmt">CREATE INDEX</literal> statement.
+ </para>
+
+ <para>
+ When a crash occurs during the creation of an InnoDB clustered
+ index, recovery is 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.
+ </para>
+
+ <para>
+ MySQL creates the new clustered index by copying the existing data
+ from the original InnoDB 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 renamed with the name of the original
+ table, and the original table is dropped from the database.
+ </para>
+
+<!-- JDR: is there a MySQL support page where this recovery procedure could live? -->
+
+<!-- JDR: innodb URL might be obsolete now or soon. -->
+
+<!-- JDR: could be a usage not under ALTER TABLE. -->
+
+ <para>
+ If a system crash occurs while creating a new clustered index, no
+ data is lost, but you must complete the recovery process using the
+ temporary tables that exist during the process. Since it is rare
+ to re-create a clustered index or re-define primary keys on large
+ tables, or to encounter a system crash during this operation, this
+ manual does not provide information on recovering from this
+ scenario. Instead, please see the InnoDB web site:
+ <ulink url="http://www.innodb.com/support/tips"/>.
+ </para>
+
+ </section>
+
+ <section id="innodb-create-index-limitations">
+
+<!-- JDR: seems like general limitations for CREATE INDEX / DROP INDEX / ALTER TABLE, or Optimization.
+Probably Optimization since they're mainly "don't do this or it will be slower". -->
+
+ <title>Limitations of Fast Index Creation</title>
+
+ <indexterm>
+ <primary>Fast Index Creation</primary>
+ <secondary>limitations</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>FOREIGN KEY</literal> constraints</primary>
+ <secondary>and fast index creation</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>troubleshooting</secondary>
+ <tertiary>fast index creation</tertiary>
+ </indexterm>
+
+ <para>
+ Take the following considerations into account when creating or
+ dropping InnoDB indexes:
+ </para>
+
+<!-- JDR: would prefer that these bugs be closed rather than keeping the pointers to the old bug system.
+Currently they're all still open. -->
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ During index creation, files are written to the temporary
+ directory (<literal>$TMPDIR</literal> on Unix,
+ <literal>%TEMP%</literal> on Windows, or the value of the
+ <literal role="sysvar">--tmpdir</literal> configuration
+ variable). Each temporary file is large enough to hold one
+ column that makes up the new index, and each one is removed as
+ soon as it is merged into the final index.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If any of the indexed columns use the UTF-8 character
+ encoding, MySQL copies the table instead of using Fast Index
+ Creation. This has been reported as
+ <ulink url="http://bugs.mysql.com/bug.php?id=33650">MySQL Bug
+ #33650</ulink>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The table is copied, rather than using Fast Index Creation
+ when you create an index on a <literal>TEMPORARY
+ TABLE</literal>. This has been reported as
+ <ulink
+ url="http://bugs.mysql.com/bug.php?id=39833">MySQL
+ Bug #39833</ulink>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To avoid consistency issues between the InnoDB data dictionary
+ and the MySQL data dictionary, the table is copied, rather
+ than using Fast Index Creation when you use the <literal>ALTER
+ TABLE ... RENAME COLUMN</literal> syntax.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The statement <literal>ALTER IGNORE TABLE
+ <replaceable>t</replaceable> ADD UNIQUE INDEX</literal> does
+ not delete duplicate rows. This has been reported as
+ <ulink
+ url="http://bugs.mysql.com/bug.php?id=40344">MySQL
+ Bug #40344</ulink>. The <literal>IGNORE</literal> keyword is
+ ignored. If any duplicate rows exist, the operation fails with
+ the following error message:
+ </para>
+
+<programlisting
+ >ERROR 23000: Duplicate entry '<replaceable
+ >347</replaceable>' for key '<replaceable>pl</replaceable
+ >'</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 <quote>old</quote> data that is consistent
+ with the rest of the data read by the transaction. See the
+ discussion of locking in
+ <xref linkend="innodb-create-index-concurrency"/>.
+ </para>
+
+ <para>
+ If a transaction attempts to access an index created after the
+ transaction started, InnoDB reports an error:
+ </para>
+
+<programlisting>
+ERROR HY000: Table definition has changed, please retry transaction
+</programlisting>
+
+ <para>
+ As the error message suggests, committing (or rolling back)
+ the transaction, and restarting it, cures the problem. Prior
+ to MySQL 5.5, unexpected results could occur in such a case.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ InnoDB handles error cases when users attempt to drop indexes
+ needed for foreign keys. See section
+ <xref linkend="innodb-other-changes-drop-index"/> for details.
+ </para>
+ </listitem>
+
+<!-- JDR: also potentially something to note in a central topic about foreign keys -->
+
+<!-- JDR: still an issue? Any enhancement request to remove this limitation? -->
+
+ <listitem>
+ <para>
+ MySQL does not support efficient creation or dropping of
+ <literal>FOREIGN KEY</literal> constraints. Therefore, if you
+ use <literal role="stmt">ALTER TABLE</literal> to add or
+ remove a <literal>REFERENCES</literal> constraint, the child
+ table is copied, rather than using Fast Index Creation.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+</section>
Modified: trunk/refman-5.6/se-innodb-core.xml
===================================================================
--- trunk/refman-5.6/se-innodb-core.xml 2011-04-01 19:41:30 UTC (rev 25680)
+++ trunk/refman-5.6/se-innodb-core.xml 2011-04-01 20:01:23 UTC (rev 25681)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 0; 456 bytes
@@ -6201,6 +6201,8 @@
</section>
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="innodb-create-index.xml"/>
+
</section>
<section id="innodb-data-log-reconfiguration">
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r25681 - trunk/refman-5.6 | john.russell | 1 Apr |
