【高性能Mysql 】读书笔记(二)

第4章 Schema 与数据类型优化


本文为《高性能Mysql 第三版》第四章读书笔记,Mysql版本为5.5

选择优化的数据类型

选择合适数据类型的三个原则

  • 更小的通常更好 - 速度更快,占用更少
  • 简单就好 - 简单数据类型占用更少的CPU周期,例如整型的比字符串操作代价更低
  • 尽量避免NULL - 查询包含NULL的列,对Mysql来说更难优化,因为会使得索引,索引统计和值比较更为复杂

整数类型

整数的类型有:TINYINT 、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用8,16,24,32,64位存储空间

它们存储的值的范围:-2(N-1) 到 2(N-1)- 1

其中N为存储空间的位数

Mysql可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了一些交互工

具用来显示字符的个数而已,对于存储和计算来说,INT(1) 和 INT(20) 是相同的

实数类型

在mysql的数据类型中浮点型分为两种,float()与double()类型,定点型为decimal()

数据类型(M,D) -》M:精度,数据的总长度; D:标度,小数点后的长度;

其区别在于:

  • 当不指定精度时,Float、Double默认会保存实际精度,而Decimal默认是整数
  • 当标度不够时,都会四舍五入,但Decimal会警告信息

字符串类型

VARCHAR 和 CHAR是最主要的字符串类型,CHAR自不必说,实际使用的情况较少,例如存储男/女,YES/NO等确定长度的字符串,但是这种固定的情况有时候用整型去存储效率更高,所以视情况而定吧

VARCHAR存储的是可变长字符串,它比定长类型更节省空间,因为它仅使用必要的空间,它需要用1个或者2个额外字节记录字符串长度

BLOB和TEXT类型:

  • BLOB:二进制存储,没有排序规则和字符集
  • TEXT:字符串存储,有排序规则和字符集

当BLOB和TEXT值太大时,InnoDB会使用专门的“外部”存储区域来进行存储,此时每个值在行内都需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值

日期和时间类型

对于日期和时间类型,据我了解到身边的人大多都不会把时间直接存储到数据库中,同时《高性能Mysql》一书中也推荐另一种做法去存储时间,在这里推荐一下,即:

通过BIGINT类型存储毫秒/微秒级别的时间戳,再显示或者计算的时候都基于时间戳进行计算

选择标识符

选择标识列(identifier column)类型时,不仅要考虑存储类型还要考虑如何进行计算和比较,一旦选定了一种类型,还要确保所

有关联表中使用同样的类型,类型之间需要精确匹配(包括UNSIGNED这样的属性)

整数通常是ID列最好的选择。

使用MD5(),SHA1(),UUID()产生的字符串的值会随机分布在很大的空间中,导致INSERT和一些SELECT语句变得很慢:

  • 插入值随机写到索引的不同位置,导致页分裂,磁盘随机访问等,详见第五章
  • 逻辑上相邻的行会分布在磁盘和内存的不同地方
  • 随机值使得缓存赖以工作的访问局部性原理失效。

存储UUID值应该移除“-”符号;最好使用UNHEX()函数将UUID值转换为16字节的数字,存储在BINARY(16)列中。检索时可以通过HEX()函数格式化成十六进制格式

特殊类型数据

例如:IPV4地址,人们经常使用 VARCHAR(15)列来存储IP地址。然而,它们实际上是32位无符号整数,不是字符串。用小数点将地址分

成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址,MYSQL提供INET_ATON()和 INET NTOA()函数在这

两种表示方法之间转换

MySQL schema 设计中的陷阱

我们应该避免以下几种情况的出现:

  • 太多的列
  • 太多的关联(上限61张表)
  • 全能的枚举,变相的枚举
  • 随随便便的NULL

范式和反范式

反范式的标志:信息冗余

随着时代和机器的发展,我们会经常使用空间换时间的策略,因此基本淘汰了完全遵循范式的做法,但是在范式与反范式中间一定

要根据业务需求做好设计,减少不必要的空间浪费

缓存表和汇总表

利用Mysql做缓存的可能很少,用作汇总表的可能很多,提供原文中两个场景的较好的解决方案:

例如,如何更好的汇总一天中任务执行次数?

我们可以采用分割的思想,把一天划成小时,一天过去进行数据汇总时全部累加即可

例如,如果统计网站访问人数更合适?

我们当然可以用一条记录总人数,也可以用N天(条)数据记录总人数,然后累加,但是Mysql在执行时候有一定的延时,可能一秒

之内有好几十个人点击,那我们可以针对一条数据进行分割成1-100条数据,通过算法求余等等,让100条数据可以均匀的一起工作

这样可以大幅度增加效率,最终再汇总即可

加快ALTER TABLE操作的速度

不知道其他公司对于底层数据库的字段是否会经常调整,反正我们公司每次需求都会涉及数据库字段的调整,每次都需要执行

ALTER操作,如何提高效率?

  • 主从库切换,减少ALTER影响时间
  • “影子拷贝”,完全创建新表,然后通过重命名+删除的方式替换旧表(无数据的情况)
上一篇:python3绝对路径,相对路径


下一篇:CentOS 6.5安装配置LNMP服务器(Nginx+PHP+MySQL)