存储过程

数据库SQL语言层面的封装与重用

优点

  • 存储过程可以进行封装、影藏逻辑
  • 存储过程可以回传值，并可接收参数

  • 执行效率高

    缺点

  • 不同数据库的存储过程语法不一样，难维护

  • 业务逻辑在数据库中，难以迭代

    三大范式

    第一范式

    表中的每一列都是不可再分的基本数据项
    image.png

    第二范式

    必须满足第一范式，表中的字段必须完全依赖于全部主键，即其他字段组成的这行记录和主键表示的东西完全一致，而主键是唯一的，所以他们依赖于主键也就成了唯一的

    第三范式

    满足第二范式，非主键的所有字段互不依赖。比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段

    优缺点

    范式化

  • 优点

减少数据冗余、表中重复数据少 更新较快、表较反范式化的表较小

  • 缺点

查询时候需要多表联合查询、查询效率较低、增加索引优化难度
反范式化

  • 优点

减少表的关联、更好的进行索引优化

  • 缺点

数据冗余、对数据修改成本较高

视图

视图时基于数据表的一种虚表

  • （1）视图是一种虚表
  • （2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
  • （3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
  • （4）视图向用户提供基表数据的另一种表现形式
  • （5）视图没有存储真正的数据，真正的数据还是存储在基表中
  • （6）程序员虽然操作的是视图，但最终视图还会转成操作基表
  • （7）一个基表可以有0个或多个视图

示例表

  1. CREATE TABLE `message` (
  2.     `id` int(9) NOT NULL AUTO_INCREMENT,
  3.     `name1` varchar(90) NOT NULL,
  4.     `name2` varchar(90) NOT NULL,
  5.     `data` varchar(90) NOT NULL,
  6.     PRIMARY KEY (`id`)
  7.   ) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8

创建视图

  1. create view msg_view as
  2. select
  3.   count(1)
  4. from
  5.   message

查询视图 

  1. select * from msg_view

删除视图

  1. drop view view_name

更新视图 

  1. create or replace view view_name []

视图就是基于查询的一种虚表，也就是说，视图可以将查询出来的数据进行封装那么我们在使用的时候就会变得非常方便，使用视图可以让我们专注与逻辑，但不提高查询效率。

Mysql 数据库索引B+树和B树的区别

  • B+树得非叶子节点只存储关键字和指向节点得指针，叶子节点之间用指针相连，而B树得非叶子节点还存储数据
  • B+树叶子节点存放了关键字和数据，且多个节点用链表连接

  • B+树非叶子节点不存储数据

    聚集索引和非聚集索引（辅助索引）

  • 聚集索引

每个表只有一个聚集索引，叶子节点保存主键得值和所表示得数据

  • 辅助索引

叶子节点保存索引字段得值和主键得值

数据库事务

  • atomicity（原子性） ：要么全执行，要么全都不执行即要么全部执行成功要么全部执行失败；
  • consistency（一致性）：在事务开始和完成时，数据都必须保持一致状态—不能破坏关系数据的完整性以及业务逻辑上的一致性，入转账逻辑无论操作正常与否最终转账两端的总额应该是不会发生变化的；
  • isolation（隔离性） ：事务处理过程中的中间状态对外部是不可见的—一个事务在所作的修改在最终提交之前，对其他事务是不可见的；

  • durability（持久性） ：事务完成之后，它对于数据的修改是永久性的。

    日志相关

    BinLog

    binlog 用于记录数据库执行的写入性质的操作，以二进制形式保存在磁盘中，其使用场景为主从复制
    数据恢复。

  • 主从复制

在Master端开启binlog 然后将binlog发送到各个slave端，salve端重放binlog从而达到主从复制

  • 数据恢复

通过mysqlbinlog工具进行数据恢复

Redo Log

在事务的四大特性中有持久性即只要事务提交成功，那么对数据库的修改就是永久的，无法再返回之前的状态，如何保证数据的一致性？最简单的做法就是每次事务提交的时候，将该事务涉及到的修改的数据页全部刷新到磁盘中，但是这种机制存在严重的性能问题，具体表现在

  • Innodb是以页为单位进行磁盘交互的，一个事务可能修改一个页中的部分数据，这时候如果将完整的数据页刷新到磁盘，则太浪费资源
  • 一个事务可能涉及修改多个数据页，数据页在物理上并不连续，使用随机IO写入性能太差

mysql设计了redo log 来记录事务对数据页做了哪些修改。redo log包括两部分，分别为
内存中的日志缓冲和磁盘上的日志文件。
mysql中执行的每一条DML语句都会先记录到日志缓冲中，然后在某个时间再一次性的将多个记录写入到日志文件中，这种先写日志再写磁盘文件的技术叫做WAL（Write ahead logging）

Undo Log

Undo log 是逻辑日志，将数据库恢复到之前的样子。如果事务时insert 其对应的回滚操作就是delete，如果时delete，回滚操作就是insert等。Undo的作用就是回滚操作，另一个作用就时MVCC。当用户读取一行记录时，如果当前记录已经被其他事务占用，则当前事务通过undo log来读取之前的行版本信息，因为没有事务需要对历史数据做修改操作，所以不需要加锁，已此来实现非锁定读取。

锁

锁的类型

表锁

  • 表锁使用场景

事务需要更新某张表大部分数据时候，如果使用默认的行锁，执行效率底下，而且有可能造成其他事务长时间等待锁和锁冲突
事务涉及多个表，比较复杂， 可能会引起死锁，导致大量事务回滚，可以使用表锁

  • 表锁分为表读锁以及表写锁

加表读锁 （共享锁 shared lock）

  1. -- 加锁
  2. lock tables tablename read;
  3. -- 解锁 
  4. unlock tables;

如果对其表进行了表读锁，那么所有连接只能查询，执行更新添加等操作当前连接会报错，其余连接会阻塞等待直到锁释放。
image.png
写锁 排他锁 exclusive lock

  1. -- 加锁
  2. lock tables tablename write;
  3. -- 解锁 
  4. unlock tables;

image.png
如果对表进行表写锁，只有本连接可进行操作，其余连接所有操作都会阻塞等待，直到锁释放。
总结

  • select操作会隐式的上读锁

  • insert delete update隐式上写锁

    行锁

    Mysql的行锁是在引擎层由引擎实现，MyISAM是不支持行锁的，行锁是针对表中行记录的锁,事务A更新了一行数据，事务B也要更新同一行数据，必须等事务A的操作完成才能进行更新。
    两阶段锁 加锁阶段和解锁阶段，且加锁阶段和解锁阶段互不相交。InnoDB事务中，行锁是需要的时候才加上的，不是不需要了就立即释放，是要等到事务结束才能释放。

  • select 默认不加锁

  • update delete insert 上写锁

  • 显示上锁

    1. select * from tableName lock in share mode;//读锁
    2. select * from tableName for update;//写锁

  • 手动解锁

    1. 提交事务（commit）
    2. 回滚事务（rollback）
    3. kill 阻塞进程

    为什么使用数据库索引可以提高效率

    默认执行得SQL是进行全表扫描，遇到匹配条件得就加入搜索结果集，如果存在索引，就会到索引表中定位到特定值得行，减少匹配次数，索引相当于把数据变成了相对有序的数据结构

    数据库外键的优缺点

    优点： （1）能最大限度的保证数据的一致性和完整性。 （2）增加ER图的可读性。
    缺点： （1）影响数据操作的效率。 （2）增加开发难度，导致表过多。

    mysql 主键使用自增id和uuid的优缺点

    自增id

    优点

  • 数字类型，占用空间小，写入速度相对快

  • 数据库自动增量排序，对检索有利，读速度快

  • 系统编码过程中，可以不指定id，数据库自增

    缺点

  • 因自动增长，当手动插入的时候会出现麻烦，主键冲突的情况会有发生

  • 分表之后控制不好会出现主键重复现象

  • 新老数据合并，要是新数据主键也是数字类型，想新老数据区分会出现一些冲突

    uuid

    优点

  • 全局唯一性，不用担心重复的现象，对数据的拆分、合并比较友好

    缺点

  • 字符串类型，占用空间比较大，读写速度慢，而且索引会随着数据量的增加越来越难用