Mysql基础篇-38-设计表字段的一些建议

Alan0517

已于 2023-05-30 10:38:38 修改

阅读量2.7k

点赞数 2

文章标签： java mysql

于 2021-11-18 13:17:15 首次发布

本文链接：https://blog.csdn.net/Hmj050117/article/details/121369738

版权

Mysql-基础篇专栏收录该内容

41 篇文章 5 订阅

订阅专栏

文章目录

1. 主键Id
2. 设计表的一些建议
3. 创建索引建议
4. 默认值为not null的好处
5. timestamp时区问题

1. 主键Id

一般我们会考虑将主键设置为自增或者说采用雪花算法或者其他算法生成Id插入作为主键的方式，下面分别介绍一下

1.1 `int类型`

采用无符号int 作为自增主键；

`id` int(10) unsigned NOT NULL AUTO_INCREMENT

有符号和无符号的区别:

整型的每一种都分无符号(unsigned)和有符号(signed)两种类型(float和double总是带符号的)，在默认情况下声明的整型变量都是有符号的类型(char有点特别)，如果需声明无符号类型的话就需要在类型前加上unsigned。

无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据，比如int ,默认有符号，最大值为21亿多，设置无符号则42亿多;

int(M) 当中的M代表什么意思？
整数类型后面跟的是显示的宽度。M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。
因为无论你int多少，最多都只能存10位。一般如何设置int宽度都是搭配 zerofill 的使用: id int(12) zerofill
eg:

create table int_12(id int(12) zerofill);
insert into int_12 (id) values(1);
select * from int_12;

1.2 使用int 如果达到最大值，会怎样

创建一张表，设置自增起点为2147483647，然后新增两条数据，

create table `test1`(
`id` int(11) not null AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=2147483647;


INSERT into test1(`name`) VALUES('1');
INSERT into test1(`name`) VALUES('2');

发现报错：

INSERT into test1(`name`) VALUES('2')
> 1062 - Duplicate entry '2147483647' for key 'PRIMARY'
> 时间: 0.001s

1.3 不设置主键会怎样

如果没有设置主键的话，InnoDB则会自动帮你创建一个6个字节的row_id，由于row_id是无符号的，所以最大长度是2^48-1。当id用完之后，row_id会从0重新开始计算，数据则会覆盖;

1.4 使用bitint作为索引

从索引性能来讲，使用bigint，肯定会有影响，比如加载相同数量的记录条数的索引进内存，内存占用肯定翻倍。也就是相同内存可以加载的信息少了，读取相同数量信息的次数翻倍，内存空间毕竟有限，无效的占用会导致更多的数据换入换出，额外增加了IO的压力，对性能是不利的；

1.5 小总结：

主键可以使用无符号作为自增主键，一般int就行，无符号的int最大值可以达到42亿多，足够满足一般公司需求，换言之，即使超过这个数量了，后面可以采取分库分表，实在特殊需求，可以使用无符号bitint；

2. 设计表的一些建议

下面一些规范是个人建议，非必须，根据实际需求来：

建表时不要指定表的字符集，全部使用数据库默认字符集，以免造成不同字符集的表不能相互Join；
单表不超过30个字段（字段越多，查询越慢），对于一些特殊的表可超过30字段；
所有表、字段必须添加注释，推荐采用英文标点；
建议表设计时字段定义为NOT NULL，并指定默认值
- 数字类型默认0 或者根据业务需求指定默认值
- 字符串类型默认 ‘’ 空字符串,而不是Null
索引字段必须要求定义为NOT NULL，并指定默认值；
建议使用UNSIGNED存储非负数值。推荐主键使用unsigned；
有符号和无符号类型使用相同的存储空间，并具有相同的性能
- eg: tinyint -127~ 127，那tinyint unsigned 最大就可以到127 * 2
数据类型尽量用数字型，数字型的比较比字符型的快很多;
使用varchar存储大小写敏感的变长字符串或二进制内容，区别使用char与varchar，比如:身份证定长使用char；
- varchar用于存储可变⻓字符串，长度支持到65535,需要使用1或2个额外字节记录字符串的长度,适合字符串的最大⻓度比平均⻓度⼤很多,更新很少；
- char: 定⻓，⻓度范围是1~255,适合：存储很短的字符串，或者所有值接近同一个长度；经常变更；
- 更长的列会消耗更多的内存，所以最好的策略是只分配真正需要的空间
尽量使用TIMESTAMP类型, 因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。至于时分问题，其实TIMESTAMP本身也是没有时区问题的，看配置，具体可以参考这篇论证博客https://blog.csdn.net/shy111111111/article/details/120266768
所有表设计时须有id、del_flag、create time、（update_time）四个字段，当然最后还得根据实际情况设定；且创建时间和更新时间不要自己赋值，通过程序中传的update_time可能并不是当时的时间，获取增量数据时会发生错误；
- 自增主键：int（10）unsigned not null auto_increment
- 创建时间: datetime NOT NULL DEFAULT CURRENT TIMESTAMP
- 修改时间: datetime NOT NULL DEFAULT CURRENT TIMESTAMP on UPDATE CURRENT TIMESTAMP
- 逻辑删除标识: bit（1）not null default b’0’
Mysql使用效率bit/tinyint/smallint/int/bigint > char > varchar

CREATE TABLE `business_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `status` bit(1) NOT NULL DEFAULT b'1' COMMENT '状态: 1-有效,0-删除',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `creator` varchar(30) DEFAULT NULL COMMENT '创建人',
  `updater` varchar(30) DEFAULT NULL COMMENT '更新人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用无符号整数存储IP地址
IP地址属于特殊类型数据，应转为整数存储,人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是 32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易;
IP地址数据采用整数（UNSIGNED INT）存储，在存储和CPU资源使用上都少于字符串存储形式

select  INET_ATON('192.0.0.0')

select INET_NTOA(3221225472)

3. 创建索引建议

一张表是否需要创建索引，得先关注该表的数据的零散度，其次再考虑如何创建优质的索引，索引优化：https://blog.csdn.net/Hmj050117/article/details/112428506

谨慎合理添加索引，禁止冗余的重复的索引；
单个索引中的字段数不超过7个；
联表查询时JOIN（即关联字段）的数据类型必须相同，并且要有相应索引，且要求关联的字段定义，长度保持一致。
选择区分度大的列建立索引，组合索引中，区分度大的字段放在最前；
不在低基数列上建立单独索引，例如“状态”，“性别“只有男、女两种；可以建立联合索引，应该将低基数列放在后面；
对过长的varchar字段建议优先考虑前缀索引，前缀索引长度不超过12个字符;
- 什么是前缀索引？
  前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。
- 为什么要用前缀索引？
  前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引(索引会失效,group by 是去重)，也不能把它们用于覆盖索引。
- 什么情况下适合使用前缀索引？
  当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。
索引字段不能为空；
对于过长的普通索引字段，建索引时要限制索引长度
- myisam表，单列索引，最大长度不能超过 1000 bytes；
- innodb表，单列索引，最大长度不能超过 767 bytes；
- utf8 编码时一个字符占三个字节：
  varchar 型能建立索引的最大长度分别为:
  myisam : 1000/3 = 333
  innodb : 767/3 = 255
- utf8mb4 编码时一个字符占四个字节
  varchar 型能建立索引的最大长度分别为:
  myisam: 1000/4 = 250
  innodb: 767/4 = 191

4. 默认值为not null的好处

节省空间
NULL 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位
空指针
查询时，可以在一定程度上减少 NullPointerException。
Count(NULL column) 不参与计算
如果有 Null column 存在的情况下，count(NULL column) 需要格外注意，NULL 值不会参与统计。
索引
含有空值的列很难进行查询优化，而且对表索引时不会存储 NULL 值的。所以如果索引的字段可以为 NULL值，索引的效率会下降。因为它们使得索引、索引的统计信息以及比较运算更加复杂。
应该用0、一个特殊的值或者一个空串代替 NULL值。
使用 != ，NOT IN 的情况
NOT IN 、!= 等负向条件查询在有 NULL 值的情况下，返回永远为空结果，查询容易出错。

5. timestamp时区问题

DATETIME的特点
保存从1001 年到 9999年，精度为秒，使用8个字节把日期和时间装到格式为“YYYYMMDDHHMMSS” 的整数，整数格式在处理时不方便，此外没有区分时区。
TIMESTAMP的特点
保存1970（同 UNIX 时间戳）年到2038年的时间，精度也是秒，使用4个字节存储。MySQL提供 FROM_UNIXTIME() 将Unix时间戳转换为日期，还提供 UNIX_TIMESTAMP() 将日期转换为Unix 时间戳。时间显示与时区相关，不同的时区时间显示不同。

5.1 时区概念

由于地域的限制，人们发明了时区的概念，用来适应人们在时间感受上的差异，比如中国的时区是东8区，表示为+8:00，或GMT+8，而日本的时区是东9区，表示为+9:00，或GMT+9，当中国是早上8点时，日本是早上9点，即东8区的8点与东9区的9点，这两个时间是相等的；

绝对时间：
如unix时间缀，是1970-01-01 00:00:00开始到现在的秒数，如：1582416000，这种表示是绝对时间，不受时区影响，也叫纪元时epoch。
本地时间：
相对于某一时区的时间，是本地时间，比如东8区的2020-02-23 08:00:00，是中国人的本地时间，而在此时，日本人的本地时间是2020-02-23 09:00:00，所以本地时间都是与某一时区相关的，脱离时区看本地时间，是没有意义的，因为你并不知道这具体是指的什么时间点。
比如在Java中，Date对象是绝对时间，通过SimpleDateFormat格式化出来的yyyy-MM-dd HH:mm:ss形式的时间字符串，是本地时间，如果SimpleDateFormat没有调用setTimeZone()显示指定时区，那么默认用的是jvm运行在的操作系统上的时区，我们开发机上的时区基本都是GMT+8。

5.2 时区相关配置

先查看一下刚安装好的mysql的时区默认设置

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.36    |
+-----------+
1 row in set (0.00 sec)

mysql>

PST ：太平洋标准时间
PST时间是西八区,北京时间是东八区,所以两者相差16小时；

mysql驱动创建连接后，会调用com.mysql.jdbc.ConnectionImpl#configureTimezone()来配置此连接的时区，如果配置了serverTimezone，则会使用serverTimezone配置的时区，没配置时会去取数据库中的time_zone变量，mysql5.7默认时区是PST ；

5.3 建议修改配置

将jdbc的时区改成东八区
jdbc配置时区如下: 因为在url上需要urlencode，所以就变成了GMT%2B8
- GTM+8 ： serverTimezone=GMT%2B8
如果mysql使用timestamp类型，一定要保证jdbc url中的serverTimezone与数据库中的时区配置是一致的；
修改mysql的默认时区配置，改成东八区
在mysqld里面添加 default-time-zone = ‘+8:00’

改完记得重启mysql
如果数据库时区修改后，jdbc的serverTimezone也要跟着修改，并重启Java应用，就算没有配置serverTimezone，也需要重启，因为mysql驱动初始化连接时，会将当前数据库时区缓存到一个java变量中，不重启Java应用它不会变。

Alan0517

关注

2
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql基础篇-38-设计表字段的一些建议

主键可以使用无符号作为自增主键，一般int就行，无符号的int最大值可以达到42亿多，足够满足一般公司需求，换言之，即使超过这个数量了，后面可以采取分库分表，实在特殊需求，可以使用无符号bitint；
复制链接

扫一扫