MySQL 必知必会读书笔记 (2)

MySQL 必知必会读书笔记 (1)


关系数据库的三级模式结构

如图:外模式对应视图和部分表,模式对应基本表,内模式对应于存储文件

clipboard.png


基本表: 是本身独立存在的表,在SQL中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件,一个表可以带若干索引,索引也存储在存储文件中。
视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中,即数据库只存放视图的定义,而不存放视图对应的数据。这些数据仍然存放在导出视图的基本表中,因此视图是一个虚拟表。


视图

视图是虚拟的表,different from 包含数据的表,视图只包含使用时动态检索数据的查询操作,并不包含数据。创建视图view后,可以用table基本相同的方式利用它们。可以对视图执行select 操作,过滤和排序操作,将视图联结到其他视图或表,甚至能添加和更改数据。

视图仅仅是用来查看存储在别处数据的一种设施。只是用来检索
视图不能索引,也不能有关联的触发器或默认值。

为什么使用视图:

  • 重用SQL语句

  • 简化复杂的SQL操作。在编写查询后,可以方便地从重用它而不必知道它的基本查询细节

  • 使用表的组成部分而不是整个表

  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

使用视图

  • 简化复杂的联接
    先创建一个包含了联接操作的视图

    clipboard.png

    clipboard.png

  • 用视图重新格式化检索出的数据
    先创建一个包含了格式化检索数据操作的视图
    clipboard.png

    clipboard.png

  • 用视图过滤不想要的数据

    clipboard.png

    clipboard.png

  • 使用视图简化计算字段

clipboard.png

clipboard.png

索引

普通 mysql 运行,数据量和访问量不大的话,是足够快的,但是当数据量和访问量剧增的时候,那么就会明显发现 MySQL 很慢,甚至 down 掉,那么就要考虑优化mysql 了。其中优化 mysql 的一个重要环节就是为数据库建立正确合理的索引。

如果没有索引,执行查询时 mysql 必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,mysql 无需扫描任何记录即可迅速得到目标记录所在的位置。也就是说索引可以大大减少DBMS查找数据的时间

索引有哪些优点?

1、  通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
2、  可以大大加快数据的检索速度,这也是创建索引的最主要原因。
3、  可以加速表和表之间的连接,这在实现数据的参考完整性方面特别有意义。
4、  在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

索引有哪些缺点?

1、  创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2、  除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,需要的空间就会更大。
3、  当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

建立和使用索引有哪些注意事项:

 1、 索引要建立在经常进行 select 操作的字段上。这是因为,如果这些列很少用到,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
 2、 索引要建立在值比较唯一的字段上。这样做才是发挥索引的最大效果。,比如主键的 id 字段,唯一的名字 name 字段等等。如果索引建立在唯一值比较少的字段,比如性别 gender 字段,寥寥无几的类别字段等,刚索引几乎没有任何意义。
 3、 对于那些定义为 text、image 和 bit 数据类型的列不应该增加索引。因为这些列的数据量要么相当大,要么取值很少。
 4、 当修改性能远远大于检索性能时,不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
 5、 在 WHERE 和 JOIN 中出现的列需要建立索引。
 6、 在以通配符 % 和_ 开头作查询时,mysql 索引是无效的。但是这样索引是有效的:select * from tbl1 where name like 'xxx%',所以 mysql 正确建立索引是很重要的。

mysql语句

对orders表的order_num列进行查询,如果在数据不多的时候,执行效果是不错的。
但是随着数据量的增加,这个查询执行起来就越来越慢了。

  • 建立索引
    所以在order_num列上面建立索引
    clipboard.png
    这样,可以加快前面的查询的速度。

    但是如果使用语句select * from orders where cust_id = 10001; 这个检索的查询速度仍然很慢。因为在cust_id列上并没有建立索引。也就是 WHERE 里面的条件, 会自动判断,有没有可用的索引。

    P.S.
    在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为 PRIMARY KEY 或 UNIQUE 索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

    还可以建立多列索引

    clipboard.png

  • 重建索引
    重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后,索引都有损坏的可能,这时就需要重建。对数据重建索引可以起到提高检索效率。

    REPAIR TABLE table_name QUICK;


数据库完整性

关系模型中有三类完整性约束: 实体完整性,参照完整性,和用户定义完整性。其中前二是关系模型必须满足的完整性约束条件,即关系的两个不变性,关系系统自动支持。

实体完整性

实体完整性:1 主键唯一 2 主键的各个属性不许为空
RMDB的实体完整性在create table中用primary key定义。

mysql#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) 

参照完整性

参照完整性: 连接两个表中对应的元组。或者取空值(该属性均为null)或者等于另一个关系的主键值

mysql#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

用户自定义完整性


数据库设计

应该设计几个关系模式,每个关系由哪些属性组成。

范式

关系模型R(U,F),U为属性,F为属性U上的一组数据依赖。
关系数据库的关系是要满足一定要求的,满足不同程度要求的为不同范式。

via aijuans

  • 第一范式 (1NF)
    定义:如果关系模式 R 的每个关系 r 的属性都是不可分的数据项,那么就称 R 是第一范式的模式。
    简单的说,每一个属性都是原子项,不可分割。

    1NF 是关系模式应具备的最起码的条件,如果数据库设计不能满足第一范式,就不称为关系型数据库。关系数据库设计研究的关系规范化是在 1NF 之上进行的。
    例如 (学生信息表):

学生编号 姓名 性别 联系方式
20080901 张三 email:zs@126.com,phone:88886666
20080902 李四 email:ls@126.com,phone:66668888

以上的表就不符合,第一范式:联系方式字段可以再分,所以变更为正确的是:

学生编号 姓名 性别 电子邮件 电话
20080901 张三 zs@126.com 88886666
20080902 李四 ls@126.com 66668888

第二范式(2NF)
定义:如果关系模式 R 是 1NF,且每个非主属性完全函数依赖于候选键,那么就称 R 是第二范式。
简单的说,第二范式要满足以下的条件:首先要满足第一范式,其次每个非主属性要完全函数依赖与候选键,或者是主键。也就是说,每个非主属性是由整个主键函数决定的,而不能由主键的一部分来决定。
例如 (学生选课表):

学生 课程 教师 教师职称 教材 教室 上课时间
李四 Spring 张老师 java 讲师 《Spring 深入浅出》 301 08:00
张三 Struts 杨老师 java 讲师 《Struts in Action》 302 13:30

这里通过(学生,课程)可以确定教师、教师职称,教材,教室和上课时间,所以可以把(学生,课程)作为主键。但是,教材并不完全依赖于(学生,课程),只拿出课程就可以确定教材,因为一个课程,一定指定了某个教材。这就叫不完全依赖,或者部分依赖。出现这种情况,就不满足第二范式。
修改后,
选课表:

学生 课程 教师 教师职称 教室 上课时间
李四 Spring 张老师 java 讲师 301 08:00
张三 Struts 杨老师 java 讲师 302 13:30

课程表:

课程 教材
Spring 《Spring 深入浅出》
Struts 《Struts in Action》

所以,第二范式可以说是消除部分依赖。第二范式可以减少插入异常,删除异常和修改异常。
第三范式(3NF)
定义:如果关系模式 R 是 2NF,且关系模式 R(U,F)中的所有非主属性对任何候选关键字都不存在传递依赖,则称关系 R 是属于第三范式。
简单的说,第三范式要满足以下的条件:首先要满足第二范式,其次非主属性之间不存在函数依赖。由于满足了第二范式,表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖,就会存在传递依赖,这样就不满足第三范式。
上例中修改后的选课表中,一个教师能确定一个教师职称。这样,教师依赖于(学生,课程),而教师职称又依赖于教师,这叫传递依赖。第三范式就是要消除传递依赖。
修改后,
选课表:

学生 课程 教师 教室 上课时间
李四 Spring 张老师 301 08:00
张三 Struts 杨老师 302 13:30

教师表:

教师 教师职称
张老师 java 讲师
杨老师 java 讲师

这样,新教师的职称在没被选课的时候也有地方存了,没人选这个教师的课的时候教师的职称也不至于被删除,修改教师职称时只修改教师表就可以了。
简单的说,
第一范式就是原子性,字段不可再分割;
第二范式就是完全依赖,没有部分依赖;
第三范式就是没有传递依赖。

clipboard.png

阅读 2.2k

推荐阅读
我们俩
用户专栏

学习笔记 ,分享与反思。互联网搬砖工

86 人关注
74 篇文章
专栏主页