MySQL 体系结构和存储引擎

  1. MySQL 是一个可移植的数据库, 在Linux,Solaris, FreeBSD,Mac 和 Windows上运行
  2. MySQL 能保证在各个平台上的物理体系结构是一致的.

MySQL 物理体系结构
MySQL 技术内幕--MySQL 体系结构和存储引擎 - 图1

定义数据库和实例

数据库：物理操作系统文件或者其他形式文件类型的集合.

  1. 文件的集合(一般来说是二进制文件),是依照某种数据模型组织起来并且存放在二级存储器中的数据集合
  2. 在MySQL 数据库中,数据库文件可以是 frm,MYD,ibd 结尾的文件
  3. 在NDB 引擎中,数据库的文件可能不是存放在操作系统中,而是存放在内存中的文件.

数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；
数据库实例：数据库实例才是真正用于操作数据库文件的程序集.

  1. Mysql被设计成一个单进程多线程架构的数据库，所以在操作系统上表现就是一个进程。

MySQL 技术内幕--MySQL 体系结构和存储引擎 - 图2

  1. 数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库中的数据做任何的操作，包括数据定义、数据查询、数据维护、数据库运行控制等等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。
  2. 一般情况下一个实例操作一个或多个数据库（Oracle一个实例对应一个数据库）；集群情况下多个实例操作一个或多个数据库。

    数据库实例读取配置文件:

    1. 启动实例时, MySQL 数据库会读取配置文件,根据配置文件的参数启动数据库实例.
    2. 读取顺序：/etc/my.cnf > /etc/mysql/my.cnf > /usr/etc/my.cnf > ~/.my.cnf
    3. 如果没有参数文件,MySQL 会按照编译时的默认参数设置启动实例,Oracle 数据库会启动失败

DataBase/instane/session

  1. 访问以及操作Database需要instance
  2. 与MySQL 连接成功建立一个session,session是和instance建立,每个session可以使用不同的用户身份
  3. 一个instance可以操纵多个database, 一个session和一个instance建立连接,既一个session可以操纵多个database.

计算机存储器

主存=内存 指计算机中的内存条；外存=辅存 如：硬盘、U盘、光盘及软盘等；
主存储器 /又称内存储器（简称内存）:

  1. 内存又称主存，是CPU能直接寻址的存储空间，它的特点是存取速率快。内存是电脑中主要部件，它是相对于外存来说。
  2. 内存一般采用半导体存储单元，包括随机存储器（RAM）、只读存储器（ROM）和高级缓存（Cache）。

    随机存储器（RAM）：（Random Access Memory）我们不仅可以从中读取数据，而且还可以写入数据。但是机器电源关闭时，它存入的数据就会丢失。内存条（SIMM）就是把RAM集成块集中在一小块电路板上。 只读存储器（ROM）：（Read Only Memory）它是把数据或程序永久保存在其中，不能更改，只能读取。即使机器断电，数据也不会丢失。 高级缓存（Cache）：它是介于CPU与内存之间，常用有一级缓存（L1）、二级缓存（L2）、三级缓存（L3）（一般存在于Intel系列）。它的读写速度比内存还快，当CPU在内存中读取或写入数据时，数据会被保存在高级缓冲存储器中，当下次访问该数据时，CPU直接读取高级缓冲存储器，而不是更慢的内存。

辅助存储器/又称外存储器/又称二级存储器:

  1. 计算机主存储器或内存之外的所有可访问数据存储器，是程序和数据长期保存的地方，常见的二级存储设备有（固定/移动）硬盘、光盘、等
  2. 是指计算机用来存储数据和程序的各种存储介质。它是指除CPU缓存和计算机内存以外的存储器。

MySQL 体系结构

MySQL 技术内幕--MySQL 体系结构和存储引擎 - 图3
MySQL数据库引区别于其他数据库的特点:
插件式的表存储引擎, MySQL插件式的存储引擎结构提供了一些列标准的管理和服务支持,这些标准与存储引擎本身无关,是数据库系统本身需要的.

插件式体系结构是一种非常灵活的组件式结构，它把程序的功能分散在插件中来完成。插件是可独立开发的程序模块，它能够动态地插入到系统中，并且可以被自由地删除和替换。因此它能够提高软件开发的并行性和开发效率，降低设计开发难度，缩短开发周期，增强应用程序的可运行性、可测试性和可维护性.

MySQL 逻辑系统架构分为3层

应用层: 是MySQL体系架构的最上层，它可其他client-server架构一样，(客户端—>数据库服务端).
1. 连接处理: 当一个客户端向服务端发送连接请求后，MySQL server会从线程池中分配一个线程来和客户端进行连接，以后该客户端的请求都会被分配到该线程上。MySQL Server为了提高性能，提供了线程池，减少了创建线程和释放线程所花费的时间。
2. 用户鉴权: 当客户端向MySQL服务端发起连接请求后，MySQL server会对发起连接的用户进行鉴权处理，MySQL鉴权依据是: 用户名，客户端主机地址和用户密码
3. 安全管理 当客户连接到MySQL server后，MySQL server会根据用户的权限来判断用户具体可执行哪些操作。MySQL 提供的部分权限的如下:

服务层: 提供了MySQL Server数据库系统的所有逻辑功能.

  1. MySQL Management Server & utilities(系统管理): 丰富的数据库管理功能
  • 数据库备份和恢复
  • 数据库安全管理，如用户及权限管理
  • 数据库复制管理
  • 数据库集群管理
  • 数据库分区，分库，分表管理
  • 数据库元数据管理
  1. SQL Interface(SQL 接口)
  • Data Manipulation Language (DML).
  • Data Definition Language (DDL).
  • 存储过程
  • 视图
  • 触发器
  1. SQL Parser(SQL 解析器)

作用主要是解析查询语句，最终生成语法树。首先解析器会对查询语句进行语法分析，如果语句语法有错误，则返回相应的错误信息。语法检查通过后，解析器会查询缓存，如果缓存中有对应的语句，就直接返回结果不进行接下来的优化执行操作。

  1. Optimizer (查询优化器)

优化器的作用主要是对查询语句进行优化，包括选择合适的索引，数据的读取方式。

  1. Caches & buffers(缓存)

包括全局和引擎特定的缓存，提高查询的效率。如果查询缓存中有命中的查询结果，则查询语句就可以从缓存中取数据，无须再通过解析和执行。这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等。

存储引擎层:
1. 存储引擎 存储引擎是MySQL中具体与文件打交道的子系统，也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎）。
2. 物理文件 物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等

MySQL 存储引擎

InnoDB 存储引擎

image.png

特点：

  1. InnoDB 存储引擎支持事务，设计目的主要是面向在线事务处理（OLTP）的应用

    oltp和olap是两种常见的业务模式：

    1. On line transaction processing 在线事务处理，比如常见的银行系统，特点：
    • 实时性要求高
    • 数据量小，并发量高
    • 要求绝对的事务完整性
    1. olap(on line analytical processing) 在线分析处理,这种业务一般是日志的分析和深度挖掘,常见如淘宝的交易记录,百度地图的人口迁徙记录 具有以下特点,olap的结果一般是为决策提供支持
    • 数据量通常非常大
    • 对事务要求不高 通常只有 添加和查询操作
    • 实时性要求不高通常只有汇总后的结果,而汇总分析过程通常可以执行很久

  2. 行锁设计、支持外键，以及类似orcal的非锁定读，即默认读取操作不会产生锁。

image.png

  • 行锁：一锁锁定一行或者多行记录，Mysql的行锁是基于索引加载的，所以行锁是要加在索引响应上，即命中索引。

行锁特征：锁冲突概率低，并发性高，会有死锁的概率出现。

  • 表锁：一锁锁定一张表，表格锁定其他事务不能对该表格进行操作，表锁响应的是非索引字段，即全表扫描

表锁特征：由于表锁每次都是锁一整张表，所以表锁的锁冲突几率特别高，表锁不会出现死锁的情况。

  1. Mysql可以将灭个InnoDB存储索引的表单独放到一个独立的ibd文件中
  2. InnoDB引擎支持用裸设备（row disk）建立表空间
  3. InnoDB 通过使用多版本并发控制（MVCC）获得高并发性，并且实现SQL标准的四种隔离级别，默认为REPEATABLE级别。
  4. 使用next-key locking的策略来避免幻读（phantom）
  5. InnoDB 存储引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读等高性能和高可用的功能

  6. 表中的数据，innoDB采用了聚集的方式， 因此每张表的存储都是按照主键的顺序进行存放，如果表格没有显示的在表定义的时候指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID作为主键。

    MyISAM 存储引擎

    特征：

  7. 不支持事务、表锁设计，支持全文索引，主要面对OLAP数据库应用。

  8. MyISAM 存储引擎的缓冲池只缓冲索引文件，不缓冲数据文件

    NDB 存储引擎

    特点： 高可用/高性能

  9. NDB 引擎是一个集群引擎，类似于Oracle的RAC集群，不过于RAC sharing everything架构不同的是，结构是sharing nothing的集群架构.

  10. NDB的数据全部存放在内存中，因此主键查找的速度非常快，并且可以通过添加NDB数据存储节点可以线性提高数据库性能

  11. NDB存储引擎的连接操作（JOIN）是在MySQL 数据层完成的，不是自存储引擎层完成，所以执行连接操作时网络开销巨大，查询速度很慢.

    Memory 存储引擎

    特點：

  12. Memory存儲引擎(之前称HEAP存儲引擎)，表中数据存放在内存中

（缺点： 数据库发生重启或者崩溃，表中的数据会消失）

  1. 适用于存储临时数据的数据表，以及数据仓库中的维度表
    1. 事实表： 事实表（Fact Table）是指存储有事实记录的表，如系统日志、销售记录等；事实表的记录在不断地动态增长，所以它的体积通常远大于其他表。
    • 事实表作为数据仓库建模的核心，需要根据业务过程来设计，包含了引用的维度和业务过程有关的度量。
    1. 维度表（Dimension Table）或维表： 有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联；相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）、地点表（包含国家、省／州、城市等属性）等。

Archive 存储引擎

特点：

  1. 只支持INSERT和SELECT操作，运用zlib算法将数据行进行压缩存储，压缩比可高达1：10，适合存储归档数据（eg：日志信息）。
  2. Archive存储使用行锁实现高并发的插入操作，本身并不是事务安全的存储引擎，设计目标是提供高速的插入和压缩功能。

Federated 存储引擎

特点：

  1. 不存储数据，只是指向远程MySQL数据库服务器上的表。（类似于SQL Server的链接服务和Oracle的透明网关）

  2. 只支持MySQL数据库表，不支持异构数据表。

    Maria 存储引擎

    特点：

  3. 新开发的引擎，设计目标是用来取代原有的MyISAM存储引擎，成为MySQL默认存储引擎。

  4. 支持存储缓存数据和索引文件，应用了行锁设计，提供了MVCC功能
  5. 支持事务和非实物安全的选项，以及更好的BLOB字符类型的处理性能。

其他存储引擎

QA

  • MySQL 支持全文索引，MyISAM、InnoDB和Sphinx引擎存储都支持全文索引。
  • MySQL的MyISAM存储引擎不支持事务，但是InnoDB支持。
  • 对于ETL 操作，MyISAM有优势，在OLTP环境中，InnoDB存储引擎效率更好。
  • 数据量变大，MySQL性能会急剧下降：×
    • MySQL是数据库，不是文件，数据行增加会导致性能下降，但是下降不是线性的。
    • 用户选择正确的存储引擎以及正确的配置，再多的数据量MySQL都能承受

      各个存储引擎之间的比较

      包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。
      image.png
      查看当前使用的MySQL数据库支持的存储引擎

      mysql>SHOW ENGINES\G;

比较运行不同的存储引擎之后，表的大小：
MyISAM—>table size: 40.7MB; InnoDB—>113.6MB; Archive—>20.2MB;
image.png
示例数据库：
SQL SERVER —>AdventureWorks;
Oracle/MySQL —> 示例数据库；

连接MySQL

连接MySQL操作是一个连接进程和MySQL数据库实例进行通信，MySQL数据库提供的连接方式本质上是进程通信。
（常见的进程通讯：管道，命名管道，命名字，TCP/IP套接字，UNIX域套接字）

TCP/IP

TCP/IP套接字 ：

  1. MySQL数据库在任何平台下都提供的连接方式。
  2. 网络中使用最多的一种方式
  3. 在TCP/IP连接上建立一种基于网络的连接请求
  4. 一般情况下客户端（client）在一台服务器上，MySQL 实例（server）在另一台服务器上，两台机器通过一个TCP/IP网络连接。
  5. 用户在Windows服务器下请求一台远程Linux服务器下的MySQL实例。

host ip:192.168.0.101 user:david
eg： C：>mysql -h192.168.0.101 -u david -p

  • 在通过TCP/IP 连接到MySQL实例时，MySQL数据库会先检查一张权限视图，用来判断发起请求的客户端IP是否允许连接到MySQL实例。

eg：mysql> use mysql;
mysql>select host,user,password from user;

命名管道和共享内存

命名管道的连接方式：如果两个需要通信的进程在同一台服务器上，可以使用命名管道。

  • （SQL SERVER 数据库安装后的本地连接也是使用命名管道。）
  • 在MySQL数据库中必须在配置文件中启用 —enable-named-pipe

共享内存的连接方式：

  • 在配置文件中添加，—shared-memory。

  • 连接时，MySQL客户端必须使用 —protocal=memory

    UNIX 域套接字

  • 不是一个网络协议

  • 只能MySQL客户端和数据库实例在同一台服务器上才可以使用
  • 套接字连接：
    • 查找 套接字文件 的路径：
      • 配置文件中指定套接字文件的路径， —socket=/tmp/mysql.sock
      • 启动MySQL实例，命令查找套接字路径：
        • mysql>SHOW VARIABLES LIKE ‘socket’
    • 连接 套接字文件
      • mysql -udavid -S /temp/mysql.sock

[

](https://blog.csdn.net/wangyijie521/article/details/82146997)