七、MySQL体系架构

一、MySQL的分支、变种、替代

1、分支与变种

Drizzle （c++），高可用；

MariaDB --> MySQL的扩展集；

Percona Server --> 向后兼容MySQL，与MySQL重合度高；

        服务器信息（日志记录）改进；

        性能改进；

        操作灵活；

2、替代

Postgre SQL（PG）；

        //一专多长，稳定性强（安全、性能）；

SQLite；

        //占用空间小，不需经常维护；MP3等设备；

二、MySQL体系架构与整体

1、架构图示例

2、连接池

缓存已经建立的连接；  

3、管理工具和服务

系统自带，数据备份、恢复、MySQL复制、搭建MySQL集群；

4、SQL接口

接收用户SQL命令，返回SQL运行结果；

5、解析器

SQL命令传过来，验证语法和数据结构；

SQL验证完后，做成一个解析树，传到后面的流程；

6、缓存器

对应相同的SQL语句，将结果缓存起来；

包含许多小缓存器：表缓存器、记录缓存器、KEY缓存器、权限缓存器；

7、插件式引擎

8、文件与日志

9、连接层

与外部建立连接时，创建一个独立线程来维护这个连接；

连接断开时，不会kill线程，而是缓存线程，等待下次连接上继续使用；

不同的连接有各自的内存处理空间；

客户端连接进入连接层后会做验证：

        1、身份验证，确认用户名、主机、口令等；SSL方式连接；

        2、对登录用户做权限验证；

10、server层

SQL处理层；做解析、优化、执行、缓存、内置函数（存储过程，视图）；

SQL -> 命中缓存（直接返回）； --> 未命中缓存 --> 解析查询（解析树） --> 优化（选择需要的索引） --> 执行查询；

会询问执行引擎来确认优化方案，并不是一个单独的一层； 

默认关闭缓存，不建议开启缓存；

缓存缺点：

-- 1、要求严格，SQL完全一致（SQL语句，协议版本，字符集，连接的数据库）；

-- 2、8 版本后放弃缓存；

-- 3、会逐字节匹配，导致：select * from table != SELECT * FROM TABLE

-- 4、不确定的查询结果（两次SQL之间，任何涉及对表的修改，会导致缓存失效）；只读数据库不会失效；

-- 5、处理的问题没有带来的问题多；不如使用缓存数据库 redis；

三、MySQL存储引擎

1、MySQL支持可插拔的执行引擎，插件式；

2、提供了完整的标准的管理和服务；

3、连接层和server层的解析、优化与具体使用哪个引擎没有关联，这才能实现可插拔的执行引擎；

4、存储引擎不是针对数据库的，是针对单个表的；

1、MySQL官方引擎

innoDB

-- 1、最早是第三方引擎，最后被收购未官方引擎； -- 2、应用最广泛； -- 3、满足事务要求； -- 4、自动崩溃修复； -- 5、在非事务环境下性能好；

MyISAM

-- 1、MySQL5.1之前是默认引擎； -- 2、全文索引，支持压缩； -- 3、不支持事务； -- 4、仅支持表锁，不支持行锁，性能略低； -- 5、数据库崩溃，无法做安全恢复； -- 6、针对只读数据，使用表共享锁，读性能高； -- 7、针对地图搜索适合使用；

Archive

-- 1、仅支持insert 和 select； -- 2、把所有插入的数据做压缩，存储空间小，io次数少； -- 3、每次查询需要全表扫描； -- 4、适合做日志和数据采集； -- 5、不是事务类型引擎；

Blackhole -- 禁止

-- 1、没有存储机制，会丢弃所有插入的数据不做保存； -- 2、只会记录表的日志； -- 3、适合复制数据到备份里面；做日志审核；

CSV

-- 1、适合保存CSV文件；excel -> csv -- 2、不支持索引； -- 3、做数据交互；

Ferderated -- 禁止

-- 1、访问其他MySQL服务器代理； -- 2、创建远程MySQL连接，并且将查询的东西做转交；//中介

Memory

-- 1、提高查询速度，像缓存； -- 2、适合快速访问数据，且数据不会被修改； -- 3、MySQL重启，不会丢失数据； -- 4、支持hash索引； -- 5、只有表锁；//写入时读取效率低 -- 6、不支持 blob，text类型；每一行长度固定；

NDB集群引擎

-- 1、使用MySQL的服务和这个引擎可以搭建一个高可用结构；

2、第三方引擎

Percona 的 XtraDB 存储引擎

-- 1、是innodb的改进版； -- 2、支持innodb所有查询，兼容innodb的数据；

TokuDB 引擎

-- 1、数据结构：分形树； -- 2、一个写优化的 索引的 数据结构； -- 3、读的性能接近于B+树，写的新能大于B+树； -- 4、思想：节点的 MessageBuffer 的缓存更新操作，数据的局部性原理，将随机写 改成 顺序写；

infobright引擎

其他

3、选择合适的引擎：

先用innodb；

除非innodb不具备某种特性、或innodb中找不到替代的办法，那就换引擎；

不建议混合使用多种存储引擎；

四、表引擎的转换

1、Alter Table 命令

alter table mytable ENGINE = InnoDB；

适合全部类型引擎；

原理：创建一个新的表，逐行复制数据；

底层需要加表锁，效率低；

2、导出与导入

使用 mysqldump 工具将数据导出到文件；

修改文件中 CREATE TABLE 语句的存储引擎；

修改表名；

导入数据；

3、CREATE 和 INSERT...SELECT

创建一个新存储引擎的表，用 INSERT...SELECT 语句导入数据；

如果数据量大，可以分批处理；

每一段执行事务来提交操作；

可以维护一个记录表，应对突发情况；

五、MyISAM 和 innoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行、表锁	表锁； 即使操作一条数据也要锁整张表； 不适合高并发场景；	支持行锁； 操作一条数据时，仅锁一行； 适合高并发场景；
缓存	只缓存索引； 不缓存真实数据；	缓存索引和真实数据； 对内存要求高，内存大小影响性能；
表空间	小	大
关注点	性能	事务(安全)
默认安装	是	是

1、相关命令

-- 查询引擎支持
show engines;

-- 查询默认引擎
show variables like '%storage_engine%';

六、MySQL中的目录和文件

1、bin目录

1、mysqld -- 启动

2、mysqld_safe  -- 脚本启动：维护了一个线程监控mysql服务，如果mysql掉了，尝试重启；

3、mysqld_multi -- 一台计算机可以运行多个mysql实例，可以对服务器进程做启动和停止；

4、mysqladmin

5、mysqldump

6、mysqlcheck

七、MySQL的数据目录

1、查找MySQL数据目录：

show variables like 'datadir';

2、进入datadir目录，可以找到MySQL的库文件夹；

3、数据文件

innoDB

-- table.frm； -- table.ibd；

MyISAM

-- table.frm； -- table.MYD； //表数据 -- table.MYI ； //表索引

4、表空间：系统中的，一个或多个真实文件；

-- 每张表都有自己的表空间；

-- 每个表空间都包含许多页；

-- 数据表保存在表空间的某些页里面；

-- 表空间包含：系统表空间、独立表空间；

-- MySQL带的数据库叫系统表空间：ibtmpl；（5.5 ~ 5.6.6）

-- 自己创建的数据库叫独立表空间：mydb；

八、MySQL的日志文件

1、错误日志：

-- 记录系统报错信息
show variables like 'log_error'\G;

2、慢查询日志：

-- 记录慢查询的SQL；

-- 查询慢SQL日志
show variables like 'slow_query_log';

-- 查询慢日志存放地址
show variables like '%slow_query_log_file%'; 

-- 开启/关闭 慢SQL日志
set global slow_quer_log = 1;

-- 查询慢SQL阈值
show variables like '%long_query_time%'; 

-- 设置慢SQL阈值
set global long_query_time = 10;

3、普通查询日志（默认关闭）

-- 记录所有select 的日志

-- 查询普通日志
show variables like '%general%';

-- 启用普通日志
set global general_log = 1;

4、二进制文件（binlog）

-- 记录select之外的SQL；DDL，DML；

-- 以事务形式记录，记录执行消耗时间；

-- 数据恢复、数据复制、安全审计（数据库注入攻击）；

-- 查询二进制日志，配置文件修改，需要重启MySQL
show variables like 'log_bin';

-- 包含操作文件、序号文件；

九、MySQL其他的数据文件

还没学会