1. 简介
1.1 定义
- 建库/建表规范
- 设计数据库表的规范(表不能再分,列不能在分)
- 为了查询效率,可以做冗余字段设计(空间换时间的思想,属于一种反范式设计)
1. 应用场景
- 根据项目产品原型图中的部分,进行数据库设计。
1. E-R模型
1.1 简介
1. 定义
- E-R模型即实体-关系模型,E-R模型就是描述数据库存储数据的结构模型。
- E-R模型由 实体、属性、实体之间的关系构成,主要用来描述数据库中表结构。
- 开发流程是先画出E-R模型,然后根据三范式进行数据库表结构设计
2. 原理
E-R模型的效果图:
说明:
- 实体: 用矩形表示,并标注实体名称
- 属性: 用椭圆表示,并标注属性名称,
- 关系: 用菱形表示,并标注关系名称
- 一对一:关系字段存储在两个任意一个表中的一个字段
- 一对多:关系字段存储在多的那个表的字段
- 多对多:关系字段存储在另外一张表(中间表),表有两个字段,两个表的主键
一对一的关系:
说明:
- 关系也是一种数据,需要通过一个字段存储在表中
- 1对1关系,在表A或表B中创建一个字段,存储另一个表的主键值
一对多的关系:
说明:
- 1对多关系,在多的一方表(学生表)中创建一个字段,存储班级表的主键值
多对多的关系:
说明:
- 多对多关系,新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值
1.2 使用场景
- 对于大型公司开发项目,我们需要根据产品经理的设计,我们先使用建模工具, 如:power designer,db desinger等这些软件来画出实体-关系模型(E-R模型)
- 然后根据三范式设计数据库表结构
2. 三范式
2.1 简介
- 范式: 对设计数据库提出的一些规范,目前有迹可寻的共有8种范式,一般遵守3范式即可。
- 范式就是设计数据库的一些通用规范。
- 1NF强调字段是最小单元,不可再分
- 2NF强调在1NF基础上必须要有主键和非主键字段必须完全依赖于主键,也就是说 不能部分依赖(复合主键)
- 3MF强调在2NF基础上 非主键字段必须直接依赖于主键,也就是说不能传递依赖(间接依赖)。
2.2 分类
- 第一范式(1NF): 强调的是列的原子性,即列不能够再分成其他几列。
- 第二范式(2NF): 满足 1NF,另外包含两部分内容,一是表必须有一个主键;二是非主键字段 必须完全依赖于主键,而不能只依赖于主键的一部分。
- 第三范式(3NF): 满足 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。
1. 第一范式
如图所示的表结构:
说明:
- 这种表结构设计就没有达到 1NF,要符合 1NF 我们只需把列拆分,即:把 contact 字段拆分成 name 、tel、addr 等字段。
2. 第二范式
如图所示的表结构:
说明:
- 这种表结构设计就没有达到 2NF,因为 Discount(折扣),Quantity(数量)完全依赖于主键(OrderID),而 UnitPrice单价,ProductName产品名称 只依赖于 ProductID, 所以 OrderDetail 表不符合 2NF。
- 我们可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)这样就符合第二范式了。
3. 第三范式
如图所示的表结构:
说明:
- 这种表结构设计就没有达到 3NF,因为 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。
- 可以把【Order】表拆分为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。
3. 数据类型和约束
3.1 简介
- 数据库中的数据保存在数据表中,在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,比如:数据类型和约束。
3.2 数据类型
1. 简介
- 数据类型是指在创建表的时候为表中字段指定数据类型,只有数据符合类型要求才能存储起来,使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间。
- (够用就行)
2. 常见数据类型
- 整数:int,bit
- 小数:decimal(float,double)
- 字符串:varchar,char(固定)
-
日期时间:date,time,datetime
-
枚举类型:enum
-
图片,音频,视频:保存路径
-
数据类型说明:
-
decimal表示浮点数,如 decimal(5, 2) 表示共存5位数,小数占 2 位.
-
char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ',3表示字符数
-
varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’,3表示字符数
-
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径.
-
字符串 text 表示存储大文本,当字符大于 4000 时推荐使用, 比如技术博客.
1. 字段类型的选择
1.1 整型的存储大小与显示大小
mysql的字段,unsigned int(3), 和unsinged int(6), 能存储的数值范围是否相同。如果不同,分别是多大?
建立下面这张表:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`i1` int(3) unsigned zerofill DEFAULT NULL,
`i2` int(6) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
插入一些数据后
发现,无论是int(3), int(6), 都可以显示6位以上的整数。但是,当数字不足3位或6位时,前面会用0补齐。
手册解释是这样的:
MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。
也就是说,int的长度并不影响数据的存储精度,长度只和显示有关,在上面例子的建表语句中,使用了zerofill。
最终答案:存储范围相同。
1.2 char 与 varchar 的选择
- char 不可变,查询效率高,可能造成存储浪费
- varchar 可变,查询效率不如char,节省空间
常见MySQL数据类型
3.3 数据约束
1. 简介
- 约束是指数据在数据类型限定的基础上额外增加的要求.
2. 常见约束
- 主键
primary key
: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned. - 非空
not null
: 此字段不允许填写空值. - 惟一
unique
: 此字段的值不允许重复. - 默认
default
: 当不填写字段对应的值会使用默认值,如果填写时以填写为准. - 外键
foreign key
: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.
3.4 作用
数据类型和约束保证了表中数据的准确性和完整性
4. 索引设计
4.1 定义
- 索引是加快数据库的查询速度的一种手段
- 索引在MySQL中也叫做“键”,它是一个特殊的文件,它保存着数据表里所有记录的位置信息,更通俗的来说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
- 键(key):主键,外键,存储数据表里所有记录的位置信息的表
4.2 应用场景
- 当数据库中数据量很大时,查找数据会变得很慢,就可以通过索引来提高数据库的查询效率。
4.2 作用
- 给表中字段添加索引,添加一个索引表(目录), 提高大型数据库的查询效率
- 注意:增删改效率不会提高,甚至会降低
4.3 操作
1. 查看
show index from 表名;
show create table 表名;
2. 创建
alter table 表名 add index索引名[可选](列名,...)
# 主键会自动创建索引
# 索引名不指定,默认使用字段名
-- 给name字段添加索引
alter table classes add index my_name (name);
3. 删除
alter table表名 drop index 索引名
-- 如果不知道索引名,可以查看创表sql语句
show create table classes;
alter table classes drop index my_name;
4.4 案例
验证索引查询性能
创建测试表testindex:
create table test_index(title varchar(10));
向表中插入十万条数据:
from pymysql import connect
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
# 获得Cursor对象
cursor = conn.cursor()
# 插入10万次数据
for i in range(100000):
cursor.execute("insert into test_index values('ha-%d')" % i)
# 提交数据
conn.commit()
if __name__ == "__main__":
main()
验证索引性能操作:
-- 开启运行时间监测:
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
-- 查看执行的时间:
show profiles;
-- 给title字段创建索引:
alter table test_index add index (title);
-- 再次执行查询语句
select * from test_index where title='ha-99999';
-- 再次查看执行的时间
show profiles;
4.5 分类
1.普通索引
2. 联合(复合索引)
2.1 定义
- 联合索引又叫复合索引,即一个索引覆盖表中两个或者多个字段,一般用在多个字段一起查询的时候。
2.2 好处
- 减少磁盘空间开销,因为每创建一个索引,其实就是创建了一个索引文件,那么会增加磁盘空间的开销。
2.3 操作
-- 创建teacher表
create table teacher
(
id int not null primary key auto_increment,
name varchar(10),
age int
);
-- 创建联合索引
alter table teacher add index (name,age);
4.6 最左原则
在使用联合索引的时候,我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询,因为没有用到创建的联合索引。
最左原则示例:
-- 下面的查询使用到了联合索引
select * from stu where name='张三' -- 这里使用了联合索引的name部分
select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引,包括 name 和 age 部分
-- 下面的查询没有使用到联合索引
select * from stu where age=10 -- 因为联合索引里面没有这个组合,只有 name | name age 这两种组合
说明:
- 在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面,否则联合索引失效
4.7 MySQL中索引的优点和缺点和使用原则
-
优点:
- 加快数据的查询速度
-
缺点:
- 创建索引会耗费时间和占用磁盘空间,并且随着数据量的增加所耗费的时间也会增加
-
使用原则:
- 通过优缺点对比,不是索引越多越好,而是需要自己合理的使用。
- 对经常更新的表就避免对其进行过多索引的创建,对经常用于查询的字段应该创建索引,
- 数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
- 在一字段上相同值比较多不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可是建立索引。
4.8 算法
- bTree(b树):二分查找
- b+树
- hash表
5. 外键约束
5.1 简介
1. 定义
- 存储在另一张表主键的字段
2. 作用
- 对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证,数据如果不合法则更新和插入会失败,保证数据的有效性
5.2 操作
- 对于已经存在的字段添加外键约束
- 添加外键约束:
alter table 从表 add foreign key(外键字段) references 主表(主键字段);
-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);
- 在创建数据表时设置外键约束
-- 创建学校表
create table school(
id int not null primary key auto_increment,
name varchar(10)
);
-- 创建老师表
create table teacher(
id int not null primary key auto_increment,
name varchar(10),
s_id int not null,
foreign key(s_id) references school(id)
);
- 删除外键约束
- 删除外键约束:
alter table 表名 drop foreign key 外键名;
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;
-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key teacher_ibfk_1;
5.3 特点
- 安全,但效率低
5.4 索引
- 主键 Primary Key
- 外键 Foreign Key
- 保持数据完整性
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
例如:
ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`)
CASCADE
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
- ON DELETE:删除主表时自动删除从表。删除从表,主表不变
- ON UPDATE:更新主表时自动更新从表。更新从表,主表不变
SET NULL
在父表上update/delete记录时,将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
- ON DELETE:删除主表时自动更新从表值为NULL。删除从表,主表不变
- ON UPDATE:更新主表时自动更新从表值为NULL。更新从表,主表不变
NO ACTION
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
- ON DELETE:从表记录不存在时,主表才可以删除。删除从表,主表不变
- ON UPDATE:从表记录不存在时,主表才可以更新。更新从表,主表不变
RESTRICT
同no action, 都是立即检查外键约束
SET DEFAULT
父表有变更时,子表将外键列设置成一个默认的值 但Innodb目前不支持
-
索引 Key / Index
- 提升查询效率,减慢增删改速度
-
唯一约束 Unique
- 保证数据不重复
6. 数据库引擎选择
6.1 简介
- 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
- 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。
- 现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
6.2 操作
SHOW ENGINES # 命令来查看MySQL提供的引擎
SHOW VARIABLES LIKE 'storage_engine'; # 查看数据库默认使用哪个引擎
6.3 分类
1. InnoDB
存储引擎
1.1 简介
InnoDB
是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。
1.2 InnoDB特性
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎。
- InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束
5、存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键
6、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
2. MyISAM存储引擎
2.1 简介
- MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务。
2.2 MyISAM主要特性
1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节,这也可以通过编译来改变,对于键长度超过250字节的情况,一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中,这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库,将产生3个文件。文件的名字以表名字开始,扩展名之处文件类型:frm文件存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名时.MYI(MYIndex)
3. MEMORY存储引擎
3. 1 简介
MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。
3.2 MEMORY主要特性
1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享(就像其他任何非TEMPORARY表)
8、MEMORY表内存被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享
9、当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)
6.4 存储引擎选择
- 不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示:
-
如果要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB是一个好的选择
-
如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率
-
如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果
-
如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive
-
使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能