MySQL

6.MySQL架构详解

2020-05-24  本文已影响0人  Stone_説

目录：
1.MySQL架构详解
2.MyISAM和InnoDB的比较
3.SQL查询语句执行路径
4.优化查询缓存

1.MySQL架构详解

MySQL架构图.jpg

MySQL的架构图如上图所示，从用户发起SQL请求，至相关操作的完成，大概可以将其分为9个部分。

1.1 Connection Pool（线程池）

包括但不限于以下功能：

Authentication：认证
Thread Reuse：线程重用
Connection Limits：连接线程数量限制
Check Memory：检查内存，是指与连接线程相关的内存
Caches：必要时的线程缓存

MySQL是一个单进程多线程的应用，当用户通过Connetor向MySQL Server发起请求连接时，通过线程池（Connection Pool）建立一个用户连接，此连接会话将会一直存在，用户可以通过此会话，发对应的SQL语句到服务端，服务器收到SQL语句后，将对语句完成执行。

1.2 SQL Interface

SQL Interface是MySQL的外壳，无论用户是通过哪一种连接器发过来的基本的SQL请求（DML和DDL两种语句）。SQL 接口是一个完完整整的SQL命令解释器，还要提供完备的SQL接口应该具备的功能。
例如：支持所谓的过程式编程；支持代码块实现例如存储过程，存储函数，触发器必要时还要部署铺设实现所谓的一个关系型数据库应该具备的基本组件例如视图等...
SQL Interface做完词法分析，句法分析之后，需要分析语句如何执行，需要Parser实现。

1.3 Parser

Parser是专门的分析器，并不分析语法错误，做整个语句的执行分析，查询翻译，把一个查询语句转化为对应的语句，可在本地执行的特定操作，分析用户请求的对象，数据库，表字段...用户认证的通过并不代表它可以访问数据库上的所有数据。
MySQL的认证大概分为两个阶段：

1.连入时需要验证账号，密码（Authentication）
2.验证成功后，还需要分析用户是否有权限获取他期望的数据（Objective Privilege的检测过程）
3.生成多个执行树，多种路径之中肯定会有一个最优的
1.4 Optimizer

Optimizer就是分析衡量哪一种方法的开销，代价最小，开销的计算要依赖于索引的各种内部组件来进行评估，得到的是一个近似值，还要考虑到当前MySQL内部实现资源访问时的统计数据。此外，优化还包括一种功能，一旦选择完一条路径之后，这条路径实现的开销，一旦Optimizer发现有更优的实现目的方式，将会改写用户的SQL语句，优化完之后将会交与存储引擎执行：Pluggable Storage Engine

1.5 Pluggable Storage Engine

MyISAM,InnoDB...
存储引擎负责把具体分析非结果，完后对磁盘上文件路径访问的转换，因此，如果要查询某数据，索引完成后要访问某一行，行数据都存储在磁盘块上，因此存储引擎要把它映射为磁盘块，并把磁盘块加载到内存中。
注意：计算机实现或者进程实现数据处理时，是不可能直接访问磁盘上数据的，因为它没有权限，只有让内核帮其把所访问的数据加载到内存中以后，进程在内存中完成修改，由内核在帮忙存回磁盘。
此外，对于文件系统而言，数据的存储都是以磁盘块（block存储的），尽管如此，MySQL在实现数据组织时，并不依赖与磁盘，而是把磁盘块再一次组织成更大一级的逻辑单位（例如：LVM，PE，LE），这个逻辑单位称为MySQL的数据块（Data Block），最终对于关系型数据库，它的数据是按行存储的，一般而言一行数据都是存储在一起的，因此MySQL在内部有一个数据块叫做Data Block，整个存取过程是以Data Block以最小单位进行的。需要将整个块加载至内存中，MySQL再从其中挑出某一行，返回给查询者。
所以，事实上访问比较热点的数据，不可能临时从磁盘加载，为了加速性能，MySQL的部分存储引擎，可以实现把这些频繁访问的热点数据统统装入内存，用户访问直接在内存中修改，访问，周期性的写入磁盘，比如：InnoDB引擎，而组件Cache && Buffers就是实现这样一个功能。
Cache && Buffers
MySQL为了加速整个执行，因为数据库的不断被访问，IO是计算机中最慢的一环，尤其磁盘IO。通常被载入到内存中进行管理。

1.6 Management Services & Utilities
Backup & Recovery：备份恢复工具
Security：安全工具
Replication：复制工具
Cluster：集群服务
Administration Configuration：管理配置工具
Migration：迁移工具
Metadata：负责管理MySQL元数据的工具

NOTE：数据库系统的三种视图

1.物理视图（数据文件）：对应的文件系统上存储为一个又一个文件，MySQL的文件有：
Redo，Undo，Data，Index，Binary，Error，Query and slow
2.逻辑视图：在SQL接口上通过存储引擎把底下的Data，Index映射为一个一个关系型数据库应该
具备的组成部分，比如：表.一张表在底层看见的是一个数据文件（Data Block组成），
最终映射为磁盘上的文件系统Block，而后再次映射为本地扇区的存储。但是，整个MySQL需要
把他们转换映射成一个二维关系表的形式，这整个需要依赖于SQL Interface以及存储引擎
共同实现，所以，这个层次所提供给用户的就是逻辑视图。这个层次提供给用户的就是逻辑视图：
把底层数据文件映射成为了关系型数据库的组件，（逻辑视图就在SQL Interface和
Storage Engines上）.
3.用户视图：每一类用户只有一部分数据的访问权限，它也只能看到一部分数据的访问权限。

2. MySAM和InnoDB的比较

存储引擎的比较.png
表格链接：https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html
2.1 MyISAM

特点：

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键索引
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5前默认的数据库引擎

适用场景：

只读（或者写较少）、表较少（可以接受长时间进行修复操作）

MyISAM引擎文件：

MyISAM存盘有三个文件：
    tb1_name.frm    存放表的定义  file user*查看
    tb1_name.MYD    数据文件
    tb1_name.MYI    索引文件
2.2 InnoDB

特点：

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5开始为默认的数据库引擎

InnoDB数据库文件：

tb1_name.frm    表格式定义，存放每个表自己的定义
tb1_name.ibd    数据文件（存储数据和索引）
ibdata1         位于上一级目录，所有的表都集中在此处，推荐在数据库中添加一个优化选项

优化选项：

#vim /etc/my.cnf
[mysqld]
innodb_file_per_table   使用此选项，每个表都有ibd文件，每个表都有独立的文件更好管理
#rm -rf /var/lib/mysql/*
#systemctl restart mariadb
>show create table students;查看创建表时使用的引擎
>show variables like '%stroage_engine%';  可查询默认存储引擎
修改默认的存储引擎：
#vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
#service mysqld restart
>show engines   变化了
>show table status from msyql\G;  查看库中表都使用什么存储引擎
2.3 其他存储引擎
1. Performance_Schema： Performance_Schema数据库使用
2. Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。
适用存放临时数据。引擎以前被称为HEAP引擎
3. MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为
一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
4. Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持
行级锁和专用缓存区
5. Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器
的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的
能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
6. BDB：可替代InnoDB的事务引擎，支持COMMIT、 ROLLBACK和其他事务特性
7. Cluster/NDB： MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类
查找需求还要求具有最高的正常工作时间和可用性
8. CSV： CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式
导入和导出其他软件和应用程序之间的数据交换
9. BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式
数据库设计，数据自动复制，但不是本地存储
10. example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或
从中检索。目的是作为例子来说明如何开始编写新的存储引擎
2.4 管理存储引擎

2.4.1 查看mysql支持的存储引擎

#mysql
>show engines

2.4.2 查看当前默认的存储引擎

>show variables like '%storage_engines%';

2.4.3 设置默认的存储引擎

#vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

2.4.4 查看库中所有表中使用的存储引擎

>show table status from db_name;

2.4.5 查看库中指定表的存储引擎

>show table status like 'tb_name';
>show create table tb_name;

2.4.6 设置表的存储引擎

>CREATE TABLE tb_name(...) ENGINE=InnoDB;
>ALTER TABLE tb_name ENGINE=InnoDB;

3. SQL查询语句执行路径

查询语句的执行路径.jpg

4. 优化查询缓存

优化查询缓存.jpg
4.1 查询缓存（Query Cache）原理

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的的SQL语句，是否完全一样，区分大小写

4.2 优缺点

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提供查询性能。查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率。查询缓存的使用，会增加检查和清理Query Cache中记录集的开销。

4.3 哪些查询可能不会被缓存
  1. 查询语句中加了SQL_NO_CACHE参数
  2. 查询语句中含有获得值的函数，包含自定义函数，如： NOW() CURDATE()、 GET_LOCK()、 RAND()、 CONVERT_TZ()等
  3. 对系统数据库的查询： mysql、 information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
  4. 查询语句中使用了LOCK IN SHARE MODE、 FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
  5. 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
  6. 事务隔离级别为Serializable时，所有查询语句都不能缓存
4.4 查询缓存相关的服务器变量
1. query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，
但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
2. query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的
语句，建议使用SQL_NO_CACHE
3. query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，
低于此值有警报
4. query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回
结果，默认值为OFF，表示可以在表被其它会话锁定的场景
中继续从缓存返回数据； ON则表示不允许
5. query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
4.5 SELECT语句的缓存控制
SQL_CACHE：显示指定存储查询结果于缓存之中
SQL_NO_CACHE：显示查询结果不予缓存
4.6 query_cache_type参数变量
1. query_cache_type的值为OFF或0时，查询缓存功能关闭
2. query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，
否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
3. query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的
SELECT语句才会缓存；其它均不予缓存
4.7 query_cache_type参数变量
查询缓存相关的状态变量: SHOW GLOBAL STATUS LIKE 'Qcache%';
1. Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
2. Qcache_total_blocks： Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片,
    执行FLUSH QUERY CACHE清理碎片
3. Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
4. Qcache_hits： Query Cache 命中次数
5. Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没
有命中的次数
6. Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
7. Qcache_not_cached：没有被Cache的SQL数，包括无法被Cache的SQL以及由于query_cache_type设置的不会被Cache的SQL语句
8. Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量
4.8 命中率和内存使用率估算
1. 查询缓存中内存块的最小分配单位query_cache_min_res_unit:
    (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
2. 查询缓存命中率:Qcache_hits/(Qcache_hits + Qcache_inserts)*100%
3. 查询缓存内存使用率： (query_cache_size – qcache_free_memory) /
query_cache_size * 100%
上一篇下一篇

猜你喜欢

热点阅读