读薄《高性能MySQL》(一)MySQL基本知识
读薄《高性能MySQL》(二)Scheme与数据优化
读薄《高性能MySQL》(三)索引优化
读薄《高性能MySQL》(四)查询性能优化
选择更优的数据类型
当我们设计数据类型的时候应该选择最优的数据类型,因为好的数据类型会使数据库性能提升很多,特别是在使用 ORM 的时候要尤其小心,因为需求的复杂性,ORM 基本上没什么可能会生成最优的类型。
接下来介绍一些通用的数据类型结构。
更小的通常更好
一般情况下,应该尽量使用存储数据最小的数据单类型
尽量使用自带的数据类型
比如保存日期最好用 mysql 内有的日期类型而不是用字符串保存时间,除非有特殊需求。
尽量避免 NULL
MySQL 有些优化只会对 NOT NULL 列执行,当然这个优化性能有限,只是在设计的时候要注意到而已。
当然也有例外,InnoDB 在 bit 数据上使用稀疏矩阵,使用 NULL 能节省空间。
整数类型
整数类型可选 TINYINT,SMALLINT,MEDIUMENT,INT,BIGINT,分别占用 8,16,24,32,64 位存储空间。还可以选择无符号数,可以表示的数字范围翻倍。
MySQL 可以显式指定位数,但是这个在大多数情况下是没有意义的,因为比如说指定 INT(11) 也只是修改了终端显示的位数,不会改变 INT 是 32 位的这个事实。
实数类型
实数是带有小数的数字,FLOAT,DOUBLE 支持浮点数的近似计算。如果需要精确计算需要用到 DECIMAL,它可以指定精度,但是计算比较慢。
字符串类型
字符类型在 MySQL 4.0 版本以及以下指,VARCHAR(10),指的是 10 字节,只能存放 3 个汉字。
在 MySQL 5.0 版本及以上,指的是 10 字符,能存放 10 个汉字。
VARCHAR
最多能存放 65532 个字符。
VARCHAR 用于存储可变长字符串,它比定长更节省空间,因为只用必要的空间。但是 VARCHAR 需要额外 1 到 2 字节记录长度。但是由于执行 update 操作需要重新计算长度,所以更新会更耗时。
CHAR
定长字符串,最多能存放 255 个字符,无论保存的字符串有多长,占用的硬盘大小都是固定大小。
如何选择合适的字符类型
如果字符串平均长度比最大长度小很多,列更新很少,或者使用了 UTF-8 这样复杂的字符集,这种情况下用 VARCHAR 是合适的。
当保存很短的字符串,或者所有的字符串都接近于一个长度的时候可以考虑用 CHAR。
VARCHAR 和 CHAR 还有一个重要的区别就是 CHAR 会把字符串末位空格截断而 VARCHAR 不会。
枚举类型
有时候一些固定的字符串可以用枚举类型来代替,比如说性别的男女。**枚举排序的时候会按照枚举类型的数字值来进行排序,而不是对名字的字符顺序排序的。**枚举的映射信息保存在表的 .frm 文件中。
枚举删除或者增加字符串必须使用 ALTER,这对大表是非常不友好的,使用只有确认不会修改这个字段了,才使用枚举类型。
日期和时间
DATETIME
这个类型能保存大范围的值,最大到 9999 年。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中。
与时区无关。
TIMESTAMP
时间戳,最大能表示到 2038 年。因为使用比较灵活,使用一般都推荐使用这个
位数据类型
在 MySql 5.0 前,BIT 和 TINYINT 是同一个类型,在 5.0 后,bit 就是另外一种类型了。BIT 列最大长度为 64 位。
MySql 把 bit 作为字符串类型,比如说当检索 '00111001' 的时候,MySql 将检索出来的值转为数字然后返回 ASCII 码对应的字符串。
对于大部分应用,最好不要使用 BIT 类型。如果想要在一个 bit 的存储空间中保存布尔类型,可以使用 CHAR(1)。
选择 id
因为 id 需要读入内存进行查找,如果 id 长度非常长的话,检索会因为增加了磁盘 IO 而慢很多。所以最好要选择满足值的范围下最小的数据类型。
对于标识列来说,整数是最好的选择,因为它很快并且可以使用 AUTO_INCREMENT。
对于字符串类型来说,应该尽量避免用它来作索引,它们比整数慢,而且对于完全随机的字符串来说,他们会写入不同的地方,降低查询和插入的速度。
MySql设计中的陷阱
不同的数据库在设计 schema 时会有不同的问题。下面来介绍一下 Mysql 下有特有的错误。
太多的列
MySql 查询的时候会在服务层和存储引擎层之间拷贝数据,列数特别多而查询需要的结果只有一小部分,那么所付出的代价是非常高的。
太多的关联
关联查询的代价是非常高的,如果需要关联最好在 12 个表之内关联。
设计缓存表和汇总表
缓存表保存了衍生的冗余数据,避免了实时计算。比如保存了过去一个小时内的点击量,在一个小时后定时 COUNT 一次即可。
一个比较有用的技巧就是,缓存表和主表使用不同的引擎,比如主表使用 InnoDB,缓存表可以使用 MyISAM,这样可以全文检索,还占用了更小的空间。有时会把整个表导出 MySql,插入到专门搜索系统比如 Luncene 中。
计数器表在更新的时候可能会碰到并发问题,这里有一个小技巧能增加计数的并发能力。
比如说要记录一篇文章点击量,点击量可以分裂为多行,当需要统计的时候就将各行的数据累加起来即可。
本文由 鸡米 创作,采用 知识共享署名4.0
国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为: Nov 14,2021