一.数据情景

1.表格结构简介

任何类似工具的东西都是为了解决某个场景下的问题而设计的，比如Redis缓存系统热数据，ClickHouse解决海量数据的实时分析，MySQL关系数据库存储结构化数据。数据存储需要设计相应的表结构，清晰的表结构，有助于快速开发业务和了解系统。表结构的设计通常从以下几个方面考虑:业务场景、设计规范、表结构、字段属性、数据管理。

2.用户场景

比如存储用户基本信息数据时，通常会有以下相关的表结构:用户信息表、单点登录表、状态管理表、支付账户表等。

用户信息表

存储用户信息三要素:姓名、定位器号、身份证、登录密码、邮箱等。

CREATE TABLE `ms_user_center` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(20) NOT NULL COMMENT '用户名', `real_name` varchar(20) DEFAULT NULL COMMENT '真实姓名', `pass_word` varchar(32) NOT NULL COMMENT '密码', `phone` varchar(20) NOT NULL COMMENT '定位器号', `e***il` varchar(32) DEFAULT NULL COMMENT '邮箱', `head_url` varchar(100) DEFAULT NULL COMMENT '用户头像URL', `card_id` varchar(32) DEFAULT NULL COMMENT '身份证号', `user_***` int(1) DEFAULT '1' COMMENT '用户性别:0-女,1-男', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';单点登录表

意在多个业务系统中，用户一次登录即可访问所有可信的业务子系统，是聚合业务平台的常用解决方案。

CREATE TABLE `ms_user_sso` ( `user_id` int(11) NOT NULL COMMENT '用户ID', `sso_id` varchar(32) NOT NULL COMMENT '单点信息编号ID', `sso_code` varchar(32) NOT NULL COMMENT '单点登录码,唯一核心标识', `log_ip` varchar(32) DEFAULT NULL COMMENT '登录IP地址', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用', PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户单点登录表';状态管理表

用户在使用系统时可能有多种状态，如账户冻结、密码锁定等。，通过聚集状态可以更方便地对其进行管理和验证。

CREATE TABLE `ms_user_status` ( `user_id` int(11) NOT NULL COMMENT '用户ID', `account_status` int(1) DEFAULT '1' COMMENT '账户状态：0-冻结,1-未冻结', `real_name_status` int(1) DEFAULT '0' COMMENT '实名认证状态：0-未实名,1-已实名', `pay_pass_status` int(1) DEFAULT '0' COMMENT '支付密码是否设置：0-未设置,1-设置', `wallet_pass_status` int(1) DEFAULT '0' COMMENT '钱包密码是否设置：0-未设置,1-设置', `wallet_status` int(1) DEFAULT '1' COMMENT '钱包是否冻结:0-冻结,1-未冻结', `e***il_status` int(1) DEFAULT '0' COMMENT '邮箱状态:0-未激活,1-激活', `message_status` int(1) DEFAULT '1' COMMENT '短信提醒开启：0-未开启,1-开启', `letter_status` int(1) DEFAULT '1' COMMENT '站内信提醒开启：0-未开启,1-开启', `e***ilmsg_status` int(1) DEFAULT '0' COMMENT '邮件提醒开启：0-未开启,1-开启', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用', PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户状态表';支付账户表

用户交易的核心表，存储用户相关的账户资金信息。

CREATE TABLE `ms_user_wallet` ( `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '钱包ID', `user_id` int(11) NOT NULL COMMENT '用户ID', `wallet_pwd` varchar(32) DEFAULT NULL COMMENT '钱包密码', `total_account` deci***l(20,2) DEFAULT '0.00' COMMENT '账户总额', `usable_money` deci***l(20,2) DEFAULT '0.00' COMMENT '可用余额', `freeze_money` deci***l(20,2) DEFAULT '0.00' COMMENT '冻结金额', `freeze_time` datetime DEFAULT NULL COMMENT '冻结时间', `thaw_time` datetime DEFAULT NULL COMMENT '解冻时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用', PRIMARY KEY (`wallet_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户钱包';

二、设计规范

1.涉及的模块

通过以上表格设计的案例，我们可以看到表格设计涉及到数据库知识的方方面面:数据类型、索引、编码、存储引擎等。设计是一个大命题，但也遵循一个基本规范:三个范式。

2.三种范式

基础概念

一个范例

表的列是原子的，不能再分解，也就是列的信息不能再分解，关系数据库MySQL，Oracle等。都会自动满足。

第二范式

每个事实的数据记录只会出现一次，不会有冗余。通常，设计一个主键来实现它。

三种范式

要求一个表不包含其他表中已经存在的非主键信息，比如部门和员工的信息。如果employee表中包含department表的主键ID，则可以通过关联获取相关信息，不需要在employee表中保存相关信息。

优缺点对比

范式设计

规范化的结构设计通常更新快，因为冗余数据少，表结构轻，可以更好的写入内存。但是，查询涉及到相关性，这是非常昂贵的，消耗查询性能。

反范式设计

所有数据都在一个表中，避免了联查。索引更有效，但数据冗余极高。

建议结论

以上两种设计方法在实际开发中并不存在，而是在实际开发中混合使用。比如汇总统计，缓存数据，都是基于反规范化的设计。

三。字段属性

合适的场类型对于高性能非常重要。基本原则是:简单类型占用资源少；当数据可以正确存储时，选择最小的数据类型。

1.数据类型选择

整数类型

可以根据数据类型范围合理选择TINT、SMALLINT、MEDIUMINT、INT、BIGINT。

实数类型

浮点、双精度和十进制。对于相关类型的货币，建议使用高精度的小数存储，或者将数据相乘为整数，使用BIGINT存储，但是处理相对麻烦。

字符类型

CHAR，VARCHAR，长度不确定。建议使用VARCHAR进行存储，但是VARCHAR类型需要额外的开销来记录字符串长度。CHAR适合存储短字符或者定长字符串，比如MD5的加密结构。

时间类型

DATETIME，TIMESTAMP，DATETIME保存范围广泛的值，精度为秒。timestamp是TIMESTAMP的格式，作用域比较小，效率高，通常推荐使用。

MySQL的字段类型很多，可以根据数据特点选择合适的。这里只介绍几种常见的类型。

2.基本用法和操作

数据类型

修改字段类型

ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT '0' ;ALTER TABLE ms_user_sso MODIFY state INT(1) DEFAULT '1' COMMENT '状态：0不可用,1可用';

修改名称位置

ALTER TABLE ms_user_sso CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;索引使用

索引类型:主键索引、公共索引、唯一索引、组合索引和全文索引。这里演示了通用索引的操作。MySQL的核心模块，后续细节。

添加索引

ALTER TABLE ms_user_wallet ADD INDEX user_id_index(user_id) ;CREATE INDEX state_index ON ms_user_wallet(state) ;

查看索引

SHOW INDEX FROM ms_user_wallet;

删除索引

DROP INDEX state_index ON ms_user_wallet ;

修改索引

如果没有真正的变化，可以删除原来的索引，重新添加。

外键关联

有用性:外键关联的功能保证了多个数据表的数据一致性和完整性。创建表时，先有主表，后有从表；要删除数据表，需要先删除从表，然后再删除主表。不推荐复杂的场景，实际开发中也很少用到。

添加外键

ALTER TABLE ms_user_wallet ADD CONSTRAINT user_id_out_key FOREIGN KEY(user_id) REFERENCES ms_user_center(id) ;

删除外键

ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key ;

四。表格结构管理

1.检查结构

DESC ms_user_status ;SHOW CREATE TABLE ms_user_status ;

2.场结构

添加字段ALTER TABLE ms_user_status ADD `delete_time` datetime DEFAULT NULL COMMENT '删除时间' ;删除字段ALTER TABLE ms_user_status DROP COLUMN delete_time ;

3.修改表名

ALTER TABLE ms_user_center RENAME ms_user_info ;

4.存储引擎

存储引擎SELECT VERSION() ; SHOW ENGINES ;

MySQL 5.6支持的存储引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等。一般默认使用InnoDB，它支持事务管理。本模块MySQL核心，后续详细讲解。

修改引擎

在数据量较大的场景下，存储引擎的修改是一个非常困难的操作，会很容易导致表特性的改变以及各种后续反应，后面会详细介绍。

ALTER TABLE ms_user_sso ENGINE = MyISAM ;

5.修改代码。

该表的默认字符集是utf8，这是通用的，没有乱码的风险。汉字3字节，英文1字节。utf8mb4是utf8的超集，在存储4字节时使用，比如表情符号。

查看编码SHOW VARIABLES LIKE 'character%';修改编码ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4;

动词 （verb的缩写）数据管理

1.添加、删除和检查

添加数据

INSERT INTO ms_user_sso (user_id,sso_id,sso_code,create_time,update_time,login_ip,state)VALUES('1','SSO7637267','SSO78631273612','2019-12-24 11:56:57','2019-12-24 11:57:01','127.0.0.1','1');

更新数据

UPDATE ms_user_sso SET user_id = '1',sso_id = 'SSO20191224',sso_code = 'SSO20191224', create_time = '2019-11-24 11:56:57',update_time = '2019-11-24 11:57:01', login_ip = '127.0.0.1',state = '1'WHERE user_id = '1';

查询数据

一般情况下，禁止select*操作。

SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state FROM ms_user_sso WHERE user_id = '1';

删除的数据

DELETE FROM ms_user_sso WHERE user_id = '2' ;

如果没有where条件，所有数据都将被删除。这个操作原则上是不允许的，优化部分会详细解释。TRUNCATE TABLE还会清除空表数据，但它占用的资源相对较少。

2.数据安全

不可逆加密

这种加密算法多用于数据验证，比如常见的密码验证。

SELECT MD5('cicada')='94454b1241ad2cfbd0c44efda1b6b6ba' ;SELECT SHA('cicada')='0501746a2e4fd34e1d14015fc4d58309585edc7d';SELECT PASSWORD('***ile')='*B4FB95D86DCFC3F33A3852714DC742C77504479D' ;可逆加密

一个安全要求高的系统，需要电影安全，对数据来说是极其安全的。数据存储时必须加密，取出时必须解密，这就需要可逆加密。

SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ;SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');

上述数据安全管理也可以基于应用系统的服务(代码)层来处理。相对专业的流程是从数据产生的源头对数据进行处理，避免数据传输过程的泄露，造成不必要的风险。