MySQL关系型数据库基础理论详解

MySQL数据库基础

数据库管理基础

关系型数据库：存储下来表现为表，但表中数据不能过度冗余(由行和列组成的二维表），一个表可以没有行但必须有一个列；最终提供的数据就是行，跟列关系不大，列只是说明其是什么属性

数据库的模型：

·数据模型

·层次模型

·网状模型

·关系模型

·非关系模型：（在某种特定场景当中能够满足某种特殊应用的数据模型机制）

DBMS：数据库管理系统基本概念

比如：如果我们的数据只像某一个文件 ，比如文件/etc/passwd那么早期使用文本文件存储数据是没有任何问题的，无可非议。

但是，如果用文件存储数据的方式，存在很大的缺陷：

·数据可能导致大量的冗余

·难以描述数据实体之间的关系，比如我们一个用户属于一个表这种关系是容易定义的，我们通过在用户的库中加以字段调用其gid实现用户和组之间的对应关系，但是更复杂的数据关联性，文件将无法进行描述

·如果数据量非常大的时候，我们检索出来符合条件的部分数据，将会变得非常困难 （比如以passwd为例，其文件增长到百G以上的时候，如果我们要求在此文件找出以字母r开头的用户）

·无法保存数值 在数值很多处理属性上文本是无法满足的

因此需要一种按需定义数据定义保存格式来完成数据存储并且在必要的时候能够完成数据处理，如果一个应用程序使用文本文件存储数据，那么跟数据库打交道的接口无非就是文件系统的系统调用接口，既然应用程序必须通过文件系统的系统调用接口直接跟文件的数据流打交道，那么就意味着程序本身必须完成文件描述符的管理，要自己维持打开的文件，文件内容的指针（比如读到第几行的指针）缓冲区是否需要同步等都需要使应用程序自身来完成，假如说这个应用程序需要访问文件中的某一点文件内容而不是全部的，它需要在这个文件中所有内容载入到内存中才可以访问筛取等操作，因为它无法判断符合条件的内容存放在哪个位置，在文件系统这个层次上，本身也不是有更细层的逻辑组件的；

比如我们想通过应用程序查找一用户名为root的账户，那么root用户具体在哪个数据块中呢？

那么再假如可以只载入部分数据，那么也就意味着我们自己能够管理一个block，但问题是指定r开头的用户在哪个block里面吗？很显然是不行的，那么这时它需要一个更高层的抽象层，在其接口之上又提供了一种管理工具-数据库，它既可以跟文件系统打交道又可以使用文件系统的数据块来存放数据，最终存在文件系统上的的数据一定是数据块，但是它又可能将这些数据块合并为更高级别的逻辑存储单元

举个例子：

1个数据块的大小是1kb,那么它将4个数据块合并成一个更大的数据块,并且通过这么接口向硬盘存放数据的时候它会按照数据块为单位，以盘区为单位（大方块）存储的,也就是我们存放的数据最多存放这个4个块上，无论沾满还是占不满,如下图所示

索引

还是以passwd文件为例

[root@localhost ~]# head -3 /etc/passwd

root:x:0:0:root:/root:/bin/bash

bin:x:1:1:bin:/bin:/sbin/nologin

daemon:x:2:2:daemon:/sbin:/sbin/nologin

我们可以看到，passwd文件是以行组成的，于是我们定义通过这些信息对比我们发现，一个数据块只能存储7行左右的信息，还多一点，因为我们可以看到其文件内容每个用户的用户名属性信息长度是不等的，有时候其数据块能够存7行，有的可存6行等，但不管存储多少行，这个抽象层次自己知道里面到底存放了多少行，是有自知之明的。

每一行的行id 靠内部机制的映射关系，记录了在某行至某行间存储在某个数据块中，以此类推

所以，如果我们希望想访问第一行到第5行的时候，则不需要将所有的数据块载入，则可以直接载入第一个数据块，因为它有了更高级的映射关系，意味着数据块比文件更高于一级别，但是可以作为独立存储单位的数据单元有了这个层次以后，就不需要为了访问部分文件内容而载入整个文件了

那么这时问题又来了：

假如找到以r开头所有用户的用户名，很显然图中的所有块中很可能都存在，所以将其所有的块载入再去找，跟载入全部文件再去查找没有任何区别了

假如r开头的用户在第一个块上存在，第五个数据块中也有，但问题是管理系统通过映射表只知道某行到某行存在，并不知道指定的实际标准在某个数据块上，那么只能载入所有数据

首先我们的文件属性有很多，比如id 用户名家目录 默认shell 等等。但是我们的初衷标准是要查找用户名，那么意味着我们的查找标准是只查找其用户名，跟其他几个属性没有任何的关系。

所以在查找的时候只找到每个用户的用户名这些数据，那么就可以找到符合的行了。

所以如果说我们把所有数据上的每一行的用户名都抽取出来，那么这样再组合成一个文件或数据块，那么这个文件就变得非常的小，很显然我们文件中只有7个字段，那么我们只用了6个块，那么在平均的情况下大致使用七分之六块就能存储出来所有用户名。

1.映射

这样以后再去查找以r开头的用户，先去找索引，查看其用户的对应位置，每个用户名与其用户的对应关系都在一个块上，找到所有符合用户的用户名，再去根据这个映射关系找到对应的数据块将其载入进来就可以了

整个过程需消耗的代价：

首先载入搜索块，找到用户名，加入root 以及用户 root1 ；

发现root在第一个块上，而root1在第五个块上，于是我们找到root用户的默认的shell以及root1的默认shell，还必须根据这个默认映射把这两个块(第一个块和第五个块)都载入进来，所以它的额外代价还得再载入两个块，但是这两个块读到内存中对我们并不是都有用，那么还需要根据这两个块中的实际数据去找到真正用户所在的行，并将行的信息读出来找到对应的字段shell

索引分级

索引必须是搜索标准，如果用户的行非常的多，索引本身哪怕只保留一个字段也非常的大，假如说我们有7W个数据块，那么平均（并不准确）就要保存1/7 也就是1w个块，将其载入内存消耗IO也会非常的大，于是有了索引分级：

一级索引不够的话则可以扩展为二级、三级 等 索引，都是可以实现的

当我们设置索引到多个的级别之后，那我们实现的查找标准则会变得非常非常的小，那么这时一个索引内容只有几十兆而已，对一个非常大的表来讲，哪怕是四级索引也是非常常见的。

那么设置完索引后，每一次查找将索引从磁盘载入到内存中，也会消耗相当大的IO，也会很慢，所以数据库一启动，则直接将索引载入到内存中去，而且索引的大小必须小到能放到内存中才可以，由此在内存中存储的索引不只一个，由此在内存中存放的索引也不只一个

阻塞

对于mysql这种关系型数据库而言，如果有大量用户同时发起查询请求的时候，一个用户的请求就有可能大费周章，需要将索引载入内存并在内存中做匹配查找，再找到其对应的数据 再去找对应的数据块，还要从磁盘载入数据块到内存 最终将数据抽取出来以响应报文回给用户，这个过程就需要消耗大量的时间

如果同时还有第二个用户发起了查询请求，甚至与第一个用户使用了同一个资源的话，比如 第一个用户请求修改第一张表，第二个用户请求查询这张表，因此只能等到修改完成操作之后，当数据达到一致性状态之后，才可以返回给用户，在此期间后面的查询操作则被阻塞,这时对用户的体验会非常差的。这时，并发机制派上用场了

并发性

锁的概念：

读锁：共享锁

写锁：独占锁

如果读操作比较多，写操作相对少，那么这种并发也不会成为问题；

但读写请求几乎接近，这时如果还以表为例度进行加锁，并发性会非常的差；

早期数据库锁是以表为单位，意味着其中某个用户的请求正在请求某张表会对整张表加锁，如果是读则加读锁，其他用户可以继续读取内容；如果是写锁其他用户则无法读写，如果核心数据都在同一表中，那么任何用户的操作都有可能阻塞其他用户，由此读请求可以更快得到满足

减小锁粒度

表锁；

页锁；

假如第一个用户正在读取用户root和root1其在第一块和第五个块上，而第二个用户正在修改第二个用户的信息bin 要将其shell改为/sbin/nologin ，而我们通过数据组织发现 root在第一个块上root1在第五个块上 bin也在第一个块上,所以当第一个用户的操作锁定第一块和第五块的时候，第二个用户的请求则不能进行，那我们发现他们的操作不在同一行，那么如果把粒度降到更低那么就有机会同时进行操作了，那么有了行级锁：

行锁：第一个用户的请求只锁定其两行，而第二个用户则加锁第二行

死锁：

假如第一个用户要锁定第一行和第十行，而第二个用户要锁定第十行和第一行 这样一来将第一行锁死等待用户处理完成，但是其用户又不能修改第十行，一直等待

一般情况只发生锁超时，就是一个进程需要访问数据库表或者字段的时候，另外一个程序正在执行带锁的访问（比如修改数据），那么这个进程就会等待，当等了很久锁还没有解除的话就会锁超时，报告一个系统错误，拒绝执行相应的SQL操作。

数据库响应机制

通常mysql是以一个线程响应给一个用户的请求，因为一个用户所查询的数据，有可能是敏感数据是不允许其他用户访问的，所以我们在同一个线程内部如果响应多个用户的请求，这些处理机制都必须依靠着线程的内在逻辑来完成，相对来说是非常复杂的

mysql与web所不同的是在于：

mysql协议是有状态的，而http是无状态的 所以web不会始终在线，而mysql请求建立之后会始终在线，直到请求断开 所以线程会长时间在线，只等到用户请求退出，由此一个mysql服务器就算具备了同样硬件资源，那么它的并发性比web小的多，因为内置处理逻辑非常复杂

加速Mysql - 复用技术：

用户退出之后进程不会被销毁，则将其放入线程池(thread pool)

线程池的作用：

1.限制连接请求个数

2.完成线程复用，为用户的连接快速建立提供了保障

事物：ACID （一组DML语句）

事物的特性分为以下几种：

·原子性： 一组DML语句或都执行，或都不执行，同进同出

·一致性： 从一状态转换为另一状态后，数据保持不变

·隔离性：多个事物彼此之间如操作同一数据的时候不进行干扰

隔离级别：有四个隔离级别 隔离级别越高也就意味着隔离效果越高，并发性反而越低，彼此之间的影响度越小，所以又是一个折中

1.读未提交：

2.读提交

3.可重读 (MYSQL默认级别)

4.可串行化

只有在事物引擎上，隔离才有意义

持久性

数据一旦提交后 不得丢失，数据如果未提交，那必须能够撤销事物

事物日志：将随机IO转换至顺序IO

为了保障持久性，事物一旦提交立即写入磁盘

随机IO：由于DML语句所操作的行，对其进行修改操作，改了某几个行，而这些行恰好都不在同一数据块上，数据库只好依次找到对应块对其进行修改，这叫做随机IO 所以写入速度非常慢，不但保证持久度降低还有可能导致系统性能降低

顺序IO：将所有的操作本身直接记录到日志中，而这些操作能够读取出来再多次利用（幂等性）

Mysql日志：

mysql的日志类型分为以下（这里只讲事物日志，其他放在后期来说）

·错误日志

·查询日志

·慢查询日志

·事物日志

事物日志越大，并发性就越好，但是当在启动数据库则需要将提交上来的事物逐步同步到数据文件中去，将未提交的数据进行撤销，通常用户是不能手工参与的，所以事物日志也不应该过大，并且一定要避免磁盘的损坏而导致数据的丢失，可以做raid本地做镜像或手动做备份，当然前者方式更好。因为业务不会终止，日志需要大量的写还要不停的同步到数据库中去，所以不建议将事务日志与数据文件放入到一个分区上（单块硬盘存储的情况下）因为写日志需要IO同步数据也需要IO性能可想而知

·二进制日志

·中继日志

接口类型

SQL主要是使用API接口

    sql client ：能够基于某种协议跟server端进行通信，而这种客户端则不是通用的

mysql提供的连接接口：

linux系统：

·远程通信

·本地间通信 （如果是linux通常使用mysql.sock来通信 但是要求必须都在同一台主机）

windows系统：

·PIPE

·MEMORY

关系型数据库的数据如何在磁盘上组织的：

数据的组织：

数据类型：变长、定长

比如varchar(20),当我们定义一个数据字段varchar字段的时候，如果存放了4个左右的字符，比如root，需要占用5个字节，所以存储数据不同的时候，所占据的空间数是不一样的，如果使用cahr（20）来存储root字符串，那么则占用20个字节，就算后面的空闲空间没有使用，依然是20个字节。所以在数据设计的时候考虑数据增长的长度是至关重要的,所以一定要使用折中的方法，除非最长和最短相差太多，才考虑使用varchar，除此建议一律使用char

记录的类型

定长记录：行数是固定的

#在分配的每个数据块内部所存储数据条目的个数（行）是有限的，而且是固定的

变长记录：行数是变化的

所以每个记录到底在当前块内占真正存放多少个记录，以及每个记录的地址从哪个地方开始到哪个位置结束，每个记录都需要记录否则很难追中一个条，同事还需要描述当前块内有多大的空闲空间可用，于是存放行记录，空闲的行为空闲位置

记录

文件中对表而言，所存储的每一行数据都有一个唯一id号，以方便实现能让当前数据库存储引擎等最终这一行被称为RID ，其做为表本身存储的额外开销而存在，这样就可以追踪表了

一般来讲要想实现在文件存储记录的话，要完成的操作包括 插入 删除 修改 扫描整个表获取符合条件的记录等

因此扫描操作允许数据库系统能够访问一个记录的文件中的每一行进行移动，类似于指针 从第一行指到最后一行

如何获取文件的时候需要按次序进行：

存储的时候没有按次序，用户名或其他是随机存放的，我们需要统计浏览网站次数进行排序，那就意味着要把每个用户的所有信息都抽取出而后再通过某一机制进行排序，因此存储在磁盘上的时候，如果本身有次序，检索出来的时候就不需要进行排序，如果没有次序，则必须得重新排序

堆文件：无序记录的文件被称为堆文件

有序文件：通常需要结合聚簇索引来完成排序

如果索引是有序的，那么记录也就是有序的；

如果数据存储跟其索引存储相同时，而索引本身是有序的记录，我们被称为有序记录

聚簇索引：将数据直接存储在所在数据文件块上的索引上

所以，无论是检索数据或插入数据，都对其性能有影响的，尤其是检索数据

文件组织

顺序文件组织：

·根据索引来快速完成文件检索，而特意按照某种格式来完成的文件

·如果顺序文件组织存储本身是按照顺序文件来存的话，这种方式也就叫做聚簇索引

散列文件组织：

·hash索引

索引类型

·主索引：通常数据文件记录文件的时候可以按照索引次序存储的，而文件存储如果是有序的，只能跟主索引同一次序

·辅助索引：全都是无续的

·聚簇索引：索引是按照ID来存放的，索引本身没有指针指向数据，而是直接将数据直接存放在同一个文件，而且是按照顺序存放

·索引的数据结构，索引的类型：

·树状索引

·散列索引（散列索引只适合做比较）

数据库存储的文件

·数据文件

·索引文件

#对于聚簇索引来说，以上是存放在同一文件中

·日志文件

MySQL常用存储引擎：

负责将物理模型二进制代码，转换成更高一层的逻辑模型 数据块，并且理解上层次其他组件对其的请求，而只载入某个数据块并非全部的数据都是由存储引擎来完成的。

简单来讲常用两种存储引擎的名称和格式有：

·MyISAM

myisam引擎每个表都有三个文件：

数据文件：表名.MYD

索引文件: 表名.MYI

表定义:表名.frm

格式：

mydb:

    test.frm

    test.MYD

    test.MYI

想复制数据库的话，直接将目录复制并迁移即可

·InnoDB

#相对来说支持事物的，支持聚簇索引，因此数据和索引不是分开存放的

表空间：多张表可放置于同一表空间，表空间表现为一个个空间，表空间能为多个数据库存放数据

mysql支持使用独立的表空间文件，建议必须这么做

表定义文件：每张表的表定义文件在其数据库目录中，如下所示：

/mydata/data

    ibdata1

    mysql

    test

    infomation_schema

如果自己建立数据库名为mydb,在其下创建了表test：

    mydb:

         test

那么这里如果是innodb存储引擎那么只能看到一个文件 test.frm ，那么数据和索引都存放在ibadata1

这就是所谓的 "表空间"

在存储引擎载入的数据要放入到内存里面，我们要管理这些缓存到内存中的数据，这时我们需要用到缓冲区管理器

缓冲区管理器

其知道如何将那些数据放到哪些块上，如何能够高效载入内存，并且将来存储的时候如何将内存同步到磁盘上去，主要作用就是加速数据的存取，能够完成快速的数据查找

缓存置换：

·缓存转换策略：

·LRU：最少使用算法：

 #数据库会根据这种策略来查询所有缓存对象哪些访问的量最少，于是将其写回到硬盘当中，而后，后续的缓存则继续加入到缓冲区中去。

·MRU：最近最长使用算法

 #比LRU算法更为优秀

·被钉住的块(pinned block)：这样的块则不会被写回磁盘

块的强制写出：按某时间固定写回至磁盘中去，为了数据安全可靠性而设计的策略

缓存区只有innodb支持，所以innodb引擎调优就是这些参数

#查看当前有效变量 global为全局

mysql>SHOW {global|session} VARIABLES;

#varibales可以动态调整的

#但是数据库只能改配置文件并重启数据库才可以

    >SHOW {global|session} STATUS;

mysql的数据字典

mysql：

·用来保存数据库的元数据 （也叫做系统目录）

·一般来讲保存的数据有：

·关系的名字

·每个表的属性的名字

·属性的定义

·数据类型和长度

·每个表的关系上的视图名称以及视图定义

·约束

授权用户的名称：

用户的授权和账户信息

infomation_schema:

统计数据：

每个关系中属性的个数

每个关系中行的个数

每个关系的存储方法

（比如每个行有多少表，每个表有多少行等）

performace_schema:

显示性能相关信息 和统计数据信息