• MySQL 5.6 Reference Manual-14.6 InnoDB Table Management

    14.6 InnoDB Table Management

    14.6.1 Creating InnoDB Tables

    14.6.2 Moving or Copying InnoDB Tables to Another Machine

    14.6.3 Grouping DML Operations with Transactions

    14.6.4 Converting Tables from MyISAM to InnoDB

    14.6.5 AUTO_INCREMENT Handling in InnoDB

    14.6.6 InnoDB and FOREIGN KEY Constraints

    14.6.7 Limits on InnoDB Tables

    14.6.1 Creating InnoDB Tables

    To create an InnoDB table, use the CREATE TABLE statement. You do not need to specify the ENGINE=InnoDB clause if InnoDB is defined as the default storage engine, which is the default as of MySQL 5.5. You might still use ENGINE=InnoDB clause if you plan to use mysqldump or replication to replay the CREATE TABLE statement on a server where the default storage engine is not InnoDB.

    可以使用CREATE TABLE语句创建一个InnoDB表。从MySQL5.5开始，InnoDB就是默认的存储引擎，所以你不需要指定ENGINE=InnoDB子句。当你准备在默认引擎不是InnoDB的实例上使用mysqldump或者复制重现CREATE TABLE的时候仍然可以使用ENGINE=InnoDB。

    -- Default storage engine = InnoDB.

    CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a));

    -- Backward-compatible with older MySQL.

    CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

    An InnoDB table and its indexes can be created in the system tablespace or in a file-per-table tablespace. When innodb_file_per_table is enabled, which is the default setting as of MySQL 5.6.6, an InnoDB table is implicitly created in an individual file-per-table tablespace. Conversely, when innodb_file_per_table is disabled, an InnoDB table is implicitly created in the system tablespace.

    InnoDB的表和索引可以创建在系统表空间或者file-per-table表空间里。当开启了自MySQL5.6.6开始默认的innodb_file_per_table，InnoDB表会隐式地创建在一个独立的file-per-table表空间里。相反，当innodb_file_per_table关闭的时候，InnoDB表会隐式地创建在系统表空间里。

    When you create an InnoDB table, MySQL creates a .frm file in a database directory under the MySQL data directory. For a table created in a file-per-table tablespace, an .ibd file is also created. A table created in the system tablespace is created in the existing system tablespace ibdata files.

    当你创建了一个InnoDB表的时候，MySQL会在MySQL的数据目录的数据库目录里创建一个.frm，以及一个.ibd文件。创建在系统表空间里的表会被放在现有的系统表空间ibdata文件里。

    Internally, InnoDB adds an entry for each table to the InnoDB data dictionary. The entry includes the database name. For example, if table t1 is created in the test database, the data dictionary entry is 'test/t1'. This means you can create a table of the same name (t1) in a different database, and the table names do not collide inside InnoDB.

    在内部，InnoDB会为每个表在InnoDB的数据字典里面创建一个包含数据库名的条目。例如，表t1创建在test数据库里，数据字典条目就是'test/t1'。这也就意味着你可以在不同的数据库里面创建名字相同的表。在InnoDB内部这些表的名字并不会冲突。

    Viewing the Properties of InnoDB Tables

    To view the properties of InnoDB tables, issue a SHOW TABLE STATUS statement:

    执行SHOW TABLE STATUS语句查看InnoDB表的属性：

    mysql> SHOW TABLE STATUS FROM test LIKE 't%' G;

    *************************** 1. row ***************************

    Name: t1

    Engine: InnoDB

    Version: 10

    Row_format: Compact

    Rows: 0

    Avg_row_length: 0

    Data_length: 16384

    Max_data_length: 0

    Index_length: 0

    Data_free: 0

    Auto_increment: NULL

    Create_time: 2015-03-16 16:26:52

    Update_time: NULL

    Check_time: NULL

    Collation: latin1_swedish_ci

    Checksum: NULL

    Create_options:

    Comment:

    1 row in set (0.00 sec)

    In the status output, you see the Row format property of table t1 is Compact. Although that setting is fine for basic experimentation, consider using the Dynamic or Compressed row format to take advantage of InnoDB features such as table compression and off-page storage for long column values. Using these row formats requires that innodb_file_per_table is enabled (the default as of MySQL 5.6.6) and that innodb_file_format is set to Barracuda:

    在状态输出里面，你可以看到t1表的Row format属性是Compact。虽然根据基本的实验这是一个不错的设定，但是也可以考虑使用InnoDB的特性优势使用Dynamic或者Compressed行格式，例如为很长的列值使用表压缩和不同的页大小存储(off-page storage)。

    SET GLOBAL innodb_file_per_table=1;

    SET GLOBAL innodb_file_format=barracuda;

    CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;

    CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;

    InnoDB table properties may also be queried using the InnoDB Information Schema system tables:

    InnoDB表的熟悉同样也通过查询InnoDB Information Schema的系统表：

    SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' G

    *************************** 1. row ***************************

    TABLE_ID: 42

    NAME: test/t1

    FLAG: 1

    N_COLS: 5

    SPACE: 24

    FILE_FORMAT: Antelope

    ROW_FORMAT: Compact

    ZIP_PAGE_SIZE: 0

    1 row in set (0.02 sec)

    Defining a Primary Key for InnoDB Tables

    Always set up a primary key for each InnoDB table, specifying the column or columns that:

    为每个InnoDB表在这些列上建立主键：

    • Are referenced by the most important queries.
    • 最要查询所引用的。
    • Are never left blank.
    • 不会为空。
    • Never have duplicate values.
    • 没有重复值。
    • Rarely if ever change value once inserted.
    • 一旦插入后很少修改。

    For example, in a table containing information about people, you would not create a primary key on (firstname, lastname) because more than one person can have the same name, some people have blank last names, and sometimes people change their names. With so many constraints, often there is not an obvious set of columns to use as a primary key, so you create a new column with a numeric ID to serve as all or part of the primary key. You can declare an auto-increment column so that ascending values are filled in automatically as rows are inserted:

    例如，在一个关于人口信息的表里，你不能在(firstname, lastname)上创建主键因为有可能会有同名的情况，有些只有last name，还有些人会修改他们的名字。正因为有这么多的约束，这里没有明显的列来作为主键，那你可以创建一个新的数字ID列来作为主键。你可以把列定义成自增长列，使得在insert的时候自动填充递增的值：

    -- The value of ID can act like a pointer between related items in different tables.

    CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

    -- The primary key can consist of more than one column. Any autoinc column must come first.

    CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

    Although the table works correctly without defining a primary key, the primary key is involved with many aspects of performance and is a crucial design aspect for any large or frequently used table. It is recommended that you always specify a primary key in the CREATE TABLE statement. If you create the table, load data, and then run ALTER TABLE to add a primary key later, that operation is much slower than defining the primary key when creating the table.

    虽然即使没有定义主键表也能工作，但是主键涉及了很多性能方面的问题，以及对大表或者频繁使用的表的核心设计问题，所以建议你在CREATE TABLE的时候还是要指定一个主键。如果你创建了表，加载了数据，然后再运行ALTER TABLE来加主键，那这个操作将会比在建表的时候定义主键要慢得很多。

    14.6.2 Moving or Copying InnoDB Tables to Another Machine

    This section describes techniques for moving or copying some or all InnoDB tables to a different server. For example, you might move an entire MySQL instance to a larger, faster server; you might clone an entire MySQL instance to a new replication slave server; you might copy individual tables to another server to develop and test an application, or to a data warehouse server to produce reports.

    这一章节讲述了如何复制或者移动InnoDB表到不同的实例里。例如，你要把整个MySQL实例移动到一个更大，速度更快的服务器上；你要克隆整个MySQL实例到一个新的主从架构的从机上；你可以也可以复制个别表到开发或者测试环境里，又或者是数据仓库里。

    Techniques for moving or copying InnoDB tables include:

    这些移动复制InnoDB表的方法有：

    Using Lowercase Names for Cross-Platform Moving or Copying

    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. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating any databases or tables:

    在Windows上，InnoDB在内部用小写来存储数据库和表的名字。要把二进制格式的数据库从Unix移动到Windows上或者从Windows到Unix上，数据库和表的名字最好都用小写。还有一种方法可以实现这样的目的就是在创建任何的数据库或表之前，在配置文件my.cnf or my.ini的[mysqld]里加入下面的内容：

    [mysqld]

    lower_case_table_names=1

    Transportable Tablespaces

    Introduced in MySQL 5.6.6, the transportable tablespaces feature uses FLUSH TABLES ... FOR EXPORT to ready InnoDB tables for copying from one server instance to another. To use this feature, InnoDB tables must be created with innodb_file_per_table set to ON so that each InnoDB table has its own tablespace. For usage information, see Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server".

    在MySQL5.6.6里，可以使用FLUSH TABLES ... FOR EXPORT的传输表空间特性来把一个InnoDB表复制到另一个实例上。要使用这个特性，innodb_file_per_table必须是要打开的，让每个InnoDB表都有自己的表空间。相关的使用信息可以查看Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server"。

    MySQL Enterprise Backup

    The MySQL Enterprise Backup product lets you back up a running MySQL database, including InnoDB and MyISAM tables, with minimal disruption to operations while producing a consistent snapshot of the database. When MySQL Enterprise Backup is copying InnoDB tables, reads and writes to both InnoDB and MyISAM tables can continue. During the copying of MyISAM and other non-InnoDB tables, reads (but not writes) to those tables are permitted. In addition, MySQL Enterprise Backup can create compressed backup files, and back up subsets of InnoDB tables. In conjunction with the MySQL binary log, you can perform point-in-time recovery. MySQL Enterprise Backup is included as part of the MySQL Enterprise subscription.

    MySQL Enterprise Backup可以让你备份一个正在运行的MySQL数据库，包括InnoDB和MyISAM表，而且在生产数据库一致性快照的时候能够最小化地中断正常的操作。当MySQL Enterprise Backup复制InnoDB表的时候，InnoDB和MyISAM表都能够继续读和写的操作。在复制MyISAM和其他非InnoDB表的时候，读操作(不包括写)是被允许的。另外，MySQL Enterprise Backup能够创建压缩过的备份文件，并以此来备份InnoDB表的子集。连同MySQL的binary log，你还可以执行基于时间点的恢复。MySQL Enterprise Backup包含在MySQL Enterprise里面的。

    For more details about MySQL Enterprise, see Section 25.2, "MySQL Enterprise Backup Overview".

    更多关于MySQL Enterprise的细节可以查看Section 25.2, "MySQL Enterprise Backup Overview"。

    Copying Data Files (Cold Backup Method)

    You can move an InnoDB database simply by copying all the relevant files listed under "Cold Backups" in Section 14.16, "InnoDB Backup and Recovery".

    如Section 14.16, "InnoDB Backup and Recovery"里面"Cold Backups"所讲述的，你可以复制所有相关的文件来简单地移动InnoDB数据库。

    Like MyISAM data files, InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format. If the floating-point formats differ but you have not used FLOAT or DOUBLE data types in your tables, then the procedure is the same: simply copy the relevant files.

    类似于MyISAM的数据文件，InnoDB的数据和日志文件在所有使用相同浮点格式的平台上都是兼容的。如果浮点格式不同但是在表里也没有使用FLOAT or DOUBLE的数据类型，也是可以进行简单复制的。

    Portability Considerations for .ibd Files

    When you move or copy .ibd files, the database directory name must be the same on the source and destination systems. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

    当你移动或者复制.ibd文件的时候，源端和目标端的数据库名必须是相同的。表定义存储的InnoDB共享表空间里也包括了数据库名。不同数据库之间transaction IDs and log sequence numbers也是不同的。

    To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

    要把一个.ibd文件和相关的表从一个数据库移动到另一个的时候，可以使用RENAME TABLE语句：

    RENAME TABLE db1.tbl_name TO db2.tbl_name;

    If you have a "clean" backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

    如果你有一个"干净的".ibd文件备份，你可以在下面的MySQL原始安装步骤里恢复它：

    1.The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.

    1.自从你复制了.ibd文件之后表必须是没有drop或者truncate的，因为这些操作会修改存储在表空间里的 table ID

    2.Issue this ALTER TABLE statement to delete the current .ibd file:

    2.执行ALTER TABLE语句来删除当前的.ibd文件：

     

    ALTER TABLE tbl_name DISCARD TABLESPACE;

    3.Copy the backup .ibd file to the proper database directory.

    3.复制备份的.ibd文件到适当的数据库目录。

    4.Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

    4.执行ALTER TABLE语句告诉InnoDB为表使用新的.ibd文件：

     

    ALTER TABLE tbl_name IMPORT TABLESPACE;

    Note

     

    The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data.

    ALTER TABLE ... IMPORT TABLESPACE特性不会在导入的数据上强制实施外键约束。

    In this context, a "clean" .ibd file backup is one for which the following requirements are satisfied:

    在这个环境里，"干净的".ibd文件备份是要满足下面的要求的：

    • There are no uncommitted modifications by transactions in the .ibd file.
    • .ibd文件里没有事务未提交的修改。
    • There are no unmerged insert buffer entries in the .ibd file.
    • .ibd文件里没有未合并的insert buffer条目。
    • Purge has removed all delete-marked index records from the .ibd file.
    • purge操作会从.ibd文件里面移除所有有删除标记的索引记录。
    • mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.
    • mysqld已经从buffer pool里面把.ibd文件相关的修改过的数据页刷新到了文件上。

    You can make a clean backup .ibd file using the following method:

    你可以使用下面的方法创建一个干净的.ibd备份文件：

    1.Stop all activity from the mysqld server and commit all transactions.

    1.在mysqld服务上停止所有的活动，并提交所有的事务。

    2.Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

    2.等到SHOW ENGINE INNODB STATUS显示的数据库里已经没有活动的事务的，InnoDB的主线程状态是Waiting for server activity。这个时候就可以复制.ibd文件了。

    Another method for making a clean copy of an .ibd file is to use the MySQL Enterprise Backup product:

    另一个创建干净的.ibd文件的方法是使用MySQL Enterprise Backup：

    1.Use MySQL Enterprise Backup to back up the InnoDB installation.

    1.使用MySQL Enterprise Backup来备份InnoDB安装。

    2.Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.

    2.在备份上启动第二个mysqld服务，并在备份里面清除.ibd文件。

    Export and Import (mysqldump)

    You can use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.

    你可以使用mysqldump从一台机器上导出表，再在另一台机器上导入这些dump文件。这种方法可以忽略平台的差异。

    One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.

    这种方式下想要增加性能可以在导入的时候关闭autocommit，但是要确保表空间有足够的回滚空间来存放导入的事务。然后在导入完整个表或者表的分段的时候再执行commit。

    14.6.3 Grouping DML Operations with Transactions

    By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. This mode of operation might be unfamiliar if you have experience with other database systems, where it is standard practice to issue a sequence of DML statements and commit them or roll them back all together.

    默认情况下，连接到MySQL服务开始的时候使用的是autocommit模式，这会在你执行的每个SQL语句后面自动进行commit。这种操作模式可能不同于其他的数据库系统，其他数据库的标准惯例是执行完DML语句之后在进行commit或者rollback。

    To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate. To leave autocommit on, begin each transaction with START TRANSACTION and end it with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

    要使用多语句事务，则要使用SET autocommit = 0来关闭autocommit，并在每个事务的尾端执行适当的COMMIT或者ROLLBACK。还有一种方式可以关闭autocommit，在每个事务开始的时候运行START TRANSACTION，在结尾的地方运行COMMIT或者ROLLBACK。下面的例子显示了两个事务，第一个进行了commit，第二个做了rollback。

    shell> mysql test

     

    mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));

    Query OK, 0 rows affected (0.00 sec)

    mysql> -- Do a transaction with autocommit turned on.

    mysql> START TRANSACTION;

    Query OK, 0 rows affected (0.00 sec)

    mysql> INSERT INTO customer VALUES (10, 'Heikki');

    Query OK, 1 row affected (0.00 sec)

    mysql> COMMIT;

    Query OK, 0 rows affected (0.00 sec)

    mysql> -- Do another transaction with autocommit turned off.

    mysql> SET autocommit=0;

    Query OK, 0 rows affected (0.00 sec)

    mysql> INSERT INTO customer VALUES (15, 'John');

    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO customer VALUES (20, 'Paul');

    Query OK, 1 row affected (0.00 sec)

    mysql> DELETE FROM customer WHERE b = 'Heikki';

    Query OK, 1 row affected (0.00 sec)

    mysql> -- Now we undo those last 2 inserts and the delete.

    mysql> ROLLBACK;

    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM customer;

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

    | a | b |

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

    | 10 | Heikki |

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

    1 row in set (0.00 sec)

    mysql>

    Transactions in Client-Side Languages

    In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.

    在一些其他程序的接口如PHP, Perl DBI, JDBC, ODBC, or the standard C上，你可以和其他的SQL语句一样通过发送COMMIT到MySQL服务器来控制事务。一些接口还提供独立的特殊事务的commit和rollback函数。

    14.6.4 Converting Tables from MyISAM to InnoDB

    If you have existing tables, and applications that use them, that you want to convert to InnoDB for better reliability and scalability, use the following guidelines and tips. This section assumes most such tables were originally MyISAM, which was formerly the default.

    如果你已经有一些表，应用程序也都在使用它们，你想把它们转化成InnoDB以获得更好的可靠性和可扩展性，就可以使用下面的知道建议。这一章节假设有很多原来默认的就是MyISAM的表。

    Reduce Memory Usage for MyISAM, Increase Memory Usage for InnoDB

    As you transition away from MyISAM tables, lower the value of the key_buffer_size configuration option to free memory no longer needed for caching results. Increase the value of the innodb_buffer_pool_size configuration option, which performs a similar role of allocating cache memory for InnoDB tables. The InnoDB buffer pool caches both table data and index data, so it does double duty in speeding up lookups for queries and keeping query results in memory for reuse.

    因为MyISAM表是不能使用事务的，那么可以把key_buffer_size配置成一个较小的值来节约更多的内存。增加innodb_buffer_pool_size配置参数的值，让InnoDB可以分配更多的内存。InnoDB buffer pool可以同时缓存表数据和索引数据，所以它有双重的责任来加速查询的速度，并把查询结果缓存在内存里进行重复使用。

    • Allocate as much memory to this option as you can afford, often up to 80% of physical memory on the server.
    • 尽可能多地分配更多的内存，通常会占到整个服务器物理内存的80%。
    • If the operating system runs short of memory for other processes and begins to swap, reduce the innodb_buffer_pool_size value. Swapping is such an expensive operation that it drastically reduces the benefit of the cache memory.
    • 如果操作系统运行了其他程序值剩余很小的内存，并且已经开始使用swap了，那就要减少innodb_buffer_pool_size的值。swapping是一个很昂贵的操作，它会大幅减少内存缓存所带来的好处。
    • If the innodb_buffer_pool_size value is several gigabytes or higher, consider increasing the values of innodb_buffer_pool_instances. Doing so helps on busy servers where many connections are reading data into the cache at the same time.
    • 如果innodb_buffer_pool_size的值有好几个G或者更高，那就要考虑增加innodb_buffer_pool_instances的值。在繁忙的服务器上这样做能够在同一个时间点上让多个连接同时从缓存里都数据。
    • On a busy server, run benchmarks with the Query Cache turned off. The InnoDB buffer pool provides similar benefits, so the Query Cache might be tying up memory unnecessarily.
    • 在繁忙的服务器上，关闭Query Cache来做基准测试。InnoDB buffer pool提供了类似的功能，所以Query Cache可能就不是必须的了。

    Watch Out for Too-Long Or Too-Short Transactions

    Because MyISAM tables do not support transactions, you might not have paid much attention to the autocommit configuration option and the COMMIT and ROLLBACK statements. These keywords are important to allow multiple sessions to read and write InnoDB tables concurrently, providing substantial scalability benefits in write-heavy workloads.

    因为MyISAM表不支持事务，你可能不怎么会关注autocommit的配置以及COMMIT，ROLLBACK语句。但这些关键字对于多session并发读写InnoDB表则是非常重要的，它们能够在写负载高的情况下提供更好的扩展性。

    While a transaction is open, the system keeps a snapshot of the data as seen at the beginning of the transaction, which can cause substantial overhead if the system inserts, updates, and deletes millions of rows while a stray transaction keeps running. Thus, take care to avoid transactions that run for too long:

    当开启了一个事务，系统会保持一个事务开启点时数据的快照，那么如果事务继续在运行，那么系统就要大量地Insert，update和delete成千上万的行数据，并带来大量的系统资源损耗。因此，要避免事务太长：

    • If you are using a mysql session for interactive experiments, always COMMIT (to finalize the changes) or ROLLBACK (to undo the changes) when finished. Close down interactive sessions rather than leaving them open for long periods, to avoid keeping transactions open for long periods by accident.
    • 如果你使用一个mysql session来做交互式实验，并且在结束的时候是需要手动COMMIT或者ROLLBACK的。这种情况相对于长时间开着事务，更好的办法是及时关闭交互式的session，这样能够避免长事务带来的意外。
    • Make sure that any error handlers in your application also ROLLBACK incomplete changes or COMMIT completed changes.
    • 确保应用程序有error handler，这样能够ROLLBACK不完整修改，或者进行COMMIT。
    • ROLLBACK is a relatively expensive operation, because INSERT, UPDATE, and DELETE operations are written to InnoDB tables prior to the COMMIT, with the expectation that most changes will be committed successfully and rollbacks will be rare. When experimenting with large volumes of data, avoid making changes to large numbers of rows and then rolling back those changes.
    • ROLLBACK是一个相当昂贵的操作，因为对于写入到InnoDB表的DML操作优先是进行COMMIT的，我们所期望的大部分的修改是成功提交的，而要回滚的毕竟是少部分。当进行大批量数据尝试的时候，要避免修改大量的行数据并再对其进行回滚。
    • When loading large volumes of data with a sequence of INSERT statements, periodically COMMIT the results to avoid having transactions that last for hours. In typical load operations for data warehousing, if something goes wrong, you TRUNCATE TABLE and start over from the beginning rather than doing a ROLLBACK.
    • 当要顺序执行INSERT语句来加载大量数据的时候，要定期执行COMMIT来避免事务持续得时间太长。在通常的数据仓库进行加载操作的时候，如果发生了错误，相对而言应该是TRUNCATE TABLE然后重新开始，而不是执行ROLLBACK操作。

    The preceding tips save memory and disk space that can be wasted during too-long transactions. When transactions are shorter than they should be, the problem is excessive I/O. With each COMMIT, MySQL makes sure each change is safely recorded to disk, which involves some I/O.

    太长的事务会浪费内存和磁盘的空间。但是当事务太短的时候，过多的I/O也那会是个问题。每个COMMIT语句，MySQL都要确保每个修改都已经安全写入到磁盘上，这就涉及到了很多的I/O操作。

    • For most operations on InnoDB tables, you should use the setting autocommit=0. From an efficiency perspective, this avoids unnecessary I/O when you issue large numbers of consecutive INSERT, UPDATE, or DELETE statements. From a safety perspective, this allows you to issue a ROLLBACK statement to recover lost or garbled data if you make a mistake on the mysql command line, or in an exception handler in your application.
    • 对于大多数在InnoDB表上的操作，你应该设定autocommit=0。从效率的角度来说，当你执行大量连续的DML操作时能够避免不必要的I/O。从安全性来说，如果你执行了一个错误的mysql命令或者在程序中进行了异常处理，那这也能允许你执行ROLLBACK来恢复丢失或者错乱的数据。
    • The time when autocommit=1 is suitable for InnoDB tables is when running a sequence of queries for generating reports or analyzing statistics. In this situation, there is no I/O penalty related to COMMIT or ROLLBACK, and InnoDB can automatically optimize the read-only workload.
    • 当你为生成报表或者分析统计而运行一系列查询的时候，对于InnoDB表来说autocommit=1是更为合适的。在这种情况下，没有关闭COMMIT或者ROLLBACK的I/O处罚，InnoDB也会自动优化只读的环境。
    • If you make a series of related changes, finalize all those changes at once with a single COMMIT at the end. For example, if you insert related pieces of information into several tables, do a single COMMIT after making all the changes. Or if you run many consecutive INSERT statements, do a single COMMIT after all the data is loaded; if you are doing millions of INSERT statements, perhaps split up the huge transaction by issuing a COMMIT every ten thousand or hundred thousand records, so the transaction does not grow too large.
    • 如果你进行了一系列的修改，可以在结尾通过一次COMMIT定形所有的修改。例如，你要插入相关的信息到不同的表上，可以在所有修改完成之后执行一个单独的COMMIT。又或者你要运行很多连续的INSERT语句，那可以在所有的数据加载完之后统一进行COMMIT；如果你要执行上百万的INSERT，那可以把这个大事务进行分割，对每一万个或者十万个进行分批COMMIT，这样事务就不会增长得过大。
    • Remember that even a SELECT statement opens a transaction, so after running some report or debugging queries in an interactive mysql session, either issue a COMMIT or close the mysql session.
    • 记住即使是只在事务里打开一个SELECT语句，在mysql交互式session里运行完一些报表查询之后，也要执行COMMIT或者是关闭mysql session。

    Don't Worry Too Much About Deadlocks

    You might see warning messages referring to "deadlocks" in the MySQL error log, or the output of SHOW ENGINE INNODB STATUS. Despite the scary-sounding name, a deadlock is not a serious issue for InnoDB tables, and often does not require any corrective action. When two transactions start modifying multiple tables, accessing the tables in a different order, they can reach a state where each transaction is waiting for the other and neither can proceed. MySQL immediately detects this condition and cancels (rolls back) the "smaller" transaction, allowing the other to proceed.

    你可能会在MySQL的error log或者是SHOW ENGINE INNODB STATUS的输出里看到相关"deadlocks(死锁)"的警告信息。除去不怎么好的名字，死锁对于InnoDB表来说并不是什么严重的问题，也不需要什么补救的动作。当两个事务以不同的顺序访问修改多个表的时候，那就会发生相互等待对方的情况。MySQL会立即发现这种情况，并取消（回滚）较小的那个事务，以便另一个操作能够继续。

    Your applications do need error-handling logic to restart a transaction that is forcibly cancelled like this. When you re-issue the same SQL statements as before, the original timing issue no longer applies: either the other transaction has already finished and yours can proceed, or the other transaction is still in progress and your transaction waits until it finishes.

    你的应用程序要有错误处理逻辑来重启事务以便强制取消。当你再执行和之前相同的SQL语句的时候，原来事务的时间戳将不再适用：要么是其他的事务已经结束而你的仍然在继续，要么是其他的事务仍然在处理而你的事务则要等到其结束。

    If deadlock warnings occur constantly, you might review the application code to reorder the SQL operations in a consistent way, or to shorten the transactions. You can test with the innodb_print_all_deadlocks option enabled to see all deadlock warnings in the MySQL error log, rather than only the last warning in the SHOW ENGINE INNODB STATUS output.

    如果不断地发生死锁的警告，你需要review应用程序的代码以一致的方式重新排序SQL的操作，又或者再减短事务。你可以通过开启innodb_print_all_deadlocks参数进行测试来查看MySQL的error log里面所有的死锁警告（SHOW ENGINE INNODB STATUS的输出里只有最后一次的警告）。

    Plan the Storage Layout

    To get the best performance from InnoDB tables, you can adjust a number of parameters related to storage layout.

    InnoDB表要等到最佳的性能，你可以调整一系列存储布局相关的参数。

    When you convert MyISAM tables that are large, frequently accessed, and hold vital data, investigate and consider the innodb_file_per_table, innodb_file_format, and innodb_page_size configuration options, and the ROW_FORMAT and KEY_BLOCK_SIZE clauses of the CREATE TABLE statement.

    当你要转换的MyISAM表非常大，而且还要频繁访问，又是至关重要的时候，那就要调查考虑这几个参数：innodb_file_per_table, innodb_file_format, and innodb_page_size，还有在CREATE TABLE语句中的ROW_FORMAT and KEY_BLOCK_SIZE子句。

    During your initial experiments, the most important setting is innodb_file_per_table. When this setting is enabled, which is the default as of MySQL 5.6.6, new InnoDB tables are implicitly created in file-per-table tablespaces. In contrast with the InnoDB system tablespace, file-per-table tablespaces allow disk space to be reclaimed by the operating system when a table is truncated or dropped. File-per-table tablespaces also support the Barracuda file format and associated features such as table compression and off-page storage for long variable-length columns. For more information, see Section 14.5.4, "InnoDB File-Per-Table Tablespaces".

    开始的实验证明innodb_file_per_table是最为重要的参数。当这个参数开启（从MySQL5.6.6开始默认是开启的）的时候，新的InnoDB表会隐式地创建在file-per-table表空间里。相对于InnoDB的系统表空间，当表被truncate或者drop的时候file-per-table表空间允许磁盘空间能被操作系统进行回收利用。file-per-table表空间还支持Barracuda文件格式以及其相关的特性，例如表压缩，以及off-page storage for long variable-length columns。更多信息可以查看Section 14.5.4, "InnoDB File-Per-Table Tablespaces"。

    Converting an Existing Table

    To convert a non-InnoDB table to use InnoDB use ALTER TABLE:

    使用ALTER TABLE把非InnoDB表转成InnoDB表：

    ALTER TABLE table_name ENGINE=InnoDB;

    Important

     

    Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.

    不要把在mysql数据库里的MySQL系统表转化成InnoDB的类型。这个操作是不支持的。系统表必须要是MyISAM类型的。

    Cloning the Structure of a Table

    You might make an InnoDB table that is a clone of a MyISAM table, rather than doing the ALTER TABLE conversion, to test the old and new table side-by-side before switching.

    你可以把一个MyISAM表克隆成InnoDB表，而不用执行ALTER TABLE来进行转换，这样可以在切换之前对新旧表分别进行测试。

    Create an empty InnoDB table with identical column and index definitions. Use show create table table_nameG to see the full CREATE TABLE statement to use. Change the ENGINE clause to ENGINE=INNODB.

    创建一个空的有相同列和索引定义的InnoDB表。使用create table table_nameG可以看到完整的CREATE TABLE语句来使用，只要把ENGINE子句改成ENGINE=INNODB。

    Transferring Existing Data

    To transfer a large volume of data into an empty InnoDB table created as shown in the previous section, insert the rows with INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.

    要把大量的数据传输到上文创建的空InnoDB表里，可以通过INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns来插入数据。

    You can also create the indexes for the InnoDB table after inserting the data. Historically, creating new secondary indexes was a slow operation for InnoDB, but now you can create the indexes after the data is loaded with relatively little overhead from the index creation step.

    你还可以在插入数据之后为InnoDB表创建索引。以前，对于InnoDB来说创建一个新的secondary index是一个非常慢的操作，但现在在加载完数据之后创建索引只要很少的成本开销。

    If you have UNIQUE constraints on secondary keys, you can speed up a table import by turning off the uniqueness checks temporarily during the import operation:

    如果你在secondary index上有UNIQUE约束，那你可以在导入操作的时候临时关闭唯一检查来加快导入的速度：

    SET unique_checks=0;

    ... import operation ...

    SET unique_checks=1;

    For big tables, this saves disk I/O because InnoDB can use its change buffer to write secondary index records as a batch. Be certain that the data contains no duplicate keys. unique_checks permits but does not require storage engines to ignore duplicate keys.

    对于大表来说，这能够减少磁盘的I/O，因为InnoDB可以用它自己的change buffer来批量写secondary index记录。当然你需要自己去确认导入的没有重复的key，unique_checks会允许你插入重复key但不会要求存储引擎去忽略重复的key。

    To get better control over the insertion process, you might insert big tables in pieces:

    为了更好地控制插入操作，你可以分片插入大表：

    INSERT INTO newtable SELECT * FROM oldtable

    WHERE yourkey > something AND yourkey <= somethingelse;

    After all records have been inserted, you can rename the tables.

    在所有的记录都被插入之后，那你就可以重命名表了。

    During the conversion of big tables, increase the size of the InnoDB buffer pool to reduce disk I/O, to a maximum of 80% of physical memory. You can also increase the sizes of the InnoDB log files.

    在转换大表的过程中，适当增加InnoDB buffer pool的值能够减少磁盘的I/O，最大可以增加到物理内存的80%。同样你也可以增加InnoDB日志文件的大小。

    Storage Requirements

    If you intend to make several temporary copies of your data in InnoDB tables during the conversion process, it is recommended that you create the tables in file-per-table tablespaces so that you can reclaim the disk space when you drop the tables. As mentioned previously, when the innodb_file_per_table option is enabled, newly created InnoDB tables are implicitly created in file-per-table tablespaces.

    如果你想要在转换的过程中多做几个临时拷贝，那就要求你把表建在file-per-table的表空间里，这样当你删除表的时候就能再利用磁盘空间。就如前面是所说的，当开启了innodb_file_per_table，最新创建的InnoDB表会隐式地创建在file-per-table表空间里。

    Whether you convert the MyISAM table directly or create a cloned InnoDB table, make sure that you have sufficient disk space to hold both the old and new tables during the process. InnoDB tables require more disk space than MyISAM tables. If an ALTER TABLE operation runs out of space, it starts a rollback, and that can take hours if it is disk-bound. For inserts, InnoDB uses the insert buffer to merge secondary index records to indexes in batches. That saves a lot of disk I/O. For rollback, no such mechanism is used, and the rollback can take 30 times longer than the insertion.

    无论你是直接转换MyISAM表还是创建了一个克隆的InnoDB表，都要确保有足够的磁盘空间在处理过程中能够同时保存新旧表的数据。相对于MyISAM表，InnoDB表会要求更多的磁盘空间。如果ALTER TABLE运行的时候磁盘空间不够了，那么实例就会进行回滚，那可能就会要花费数个小时。对于插入操作，InnoDB会使用insert buffer来批量合并secondary index记录，这样就能节省很多的磁盘I/O。For rollback, no such mechanism is used, and the rollback can take 30 times longer than the insertion.

    In the case of a runaway rollback, if you do not have valuable data in your database, it may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see Section 14.19.2, "Forcing InnoDB Recovery".

    对于这种失控回滚的情况，如果数据库中的数据不是太重要，那么比较明智的做法是kill掉数据库的操作线程，而不是等待上百万的磁盘I/O操作完成。整个完整的过程可以查看Section 14.19.2, "Forcing InnoDB Recovery"。

    Carefully Choose a PRIMARY KEY for Each Table

    The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. Perhaps you have phoned a financial institution where you are asked for an account number. If you do not have the number, you are asked for a dozen different pieces of information to "uniquely identify" yourself. The primary key is like that unique account number that lets you get straight down to business when querying or modifying the information in a table. Every row in the table must have a primary key value, and no two rows can have the same primary key value.

    主键是影响MySQL查询性能以及表和索引使用空间的关键要素。假设你打电话给金融机构要求一个帐号号码。如果你没有号码，那你就需要一达不同的信息来"唯一标识"你自己。主键就像那个唯一的帐号号码，当你查询或者修改一个的表的信息的时候可以立即找到。表里的每一行记录都要有一个主键值，而且不会有两行记录有相同主键值的情况。

    Here are some guidelines for the primary key, followed by more detailed explanations.

    这里是一些针对于主键的指导方针：

    • Declare a PRIMARY KEY for each table. Typically, it is the most important column that you refer to in WHERE clauses when looking up a single row.
    • 为每个表都定义一个主键。通常，是那种涉及WHERE语句中最重要的列，可以让你定位到单独的一行记录。
    • Declare the PRIMARY KEY clause in the original CREATE TABLE statement, rather than adding it later through an ALTER TABLE statement.
    • 在CREATE TABLE的时候就定义好主键，而不是之后通过ALTER TABLE语句来添加主键。
    • Choose the column and its data type carefully. Prefer numeric columns over character or string ones.
    • 小心选择列和它的数据类型。相对于字符或者字符串，数值型的更为合适。
    • Consider using an auto-increment column if there is not another stable, unique, non-null, numeric column to use.
    • 如果没有适当的唯一，非空，数值型的列使用，那么可以考虑使用一个自增长的列。
    • An auto-increment column is also a good choice if there is any doubt whether the value of the primary key column could ever change. Changing the value of a primary key column is an expensive operation, possibly involving rearranging data within the table and within each secondary index.
    • 如果怀疑主键首选列的值有可能会变化的话，那么自增长列也是一个不错的。修改主键列的值是一个非常昂贵的操作，这其中涉及到重排表中的数据以及每一个secondary index。

    Consider adding a primary key to any table that does not already have one. Use the smallest practical numeric type based on the maximum projected size of the table. This can make each row slightly more compact, which can yield substantial space savings for large tables. The space savings are multiplied if the table has any secondary indexes, because the primary key value is repeated in each secondary index entry. In addition to reducing data size on disk, a small primary key also lets more data fit into the buffer pool, speeding up all kinds of operations and improving concurrency.

    为没有主键的表考虑增加一个主键。在大表上可以使用最小的实际数值类型的列来做主键。这样可以使得每一行更为紧凑，在大表上可以节约大量的空间。如果表有很多的secondary index，那这节约的空间都是成倍的，因为主键值是要重叠在每个secondary index条目上的。另外这还能减少磁盘上的数据大小。小的主键能够让buffer pool装载更多的数据，这样也就加快各种类型的操作，提升了并发性。

    If the table already has a primary key on some longer column, such as a VARCHAR, consider adding a new unsigned AUTO_INCREMENT column and switching the primary key to that, even if that column is not referenced in queries. This design change can produce substantial space savings in the secondary indexes. You can designate the former primary key columns as UNIQUE NOT NULL to enforce the same constraints as the PRIMARY KEY clause, that is, to prevent duplicate or null values across all those columns.

    如果表已经在一个比较长的列上有主键了，例如VARCHAR类型的列，那么可以考虑增加一个自增长的列并为主键，即使这个在查询中不会被引用。这样设计的修改能够为secondary index节省大量的空间。你可以把原来设计的主键列改成UNIQUE NOT NULL来强制其的值和主键有相同的约束，这样也就能够阻止重复值或者空值写入了。

    If you spread related information across multiple tables, typically each table uses the same column for its primary key. For example, a personnel database might have several tables, each with a primary key of employee number. A sales database might have some tables with a primary key of customer number, and other tables with a primary key of order number. Because lookups using the primary key are very fast, you can construct efficient join queries for such tables.

    如果你要在多个表之间传播相关的信息，通常都会使用相同的列来作为主键。例如，personnel数据库有很多个表，而且每个表都有一个员工号来作为主键。sales 数据库里部分使用客户号作为主键，其他的表用订单号来作为主键。因为通过主键的查询是非常快速的，所以你可以使用这些列来构造高效的join查询。

    If you leave the PRIMARY KEY clause out entirely, MySQL creates an invisible one for you. It is a 6-byte value that might be longer than you need, thus wasting space. Because it is hidden, you cannot refer to it in queries.

    如果你没有显式指定主键，那么MySQL会为你创建一个隐藏的主键。那是一个6-byte的值，远大于你所需要的，因此会浪费大量的空间。因为是隐藏的，所以你还不能在查询中使用它。

    Application Performance Considerations

    The extra reliability and scalability features of InnoDB do require more disk storage than equivalent MyISAM tables. You might change the column and index definitions slightly, for better space utilization, reduced I/O and memory consumption when processing result sets, and better query optimization plans making efficient use of index lookups.

    InnoDB额外的可靠性和可扩展性会比MyISAM要求更多的磁盘空间。你可以稍微修改列和索引的定义，这样在处理结果集的时候能得到更好空间利用率，减少I/O和内存的消耗，也能让执行计划更有效地使用索引。

    If you do set up a numeric ID column for the primary key, use that value to cross-reference with related values in any other tables, particularly for join queries. For example, rather than accepting a country name as input and doing queries searching for the same name, do one lookup to determine the country ID, then do other queries (or a single join query) to look up relevant information across several tables. Rather than storing a customer or catalog item number as a string of digits, potentially using up several bytes, convert it to a numeric ID for storing and querying. A 4-byte unsigned INT column can index over 4 billion items (with the US meaning of billion: 1000 million). For the ranges of the different integer types, see Section 11.2.1, "Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT".

    如果你建立一个数字ID列作为主键，那么就用这列作为和其他表进行join的join列。例如，相对于使用country name来进行join，使用country ID来作为join条件更好。字符串类型的数字，会消耗更多的空间，更好的办法是使用数值的的数字。一个4-byte unsigned INT列最大能够到4294967295。对于不同整数类型的范围，可以查看Section 11.2.1, "Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"。

    Understand Files Associated with InnoDB Tables

    InnoDB files require more care and planning than MyISAM files do:

    相对于MyISAM文件，InnoDB的文件需要更多的关注和规划：

    • You must not delete the ibdata files that represent the InnoDB system tablespace.
    • 你不能删除代表InnoDB系统表空间的ibdata文件。
    • Copying InnoDB tables from one server to another requires issuing the FLUSH TABLES ... FOR EXPORT statement first, and copying the table_name.cfg file along with the table_name.ibd file.
    • 从一个实例上复制InnoDB表到另一个实例上需要首先执行FLUSH TABLES ... FOR EXPORT，然后再拷贝table_name.cfg和table_name.ibd文件。

    14.6.5 AUTO_INCREMENT Handling in InnoDB

    InnoDB provides a configurable locking mechanism that can significantly improve scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column 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.

    在添加新的数据行到AUTO_INCREMENT列的时候，InnoDB提供了一种可配置的锁机制，可以显著改善SQL语句的可扩展性和性能。要在InnoDB表上使用AUTO_INCREMENT的机制，AUTO_INCREMENT列必须要被定义成索引的一部分，这样就能够执行等价于SELECT MAX(ai_col)的查询来查找这列的最大值。通常，还要把这列作为索引的第一个列（前缀列）。

    This section describes the behavior of AUTO_INCREMENT lock modes, usage implications for different AUTO_INCREMENT lock mode settings, and how InnoDB initializes the AUTO_INCREMENT counter.

    这章讲述了AUTO_INCREMENT行为的锁模式，不同AUTO_INCREMENT锁模式设定的使用影响，以及InnoDB如何初始化AUTO_INCREMENT计数器。

    InnoDB AUTO_INCREMENT Lock Modes

    This section describes the behavior of AUTO_INCREMENT lock modes used to generate auto-increment values, and how each lock mode affects replication. Auto-increment lock modes are configured at startup using the innodb_autoinc_lock_mode configuration parameter.

    这一章节讲述了AUTO_INCREMENT的锁模式，以及每个锁模式是如何影响主从复制的。AUTO_INCREMENT的锁模式可以在实例启动的时候通过innodb_autoinc_lock_mode配置参数来设定。

    The following terms are used in describing innodb_autoinc_lock_mode settings:

    下面的内容描述了innodb_autoinc_lock_mode的设定：

    • "INSERT-like" statements

    All statements that generate new rows in a table, including INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA. Includes "simple-inserts", "bulk-inserts", and "mixed-mode" inserts.

    生成新数据行的所有语句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。也包括"simple-inserts", "bulk-inserts", and "mixed-mode"。

    • "Simple inserts"

    Statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT ... ON DUPLICATE KEY UPDATE.

    语句要插入的行数可以提前确认（语句预先处理）。这包括了单行或者多行的INSERT，以及没有嵌套子查询的REPLACE 语句，但是不包括INSERT ... ON DUPLICATE KEY UPDATE。

    • "Bulk inserts"

    Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT. InnoDB will assign new values for the AUTO_INCREMENT column one at a time as each row is processed.

    语句要插入的行数（以及要自增长数的数量）预先是未知的。这包括了INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA ，但是不包括普通的INSERT。InnoDB会为AUTO_INCREMENT列每一行处理一次地分配新值。

    • "Mixed-mode inserts"

    These are "simple insert" statements that specify the auto-increment value for some (but not all) of the new rows. An example follows, where c1 is an AUTO_INCREMENT column of table t1:

    这是为新的行（但不是所有）指定自增长值的"simple insert"语句。例如下面的例子，c1是t1表的AUTO_INCREMENT列：

    INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

    Another type of "mixed-mode insert" is INSERT ... ON DUPLICATE KEY UPDATE, which in the worst case is in effect an INSERT followed by a UPDATE, where the allocated value for the AUTO_INCREMENT column may or may not be used during the update phase.

    INSERT ... ON DUPLICATE KEY UPDATE是另一种类型的"mixed-mode insert"，实际上最坏的情况是INSERT后面紧跟着UPDATE，这样为AUTO_INCREMENT分配的值可能在update阶段无法使用。

    There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for "traditional", "consecutive", or "interleaved" lock mode, respectively.

    innodb_autoinc_lock_mode配置参数有三种可能的设定，分别是0, 1, or 2，分别对应"traditional（传统的）", "consecutive（连续的）", or "interleaved（交错的）"锁模式。

    • innodb_autoinc_lock_mode = 0 ("traditional" lock mode)

    The traditional lock mode provides the same behavior that existed before the innodb_autoinc_lock_mode configuration parameter was introduced in MySQL 5.1. The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with "mixed-mode inserts", due to possible differences in semantics.

    传统的锁模式提供了自MySQL5.1引进innodb_autoinc_lock_mode之前已存在相同的行为。传统锁模式主要用于向后兼容，性能测试，以及由于可能的有语义问题的"mixed-mode inserts"。

    In this lock mode, all "INSERT-like" statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive.

    在这种锁模式下，所有的"INSERT-like"语句都包含了一个特别的表级别的AUTO-INC锁，用于往AUTO_INCREMENT列插入数据。这个锁通常会保持到语句结束（不是事务结束）来确保分配的自增长的值是可预测的，以及对于给定顺序的INSERT语句的顺序是可重现的，还有要确保对于给定语句分配的自增长值是连续的。

    In the case of statement-based replication, this means that when an SQL statement is replicated on a slave server, the same values are used for the auto-increment column as on the master server. The result of execution of multiple INSERT statements is deterministic, and the slave reproduces the same data as on the master. If auto-increment values generated by multiple INSERT statements were interleaved, the result of two concurrent INSERT statements would be nondeterministic, and could not reliably be propagated to a slave server using statement-based replication.

    在基于语句(statement-based)的主从复制情况下，这也就意味着当一个SQL语句重复到slave端的时候，自增长列的值会和master端的相同。多个INSERT语句的执行结果是确定的，slave端产生的值会和master端相同。如果多ISNERT语句生成的自增长值是交错的，两个并发的INSERT语句产生的结果是不确定的，那就不能使用基于语句的主从复制把结果可靠地传播到slave端上。

    To make this clear, consider an example that uses this table:

    为了更清楚说明情况，可以查看下面的例子：

    CREATE TABLE t1 (

    c1 INT(11) NOT NULL AUTO_INCREMENT,

    c2 VARCHAR(10) DEFAULT NULL,

    PRIMARY KEY (c1)

    ) ENGINE=InnoDB;

    Suppose that there are two transactions running, each inserting rows into a table with an AUTO_INCREMENT column. One transaction is using an INSERT ... SELECT statement that inserts 1000 rows, and another is using a simple INSERT statement that inserts one row:

    假设有两个事务正在运行，每个都往表里插入自增长值。一个事务使用INSERT ... SELECT插入1000行记录，另一个使用简单的INSERT语句插入一行记录：

    Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

    Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

    InnoDB cannot tell in advance how many rows will be retrieved from the SELECT in the INSERT statement in Tx1, and it assigns the auto-increment values one at a time as the statement proceeds. With a table-level lock, held to the end of the statement, only one INSERT statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved. The auto-increment value generated by the Tx1 INSERT ... SELECT statement will be consecutive, and the (single) auto-increment value used by the INSERT statement in Tx2 will either be smaller or larger than all those used for Tx1, depending on which statement executes first.

    在Tx1里面InnoDB不会预先告诉你INSERT语句里面的SELECT会检索多少行的记录，它会在语句处理过程中每次一个分配自增长值。这个表级的锁会保持到语句结束，一次执行只执行一个INSERT语句，那么不同的语句产生的自增长值就不会是交错的。Tx1 INSERT ... SELECT产生的自增长值将会是连续的，而Tx2的自增长值只会小于或者大于Tx1生产的值，只取决于哪个语句先实行。

    As long as the SQL statements execute in the same order when replayed from the binary log (when using statement-based replication, or in recovery scenarios), the results will be the same as they were when Tx1 and Tx2 first ran. Thus, table-level locks held until the end of a statement make INSERT statements using auto-increment safe for use with statement-based replication. However, those table-level locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time.

    在从binary log重现的时候（使用基于语句的主从复制，或者恢复场景中）只要SQL语句是以相同的顺序执行的，那么结果集合Tx1 and Tx2运行的顺序一样。因此，保持到语句结束的表级锁使得在基于主从复制中INSERT语句使用自增长更为安全。然而，当多个事务在同一个时间点指定insert的时候，这些表级锁也限制了并发性和可扩展性。

    In the preceding example, if there were no table-level lock, the value of the auto-increment column used for the INSERT in Tx2 depends on precisely when the statement executes. If the INSERT of Tx2 executes while the INSERT of Tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two INSERT statements are nondeterministic, and may vary from run to run.

    在上面的例子里，如果没有表级锁，那么Tx2里的INSERT的自增长值就要取决于语句执行的准确时间。如果Tx2的INSERT执行的时候Tx1的INSERT正在运行（不是在它开始之前或者完成之后），这两个INSERT语句分配的自增长值将会是非确定性的，几次运行之间也可能会不同。

    Under the consecutive lock mode, InnoDB can avoid using table-level AUTO-INC locks for "simple insert" statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication.

    在连续锁(consecutive )模式下面，InnoDB会避免对"simple insert"语句使用表级别的AUTO-INC锁，因为它们产生的新行数是可预期的，但对于基于语句的主从复制还是会保持执行语句的确定性和安全性。

    If you are not using the binary log to replay SQL statements as part of recovery or replication, the interleaved lock mode can be used to eliminate all use of table-level AUTO-INC locks for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved.

    如果你没有使用使用binary log重现SQL来作为恢复或者主从复制的一部分，交错的（interleaved ）锁模式可以用于消除所有的表级AUTO-INC锁，并以此获得更高的并发性和性能。这种情况会允许在语句生成的自增长值上有间隙，因为并发执行的语句可能是交错执行的。

    • innodb_autoinc_lock_mode = 1 ("consecutive" lock mode)

    This is the default lock mode. In this mode, "bulk inserts" use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.

    这是默认的锁模式。在这种模式下，"bulk inserts"会使用特殊的AUTO-INC表级锁，并保持到语句执行结束。这适用于所有的INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA语句。同一个时间点上只有一个语句持有AUTO-INC锁来执行。

    "Simple inserts" (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes. No table-level AUTO-INC lock is used unless an AUTO-INC lock is held by another transaction. If another transaction holds an AUTO-INC lock, a "simple insert" waits for the AUTO-INC lock, as if it were a "bulk insert".

    "Simple inserts"（插入行的数量是可预期的）通过在一个mutex的控制下或者要求的自增长值的数量来避免表级别的AUTO-INC锁。这个mutex只会才分配的过程中持有，不需要等到语句结束。除非另一个事务持有了一个 AUTO-INC锁，那么InnoDB将不会使用表级别的 AUTO-INC锁。如果另一个事务持有了 AUTO-INC锁，"simple insert"将会等待AUTO-INC锁，就如"bulk insert"一样。

    This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any "INSERT-like" statement are consecutive, and operations are safe for statement-based replication.

    这种模式会确保在面对不能预知插入行的数量（语句分配自增长值的数量）的INSERT的语句的时候，任何"INSERT-like"语句分配的 所有自增长值都是连续的，对于基于语句复制的主从复制的操作是安全的。

    Simply put, this lock mode significantly improves scalability while being safe for use with statement-based replication. Further, as with "traditional" lock mode, auto-increment numbers assigned by any given statement are consecutive. There is no change in semantics compared to "traditional" mode for any statement that uses auto-increment, with one important exception.

    简而言之，对于使用基于语句的主从复制环境，这种锁模式不仅能够保证安全性，还能显著改善可扩展性。更进一步地说，和"traditional"锁模式一样，任何语句分配的自增长值都是连续的。对于任何使用自增长的语句来说，这和"traditional"模式在语义上是没有改变的，只有有一个重要的例外。

    The exception is for "mixed-mode inserts", where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row "simple insert". For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. "Excess" numbers are lost.

    这个例外是针对"mixed-mode inserts"的。这种情况下用户会为自增长列提供一些明确的值，但又不是全部的。对于这样insert语句，InnoDB会分配比插入行数更多的自增长值。可是，所有自动分配的值都是连续产生的，因此要比由最近上次执行的语句生成的自增长值要多。"超出"的数值将会被丢弃。

    • innodb_autoinc_lock_mode = 2 ("interleaved" lock mode)

    In this lock mode, no "INSERT-like" statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.

    在这种锁模式下，没有"INSERT-like"语句会使用表级别的AUTO-INC锁，同一个时间点上也能执行多个语句。这是最快的，也是最易升级的锁模式，但是在使用基于语句的主从复制或者恢复场景中从binary log进行基于语句的重现将会是不安全的。

    In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing "INSERT-like" statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive.

    在这种锁模式下，并发执行的"INSERT-like"语句产生的自增长值是唯一单调递增的。然而，因为在同一个时间上会有多个语句生成值（也就是说多个语句分配的值是交错的），那语句插入行生成的值就可能会是不连续的。

    If the only statements executing are "simple inserts" where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for "mixed-mode inserts". However, when "bulk inserts" are executed, there may be gaps in the auto-increment values assigned by any given statement.

    如果只会执行"simple inserts"（插入行的数量预先可知的）语句，除非是mixed-mode inserts"，那么对于单个语句生成的数值是不会有间隙的。然而，当执行了"bulk inserts"，那么语句分配的自增长值就可能会有间隙了。

    InnoDB AUTO_INCREMENT Lock Mode Usage Implications

    • Using auto-increment with replication
    • 主从复制中使用自增长

    If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the master and its slaves. Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 ("interleaved") or configurations where the master and slaves do not use the same lock mode.

    如果你使用基于语句的主从复制，innodb_autoinc_lock_mode的值为0或1可以让主从端有相同的值。如果innodb_autoinc_lock_mode = 2 ("interleaved")或者主从端使用了不同的锁模式的时候，则就不能确保主从端的自增长值相同。

    If you are using row-based or mixed-format replication, all of the auto-increment lock modes are safe, since row-based replication is not sensitive to the order of execution of the SQL statements (and the mixed format uses row-based replication for any statements that are unsafe for statement-based replication).

    如果你使用基于行(row-based)或者混合模式(mixed-format)的主从复制，所有的自增长锁模式都是安全的，因为基于行的主从复制(row-based replication)不受SQL语句执行顺序的影响（对于基于语句的主从复制中不安全的语句，混合模式会使用基于行的主从复制）。

    • "Lost" auto-increment values and sequence gaps
    • "丢失"自增长值和序列空位

    In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are "lost". Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the "INSERT-like" statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

    在所有的锁模式中(0, 1, and 2)，如果一个事务里产生了自增长值，但这个事务回滚了，那么这些自增长值也就"丢失"了。一旦自增长列生成了值，那这个值是不能被回滚掉的，不论这个"INSERT-like"语句是否完成，也不会管这个事务时不会被会滚了。这些丢失的值是补充重新利用的，因此，表的自增长列上存储的值是有可能有间隙的。

    • Specifying NULL or 0 for the AUTO_INCREMENT column
    • 为AUTO_INCREMENT列指定NULL或者0

    In all lock modes (0, 1, and 2), if a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it.

    在所有的锁模式里(0, 1, and 2)，如果在INSERT里面为AUTO_INCREMENT列指定NULL或者0，InnoDB就会像没指定值一样生成新值。

    • Assigning a negative value to the AUTO_INCREMENT column
    • 为AUTO_INCREMENT分配一个负值

    In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if you assign a negative value to the AUTO_INCREMENT column.

    在所有的锁模式里(0, 1, and 2)，如果你为AUTO_INCREMENT列分配一个负值，那么自增长机制就不会工作（不会分配新值）。

    • If the AUTO_INCREMENT value becomes larger than the maximum integer for the specified integer type
    • 如果AUTO_INCREMENT值开始大于指定的整数类型的最大值

    In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if the value becomes larger than the maximum integer that can be stored in the specified integer type.

    在所有的锁模式里(0, 1, and 2)，如果自增长值开始大于定义的整数类型的最大值，那么自增长机制就不工作了。

    • Gaps in auto-increment values for "bulk inserts"
    • "bulk inserts"中自增长值的间隙

    With innodb_autoinc_lock_mode set to 0 ("traditional") or 1 ("consecutive"), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

    当innodb_autoinc_lock_mode被设置成0 ("traditional") or 1 ("consecutive")的时候，任何语句产生的自增长值都会是连续的，没有间隙，因为表级别的AUTO-INC锁会被持有到语句执行结束，但同一时间点也就只能有一个语句能被执行。

    With innodb_autoinc_lock_mode set to 2 ("interleaved"), there may be gaps in the auto-increment values generated by "bulk inserts," but only if there are concurrently executing "INSERT-like" statements.

    当innodb_autoinc_lock_mode被设置成2 ("interleaved")，那么"bulk inserts,"就有可能产生有间隙的自增长值，但也只有在并发执行"INSERT-like"语句时候才会发生。

    For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

    对于1 or 2的锁模式来说，连续的语句是可能发生间隙的，因为批量插入的每个语句要求的确切的自增长值的数量是不可知的，只能进行过量估计。

    • Auto-increment values assigned by "mixed-mode inserts"
    • "mixed-mode inserts"分配的自增长值

    Consider a "mixed-mode insert," where a "simple insert" specifies the auto-increment value for some (but not all) resulting rows. Such a statement will behave differently in lock modes 0, 1, and 2. For example, assume c1 is an AUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100.

    要考虑一种"mixed-mode insert"的情况：其中一个"simple insert"语句为在结果集中部分指定了自增长列的确切的值，但又不是全部的。这种情况完全不同于0, 1, and 2的锁模式。例如，假设t1表有自增长列c1，最近自动生成的自增长序号是100。

    mysql> CREATE TABLE t1 (

    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    -> c2 CHAR(1)

    -> ) ENGINE = INNODB;

     

    mysql> INSERT INTO t1 VALUES(1,'a'),(101,'b'),(5,'c'),(102,'d');

    Now, consider the following "mixed-mode insert" statement:

    现在考虑一下下面的"mixed-mode insert"语句：

    mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

    With innodb_autoinc_lock_mode set to 0 ("traditional"), the four new rows will be:

    当innodb_autoinc_lock_mode=0("traditional")的时候，四行新记录将会是：

    mysql> SELECT c1, c2 FROM t1 ORDER BY c2;

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

    | c1 | c2 |

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

    | 1 | a |

    | 101 | b |

    | 5 | c |

    | 102 | d |

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

    The next available auto-increment value will be 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing "INSERT-like" statements (of any type).

    下一个自增长值将是103，因为这些自增长值是每次分配一个的，并不是所有的都在语句开始执行的时候分配的。不论是否并发执行了"INSERT-like"语句结果都是这样的。

    With innodb_autoinc_lock_mode set to 1 ("consecutive"), the four new rows will also be:

    当innodb_autoinc_lock_mode=1("consecutive")的时候，新的四行记录也是一样的：

    mysql> SELECT c1, c2 FROM t1 ORDER BY c2;

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

    | c1 | c2 |

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

    | 1 | a |

    | 101 | b |

    | 5 | c |

    | 102 | d |

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

    However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing "INSERT-like" statements (of any type).

    然而，在这种情况下，下一个自增长值将会是105，而不是103，因为在语句处理的时候已经分配了四个自增长值，但只是用了两个。不论是否并发执行了"INSERT-like"语句结果都是这样的。

    With innodb_autoinc_lock_mode set to mode 2 ("interleaved"), the four new rows will be:

    当innodb_autoinc_lock_mode=2("interleaved")，新的四行记录将会是：

    mysql> SELECT c1, c2 FROM t1 ORDER BY c2;

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

    | c1 | c2 |

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

    | 1 | a |

    | x | b |

    | 5 | c |

    | y | d |

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

    The values of x and y will be unique and larger than any previously generated rows. However, the specific values of x and y will depend on the number of auto-increment values generated by concurrently executing statements.

    这里面的x,y都是唯一的，而且会比之前生成的要大。可是，这些特定的x,y也是要依赖于来并发执行语句生成自增长值的数量的。

    Finally, consider the following statement, issued when the most-recently generated sequence number was the value 4:

    最后，考虑一下下面的语句，这句执行的时候最近生成的序号数是4：

    mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

    With any innodb_autoinc_lock_mode setting, this statement will generate a duplicate-key error 23000 (Can't write; duplicate key in table) because 5 will be allocated for the row (NULL, 'b') and insertion of the row (5, 'c') will fail.

    无论innodb_autoinc_lock_mode的设置是什么，这个语句都会报重复键error 23000 的错误，因为(NULL, 'b')会分配到5，而 (5, 'c') 的插入就会失败。

    • Modifying AUTO_INCREMENT column values in the middle of a sequence of INSERT statements
    • 在一些列INSERT语句的中间修改AUTO_INCREMENT列的值

    In all lock modes (0, 1, and 2), modifying an AUTO_INCREMENT column value in the middle of a sequence of INSERT statements could lead to "Duplicate entry" errors. For example, if you perform an UPDATE operation that changes an AUTO_INCREMENT column value to a value larger than the current maximum auto-increment value, subsequent INSERT operations that do not specify an unused auto-increment value could encounter "Duplicate entry" errors. This behavior is demonstrated in the following example.

    在所有的锁模式中(0, 1, and 2)，在一系列INSERT语句中间修改AUTO_INCREMENT列的值将会导致"Duplicate entry"错误。例如，如果你执行了一个UPDATE操作把AUTO_INCREMENT列的值修改的超过当前自增长的最大值了，那么随后没有指定自增长值的INSERT操作就会遭遇到"Duplicate entry"错误。下面的例子演示了这种情况。

    mysql> CREATE TABLE t1 (

    -> c1 INT NOT NULL AUTO_INCREMENT,

    -> PRIMARY KEY (c1)

    -> ) ENGINE = InnoDB;

     

    mysql> INSERT INTO t1 VALUES(0), (0), (3);

     

    mysql> SELECT c1 FROM t1;

    +----+

    | c1 |

    +----+

    | 1 |

    | 2 |

    | 3 |

    +----+

     

    mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

     

    mysql> SELECT c1 FROM t1;

    +----+

    | c1 |

    +----+

    | 2 |

    | 3 |

    | 4 |

    +----+

     

    mysql> INSERT INTO t1 VALUES(0);

    ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

    InnoDB AUTO_INCREMENT Counter Initialization

    This section describes how InnoDB initializes AUTO_INCREMENT counters.

    这部分讲述了InnoDB如何初始化AUTO_INCREMENT计数器。

    If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

    如果你为InnoDB表指定了一个AUTO_INCREMENT列，InnoDB在数据字典里就会包含一个称之为auto-increment的计数器，用于为AUTO_INCREMENT列分配新值。这个计数器只存储在主内存里，而不是在磁盘上。

    To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

    在实例重启之后为了初始化一个auto-increment counter，InnoDB会在AUTO_INCREMENT列插入的时候执行等价于下面的语句。

    SELECT MAX(ai_col) FROM table_name FOR UPDATE;

    InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

    InnoDB会增加语句返回的值，然后将其分配到列以及auto-increment counter上。默认情况下，递增的值是1。这个默认值是可以被auto_increment_increment配置参数覆盖的。

    If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

    如果表是空的，InnoDB使用的值是1。这个默认值是可以被auto_increment_offset配置参数覆盖的。

    If a SHOW TABLE STATUS statement examines the table before the auto-increment counter is initialized, InnoDB initializes but does not increment the value. The value is stored for use by later inserts. This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction. InnoDB follows the same procedure for initializing the auto-increment counter for a newly created table.

    如果在初始化auto-increment counter初始化之前执行了SHOW TABLE STATUS，InnoDB会初始化但不会增加这个值。这个值会被存储起来以供后来insert使用。这个初始化的操作会在表上加一个排他的读锁直到事务结束。对于最新创建的表InnoDB也是以同样的方式处理auto-increment counter的。

    After the auto-increment counter has been initialized, if you do not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter and assigns the new value to the column. If you insert a row that explicitly specifies the column value, and the value is greater than the current counter value, the counter is set to the specified column value.

    在auto-increment counter被初始化之后，如果你没有为AUTO_INCREMENT列明确指定一个 值，那么InnoDB会增加这个计数器并为列分配一个新增。如果你插入的时候明确指定了值，并且这个值大于当前的counter值，那counter将会被设定为这个指定的值。

    InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

    只要实例在运行，InnoDB会在内存里使用auto-increment counter。当实例关闭和重启的时候，InnoDB为每张表的第一个INSERT重新初始化counter，就如前面所说的。

    A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.

    实例重启会取消当时CREATE TABLE and ALTER TABLE语句中AUTO_INCREMENT = N的影响。

    14.6.6 InnoDB and FOREIGN KEY Constraints

    This section describes differences in the InnoDB storage engine's handling of foreign keys as compared with that of the MySQL Server.

    这一部分讲述了InnoDB在处理外键上和其他引擎之间的不同之处。

    Foreign Key Definitions

    Foreign key definitions for InnoDB tables are subject to the following conditions:

    InnoDB对于外键的定义有下面几条：

    • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
    • InnoDB允许外键引用任何的索引中的单个列或者一组列。然而，在引用的表里，必须要索引列的顺序要和外键列的顺序一样。
    • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
    • 目前分区表还不支持外键。
    • InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.
    • InnoDB允许外键引向一个非唯一的索引。这是InnoDB对于标准SQL的一个扩展。

    Referential Actions

    Referential actions for foreign keys of InnoDB tables are subject to the following conditions:

    InnoDB外键的referential actions有下面几条：

    • While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.
    • 当MySQL允许SET DEFAULT的时候，InnoDB会拒绝无效的。InnoDB不允许在CREATE TABLE and ALTER TABLE使用这个子句。
    • If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.
    • 如果在父表里好几行数据有着相同的referenced key值，InnoDB外键会检查是否有其他的parent row有相同的key值。例如，你定义了一个RESTRICT类型的约束，并且有个child row有好几个parent row，那么InnoDB将不会允许删除这些parent row中的任何一个。
    • InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.
    • InnoDB会通过depth-first算法，基于对应外键的索引的记录来执行串联的操作，
    • If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.
    • 如果ON UPDATE CASCADE or ON UPDATE SET NULL递归更新相同的表，那么在串联的时候与预先update，就像RESTRICT。这意味着你不能使用自我引用的ON UPDATE CASCADE or ON UPDATE SET NULL操作。这是为了从串联更新中阻止死循环。另一方面，自我引用的ON DELETE SET NULL是可行的，就如同自我引用的ON DELETE CASCADE一样。串联操作不能超过15层的嵌套循环。
    • Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.
    • 和普通的MySQL一样，在一个操作多行的DML语句里面，InnoDB会逐行检查唯一和外键约束。当执行外键检查的时候，InnoDB会子查看的child或者parnet记录上放置一个行级的共享锁。InnoDB会立即检查外键约束；这个检查也不会延迟到事务提交。但是在SQL标准里面，默认的是为进行延迟检查的。那就是说，只有在完整的SQL处理完之后才会进行约束检查。直到InnoDB落实了约束延迟检查，一些事情也就不会发生了，例如删除一个使用外键引向其自身的记录。

    Foreign Key Usage and Error Information

    You can obtain general information about foreign keys and their usage from querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table, and more information more specific to InnoDB tables can be found in the INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables, also in the INFORMATION_SCHEMA database. See also Section 13.1.17.3, "Using FOREIGN KEY Constraints".

    你可以通过查询INFORMATION_SCHEMA.KEY_COLUMN_USAGE表来获得外键及其使用的相关信息，InnoDB中更多详细的信息可以在INFORMATION_SCHEMA数据库的INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLS表里找到。相关内容可见Section 13.1.17.3, "Using FOREIGN KEY Constraints"。

    In addition to SHOW ERRORS, in the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

    除了SHOW ERRORS之外，在涉及InnoDB表外键错误（在MySQL里通常是Error 150）的事件里，你可以检查SHOW ENGINE INNODB STATUS来获得最近的InnoDB外键错误的详细解读。

    14.6.7 Limits on InnoDB Tables

    Warning

     

    Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables. This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or regenerate them by reinitializing the data directory (see Section 2.10.1, "Initializing the Data Directory").

    不要把mysql数据库里的系统表从MyISAM转成InnoDB。这个操作是不支持的。如果你这么做了，在你从备份里恢复这些表重新初始化了数据字典之前不要重启MySQL（详见2.10.1, "Initializing the Data Directory"）。

     

    Warning

     

    It is not a good idea to configure InnoDB to use data files or log files on NFS volumes. Otherwise, the files might be locked by other processes and become unavailable for use by MySQL.

    不要把InnoDB的数据文件或者日志文件放到NFS卷上。否则，对于MySQL来说这些文件可能会被其他进程锁住而变得不可用。

    Maximums and Minimums

    • A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1000).
    • 一个表最大可以有1017个列（从MySQL5.6.9来提升的，以前的限制是1000）。
    • A table can contain a maximum of 64 secondary indexes.
    • 一个表最多可以有64个secondary index。
    • By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, "CREATE INDEX Syntax". For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
    • 默认情况下，单列索引的key最大可以达到767 bytes。任何索引key的前缀也都有相同的长度限制。相关内容可见Section 13.1.13, "CREATE INDEX Syntax"。例如，假设使用的是UTF-8的字符集，每个字符最大能够达到3 bytes，那么在超过255个字符的TEXT或者VARCHAR列的索引前缀上就很容易达到这个限制。对于使用了DYNAMIC and COMPRESSED行格式的InnoDB表来说，当开启了nnodb_large_prefix配置参数，这个长度的限制可以达到3072 bytes。

    Attempting to use an index prefix length that is greater than the allowed maximum value produces an error. To avoid such errors for replication configurations, avoid setting the innodb_large_prefix option on the master if it cannot also be set on the slaves, and the slaves have unique indexes that could be affected by this limit.

    假设使用的索引前缀的长度大于了最大允许的值并报了错。To avoid such errors for replication configurations, avoid setting the innodb_large_prefix option on the master if it cannot also be set on the slaves, and the slaves have unique indexes that could be affected by this limit.

    • The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.
    • InnoDB在内部key的最大长度是3500 bytes，但MySQL约束了这个只能达到3072 bytes。所有多列索引的组合索引key都有这个限制。
    • If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.
    • 如果在创建MySQL实例的时候通过指定innodb_page_size参数把InnoDB的数据页的大小降到了8KB or 4KB，基于16KB页大小3072 bytes的限制，索引key的最大长度也会成比例地下降。那就是说，当页大小是8KB的时候最大的索引key的长度是1536 bytes，当也大小是4KB的时候最大索引key的长度是768 bytes。
    • The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes for the default page size of 16KB; if you reduce the page size by specifying the innodb_page_size option when creating the MySQL instance, the maximum row length is 4000 bytes for 8KB pages and 2000 bytes for 4KB pages. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.
    • 除了可变长度列(VARBINARY, VARCHAR, BLOB and TEXT)，行的最大长度会比数据页的大小的一半再稍微小一点。那就是说，默认的16KB的页大小，最大的行长度在8000 bytes左右；如果你在创建MySQL实例的时候通过innodb_page_size减少了页的大小，那么8KB的页对应的是限制是4000 bytes，4KB的页大小对应的是2000 bytes。另外LONGBLOB and LONGTEXT列必须小于4GB，还有包括BLOB and TEXT在内的所有的行长度必须小于4GB。

    If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page, as described in Section 14.10.2, "File Space Management".

    如果一个行的长度不到页大小的一半，那么它的全部内容都会被存储在这个数据页里面。如果超过了数据页的一半，那么可变长度列会选择外部off-page存储直到这行能够放入到数据页的一半的空间里，详见Section 14.10.2, "File Space Management"。

    • Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:
    • 虽然InnoDB内部支持的的行大小超过65,535 bytes，但是MySQL自身限制了所有行相加后的大小不能超过65,535：

    mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),

    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

    -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;

    ERROR 1118 (42000): Row size too large. The maximum row size for the

    used table type, not counting BLOBs, is 65535. You have to change some

    columns to TEXT or BLOBs

    See Section C.10.4, "Limits on Table Column Count and Row Size".

    详见Section C.10.4, "Limits on Table Column Count and Row Size".

    • On some older operating systems, files must be less than 2GB. This is not a limitation of InnoDB itself, but if you require a large tablespace, you will need to configure it using several smaller data files rather than one large data file.
    • 在一些老的操作系统上，文件必须要小于2GB。这不是InnoDB本身的限制，但是如果你需要一个大的表空间，那你就需要把表空间配制成使用多个小的数据文件，而不是一个大的文件。
    • The combined size of the InnoDB log files can be up to 512GB.
    • InnoDB所有的日志文件相加最大能达到512GB。
    • The minimum tablespace size is slightly larger than 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.
    • 最小的表空间大小比10MB多一点。最大的表空间大小是40亿个数据页(64TB)。这也是一个表的最大限制。
    • The default database page size in InnoDB is 16KB, or you can lower the page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance.
    • InnoDB里默认的数据页是16KB，或者在创建MySQL实例的时候通过innodb_page_size把数据页减小到8KB or 4KB。

    Note

     

    Increasing the page size is not a supported operation: there is no guarantee that InnoDB will function normally with a page size greater than 16KB. Problems compiling or running InnoDB may occur. In particular, ROW_FORMAT=COMPRESSED in the Barracuda file format assumes that the page size is at most 16KB and uses 14-bit pointers.

    是不能增加数据页的大小的：InnoDB无法保证能够处理超过16KB的数据页。这样在在编译或者运行InnoDB的时候会发生错误。另外，Barracuda文件格式里的ROW_FORMAT=COMPRESSED假设数据页最多是16KB，并使用了14-bit的指针。

     

    A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size. This limitation could affect restore or downgrade operations using data from MySQL 5.6, which does support page sizes other than 16KB.

    同一个MySQL实例上不能把InnoDB的数据文件和日志文件使用不同的大小的数据页。这个限制会影响MySQL5.6的恢复和先下升级操作，因为它们不支持大于16KB的数据页。

    Index Types

    • InnoDB tables support FULLTEXT indexes, starting in MySQL 5.6.4. See Section 14.2.5.3, "InnoDB FULLTEXT Indexes" for details.
    • InnoDB表从MySQL5.6.4开始支持FULLTEXT索引。详见14.2.5.3, "InnoDB FULLTEXT Indexes"。
    • InnoDB tables support spatial data types, but not indexes on them.
    • InnoDB支持空间数据类型，但上面无法加索引。

    Restrictions on InnoDB Tables

    • ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE could produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.
    • ANALYZE TABLE通过对每个索引树的进行random dives来确认索引的基数（显示在SHOW INDEX 的Cardinality列上），并以此来更新索引的基数评估。因为这只是评估，所以重复运行ANALYZE TABLE可能会得到不同的数字。这样能够使得ANALYZE TABLE在InnoDB上运行得更快但无法100%准确，因为它不会对所有的行进行统计。

    You can make the statistics collected by ANALYZE TABLE more precise and more stable by turning on the innodb_stats_persistent configuration option, as explained in Section 14.4.11.1, "Configuring Persistent Optimizer Statistics Parameters". When that setting is enabled, it is important to run ANALYZE TABLE after major changes to indexed column data, because the statistics are not recalculated periodically (such as after a server restart) as they traditionally have been.

    如Section 14.4.11.1, "Configuring Persistent Optimizer Statistics Parameters"所讲述的，你可以通过nnodb_stats_persistent配置参数使得ANALYZE TABLE得到的结果更精确，更稳定。当开启了这个参数的时候，那对在大的索引列数据改动之后运行ANALYZE TABLE是非常重要的，because the statistics are not recalculated periodically (such as after a server restart) as they traditionally have been.

    You can change the number of random dives by modifying the innodb_stats_persistent_sample_pages system variable (if the persistent statistics setting is turned on), or the innodb_stats_transient_sample_pages system variable (if the persistent statistics setting is turned off).

    你可以通过修改innodb_stats_persistent_sample_pages系统变量来修改random dives的数量（要求开启持久的统计信息），或者是innodb_stats_transient_sample_pages系统变量（关闭持久化统计信息的情况下）。

    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 system variable to ensure that MySQL prefers index lookups over table scans. See Section 5.1.4, "Server System Variables", and Section B.5.5, "Optimizer-Related Issues".

    MySQL只有在join优化的时候才会使用索引基数的评估信息。如果一些join操作没有以正确的方式进行优化，那么你就需要运行ANALYZE TABLE。在少部分的情况下ANALYZE TABLE无法为个别表生成足够优质的数据，那么你可以在 查询中使用FORCE INDEX来强制使用一个特定的索引，或者是设置max_seeks_for_key系统变量来确保MySQL更喜欢使用索引查找而不是表扫描。详见Section 5.1.4, "Server System Variables"和Section B.5.5, "Optimizer-Related Issues"。

    • If statements or transactions are running on a table and ANALYZE TABLE is run on the same table followed by a second ANALYZE TABLE operation, the second ANALYZE TABLE operation is blocked until the statements or transactions are completed. This behavior occurs because ANALYZE TABLE marks the currently loaded table definition as obsolete when ANALYZE TABLE is finished running. New statements or transactions (including a second ANALYZE TABLE statement) must load the new table definition into the table cache, which cannot occur until currently running statements or transactions are completed and the old table definition is purged. Loading multiple concurrent table definitions is not supported.
    • 如果在一个表上运行一些语句或者事务，在同一个表上又接连着运行了两次ANALYZE TABLE，那么第二次ANALYZE TABLE操作会被阻塞直到语句或者事务运行完成。之所以这样是因为ANALYZE TABLE在运行完成之后会把当前加载的数据标记为过期。新的语句或者事务（包括第二次的运行的ANALYZE TABLE语句）必须要加载新的表定义到表缓存里，这就要等待当前的运行的语句或者事务允许完成并把旧的表定义purge掉。同时加载多个表定义是不支持的。
    • 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.
    • SHOW TABLE STATUS不会给出精确的InnoDB统计信息，除非表预留了物理空间。行统计仅是用于SQL优化的大致评估。
    • 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 scans 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.
    • InnoDB不会保留内部的行统计数据因为并发的事务在同一时间点上可能会看到不同的行统计数字。为了处理SELECT COUNT(*) FROM t语句，InnoDB会扫密码哦表上的索引，如果这个索引每个完全放置在buffer pool里的话那就要花费一段的时间了。如果你的表不是经常更新的，那么MySQL的query cache是个不错的选择。要快速地得到这个统计值，你可以自己创建一个表counter并让应用程序在insert或者delete的时候去更新它。如果只是要一个大概的值，那也可以使用SHOW TABLE STATUS。
    • 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.
    • 在Windows上，InnoDB在内部总是用小写存储数据和和表的名字。要在不同的区分大小的平台上移动二进制格式的数据库，数据库和表名最好都用小写。
    • 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.
    • AUTO_INCREMENT列ai_col列必须定义成一个索引的一部分，这样就能在表上执行等价的SELECT MAX(ai_col)索引查询来得到这列的最大值。通常，还要求这列是索引的第一个列（前缀列）。
    • InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column while initializing a previously specified AUTO_INCREMENT column on a table.
    • InnoDB在初始化之前的AUTO_INCREMENT列的时候会在索引相关的AUTO_INCREMENT列的尾端放置一个排他锁。

    With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for "bulk inserts" with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2. For more information, See Section 14.6.5, "AUTO_INCREMENT Handling in InnoDB".

    当innodb_autoinc_lock_mode=0，InnoDB会使用特殊的AUTO-INC表锁模式，当要访问auto-increment counter的时候，这个锁会被获得并被保持到当前语句执行结束。当持有了AUTO-INC表锁的时候其他客户端是无法向表里插入数据的。对于innodb_autoinc_lock_mode=1，"bulk inserts"也有相同的情况。当innodb_autoinc_lock_mode=2的时候表界别的AUTO-INC锁是无法使用的。更多的信息查看Section 14.6.5, "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).
    • 当你重启MySQL服务的时候，InnoDB会重复利用AUTO_INCREMENT列生成但未存储的旧值（也就是说是旧事务生成了但被回滚了的值）。
    • When an AUTO_INCREMENT integer column runs out of values, a subsequent INSERT operation returns a duplicate-key error. This is general MySQL behavior, similar to how MyISAM works.
    • 当AUTO_INCREMENT整数列的值超出了范围，那就会报duplicate-key的错误，和MyISAM的情况相似。
    • DELETE FROM tbl_name does not regenerate the table but instead deletes all rows, one by one.
    • DELETE FROM tbl_name不会再生表，而是一行一行地删除所有行。
    • Cascaded foreign key actions do not activate triggers.
    • 级联的外键动作不会触发trigger。
    • You cannot create a table with a column name that matches the name of an internal InnoDB column (including DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID). The server reports error 1005 and refers to error −1 in the error message. This restriction applies only to use of the names in uppercase.
    • 你不能用InnoDB的内部列(including DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID)来为列命名。这样做的话会报1005的错误以及相关联的−1错误。这种限制仅适用于名字使用的是大写的字母。

    Locking and Transactions

    • LOCK TABLES acquires two locks on each table if innodb_table_locks=1 (the default). In addition to a table lock on the MySQL layer, it also acquires an InnoDB table lock. Versions of MySQL before 4.1.2 did not acquire InnoDB table locks; the old behavior can be selected by setting innodb_table_locks=0. If no InnoDB table lock is acquired, LOCK TABLES completes even if some records of the tables are being locked by other transactions.
    • 当innodb_table_locks=1（默认）的时候LOCK TABLES会在每个表上加两个锁。除了在MySQL层上的一个表锁外，还会加一个InnoDB的表锁。MySQL4.1.2之前的版本不会加InnoDB的表锁；这种老的情况和innodb_table_locks=0相似。如果没有获得InnoDB的表锁，即使表里的部分数据被其他的事务锁住了，LOCK TABLES任然会执行完成的（不会阻塞）。
    • In MySQL 5.6, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.
    • 在MySQL5.6里，innodb_table_locks=0不会影响对表显式地使用LOCK TABLES ... WRITE。但是对隐式（例如通过trigger）使用LOCK TABLES ...或者LOCK TABLES ... READ带来的读或写锁是有影响的。
    • All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.
    • 一个事务持有的所有的锁都会在它提交或者中断的时候释放。因此，在autocommit=1模式里好像感觉不到锁的存在，因为持有的锁都会被立即释放掉。
    • You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.
    • 你不能在事务的中间去锁额外的表，因为LOCK TABLES会隐式地执行COMMIT和UNLOCK TABLES。
    • The limit of 1023 concurrent data-modifying transactions has been raised in MySQL 5.5 and above. The limit is now 128 * 1023 concurrent transactions that generate undo records. You can remove any workarounds that require changing the proper structure of your transactions, such as committing more frequently.
    • 并发修改数据事务的限制1023在MySQL5.5级之后的版本已经有所提升。现在并发事务（生成undo记录）的限制是128 * 1023。要避免这个限制你可以更改你的事务结构，例如提交得更频繁一些。
  • 相关阅读:
    牛客（28）数组中出现次数超过一半的数字
    牛客（27）字符串的排列
    2、Spring Cloud和dubbo简介
    1、微服务简介
    12、Spring Boot监控
    11、Spring Boot热部署
    10、Spring Boot分布式
    9、Spring Boot安全
    8、Spring Boot任务
    7、Spring Boot检索
  • 原文地址：https://www.cnblogs.com/paololiu/p/5661052.html
Copyright © 2020-2023  润新知