List:Commits« Previous MessageNext Message »
From:john.russell Date:April 1 2011 8:01pm
Subject:svn commit - mysqldoc@oter02: r25681 - trunk/refman-5.6
View as plain text  
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.6john.russell1 Apr