构建优化之城：MySQL 数据建模、数据类型优化与索引常识全面解析

前言

MySQL 调优是必备的技能，从系统层面来看，MySQL 基于磁盘交互，是它的瓶颈所在，大量依赖于可靠性、持久化操作，都需要经过 DB，基于它能有调优能力，能最大化提升自己的竞争力；该博文会从以下几方面来对 MySQL 调优作系统的介绍

1. 数据建模方案及数据类型优化
2. 索引优化
3. 大数据量查询优化
4. 海量数据解耦优化处理

数据建模方案、数据类型优化

存储引擎选择

存储引擎的选择是经常会被忽视的问题，因为在绝大部分场景下，都不需要指定存储引擎，直接会使用 MySQL 默认的存储引擎：InnoDB

若不作任何设置的话，默认的存储引擎就是 InnoDB，但也可以通过修改配置文件的方式进行修改操作，如：在 /etc/my.cnf 文件追加内容

[mysqld]
default-storage-engine=InnoDB

可以通过以上配置写上存储引擎类型，MySQL 支持的存储引擎如下：

之前在介绍 performance_schema 时，它本身就是一种存储引擎，但是在日常工作中 InnoDB、MyISAM 用的最多，它们两者的区别如下：

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是（5.6 后支持）
适合操作类型	大量 SELECT	大量 Insert、Delete、Update

1. 索引类型：数据、索引文件放在一起的叫做聚簇索引，数据、索引文件不放在一起的叫做非聚簇索引，InnoDB 存储引擎中既有聚簇索引也有非聚簇索引，而 MyISAM 只有非聚簇索引
2. 支持事务：MyISAM 不支持事务，InnoDB 支持事务
3. 支持表、行锁：MyISAM 默认情况下只支持表锁、不支持行锁，但 InnoDB 它既支持表锁也支持行锁，这其中就牵扯到一个锁粒度问题，在进行加锁控制时，肯定是粒度越小越好；粒度越小，锁定的数据范围越小，即并行度越高，效率越高。所以一般在实际代码开发中，进行事务操作时，会把大事务以编程式事务的方式去进行处理，能够精确控制，小事务使用声明式事务处理即可.
4. 支持外键：MyISAM 不支持外键，InnoDB 支持外键
5. 支持全文索引：MyISAM 支持，InnoDB 5.6 版本之后才支持，全文索引很好理解，举例说明如下：

假设，数据库中有一张文章表，里面存在一个列 content，代表内容的意思，若在当前表里需要检索出包含 java 关键字的文章，此时可以利用全文索引的方式来进行分词操作，但是在企业里面一般情况下不会使用全文索引处理，当涉及到分词操作时会选择使用 ES.

6. 适合操作类型：MyISAM 支持大量查询操作， InnoDB 适合操作大量 Insert、Update、Delete 操作

合理使用范式、反范式

范式：会遵循第三范式原则设计表结构，不会产生多的冗余列，即都会进行 Join 表查询；反范式：在表中新增冗余列避免多查一张表，现在的主流设计都是 范式+反范式 设计结合使用

范式优点如下：

1. 范式化的更新通常比反范式更快
2. 当数据模型较好的范式化后，很少或没有重复的数据
3. 范式化的数据比较小，可以放在内存中，操作比较快

范式的缺点：必须要进行表关联查询

反范式优点如下：

1. 所有的数据都在同一张表中，可以避免关联
2. 可以设计有效的索引

当然反范式也有缺点，表内冗余的字段较多，删除数据时会造成表有些有用的信息丢失

总结：在企业中为了能够更好的提高整个项目的运行效率，一般情况下范式、反范式是要配合使用的，或者说是混合使用的

1. 在一个网站实例中，网站允许用户发送消息，并且一些用户是付费用户；若想要查看付费用户最近 10 条信息，在 user、message 表中都存储在用户类型（account_type）而不用完全的反范式化；这避免了完全反范式化的插入、删除问题，因为即使没有消息时也不会丢失用户的信息；这样也不会把 user_message 表搞太大，有利于高效地获取数据
2. 从父表冗余一些字段数据到子表的理由是排序的需要
3. 若缓存衍生值也是有用的，若需要显示每个用户发了多少消息（类似论坛的）可以每次执行一个昂贵的子查询来计算并显示它，也可以在 user 表建一个 num_message 字段，当用户发送消息时更新这个值即可.

字符集选择

一般在设置字符集时都是默认给它指定 utf-8，若你要存储中文时，不建议设置成 utf-8，因为中文支持的字节数可能是 2 个、3 个、4 个，而 utf-8 只支持 3 个字节存储，因此在进行插入 emoji 表情，如：😊 就会有问题，报错内容 > Incorrect string value: '\xF0\x9F\x91\x87' for column

因此，建议在进行设置时最好设置成 utf8mb4 指定 column 字符集，同时不同的编码格式还有以下特性：

1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间
2. 若我们可以确定不需要存放多种语言，就没有必要非要使用 utf8 或其他 Unicode 字符串类型，这会造成大量的存储空间浪费
3. MySQL 数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据时，可以通过对不同表不同字段使用不同的数据类型来较大程度减少数据存储量，进而降低 IO 操作次数并提高缓存命中率

一般在实际开发过程中，会指定字符列字符集：CHARACTER SET utf8mb4，字符排序规则：COLLATE utf8mb4_general_ci
出现两表关联时，若表中的列字符集或排序规则不一致时，就会出现错误 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='，此时可以在 join on 字段后面指定 > COLLATE utf8mb4_unicode_ci 来完成 SQL 的正确执行，但这种方式会难免降低 SQL 执行效率，从而在表设计时指定好字符集、字符排序规则是一件很重要的事情！

主键选择

代理主键与事物无关的，无意义的数字序列
自然主键时事物属性中一个自然的唯一标识

举例说明：若要存储用户信息，要设置一个唯一标识，可以设置一个无意义的 id，也可以直接使用身份证号，身份证号就是一个自然主键，普通 ID 就是一个代理主键

推荐大家采用代理主键，原因如下：

1. 不与业务耦合，因此更容易维护一点
2. 设计通用的键策略，能够减少需要编写的源码数量，减少系统的总体维护成本

适当数据冗余

适当的数据冗余，表示数据可能存储多份，详细说明如下：

1. 若被频繁引用只能通过 Join 两张表或更多大表的方式才能获取到独立的小字段内容信息
2. 由于每次 Join 仅仅只是为了小字段的值，Join 到的记录又不大，会造成大量不必要产生的 IO，完全可以通过空间换时间的方式来进行优化；不过，冗余的同时需要确保数据的一致性不会被破坏，确保更新基础表的同时冗余字段也要被更新

举例说明：
一张 4 百万的会员表，它以会员手机号或系统生成的唯一值来区分用户，此时还有一张 4 百万的会员等级表（一个会员对应一个等级）此时需求若要查询出前 100 条会员的基础信息以及其对应的等级信息，即使，100 条已经限制了，但它仍然会扫描 Join 大量的数据，会造成数据库查询效率极速下降；若此时【等级 id、等级名称】冗余到会员表中，那么 SQL 只需要查询一张表即可，同时搭配上 LIMIT 关键字，可谓天作之合，唯一的缺陷就是在更新会员等级表信息的同时需要更新会员表冗余的等级字段信息

select m.id,m.name,m.phone,ml.level_id,ml.level_name 
from member m 
left join member_level ml on ml.phone=m.phone
limit 100

举例说明：
一张活动基本表，一张活动参与记录表，而活动表的信息一旦创建好以后，它的活动名称就不能再进行变更，此时，可以在活动参与表中直接冗余活动名称，后续在 C 端用户查询活动的参与信息时就不需要再通过活动 id 去关联查询活动基本表的活动名称字段。

适当拆分

适当拆分 > 反例：指的不是分库分表中的垂直拆分、水平拆分，垂直拆分指的是按照业务来进行切分，有 N 张表可以把不同的表放到不同的物理服务器上，这样做的话，不同的查询请求会被分散到不同的物理服务器上去，减少单台服务器的压力；水平拆分指的是将数据按照某一个范围放入到不同的物理服务器上，此处说的适当拆分并不是指的这两个方面

适当拆分 > 正例：当表中存在类似于 TEXT 或 Blob 类型的字段，大部分访问这张表的查询并不需要这种类型字段，那么就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间，这样做的明显好处：每个数据块中可存储的数据条数将大大增加，既减少了物理 IO 交互次数，同时也能大大提高内存中的缓存命中率。

比如：一张表中里面有 100 多个字段，但这 100 多个字段可能常用的也就 10、20、30 个，有剩下好几十个字段可能压根用不到，但这些字段又占用了较大的存储空间，此时可以把这些不常用的数据拆分出来；若需要查询了，就可做关联查询，若不需要查询了，就不需要做关联查询

该点业务量比较大或者表数据字段比较多时是必须要做的，这样操作会带来查询效率成倍数的提升！

数据类型优化

更小更好

首先第一点通常是更小更好，在我们进行数据存储时，可以选择任意的类型进行存储，比如：整型 > 可以使用 int、tinyint，还可以使用 bigint，我们应当尽量使用存储数据的正确数据类型，更小的数据类型通常更快；因为越小的数据类型占用越少的磁盘空间、内存，而且处理时需要的 CPU 周期也比较少，但是要正确评估需要存储的一个数据范围

比如：
1、一般的名称、描述可以基于产品给出的 PRD 文档描述，最大长度不可超过多少长度，那么就可以设置长度为这个最大数
2、状态枚举类型：尽量使用 tinyint 来存储，tinyint(1) 代表一个字节，足以存储逻辑删除 > 0、1，若存在多种状态，根据状态的最大数量计算好它需要多少字节再设置即可，弊端：枚举类型值使用字符串的方式去存储，是一种很不明智的行为，虽然能提高数据的可读性，但浪费了数据库的存储空间，最大可能又会数据的误操作
3、对接第三方接口，当我们要存储这些第三方返回的信息时，第三方对应的接口文档也有描述最大长度，根据字段的数据类型选择合适的长度

简单就好

简单的数据类型通常需要更少的 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 存储占用空间更少，而且这些日期类型在存储时，可以直接进行函数的比较

	datetime	date	timestamp	int（无符号）	int（有符号）
存储空间	8 字节	4 字节	4 字节	4 字节	4 字节
显示格式	yyyy-MM-dd HH:mm:ss	yyyy-MM-dd	时间戳	时间戳	时间戳
时间范围	1000-01-01～9999-12-31	1000-01-01～9999-12-31	1970-01-01～2038-01-19	1970-01-01～2038-01-19	1970-01-01～2106-02-07
操作效率	相对 int 低一点	相对 int 低一点	相对 int 低一点	高	高
跨时区	无	无	MySQL 转化时区	无	无

关于数据库时区经常会出现的问题，数据库与实际的时间少了八小时，注意三者的时区配置
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，订阅一波不再迷路

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