Mysql 表设计

Mysql要设一张好的表, 先要了解以下2个知识：

• 数据类型
• 索引

---

数据类型

• 整数
  
  • TINYINT
    
    • 存储空间8位
    • -128 ~ 127
    • 0 ~ 255
  • SMALLINT
    
    • 存储空间16位
    • -32,768 ~ 32,767
    • 0 ~ 65535
  • MEDIUMINT
    
    • 存储空间24位
    • -8388608 ~ 8388607
    • 0 ~ 16777215
  • INT
    
    • 存储空间32位
    • -2,147,483,648 ~ 2,147,483,647
    • 0 ~ 4294967295
  • BIGINT
    
    • 存储空间64位
    • -9223372036854775808 ~ 9223372036854775807
    • 0 ~ 18446744073709551615
• 实数
  
  • DOUBLE
    
    • 8字节
  • Float
    
    • 4字节
  • DECIMAL
    
    • DECIMAL(18,9) 小数点左右各存9个数字,每个数字4字节,小数点1字节,一共9字节
• 字符串
  
  • CHAR 定长字符串
  • VARCHAR 可变长字符串
    
    • 长度小于255时, 使用1个额外字节记录长度信息, 大于255时使用2字节
  • BLOB
  • TEXT
• 枚举ENUM
• 日期
  
  • DATETIME
    
    • 8字节
    • 1001年 ~ 9999年
  • TIMESTAMP
    
    • 4字节
    • 1970年 ~ 2038年

整数 有符号与无符号的效率是一样的, 应选择小但够用的类型

对于实数, 可以选择使用整数存储, 计算时除以10/100/1000就可以得到一个小数

字符串 长度固定的应选择CHAR, VARCHAR可以优化存储空间, 但UPDATE需要额外的工作, 使用VARCHAR时应分配真正需要的空间,不要多分配

BLOB&TEXT 尽量避免使用

枚举类 可以用整数代替

TIMESTAMP的空间效率比DATETIME高

避免使用NULL, 会使得索引和值比较变得更负责

IP地址 使用无符号整数存储 INET_ATON() & INET_NTOA()进行转换
电话号码 使用无符号整数存储, 空间效率更高, 而且可以数据类型校验

索引(B-Tree)

• B-Tree索引必须从左到右按顺序匹配(个数不限,按顺序就可以)才有效
• 索引查询间不能有范围(<,>,like, <> …)查询, 除IN和<=>(<>和<=>是不同的操作)

• 表达式&函数不能使用索引(例如:id + 1 = 5, id + 1是表达式不能使用id索引)

  索引选择 不重复的索引值 / 总值 占比最高的

  对于字符串, 可以冗余字段存储字符串的哈希作为索引(CRC32(), FNV64())

  索引可以用于排序, 但必须满足索引的使用条件

  IN会以指数增加, 一个查询中的IN不能太多

  • 例如: where a1 in (1,2,3) and a2 in (4,5,6,7) and a3 in (9,10) 就会有 3*4*2 = 24种不同的组合

技巧

• 不要有太多的列
• 单个查询关联在12表以内
• 避免NULL
• 某些冗余字段可以提升查询
• 缓存表 & 汇总表
• 计数器表