MySQL：（六）、（七）、（八）
（一）关系型数据库基础
（二）MySQL安装
（三）管理数据库和表
（四）用户和权限管理
（五）函数，存储过程和触发器
（六）MySQL架构
（七）存储引擎
（八）MySQL服务器选项，系统和状态变量
（九）优化查询和索引管理
（十）锁和事务管理
（十一）日志管理
（十二）备份还原
（十三）MySQL集群
（六）MySQL架构
1）mysql架构图：
-
connectors：连接器
远程用户通过连接器连接至数据库中，其中连接器支持各种语言，比如：C API,Java JDBC,ODBC…连接器简单理解为各种语言与数据库连接的接口。 -
connection pool：连接池，mysql中负责接收连接。
连接池给每个连接的用户分配一个连接服务于连接的用户，即分配一个mysql线程服务于某个连接。
连接池的功能除供用户连接外，其余功能：
身份验证：
线程复用：
发起连接时mysql提供一个新的线程提供服务，断开连接时线程不会被销毁，将产生的数据清理后，又可以作为一个新线程对另一新连接提供服务。
连接限制：
用户发起连接时，都占据一个连接，连接限制决定了最多可有多少个连接，默认连接限制比较保守，只有100+，并不能充分发挥mysql性能。优化时需要设置参数将此连接数增大。
内存检查：
缓存： -
SQL语句处理：
SQL interface：
SQL接口，负责处理各种SQL语句，包括通过SQL语句编写的视图、存储过程、函数、触发器。SQL接口可检查出SQL语句是否有语法问题。
Parser：
解析器，负责将SQL语句进行解析。
Optimizer：
优化器，性能优化。以查询优化而言：是否利用索引，mysql会进行优化
Cache & Buffer：
缓存和存储引擎相关的缓存 -
存储引擎：mysql中存储引擎有许多，存储引擎决定了如何管理文件（文件的组织格式、文件的存放格式等等…）
myisam
innodb -
磁盘数据库文件和日志
file system,ntfs,nfs
redo,undo,index,binary
…
mysql将不同类别的信息分类保存于对应的不同文件中。 -
mysql提供的管理工具：
备份还原工具，安全，复制，集群等管理工具
（七）mysql的存储引擎
- 从mysql5.5.5往后的版本，mysql的默认存储引擎为innodb
1）存储引擎的比较：
- 存储引擎比较网址： https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html
MyISAM引擎
1）MyISAM引擎特点
- 不支持事务
- 表级别锁定 table level lock
- 读写相互阻塞，写时不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 不支持 高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
- 读取数据较快，占用资源较少
2）MyISAM存储引擎适用场景
- 只读（或者写较少）、表较小（可以接受长时间进行修复操作）
3)MyISAM引擎文件
- bl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
InnoDB引擎※※※※※
1）innodb存储引擎特点
- 支持事务，适合处理大量短期事务
- 行级锁
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库存储引擎
2）InnoDB数据库文件
-
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, … -
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON (>= MariaDB 5.5)
参看：https://mariadb.com/kb/en/library/xtradbinnodb-serversystem-variables/#innodb_file_per_table -
两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm
innodb中涉及的MVCC和事务
- MVCC：多版本并发控制机制
- MVCC: 多版本并发控制，和事务级别相关
以下内容与图片内容有所冲突：两个隐藏列（数据行版本号、删除版本号）
以下举例使用create和delete代替，表达大体内容差不多。
事务：多个小事件组合而成。
每个事务执行时，会默认有一个事务编号
注：事务：是可以持续的~~即在一定时间内发生的事儿，但事务编号均是一个值。
事务编号：事务编号在数据库中是全局唯一的，每个发生一件事，
就对应一个事务编号，事务编号会自动增长
在innodb存储引擎中一张表中会默认有两个隐藏字段：create和delete
create：创建记录的事务编号
delete：删除记录的事务编号
此处以时间代替事务编号：2019-9-18 16:13:32
现假设表student、
id name create delete
1 tom 2019-9-16 16:13:41 2019-9-18 16:14:07
2 bob 2019-9-17 10:14:00
3 Alice 2019-9-18 16:14:39
当对id=3的记录进行修改时，记录的事务编号为：
3 Alice 2019-9-18 16:14:39 2019-9-18 16:17:19
3 Alices 2019-9-18 16:17:19
即create和delete记录的事务编号相同，相当于先删后增
综上：当对记录进行删和改时：对应的记录并未真正的删除，只是在delete字段上
添加了一个事务编号，但是真正的记录并未删除。
这样的机制所具备的优势：利用MVCC可以实现并发控制
解释并发控制的实现：
假设执行查询时：select * from student 事务编号：2019-9-17 16:15:25
查询的记录是介于create 和 delete 之间的记录
即当前能查询的记录：
1 tom
2 bob
- 事务：多个小事件的组成
①原子性（Autmic）：多个小事件必须作为一个整体执行
②一致性（Consistency）：事务执行之前和执行之后必须处于一致的状态
③隔离性（Isolation）：当多个用户并发访问数据库时，比如同时操作同一④张表时，数据库为每个用户开启的事务不能被其他的事务干扰。即多个并发事务之间没有相互关联性。
④持久性（Durability）：事务一旦完成，对数据库的操作是永久性的，即使在数据库遇到故障的情况下也不会丢失事务的操作。
以转账作为事务特性的说明：
A 转账给 B 500
那么对数据库此操作：
事件1：在A的账户余额中减去500
事件2：在B的账户余额中增加500
必须作为一个事务处理：事件1和事件2必须作为整体执行
原子性：
一致性：A减掉500，B加上的必须也是500
隔离性：事务并发执行时，各事务是隔离的
持久性：
事务可保证数据的完整。
- 事务的redo和undo
innodb支持事务，myisam不支持事务，因为innodb支持事务，在数据库操作过程
中出现故障，数据库崩溃以后的恢复性更好，恢复时涉及redo和undo的恢复方式
以事务方式增删改数据库时：
==>在内存中将事件发生，发生一个事件，事务日志记录对应事件
①先写事务日志
②再写数据库文件
以A转账给B 500为例：
redo：
事件1：在A的账户余额中减去500
事件2：在B的账户余额中增加500
==>事务日志记录事务事件已经执行完成 -- 完整
========此时掉电
恢复数据库时redo
undo：
事件1：在A的账户余额中减去500
==>事务日志记录事务事件未完成 -- 不完整
========此时掉电
恢复时数据库时undo
其他存储引擎
-
Performance_Schema：Performance_Schema数据库使用
-
Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
-
MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
-
Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
-
Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
-
BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
-
Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
-
CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
-
BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
-
example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
-
MariaDB支持的其它存储引擎：
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE
管理存储引擎
-
查看mysql支持的存储引擎
show engines; -
查看当前默认的存储引擎
show variables like ‘%storage_engine%’; -
设置默认的存储引擎
vim /etc/my.conf
[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数据库
是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息 -
performance_schema数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表 -
information_schema数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）
（八）MySQL服务器选项，系统变量和系统状态变量
服务器选项
-
mysqld选项，服务器系统变量和服务器状态变量
https://dev.mysql.com/doc/refman/5.7/en/server-optionvariable-reference.htmlhttps://mariadb.com/kb/en/library/full-list-of-mariadb-optionssystem-and-status-variables/
-
注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置
-
获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 获取默认设置 -
设置服务器选项方法：
在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
skip_name_resolve=1
服务器系统变量
- 服务器系统变量：分全局和会话两种
- 获取系统变量
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES; - 修改服务器变量的值：
mysql> help SET - 修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value; - 修改会话变量：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;
服务器变量服务器变量SQL_MODE
-
SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/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:
> select @@sql_mode; ==>默认值
10.2.4 STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
10.1.7 NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER
10.1.7之前 No value
设置sql_mode：
set sql_mode='NO_AUTO_CREATE_USER' ... ;
TRADITIONAL:以下值的集合
STRICT_TRANS_TABLES,
STRICT_ALL_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
TRADITIONAL,
NO_AUTO_CREATE_USER
服务器系统状态变量
- 服务器状态变量：
分全局和会话两种 - 状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;
总而言之，言而总之：
1）服务器选项：
放到配置文件中的是服务器选项
选项在官方网页说明使用的单词连接符为' - '
2）服务器变量：
可以变量查询语句查询的为变量。
show variable；
show variables like 'var_name';
select @@var_name
变量在官方网页说明使用的单词连接符是' _ '
3）服务器状态变量
当前状态的一种体现，只读变量，无法修改
注：配置文件不区分 ' - ' 和 ' _ '
mysql官网表格分析：
server option ,system variable , status variable reference
①Cmd-Line：命令行
支持命令行就是支持当服务器程序的参数
②Option File:文件选项
③System Var:系统变量
④Status Var:状态变量
⑤Var Scope：变量的生效范围
global：全局变量
Session：会话变量生效范围为当前会话
both：global and session
Varies：
⑥Dynamic：支持动态修改
在数据库不重启的基础上直接修改。
举例：
max_connections
(cmd-line) (option file) (system var) (global var) (dynamic)
默认值：151
max_connections：同时运行客户端最大连接数即并发连接数。
查询：
> select @@max_connections;
> show variables like 'max_connections';
修改：
> set global max_connections=2000; 临时修改
配置文件修改：
配置文件修改：vim /etc/my.cnf
[mysqld]
max_connections=2000
仅仅改[mysqld]语句块是不够滴！
找到对应的open_files_limit变量
①使用mysqld_safe 启动时
[mysqld_safe]
Open_File_Limit=4000
②使用systemctl启动程序时还要修改对应的.service文件
vim /usr/lib/systemd/system/mariadb.service
[Service]
LimitNOFILE=5000
systemd启动时需要修改三个地方！
③除此之外，修改/etc/security/limits.conf
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
