数据库（基础知识）

数据库

范化

范化是在识别数据库中的数据元素，关系，以及定义所需的表和各表中的项目这些初始化工作之后的一个细化过程

常见的有1nf 2nf 3nf bcnf 4nf

1nf 第一范式 

数据库表的每一列都是不可分割的，同一列不能有多个值 即 一个实体的某个属性不能有多个值或者不能有重复的属性  如果出现重复的属性，可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多 

第一范式就是要求属性值不可再分成更小的部分，即属性不能是属性组合   就是无重复列

id 电话 --> id 手机 台式   1nf

 

2nf 第二范式

第二范式是在第一范式的基础上建立起来的，第二范式要求每个实例可以被唯一区分 通常是加上id 

如果关系R为第一范式，并且r中的每个非主属性完全依赖于r的某个候选键，称r为第二范式

如果a是r中的一个候选键，则称a为r的主属性

注：每个非主属性都需要依赖于主属性

学号 课程号 成绩 学分    关键字为（学号，课程号） 但是非主属性学分仅依赖于课程号，对关键字是部分依赖，而不是完全依赖  因此需要拆表

 

 

 

3nf 第三范式

关系r属于第二范式，且每个非主属性都不传递依赖于r的候选键 则为第三范式

学号 姓名 课程号 成绩     姓名无重复

两个候选码 学号，课程号  姓名，课程号

学号->姓名  学号，课程号->成绩  姓名，课程号->成绩

唯一的非主属性成绩不存在部分依赖，也不存在传递依赖 

 

bcnf 

关系模式r为第一范式，且每个属性不传递依赖于r的候选键则为bcnf

 

数据库范式

    1NF:每一条满足原子性,不可再分解

    2NF:每一条满足唯一性,记录有唯一标识

    3NF:字段不冗余

 

sql语句应该考虑哪些安全性

防止sql注入,对特殊字符进行转译,过滤

使用最小权限原则,为不同的操作或用户建立账户

sql出错时,不要把数据出错的信息暴露在客户端

 

优化sql的方法

    打字段时设置为not null

    使用join代替子查询

    避免使用select *

    用exists 代替in  用 not exists 代替not in

    使用union代替手动创建临时表

    尽量减少使用like与通配符

    使用事务与外键

    使用索引

    生成缓存

 

mysql数据库引擎

    myisam  isam  innodb cvs 

 

myisam与innodb的区别

    基本差别:myisam不支持事务,innodb支持  myisam的性能较强

    myisam 适用于频繁的查询应用;表锁,不会出现死锁;适合小数据,小并发

    innodb  适合插入,更新较多的应用,行锁;适合大数据,大并发

    innodb与myisam最大的区别:支持事务     采用了行锁

 

锁

    数据库是一个多用户使用的共享资源,当多个用户并发的存取数据时,在数据库中会产生多个事务同时存取的情况,此时     需要加锁来对数据库进行控制

    基本锁:行级锁,表级锁

 

索引

索引是一种特殊的查询表,b+树,索引是唯一的,创建索引允许一~多个列,缺点是录入减慢,尺寸加大

 

    b树     b+树     哈希索引

b树/b-树     --     二叉/多路搜索树

         从根节点开始,对节点内的关键字有序进行二分查找,如果命中则结束,否则进入查询所属的儿子节点,直到对应儿子指针为空,或者为叶子节点

                                                                    

b+树     --     b-树的变体 多路搜索树    

         b+树只有到达叶子节点才命中       非叶子结点仅有索引作用,不包含实际的值   

         有k个叶子节点必然有k个关键字

         所有叶子节点构成一个有序链表

        

b树与b+树的区别:

         B+树在内部节点上不保存数据信息,在内存页可以有更多的key.     数据存放的更加紧密,具有很好的空间局部性,因此访问叶子节点上关联得数据有更好的缓存命中率

         B+树的叶子节点都是相链的,对整个树的遍历只需要一次遍历叶子节点即可.而且数据顺序排列,且相连,便于区间查找与搜索.     而B树需要每一层递归遍历,相邻元素可能在内存中不相邻,缓存命中没有B+树好

         B树每个节点都包含k/v,经常访问的元素可能离根节点近,访问迅速

 

哈希索引

         哈希索引就是采用一定的哈希算法,把key值转换成新的哈希值,只需要一次哈希算法即可立刻定位到相应位置

 

索引有B+树,哈希索引,MySql里常用为B+树

哈希索引:哈希索引就是把键值根据哈希算法换成新的哈希值,只需一次计算即可定位

1. 但是对于其值是根据链表查找

2. 因为索引为计算后的哈希值,因此无法用于排序

3. 只支持等值查询,不支持范围查询

4. 存在哈希冲突的问题

B+树索引:B+树是一个平衡多叉树,根节点到叶子节点高度差值不超过1,同级间有指针相互链接

1. 在B+树中,所有记录节点按键值大小顺序排在同一层

2. B+树中,常规检索,根节点到叶子节点搜索效率相当,无大波动

3. 可以基于索引顺序扫描

 

NoSql索引:

NoSql的索引采用B(B-)树

原因:

B+树与B树最大的区别在于:B+只有叶子节点存放数据,其余节点存放索引

B树是每个索引点都会有个Data域,因此更适合nosql使用

 

索引优化

联合索引     

         最左前缀     如果使用了最左前缀,但是顺序颠倒,是可以使用索引的

前缀索引

         

优化策略

         最左前缀匹配原则

         主键外键一定要建立索引

         对于where on groupby 出现的列使用索引

         为较长的字符串使用前缀索引

         对于like查询 %放在前面就不能使用索引了     因此需要%放在后面

         尽量扩展索引,而不是新建索引

 

数据库的游标

    游标是数据库中的指针,尽管能遍历所有的行,但是一次指向一行,是一种从包含多条记录结果集中每次提取一条记录的机制

    sql的游标就是一种临时的数据库对象,可以存放数据库表中的数据行副本

    游标的常见用途是保存查询结果,结果集由SELECT生成,如果处理过程需要重复使用一个记录集,可以创建游标使用多次

 

事务

隔离

    未提交读     都不能避免

    已提交读     避免脏读

    可重复读     避免脏读,不可重复读

    可序列化     避免脏读,不可重复读,幻读

 

脏读     

       一个事务正在对一条记录做修改,在提交前,另一个事务来读取这条数据,第二个事务读取到的就时脏数据

不可重复读     

       一个事务正在读取的某些数据发生了改变,或者记录被删除     这种现象叫做不可重复读

幻读   

       一个数据按照相同的查询条件读取以前的数据,却发现了其他事务插入的新数据叫做幻读

注

1. innodb的行锁是通过索引上的索引项来实现的     只有通过索引检索数据,innodb才会使用行锁,否则则会使用表锁