【041】Python全栈日记-MySQL（四）

 

一、数据库范式与设计基本原则

1、关系型数据库的特点

基本组成单位为二维表

各二维表之间存在一定的关系

 

为什么我们要用多张表而不是一张表呢？为什么不能把所有的信息都放在一张表中呢？

以一张上课出勤表为例：

如果我们说有的数据都放在一起

首先会有大量的重复数据

其次如果发生班主任或者其他信息变更，需要修改的地方会很多。

 

 

2、范式

范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求，满足不同程度要求的为不同范式。

 

级别越高重复数据越少。

 

（1）第一范式：字段不可再分。只要在关系型数据库中建立了一个二维表就符合第一范式

 

（2）第二范式：每个表都有一个能区分每条记录的主键（主键：非空 + 唯一 + 索引）

 

（3）第三范式：指表中的所有数据元素不但要能惟一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系

 

说人话就是：

第三范式要求一个表中不要包含在其它表中已有的非主关键字信息。

 

以学生表和班级表为例，‘学生’和‘班级’是2个相对独立的概念，应该分别建立学生表和班级表。

学生表的学生用主键学号区分，没有多余的重复记录

班级表的班级用主键班号区分，没有多余的重复记录

学生对班级存在依赖关系，学生要属于一个班级，如果按照如下设计，会有重复：

班级表：班号、班名

学生表：学号、姓名、班号、班名（已经存在于班级表中，不是主键，再放到学生表中数据就有重复了）

把学生表中的班名去掉，保留能唯一区分班级的班号，学生表对于班级表来说就符合第三范式了

 

 

 

3、数据库表设计原则

（1）符合第二范式 （每个表都有主键）

（2）尽量满足第三范式（根据业务需求，数据库服务器架构、性能要求灵活掌握）

 

设计方式

根据需求，提取业务中相对独立的业务要素（如：学生、班级、课程、家长、教职员工、学院、系、岗位、教学楼，教室、设备、厂商、IP地址、课程表、考试、成绩表、字典表（性别、职称、岗位……）等等），为每个业务要素建立单独的表并建立主键，确保无重复记录

 

根据需求，理清业务要素及其所建的表间的三种关系：

一对一

一对多

多对多

并根据情况建立表间的约束关系.

 

 

4、表列间的三种关系：

（1）一对一

例如：设计人员先建立学生表，存储学号、姓名、性别、生日、身高、体重、高考分、班号的信息，后来学校要求还要存储学生的照片和备注说明等信息。

设计人员可以在原来学生表中添加照片和备注说明2列内容，也可以为每个学生单独建立一张学生信息表用来存储学生照片和备注信息；

如果这样设计，学生表的学号列和后建的学生信息表的学号列之间就是一对一的关系，即学生表的一条行记录对应学生信息表的一条行记录。

 

有时一张列比较多的表，可以根据列拆分成几张小表，每个小表都有一样的主键，每个小表之间就是一对一的关系

 

（2）一对多

例如：当前学生表的班号和班级表的班号就是一对多的关系，一个班级可以包括多名学生、一个学生只能属于一个班级。一对多的例子有很多，例如：一个城市只能属于一个国家，国家和城市就是一对多的关系；教室只能属于一个教学楼，教学楼和教室就是一对多的关系，等等

 

图示一：

图示二：

 

此类图可以通过MySQL第一期中提到的Navicat Premium软件实现：

选中有关联的表，然后选择建模

 

 

（3）多对多

例如：学生和课程之间就是多对多的关系，一个学生可以选择多门课程，一个课程也可以有多名学生来学。

演员和电影可是多对多的关系，一名演员可以参演多部电影，一部电影可以有多名演员来参演。

 

设计时，已学生和课程为例，分别建立学生表和课程表，确保学生表无重复，课程表无重复，然后建立关系表，分别包含学生编号和课程编号，这样可以将多对多的关系利用中间表，变成两个一对多的关系。如下图：

 

 

（4）如何确定表间的三种关系：

主要根据业务需求来定，业务需求决定表间关系。

举例说明：一般来说班级和学生是一对多的关系，但是如果实际情况是一个学生可以自由选择加入多个班听课，也可以把班级表和学生表设计成多对多的关系。这样可以满足需求，不会出现表中没有位置存储数据的问题。

 

【1】一对多 变成 多对多

假如业务需求变更，一个学生可以属于多个班，需要将一个学生对应的多个班号存入数据库表，数据库表需要进行调整，通常会引起程序的级联调整。

通常有2种办法：

 

方法1：在学生表添加额外的班号列，cno1,cno2.......这种办法可以临时解决问题，比如规定一个学生最多可属于3个班，加3列就可以了。但是如果没有限制，如极端情况，一个学生可以有100个班，加列的方式就有局限性了。需要采用中间表的形式。

 

方法2：给学生表和课程表建立一个中间表（比如：rel_class_stu），该表分别包括学号(sno)和班号(cno)，这样的设计是正规的多对多的设计方式，灵活度比方法一要大，方法一的加列的方式往往用于因需求调整而采取的一种临时补救措施。

原来一个学生只能属于一个班，现在学校规定调整为一个学生最多可以属于5个班，就在学生表中再增加4列班号。此时，如果要采用方法2建立中间表的方式，可能需要对现有数据进行迁移会有更多额外的工作要做。

 

   

 

二、索引

索引是一种可以让SELECT语句提高效率的一种数据结构

 

1、什么是索引

索引可以起到快速定位数据的作用

索引在生活中的一些例子：字典的索引页、图书的目录页、Word文档的文档结构树、黄页、术语表。

最常见的一个是我们操作系统的开始菜单：

 

 

2、索引的优缺点

优点：某些情况下使SELECT语句大幅提高效率，合适的索引可以优化MySQL服务器的查询性能，从而起到优化MySQL的作用

缺点：表行数据的变化（insert, update, delete），建立在表列上的索引也会自动维护，一定程度上会使DML（insert, update, delete）操作变慢；索引会占用磁盘额外的存储空间。

 

 

3、索引如何加快查询

（1）引入

假设我们有一本没有索引表的字典，我们怎么找‘齉’’这个字呢？对于我们来说这个字完全不知道读音，又没有索引表可以通过笔划查询，那只能从第一页开始翻，直到翻到这个字，可是我们如果有了索引表，通过笔划很快就能找到这个字。

 

 

（2）所以用人话解释一下索引的好坏：

索引页的好处

方便查找你要找的字（每个字类比库表的行）

索引页的坏处（代价）

索引页要占用几页纸（数据库要耗费磁盘空间存索引）

字典出版前，要有人工来精心制作索引页（数据库维护索引要占用CPU资源）

 

（3）思考一下什么情况下新华字典索引页有用？

情况1：从字典的上万汉字中查找少量汉字（索引页非常有用）

情况2：从字典的上万汉字中查找所有汉字（索引页没有任何用处）

做好的办法就是从字典的第1页翻到最末页（不使用索引页，挨篇翻）

如果还要用索引页（要翻的页数更多，每页都要翻到，还要翻查多次索引页）

 

 

4、类比新华字典和数据库

（1）表页面对比字典

（2）索引页面对比字典

 

5、索引要占用额外磁盘存储空间

例（oracle数据库）：scott库 T表表段所占用users表空间中的空间，图中黄色部分，T表100万行记录，记录越多占用的格子(类比“页”)就越多

scott库 T表 的表列id上创建的 索引 t_ind (段) 所占用users表空间中的空间，图中黄色部分

 

 

6、MySQL 创建索引

建表时创建索引

 create table t(id int,name varchar(20),index idx_name (name));

 

给表追加索引

 alter table t add unique index idx_id(id);

 

给表的多列上追加索引，以下2种方式均可

alter table t add index idx_id_name(id,name);

create index idx_id_name on t(id,name);

 

 

7、MySQL 查看索引

使用show语句查看t表列上的索引，以下2种方式均可：

show index from t;

show keys from t;  --mysql中索引也被称为key

 

使用show create table语句查看t表列上的索引：

show create table t\G

 

 

8、MySQL 删除索引

 

使用alter table命令删除索引

alter table 表名 drop index 索引名

alter table t drop index idx_id;

 

使用drop index命令删除索引：

drop index 索引名 on 表名

drop index idx_name on t;

drop index idx_id_name on t;

 

 

9、MySQL索引基本原理

stu有1百万个学生，1百行记录，假如分别存放在5000个页中（页page是最小的逻辑存储单元，默认一个页16k大小，系统会给每个页分配一个唯一编号）

 

如果查询select * from stu where sno=13不使用索引,会翻遍所有5000个页，看看页中有没有行记录的sno号值等于13的，有就符合where条件，将该行记录保留在查询结果集中。

如果每个页挨个都要翻一遍，行记录很多的时候，查询就会很慢

 

如果在stu表的sno列上创建索引alter table stu add index idx_sno(sno);

首先会提取stu表中所有记录sno的值，在内存中按照从小到大的顺序排序

排序后的结果形成了很多索引页(page)，这个过程由mysql按照算法自动完成，不用过于深究其中的详细过程

 

索引页之间存在一定的关联关系，一般为树形结构;分为根节点、分支节点、和叶子节点

根节点页中存放分段sno的起始值，以及值所对应的分支索引页号

分支索引页中存放分段sno的起始值，以及值所对应的叶子索引页号

叶子索引页中存放排序后的sno值，该值所对应的表页号, 下一个叶子索引页的页号

 

sno建立索引后，执行select * from stu where sno=13查询过程如下：

第一步 索引页存在关联关系，先找索引页号20的根节点，13在>=11和<17的范围内，需要查找25号索引页

第二步 读取25号索引页，13在>=11和<14范围内，得到了26号叶子索引页

第三步 读取26号叶子索引页，找到了13这个值，以及该值所对应表页的页号161，因为是select * 所有列，目前只得到了sno的值，还要得到sname,sex,height等，因此需要再读一次编号为161的表页，里面存放了sno之外的值

sno建立索引后，执行select * from stu where sno=13查询过程如下：

第四步 读取161号表页，获得sname,sex,height等值

以上4步，只读取了3个索引页1个表页，共4个页，比读取所有表页（5000个页），按照sno=13

 

 

10、MySQL 索引使用条件和弊端

大表（记录数多），仅从中找出少量行（总行数的3%-5%）

例如：100万个学生中，学号是主键没有重值，仅找1个学生，在学号列上加索引并按照学号来查找会很快找到

 

例如：100万个学生中，学号1到100万，要找学号大于1的学生，这样99%以上的结果都会返回，此时索引没有作用，不能起到加速查询的作用

 

在经常作为查询条件(where)的列上添加索引，返回记录少，就可能用上索引，起到加速查询的作用

 

索引和表的区别是索引页之间存在关联关系，但是会占用额外的磁盘空间，有可能出现索引占的磁盘空间比表还大的情况

 

例如：在表上根据常用查询条件，使用多个列建立了索引

索引会自动维护，常规的DML操作会导致索引的变化，这会增加服务器的负担，导致DML操作变慢，尤其是一个表有多个索引的情况下

 

11、图形界面

当然在我们工作中可以借助图形界面，虽然单位肯定不会提供，毕竟收费软件还挺贵。