本文主要是介绍SQL优化系列 - MySQL表设计时要注意什么?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
前言
今天是5月的最后一天了,一边听着五月天的线上演唱会,一边整理下学过SQL相关的内容,之前有篇是针对SQL的知识点:https://blog.csdn.net/Totoro1745/article/details/106036161,接下来的优化系列也会不断的进行更新~
MySQL表设计时要注意什么?
1.为什么一定要设一个主键?
在不设主键的情况下,innodb也会生成一个隐藏列,作为自增主键。自己指定一个主键,在有些情况下,就能显式的用上主键索引,提高查询效率~
2.主键为什么最好用自增呢?
innodb 中的主键是聚簇索引。如果主键是自增的,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如果不是自增主键,那么可能会在中间插入,就会引发页的分裂,从而产生很多表碎片,所以用自增插入性能好~
3.主键为什么不推荐有业务含义呢?
主要有以下两个原因:
(1)因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义,那么主键就有可能发生变更。主键一旦发生变更,该数据在磁盘上的存储位置就会发生变更,有可能会引发页分裂,产生空间碎片。
(2)带有业务含义的主键,不一定是顺序自增的。那么就会导致数据的插入顺序,并不能保证后面插入数据的主键一定比前面的数据大。如果出现了,后面插入数据的主键比前面的小,就有可能引发页分裂,产生空间碎片。
4.表示枚举的字段为什么不用enum类型?
在工作中表示枚举的字段,一般用tinyint类型。
不用enum类型有两个原因:
(1)ENUM类型的ORDER BY操作效率低,需要额外操作
(2)如果枚举值是数值,有陷阱
5.货币字段用什么类型?
如果货币单位是分,可以用Int类型。如果坚持用元,用Decimal。
最好不要用float和double,因为float和double是以二进制存储的,所以有一定的误差,对于钱的东西要慎重!
6.时间字段用什么类型?
(1)varchar,如果用varchar类型来存时间,优点在于显示直观,但是坑的地方也是挺多的。比如,插入的数据没有校验,你可能某天就发现一条数据为2013111的数据,请问这是代表2013年1月11日,还是2013年11月1日?其次,做时间比较运算,你需要用STR_TO_DATE等函数将其转化为时间类型,你会发现这么写是无法命中索引的。数据量一大,是个坑!
(2)timestamp,该类型是四个字节的整数,它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间,是无法用timestamp类型存储的。但是它有一个优势,timestamp类型是带有时区信息的。一旦你系统中的时区发生改变,例如你修改了时区SET TIME_ZONE = “america/new_york”;你会发现,项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目,跨时区的应用时,特别注意!
(3)datetime,datetime储存占用8个字节,它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然,存储时间范围更大。但是它坑的地方在于,他存储的是时间绝对值,不带有时区信息。如果你改变数据库的时区,该项的值不会自己发生变更!
(4)bigint,也是8个字节,自己维护一个时间戳,表示范围比timestamp大多了,就是要自己维护,不大方便。
7.为什么不直接存储图片、音频、视频等大容量内容?
mysql中有两个字段类型被用来设计存放大容量文件,也就是text和blob类型。但是,我们在生产中,基本不用这两个类型~Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。导致查询效率缓慢。
8.字段为什么要定义为NOT NULL?
(1)索引性能不好
(2)查询会出现一些不可预料的结果(空值不会被计算)
参考资料:https://mp.weixin.qq.com/s/HPODDzEVofXwU9IOvi9dhw
这篇关于SQL优化系列 - MySQL表设计时要注意什么?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!