数据库学习笔记Day05 索引，事务，数据库设计案例

索引

数据库中的数据的查询速度会随着数据量的增长逐渐变慢，导致响应用户的请求速度变慢。

索引就是提高数据表中数据查询效率的。

数据准备：

DROP TABLE testindex;
CREATE TABLE testindex(
  fid INT PRIMARY KEY,
  sid INT UNIQUE,
  tid INT,
  tname VARCHAR(20),
  remark VARCHAR(20)
);

DELIMITER//
CREATE PROCEDURE add_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i<=1000000 DO
    INSERT INTO testindex(fid,sid,tid,tname,remark) VALUES(i,i,i,'name','remark');
    SET i=i+1;
  END WHILE;
END//

CALL add_data()

慢的要死！！！！

SELECT fid FROM testindex WHERE tid=444444

查询数据变慢，就像一页一页的查字典，索引就像是根据拼音查字典的目录。

将数据中的某一列或几列取出，构建成一个便于查找的树形结构进行存储，生成数据表的目录，党对数据进行查询时，先在目录中进行查找得到对应的数据地址，然后在数据表中根据地址快速获取数据记录，避免全盘扫描。

索引的分类

根据创建的索引的列不同分为

主键索引：在数据表的主键字段创建的索引

唯一索引：在数据表的唯一列创建的索引，此列所有值只能出现一次，可以为NULL

普通索引：在普通字段创建的索引

组合索引：两个或以上字段创建的索引

查询数据表的索引：SHOW INDEXES FROM testindex

说明：在创建数据表时，将字段声明为主键，会自动在主键字段会创建主键索引。

            在创建数据表时，将字段声明为唯一键，会自动在唯一字段创建唯一索引。

索引的创建

唯一索引：（在没有重复值的列创建）：CREATE UNIQUE INDEX index_test1 ON testindex(tid);

普通索引：CREATE INDEX index_test2 ON testindex(remark);

组合索引：CREATE INDEX index_test3 ON testindex(tid,tname)

全文索引：

        可以通过此索引进行全文检索，因为MySQL全文检索不支持中文，因此并不被关注，在应用开发中，通过数据库中间件来实现全文检索。

        语法：CREATE FULLTEXT INDEX <index_name> ON 表名（字段名）

索引的使用

索引创建完成之后无需调用，当根据创建索引的列进行查询时会自动使用。

SELECT fid FROM testindex WHERE sid=444444

组合索引也需要根据索引的所有字段查询时

SELECT * FROM testindex WHERE tid=450000;

 

SELECT * FROM testindex WHERE tid=450000 AND tname='name';

 

索引的使用总结

事务

数据库事务的介绍

数据库事务的特性

 

 事务的隔离级别

 

 

 

 设置MySQL默认隔离级别

语法： 

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;

数据库设计

数据库是用来存放数据的，我们需要设计合理的数据表--能够完成数据的存储，同时能方便的提取应用系统所需的数据

数据库设计流程

数据库是为应用系统服务的，数据库存储什么样的数据也是由我们的应用系统来决定的。

我们在进行应用系统开发时，首先要明确应用系统的功能需求--需求分析。

1、根据应用系统的功能，分析数据实体（要存储的数据对象）

         电商系统：商品，用户，订单......

         教务管理系统：学生，课程，成绩......

2、提取实体的数据项（实体的属性）

          商品（商品名称，商品图片，商品描述......）

          用户（姓名，登录名，登录密码......）

3、根据数据库设计三范式规范视图的数据项

           如果实体的数据项不满足三范式可能会导致数据的冗余，从而引起数据维护工作的困难，破坏数据的一致性。

4、绘制E-R图（实体关系图，直观的展示实体和实体之间的关系）

5、数据库建模

            1、三线图进行数据表设计

             2、PowerDesigner

             3、PDMan

6、建库建表（编写SQL语句创建数据库，数据表）

7、添加测试数据，SQL测试

数据库设计案例

学校图书管理系统（借书）

1、数据实体

学生

类别

图书

借书记录

管理员

2、提取数据项

学生（学号，姓名，性别，年龄，院系编号.......）

院系信息（院系编号，院系名称，院系描述.......）

类别（类别id，类别名称，类别描述......）

图书（图书id，图书名称，图书作者，图书封面，图书价格，图书库存......）

借书记录（记录id，学号，图书编号，数量，是否归还，借书日期，还书日期......）

管理员（管理员id，登录名，登录密码，员工编号......）

员工信息（员工编号，员工姓名，联系方式......）

3、数据库设计三范式

第一范式：要求数据表中的字段不可再分  数据库中创建不出不满足第一范式的表

错误：

第二范式：不存在非关键字段对关键字段的部分依赖
错误：

 解决：根据其依赖关系将表分为3个

 第三范式：不存在非关键字段之间的传递依赖

错误：

 将关键字段和被依赖的非关键字段分别作为主键，检查依赖关系。

解决：

 4、绘制E-R图

实体关系图，用于直观的体现实体与实体之间的关系（一对一，一对多，多对一，多对多）

 

5、三线图