8.4 Optimizing Database Structure 优化数据库结构

8.4 Optimizing Database Structure 优化数据库结构

8.4.1 Optimizing Data Size
8.4.2 Optimizing MySQL Data Types
8.4.3 Optimizing for Many Tables
8.4.4 How MySQL Uses Internal Temporary Tables

当你作为数据库designer 的角色的时候, 寻找最有效的方法来组织你的schemas,tables 和列。

当调优应用代码时, 你可以最小化I/O, 把相关的项目联系起来,并提前计划

以使性能保持高效 在随着数据增加的同时。 有效的数据库设计可以让你的team成员轻松的写高性能的应用代码。

8.4.1 Optimizing Data Size 优化数据大小

设计你的表来最小化它们的空间,这能导致巨大的改进通过降低从磁盘数据读取和写入的数量,较小的表通常需要更少的内存,

当它们的内容被积极的处理在查询执行期间。

MySQL 支持许多不同的存储引擎(表类型) 和行格式,对于每个表,你可以确定使用哪种存储引擎和索引。

选择合适的表格式。

你可以得到更好的性能和最小化存储空间通过使用下面列出的技术:

表列:

使用最有效的(最少的)数据类型, MySQL 有很多特别的类型来节省磁盘空间和内存。

比如, 使用小的整型类型如果可能来得到小的表。

MEDIUMINT 通常是一个更好的选择相比INT,因为一个MEDIUMINT 列使用 25% less space.

如果可能的话,定义列为NOT NULL, 它会让SQL操作变的更快,能够更好的使用indexes 和消除测试是否每个值是NULL的开销.

你可可以节约一些空间, 每列一个位。如果你真的需要NULL值在你的表里, 请使用它们。

只能避免默认设置,允许NULL值。

Row Format

InnoDB 表使用一个紧凑的存储格式。默认,表创建紧凑合适(ROW_FORMAT=COMPACT).

如果你希望降级到旧的MySQL 版本。你可以要求来的格式 ROW_FORMAT=REDUNDANT.

紧凑行格式的存在降低了约20%的行存储空间,代价是增加了CPU 使用在某些操作,

如果你的负载是典型的受cache 命中率和磁盘速度限制,它可能变的更加快。如果是一种罕见的情况，是由处理器的速度限制，它可能是慢。

紧凑的InnoDB 格式会改变 CHAR列包含UTF-8数据的存储。

在ROW_FORMAT=REDUNDANT, 一个UTF-8 CHAR(N) 占据3 * N 字节.

为了最大限度地减少空间进一步压缩表数据,

指定ROW_FORMAT=COMPRESSED ,在创建InnoDB 表的时候, 运行myisampack 在一个存在的MyISAM表上
（InnoDB 表压缩是读可读可写的,但是MyISAM 压缩表是只读的)

对于MyISAM表,如果你没有任何可变长度的列(VARCHAR, TEXT, or BLOB columns),

使用固定尺寸的记录格式。这是更快的,但可能浪费一些空间。

indexes

表的主要索引必须尽可能的短, 这个使得识别每一行变的容易和有效,对于InnoDB 表,

主要的key 列是重复的 在每个第2个索引entry，因此一个短的primary key 节省了想多的空间,如果你有很多的secondary indexes.

只创建索引来提高查询性能, indexes 是良好的检索, 但会变慢insert和update 操作。

如果它很有可能,一个长的字符串列有一个唯一前缀在字符串的第一个数字,

最好时只索引前缀部分,使用MySQL 的支持创建一个索引在列的最左边部分

短的索引更快,较短的索引更快，不仅是因为它们需要更少的磁盘空间，而且因为它们也会给你更多的索引缓存的命中率，从而更少的磁盘寻找。见第8.12.2，“调整服务器参数”。