尽可能使用最有效（最小）的数据类型。MySQL 有许多专门的类型可以节省磁盘空间和内存。例如，如果可能的话，使用较小的整数类型以获得更小的表。MEDIUMINT 通常比 INT 更好，因为 MEDIUMINT 列使用的空间少 25%。Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

如果可能，请声明列为NOT NULL。这可以加快 SQL 操作速度，通过更好地使用索引并消除测试每个值是否为NULL的开销。你还可以节省一些存储空间，每列节省一个比特。如果你真的需要表中包含NULL值，就使用它们。只是要避免允许每列默认NULL值的设置。Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

InnoDB 表默认使用 DYNAMIC 行格式创建。要使用除 DYNAMIC 以外的行格式，可以配置 innodb_default_row_format，或在 ROW_FORMAT 或 CREATE TABLE 语句中显式指定 ALTER TABLE 选项。InnoDB tables are created using the DYNAMIC row format by default. To use a row format other than DYNAMIC, configure innodb_default_row_format, or specify the ROW_FORMAT option explicitly in a CREATE TABLE or ALTER TABLE statement.

紧凑型行格式家族，包括COMPACT、DYNAMIC和COMPRESSED，通过增加某些操作的 CPU 使用以换取行存储空间的减少。如果您的工作负载是典型的、受缓存命中率和磁盘速度限制的负载，它很可能会更快。如果是受 CPU 速度限制的少见情况，它可能会更慢。The compact family of row formats, which includes COMPACT, DYNAMIC, and COMPRESSED, decreases row storage space at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.

The compact family of row formats also optimizes CHAR column storage when using a variable-length character set such as utf8mb3或者utf8mb4. With ROW_FORMAT=REDUNDANT, CHAR(N) occupies N × the maximum byte length of the character set. Many languages can be written primarily using single-byte utf8mb3或者utf8mb4 characters, so a fixed storage length often wastes space. With the compact family of rows formats, InnoDB allocates a variable amount of storage in the range of N到N × the maximum byte length of the character set for these columns by stripping trailing spaces. The minimum storage length is N bytes to facilitate in-place updates in typical cases. For more information, see Section 17.10, “InnoDB Row Formats”.The compact family of row formats also optimizes CHAR column storage when using a variable-length character set such as utf8mb3 or utf8mb4. With ROW_FORMAT=REDUNDANT, CHAR(N) occupies N × the maximum byte length of the character set. Many languages can be written primarily using single-byte utf8mb3or utf8mb4 characters, so a fixed storage length often wastes space. With the compact family of rows formats, InnoDB allocates a variable amount of storage in the range of N to N × the maximum byte length of the character set for these columns by stripping trailing spaces. The minimum storage length is N bytes to facilitate in-place updates in typical cases. For more information, see Section 17.10, “InnoDB Row Formats”.

为了进一步节省空间，通过以压缩形式存储表数据，可以在创建 InnoDB 表时指定 ROW_FORMAT=COMPRESSED，或者对现有的 MyISAM 表运行 myisampack 命令。(InnoDB 压缩表可读写，而 MyISAM 压缩表为只读。)To minimize space even further by storing table data in compressed form, specify ROW_FORMAT=COMPRESSED when creating InnoDB tables, or run the myisampack command on an existing MyISAM table. (InnoDB compressed tables are readable and writable, while MyISAM compressed tables are read-only.)

表，如果没有任何可变长度列（MyISAM列），将使用固定大小的行格式。这更快，但可能会浪费一些空间。参见VARCHAR, TEXT，或BLOB第 18.2.3 节，“MyISAM 表存储格式”。即使有列，你也可以通过VARCHAR选项CREATE TABLE提示希望使用固定长度的行ROW_FORMAT=FIXED.For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. This is faster but may waste some space. See Section 18.2.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED.

表的主索引应尽可能短。这使得每行的识别变得简单高效。对于InnoDB表，主键列会在每个二级索引条目中重复，因此如果有许多二级索引，较短的主键可以节省大量空间。The primary index of a table should be as short as possible. This makes identification of each row easy and efficient. For InnoDB tables, the primary key columns are duplicated in each secondary index entry, so a short primary key saves considerable space if you have many secondary indexes.

只创建那些你需要用来提高查询性能的索引。索引有利于检索，但会减慢插入和更新操作。如果你主要通过组合列来访问表，请在这些列上创建单个复合索引，而不是为每个列创建单独的索引。索引的第一部分应为使用最频繁的列。如果你总是在选择表中的数据时使用多列，索引的第一列应为重复值最多的列，以获得更好的索引压缩效果。Create only the indexes that you need to improve query performance. Indexes are good for retrieval, but slow down insert and update operations. If you access a table mostly by searching on a combination of columns, create a single composite index on them rather than a separate index for each column. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates, to obtain better compression of the index.

如果很可能一个长字符串列在前几个字符上有唯一前缀，那么最好只对这个前缀建立索引，利用 MySQL 支持在列的最左侧部分创建索引（参见 第 15.1.15 节，“CREATE INDEX 语句”）。较短的索引更快，不仅因为它们需要更少的磁盘空间，还因为它们在索引缓存中命中更多，从而减少磁盘寻道次数。参见 第 7.1.1 节，“配置服务器”。If it is very likely that a long string column has a unique prefix on the first number of characters, it is better to index only this prefix, using MySQL's support for creating an index on the leftmost part of the column (see Section 15.1.15, “CREATE INDEX Statement”). Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks. See Section 7.1.1, “Configuring the Server”.

在某些情况下，将经常扫描的表分成两个可能会有好处。如果是动态格式表，并且可以使用较小的静态格式表，以便在扫描表时查找相关行，则尤其如此。In some circumstances, it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic-format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table.

在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的联接。Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding columns.

保持列名简单，这样你可以在不同的表中使用相同的名称，并简化连接查询。例如，在名为customer的表中，使用列名name而不是customer_name。为了使列名在其他 SQL 服务器上可移植，考虑将其字符数保持在 18 个以下。Keep column names simple, so that you can use the same name across different tables and simplify join queries. For example, in a table named customer, use a column name of name instead of customer_name. To make your names portable to other SQL servers, consider keeping them shorter than 18 characters.

通常，尽量保持所有数据不冗余（遵循数据库理论中的第三范式）。不要重复冗长的值，例如姓名和地址，而是给它们分配唯一的 ID，在多个较小的表中按需重复这些 ID，并在查询中通过在连接子句中引用这些 ID 来连接表。Normally, try to keep all data nonredundant (observing what is referred to in database theory as third normal form). Instead of repeating lengthy values such as names and addresses, assign them unique IDs, repeat these IDs as needed across multiple smaller tables, and join the tables in queries by referencing the IDs in the join clause.

如果速度比磁盘空间和维护多份数据副本的成本更重要，例如在商业智能场景中分析大型表的所有数据时，您可以放宽规范化规则，重复信息或创建汇总表以获得更高的速度。If speed is more important than disk space and the maintenance costs of keeping multiple copies of data, for example in a business intelligence scenario where you analyze all the data from large tables, you can relax the normalization rules, duplicating information or creating summary tables to gain more speed.