设计您的表以最小化它们在磁盘上的空间。通过减少写入磁盘和从磁盘读取的数据量,可以带来巨大的改进。较小的表通常需要较少的主内存,而它们的内容在查询执行期间被主动处理。表数据的任何空间减少也会导致可以更快处理的更小索引。
MySQL 支持许多不同的存储引擎(表类型)和行格式。对于每个表,您可以决定使用哪种存储和索引方法。为您的应用程序选择合适的表格格式可以大大提高性能。请参阅 第 14 章,InnoDB 存储引擎和 第 15 章,替代存储引擎。
通过使用此处列出的技术,您可以获得更好的表性能并最大限度地减少存储空间:
InnoDB
默认情况下,表是使用DYNAMIC
行格式创建的。要使用 以外的行格式,请在or语句 中DYNAMIC
配置innodb_default_row_format
或明确指定ROW_FORMAT
选项。CREATE TABLE
ALTER TABLE
The compact family of row formats, which includes
COMPACT
,DYNAMIC
, andCOMPRESSED
, 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 asutf8mb3
orutf8mb4
. WithROW_FORMAT=REDUNDANT
,CHAR(
occupiesN
)N
× the maximum byte length of the character set. Many languages can be written primarily using single-byteutf8
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 ofN
toN
× the maximum byte length of the character set for these columns by stripping trailing spaces. The minimum storage length isN
bytes to facilitate in-place updates in typical cases. For more information, see Section 14.11, “InnoDB Row Formats”.To minimize space even further by storing table data in compressed form, specify
ROW_FORMAT=COMPRESSED
when creatingInnoDB
tables, or run the myisampack command on an existingMyISAM
table. (InnoDB
compressed tables are readable and writable, whileMyISAM
compressed tables are read-only.)For
MyISAM
tables, if you do not have any variable-length columns (VARCHAR
,TEXT
, orBLOB
columns), a fixed-size row format is used. This is faster but may waste some space. See Section 15.2.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you haveVARCHAR
columns with theCREATE TABLE
optionROW_FORMAT=FIXED
.
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.
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 13.1.14, “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 5.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.
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 ofname
instead ofcustomer_name
. To make your names portable to other SQL servers, consider keeping them shorter than 18 characters.
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.
如果速度比磁盘空间和保留多个数据副本的维护成本更重要,例如在分析大表中所有数据的商业智能场景中,您可以放宽规范化规则,复制信息或创建汇总表以获得更快的速度。