MySQL数据库表结构优化方式详解

灰太狼 2024-03-16 13:28 98阅读 0赞

## 前言 ##

从今天开始本系列文章就带各位小伙伴学习数据库技术。**数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。**

--------------------

全文大约【**2083】** 字，不说废话，只讲可以让你学到技术、明白原理的纯干货！本文带有丰富案例及配图视频，让你更好的理解和运用文中的技术概念，并可以给你带来具有足够启迪的思考…

#### 文章目录 ####

*  前言
 *  一. 数据库结构优化
 *   *  1. 字段类型优化
     *  2. 垂直拆分表（垂直切割）
     *   *  2.1 拆分时机:
         *  2.2 拆分方法:
         *  2.3 优点 :
         *  2.4 缺点:
     *  3. 水平拆分表（水平切割）
     *   *  3.1 拆分时机:
         *  3.2 拆分方法:
         *  3.3 优点:
         *  3.4 缺点:
     *  4. 表分区
     *   *  4.1 目的
         *  4.2 录入使用key键值分区
 *  二. 结语

## 一. 数据库结构优化 ##

数据库的库表结构优化包含很多内容，例如：**字段类型优化、垂直分表、水平分表、表分区、分库等内容。** 下面就带各位小伙伴一起分析下具体内容。

### 1. 字段类型优化 ###

字段类型优化的目的是将字段类型设置的更合理一些，**我们总结的规则如下：**

>  *  尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
>  *  对于只包含特定类型的字段，可以使用enum、set 等数据类型。
>  *  数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
>  *  尽量使用TINYINT、SMALLINT、MEDIUM\_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
>  *  VARCHAR的长度只分配真正需要的空间。
>  *  尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
>  *  单表不要有太多字段，建议在20以内。
>  *  合理的加入冗余字段可以提高查询速度。

### 2. 垂直拆分表（垂直切割） ###

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

#### 2.1 拆分时机: ####

单表字段过多, 并且不需要一次性查询返回展示到一个页面上。

#### 2.2 拆分方法: ####

一个页面需要展示的数据字段, 尽量放在一张表中. 如果拆分成多个表, 那么这多个表的关系是1:1:1:1…的关系。

#### 2.3 优点 : ####

拆分后查询单表性能得到提升, 速度快。

#### 2.4 缺点: ####

如果拆分后, 需要查询跨多张表的数据就会用到join连接查询, 查询速度会比没拆分前慢。

### 3. 水平拆分表（水平切割） ###

水平拆分按照行进行拆分，常见的就是**分库分表**。

以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

#### 3.1 拆分时机: ####

MySQL单表大于500万条数据, 就需要考虑水平分表, 因为单表数据量越大查询越慢, 查询效率成指数级下降。

#### 3.2 拆分方法: ####

**根据业务拆分:**

根据时间范围拆分, 例如: 订单数据, 按照订单创建时间, 一个月一张表。

按照区间分为拆分, 例如: 按照表自增id, 1-300万条一张表。

按照业务分类拆分, 例如: 用户表按照男女拆分。

**hash分表:**

通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。普通hash算法和一致性hash算法均可。

一般企业利用工具来做水平分表：

**服务端分库分表: MyCat, corbar等。**

**客户端分库分表: shardingJDBC, ShardingSphere等。**

#### 3.3 优点: ####

提高了查询效率, 主要是在大量数据情况, 提高了查询的命中率。

#### 3.4 缺点: ####

提高了复杂度. 在数据量不大的情况, 水平分表体现不出好处。

### 4. 表分区 ###

#### 4.1 目的 ####

表分区给大家简明扼要的说就是**大量数据操作**, 会有磁盘IO跟不上的情况, 所以表分区, 就是将保存在硬盘上的数据文件放到一块或者多块硬盘上, 以提高数据库的IO性能。

分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

**MySQL主要支持4种模式的分区：Range分区、List预定义列表分区，Hash 分区，Key键值分区。**

#### 4.2 录入使用key键值分区 ####

举个栗子：  
![请添加图片描述][b1cc007a744641009eff9aa200a8aa11.png]

--------------------

## 二. 结语 ##

**最后在这里对本文核心要点进行总结:**

1.  各位小伙伴需要熟练记住垂直分表和水平分表的方案以及优缺点，在合适的时机进行灵活使用。
2.  垂直分表一定要慎用，用不好会造成表结构变得复杂化不利于查询。

--------------------

以上就是本文的全部内容，关于MySQL数据库相关知识点，欢迎和我们一起交流讨论~

[配套视频资料戳这里][Link 1]

[b1cc007a744641009eff9aa200a8aa11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/38686b52912e4b0789009568d2dffdc1.png
[Link 1]: https://www.bilibili.com/video/BV1qb4y1Y722?t=0.0