数据库查询性能优化方法1：设计最优的库表结构

查询性能优化，有3个方法。

• 如何设计最优的库表结构

• 如何建立最好的索引

• 合理的设计查询

库表结构优化、索引优化、查询优化需要齐头并进，一个不落。

在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。

提升性能的两个方法：空间换时间，随机转顺序

方法2：建立最好的索引，参考：数据库查询性能优化方法2：建立最好的索引

方法3：合理设计查询语句，参考：数据库查询性能优化方法3：合理设计查询语句

本篇为方法1：设计最优的库表结构

设计最优的库表结构

主要就是选择合适的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要

1. 数字类型

有两种类型的数字：整数（whole number）和实数（real number）。

如果存储整数，可以使用这几种整数类型TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16, 24, 32, 64 位存储空间。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT，UNSIGNED可以存储的范围是0～255，而TINYINT的存储范围是-128～127。对于非负型数据，要优先使用unsigned来存储。

int(2)这种写法没有意义

MySQL 为了兼容性支持很多别名，例如INTEGER、BOOL,以及NUMERIC。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE 检查，会发现MySQL 报告的是基本类型，而不是别名。

例：应该用整型存储IP地址。一个IPv4地址。人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示表示只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。

将字符串转化为数字类型存储 如 将ip地址转换为数据，利用INET_ATON或INET_NTOA

inet_aton('255.255.255.255')=4294967295
inet_ntoa(4294967295) = '255.255.255.255'

参考：mysql 使用inet_aton和inet_ntoa处理ip地址数据

实数类型

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT 里，这样可以同时避免浮点存储计算不精确和DECIMAL 精确计算代价高的问题。

DECIMAL可以用于存储比bigint更大的整数数据

如果存储的数据范围超过decimal 的范围， 建议将数据拆成整数和小数并分开存储。

DECIMAL(18,9)需要4个字节 小数点 4个字节 最多65个数字

财务类数据 推荐使用DECIMAL

2. 日期类型

DATETIME类型

YYYY-MM-DD HH:mm:SS[.fraction] 格式存储日期时间

5.6以后才支持微秒类型 datetime(6) 设置宽度

与时区无关，占用8个字节的存储空间

时间范围1000-01-01 00:00:00到9999-12-31 23:59:59

mysql5.6 添加了 date类型 和 time类型，date类型：1000-01-01到9999-12-31之间的日期

TIMESTAMP类型

1970年1月1日到当前时间的描述

YYYY-MM-DD HH:mm:SS[.fraction] 占用4个字节

2038-01-19

显示上与DATETIME相同，但更节省空间

timestamp类型依赖于所指定的时区

在进行数据修改时可以自动修改timestamp列的值，标识每列的最后修改时间

set time_zone= '+10:00'
create table t(d1 datetime, d2 timestamp)
alter table t modify d1 datetime(6) d2 timestamp(6)

insert into values(now(),now(),now())
-- 只有第一个timestamp列默认是可以自动更新的

如何存储用户生日

• 方案1：把日期部分存储为字符串（至少要8个字节）
• 方案2：使用int类型类存储（4个字节）
• 方案3：使用datetime类型来存储（8个字节）
• 方案4：使用date类型（3个字节）

存储日期时间数据的注意事项

1. 不要使用字符串类型来存储日期时间数据，应该使用MySQL 内建的类型而不是字符串来存储日期和时间 date, time, datatime

   • 日期时间类型存储空间小
   • 日期时间类型在进行查找过滤时可以利用日期来进行对比，如果是字符串类型，那么只能通过字符集的排序规则来顺序查找
   • 日期时间类型有着丰富的处理函数，可以方便的对日期类型进行日期计算

2. 使用int存储日期时间不如直接使用Timestamp类型，其本质上就是int类型，只是显示的时候用YYYY-MM-DD 来展示

3. char类型

字符串存储在char类型中的列中会删除末尾的空格

最大宽度为255，超过255，只能使用varchar

使用场景

1. char类型适合存储长度近似的值

   如MD5值，身份证，手机号

2. 适合存储短字符串

   char(1) 性别 男 或 女 只需要3个字节（汉字UTF-8）

3. char适合存储经常更新的字符串列，减少存储碎片

例：CHAR 非常适合存储密码的MD5 值，因为这是一个定长的值。对于经常变更的数据， CHAR 也比VARCHAR 更好，因为定长的CHAR 类型不容易产生碎片。对于非常短的列， CHAR比VARCHAR 在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

如果使用的是utf8mb4多字符集，最大的字符如表情占用4个字节，中文一般3个字节，英文一般1个字节，varchar和char表示的都是字符，而不是字节。也就是说char(10)可能占用10~40个字节

那么字符集为utf8mb4，那col3 char(10)插入bb，填充多少个空格呢？其实其填充的是最小字节数，所以会填充8个space，这样就看出了在多字符集的情况下，char失去了其优势，char也变为变长的了。定长类型的好处是可以进行原地更新。

4. VARCHAR类型

列的最大长度小于255则只占用一个额外字节用于记录字符串长度

列的最大长度大于255则要占用两个额外字节用于记录字符串长度

varchar(65535) 这种定义是不行的，65535是一个行中所有varchar类型共享的最大宽度，所以如果接收很大的内容，那么应该使用text类型

12345的反馈是不是应该使用text类型？另外前后端是不是也要对输入的字符数进行限制

varchar存储的字符，而不是字节，存储utf-8 10个汉字，那么就直接定义10，而不是30

如何确定varchar的宽度？

使用最小的符合需求的长度，这需要了解业务

用户姓名 中文 应该不会超过10个字符，但如果有外国名，应该不会超过20个字符，用varchar(255)就比较大

业务上线，修改列宽度，5.7以前都是要锁表的，5.7是在不超过255的时候是不需要锁表的

varchar(5)和varchar(200)存储‘mysql’字符串，都只需要6个字节，性能上有啥不同？mysql为了更有效的优化查询，所以在内存中对字符串使用的是固定的宽度，特别是在使用一些隐式的内存表的时候，更是这样，所以使用200会占用更大的内存

varchar适用场景

1. 字符串列的最大长度比平均长度大很多

2. 字符串列很少被更新的数据，所以碎片不是问题

   经常更新可能引起存储页的分裂，产生存储碎片

3. 使用了多字节字符集存储字符串

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

注意事项

1. 一般情况下，应该尽量使用可以正确存储数据的最小数据类型

   例如只需要存0-200, tinyint unsigned 更好

   当一个列可以选择多种数据类型时，如何选择？

   • 优先考虑数字类型

   • 其次是日期或二进制类型

   • 最后是字符类型

   对于相同级别的数据类型，优先选择占用空间小的数据类型

   查询时经常会用到比较，innodb中一页16kb，较小会在单页中容纳更多的内容，节省磁盘IO

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

2. 尽量避免NULL

   如果定义表结构时没有指定列为NOT NULL，默认都是允许为NULL 的。

   如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。索引NULL列需要额外的空间来保存，所以要占用更多的空间（索引空间占用越少越好，这样一页可以放更多的索引）

   进行比较和计算时要对NULL值做特别的处理

   NULL在最底层页中的存储形式，不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不会占用任何存储空间，其是使用一个NULL标志位来存储，比如NULL标志位是06，转换成二进制为00000110，有1的值代表第2列和第3列的数据为NULL。

   我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL. 也许可以使用0、某个特殊值，或者空字符串作为代替。

   但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。在一些场景中，使用NULL 可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值，例如用-1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug，还可能会让事情变得一团糟。处理NULL 确实不容易，但有时候会比它的替代方案更好。

3. 避免使用ENUM数据类型

   枚举类型的不足

   • 修改ENUM值需要使用ALTER语句
   • ENUM类型的ORDER BY操作效率低，需要额外操作
   • 禁止使用数值作为ENUM的枚举值

4. 避免使用TEXT、BLOB数据类型

   64k，建议把BLOB或者TEXT列分离到单独的扩展表中

   TEXT或BLOB类型只能使用前缀索引