mysqlStructureOptimization - juedaiyuer/researchNote GitHub Wiki

#MySQL结构优化#

良好的数据库逻辑设计和物理设计是数据库获得高性能的基础

反范式化设计,加快一些语句查询速度,影响其他语句性能

##选择合适的数据类型##

使用可以存下你的数据的最小的数据类型
使用简单的数据类型.int要比varchar类型在mysql处理简单
尽可能的使用not null定义字段
尽量少用text类型,非用不可时考虑分表

###使用int来存储日期时间###

利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数进行转换

CREATE TABLE test(id INT AUTO_INCREMENT NOT NULL,timestr INT,PRIMARY KEY(id));

INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP('2016-7-3 9:30:00'));

mysql> select * from test1;
+----+------------+
| id | timestr    |
+----+------------+
|  1 | 1467509400 |
+----+------------+

SELECT FROM_UNIXTIME(timestr) FROM test;

###使用bigint存储ip地址###

INET_ATON(),INET_NTOA()两个函数来进行转换

一般情况下使用varchar数据类型,大约15个字节

CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL,ipaddress BIGINT,PRIMARY KEY(id));

INSERT INTO sessions(ipaddress) VALUES (INET_ATON('192.168.1.103'));

mysql> select * from sessions;
+----+------------+
| id | ipaddress  |
+----+------------+
|  1 | 3232235879 |
+----+------------+

SELECT INET_NTOA(ipaddress) FROM sessions;

##数据库结构优化的目的##

减少数据冗余
尽量避免数据维护中出现更新,插入和删除异常
节约数据存储空间
提高查询效率

插入异常:如果表中的某个实体随着另一个实体而存在
更新异常:如果更改表中的某个实体的单独属性时,需要对多行进行更新
删除异常:如果删除表中的某一实体则导致其他实体的消失
解决方案:范式化

##数据库结构设计的步骤##

需求分析:全面了解产品设计的存储需求,数据处理需求,数据的安全性和完整性
逻辑设计:设计数据的逻辑存储结构,数据实体之间的逻辑关系,解决数据冗余和数据维护异常
物理设计:根据所使用的数据库特点进行表结构设计
维护优化:根据实际情况对索引,存储结构等进行优化

###需求分析及逻辑设计###

电子商务网站数据库结构

用户登陆及用户管理功能

用户必须注册并登陆系统才能进行网上交易,用户名来作为用户信息的业务主键
同一时间一个用户只能在一个地方登陆
用户信息:用户名,密码,手机号,姓名,注册日期,在线状态,出生日期

只有一个业务主键,一定是符合第二范式
没有属性和业务主键存在传递依赖的关系,符合第三范式

商品展示及商品管理功能

商品信息:商品名称,分类名称,出版社名称,图书价格,图书描述,作者

分类存在数据维护异常,比如新增加一个分类,该分类没有图书,该分类无法记录
拆分

商品信息
分类信息
商品分类(对应关系表)

##数据库设计范式##

第一范式是不可拆分,原子性
第二是完全依赖
第三消除传递依赖

###第一范式###

数据库表中的所有字段都只具有单一属性
单一属性的列是由基本的数据类型所构成
设计出来的表都是简单的二维表

###第二范式##

要求一个表中只具有一个业务主键,也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系(联合主键)

###第三范式##

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

##反范式化##

为了查询效率的考虑把原本符合第三范式的表适当的增加冗余,以达到优化查询效率的目的,反范式化是空间换时间的操作

##表的垂直拆分##

把不常用的字段单独存放到一个表中
把大字段独立存放到一个表中
把经常一起使用的字段放到一起

##表的水平拆分##

表的水平拆分为了解决单表的数据量过大的问题

###常用的水平拆分方法###

对id进行hash运算,如果要拆分成5个表则使用mod(id,5)取出0~4的值
针对不同的hashID把数据存到不同的表中

###挑战###

跨分区表进行数据查询
统计及后台报表操作

##source##

数据库设计三大范式