MySQL数据库结构设计

前言

系列文章：
1.MySQL主从复制
2.OneProxy实现MySQL读写分离
3.MySQL数据库结构设计
4.MySQL基于GTID主从复制的杂谈

在编码过程中，如果MySQL数据结构设计不好的话，会大大影响开发人员编码效率。比如说MySQL数据库表设计不规范，创建时间字段设计成cjsj，创建者字段设计成cjr或者cjz。这样的数据库表可读性和表意性相当差。下面我们就来讲讲如何规范设计数据库结构。

数据库结构优化

数据库结构优化的目的有哪些？

  • 减少数据冗余。
  • 尽量避免数据维护中出现更新，插入，删除异常。插入异常是指如果表中的某个实体随着另一个实体而存在。更新异常是指如果更改表中的某个实体的单独属性时，需要对多行进行更新。删除异常是指如果删除表中的某一实体则会导致其他实体消失。
  • 节约数据查询空间。

假设有一张学生选课表，字段信息如下：

id ,
stu_id //学生id,
stu_name //学生姓名,
stu_sex //学生性别,
course_name //课程名称,
course_point //学分

插入异常就是当插入几门课程的时候，stu_id和stu_name，stu_sex会为空。如果将stu_id设置为非空，会造成异常。

更新异常就是当更新某一个课程的学分时，那么这一门科目的选课记录都将要更新。

删除异常就是当删除某一个课程时，那么这一门科目的选课记录都要删除。

解决上述异常很简单，设计数据库表时遵循数据库三大范式即可。

数据库结构设计又分为逻辑设计和物理设计。
前面说的数据库三大范式可以说是逻辑设计。逻辑设计是根据数据实体之间的逻辑关系对表进行设计。一个好的逻辑设计可以解决数据冗余和数据维护的异常，反之亦然。

物理设计则是根据所使用的数据库特点进行表结构设计。比如Myisam引擎不支持事务，但是支持并发插入的表级锁，主要应用于select,insert。不适合读写频繁的场景。Innodb支持事务，支持MVCC(多版本并发控制)的行级锁，可以应用事务处理。
维护优化是指根据实际情况对索引存储结构等进行优化。

一般数据库结构设计的步骤是：
1.需求分析：全面了解产品设计的存储需求。存取需求是指数据库要存储什么样的数据，这些数据具有什么特点。数据处理需是指如何对数据库进行读取和写入以及对数据的响应时间有什么样的要求，数据的安全性和完整性需求是数据的生命周期。
2.进行逻辑设计和物理设计。
3.维护优化。

有一些场景，我们需要反范式化设计。比如查询订单信息时，我们肯定是要查询下单的收货人信息。如果我们更改了收货人信息，那么查询订单时，会出现收货人信息不一致的现象。所以我们要在订单表中冗余收货人信息。

范式化与反范式化

反范式化设计就是为了性能和读取效率的考虑而适当的对数据库设计范式进行违反，而允许存在少量数据冗余。换句话来说反范式化就是使用空间换时间。

范式化的优点：
1.可以减少数据冗余。
2.范式化的更新操作比反范式化要快。
3.范式化的表同样比反范式化的表要小。

范式化的缺点：
1.关联查询。
2.更难于索引优化。

反范式化优点：
1.减少表的关联。
2.更好的索引优化，覆盖索引。

反范式化缺点：
1.存在数据冗余及数据维护异常。
2.对数据的修改需要更多的成本。

如何为表选择字段类型

在物理设计中，我们要做到可读性，表意性，长名性。
当我们为表进行物理设计时，常常为对表中的字段选择合适的数据类型进行纠结。当一个列可以选择多种数据类型时，应该优化考虑数字类型，其次是日期或者二进制类型，最后是字符串类型。对于相同级别的数据类型，应该优化考虑占用空间小的数据类型。

Innodb一页是16K。

tinyint 占1个字节
smallint占2个字节
mediumint占3个字节
int占4个字节
bigint占8个字节

float 占4个字节，非精确
double占8个字节，非精确
decimal 每4个字节存9个数据，小数点占1个字节。比如decimal(18,9)需要9个字节来存储，最多支持65个数字。 精确

year占1个字节
time占3个字节
date占3个字节
datetime占8个字节
timestamp占4个字节

以UTF-8为例，中文占3个字节，英文占1个字节。
下面我们就以字符串和日期类型为例，讲一讲。

varchar和char中宽度的定义是字符长度。
varchar用于存储变长字符串，只占用必要的存储空间，最多65535。varchar的最大长度小于255，则占用一个额外字节记录字符串长度。大于255，则占用2个额外字节用于记录字符串长度。在mysql老版本的时候，修改varchar的长度会锁表。在mysql5.7之后，修改之后不超过255，是不会锁表。

varchar的适用场景：
1.字符串的最大长度比平均长度大很多。
2.字符串很少被更新。
3.使用了多字节字符集来存储字符串。

char类型的存储特点：
1.char类型是定长的。
2.字符串存储在char类型的列中会删除末尾的空格。
3.最大宽度为255。

char适用的场景：
1.char类型适用于存储所有长度近似的值。
2.char类型适合存储短字符串。
3.char类型适用存储经常更新的字符串，可以避免形成存储碎片。

datetime类型以YYYY-MM-DD HH:MM:SS.[fraction]格式存储日期时间。
datetime = YYYY-MM-DD HH:MM:SS
datetime(6)=YYYY-MM-DD HH:MM:SS.fraction
datetime类型与时区无关，占用8个字节来存储时间。
时间范围为1000-01-01 00:00:00 ~9999-12-31 23:59:59

timestamp占用4个字节，代表的时间为格林威治时间。时间范围是1970-01-01到2038-01-19。timestamp类型显示依赖于所指定的时区。在行的数据被修改时，可以自动修改timestamp列的值。如果一行记录有多个timestamp的字段，那么修改该记录时只有第一个timestamp类型的字段会自动更新时间。我们可以在定义timestamp类型字段时加上default current_timestamp on update current_timestamp

date占用的字节数要比使用字符串、datetime、int存储的要少。使用date类型只需要3个字节。使用date类型还可以利用日期时间函数进行日期相关的计算。时间范围为1000-01-01~9999-12-31

time类型用于存储时间数据，格式为HH:mm:ss

我们在存储日期格式相关的数据时，要注意以下几点：
1.不要使用字符串类型来存储日期时间数据。
2.日期时间类型通常要比字符串占用的存储空间小。
3.日期类型在进行查询过滤时，可以利用日期来进行对比，避免隐式转换造成索引全盘扫描。
4.日期时间类型有丰富的处理函数，可以更加方便对日期类型数据进行日期过滤。
5.使用int存储日期时间还不如使用timestamp类型。

我们可以创建测试表，来测试不同日期类型的查询速度。

create table `date_demo`
(
id int(11) not null auto_increment,
`time` TIME not null,
`timestamp` TIMESTAMP not null,
`datetime` datetime not null,
`date` date not null,
`int_date` bigint(20) not null,
primary key (`id`),
key `idx_time`(`time`),
key `idx_timestamp`(`timestamp`),
key `idx_datetime`(`datetime`),
key `idx_date`(`date`),
key `idx_int_date`(`int_date`)
)

为了更加直观的看见结果，我们插入200w测试数据。

    public static void test() {
        try {
            long start = System.currentTimeMillis();
            String url = "jdbc:mysql://127.0.0.1:3306/groupon?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false";
            String username = "root";
            String password= "root";
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver);
            Connection conn  = DriverManager.getConnection(url, username, password);
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("insert into date_demo (time,timestamp,datetime,date,int_date) values(now(),now(),now(),now(),now())");

            for (int i = 1; i <= 1999; i++) {
                sqlBuffer.append(" ,(now(),now(),now(),now(),now()) ");
            }

            PreparedStatement pstmt = conn.prepareStatement(sqlBuffer.toString());

            for (int i = 1; i <= 500; i++) {
                int result = pstmt.executeUpdate();
                System.out.println("result=" + result);
            }
            pstmt.close();
            conn.close();
            long end = System.currentTimeMillis();
            System.out.println("cost=" + (end - start) + "ms");
        } catch (Exception e) {
           e.printStackTrace();
        }
    }

time查询时间为0.233s

select * from date_demo
where time = '23:13:09'
image.png

timestamp查询时间为0.230s


select * from date_demo
where `timestamp` = '2018-10-28 23:13:09'
image.png

datetime查询时间为0.242s

select * from date_demo
where datetime = '2018-10-28 23:13:09'
image.png

date查询时间为0.221s

select * from date_demo
where date = '2018-10-28' limit 9
image.png

int查询时间为0.211s


select * from date_demo
where int_date = 20181028231309
image.png

查询速度从快到慢如下：bigint> date>timestamp>time>datetime(仅供参考)

尾言

大家好，我是cmazxiaoma(寓意是沉梦昂志的小马)，感谢各位阅读本文章。
小弟不才。
如果您对这篇文章有什么意见或者错误需要改进的地方,欢迎与我讨论。
如果您觉得还不错的话,希望你们可以点个赞。
希望我的文章对你能有所帮助。
有什么意见、见解或疑惑，欢迎留言讨论。

最后送上：心之所向，素履以往。生如逆旅，一苇以航。


saoqi.png
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 211,884评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,347评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,435评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,509评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,611评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,837评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,987评论 3赞 408
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,730评论 0赞 267
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,194评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,525评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,664评论 1赞 340
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,334评论 4赞 330
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,944评论 3赞 313
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,764评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,997评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,389评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,554评论 2赞 349