8.4 Optimizing Database Structure 优化数据库结构

8.4.1 Optimizing Data Size 优化数组大小

8.4.2 Optimizing MySQL Data Types 优化MySQL 数据类型

8.4.3 Optimizing for Many Tables

8.4.4 How MySQL Uses Internal Temporary Tables

如果你的作用是作为数据库设计者,寻找最有效的方式来组织你的schemas,tables,和列。

当你调优代码的时候,你最小化I/O,将相关的项目放在一起,并提前计划当数据量增加时保持性能。

从一个有效的数据库设计开始,它变得团队成员很容易的写出高性能的代码

8.4.1 Optimizing Data Size 优化数据大小

设计你的表来最小化磁盘空间,这可能会导致巨大的改进,通过减少数据的读取和写入的量。

小表通常需要很少的内存在它们的内容被机器处理在查询期间,任何的表的数据空间减少,也减少了索引的大小。

MySQL 支持很多不同的存储类型(表类型和行格式,

对于每个表,你可以决定哪种存储引擎和索引方法来使用

选择合适的表格式用于应用可能会有很大的性能增益。See Chapter 15, Alternative Storage Engines.

你可以得到更好的性能对于一个表,最小化存储空间通过使用下面的技术:

Table Columns

1.使用最有效的(最小的) 数据类型, MySQL 有很多种特殊的类型可以节约磁盘和内存,

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

MEDIUMINT 通常是比INT好,因为MEDIUMINT 列使用25%少的空间

定义列为NOT NULL 如果可能的话,它让SQL 操作变的更快,

通过更好的使用索引和消除测试是否值为NULL的负载。

你也节约了存储空间, 一个列一个位。如果你真的需要NULL 值在你的表里,

使用它们。只是避免摩恩设置允许NULL 值在每个列里。

Row Format

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

如果你希望降级到老版本的MySQL，你可以请求老的格式ROW_FORMAT=REDUNDANT.

compact row format的存在降低了行存储空间 20%,代价是增加了CPU 使用对于一些操作。

如果你的工作负载是典型的缓存命中率和磁盘的速度限制

compact InnoDB 的格式也改变CHAR列包含UTF-8 数据如何存储

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

给定一个UTF-8编码的最大长度是3个字节。

为了最小化空间甚至可以通过排序表数据以压缩的格式,指定ROW_FORMAT=COMPRESSED

当创建InnoDB表,或者运行myisampack 命令在一个存在的MyISAM 表

(InnoDB 表压缩是可读可写的,但是MyISAM 压缩是只读的)

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

一个固定大小的行格式被使用。这个是更快的但是会浪费很多的空间。

See Section 15.2.3, “MyISAM Table Storage Formats”.

你可以提示你需要固定长度的行,即使你有VARCHAR 列在你的CREATE TABLE option ROW_FORMAT=FIXED.

Indexes

表的主键应该尽可能的短,这使得识别每一行变的简单和高效。对于一个InnoDB 表,

主键列是重复的在每个 secondary index entry, 因此一个短的主键节约了相当多的空间,

如果你有很多secondary indexes.

只创建一个索引需要改善查询性能, indexes 具有很好的检索功能,

但是减慢了插入和更新操作。

如果你访问一个表主要是通过搜索一个组合列,创建一个单独的组合索引相比一个单独的索引在每个列。

索引的第一部分应该是最长使用的列, 如果你经常使用很多列当你查询表的时候,

第一列在索引里应该是做重复的列,以获得更好的索引压缩。

一个长的字符串的列有一个唯一索引在字符的第一个,最好是只索引前缀,使用MySQL的支持

用于创建一个在列的最左部分

短的索引是更快的, 不只是因为它们需要更少的磁盘空间,但是因为它们也会给你更火的命中率在Index cache里,

从而需要更少的磁盘寻址。 See Section 8.12.2, “Tuning Server Parameters”.