Mysql范式与数据类型选择
良好的逻辑设计与物理设计是高性能的基石,当我们在设计数据表结构的时候,应该跟根据业务逻辑来分析具体情况,然后设计出比较合理,高效的数据表结构
在数据表结构设计中,不得不提的就是范式
与数据类型
了
Mysql三范式
- 字段不可分;即字段具有原子性 字段不可再分,否则就不是关系数据库;
- 有主键,非主键字段依赖主键; 唯一性 一个表只说明一个事物
- 非主键字段不能相互依赖;每列都与主键有直接关系,不存在传递依赖
范式的优点与缺点
-
优点
- 范式化设计更新通常比反范式化更新要快
- 当数据高度范式化时,就只有少量或者没有重复数据,这样修改的时候就只需要修改少量的数据
- 范式化的表通常比较小,可以更好的放在内存里面,操作就会更容易
- 因为范式化设计之后,冗余数据较少,所以在执行某些查询的时候可能就不会用到
group by
,distinct
这样也提高了查询效率
- 缺点
因为严格遵循范式化设计的话。在某些业务场景下可能会查询多个表。这样的同样会使得查询效率变的很低。而且在某些时候因为多表查询的原因,可能某些索引不会被命中。
这里我们看到范式化的设计有优点也有缺点,所以在实际的项目中,我们通常是混范式化设计。
某些表完全遵循范式化;某些表遵循部分范式化设计。在设计某些表的时候 会用到反范式化的思想,将某些数据存到同一张表中。这样可以减少很多关联查询,也可以更好的去设计索引关系。
比如users
表 与user_messages
表中,都会保存一个user_account_type
字段。这样的话,在单独查询user_account_type=1
的消息总数时就不需要再去关联users
表了。
数据类型
Mysql支持的数据类型有很多种,所以选择正确的数据类型对提高性能有着至关重要的作用。但是不管哪种数据类型我们都应该参考下面几个原则
-
更小的通常更好
;更小的数据类型通常更快,因为他们占用更少的磁盘、内存、CPU缓存。 -
简单就好
;操作简单的数据类型通常需要更少的CPU周期。例如:整型比字符串操作代价更低 -
尽量避免NULL
;如果查询中包含可为Null
的列,对于Mysql
来说更难优化,因为可以为null
的列使得索引,索引统计和值都变的更加复杂
整数类型
Mysql
的整数类型有 TINYINT
,SMALLINT
,MEDIUMINT
,INT
,BIGINT
。他们使用到的储存空间分别是,8
,16
,24
,32
,64
位。值的范围是-2(N-1)到2(N-1)-1
整形可以选择UNSIGNED
属性,表示是否有符号,不允许为负值。
如 TINYINT UNSIGNED 值的范围 0~255, 而 TINYINT 值的范围是-128-127。需要注意点是有符号跟无符号使用相同的储存空间,拥有相同的性能,所以可以根据实际情况来选择类型。
字符串类型
VARCHAR
,CHAR
是两种最主要的字符串类型,
-
VARCHAR
类型用于储存可变字符串,是常见的字符串类型。它比定长类型更节省空间。 -
CHAR
定长字符串类型,分配固定长度的空间。在保存某些定长字符串时比VARCHAR
更有优势、比如md5
定长字符串,因为定长类型字符串不容易产生碎片。
对于VARCHAR(5)
和VARCHAR(100)
储存hello
的空间开销是一样的,那么是不是我们就可以定义长度为100
呢?当然不是了,更长的列会消耗更多的内存,因为Mysql通常会分配固定大小的内存块来保存内部值。所以最好的策略就是分配合理的长度,这样就分配到真正需要的 空间。
日期,时间类型
-
DATETIME
类型,能够保存1001-9999年,精度为秒,与时区无关。 -
TIMESTAMP
类型,保存了从1970-01-01午夜到现在的秒数,只使用了4个字节,只能表示1970-2038年。TIMESTAMP
依赖于时区。TIMESTAMP
在默认情况下,如果没有指定列的值,会把列的值设置为当前时间,在更新的时候也可以更新列的值为当前时间。
通常情况下应该尽量使用TIMESTAMP
,因为它比DATETIME
空间效率更高,有时候我们会将Unix时间戳保存为整数值以表示当前时间,实际上并不会带来任何收益。
上面列举了几个常用的mysql
类型,在实际使用中可以根据业务选择最优的方案。一般情况下遵循更小的通常更好
,简单就好
,尽量避免NULL
是没有问题的。
关于mysql的数据类型选择,就写到这里。后面也会写一些关于索引优化方面的文章,如果问题欢迎大家指出。
逸梦
一个PHP程序猿的文章栏目
推荐阅读
Golang Recover提高程序的健壮性
在GO语言中goroutine的开启是很方便的只需要一个关键词go func() 就可以开启一个协程。但是在协程中我们可能会遇到一些致命的错误,如果这个时候我们没有对这个错误进行处理,那么整个程序就会崩溃。而在Go中为解...
旧梦发癫阅读 4.5k
初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。
王中阳Go赞 4阅读 1.7k评论 2
Vue+Express+Mysql全栈项目之增删改查、分页排序导出表格功能
本文记录一下实现一个全栈项目,前端使用vue框架、后端使用express框架、数据库使用mysql。此项目的意义不仅仅有助于我们复习nodejs相关知识、更有助于带前端新人,使其快速从整体全局角度中,理解常规后台管理系...
水冗水孚赞 4阅读 2.6k
MySQL百万数据深度分页优化思路分析
一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看,最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万...
一个程序员的成长赞 7阅读 893
深入理解MySQL索引底层数据结构
在日常工作中,我们会遇见一些慢SQL,在分析这些慢SQL时,我们通常会看下SQL的执行计划,验证SQL执行过程中有没有走索引。通常我们会调整一些查询条件,增加必要的索引,SQL执行效率就会提升几个数量级。我们有没...
京东云开发者赞 3阅读 581
Laravel入门及实践,快速上手ThinkSNS+二次开发
【摘要】自从ThinkSNS+不使用ThinkPHP框架而使用Laravel框架之后,很多人都说技术门槛抬高了,其实你与TS+的距离仅仅只是学习一个新框架而已,所以,我们今天来说说Laravel的入门。
ThinkSNS赞 1阅读 2.4k
一文了解MySQL中的多版本并发控制
作者:京东零售 李泽阳最近在阅读《认知觉醒》这本书,里面有句话非常打动我:通过自己的语言,用最简单的话把一件事情讲清楚,最好让外行人也能听懂。也许这就是大道至简,只是我们习惯了烦琐和复杂。希望借助...
京东云开发者赞 2阅读 501
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。