目标:
-
掌握Mysql的各组件及各组件的功能
-
理解Mysql简版执行流程和详细执行流程
-
掌握MyIsam和InnoDB的区别并说明使用场景
-
掌握Mysql日志文件及主要日志文件的作用
-
理解Mysql的数据文件及作用
-
使用命令查看mysql日志 配置my.cnf开启二进制日志、通用查询日志、慢查询日志等
-
掌握索引、分类、优劣势
-
使用命令创建、查看、删除索引
-
理解索引的原理和存储结构
一、MySQL架构篇
逻辑架构
MySQL架构总共三层
首先,最上层的服务并不是MySQL独有的,大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如:连接处理、授权认证、安全等。
第二层的架构包括大多数的MySQL的核心服务。包括:查询解析、分析、优化、缓存以及所有的内置函数(例如:日期、时间、数学和加密函数)。同时,所有的跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数,用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL(InnoDB会解析外键定义,因为其本身没有实现该功能),不同存储引擎之间也不会相互通信,而只是简单的响应上层的服务器请求。
![](https://i-blog.csdnimg.cn/blog_migrate/703c37cd015e1454467ff73ffc57e822.png)
详细的MySQL系统架构图
MySQL模块包括:
-
连接器(Connectors)
-
系统管理和控制工具(Management Serveices & Utilities)
-
连接池(Connection Pool)
-
SQL接口(SQL Interface)
-
解析器(Parser)
-
查询优化器(Optimizer)
-
查询缓存(Cache和Buffer)
-
存储引擎(Pluggable Storage Engines)
首先,我们对各个模块做一简单介绍
1、Connectors
指的是不同语言中与SQL的交互。
2、Connection Pool
管理缓冲用户连接,线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求,接收连接请求,转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配(或创建)一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信,接受客户端的命令请求,传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建,线程的 cache 等。
3、 Management Serveices & Utilities
系统管理和控制工具。
4、 SQL Interface
接受用户的SQL命令,并且返回用户需要查询的结果。
5、 Parser
SQL命令传递到解析器的时候会被解析器验证和解析。对一条SQL语句(
select * from t1
)进行
词法分析 根据分词 ----- 》形成语法树。 语法分析: 符合SQL的语法 SQL的语法 : SQL 92标准。 limit是 MYSQL自己的语法。
6、 Optimizer
查询优化器:SQL语句在查询之前会使用查询优化器对查询进行优化。
mysql 觉得你写的SQL 不是完美的需要进行优化。 那么会优化什么呢? MySQL会对SQL语句进行索引使用优化, 只使用一个 使用最优索引。
我们可以通过explain查看MySQL执行索引优化结果。一般在进行多表关联查询时,尽量以小表驱动大表。Where条件在执行时,MySQL会根据
where条件从左到右
自动找过滤力度最大的条件先执行,例如 where id=1 and sex='男' 和 where sex='男' and id=1 两个条件过滤语句MySQL执行顺序是一致的。
7 、Cache和Buffer
查询缓存:主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中,与该 query 的一个 hash 值 做一个对应。该 query 所取数据的基表发生任何数据的变化之后, MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中, Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
MySQL8.0后不再使用
Cache和Buffer
8 、存储引擎接口
MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。
注意:存储引擎是基于表的,而不是数据库。
以表为单位 creat table xxx() engine=InnoDB/Memory/MyISAM MySQL的存储引擎是针对表进行指定的。(engine=InnoDB\myisam)
存储引擎
|
说明
|
MyISAM
|
高速引擎,拥有较高的插入,查询速度,但不支持事务、不支持行 锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
|
InnoDB
|
5.5版本后MySQL的默认数据库,支持事务和行级锁定,事务处 理、回滚、崩溃修复能力和多版本并发控制的事务安全,比 MyISAM处理速度稍慢、支持外键(FOREIGN KEY)
|
ISAM
|
MyISAM的前身,MySQL5.0以后不再默认安装
|
MRG_MyISAM(MERGE)
|
将多个表联合成一个表使用,在超大规模数据存储时很有用
|
Memory
|
内存存储引擎,拥有极高的插入,更新和查询效率。但是会占用和 数据量成正比的内存空间。只在内存上保存数据,意味着数据可能 会丢失
|
Falcon
|
一种新的存储引擎,支持事物处理,传言可能是InnoDB的替代者
|
Archive
|
将数据压缩后进行存储,非常适合存储大量的独立的,作为历史记 录的数据,但是只能进行插入和查询操作
|
CSV
|
CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据 交换)
|
xtraDB存储引擎是由Percona公司提供的存储引擎,该公司还出品了Percona Server这个产品,它是基 于MySQL开源代码进行修改之后的产品。 阿里对于Percona Server服务器进行修改,衍生了自己的数据库(alisql)。
InnoDB和MyISAM存储引擎区别:
|
Innodb
|
Myisam
|
存储文件
|
.frm 表定义文件
.ibd 数据文件和索引文件
|
.frm 表定义文件
.myd 数据文件
.myi 索引文件
|
锁
|
表锁、行锁
|
表锁
|
事务
|
支持
|
不支持
|
CRDU
|
读、写
|
读多
|
count
|
扫表
|
专门存储的地方 (加where也扫表)
|
索引结构
|
B+ Tree
|
B+ Tree
|
外键
|
支持
|
不支持
|
存储引擎的选型:
InnoDB:支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比 较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。如果需要频繁的 更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚 (rollback)。
MyISAM:插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择 MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低,也可以使用。
MEMORY:所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度, 对数据的安全性要求较低,不需要持久保存,可以选择MEMOEY。它对表的大小有要求,不能建立太大 的表。所以,这类数据库只使用在相对较小的数据库表。
注意,同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择 InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询 的临时表,可以选择MEMORY存储引擎。
详细执行流程图
![](https://i-blog.csdnimg.cn/blog_migrate/d82c49f84a43d442aa107f0b52303cf1.png)
物理结构
-
MySQL是通过文件系统对数据和索引进行存储的。
-
MySQL从物理结构上可以分为日志文件和数据索引文件。
-
MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。
-
日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。
日志文件
错误日志(errorlog):
默认是开启的,而且从5.5.7以后无法关闭错误日志,错误日志记录了运行过程中遇到的所有严重的错误 信息,以及 MySQL每次启动和关闭的详细信息。
二进制日志(bin log):记录数据变化 binlog记录了数据库所有的ddl语句和dml语句,但不包括select语句内容,语句以事件的形式保存,描 述了数据的变更顺序,binlog还包括了每个更新语句的执行时间信息。如果是DDL语句,则直接记录到 binlog日志,而DML语句,必须通过事务提交才能记录到binlog日志中。 生产中开启binlog,进行数据备份、恢复、主从同步。
通用查询日志(general query log):
啥都记录 耗性能 生产中不开启
慢查询日志(slow query log):
SQL调优 定位慢查询select语句。
默认是关闭的。
需要通过以下设置进行开启:
--开启慢查询日志
slow_query_log=ON
--慢查询的阈值
long_query_time=3
--日志记录文件如果没有给出file_name值, 默认为主机名,后缀为-slow.log。如果给出了文件名,但不 是绝对路径名,文件则写入数据目录。
slow_query_log_file=file_name
记录执行时间超过long_query_time秒的所有查询,便于收集查询时间比较长的SQL语句。
重做日志(redo log)
回滚日志(undo log)
中继日志(relay log)
查看日志开启情况:
show variables like 'log_%';
数据文件
SHOW VARIABLES LIKE '%datadir%';
InnoDB数据文件
.frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd文件:使用独享表空间存储表数据和索引信息,一张表对应一个ibd文件。
.
ibdata文件:使用共享表空间存储表数据和索引信息,所有表共同使用一个或者多个ibdata文 件。
MyIsam数据文件
.frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息
.myd文件:主要用来存储表数据信息。
.myi文件:主要用来存储表数据文件中任何索引的数据树。