MySQL的架构

一、MySQL简介

• 数据库是一个以某种有组织的方式存储的数据集合，它是一个按数据结构来存储和管理数据的计算机软件系统。理解数据库的一种最简单的办法是将其想象为一个文件柜，这个文件柜只是一个存储数据的空间，而数据库就是这么一个空间。
• 数据库管理系统（Database Management System, DBMS）是用于创建、管理、和维护数据库时所使用的软件，用以管理数据库，一定程度上，用户都是在操作DBMS，所以日常的数据库概念一般就是指DBMS。
• MySQL是一种DBMS，而且是一种一个小型的开源的关系型数据库管理系统。2008年1月16日MySQL AB被Sun公司收购。而2009年，SUN又被Oracle收购。就这样如同一个轮回，MySQL成为了Oracle公司的另一个数据库项目。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

二、MySQL用户组管理

1，用户管理相关命令

　　a)创建用户

#创建用户:zs  密码:123456
create user zs identified by '123456';

　　b)查看用户权限

select host, user, password, select_priv, insert_priv,drop_priv from mysql.user;
host :表示连接类型
　　% 表示所有远程通过 TCP 方式的连接
　　IP 地址 如 (192.168.1.2,127.0.0.1) 通过制定 ip 地址进行的 TCP 方式的连接
　　机器名 通过制定 i 网络中的机器名进行的 TCP 方式的连接
　　::1 IPv6 的本地 ip 地址 等同于 IPv4 的 127.0.0.1
　　localhost 本地方式通过命令行方式的连接 ， 比如 mysql -u xxx -p 123xxx 方式的连接。
user:表示用户名
　　同一用户通过不同方式链接的权限是不一样的。
password:密码
　　所有密码串通过 password(明文字符串) 生成的密文字符串。 加密算法为 MYSQLSHA1 ， 不可逆 。
　　mysql 5.7 的密码保存到 authentication_string 字段中不再使用 password 字段。
select_priv , insert_priv 等
　　为该用户所拥有的权限。

　　c)修改密码

#MySQL5.6使用
#基本使用 修改lisi密码为123456
update mysql.user set password=password('123456') where user='lisi';
#修改当前用户
set password =password('123456');
#刷新权限生效
flush privileges;

#MySQL5.7推荐使用
#基本使用 修改testuser用户密码为123456
ALTER USER testuser IDENTIFIED BY '123456';
#修改当前登录用户 密码为123456
ALTER USER USER() IDENTIFIED BY '123456';

　　d)修改用户名

update mysql.user set user='lisi' where user='wangwu';
flush privileges;

　　e)删除用户

drop user lisi

2，MySQL的权限管理

　　a)授予权限

#grant 权限 1,权限 2,…权限 n on 数据库名称.表名称 to 用户名@用户地址 identified by '连接口令'
#给 lisi 用户用本地命令行方式下， 授予 test这个库下的所有 表的插删改查的权限。
grant select,insert,delete,drop on test.* to lisi@'%' ;
#授予通过网络方式登录的的 jack 用户，对所有库所有表的全部权 限， 密码设为 123
grant all privileges on *.* to jack@'%' identified by '123';

　　b)收回权限

#查看当前用户权限
show grants;
#收回权限
#revoke [权限 1,权限 2,…权限 n] on 库名.表名 from 用户名@用户地址;
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM jack@'%';
#收回 mysql 库下的所有表的插删改查 权限
REVOKE select,insert,update,delete ON mysql.* FROM jack@'%';

三、MySQL配置文件

• 二进制日志文件 log-bin：用于主从复制
• 错误日志log-error：默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息等
• 查询日志log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的
• 数据文件
  • 数据库文件存储：默认为 /var/lib/mysql
  • frm文件：存放表结构(都存在)
  • idb文件：存放表的数据(InnoDB)
  • myd文件：存放表数据(MyIsam)
  • myi文件：存放表索引(MyIsam)

四、MySQL的逻辑架构

1，MySQL的分层思想

• 与其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。
• 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2，MySQL的四层架构

• 连接层：最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成类似于连接处理、授权认证、以及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层：第二层架构主要完成大多数的核心服务功能，比如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

• 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
• 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

3，MySQL的查询流程

1. 客户端向MySQL服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4. MySQL根据执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端，同时缓存查询结果

五、MySQL的存储引擎

1，查看MySQL的存储引擎

#查看MySQL支持的存储引擎
show engines;
#查看当前使用的存储引擎
show variables like '%storage_engine%';

　　关于当前存储引擎出现：disabled_storage_engines

2，各存储引擎比较

a）InnoDB存储引擎（默认存储引擎），支持事务安全表（ACID），支持行锁定和外键

1. 设计遵循ACID模型，支持事务，具有从服务崩溃中恢复数据的能力，能够最大限度包含用户的数据
2. 支持行级所，可以提升多用户并发时的读写性能
3. 支持外键，保持数据的一致性和完整性
4. innoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中

b）MyISAM存储引擎：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

1. 默认MyISAM的表会在磁盘中产生三个文件：.frm、.MYD和.MYI
2. MyISAM单表最大支持的数据量是2的64次方条记录
3. 每个表最多可以建立64个索引
4. 如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
5. MyISAM引擎的存储格式：定长（FIXED 静态）：是指字段中不包含VARCHAR\TEXTBLOB；动态（DYNAMIC）：只要字段中包含VARCHAR\TEXTBLOB；压缩（COMPRESSED）:myisampack创建

c）MEMORY存储引擎：将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

1. 磁盘中产生一个以表名为名称的.frm文件，只保存表结构
2. 如果关闭MySQL服务，此时数据会产生都是rr
3. max_head_table_size默认16MB

d）ARCHIVE存储引擎：

1. 适合对于不经常访问又删除不了的数据做归档储存
2. .frm文件结构文件，.arz数据文件
3. 插入效率很高，而且占用空间小
4. ARCHIVE存储引擎只支持INSERT和SELECT操作，不支持UPDATE/DELECT/

3，MyIsam与InnoDB的索引结构

　　从图中可以看出：由于MyIsam的存储文件中多了一个.MYI的文件,所以建立的索引为非聚集索引都会指向对应的地址值；而Innodb建立的主键索引为聚合索引，所以当以普通字段建索引时，也称之为二级索引，其建立的索引必须关联对应的主键id，所以查询会执行两次。