【轉】MySql官方建議：Innodb表最佳實踐

原文：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.

指定主鍵：使用最頻繁查詢的一個或者多個列作為主鍵，如果沒有，就使用自增id作為主鍵

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.

擁抱join：當資料從多個表裡面按照相同的id值取出來的時候，優先使用join，可以使用外鍵來提高join的性能。

3) turn off autocommit. committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).

關閉autocommit：每秒送出幾百次會限制性能。

（譯者注：業務系統建議不要關閉，否則程式設計比較麻煩，批量導入資料的時候可以關閉，然後定時或者定量commit）

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.

使用事務送出，避免頻繁送出

5) stop using lock table statements. innodb can handle multiple sessions all reading and writing to the same table at once, without sacrificing 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.

不要使用lock table操作，innodb的機制能夠支援高并發操作，且不損失可靠性和性能。推薦使用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.)

打開innodb_file_per_table選項