MySQL（四）

MySQL架构原理

参考原文：百度安全验证

MySQL是C/S架构。

连接层

提供与MySQL服务器建立连接的支持。

几乎支持所有主流的编程技术，Java、php、Python等。

服务层

MySQL Server的核心。主要包括 系统管理和控制工具、连接池、SQL接口、解析器、查询优化器、缓存 六部分。

系统管理和控制工具（Management Services & Utilities）

例如备份恢复、安全管理、集群 管理等。

连接池（Connection Pool）

用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

MySQL是单进程多线程模型的。分为长连接和短连接。

• 长连接：使用MySQL客户端登录数据库后，直到使用quit命令退出数据库。

• 短连接：使用mysql -e 选项，客户端向服务端申请运行一个命令后立即退出。

Authentication

认证。用户发送的账号密码是否正确。

Thread reuse

线程重用。当一个用户连接进来以后要用一个线程来响应它，而后当用户退出时，这个线程可能并非被销毁，而是把它清理完后，重新收归到线程池当中的空闲线程中，以完成线程重用。

Connection limit

连接限制。线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦到达上限，后续的请求只能排队或拒绝连接。

Check memory

检测内存

caches

线程缓存

解析器（Parser）

负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。

查询优化器（Optimizer）

当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

缓存（Cache&Buffer）

缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

存储引擎层

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。

最常见的是MyISAM和InnoDB。

系统文件层

负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

存储引擎

MySQL支持多种存储引擎，目前应用最广的是MyISAM和InnoDB。

MyISAM

特点

1、表级别锁定

2、不支持事务

3、读写相互阻塞，写时不能读，读时不能写

4、只缓存索引

5、不支持外键约束

6、不支持聚簇索引

7、不支持MVCC（多版本并发控制机制）高并发

8、崩溃恢复性较差

9、MySQL5.5.5 前默认的数据库引擎

引擎文件

table_name.frm         表格式定义

table_name.MYD      数据文件

table_name.MYI        索引文件

InnoDB

特点

1、行级别锁定

2、支持事务

3、读写阻塞与事务隔离级别相关

4、缓存数据和索引

5、支持聚簇索引

6、支持MVCC（多版本并发控制机制）高并发

7、崩溃恢复性更好

8、MySQL5.5.5 开始成为默认的数据库引擎

9、从MySQL5.5 后支持全文索引

引擎文件

1、所有InnoDB表的数据和索引放置于同一个表空间中

适合对数据的集中管理和简单的管理需求。

数据文件：ibdata，存放在datadir定义的目录下

表格式定义：tb_name.frm，存放在datadir定义的每个数据库对应目录下

2、每个表单独使用一个表空间存储表的数据和索引

适合需要更高的灵活性或更细粒度的存储管理。

两类文件放在每个数据库对应的独立目录中。

数据文件（存储数据和索引）：tb_name.ibd

表格式定义：tb_name.frm

管理存储引擎

查看MySQL支持的存储引擎

show engines;

查看默认的存储引擎

show variables like '%storage_engine%';

设置默认存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like 'tb_name';

show create table tb_name;

设置表的存储引擎

create table tb_name(... ) ENGINE=InnoDB;
alter table tb_name engine=InnoDB;

MySQL中的系统数据库

mysql

核心数据库，包含关于用户权限、全局系统变量等的信息

sys

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema，包含一些系统视图、表和存储过程，为了帮助数据库管理员更好地管理和监控MySQL服务器而设计的。

information_schema

虚拟数据库，提供关于其他所有数据库的元数据（metadata）信息。如数据库名、表名、访问权限等

performance_schema

主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

服务器配置和状态

官方帮助文档：https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-andstatus-variables/

服务器选项

查看mysqld的当前启动选项

mysqld --print-defaults

[root@wenzi ~]$mysqld --print-defaults
mysqld would have been started with the following arguments:
--server-id=1 --log-bin --datadir=/data/mysql --socket=/data/mysql/mysql.sock --log-error=/data/mysql/mysql.log --pid-file=/data/mysql/mysql.pid

设置服务器选项

在配置文件my.cnf中设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1    #不使用dns解析
skip-grant-tables    #跳过权限表的检查

服务器系统变量

分为：全局、会话

查看所有系统变量        show variables;

查看全局系统变量        show global variables;

查看会话系统变量        show session variables;

查看指定系统变量        show variables like 'var_name'; 或 select @@var_name;

修改全局系统变量（仅对修改后新创建的会话有效，对已经建立的会话无效）       

set global var_name=value; 或 set @@var_name=value;

修改会话系统变量        set [session] var_name=value; 或 set @@[session.]var_name=value;

服务器状态变量

分为：全局、会话

查看全局状态变量        show global status;

查看会话状态变量        show session status;

查看指定状态变量        show status like 'var_name';

服务器变量SQL_MODE

常见mode：

NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ZERO_DATE：在严格模式，不允许使用'0000-00-00'的时间
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
NO_BACKSLASH_ESCAPES：反斜杠"\"作为普通字符而非转义字符
PIPES_AS_CONCAT：将"||"视为连接操作符而非"或"运算符

查看现有sql_mode设置        show variables like 'sql_mode';

设置sql_mode        set sql_mode=mode选项

INDEX索引

简介

索引：是排序的快速查找的特殊数据结构，定义在作为查找条件的字段上，又称为键key，索引通过存储引擎实现

优点

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O

缺点

占用额外空间，影响插入速度

类型

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

索引结构

参考链接：Data Structure Visualization

二叉树

Binary Search Tree Visualization

数据插入顺序：100 80 120 60 70 130 110 105 75

红黑树

Red/Black Tree Visualization

数据插入顺序：100 80 120 60 70 130 110 105 75

红黑树满足以下性质：

1. 每个节点要么是红色，要么是黑色。
2. 根节点是黑色。
3. 如果一个节点是红色的，则它的两个子节点都是黑色。
4. 从任一节点到其所有后代叶节点的简单路径上，均包含相同数目的黑色节点。
5. 每个叶节点（NIL节点，空节点）都是黑色的。

B-Tree

B-Tree Visualization

B+Tree

Hash

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针。

R-Tree

R树是一种用于处理空间数据的数据结构，主要用于地理信息系统（GIS）和数据库系统。

全文索引FULLTEXT

全文索引是一种特殊类型的索引，用于全文搜索。它允许用户搜索文本数据中的词或短语。

聚簇索引

聚簇索引决定了数据在磁盘上的物理存储顺序，如果一个表有聚簇索引，那么它的数据记录实际上是按该索引的键值顺序存储的

二级索（非聚簇索引）

是为数据记录存储一个指向物理存储位置的指针；数据记录本身并不按非聚簇索引键的顺序存储

主键索引

主键索引是基于表的主键的索引

冗余索引

当一个字段或字段组合的索引值有重复时

管理索引

创建索引

CREATE INDEX index_name ON tbl_name (index_col_name[(length)],...);

查看索引

SHOW INDEX FROM [db_name.]tbl_name;

删除索引

DROP INDEX index_name ON tbl_name;

EXPLAIN工具

用来分析查询是如何被执行的，以及如何优化查询以获得更好的性能

格式：EXPLAIN SELECT clause

mysql> explain select StuID,Name from students where StuID<5;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

PROFILE工具

开启profile：set profiling = on;

查看语句：show profiles;

显示语句的详细执行步骤和时长：show profile for query query_id;

mysql> select StuID,Name from students where StuID<5;
mysql> select StuID,Name from students where StuID=5;

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.00418225 | select StuID,Name from students where StuID<5 |
|        2 | 0.00034500 | select StuID,Name from students where StuID=5 |
+----------+------------+-----------------------------------------------+

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000134 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000016 |
| init                 | 0.000020 |
| System lock          | 0.000008 |
| optimizing           | 0.000042 |
| statistics           | 0.000047 |
| preparing            | 0.000008 |
| executing            | 0.000002 |
| Sending data         | 0.000009 |
| end                  | 0.000002 |
| query end            | 0.000006 |
| closing tables       | 0.000005 |
| freeing items        | 0.000029 |
| cleaning up          | 0.000012 |
+----------------------+----------+

并发控制

锁机制

锁类型

读锁：S锁，共享锁，只读不可写（包括当前事务），多个读互不阻塞。

写锁：X锁，独占锁，会阻碍其它事务（不包括当前事务）的读和写

S锁和S锁是兼容的；X锁和其它锁是冲突的；

兼容锁：事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁。

锁冲突：但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放。

锁粒度

表级锁：MyISAM

行级锁：InnoDB

分类

隐式锁：由存储引擎自动施加锁

显示锁：用户手动请求

显示使用锁

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias] lock_type]

lock_type 分为：read、write

解锁

unlock tables

事务

是一系列相关数据库操作的集合

特性（ACID）

1. 原子性（Atomicity）：事务被视为一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态转移到另一个一致性状态。
3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务。
4. 持久性（Durability）：一旦事务提交，其更改就是永久保存于数据库中。

管理事务

启动事务

begin

结束事务

提交：commit        相当于vim中wq保存退出

回滚：rollback        相当于vim中q!不保存退出

查看当期正在进行的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

死锁

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态；

可通过show processlist;查看事务编号，在通过kill杀死事务

隔离级别

MySQL默认是可重复读。

脏读（Dirty Read）：在一个事务中读取到另一个事务未提交的数据。如果那个事务回滚了，那么读取到的数据就是临时的，可能会造成错误。

不可重复读（Non-Repeatable Read）：在一个事务中，多次读取同一数据集合时，由于其他事务的并发修改，可能会得到不同的结果。这包括数据被更新或删除的情况。

幻读（Phantom Read）：在一个事务中，多次执行相同的查询，由于其他事务的并发插入操作，可能会得到不同的结果集。幻读主要发生在对数据库进行查询操作时，结果集中出现了原本不存在的行。

隔离级别	脏读	不可重复读	幻读	加读锁
Read Uncommitted        读未提交	可能	可能	可能	否
Read Committed            读已提交	不可能	可能	可能	否
Repeatable Read           可重复读	不可能	不可能	可能	否
Serializable                     序列化	不可能	不可能	不可能	是

1. Read Uncommitted（读未提交）：这是最低的隔离级别，一个事务可以读取到另一个事务尚未提交的数据。这种隔离级别可能会导致脏读、不可重复读和幻读。
2. Read Committed（读已提交）：只有当数据被另一个事务提交后，当前事务才能读取该数据。这种隔离级别解决了脏读问题，但可能发生不可重复读和幻读。
3. Repeatable Read（可重复读）：在这个隔离级别下，一个事务读取某个数据过程中，即使有其它提交的事务修改数据，仍只能读取未修改前的数据。这防止了不可重复读，但仍然可能发生幻读。
4. Serializable（串行化）：这是最高的隔离级别，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务）或未提交的写事务阻塞读、写事务（加写锁，其它事务读、写都不可执行），避免了脏读、不可重复读和幻读问题。

MVCC多版本并发控制

MVCC（多版本并发控制机制）只在READ COMMITTED（读已提交）和REPEATABLE READ（可重复读）两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE则会对所有读取的行都加锁。

指定事务隔离级别

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

日志

事务日志 transaction log

当事务发生时，MySQL会先将修改记录到重做日志redo中，而不是直接修改数据页。这样即使系统崩溃，也可以通过重做日志来恢复未提交的事务的修改。

如果事务需要回滚，MySQL可以利用回滚日志undo中的数据来撤销已经做的修改，恢复到事务开始前的状态。

建议和数据文件分开存放

redo log：记录某数据块被修改后的值，数据更新前先记录redo log( WALWrite Ahead Log )，可以
用来恢复未写入data file的已成功事务更新的数据。

undo log：保存与执行操作相反的操作，即记录某数据被修改前的值，可以用来在事务失败时进
行rollback。

InnoDB事务日志相关配置

show variables like '%innodb_log%';

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |    每个日志文件大小
| innodb_log_files_in_group   | 2        |    日志组成员个数
| innodb_log_group_home_dir   | ./       |    事务文件路径
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

0

在insert后，把对数据表的修改操作记录在MySQL的log缓冲区；当事务提交时，将数据写入log缓冲区，每秒结束的时刻将log缓冲区的数据写入OS缓冲区并刷入到磁盘。

事务提交时没有写磁盘的操作；而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。

由于减少了I/O次数，所以性能更好，但服务器崩溃可能丢失最后一秒的事务。

1

在insert后，把对数据表的修改操作记录在MySQL的log缓冲区；当事务提交时，将数据直接写入OS缓冲区，并且刷入到磁盘。

默认值；每次提交事务后都及时把数据写入刷新到磁盘。

完全遵守ACID特性；每次事务提交时都会有一个I/O操作，可能影响性能。

2

在insert后，把对数据表的修改操作记录在MySQL的log缓冲区；当事务提交时，将数据写入OS缓冲区，每秒结束的时刻将OS缓冲区的数据刷入到磁盘。

每次提交都会将数据写入OS缓冲区，但每秒才会进行一次刷新写入到磁盘文件中。

性能比0差些，但服务器崩溃可能导致丢失最后一秒事务。

高并发业务行业推荐使用 2 。

解释：配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快 。

           配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

设置为 1 ，同时sync_binlog = 1时表示最高级别容错。

错误日志 error log

默认保存在 /var/log/mysqld.log

内容

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志文件路径

select @@log_error;

通用查询日志 general query log

默认保存在 /var/lib/mysql/ 目录下

记录服务器接收到的每个客户端的连接和语句。

默认关闭状态。

相关配置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

查询是否启用通用日志

select @@general_log;

启用通用日志

set global general_log=1;

查询通用日志文件路径

select @@general_log_file;

慢查询日志 slow query log

记录执行查询时长超出指定时长的操作

相关配置

开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
slow_query_log=ON|OFF

慢查询的阀值，单位秒,默认为10s
long_query_time=N

慢查询日志文件
slow_query_log_file=HOSTNAME-slow.log

二进制日志 binary log

默认位于 /var/lib/mysql/ 目录下，建议二进制日志和数据文件分开存放。

内容

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

功能

通过"重放"日志文件中的事件来生成数据副本

二进制日志记录的三种格式（推荐row）

基于"语句"记录：statement，记录执行的SQL语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少

基于"行"记录：row，记录数据数据的变化，而不是执行的SQL语句，日志量较大，更加安全，建议使用的格式，MySQL8.0默认格式

混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上
）

查看当前二进制日志记录格式

show variables like 'binlog_format';

二进制日志文件的构成

索引文件：文本格式，记录当前已有的二进制日志文件列表。如： wenzi-bin.index

日志文件：二进制格式，用特定工具mysqlbinlog查看。如：wenzi-bin.000001

相关配置

sql_log_bin=ON|OFF

是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项

log_bin=/PATH/BIN_LOG_FILE

指定文件位置；默认OFF，表示不启用二进制日志功能，和sql_log_bin都开启才生效

binlog_format=STATEMENT|ROW|MIXED

二进制日志记录的格式

pire_logs_days=N

二进制日志可以自动删除的天数。 默认为0，即不自动删除

sync_binlog

控制着二进制日志（binlog）何时写入磁盘

当sync_binlog=0：这是默认设置，表示 MySQL 不会主动将 binlog 内容同步到磁盘，完全依赖操作系统的文件系统来缓存和刷新数据。这种方式下，性能最好，但存在数据丢失的风险，尤其是在系统崩溃时。如果系统崩溃，可能丢失所有在 binlog cache 中的事务日志数据。

当sync_binlog=1：这是最安全的设置，表示每次事务提交时，MySQL 都会将 binlog 内容同步到磁盘。这样可以保证即使发生崩溃，所有已提交的事务也会被记录到 binlog 中。然而，这样会增加磁盘 I/O 的负担，可能会对性能产生影响。

当sync_binlog=N（N>1）：表示每 N 个事务提交后，MySQL 才会将 binlog 内容同步到磁盘。这种方式是前两者的折中，既考虑了一定的性能，也兼顾了数据的安全性。在系统崩溃时，可能会丢失最近 N 个事务的数据。这种方式可以在一定程度上平衡 MySQL 的性能和数据的安全性。

查看二进制日志文件列表及大小

show master logs;

查看使用中的二进制文件

show master status;

mysqlbinlog

查看二进制日志工具

格式：mysqlbinlog 选项 log_file

选项：

--start-position= ：指定开始位置，即at后面的数字

--stop-position= ：指定结束位置，不含此

--start-datetime= ：指定开始时间，时间格式 YYYY-MM-DD hh:mm:ss

--stop-datetime= ：指定结束时间

同步远程主机的二进制日志，可搭配crond定时任务使用

从10.0.0.8远程主机实时同步从指定的二进制日志binlog.000002开始向后进行同步到当前目录
[root@centos8 data]#mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456 
--raw --stop-never binlog.000002

二进制日志格式

# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0

事件发生的日期和时间：151105 16:31:4
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0

删除所有二进制日志，并且index文件重新计数

reset master

切割日志

mysql>flush logs;

mysqladmin flush-logs

中继日志 relay log

用于主从复制中的从服务器，记录从主服务器接收到的事件。