MySql official recommendation: Innodb table Best Practices

Original article: http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html
Best Practices for InnoDB Tables
If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them throughout this chapter. To make a long story short:

1) Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn't an obvious primary key.
Specify primary key:Use one or more columns that are most frequently queried as the primary key. If not, use the auto-increment id as the primary key.

2) Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare
Those columns with the same datatype in each table. the foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
Embracing join:When data is obtained from multiple tables according to the same ID value, join is preferred. Foreign keys can be used to improve join performance.

3) Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device ).
Disable autocommit: Hundreds of submissions per second will limit performance.
(Note: Do not close the business system. Otherwise, programming is troublesome. You can disable it when importing data in batches, and then periodically or quantitatively commit the data)

4) Bracket sets of related changes, logical units of work, with start transaction and COMMIT statements. while you don't want to commit too often, you also don't want to issue huge batches of INSERT,
UPDATE, or DELETEstatements that run for hours without committing.
Commit using transactionsTo avoid frequent submission

5) Stop using lock table statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacriicing reliability or high performance. To get exclusive
Write access to a set of rows, use theSELECT... for update syntax to lock just the rows you intend to update.
Do not use the lock table operationThe Innodb mechanism supports high concurrency operations without compromising reliability and performance. We recommend that you use SELECT... for update.

6) Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace.
(This setting is required to use some of the other features, such as table compression and fast truncation .)
Enable the innodb_file_per_table Option