mysql梳理笔记---mysql的物理设计

物理设计涉及的内容

  • 定义数据库/表及字段的命名规范
  • 选择合适的存储引擎
  • 为表中的字段选择合适的数据类型
  • 建立数据库结构

定义数据库/表及字段的命名规范

  • 数据库/表及字段的命名要遵循 可读性原则:使命名具有更好对可读性。比如下划线分割不同单词,或首字母大写(mysql对大小写敏感)
  • 数据库/表及字段的命名要遵循 表意性 原则:使用能够表明对象意义对名字。比如order表。
  • 数据库/表及字段的命名要遵循 长名 原则:对象数据库命名时,少用或尽量不用缩写,充分体现命名内容

选择合适的存储引擎

为表中字段选择合适数据类型

  • 当一个列可以选择多种数据类型时,优先考虑数字类型,其次时日期或二进制类型,最后时字符类型。相同级别的数据类型里,应该优先选择占用空间小的数据类型。
    • 如何选择正确的整数类型(如果两位整数,选tinyInt。):
    • 如何选择正确的实数类型(float/double 是非精确的值,得出结果和实际有异性差异。101.4-80 = 21.40000000000006。decimal需要占据9个字节。小数点前后各四个,小数点一个。如果是存储财务相关数据,必须要用精确的decimal类型。其他数据可以考虑使用double和float):

      float类型表示单精度浮点数值,double类型表示双精度浮点数值,float和double都是浮点型,而decimal是定点型;

    • 如何选择varchar和char类型:
      • varchar和char的宽度计算是字符,而不是字节。
      • varchar类型特点
        • varchar用户存储变长字符串,只占用必要的存储空间。可能很长,也可能很短。比如一个长度50的varchar列,存了一个长度为10的字符,这个列的存储空间只占用10的宽度。
        • 列的最大长度小于255,则只占用一个额外字节用于记录字符串长度
        • 列的最大长度大于255,则只占用两个额外字节用于记录字符串长度
      • varchar长度选择的问题
        • 使用最小的符合需求的长度 :存储姓名,中文名,基本上不会超过是个字符,涉及到外国人姓名,长度会变长,但也不会有255的长度。 也不是越小越好,因为一旦发现字段长度不够,线上修改varchar长度,会锁表。
        • varchar(5)和varchar(200),都没超过255,都只占用一个额外字节记录长度。但是 占用的内存不一样。所以还是需要选择最小长度。
      • varchar的使用场景
        • 字符串列的长度比平均长度大很多,发挥 变长存储的特点。
        • 字符串列很少被更新。 由于varchar类型的更新,由于字符串长度会产生变化,可能引起存储页的分裂,造成存储碎片。
        • 使用多字节字符集存储字符串(utf-8等)
      • char类型特点
        • char类型是定长的
        • 字符串存储在char类型中时,如果末尾包含空格,空格会被删除
        • char类型的最大宽度只有255个字符
      • char的使用场景
        • 字符串拥有近似的长度 :如md5值,身份证,手机号
        • 适合存储短字符串:节省空间。如用户性别
        • 适合存储经常被更新的字符串
    • 如何选择日期类型:
      • DATETIME类型:以YYYY-MM-DD HH:MM:SS[.fraction]格式存储时间。

        • datetime=YYYY-MM-DD HH:MM:SS
        • datetime(6)=YYYY-MM-DD HH:MM:SS.fraction (5.6之后可用与保存微秒,最大是6)
        • datetime类型与时区无关,占用8个字节的存储空间。
        • 时间范围:1000-01-01 00:00:00 到 9999-12-12 23:59:59
      • TIMESTAMP类型:

        • 存储的是时间戳,格林尼治时间1970年1月1日到当前时间的秒数
        • 以YYYY-MM-DD HH:MM:SS.fraction 格式显示,占用4个字节
        • 时间范围只能保存1970-01-01 到2038-01-19号。
        • timestamp类型显示依赖于所指定的时区
        • 在行数据修改时,可以自动修改timestamp列的值
      • date和time类型:

        • date存储生日等,只需要3个字节
        • 使用date类型,还可以利用日期时间函数进行计算
        • time存储HH:MM:SS
        • time也可以通过长度存储微秒
      • 日期时间数据存储注意事项:
        • 不要使用字符串类型存储日期时间数据
          • 比字符串占用空间小
          • 日期时间类型在查找过滤时,可以利用日期来对比
          • 日期时间类型有丰富的处理函数,可以方便对日期类型进行日期计算

随机浏览