mysql

DesignDB-4-2_物理设计

物理设计介绍

涉及到的内容

  1. 定义数据库.表.字段的命名规范
  2. 选择合适的存储引擎
  3. 为表字段选择合适的数据类型
  4. 建立数据库结构

1. 定义数据库.表.字段的命名规范

  • 数据库.表和字段命令要遵循可读性原则 : 下划线分割单词
  • 数据库.表和字段命令要遵循表意性原则 : 起名字要有意义
  • 数据库.表和字段命令要遵循长名原则 : 短名往往无法体现内容意思,长度适度

2. 选择合适的存储引擎

  • MyISAM
  • InnoDB
  • Archive

==无脑选择InnoDB==

3. 为表字段选择合适的数据类型

原则

一个列可选多个数据类型
- 数字类型 > 日期/二进制类型 > 字符类型
一个列只有相同级别数据类型
- 优先选择占用空间小的数据类型

整数类型
列类型|存储空间|SIGNED|UNSIGNED
–|–|–|–
tinyint|1字节|-128127｜0255
smallint|2字节|-3276832768｜065535
mediumint|3字节|-83886088388007｜016777215
int|4字节|-21474836488214748364｜04294967295
bigint|8字节|+-9223372036854775808|9223372036854775808*2

int(2)也会存储4字节,如果只2位数还是要用tinyint

实数类型
列类型|存储空间|是否精确
–|–|–
FLOAT|4字节|否
DOUBLE|8字节|否
DECIMAL|每4字节存9个数字,小数点占1个字节|是

DECIAML(18,9) 需要9个字节来存储

VARCHAR和CHAR

VARCHAR特点

  • 用于存储变长字符串,只占用实际存储空间
  • 列最大长度小于255则只占用1个额外字节用于记录字符串长度
  • 列最大长度大于255则占用2个额外字节用于记录字符串长度

VARCHAR长度选择原则:

  • 使用最小符合需求的长度
  • varchar(5)和varchar(255)存储”MYSQL”字符串性能不同

适用场景:

  • 字符串列的最大长度比平均长度大很多
  • 适合字符串列很少被更新
  • 适合使用了多字节字符集存储的字符串

CHAR特点

  • char类型的长度固定
  • 字符串在char类型的列中会自动删除末尾的空格
  • char类型最大长度为255

适用场景:

  • 存储近似长度,类似md5
  • 长度短小的字符串，如性别只有男和女则用char(1)比较合适
  • 适合经常更新的字段,避免产生存储碎片

日期类型

  • datetime
    • 和时区无关,占用8个字节的存储空间
    • datetime = YYYY-MM-DD HH:MM:SS
    • 定义6微秒 datetime(6) = YYYY-MM-DD HH:MM:SS.fraction
    • 存储范围 1000-0101 00:00:00~9999:12:31 23:59:59
  • timestamp
    • 存储时间戳,19700101到当前时间的秒数
    • YYYY-MM-DD HH:MM:SS.[.fraction]格式显示,占用4字节
    • 保存1970-01-01到2038-01-19的时间
    • 显示依赖于所指定的时区
    • 在行数据修改时可自动修改timestamp列的值
1
2
3
4
5
6
create table t( d1 datetime,d2 timestamp );
insert into t values (now(),now());
select * from t;
set time_zone = '-10:00';
select * from t;
alter table t modify d1 datetime(6), modify d2 timestamp(6);
  • date类型和time类型
    • date类型优点
      • 占用字节数比字符串.datetime.int存储还要少,date类型只需3字节
      • date类型还可以利用日期函数进行日期间的计算
      • 保存时间 1000-01-01到9999-12-31之间
    • time类型
      • 用于存储时间数据,格式 HH:MM:SS

注意

  1. 不要用字符串来存储时间数据
  2. 日期时间类型存储空间占用比字符串小
  3. 日期时间类型在进行查找过滤时可以利用时期来进行对比
  4. 日期时间类型有丰富的处理函数,方便的对时期类型进行日期计算
  5. 利用int存储日期时间不如timestamp类型