MySQL第一讲

逻辑架构

逻辑架构图

连接器 Connectors

驱动 JDBC ODBC Python PHP .NET

管理和控制工具 Management Services & Utilities

备份、恢复、安全、集群，主要是DBA用的

连接池 Connection Pool

管理连接，不能无限制连

连接认证…

SQL接口 SQL Interface

分发各种SQL语句，DML DDL

解析器 Parser

词法解析和语法解析

词法解析 → 通过空格进行分割，形成语法树

语法分析 → 符合SQL语法 SQL92 MYSQL有自己的语法，比如limit

查询优化 Optimizer

索引 可建立多个索引，实际使用时只用一个，使用最优的，通过explain分析后得到最优

多表关联 小表驱动大表

WHERE条件 左→右 注：过滤力度最大的先执行，放在左边

Oracle 右→左，过滤力度大的放右边

查询缓存 Cache & Buffer

把查询结果缓存

Map

key：SQL语句的hash值

value：查询结果

注：1、where a=1 and b=2 跟 where b=2 and a=1 是不一样的

​ 2、只要做过新增、修改、删除，缓存会清掉

​ 3、MySQL8.0后不再用

存储引擎 Pluggable Storage Engines

存储引擎是针对表来指定的，5.5后的MySQL默认存储引擎是InnoDB

简单介绍：

存储引擎	说明
MyISAM	较高的插入、查询速度。不支持事务，不支持行锁。
InnoDB	支持事务、行级锁定、外键、崩溃修复、并发控制，比MyISAM处理速度稍慢。
Memory	内存存储引擎，极高的插入、更新、查询效率。不持久化，数据会丢失。不能建立太大的表。
Archive	压缩存储，适合存储大量的、独立的历史数据，只能插入和查询。
CSV	基于CSV格式文件存储数据，用作跨平台的数据交换。
Merge	将多表联合成一个表，适用于超大规模数据存储。
xtraDB	这是个第三方自定义的存储引擎，Percona公司出品，Percona Server为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具。

InnoDB和MyISAM区别

	InnoDB	MyISAM
存储文件	.frm 表定义文件 .ibd 数据文件和索引文件	.frm 表定义文件 .myd 数据文件 .myi 索引文件
锁	表锁、行锁	表锁
事务	支持	不支持
COUNT	扫表	不扫表（加where条件也扫表）
索引结构	B+ Tree	B+ Tree
外键	支持	不支持

执行流程

物理结构

• 通过文件系统存储数据和索引。
• 从物理结构上，分为日志文件和数据、索引文件。
• 以上两种文件都在/var/lib/mysql目录下。
• 日志文件采用顺序IO存储（记录速度快、只能追加，浪费空间）、数据文件采用随机IO存储（省空间、相对慢）。

日志文件

在 /etc/my.cnf 中进行配置。

通过命令 show variables like ‘log_%’ 查看日志开启情况。

错误日志 errorlog

默认开启，无法关闭

二进制日志 binlog

默认开启，主要记录增删改操作，描述了数据变更的顺序，DML语句，事务提交才会记录，DDL语句会直接记录。重启一次服务就会生成一个新的binlog日志。

涉及备份、恢复、主从。

通用查询日志 general query log

记录所有，一般不能开启，尤其生产环境

慢查询日志 slow query log

调优用的，定位执行慢的select，默认关闭

重做日志 redo log

ib_logfile0、ib_logfile1 涉及持久化

回滚日志 undo log

中继日志 relay log

涉及主从

数据文件

通过命令 show variables like ‘%datadir%’ 查看数据文件情况。

InnoDB数据文件

• .frm文件：主要存储表结构的定义信息。
• .ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件
• ibdata文件：使用共享表空间存储表数据和索引信息（系统空间数据），所有表共同使用一个或者多个ibdata文件。

MyISAM数据文件

• .frm文件：主要存储表结构的定义信息。
• .myd文件：主要存储表数据信息。
• .myi文件：主要存储索引信息。