1、选择优化的数据类型
1.1、更小的通常的更好
一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
1.2、简单就好
简单数据类型的操作通常需要更少的CPU周期。
1.3、尽量避免NULL
很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好制定列为NOT NULL,除非真的需要存储NULL值。
2、数据类型
2.1、整数类型
有两种类型的数字:整数(whole number)和实数(real number)。如果存储整数,可以使用这几种整数类型:TINYINT,SAMLLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。
整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128~127。
有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。
2.2、实数类型
实数是带有小数部分的数字。然而,它们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。
2.3、VARCHAR类型
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。有一种情况例外,如果MySQL表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。
2.4、CHAR类型
CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格(在MySQL4.1和更老版本中VARCHAR也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的,区别只是在存储格式上)。CHAR值会根据需要采用空格进行填充以方便比较。
2.5、BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
2.6、DATETIME类型
这个类型能保存大范围的值,从1001到9999年,精度为妙。它把日期和实践封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字符的存储空间。默认情况下,MySQL以一种可排序的、无歧义的格式显示DATETIME值,例如“200-01-16 22:37:08”。这是ANIS标准定义的日期和时间表示方法。
2.7、TIMESTAMP类型
TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字符的存储空间,因此它的范围比DATETIME小得多:只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期,并提供了UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。
3、MySQL schema设计中的陷阱
- 太多的列
- 太多的关联
- 全能的枚举
- 变相的枚举
4、范式
4.1、范式的优点和缺点
- 范式化的更新操作通常比反范式化要快
- 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据
- 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快
- 很少有多余的数据,意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句
4.2、反范式的优点和缺点
反范式化的schema因为所有数据都在一张表中,可以很好地避免关联
如果不需要关联表,则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多,因为这样避免了随机I/O。
5、缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然后,有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据,这是非常好的方法,但是有时确实没有选择的余地(例如,需要避免负责、昂贵的实时更新操作)。
6、物化视图
许多数据库管理系统(例如Oracle或者微软SQL Server)都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表,可通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图。然而,使用Justin Swanhart的开源工具Flexviews,也可以自己实现物化视图。Flexviews比完全自己实现的解决方案更要惊喜,并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成:
- 变更数据抓取(Change Data Capture,CDC)功能,可以读取服务器的二进制日志并且解析相关行的变更
- 一系列可以帮助创建和管理视图的定义的存储过程
- 一些可以应用变更到数据库中的物化视图的工图
7、计数器表
如果应用在表中保存计数器,则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意,这样可使计数器表小且快,还可以帮助避免查询缓存失效。
8、加快ALTER TABLE操作的速度
MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用心的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。这样操作可能需要花费很长时间,如果内存不足而表又很大,而且还有很多索引的情况下尤其如此。对以上情况一般有三种方法:
- 先在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服务的主库进行切换
- “影子拷贝”:用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表
- 只修改 .frm文件
9、总结
良好的schema设计原则是普遍使用的,但MySQL有它自己的实现细节要注意。概括来说,尽可能保持任何东西小而简单总是好的。MySQL喜欢简单,需要使用数据库的人应该也同样会喜欢简单地原则:
- 尽量避免过度设计,例如会导致极其复杂查询的schema设计,或者有很多列的表设计
- 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值
- 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列
- 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存
- 尽量使用整型定义标识列
- 避免使用MySQL已经遗弃的特性,例如制定浮点数的精度,或者整数的显示宽度。
- 小心使用ENUM和SET,虽然它们用起来很方便,但是不要滥用,否则有时候会变成陷阱,最好避免使用BIT
- 范式是好的,但是反范式有时也是必须的,并且能带来好处