侧边栏壁纸
博主头像
qiuker

常态咸鱼,偶尔动弹

  • 累计撰写 7 篇文章
  • 累计创建 11 个标签
  • 累计收到 0 条评论

Mysql

qiuker
2022-03-07 / 0 评论 / 0 点赞 / 55 阅读 / 5,111 字

一.概述

1.数据库概述

数据库: DataBase,简称DB,就是按一定格式存储数据的数据仓库。

在计算机中,存储设备有内存和外存之分。而内存主要用于存放正在运行的程序和相关数据,无法永久保存数据,断电后数据会丢失;所以在计算机上存储数据肯定要写入外存(硬盘)中。

我们可以将数据库理解为一堆文件,但这些文件中的数据必需遵循一定的格式

注:文件是操作系统所抽象出的概念,本质就是硬盘中的数据。

数据库管理系统: DataBase Management System,简称DBMS,是一个用来科学组织和存储数据、高效获取和维护数据的 软件,是操作系统和用户之间的一层数据管理软件。人们往往也将数据库管理系统简称为数据库。

常见的数据库管理系统有Mysql、Oracle、Sql Server 等。

最简单的存储数据的方式就是新建一个文本文件并保存,再高级一点的就是用excel软件,还能对数据进行一定的管理、运算,数据库管理系统是功能更加强大的用于管理数据的软件。

2.关系型数据库概述

关系型数据库,是指采用了关系模型来组织数据的数据库,关系模型可以简单理解为二维表格模型,是数据库的主流模型。

关系模型中的概念:

  • 关系:可以理解为一张二维表,每个关系都具有一个关系名,即表名
  • 元组:可以理解为二维表中的一行,在数据库中经常被称为记录
  • 属性:可以理解为二维表中的一列,在数据库中经常被称为字段
  • 域:属性的取值范围,也就是数据库中某一列的取值限制
  • 关键字:一组可以唯一标识元组的属性,数据库中常称为主键,由一个或多个列组成
  • 关系模式:指对关系的描述。其格式为:关系名(属性1,属性2, ... ... ,属性N),在数据库中称为表结构

现在有三张表,分别为学生(学号,姓名),课程(课程号,课程名)和成绩(学号,课程号,成绩)

学生表中的主键是学号,课程表中的主键是课程号。

成绩表揭示的就是学生和课程的关系,其中学号这一列的值必须是学生表中存在的,课程号这一列的值必须是课程表中存在的,它们都是其它表中的主键,称为外键。此时称成绩表参照了学生表和课程表。

关系的三个完整性:

  1. 实体完整性:关系中主键不能为空,因为主键是区分不同元组的标识。
  2. 参照完整性:关系中的外键要不取空值,要不是被参照关系中主键的值
  3. 用户自定义完整性:用户可以定义完整性,比如规定年龄属性值小于200

关系型数据库中的数据必须要满足这三个完整性约束条件。

关系型数据库的优点:

  • 容易理解:二维表结构是非常贴近逻辑世界的一个概念,关系模型相对网状、层次等其他模型来说更容易理解
  • 使用方便:通用的SQL语言使得操作关系型数据库非常方便
  • 易于维护:丰富的完整性大大减低了数据冗余和数据不一致的概率

3.SQL概述

SQL(Structured Query Language)即结构化查询语言,是关系数据库的标准语言。

  • SQL 是所有关系型数据库所采用的语言
  • SQL 功能极强,除了查询外还提供数据的插入、修改、删除,数据库的创建、删除等等一系列功能

SQL 是关系型数据库的国际标准语言,不同的软件厂商提供的数据库都是以SQL 作为数据存取语言。但是每个厂商都会对 SQL 的命令集做一定的修改和扩充。也就是说 Mysql、Oracle 等软件的语法大体都遵循SQL标准,但也存在不少差异,并不完全相同。

关系型数据库是最为流行的数据库,其它数据库统称为 NoSQL。

SQL语言中的语句分为以下几类:

  • DQL:Data Query Language,数据查询语言,负责进行数据查询而不会对数据本身进行修改,用得最多的动词是SELECT,此外还有WHERE,GROUP BY,HAVING 等等。
  • DML:Data Manipulation Language,数据操作语言,负责数据访问工作,包括增删改操作,分别使用 INSERT、UPDATE、DELETE 等保留字
  • DDL:Data Definition Language,数据定义语言,负责数据结构定义与数据库对象定义的语言,主要由CREATE、ALTER、DROP组成,虽然也表示增删改,但增删改的对象是数据库中表的结构而不是数据
  • DCL:Data Control Language,用于控制用户对数据的访问权限
  • 其它:如事务相关的语言

4.Mysql概述

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一,与其他的大型数据库例如 Oracle、DB2、SQL Server 等相比,MySQL 自有它的不足之处,但是对于一般的个人使用者和中小型企业来说,MySQL 提供的功能已经绰绰有余,而且由于 MySQL是开放源码软件,因此可以大大降低总体拥有成本。

二. 索引

1.概述

索引是为了提高查询速率而创建的一种机制,通过数据结构实现:

  • InnoDB、MyISAM引擎的索引是通过B+树实现的
  • Memory引擎的索引是通过Hash表实现的

在执行SELECT语句时,如果没有索引就只能O(N)遍历的方式去寻找想要的数据

2.B+树

数据页:

页是mysql中磁盘和内存交换的基本单位,也是mysql管理存储空间的基本单位。也就是说不管是读取一行还是多行,其实都是先将行所在的页加载进来,每读取/写入一次页都要进行一次磁盘I/O。默认情况下,页的大小为16KB。

数据库中的一个表如果存在非常多的记录,则这些记录可能存在不同的页中。

B+树:

一个简化的B+树结构如下:

image.png

树上的每个节点其实就对应一个页,节点中可以存在多个元素,每个元素就相当于页中的一条记录。

B+树的特点是只有叶子节点才存放数据,而非叶子节点所对应的页中,每条记录的内容为:指向某个子节点的指针以及子节点中元素的最大值(最小值也可以)。

比如根节点[8,15],8指的是[2,5,8]中的最大值,15指的是[11,15]中的最大值

假设现在要查询13这个值,由于其大于8小于15,就可以直接判断出其必定位于15所指向的子节点。

B树结构大致如下:

image.png

B+树和B树的区别:

  • B树中非叶子节点也会存储数据

  • B+树的叶子节点会用链表连接起来,B树不会

  • B树的子节点个数为节点中元素数+1,B+数中子节点个数等于关键字个数

B+树的优势:

  • 中间节点不包含数据,可以容纳更多元素(数据项往往更大),从而可以降低树的高度,减少磁盘I/O次数

  • 查询性能更稳定(B树最好情况是在根节点就查到数据,最坏情况要查到叶子节点)

  • 所有叶子节点形成有序链表,便于范围查询(如查询值为3-11的元素)。

3.聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是InnoDB引擎中的概念

聚簇索引:是一种数据存储的方式,数据和索引存储在一起(比如B+树中叶子节点里存储数据)

非聚簇索引:数据和索引分开存储(比如B+树中叶子节点只存储记录中的主键值,而不是存储整行记录,然后再根据主键值去用聚簇索引找到整行数据

InnoDB引擎会在表创建时就默认创建一个聚簇索引,默认按主键排序,如果没有主键会用唯一非空列来进行代替

我们可以自己再创建索引(非聚簇索引,也成为辅助索引/二级索引),即再建立一棵针对非主键来进行排序的B+树。

聚簇索引和非聚簇索引的区别:

  • 聚簇索引的叶子节点存储数据记录,非聚簇索引的叶子节点只存储数据位置(主键值)

  • 一个表只能有一个聚簇索引,但可以有多个非聚簇索引

  • 聚簇索引查询效率更高(非聚簇索引查完还要再用聚簇索引来找到数据),但非聚簇索引再数据插入、删除、更新时效率较高(任何列发生改动,聚簇索引中的数据都要更新,非聚簇索引是针对特定列建立的,只有主键值/自己这列值发生改动时需要更新)

MyISAM 引擎不支持聚簇索引,B+树的叶子节点存放的就是数据的地址(MyISAM中数据放在一个文件中,索引放在一个文件中)

4.索引的分类

从功能上划分:

  • 普通索引:没有任何限制
  • 唯一索引:在创建表时使用Unique关键字会自动增加唯一索引
  • 主键索引:特殊的唯一索引,还要求不为空,创建表时声明为主键的会自动增加主键索引(InnoDB中就是聚簇索引)
  • 全文索引:搜索引擎使用的技术,利用分词技术等算法分析文中关键字的频率和重要性

按照物理实现方式划分:

  • 聚簇索引
  • 非聚簇索引

按照作用字段划分:

  • 单列索引:针对单个字段建立的索引
  • 联合索引:针对表的字段组合建立的索引

5.索引的创建

Mysql支持多种创建索引的方式:

  • 在创建表的定义语句CREATE TABLE时指定索引列

  • 使用ALTER TABLE语句在修改表结构时指定索引列

  • 使用CREATE INDEX语句创建索引

创建表的时候添加索引:

①在定义主键约束、外键约束和唯一性约束时,会默认给列加上索引

②在添加表级约束的位置,写上[UNIQUE] KEY|INDEX 索引名(列名),即可

适合创建索引的情况:

  • 字段数值有唯一限制,如学号

  • 频繁作为where查询条件的字段

  • 频繁在group by、order by子句中被用到的字段(B+树中排好序了)

  • 频繁使用distinct去重的字段

  • 数据类型所占空间小的字段,如INT字段加索引代价比 BIGINT字段加索引小

  • 对字符串字段创建索引时,可以截取字符串的前缀来建立索引

不适合创建索引的情况:

  • 在where、group by、order by子句中用不到的字段

  • 数据量小的情况没必要创建索引

  • 有大量重复数据的列(如性别)

  • 经常更新的字段

三. 事务

1.概述

事务的概念很难说清,例:

要求A向B转账10000,此时数据库中需要完成以下操作:

  • A的账户减去10000
  • B的账户加上10000

这两个对数据库的操作构成了一个事务

事务的原子性:一个事务中的所有操作,要么全部成功,要么全部失败(比如转账);若某一步发生了错误,会回滚到事务开始前的状态

可见,事务是出于操作数据的安全性所提出的一个概念;换言之,事务本质是为了多条DML语句能同时执行成功/失败所提出的一种机制

事务的实现:InnoDB引擎提供了记录事务活动的日志文件,从而在DML语句失败时能实现回滚

2.事务的提交和回滚

在事务的执行过程中,每一条DML语句都会被记录到日志中

在事务的执行过程中,可以提交事务和回滚事务

  • 提交事务:日志文件被清空,数据持久化地储存到了数据库的表中;提交事务标志着事务的成功结束

  • 回滚事务:撤销之前所有的DML操作,并且清空日志文件;回滚事务标志着事务的失败结束

默认情况下,每一条语句都是一个事务,且自动提交

通过commit;语句可以提交事务

通过rollback;语句可以回滚事务

通过start transaction;语句,表明接下来的所有DML语句共同构成一个事务,直到用commit;语句提交事务或用rollback;语句取消前面所有的DML语句

3.事务的特性

事务必须满足4个条件(ACID):

  • 原子性Atomicity):一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态。
  • 一致性Consistency):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性Isolation):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别。
  • 持久性Durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

隔离性主要用于当两个用户同时访问一个数据库并开启事务时,称之为事务的并发

两个事务间隔离性的四个级别(从低到高)(以下都表示事务A的级别):

  • 读未提交(Read uncommitted):事务A可以读取到事务B未提交的数据,隔离级别最低,会发生脏读
  • 读提交(read committed):事务A可以读取到事务B已提交的数据,不会发生脏读现象,但不可重复读取(这个意思是当事务B提交数据后又修改了数据,事务A读到的是B修改后的数据,从而可能导致每次读到的数据都不一样)
  • 可重复读(repeatable read):事务A每次读取到的都是事务B刚提交时的数据,是Mysql中默认的隔离级别
  • 串行化/序列化(Serializable):最高的隔离级别,不允许并发事务

关于可重复读的理解:

假设银行在做帐,读取数据所需花费的时间非常多,Select语句从1点跑到3点。而如果有人在这期间存/取款导致数据发生变动,也应该要读取1点时的数据,所以可重复读是更高的隔离级别。

使用语句select @@transaction_isolation;查看当前的隔离级别

0

评论区