MySQL数据库结构设计

在编码过程中,如果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(仅供参考)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JavaEdge

用弱引用堵住内存泄漏全局 Map 造成的内存泄漏找出内存泄漏HPROF 输出,显示 Map.Entry 对象的分配点弱引用WeakReference.get() 的一种可能实现用 WeakHashMa

3635
来自专栏浪淘沙

Hive学习

         Hive是基于Hadoop的一个数据仓库工具(离线),可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。

3272
来自专栏魏琼东

一步一步教你使用AgileEAS.NET基础类库进行应用开发-基础篇-演示ORM中的查询

前文回顾           前面的文章一步一步教你使用AgileEAS.NET基础类库进行应用开发-基础篇-演示ORM的基本操作一文给大家介绍了如果使用ORM...

2035
来自专栏大内老A

一个通过JSONP跨域调用WCF REST服务的例子(以jQuery为例)

JSONP(JSON with Padding)可以看成是JSON的一种“使用模式”,用以解决“跨域访问”的问题,这篇简单的文章给出一个简单的例子用于模拟如何通...

2117
来自专栏安恒网络空间安全讲武堂

seacms修复历程总结

seacms修复历程总结 从6.45版本开始search.php就存在前台getshell的漏洞,到6.54官方对其进行修补,但修复方法是对用户输入的参数进行过...

6347
来自专栏用户2442861的专栏

2014 360校园招聘技术类面试题

851
来自专栏恰童鞋骚年

Entity Framework 基础知识走马观花

  (1)通过选择以XML方式打开edmx文件,我们可以可以清楚地看到,edmx模型文件本质就是一个XML文件;

1122
来自专栏Dato

浅谈 Mybatis中的 ${ } 和 #{ }的区别

好了,真正做开发也差不多一年了。一直都是看别人的博客,自己懒得写,而且也不会写博客,今天就开始慢慢的练习一下写博客吧。前段时间刚好在公司遇到这样的问题。 一、举...

3489
来自专栏Kirito的技术分享

JAVA 拾遗--Future 模式与 Promise 模式

写这篇文章的动机,是缘起于微信闲聊群的一场讨论,粗略整理下,主要涉及了以下几个具体的问题: 同步,异步,阻塞,非阻塞的关联及区别。 JAVA 中有 callb...

3K10
来自专栏進无尽的文章

简述OC语言

对于一门语言的学习是需要时间领悟的,而对于一些原理性的问题,我们需要清楚其核心思想,知其然而知其所以然,这样才能有利于自己的后续发展。本文只是简述,没有面面具到...

2292

扫码关注云+社区

领取腾讯云代金券