手机

2023年中国光学膜产业链上中下游市场分析（附产业链全景图）

周末不出城也可赏苇打卡健身

家电

读高性能MySQL（第4版）笔记06_优化数据类型（上）

2023-09-12 10:07:31 来源：博客园

(资料图片仅供参考)

1.良好的逻辑设计和物理设计是高性能的基石

1.1.反范式的schema可以加速某些类型的查询，但同时可能减慢其他类型的查询

1.2.添加计数器和汇总表是一个优化查询的好方法，但它们的维护成本可能很

1.3.将修改schema作为一个常见事件来规划

2.让事情尽可能小而简单是一个好主意

2.1.尽量避免在设计中出现极端情况

2.2.使用小的、简单的、适当的数据类型，并避免使用NULL，除非确实是对真实数据进行建模的正确方法

2.3.尝试使用相同的数据类型来存储相似或相关的值，尤其是在联接条件中使用这些值时

2.4.注意可变长度字符串，它可能会导致临时表和排序的全长内存分配不乐观

2.5.如果可能的话，尝试使用整数作为标识符

2.6.小心使用ENUM和SET类型

2.7.避免使用BIT类型

3.选择正确的数据类型对于获得高性能至关重要

3.1.更小的通常更好

3.1.1.尽量使用能够正确存储和表示数据的最小数据类型

3.1.2.更小的数据类型通常更快，因为它们占用的磁盘、内存和CPU缓存的空间更少，并且处理时需要的CPU周期也更少

3.1.3.在schema中的多个地方增加数据类型范围是一个痛苦且耗时的操作

3.1.4.如果无法确定哪个数据类型是最好的，请选择你认为不会超过的最小数据类型

3.2.简单为好

3.2.1.简单数据类型的操作通常需要更少的CPU周期

3.2.2.整型数据比字符型数据的比较操作代价更低

3.2.2.1.字符集和排序规则（collation）使字符型数据的比较更复杂

3.2.2.2.应该将日期和时间存储为MySQL的内置类型而不是字符串类型

3.2.2.3.应该用整型数据存储IP地址

3.3.尽量避免存储NULL

3.3.1.最好指定列为NOT NULL，除非明确需要存储NULL值

3.3.2.如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂

3.3.3.可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理

3.3.4.可为NULL的列改为NOT NULL带来的性能提升比较小

4.整数类型

4.1.整数（whole number）

4.1.1.TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT

4.1.1.1.使用8、16、24、32和64位存储空间

4.1.2.整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍

4.1.3.有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据数据实际范围选择合适的类型

4.1.4.整数计算通常使用64位的BIGINT整数

4.1.5.对于存储和计算来说，INT（1）和INT（20）是相同的

4.1.6.一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可

4.1.7.存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里

4.1.8.同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

4.2.实数（real number，带有小数部分的数字）

4.2.1.不仅适用于带小数的数字，也可以使用DECIMAL存储比BIGINT还大的整数

4.2.2.浮点类型通常比DECIMAL使用更少的空间来存储相同范围的值

4.2.3.FLOAT列使用4字节的存储空间

4.2.4.DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围

4.2.5.应该尽量只在对小数进行精确计算时才使用DECIMAL

5.字符串类型

5.1.字符串长度定义的不是字节数，是字符数

5.2.VARCHAR

5.2.1.用于存储可变长度的字符串，是最常见的字符串数据类型

5.2.2.它比固定长度的类型更节省空间，因为它仅使用必要的空间

5.2.3.更少的空间用于存储更短的值

5.2.4.需要额外使用1或2字节记录字符串的长度

5.2.4.1.VARCHAR（1000）的列则需要1002个字节，因为需要2字节存储长度信息

5.2.5.节省了存储空间，所以对性能也有帮助

5.2.5.1.由于行是可变长度的，在更新时可能会增长，这会导致额外的工作

5.2.6.推荐使用场景

5.2.6.1.字符串列的最大长度远大于平均长度

5.2.6.2.列的更新很少，所以碎片不是问题

5.2.6.3.使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

5.3.CHAR

5.3.1.总是为定义的字符串长度分配足够的空间

5.3.2.当存储CHAR值时，MySQL删除所有尾随空格

5.3.3.如果需要进行比较，值会用空格填充

5.3.4.推荐使用场景

5.3.4.1.存储非常短的字符串

5.3.4.1.1.对于非常短的列，CHAR也比VARCHAR更高效

5.3.4.1.2.设计为只保存Y和N的值的CHAR（1）在单字节字符集中只使用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外字节

5.3.4.2.所有值的长度都几乎相同的情况

5.3.5.对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片

5.4.二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符

5.5.填充也不同：MySQL填充BINANRY用的是\0（零字节）而不是空格，并且在检索时不会去除填充值

5.6.字节比较的优势

5.6.1.大小写不敏感

5.6.2.二进制比较比字符比较简单得多，因此速度更快

5.7.BLOB和TEXT类型

5.7.1.存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储

5.7.2.字符类型

5.7.2.1.TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT

5.7.2.2.TEXT是SMALLTEXT的同义词。

5.7.2.3.有字符集和排序规则

5.7.3.二进制类型

5.7.3.1.TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB

5.7.3.2.BLOB是SMALLBLOB的同义词

5.7.3.3.二进制数据，没有排序规则或字符集

5.7.3.4.如果需要在检索后保持值不变，请小心使用BINARY类型，MySQL会使用\0将其填充到需要的长度

5.7.4.当BLOB和TEXT值太大时，InnoDB会使用独立的“外部”存储区域，此时每个值在行内需要1～4字节的存储空间，然后在外部存储区域需要足够的空间来存储实际的值

5.7.5.只对这些列的最前max_sort_length字节而不是整个字符串做排序

5.7.6.不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序

关键词：