数据库表设计

字节

首先需要达成共识的是：1个Byte字节等于8个bit位。bit是最小一级的信息单位，可以表示一个0或1（即二进制）；

字符

字符其实是一个统称，字母、数字、运算符号、标点符号和其他符号，以及一些功能性符号都属于字符。比如：&、中、A。

那到底一个字符占用多少字节呢？这个问题其实取决于数据库所采用的编码格式。

一般来说一个英文字符（A、b）都占用1个字节。在UTF8编码下，一个中文汉字占3个字节；在GBK编码下，一个中文汉字占用2个字节。

完了，gg了！     ==> 七个字符

hello,世界      ==> 八个字符

整数型

整数类型一共有5种，包括 TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和 BIGINT

                                                                           有符号整型范围值                     无符号整型范围值

TINYINT                1bytes        小整数值          -128 ~ 127                                    0 ~ 255 

SMALLINT             2bytes        大整数值          -32768 ~ 32767                            0 ~ 65535 

MEDIUMINT         3bytes         大整数值          -8388608 ~ 8388607                    0 ~ 16777215 

INT和INTEGER      4bytes         大整数值          -2147483648 ~ 2147483647        0 ~ 4294967295 

BIGINT                  8bytes         极大整数值       -2^63 ~ 2^63-1                           0 ~ 2^64-1

 

1、整数型的数值类型已经限制了取值范围，有符号整型和无符号整型都有，而M值并不代表可以存储的数值字符长度，它代表的是数据在显示时显示的最小长度；

2、当存储的字符长度超过M值时，没有任何的影响，只要不超过数值类型限制的范围；

3、当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。

        举例子，如果你设置int(11)，那么有个字段值是123，那么这个值在显示宽度上是3位，而设计的是显示的是11位，所以这时候，你如果在字段设计的时候，选择zerofill就可以发现，123变成了00000000123，也就是剩下的8位用0补足了。

所以我们在设计mysql数据库时，建表时，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

所以，就用这些默认的显示长度就可以了。不用再去自己填长度，比如搞个int(10)、tinyint(1)之类的，基本没用。而且导致表的字段类型多样化

字符串型

CHAR(M), VARCHAR(M)

    CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

    VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。

    CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。

    总结一下，从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点，所有当需要大量查询需求的时候，用char。当对于保存数据量过大的需求时，为了节省储存空间用varchar。

TEXT

    最大长度为65,535(2的16次方–1)字符的TEXT列。

    Text主要是用来存放非二进制的文本，如论坛帖子,题目，或者百度知道的问题和回答之类。TEXT列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。其实可以总结为用来储存大批量的文本信息的时候，使用TEXT。

总结

1，长度的区别，char范围是0～255，varchar最长是64k，如果遇到了大文本，考虑使用text，最大能到4G。

2，效率来说基本是char>varchar>text。

3，char和varchar可以有默认值，text不能指定默认值。

 

时间型

DATETIME：类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值，支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

DATE：类型用在你仅需要日期值时，没有时间部分。MySQL检索并且以'YYYY-MM-DD'格式显示DATE值，支持的范围是'1000-01-01'到'9999-12-31'。

TIMESTAMP：列类型提供一种类型，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。

TIME：数据类型表示一天中的时间。MySQL检索并且以"HH:MM:SS"格式显示TIME值。支持的范围是'00:00:00'到'23:59:59'。

datetime和timestamp的区别：

    1.datetime 的日期范围比较大；如果有1970年以前的数据还是要用datetime.但是timestamp 所占存储空间比较小。

    2.timestamp类型的列还有个特性：默认情况下,在 insert,update数据时，timestamp 列会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。

    3.timestamp比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。

 

    UNSIGNED ZEROFILL：刚说过了，数字型字段当存储的字符长度小于INT(M)中的M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。

    timestamp有两个属性，分别是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP两种，使用情况分别如下

1，CURRENT_TIMESTAMP 

当要向数据库执行insert操作时，如果有个timestamp字段属性设为

 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间

2，ON UPDATE CURRENT_TIMESTAMP

当执行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP属性。则字段无论值有没有变化，它的值也会跟着更新为当前UPDATE操作时的时间。

 

字段的索引以及设置

先说下对索引的最简单的理解：

如果你给一个唯一性的字段加上索引，比如一个字段是房间号，你不设置字段的情况下去搜索RoomNumber=101，那么数据库会去一条一条的搜索数据库中的RoomNumber字段，直到找到101给你返回，但如果你把RoomNumber设置为索引，那么数据库就会直接找到RoomNumber=101这条信息给你返回。

所以索引的作用是加快数据库搜索的效率，但是同样的，你给一个字段设置为索引，是要消耗资源的。

普通索引(INDEX)：最基本的索引，没有任何限制。

唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。

全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时耗空间。

 

1.为什么长度几乎相等的字符串要选char而不是varchar？

比如性别(gender)这个字段，只有两个值(男、女)，就一个字符，为什么用char(1),而不是varchar(1)?

因为char(1)就真的是只开辟了1个字符大小，而varchar(1)不是只开辟一个字符大小，varchar需要单独记录字符串长度的大小需要额外空间

2.为什么在字段设计的时候有的人设置gender为tinyint,有点设置gender为char(1)?

在设计表的时候可以通过0和1来标识男还是女，也可以通过 '男' 和 '女' 来标识，可是有什么区别呢?

这里就涉及到一个概念了，数据库字段长度的表示到底是 字符长度 还是 字节长度 ？

其实在MySQL中，varchar和char类型表示字符长度 ，而其他类型表示的长度都是字节长度

举个例子，'男' 如果用字符长度来表示就是1，但是用字节长度来表示就是3个字节 ，在UTF-8下中文基本都是三字节

而用0和1来标识男女就只需要一个字节

 

3.主键设计要合理

主键设计的话，最好不要与业务逻辑有所关联。有些业务上的字段，比如身份证，虽然是唯一的，一些开发者喜欢用它来做主键，但是不是很建议哈。主键最好是毫无意义的一串独立不重复的数字，比如UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等

 

4.选择合适的字段长度

我们在设计表的时候，需要充分考虑一个字段的长度，比如一个用户名字段（它的长度5~20个字符），你觉得应该设置多长呢？可以考虑设置为 username varchar（32） 。字段长度一般设置为2的幂哈（也就是2的n次方）。

 

为什么字段长度一般要设置为2的幂呢？

这是因为数据库在存储数据时，会使用位运算来处理字段的长度，而位运算对于2的幂次方比较方便。如果字段长度不是2的幂哈，则需要使用算术运算来处理，这样会降低数据库的性能。

 

此外，设置字段长度为2的幂哈也有助于减少数据库的内存使用。数据库会根据字段长度来分配内存空间，如果字段长度不是2的幂哈，则需要使用更大的内存块来存储，这样会造成内存的浪费。

 

因此，为了提高数据库的性能和优化内存使用，一般建议将字段长度设置为2的幂哈。如果确实需要更长的字段长度，可以使用变长字段类型（如VARCHAR）来适应不同的长度需求。

 

varchar(20)和varchar(255)有什么区别？

 

通常情况下使用varchar(20)和varchar(255)占用的空间都是一样的,但是使用索引长度有所不同。

 

int(1)和nt(11)有什么区别？

 

在MySQL中，对于int类型的字段，设置其长度并不是指存储的数字位数或最大值的大小，而是指显示宽度。

 

也就是说，长度设置对实际存储的数据范围和占用空间并无影响，它主要影响的是数据的显示格式。

 

举个例子：

 

当长度设置为1的时候，表示当该字段值被查询并显示时，MySQL会为这个整数值分配至少1个字符的宽度。如果实际值的位数小于1，MySQL会在前面补足空格以达到指定的显示宽度；如果实际值的位数大于1，则按照实际位数显示。例如，一个值为123的INT字段，若设置长度为1，实际显示时仍会完整显示为“123”，不会截断。

当长度设置为11的时候，同理，表示当该字段值被查询并显示时，MySQL会为其分配至少11个字符的宽度。同样，如果实际值的位数小于11，前面补足空格；若大于11，则按实际位数显示。

那为什么会看到有人喜欢设置int(11)呢？

 

最大可能位数： INT类型的最大可能位数是10位（不含正负号），即从-2,147,483,648到2,147,483,647。设置长度为11可以确保任何在这个范围内的整数值在显示时都不需要额外的填充空格。即使数值较小，如只有1位或2位，也能保证有足够的宽度来容纳可能出现的最大位数，使得数据显示整齐且易于阅读。

 

5.优先考虑逻辑删除，而不是物理删除

什么是物理删除？什么是逻辑删除？

 

物理删除：把数据从硬盘中删除，可释放存储空间

逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。

物理删除就是执行delete语句，如删除account_no =‘xxx’的账户信息SQL如下：

 

delete from account_info_tab whereaccount_no =‘666’;

逻辑删除呢，就是这样：

 

update account_info_tab set is_deleted = 1 where account_no =‘666’;

那么为什么推荐逻辑删除而非物理删除？

    1)、为什么不推荐使用物理删除，因为恢复数据很困难

    2)、物理删除会使自增主键不再连续

    3)、核心业务表 的数据不建议做物理删除，只适合做状态变更。

6.每个表都需要添加几个通用字段如主键、create_time、update_time

表必备一般来说，或具备这几个字段：

 

id： 主键，一个表必须得有主键，必须

create_time： 创建时间，必须

modifed_time: 修改时间，必须，更新记录时，需要更新它

version : 数据记录的版本号，用于乐观锁，非必须

remark ：数据记录备注，非必须

modified_by :修改人，非必须

creator ：创建人，非必须

7.尽可能的使用not null定义字段

如果没有特殊的理由， 一般都建议将字段定义为 NOT NULL 。

 

为什么要这样做呢？

    1)、首先，NOT NULL 可以防止出现空指针的问题

    2)、其次，NULL 值的存储也需要额外的空间，它也会导致比较运算更为复杂，使优化器难以优化SQL

    3)、NULL 值有可能会导致索引失效

8.设计表时，评估哪些字段需要加索引

首先，评估你的表数据量。如果你的表数据量只有一百几十行，就没有必要加索引。否则设计表的时候，如果有查询条件的字段，一般就需要建立索引。但是索引也不能滥用：

 

索引也不要建得太多，一般单表索引个数不要超过5个。因为创建过多的索引，会降低写得速度。

区分度不高的字段，不能加索引，如性别等

索引创建完后，还是要注意避免索引失效的情况，如使用mysql的内置函数，会导致索引失效的

索引过多的话，可以通过联合索引的话方式来优化。然后的话，索引还有一些规则，如覆盖索引，最左匹配原则等等。

假设你新建一张用户表，如下

 

CREATE TABLE user_info_tab (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `age` int(11) DEFAULT NULL,

  `name` varchar(255) NOT NULL,

  `create_time` datetime NOT NULL,

  `modifed_time` datetime NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于这张表，很可能会根据 user_id 或者 name查询用户信息，并且，user_id是唯一的，因此是可以给user_id加上唯一索引，name加上普通索引

 

CREATE TABLE user_info_tab (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `age` int(11) DEFAULT NULL,

  `name` varchar(255) NOT NULL,

  `create_time` datetime NOT NULL,

  `modifed_time` datetime NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_name` (`name`) USING BTREE,

  UNIQUE KEY un_user_id (user_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

9.不需要严格遵守3NF，通过业务字段冗余来减少表关联

什么是数据库三范式（3NF），大家是否还有印象吗？

 

第一范式：对属性的原子性，要求属性具有原子性，不可再分解；

第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖；

第三方式：对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

我们设计表及其字段之间的关系, 应尽量满足第三范式。但是有时候，可以适当冗余，来提高效率。比如以下这张表

 

商品名称    商品型号    单价    数量    总金额

手机    华为    9000    5    45000

以上这张存放商品信息的基本表。总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额可以由单价*数量得到，说明总金额是冗余字段。

 

但是，增加总金额这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

 

10.不使用外键，都在代码层维护

阿里的Java开发规范也明确规定了

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

 

我们为什么不推荐使用外键呢？

    1)、使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做DELETE或者UPDATE都必须考虑外键约束会导致开发的时候很难受,测试数据造数据也不方便。

    2)、还有一个场景不能使用外键，就是分库分表。

 

11.没有特殊场景一般都选择INNODB存储引擎

建表是需要选择 存储引擎 的，我们一般都选择 INNODB 存储引擎，除非读写比率小于1%，才会考虑使用MyISAM(也就是基本上都是读的场景)

 

12.时间类型的选择

我们设计表的时候，一般都需要加通用时间的字段，如 create_time、update_time等等，那对于时间的类型，我们应该如何选择？

 

对于MySQL来说，主要有 date、datetime、time、timestamp 和 year

 

date ：表示的日期值, 格式yyyy-mm-dd,范围1000-01-01 到 9999-12-31，3字节

time ：表示的时间值，格式 hh:mm:ss，范围-838:59:59 到 838:59:59，3字节

datetime：表示的日期时间值,格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59,8字节,跟时区无关

timestamp：表示的时间戳值,格式为yyyymmddhhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07，4字节,跟时区有关

year：年份值，格式为yyyy。范围1901到2155，1字节

 

总结：推荐优先使用datetime类型来保存日期和时间，因为存储范围更大，且跟时区无关

13.不建议在数据表中使用Text数据类型，而要单独开一张表放Text类型的数据呢？

从BufferPool的角度考虑一下，大家认为BufferPool有什么关系呢？

 

一开始我是这样考虑的，Text字段一般来说会很大，如果要加载到BufferPool里面，会把内存撑爆？

 

我一开始是从这个角度去想的，但是后来想想，无论要不要将Text类型的字段的数据单独放到一张表，都不影响加载到BufferPool，所以撑爆内存的这个角度想不太对

 

换个角度，换到索引的角度去想一下

 

在MySQL的InnoDB引擎下，我们是通过B+树去存储索引结构的，在B+树中真正存储数据的都是叶子节点

 

而且我们一页只能存放16KB大小的数据，如果说你把Text数据和其他数据同时放在一张表，那么一条记录会比没放Text的时候要大很多，导致一个索引页存放的数据条数大大的减少

14.考虑是否需要分库分表

什么是分库分表呢？

分库：就是一个数据库分成多个数据库，部署到不同机器。

分表：就是一个数据库表分成多个表。

我们在设计表的时候，其实可以提前估算一下，是否需要做分库分表。比如一些用户信息，未来可能数据量到达百万设置千万的话，就可以提前考虑分库分表。

 

为什么需要分库分表？

    1)、数据量太大的话，SQL的查询就会变慢。

    2)、如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。

    3)、即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢了，因为磁盘IO次数变多了

 

15.sql编写的一些优化经验

查询SQL不要使用select * ,而是select(具体字段)

避免在where子句中使用or来连接条件（可能会导致索引失效，因为or要两个条件都有索引）、

避免在索引列上使用mysql的内置函数

避免在where子句中对字段进行表达式操作，还有隐式转换

避免在where子句中使用 != 或 <> 操作符

使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

对查询进行优化，应考虑在where及order by涉及的列上建立索引

如果插入数据过多，考虑批量插入

在适当的时候，使用覆盖索引

使用explain 分析你SQL的计划