MySQL进阶 一

Mysql高级进阶

Mysql架构

数据库：按照数据结构来组织、存储和管理数据的仓库，通常由数据库管理系统进行管理

数据库管理软件（RDBMS）：就是我们说的数据库管理系统软件，他强调软件

数据库实例：启动数据库软件，在内存中运行一个独立进程，用来操作数据，这个正在运行的进程就是一个数据库实例，理论上可以在一台电脑上启动多个数据库实例，当然要监听在不同的端口

图片来源于网络

MySQL向外提供的交互接口（Connectors）

Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么

管理服务组件和工具组件(Management Service & Utilities)

提供MySQL的各项服务组件和管理工具，如备份(Backup)，恢复(Recovery)，安全管理(Security)等功能

连接池组件(Connection Pool)

负责监听客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

SQL接口组件(SQL Interface)

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户

查询分析器组件(Parser)

首先分析SQL命令语法的合法性，并进行抽象语法树解析，如果sql有语法错误，会抛出异常信息。

优化器组件（Optimizer）

对SQL命令按照标准流程进行优化分析，mysql会按照它认为的最优方式进行优化，选用成本最小的执行计划。

缓存主件（Caches & Buffers）

缓存和缓冲组件，这里边的内容我们后边会详细的讲解。

MySQL存储引擎

MySQL属于关系型数据库，而关系型数据库的存储是以表的形式进行的，对于表的创建，数据的存储，检索，更新等都是由MySQL存储引擎完成的。

MySQL存储引擎在MySQL中扮演着重要角色。MySQL的存储引擎种类比较多，如MyIsam存储引擎，InnoDB存储引擎和Memory存储引擎

因为mysql本身就是开源的，他允许第三方基于MySQL骨架，开发适合自己业务需求的存储引擎。从MySQL存储引擎种类上来说，可以分为官方存储引擎和第三方存储引擎，比较常用的存储引擎包括InnoDB存储引擎，MyIsam存储引擎和Momery存储引擎。

Mysql查询流程

MySQL8.0取消了查询缓存

MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。hash计算和查找所带来的开销需要重视

查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询，因为他们的hash值会不同

当向某个表写入数据的时候，必须将和这个表相关的所有缓存设置为失效，如果缓存内容很多，则消耗也会很大。

MySQLbin目录结构

MySQL服务器端工具

mysqld：SQL后台保护程序(MySQL服务器进程)。该程序必须运行之后。客户端才能通过连接服务器端程序访问和操作数据库

mysqld_safe：MySQL服务脚本。mysql_safe增加了一些安全特性，如当出现错误时重启服务器，向错误日志文件写入运行时间信息

mysql.server：MySQL服务启动服本。调用mysqld_safe来启动MySQL服务

mysql_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务

myiasmchk：用来描述、检查、优化和维护MyISAM表的实用工具

mysqlbu：MySQL缺陷报告脚本。它可以用来向MySQL邮件系统发送缺陷报告

mysql_install_db：用于默认权限创建MySQ授权表。通常只是在系统上首次安装MySQL时执行一次

MySQL客户端工具

mysql：交互式输入SQL语句或从文件以批处理模式执行SQL语句来操作数据库管理系统，就是我们的客户端

mysqldump：将MySQL数据库转储到一个文件，可以用来备份数据库

mysqladmin：用来检索版本、进程、以及服务器的状态信息

mysqlbinlog：用于从二进制日志读取语句。在二进制日志文件中包含执行的语句，可用来帮助系统从崩溃中恢复

mysqlcheck：检查、修复、分析以及优化表的表维护

mysqlhotcopy：当服务器在运行时，快速备份MyISAM或ISAM表的工具

mysql import：使用load data infile将文本文件导入相关表的客户程序

perror：显示系统或MySQL错误代码含义的工具

myisampack：压缩MyISAP表，产生更小的只读表

mysaqlaccess：检查访问主机名、用户名和数据库组合的权限

mysql.sock 不是bin目录中的，是linux系统中的mysql文件，socket 即 Unix 域套接字文件，在类 unix 平台，客户端连接 MySQL 服务端的方式有两种，分别是 TCP/IP 方式与 socket 套接字文件方式。Unix 套接字文件连接的速度比 TCP/IP 快，但是只能连接到同一台计算机上的服务器使用。通过设置 socket 变量可配置套接字文件路径及名称，默认值为 /tmp/mysql.sock，如果mysql.sock文件误删的话，就需要重启mysql服务

mysql数据备份

-- 备份一个表
mysqldump -u root -p 数据库 表  > ~/dump.txt
-- 备份一个数据库
mysqldump -u root -p 数据库  > ~/dump.txt
-- 备份所有数据库
mysqldump -u root -p --all-databases > dump.txt

mysql数据恢复

mysql -u root -p 要还原到的数据库 < ~/dump.txt

字符集和排序规则

查看字符编码

show collation like '%utf8%';

后缀	英文	描述
_ai	accent insensitive	不区分重音（è，é，ê和ë）
_as	accent sensitive	区分重音
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制的形式进行比较

utf8和utf8mb4的区别

utf8mb3(utf-8)：使用1~3个字节表示字符，utf8默认就是utf8mb3

utf8mb4：使用1~4个字节表示字符，他是utf8的超集，甚至可以存储很多,emoji表情，mysql8.0已经默认字符集设置为utf8mb4。

character_set_server和collate_server对全局的字符集和排序规则进行设置。这两个设置可以在配置文件中修改

mysql修改配置

查看全局配置变量

show global variables like '%wait_timeout%';
select @@global.wait_timeout;

修改配置变量

• 修改配置文件，然后重新启动

# vi /etc/my.cnf
[mysqld]
wait_timeout=10000

• set方式

set global wait_timeout=10000;
set @@global.wait_timeout=10000;

设置当前会话

• 修改

mysql> set wait_timeout=10000;
mysql> set session wait_timeout=10000;
mysql> set local wait_timeout=10000;
mysql> set @@wait_timeout=10000;
mysql> set @@session.wait_timeout=10000;
mysql> set @@local.wait_timeout=10000;

• 查看

mysql> select @@wait_timeout;
mysql> select @@session.wait_timeout;
mysql> select @@local.wait_timeout;
mysql> show variables like 'wait_timeout';
mysql> show local variables like 'wait_timeout';
mysql> show session variables like 'wait_timeout';

内置数据库

mysql：这个库很重要，他是mysql的核心数据库，负责存储数据库的用户、权限设置、关键字等mysql自己需要使用，控制和管理的信息

information_schema：这个数据库维护了数据库其他表的一些描述性信息，也称为元数据。比如，当前有哪些表，哪些视图，哪些触发器，哪些列等

performation_schema：这个数据库用来存储mysql服务器运行过程中的一些状态信息，是做性能监控的。比如最近执行了什么sql语句，内存使用情况等

sys：结合information_schema和performation_schema的数据，能更方便的了解mysql服务器的性能信息。

数据存储

innodb数据存储

对于innodb而言，数据是存储在表空间内的，表空间是一个抽象的概念，他对应着硬盘上的一个或多个文件

表空间存储数据的单位是页，每一页大概占用16k的空间

系统表空间

• 系统表空间包含了很多【公共数据】，比如InnoDB的数据字典，回滚信息、系统事物信息、二次写缓冲等，老版本的mysql表中的数据也会存储在系统表空间

• 系统表空间是一个共享的表空间因为它是被多个表共享的

• 该空间的数据文件通过参数【innodb_data_file_path】控制，默认值是ibdata1:12M:autoextend（文件名为ibdata1、12MB、自动扩展）

-- 如果1代表开启，0代表关闭
show variables like'innodb_file_per_table'
-- 设置对应的变量
set global innodb_file_per_table=0;
-- 查看系统表空间的配置
show variables like "innodb_data_file_path";
-- 配置文件的配置
innodb_data_file_path=data1:512M;data2:512M:autoextend

独立表空间

独立表空间是默认开启的，在5.6.6以后，Innodb不在默认将各个表的数据存储在系统表空间当中，而是会为每一个表建立一个独立表空间，innodb存储引擎的独立表空间为.ibd文件

如果启用了innodb_file_per_table参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他数据如：回滚信息、系统事物信息、二次写缓冲（Double write buffer）等还是放在原来的系统表空间内,同时即使启用了innodb_file_per_table参数，系统表空间还是会不断的增加其大小的

其他类型表空间

MyIsam没有表空间的概念，他会在目录中产生2个文件.MYD（数据文件）、.MYI（索引文件）三个文件,在5.7以前数据文件和表信息文件是分开的，相互独立的。会多一个.frm文件，8.0之后进行了合并

组织结构

区（extent）：每一个表空间保存了大量的页，为了更好的管理这些页面，Innodb提出了区的概念，对于16k的页，连续64个页就是一个区，大概1M的空间，每一个表空间都是由若干个连续的区组成的，每256个区被划分为一组

段（Segment）：分为索引段，数据段，回滚段等后边会将，段是为了区分不同的数据类型，相同的段存的数据类型是一致的。一个段包含256个区(256M大小)。

Row Format

• 第一个部分是一个非NULL【变长字段长度列表】。变长列的真实长度就保存在这个部分，他是按照列的顺序【逆序放置】的。当列的长度小于255字节，如（varchar(50)），用1字节表示；若大于255个字节（varchar(600)），用2个字节表示，这其实也就说明了为什么varchar的最大长度是65536

• 第二个部分是NULL标志位，他指示了当前行数据中哪些为null值，用一个bitmap表示。NULL值标志位也是逆序排列，占用空间按照字节数高位补零，如有九个字段可以为空（00000001 01010101）

• 第三部分为记录头信息，固定占用5个字节（40位）

• 第四部分就是实际存储的每个列的数据了，需要特别注意的是，NULL不占该部分任何数据，即NULL除了占有NULL标志位，实际存储不占有任何空间。Innodb存储变长列（VARCHAR, VARBINARY, BLOB, TEXT）的前768字节，剩下的部分存储在溢出页中。固定长度列，超过768字节的视为变长列。内部存储前768字节，20字节指针存储列的溢出页的地址，所以长度为768+20字节

举例

create table test (
　　t1 varchar(10),
　　t2 varchar(10),
　　t3 char(10),
　　t4 varchar(10)
) engine=innodb row_format=compact;

insert into row_test values('a','bb','bb','ccc'); 
insert into row_test values('d','ee','ee','fff'); 
insert into row_test values('d',NULL,NULL,'fff'); 

• 表空间二进制结构

                         03 02 01 00 00 00 10 00 
2c 00 00 00 00 2b 68 00  00 00 00 06 05 80 00 00 
00 32 01 10 61 62 62 62  62 20 20 20 20 20 20 20 
20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  
00 02 01 00 00 00 00 0f  62 c9 00 00 01 b2 01 10  
64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  
03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00 
00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第一行没有NULL值
00 00 10 00 2c // 记录头信息，固定5字节长度
00 00 00 00 2b 68 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 06 05 // 事务ID，固定6个字节
80 00 00 00 32 01 10 // 回滚指针，固定7个字节
61 // t1数据'a'
62 62 // t2'bb'
62 62 20 20 20 20 20 20 20 20 // t3数据'bb'  Ox20十进制是32对应ascii码是空字符
63 63 63 // t4数据'ccc'

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第二行没有NULL值
00 00 18 00 2b // 记录头信息，固定5字节长度
00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 62 // 事务ID，固定6个字节
c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节
64 // t1数据'd'
65 65 // t2数据'ee'
65 65 20 20 20 20 20 20 20 20 // t3数据'ee'
66 66 66 // t4数据'fff'

03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1
06 // 00000110 NULL标志位，t2和t3列为空
00 00 20 ff 98  // 记录头信息，固定5字节长度
00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 67 // 事务ID，固定6个字节
cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节
64 // t1数据'd'
66 66 66 // t4数据'fff'

REDUNDANT

• 第一个部分保存了字段长度偏移列表，这个部分保存了该行数据所有列，包括隐藏列的长度偏移量，这个偏移列表是按照列的顺序逆序排列

• 第二个部分为记录头信息record header，Redundant行格式固定占用6个字节（48位）

• 第三个部分就是实际存储的每个列的数据了

DYNAMIC

InnoDB Plugin引入了两种新的文件格式（file format，可以理解为新的页格式），对于以前支持的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种

新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。mysql8.0默认此格式

正确的开始 微小的长进 然后持续 嘿 我是小博 带你一起看我目之所及的世界……