mysql 数据类型高性能mysql笔记

最新推荐文章于 2024-03-03 16:56:38 发布

qq_25825923

最新推荐文章于 2024-03-03 16:56:38 发布

阅读量226

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/qq_25825923/article/details/83213066

mysql 专栏收录该内容

10 篇文章

订阅专栏

本文探讨了MySQL性能优化的关键策略，包括合理选择数据类型、避免过多的列和关联、处理NULL值等，强调了范式化与反范式化的优缺点，以及如何平衡读写操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.选择优化的数据类型

更小的通常更好。

应该尽量使用可以正确存储数据的最小类型，更小的数据类型通常更快，因为他们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期更少。

简单就好

更简单的数据类型的操作通常需要更少的CPU周期。例如，整型数字比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较相对整型数字比较更复杂。比如，应使用INTERGER存储IP地址（inet_aton）

尽量避免NULL

通常情况下，最好指定列为NOT NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较非常复杂，可为NULL的列会使用更多的存储空间，当可为NULL的列被索引时，每个索引记录需要一个额外的字节。但是把可为NULL的列改成NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

1.1整数类型

整数类型	占用空间	范围
TINYINT	8	[-2^7,2^7-1]
SMALLINT	16	[-2^15,2^15-1]
MEDIUMINT	24	[-2^23,2^23-1]
INT	32	[-2^31,2^31-1]
BIGINT	64	[-2^63,2^63-1]

整型类型有可选的UNSIGNED属性，表示不允许负值，可以使原本正数的上线提高一倍。有符号和无符号类型使用相同的存储空间，并具有相同的性能。整型之间相互计算，是以64位的BIGINT作为中间类型进行计算的。

1.2实数类型

实数是带有小数部分的数字，可以使用DECIMAL存储比BIGINT还大的整数。

DECIMAL类型用于存储精确的小数，支持精确计算。例如，DECIMAL(18，9)小数点两边将各存储9个数字，一共使用9个字节，其中小数点前面的数字使用。DECIMAL最多允许65个数字。

浮点类型在存储同样范围的值时，通常比DECIMAL占用更少的空间，内部计算时采用DOUBLE作为计算类型。

因为需要额外的空间和计算开销，尽量只在对小鼠进行精确计算时才使用DECIMAL，在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，讲需要存储的货币单位根据小数的位数乘以相应的倍数即可。

1.3字符串类型

varchar

varchar类型用于存储可变长字符串，比定长更节省空间，varchar需要使用1个或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255个字节，则只是用1个字节表示，否则使用2个字节。varchar节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，如果在UPDATE时增加了该边长列的实际存储长度，这就导致需要额外的工作，如果一个行占用的空间增长，并且在页内没有更多的存储空间可以存储，在这种情况下，InnoDB需要分裂页来使行可以放进页内。

varchar使用场合：1.字符串列的最大长度比平均长度大很多，列的更新很少

2.使用了UTF-8这种复合的字符集（每个字符都使用不同的字节数存储）

MySQL在存储和检索时会保留varchar尾部的空格。InnoDB可以把过长的VARCHAR存储为BLOB。

char

定长字符串，MySQL在存储时会去除char尾部的空格。会造成“A ”与“A”产生唯一性冲突。数据如何存储取决于存储引擎，填充和截取空格的行为是在MySQL服务层进行的。

更长的列会消耗更高的内存，MySQL通常会分配固定大小的内存来保存内部值，尤其是使用内存临时表进行排序或操作总是会特别糟糕。

blob

采用二进制的方式存储，没有排序规则和字符集。包含tinyblob,blob,mediumblob,longblob

text

采用字符串的方式存储，有排序规则和字符集，包含tinytext,text,mediumtext,longtext。

ENUM

枚举不推荐使用

注意:

BLOB 是二进制数据 text是字符串数据当二者过大时mysql会在该列保存一个1-4字节的内存地址然后在外部存储实际的值
varchar 的存储会额外多1或者2个字节存储字符串长度（字符串长度小于255字节用1）varchar的更新会产生更多碎片
Char 是定长的会自动去除字符末尾空格适合存储定长较短的更新频繁的数据如定长的Y和N (cahr类型不容易产生碎片)
Varchar(10) 比varhcar(100) 在查询时使用的空间更小速度更快(查询的时候是根据varchar的大小去分配内存的) 在设置varchar大小的时候要尽可能接近真实数据不要过大

1.4日期和时间类型

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000