DB
MySQL

MySQL基础

范式设计

实际应用中经常需要混用范式设计和反范式设计，最常见的反范式化数据的方法是缓存表、汇总表，缓存表即在不同的表中存储相同的特定列，汇总表即汇总count等信息。对于维护缓存表和汇总表中的数据，通常通过实时维护和定期重建两种方式，取决于应用程序，一般缓存表用实时维护数据更多点，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

第一范式1NF

属于第一范式关系的所有属性都不可再分，即数据项不可分，强调数据表的原子性，是其他范式的基础

第二范式2NF

要求数据库表中的每个实例或行必须可被惟一地区分，通常需要为表加上一个列，以存储各个实例的惟一标识，该属性列被称为主关键字或主键，实体的属性完全依赖于主关键字，不能存在仅依赖主关键字一部分的属性即联合主键的情况

第三范式3NF

每一个非主属性既不部分依赖也不传递依赖于业务主键，即在第二范式基础上消除非主键对主键的传递依赖。

反范式设计

实际业务查询中会大量存在表关联查询，大量表关联很多时候非常影响查询性能，反范式化就是为了性能和读取效率考虑而适当对数据库设计范式要求进行违反，允许存在少量得冗余，即反范式化就是使用空间来换取时间。

范式化设计优缺点

优点

  • 范式化更新操作通常比反范式化快
  • 当数据较好地范式化时，则只有很少或没有重复数据，故只需修改更少的数据
  • 范式化表通常更小，可更好地放在内存中，故执行操作会更快
  • 很少有多余数据，检索列表数据时更少需要DISTINCT或GROUP BY语句

缺点

  • 通常需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多，不但代价昂贵，也可能使一些索引策略无效
反范式化设计优缺点
  • 可减少表的关联
  • 可更好的进行索引优化

缺点

  • 存在数据冗余及数据维护异常
  • 对数据修改需要更多成本

数据类型选择

选择正确的数据类型，对于性能至关重要，在数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源，且尽量把字段定义为NOT NULL，避免使用NULL。一般应该遵循下面两步：

  • 确定合适的大类型：数字、字符串、时间、二进制
  • 确定具体的类型：有无符号、取值范围、变长定长等
数值类型

若整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍；建议使用TINYINT代替ENUM、BITENUM、SET；避免使用整数的显示宽度，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。

DECIMAL最适合保存准确度要求高，且用于计算的数据，如价格。但在使用DECIMAL类型时，注意长度设置。建议使用整形类型来运算和存储实数，方法是实数乘以相应的倍数后再操作。整数通常是最佳的数据类型，因为它速度快，且能使用AUTO_INCREMENT。

INT显示宽度：在使用ZEROFILL时有用，让查询结果前填充0，如TINYINT(5) ，若结果是5，则输出就是00005，实际存储的值还是5，且存储的数据不会超过255，只是输出数据时在前面填充了0。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 -2^7 ~ 2^7-1 (0, 255) 小整数值
SMALLINT 2 字节 -2^15 ~ 2^15-1 (0, 65535) 大整数值
MEDIUMINT 3 字节 -2^23 ~ 2^23-1 (0, 16777215) 大整数值
INT或 INTEGER 4 字节 -2^31 ~ 2^31-1 0~2^63-1 大整数值
BIGINT 8 字节 -2^63 ~ 2^63-1 0~2^127-1 极大整数值
FLOAT 4 字节 单精度浮点数值
DOUBLE 8 字节 双精度浮点数值
DECIMAL DECIMAL(M,D) 若M>D
为M+2否则为D+2		 依赖于M和D的值 依赖于M和D的值 小数值
日期和时间

MySQL能存储的最小时间粒度为秒。建议用DATE数据类型来保存日期，默认日期格式yyyy-MM-dd。用内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。

当数据格式为TIMESTAMP和DATETIME时，可用CURRENT_TIMESTAMP作为默认值，MySQL会自动返回记录插入的确切时间，TIMESTAMP是UTC时间戳，与时区相关。

DATETIME的存储格式是一个yyyy-MM-dd HH:mm:ss的整数，与时区无关，存什么读出来就是什么。除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但大公司一般用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。不推荐把Unix的时间戳保存为整数值。

类型 大小 (字节) 范围 格式 用途
DATE 3 1000-01-01到9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59到838:59:59 HH:mm:ss 时间值或持续时间
YEAR 1 1901到2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00到
9999-12-31 23:59:59		 YYYY-MM-DD HH:mm:ss 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00到
2038-01-19 03:14:07		 YYYYMMDDhhmmss 混合日期、时间，时间戳

字符串

字符串长度相差较大用VARCHAR；字符串短且所有值都接近一个长度用CHAR。CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。需要用来计算的数字不要用VARCHAR类型保存，可能会导致一些与计算相关的问题，可能影响到计算的准确性和完整性。

尽量少用BLOB和TEXT，若实在要用可考虑将BLOB和TEXT字段单独存一张表用id关联。BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。BLOB和TEXT都不能有默认值。

类型 大小 用途
CHAR 0-255字节 定长字符串，char(n)当插入字符串实际长度不足n时，
插入空格进行补充保存。检索时尾部的空格会被去掉。
VARCHAR 0-65535字节 变长字符串，varchar(n)中n代表最大列长度，
插入字符串实际长度不足n时不会补充空格
TINYBLOB 0-255字节 不超过255个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16777215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16777215字节 中等长度文本数据
LONGBLOB 0-4294967295字节 二进制形式的极大文本数据
LONGTEXT 0-4294967295字节 极大文本数据