MySQL大数据量下数据类型选择与Schema设计

最新推荐文章于 2024-03-02 16:00:55 发布

wenping4316

最新推荐文章于 2024-03-02 16:00:55 发布

阅读量472

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/wenping4316/article/details/90106445

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在当代互联网背景下，海量的数据存储是我们当今最需要考虑的，如何在MySQL中存储大数据量，良好的逻辑设计和物理设计是基础，不良的设计使其表的维护成本成倍增加，MYSQL独有的特性和实现细节对性能的影响也很大。

一、选择优化的数据类型

1、更小的通常更好

在设计MYSQL表时，应该使用可以正确存储数据的最小数据类型。更小的数据类型占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

2、简单就好

简单的数据类型操作通常需要更少的CPU周期，例如，整形比字符串操作代价更低，字符集和较对规则（排序规则）使字符比较比整数更复杂

3、尽量避免NULl

如果查询中包含NULL的列，对MQSQL更难优化，因为NULL列使索引、索引统计、和值的比较更加复杂，NULL还占用更多的存储空间

首先，我们先要了解一个基础知识点，就是下面这张表：（摘自W3C教程）

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1字节（8位）	（- $2^{7}$ ， $2^{7}$ -1）	（0， $2^{8}$ -1）	小整数值
SMALLINT	2字节（16位）	（- $2^{15}$ ， $2^{15}$ -1）	（0， $2^{16}$ -1）	大整数值
MEDIUMINT	3字节（24位）	（- $2^{23}$ ， $2^{23}$ -1）	（0， $2^{24}$ -1）	大整数值
INT	4字节（32位）	（- $2^{31}$ ， $2^{31}$ -1）	（0， $2^{32}$ -1）	大整数值
BIGINT	8字节（64位）	（- $2^{63}$ ， $2^{63}$ -1）	（0， $2^{64}$ -1）	极大整数值
FLOAT	4字节（32位）	（-3.40E+38，3.40E+38）		单精度、浮点数值
DOUBLE	8字节（64位）	（-1.79E+308，1.79E+308）		双精度、浮点数值
DECIMAL	对DECIMAL(M,D),如果M>D，为M+2否则为D+2			小数值

VARCHAR和CHAR

上面这个表格主要总结了数值类型的大小以及范围，还有两种很重要的类型字符串类型VARCHAR和CHAR。

他们两个是主要存储字符串类型，因为存储引擎的不同，他们在磁盘和内存中存储的方式也不相同

VARCHAR用于存储可变长字符串，它比定长类型更节省空间，因为它仅仅使用必要的空间，VARCHAR需要使用1或2个额外字节记录字符串长度：如果列的长度消息或等于255字节，则使用1个字节表示，否则使用2个字节。VARCHAR节省了存储空间，所以对性能也有帮助。CHAR是定长的，适合存储很短的字符传，或者所有值都接近同一个长度。例如，CHAR非常适合存储MD5值，因为它是一个定长的值。CHAR（1）来存储Y和N值采用单字节字符集只需要一个字节，VARCHAR（1)则需要两个字节。

使用VARCHAR(5) 和VARCHAR(200)存储“Hello” 的空间开销是一样的。那么使用更长的列会消耗更多的内存，因为MQSQL会分配固定大小的内存快来保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕，所以再好的策略是之分配真正需要的空间。

BLOB和TEXT

BLOB和TEXT,这种字符串类型根据经验尽量不要使用，如果非常使用可以单独建一张表存储，id建关联，不用每次都去查询

DATETIEM和TEMPSTAMP

DATETIME这个类型能保存大范围的值，从1001到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8字节的存储空间。TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，使用4个字节存储，因此它的范围比DATETIME小很多：只能表示1970到2038年。默认情况下，如果插入是没有指定第一个TIMESTAMP列的值，Mysql则设置这个列的值为当前时间。TEMPSTAMP列默认为NOT NULL ,这也和其他数据类型不一样

除了特殊行为，通常也尽量使用TEMPSTAMP，因为它比DATETIEM空间效率更高

二、MySQL schema的设计

1、避免太多的列

mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成数据结构的操作代价是非常高的。

2、不要太多的关联

一个粗略的检验法则，如果希望查询执行得快速且并发性好，单个查询在12个表以内关联

3、避免使用默认值NULL

如果默认值为NULL,可能会使你的索引效果和预期得不一样，这个后边介绍索引的时候会详细介绍。

4、范式和反范式的混用

三、总结

MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：

尽量避免过度设计，列如会导致极其复杂查询的schema设计，或者有更多列的表的设计
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能的避免使用NULL值
尽量使用相同的的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
注意可变成字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
尽量使用整形定义标识列
避免使用MySql已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度
小心使用ENUM和SET,虽然他们使用起来很方便，但是不要滥用，否则有时候会变成陷阱，最好避免使用BIT
范式是好的，但是反范式有时也是必需的，并且可能带来好处

wenping4316

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL大数据量下数据类型选择与Schema设计

在当代互联网背景下，海量的数据存储是我们当今最需要考虑的，如何在MySQL中存储大数据量，良好的逻辑设计和物理设计是基础，不良的设计使其表的维护成本成倍增加，MYSQL独有的特性和实现细节对性能的影响也很大。一、选择优化的数据类型 1、更小的通常更好在设计MYSQL表时，应该使用可以正确存储数据的最小数据类型。更小的数据类型占用更...
复制链接

扫一扫

专栏目录