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

亚囡

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数据类型的完整字符串放入索引，也不能使用索引进行排序


来源:https://www.cnblogs.com/lying7/p/17693240.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！