【MySQL】《高性能MySql》笔记二

《高性能MySql》笔记二


1、字段类型设计原则

  • smaller is usually better.(更小的通常更好)
  • simple is good.(简单就好)
  • avoid null if possible.(尽量避免 NULL) 注:NULL列改成非NULL,对性能提升有限。

2、数字类型

整数/whole numbers:

类型 位数 范围
inyint 8 bits -2^(8-1) ~ 2^(8-1)-1
smallint 16 bits -2^(16-1) ~ 2^(16-1)-1
mediumint 24 bits -2^(24-1) ~ 2^(24-1)-1
int 32 bits -2^(32-1) ~ 2^(32-1)-1
bigint 64 bits -2^(64-1) ~ 2^(64-1)-1

实数/real numbers

  1. float 和 double 使用标准的浮点数运算计算出近似值
  2. float 32 bits,double 64 bits
  3. decimal 是精确的实数,没有精度损失

注意事项:

  1. MySQL 进行整数运算使用的是 bigint,float 和 double 的运算使用的是 double
  2. decimal 能够提供精确运算可以用于金融数字,但是运算开销大。建议把小数扩大一定倍数后,按整数对待

3、字符串

对于字符串,不同的存储引擎实现方式不同,下面都是 innodb 的行为。(MyISAM engine:MySql 8.0 已经移除,不再支持)

变长字符串/varchar:

  1. 额外使用 1~2个字节存储字符串长度,超过 255 时使用两个字节记录
  2. varchar 节省存储空间,但是被更改时,一行数据的长度发生变化,会产生碎片或者页面分裂,导致额外的处理
  3. varchar 适用场景:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这个的复杂的字符集,每个字符都使用了不同的字节数进行存储。
  4. MySQL 在处理 varchar 时会对它占用的内存进行悲观假设,varchar 的最大长度不要超过实际需要

定长字符串/char:

  1. 适用于短小的定长字符串,譬如密码的 md5 数值
  2. 通常比 varchar 更高效,并且对修改友好

变长字节/varbinary:

  1. 和 varchar 类似,存放变长的二进制字符串

定长字节/binary:

  1. 和 char 类似,存放二进制字符串

注意事项:

  1. binary 字符串的比较操作比 char 字符串更高效
  2. 如果 varchar(5) 满足需求,不要使用 varchar(200),MySQL 内部处理会对需要的内存进行悲观假设,譬如临时表。

4、 文本

文本被作为一个单独对象处理,innodb 会把大的文本单独存放。

字符文本/text:

tinytext
smalltext
text  等同于 smalltext
mediumtext
longtext

二进制文本/blob:

tinyblob
smallblob
blob 等同于 smallblob
mediumblob
longblob

注意事项:

  1. 对文本进行比较,MySQL 只比较最前面 max_sort_length 个字符
  2. 不能建立索引
  3. 不要把图片存放成 blob,建议存到磁盘上,在数据库里记录位置

5、时间

datetime:

  1. 时间范围广 1000~9999年,精度毫秒,8 字节,日期部分 YYYYMMDDHHMMSS 存储为整数,和时区分开存放
  2. 跨时区使用时,显示存入的字面时间,不转换

timestamp:

  1. 1970 年开始的秒数,GMT 时间,精度为秒,4 字节,最多到 2038年1月19日
  2. 跨时区使用时,显示在当前时区的时间,会转换

注意事项:

  1. 为了避免 timestamp 的 2038 问题,直接用整数类型存放时间戳,unsigned 32-bit 支持到 2106 年

6、 主键的选择

  1. 设定主键后,其它表引用主键时,一定要用同样的定义,避免额外转换开销或转换错误
  2. 主键类型占用的空间应尽可能小
  3. 优先选择整数类型,避免使用字符串
  4. 避免使用完全随机数,随机会导致数值分布空间大,数值分布不可预测,影响缓存加载,增加插入和查询开销
  5. 如果要存放UUID,转换成16字节的数字存放

注意事项:

  1. ORM 以及一些框架自动生成的表定义,很可能使用了不合理的字段类型

7、 其它注意事项

  1. 表的列数如果有上百行,会带来大量的 cpu 开销,即使只使用几列,innodb 也需要解析所有数据列
  2. 太多表 join 会成为严重问题,MySQL 设置的上限为 61 张表
上一篇:bitset的应用例子


下一篇:一路谈谈锁