Schema与数据类型优化

曾经终败给现在 2022-05-24 08:11 215阅读 0赞

## 数据库设计时更优的Tips ##

> 选择优化的数据类型

1.更小的通常更好  
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。因为它们占用更少的磁盘。内存和CPU缓存，并且处理时需要的CPU周期也更少  
2.简单就好  
简单数据类型的操作通常需要更少的CPU周期  
3.尽量避免NULL  
通常情况下最好指定列为not null。  
通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以这并不是调优的首选项。如果计划在列上建索引，就应该尽量避免设计成可为NULL的列

> 一些数据类型的优化选择

1.整数计算一般选择64位的BigInt。  
2.实数类型一般选择double，如果是财务系统或者需要小数精确运算的则需要decimal  
3.字符串一般选择varchar变长字符串，比定长字符串更节省空间，因为它仅使用必要的空间。char适合存储很短的字符串，或者所有值都接近同一个长度  
4.BLOB和TEXT为存储大数据而设计的字符串数据类型，分别采用二进制和字符方式存。。MySQL不能将BLOB和TEXT列的全部长度建立索引  
5.日期datetime 8个字节，timestamp 4个字节，前者保存更大的范围  
6.选择整数类型作为标识符类型，因为快且可以使用AUTO\_INCREMENT  
7.特殊数据类型：IP地址，人们通常使用VARCHAR（15）列来存储IP地址，然而它们实际上只是32位无符号数，不是字符串，所以应该使用无符号整数存储IP地址。MySQL提供INET\_ATON()和INET\_NTOA()函数在这两种表示方法之间转换

> MySQL schema设计中的陷阱

1.太多的列。  
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。  
2.太多的关联  
MySQL限制每个关联操作最多只有61张表。一个粗略的经验法则，如果希望查询执行得快且并发性好，单个查询最好在12个表以内做关联  
3.防止过多的枚举

> 范式和反范式

*  范式

优点：  
范式化的更新快；重复数据很少或者基本没有，所以只需要修改更少的数据；表通常更小，可以更好的放在内存里，所以执行操作会更快  
缺点：  
通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。

*  反范式

优点：  
很好的避免关联，查询会更快；单独的表也能使用更有效的索引策略。  
缺点：  
数据重复，所以修改复杂；更新也会慢；表通常会比较大

> Alter Table

大部分情况下，它都会锁表并且重建整张表。