MySQL数据类型优化

咸鱼味罐头

已于 2023-03-28 23:03:10 修改

阅读量112

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

于 2023-03-28 22:55:55 首次发布

本文链接：https://blog.csdn.net/qq_40926572/article/details/129804229

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

《高性能MYSQL》书籍关于MySQL数据类型优化部分总结——对应书籍第四章

一、Schema和数据类型优化
二、其他优化

一、Schema和数据类型优化

选择正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。
尽量避免NULL。可谓NULL的列使得索引、索引统计和值的比较都更复杂，但也没必要强制不用，有时候NULL会比某些特定常数更好。
整数类型
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，8、16、24、32、64位存储空间。
整数类型可以使用UNSIGNED属性，表示不允许负值，这可以使正数上限提高一倍。
MySQL可以为整数类型指定宽度，只是用来显示字符的个数，对于存储和计算来说，没有意义。
实数类型
实数是带小数部分的数字。FLOAT和DOUBLE类型使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。
如果数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的数据根据小数的位数乘以相应的倍数即可，可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
字符串类型
VARCHAR类型需要使用1或者2个字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。
BINARY和VARBINARY存储的是二进制字符串，和常规字符串相似，但是存储的是字节码而不是字符。
慷慨是不明智的。更长的列数据会消耗更多的内存，因为MySQL通常分配固定大小的内存块来存储内部值。尤其使用内存临时表、磁盘临时表进行排序或者操作。
VARCHAR优势：
1）字符串列的最大长度比平均长度大很多。
2）列的更新很少，所以更新数据导致的数据碎片问题不大。
3）使用像UTF-8这种复杂字符集，每个字符使用不同的字节数存储。
4）会保留末尾空格（4.1版本之前Mysql会剔除末尾空格）。
CHAR优势：
1）适合存储很短的字符串，或者所有值都接近同一个长度。
2）经常变更数据，定长的CHAR不容易产生碎片。
3）CHAR不会保留末尾的空格（因为会用空格填充存储长度）。
BINARY优势：
1）存储对大小写敏感，需要大小写来区分的数据。
2）比较更快，比较BINARY字符串时，每次按一个字节，并根据该字节的数值进行比较。
BLOB类型和TEXT类型。分别采用二进制和字符方式存储很大的字符串类型。MySQL对BLOB类型和TEXT类型排序是只对每列的最前MAX_SORT_LENGTH字节排序，或者使用ORDER BY SUBSTRING(column, length)来进行排序。
使用枚举（ENUM）代替字符串类型。MySQL内部会将每个值在列表中的位置保存为整数。枚举字段是按照内部存储的整数而不是定义的字符串来排序的，可以使用FIELD()函数显式地指定排序顺序。
日期和时间类型
DATETIME优势：范围大，1001年到9999年，精度为秒。时间格式为YYYYMMDDHHMMSS的整数，和时区无关，使用8个字节。
TIMESTAMP优势：TIMESTAMP只能表示1970年到2038年，精度为秒，和UNIX时间戳相同。和时区有关，使用4个字节。TIMESTAMP列默认为NOT NULL。
存储更小粒度的时间，可以使用BIGINT类型存储时间。
位数据类型
BIT类型最大长度是64位，BIT(1)存储1位，BIT(2)存储2位。MySQL会把BIT当作字符串类型，而不是数字类型。但在数字上下文则是将字符串转换成数字，进行比较的时候要注意，所以慎用BIT类型。
SET类型，很少使用。

二、其他优化

加快ALTER TABLE操作的速度
1）先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。
2）“影子拷贝”，用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表操作交换两张表（推荐）。
3）设置或删除列的默认值，用ALTER TABLE tablename ALTER COLUMN …，直接修改.frm文件，不会引起表重建（ALTER COLUMN、MODIFY COLUMN、CHANGE COLUMN操作不同）。
4）特殊操作修改.frm文件，执行结果要保证不会重建表，比如移除（不是增加）一个列的AUTO_INCREMENT属性、移除或修改ENUM和SET常量不会重建表。创建一个有相同结构的空表，并修改需要修改的字段；执行FLUSH TABLES WITH READ LOCK，这将会关闭所有正在使用的表，并且禁止任何表打开；交换.frm文件；执行UNLOCK TABLES释放读锁。
MySQL限制每个每个关联操作最多只能有61张表，最好单个查询限制在12个表内做关联。
注意MAX_HEAD_TABLE_SIZE或者TMP_TABLE_SIZE，超过以后MySQL会将内存临时表转换成磁盘临时表。如果EXPLAN执行计划的Extra列包含”Using temporary“，则说明这个查询使用了隐式临时表。