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
- TINYINT
- 实数
- DOUBLE
- 8字节
- Float
- 4字节
- DECIMAL
- DECIMAL(18,9) 小数点左右各存9个数字,每个数字4字节,小数点1字节,一共9字节
- DOUBLE
- 字符串
- CHAR 定长字符串
- VARCHAR 可变长字符串
- 长度小于255时, 使用1个额外字节记录长度信息, 大于255时使用2字节
- BLOB
- TEXT
- 枚举ENUM
- 日期
- DATETIME
- 8字节
- 1001年 ~ 9999年
- TIMESTAMP
- 4字节
- 1970年 ~ 2038年
- DATETIME
整数 有符号与无符号的效率是一样的, 应选择小但够用的类型
对于实数, 可以选择使用整数存储, 计算时除以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
- 某些冗余字段可以提升查询
- 缓存表 & 汇总表
- 计数器表