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

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

---

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

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

---

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

---

视图

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

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

为什么使用视图：

• 重用SQL语句

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

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

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

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

使用视图

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

  

  

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

  

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

  

  

• 使用视图简化计算字段

索引

普通 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列上面建立索引
  
  这样，可以加快前面的查询的速度。

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

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

  还可以建立多列索引

  

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

  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:[e-mail],phone:[phone]
20080902	李四	女	email:[e-mail],phone:[phone]

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

学生编号	姓名	性别	电子邮件	电话
20080901	张三	男	[e-mail]	88886666
20080902	李四	女	[e-mail]	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 讲师

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