mysql数据库之schema与数据类型优化

数据类型的优化包含很多详细的点，这些点基本上是从高性能 mysql 这本书里总结出来的

一、更小更好

        就是我们在进行数据存储的时候，可以选择任意的类型进行存储，比如整型，可以使用 int，也可以使用 tinyint，还可以使用 bigint到底如何选择呢?应该尽量使用可以正确存储数据的一个最小数据类型，更小数据类型通常更快，因为越小的数据类型占用越少的磁盘空间、内存，而且处理的时候需要 CPU 周期比较少，但是要正确低估需要存储的一个数值范围。

二、简单就好

        简单数据类型的操作通常需要更少的 CPU 周期，例如，1、整型比字符操作代价更低，因为字符集和校对规则的比较是字符比整型更复杂，2、使用 mysql 自建类型而不是字符串来存储日期和时间 3、用整型存储IP 地址。

        使用 mysql 自建类型而不是字符串来存储日期和时间表示该使用什么类型就使用什么类型。举例说明说创建两张相同的表，改变日期的数据类型，查看SQL 语句执行的速度。创建一张 emp 表，emp 标配表里面 HIREDATE 是 date类型，然后创建第二个 emp2 表，emp2 表的 HIREDATE 字段是 varchar 类型创建完表之后我们可以做一个最基本的查询，看一下执行需要的时间。

三、劲量避免null

        这个其实很好理解，在 mysql 里面 null 是不等于 null 的，而且需要额外的列来描述是否允许为 null.大家还需要注意一件事，如果查询中包含可为 NULL 的列，对 mysql 来说很难优化，因为可为 null 的列会导致索引、索引统计和值比较都更加复杂，可为null的列会使用更多的存储空间，在 mysql 里也需要做特殊的处理。当可为 null的列被索引时，每个索引记录许需要一个额外的字节。但是坦白来说，通常情况下null的列改为 not null 带来的性能提升比较小，所有没有必要将所有的表的schema 进行修改，已有的数据库设计维持不变，新的数据库设计，尽量不要允许列为 null，但是在某些实际应用里面，比如说用户信息，你不可能强制让用户把所有信息都填完整，这不现实，这意味着我们数据库里面不可避免的要使用 null，因此能不使用null尽量不要使用。

四、具体细则

1、整形

        在整型的数据类型中包含 TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT各种各样的分类，然后分别使用 8 位、16 位、24 位、32 位、64 位进行实际数据存储。在创建表的时候尽量使用满足需求的最小数据类型，能够节省数据存储占用的空间。

2、字符和字符串类型

        一般在描述字符和字符串类型的时候包含四种，分别是 char (字符类型varchar (字符串类型)，blob (大对象)，text (长字符串)。

那么问题来了，这 4 种类型应该如何选择呢?

        一般情况下 varchar 用的比较多，是最常见的字符串数据类型，它比定长类更节省空间，因为它仅使用必要的空间，越短的字符串使用越少的空间。varcha.需要使用一个或者两个额外学节记录字符串的长度，如果列的最大长度小于或等255，那么使用一个字节，否则使用两个字节。它有如下优势:

第一，使用最小的符合需求的长度

第二，varchar5)与 varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小。

Varchar 有什么样的应用场景呢?

        第一个是存储长度波动较大的数据，如文章，有时会长有时会短;第二个是字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存我们的长度。

第三个是适合保存多字节字符，如汉字、如特殊字符

3、char

        char 是固定长度的字符串，最大长度 255，在进行实际存储的时候会自动删除末尾的空格。

        char 类型的字符串有什么样的应用场景呢?

是存储长度波动不大的数据，如: md5 摘要，在数据库里面存储密码的时候会进行加密操作，而加密算法一般是按照固定算法加密的，那么执行之后的结果也是一个固定长度的值，所以此时可以用 char 来进行存储

二是存储短字符串、经常更新的字符串。

4、Blob和text类型

        除了上述两种类型之外还有 TEXT 类型和 BLOB 类型。在 mysql 中是把BLOB和TEXT都当成独立对象进行处理，两者都为了存储很大的数据而设计的字符串类型，分别采用二进制和字符串方式进行存储。此处需要注意一件事，虽然mysgl 中支持了这样数据的存储，但是工作中用的是比较少的，原因在于这样长的字符串进行检索的时候效率比较低，一般都是单独进行存储。

5、datetime和timestamp

        在数据库中，我们经常会存储日期类型的数据，mysql 中支持的日期类型有种，分别是 datetime、timestamp、date。那么该如何选择呢?

Datetime 在进行存储的时候占用 8 个字节，与时区无关，数据库底层时区配置，对 datetime 无效，是说它跟时区是没有关系的，同时可以精确到毫秒，可保存的时间范围比较大。

Timestamp 在进行存储的时候占用 4 个字节，采用整型进行存储,秒，表示的时间范围是 1970年 1月1号到 2038 年1月19 号，精确到

Date 在进行存储的时候占用的字节数比使用字符串、datetime、int 要少，只需要 3 个字节，然后使用 date 类型还可以利用日期时间函数进行日期之间的计算，date 类型用于保存 1000-01-01 到 9999-12-31 之间的日期，所以 date 跟我们上面的 datetime 的范围是一样的，但是 date 存储占用的空间更少，而且这些日期类型在存储的时候，可以直接进行函数的比较。

6、使用枚举代替字符串类型

        在 Java 语言中包含枚举类型，可以将需要的值设置成枚举类，方便直接指定而不需要每次赋值，同样的，mysql 里也支持枚举，可以使用枚举来代替字符串类型。

7、特殊类型

        特殊类型在这里说的是IP 地址的存储，我们经常使用 varchar(15)来存储 ip地址，然而，它的本质是 32 位无符号整数不是字符串，可以使用 INETATON 和其他的特殊类型就没有别的点了要但是你要注意一点，类型这块其实非常重要，因为如果表类型设计比较好的话，表存储空间会占用的比较少，而这些设计不太好的话，会造成查询效率的降低。

8、合理适当的范式和反范式

        关于范式是什么，此处就不再赘述了，那么范式有什么优点?

1、范式化的更新通常比反范式更快

2、当数据较好的范式化后，很少或者没有重复的数据

3、范式化的数据比较小，可以放在内存中，操作比较快当前范式设计也有缺点，就是进行数据查询的时候必须要进行表关联，而且大家应该也看过阿里规范，里面有一个明确的说法就是不要超过三张表的关联操作。因为数据量比较大的话，join 过程是非常慢，所以给大家一个建议，根据实际情况或具体问题具体分析.量根本达不到那个层次，不要说严格就按照阿里规范来，因为可能你公司里面的数据你的 jin 操作是非常快的。

下面来聊一下反范式，优点如下:

1、所有的数据都在同一张表中，可以避免关联

2、可以设计有效的索引

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

在企业中为了能够更好的提高整个项目的运行效率，一般情况下范式和反范式是要配合使用的，或者说是混合使用的，你要根据你具体的应用场景来决定到底要不要用范式。

9、主键的选择

        大家在此处思考一个问题很多同学肯定会说这还不简单，自增的或者在前面加一个前缀就是在你们之前的业务中主键是如何创建的?很多创建一个 ID列并且把 ID 设成主键，同时 ID 是用 uuid 来做主键，这种方式肯定是没问题的，但是你要清楚两个概念: 代理主键，比如说订单表以 d 开头，用户表以  开头，或者和自然主键。

代理主键是与业务无关的，无意义的数字序列

自然主键是事物属性中的一个自然唯一标识.

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

推荐大家使用代理主键，原因是

第一:不与业务耦合，因此更加容易维护一点;

第二:设计通用的键策略，能够减少需要编写的源码数量，减少系统的总体拥有成本。

10、字符集的选择

        在创建数据库或者创建表的时候必须要指定字符集，在 mysql 中支持各种各样的字符集，那么该如何选择呢?有同学说这有什么可选择的，直接使用 ut-8就行了，但事实并非如此。如果你要存中文的话，不建议你设置成 utf8，因为中文支持的字节数可能是两个，也有可能是三个，也有可能是四个，而 ut8 只支持3个字节的存储。

因此建议在进行设置的时候最好设置成 utf8mb4 的格式同时不同的编码格式还有以下特性:

1、纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

2、如果我们可以确定不需要存放多种语言，就没必要非得使用 UTF8 或者其他 UNICODE 字符类型，这回造成大量的存储空间浪费。

3、MySQL 的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低IO 操作次数并提高缓存命中率。

11、存储引擎的选择

        存储引擎是很多同学经常容易忽视的一个问题，因为在绝大部分场景下大家不需要指定存储引擎，直接使用 mysql 默认的存储引擎即可。如果非要在建表的时候指定存储引擎怎么办呢?可以在建表语句下增加一个 engine 属性。如果不做任何设置的话，默认情况是 InnoDB 的，但是可以通过修改配置文件的方式进行修改操作。

下面我们来分别描述下他们的区别:

1、索引类型

数据文件跟索引文件放在一起的叫聚簇索引。数据文件跟索引文件不放在一起的叫非聚簇索引。innodb 存储引擎中既有聚索引也有非聚簇索引，而 myisam只有非聚簇索引。

2、是否支持事务

MyISAM 是不支持事务的，而 InnoDB 支持事务

3、支持表锁、支持行锁,

MyISAM默认情况下只支持表锁不支持行锁，而 InnoDB 它既支持表锁又支持行锁。其实这其中牵扯到一个粒度问题，在进行加锁控制的时候，肯定是粒度越小越好，粒度越小，锁定的数据范围越小，那么并行度越高，效率越高。

4、是否支持外键

MyISAM不支持外键，InnoDB 支持外键

5、是否支持全文索引

MyISAM 支持，InnoDB5.6 版本之后支持。全文索引很好理解，举个例子说明F:假设数据库里有一张表叫文章表，里面有一个列叫 content，表示内容的意思。如果想在当前表里检索出包含 Java 这个关键字的文章怎么办?此时就需要我们利用全文索引来进行分词操作了，但是在企业里面一般情况下不会用全文索引,当涉及到分词操作的时候会选择使用 es.

6、适合操作类型

Myisam 支持大量查询的操作，而 innodb 适合大量 insert、update、delete 操当然也没有绝对，要根据场景进行具体的分析.作，

聊了这么多，到底什么是存储引擎呢?存储引擎指的是数据文件在磁盘的组织形式，不同的存储引擎存放数据的时候格式是不一样的。

12、适当的数据冗余

        适当的数据冗余表示数据可能存储多份，下面详细说明下:1、如果被频繁引用且只能通过 Join 2 张(或者更多)大表的方式才能得到的独立小字段。

在进行数据库设计的时候，如果要遵循三范式的设计，那么数据冗余是不存在但在某些特殊情况下，必须要让数据进行一个冗余，例如有两张表，A 表、B的，表，A 表里面取了一个列，B 表里面取了一个列，每个表里面都有 n 多条数据那么 Join 的成本一定很高，所以可以把其中一张表的数据直接放到另外一张表中，避免关联查询。

2、由于每次 Join 仅仅只是为了取得某个小字段的值，Join 到的记录又大会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新

13、适当的拆分

        适当拆分不是指分库分表中的垂直切分和水平切分，垂直切分指的是按照业多来进行切分，有 多张表可以把不同的表放到不同的物理服务器上，这样的话不同的查询请求会被分散到不同的物理服务器上，减少单台服务器压力，水平切指的是按照某一个范围放到不同的物理服务器上，我们此处说的拆分并不是这两个方面。

适当拆分指的是当表中存在类似于 TEXT 或者 blo 类型的字段，但是大部分访问这张表的查询都不需要这个学段，那么就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 O 次数，也能大大提高内存中的缓存命中率。

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

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