简单就好

简单的数据类型通常需要更少的 CPU 周期，例如：

1. 整型比字符串操作的代价更低，因为字符集及校对规则的比较，字符串比整型更复杂
2. 使用 MySQL 自建的类型，比如：时间 > date、datetime、timestamp，而不是用字符串来存储日期和时间
3. 使用整型存储 IP 地址

尽量避免 NULL

在 MySQL 中 NULL 是不等于 NULL 的，而且需要额外的列来描述是否允许为 NULL，若查询中包含了可为 NULL 的列，对 MySQL 来说很难优化，因为可为 NULL 的列会导致索引、索引统计、值都更加复杂，可为 NULL 列会使用更多的存储空间；在 MySQL 里也需要做特殊的处理，当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节

使用 COUNT 聚合函数进行计数时，会发现数据统计不准确的情况，明明有 41 条数据，但计数后值为 40

原因：COUNT(字段) 会把字段值为 NULL 忽略不计，不作计数，COUNT(1) 用于统计数据的行数，NULL、NOT NULL 字段值都会统计进来

已经有的数据库设计维持不变，新的数据库设计，尽量不要允许列为 NULL

具体优化细节

以下开始聊一下具体数据类型优化的细节

整型

在整形的数据类型中，包含了 tinyint、smallint、mediumint、int、bigint 各种各样的分类，分别使用了 1 位、2 位、3 位、4 位、8 位字节来进行实际数据的存储，在创建表时尽量使用满足需求的最小数据类型，能够节省数据存储占用的空间

字符、字符串类型

一般在描述字符、字符串类型时包含了四种：char(字符类型)、varchar(字符串类型)、blob(大对象)、text(长字符串)

一般情况下 varchar 用的比较多，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅仅使用必要的空间，越短的字符串会使用越少的空间。varchar 需要使用一个或者两个额外的字节来记录字符串的长度，若列的最大长度小于或等于 255，那么就使用一个字节（1 字节 = 8 bit = 255）否则就使用两个字节

varchar 特点及应用场景如下：

1. 使用符合需求的最小长度
2. varchar(5)、varchar(255) 保存同样的内容，磁盘存储空间相同，但内存空间占用不同，是指定的大小 5 或 255
3. 存储长度波动较大的数据，如：文章，有时会长有时会短，在不确认最大长度时
4. 字符串很少更新的场景，每次更新后都会重算并使用额外的存储空间去保存我们的长度
5. 适合保存多字节的字符，如汉字、特殊字符

char 特点及应用场景如下：

1. 固定长度的字符串，最大长度 255，在实际存储时会自动剔除末尾的空间
2. 存储长度波动不大的数据，如：MD5 摘要，加密后的结果是一个固定长度的值 > 用户密码
3. 存储短字符串、经常更新的字符串

Blob、Text 类型

在 MySQL 中是把 Blob、Text 都当成独立对象进行处理，两者都是为了存储很大的数据而设计的字符串类型，分别采用二进制、字符串的方式进行存储，此处需要注意：虽然 MySQL 支持了这样数据的存储，但是在工作中用的是比较少的，原因在于这样的长字符串在进行检索时效率特别低，一般都是单独进行存储，可以直接放入到文件系统中，在数据库中保存文件的地址，然后读取时直接取出地址即可；如图片的 Base64 编码或特别长的地址时

datetime、timestamp

在数据库中，经常会存储日期类型的数据；MySQL 支持的日期类型有三种，分别是：datetime、timestamp、date

1. datetime：在存储时占用了 8 个字节，与时区无关，数据库底层时区配置，对 datetime 无效，是说它与时区是没关系的，同时可以精确到毫秒，可保存的时间范围比较大，时间范围（1000-01-01 ～ 9999-12-31）
2. timestamp：在存储时占用 4 个字节，采用整型进行存储，精确到秒，表示的时间范围（1970-01-01 ～ 2038-01-19）int 无符号也是如此
3. date：在存储时占用的字节数比使用字符串、datetime、int 要少，只需要占用 3 个字节，然后使用 date 类型还可以利用日期时间函数来进行日期之间的计算，date 类型的时间范围（1000-01-01 ～ 9999-12-31）所以 date 与我们上面的 datetime 范围是一样的，但是 date 存储占用空间更少，而且这些日期类型在存储时，可以直接进行函数的比较

关于数据库时区经常会出现的问题，数据库与实际的时间少了八小时，注意三者的时区配置

1、数据库 url > jdbc:mysql://url/db_name?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true&autoReconnect=true，配置：serverTimezone 为上海的

2、实体字段配置 Json 格式化注解：@JsonFormat(pattern = DatePattern.NORM_DATETIME_PATTERN, timezone = “GMT+8”)

3、查询服务器时区配置

[root@trench ~]# date -R
Mon, 15 May 2023 22:33:58 +0800
# 查看服务器时区 > Asia/Shanghai
[root@trench ~]# timedatectl
      Local time: Mon 2023-05-15 22:37:51 CST
  Universal time: Mon 2023-05-15 14:37:51 UTC
        RTC time: Mon 2023-05-15 22:37:51
       Time zone: Asia/Shanghai (CST, +0800)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: yes
      DST active: n/a
# 修改配置以后 > 运行：timedatectl 命令以后再查看时区即正常了
[root@trench ~]# sudo timedatectl set-timezone 'Asia/Shanghai'

枚举代替字符串类型

Java 语言中包含枚举类型，可以将需要的值设置成枚举类，方便直接指定而不需要每次都赋值；同样，MySQL 里面也支持枚举类，可以使用枚举来代替字符串类型，如以下 SQL 所示：

mysql> create table enum_test(e enum('apple','banana','orange') not null); 
Query OK, 0 rows affected (0.07 sec)
mysql> select e+0 from enum_test;
Empty set (0.01 sec)
mysql> insert into enum_test(e) values('apple'),('banana'),('orange');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select e+0 '枚举'  from enum_test;
+--------+
| 枚举   |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.00 sec)
mysql> select e '枚举'  from enum_test;
+--------+
| 枚举   |
+--------+
| apple  |
| banana |
| orange |
+--------+
3 rows in set (0.01 sec)

特殊类型

在这里特殊类型说的是 IP 地址的存储，经常会使用 varchar(15) 来存储 IP 地址；然而，它的本质在于 32 无符号整数而不是字符串，可以使用 INET_ATON(expression)、INET_NTOA(expression) 函数这两种表示方法之间相互转换，如下 SQL 所示：

mysql> select INET_ATON('1.1.1.1') as IP;
+----------+
| IP       |
+----------+
| 16843009 |
+----------+
1 row in set (0.00 sec)
mysql> select INET_NTOA('16843009') as IP;
+---------+
| IP      |
+---------+
| 1.1.1.1 |
+---------+
1 row in set (0.00 sec)

其他的特殊类型就不举例了，但要注意的是，类型这块比较重要，若类型设计比较好的话，表存储空间会占用的比较少，而这些设计不太好的话，会造成查询效率的降低！

索引优化

减少服务器扫描的数据量、避免排序及临时表、随机 IO 变为顺序 IO

索引用处

1. 快速查找匹配 WHERE 子句的行
2. 若可以在多个索引之间进行选择，MySQL 会使用成本最少的索引
3. 若表具有多列索引，则优化会使用索引的任何最左前缀来查找行
4. 当有表 Join 连接时，从其他表检索行数据
5. 查找特定索引列的 MIN、MAX 值，因为索引的数据结构中本身在叶子结点就是具有排序功能的，所以可以直接获取到最大或最小值
6. 在某些情况下，可以优化查询来检索值而无需查询数据行，直接在二级索引 B+ 树返回所有的要检索列

索引分类

一般在进行索引分类时可以分为以下几类，下面对每一种索引类型作一个详细的解释

1. 主键索引：当创建表时若包含主键，那么数据库会默认给主键列给索引，一般情况下表都是具有主键索引的 > 一级索引、聚簇索引、主键索引
2. 唯一索引：表中唯一列添加的索引
3. 普通索引：又称为二级索引或辅助索引，表示的是除了主键、唯一键添加的索引
4. 全文索引：一般在 varchar、char 或 text 类型才会建立全文索引，全文索引类似于全文检索；例如：一张文章表，里面有一个列 > content，全文索引类似于全文检索，若需要检索包含 Java 关键字的数据，此时使用 like 效率一定会很低，因此可以使用全文索引，有点像开源框架 ES，但在企业级应用的一般很少 > 全文索引
5. 前缀索引：当要创建索引的字符串比较长时，可以考虑使用索引前缀来创建索引，提高数据检索的效率
6. 组合索引：一般在创建索引时都会选择单列，在某些情况下需要给多个列添加一个索引，此时就形成了组合索引

技术名词

回表

若在二级索引 B+ 树，查询不到要查询的数据，那么还需要回到一级|主键 B+ 树去查询需要的数据，此时就叫回表

覆盖索引

覆盖索引跟回表是相反的概念，在二级索引 B+ 树叶子结点中能获取到所有列的数据，无需回表的过程称为覆盖索引，执行如下 SQL 语句：

# explain 执行计划可以看出 Extra 列 > using index
SELECT id,name FROM table where name='vnjohn';

通过分析可以得知，在 name 索引树叶子结点中，存在 id、name 两个列的值，通过 name 检索，可以直接在二级索引树获取到所有需要查询的列，而不再需要去一级索引树中查询

最左匹配

在一张表中，若有组合索引，那么组合索引在进行查询时，遵循最左匹配原则，表示必须要匹配到第一个列侯才会去匹配第二个列，无法直接匹配第二个列值

一个表中存在 id、name、age、gender 四个列，id > 主键，name、age > 组合索引，那么在进行查询时回包含以下四条 SQL 语句：

SELECT * FROM table WHERE name='vnjohn' and age=18;
SELECT * FROM table WHERE name='vnjohn';
SELECT * FROM table WHERE age=18;
SELECT * FROM table WHERE age=18 AND name='vnjohn';

在上诉四条 SQL 语句中，只有第三条不会用到组合索引，原因：没有匹配到 name 就直接匹配 age 了，所以无法使用到索引

索引下推

表明的意思是数据筛选的过程下移到存储引擎来完成，而不是在 server 层完成

MySQL 架构包含了三层次：客户端、server 端、存储引擎，若要执行如下 SQL 语句，在有无索引下推特性时，它的执行过程会有所不同

SELECT * FROM table WHERE name='vnjohn' AND age=18;

在没有使用索引下推之前，会先通过 name 值从存储引擎中将所有符合条件的结果加载到 server 层，然后在 server 层对 age 字段进行条件筛选

在使用索引下推之后，会通过 name、age 值直接在存储引擎中返回所有符合条件的结果，无需在 server 层再做任何数据筛选工作

对比两种方式，不使用索引下推时，server 层与存储引擎之间交互的数据 IO 量一定很多，所以使用索引下推能够提升整体的查询效率，该特性在 MySQL 5.7 版本之后默认支持，无需作任何的设置

总结

该篇博文，先着重讲了数据库设计方面的优化 > 存储引擎、合理使用范式以及反范式、表结构适当拆分避免过于臃肿、字符集选择，最重要的是在数据表中数据类型优化部分，应用了一些 MySQL 语法糖🍬，数据类型的选择、数据类型长度的可控性，最后，先简单介绍了索引优化部分的一些名词以及索引分类，下篇博文会继续来整体介绍如何使用索引进行优化、索引的数据结构是怎样的，大数据量查询优化、海量数据解耦优化处理 部分在一定程序上也依赖于索引，当然最大程度上还是要基于业务程序代码来控制的

如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！

推荐专栏：Spring，订阅一波不再迷路

大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！