MySQL数据库结构设计

2018-11-04 本文已影响80人 cmazxiaoma

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

MySQL数据库结构设计

数据库结构优化

范式化与反范式化

如何为表选择字段类型

猜你喜欢

热点阅读