一，MySQL数据库体系结构
| 层级 | 说明 |
|---|---|
| 连接层 | 主要完成一些类似于连接处理，授权认证，及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限 |
| 服务层 | 完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等 |
| 引擎层 | 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不通的存储引擎有不同的功能，可以根据需要，选取合适的存储引擎 |
| 存储层 | 将数据存储在文件系统上，并完成与存储引擎的交互 |
二，MySQL系统数据库
| 系统数据库 | 作用 |
|---|---|
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| mysql | 存储MySQL服务器正常运行所需要的各种信息（例如时区，主从，用户，权限等） |
| performance_schema | 为MySQL服务器运行时提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图 |
三，存储引擎
存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型
-- 查看数据库支持的存储引擎
SHOW ENGINES;
| InnoDB | MyISAM | Memory | |
|---|---|---|---|
| 描述 | 是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎 | 是MySQL早期的默认存储引擎 | 不支持事务。支持表锁。访问速度快 |
| 特点 | DML操作遵循ACID模型，支持事务。行级锁。支持外键约束 | 不支持事务。支持表锁。访问速度快 | 内存存放，支持hash索引 |
| 文件 | InnoDB引擎的每张表都会对应一个xxx.ibd表空间文件，存储该表的表结构，数据和索引 | .MYD文件存放数据。.MYI文件存放索引。 .sdi文件存放表结构的信息 | .sdi文件存储表结构信息 |
| 适用场景 | 对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。数据操作除了插入外，好包含很多的更新和删除操作 | 以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高 | 临时表或缓存 |
四，索引
索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。索引提高了数据查询和排序效率，但是索引需占用更多存储空间，降低了表更新的速度
-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 ON 表名(字段1,... ...);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX 索引名称 ON 表名;
1.MySQL索引结构
MySQL索引是在存储引擎层实现的，不同的存储引擎支持不同的索引结构
| 结构 | 描述 | InnoDB | MyISAM | Memory |
|---|---|---|---|---|
| B+Tree索引 | 最常见的索引结构，大部分引擎都支持B+树索引 | 支持 | 支持 | 支持 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 | 不支持 | 不支持 | 支持 |
| R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引，主要用于地理空间数据类型 | 不支持 | 支持 | 不支持 |
| Full-text（全文索引） | 是一种通过建立倒排索引匹配文档的方式 | 5.6版本之后支持 | 支持 | 不支持 |
2.MySQL索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 查找的是文本中的关键字，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据存储和索引放到一起，索引结构的叶子结点保存了行数据 | 只有一个，如果存在主键，主键索引就是聚集索引，如果不存在主键，将使用第一个唯一索引作为聚集索引，如果没有主键也没有唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引 |
| 二级索引（Secondary Index） | 将数据和索引分开存储，索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
五，视图
1.视图定义
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询和使用的表，并且是在使用视图是动态生成的。即视图只保存了查询的SQL逻辑，不保存查询结果
要使视图可进行更新操作，视图的行与基础表中的行之间必须存在一对一的关系。如果视图包含聚合函数、窗口函数、DISTINCT、GROUP BY、HANVING、UNION、UNION ALL则视图不可更新
-- 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列表名称)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION]
-- 查看创建视图的SQL语句
SHOW CREATE VIEW 视图名称;
-- 查看视图数据
SELECT * FROM 视图名称...;
-- 修改视图
ALTER VIEW 视图名称[(列表名称)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION]
-- 删除视图
DROP VIEW [IF EXISTS] 视图名称[, 视图名称, ...]
2.检查选项
使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图。
| 检查选项 | 作用 |
|---|---|
| CASCADED | 检查本视图及依赖视图中的规则以保持一致性 |
| LOCAL | 仅检查本视图的规则以保持一致性 |
六，存储过程（Stored Procedure）
存储过程是数据库管理系统中的一组预编译的SQL语句和可编程流程控制结构的集合。它存储在数据库中，可以通过指定名称并提供参数（如果需要的话）来调用执行
-- 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN-- SQL语句
END;
-- 调用存储过程
CALL 存储过程名称([参数列表]);
-- 查看某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;
-- 查看指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名称';
-- 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
注：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符
| 优点 | 描述 |
|---|---|
| 提高性能 | 存储过程在创建时即被编译，并以编译后的形式存储在数据库中。当存储过程被调用时，不需要再次编译，这可以显著减少网络流量和处理时间，提高应用程序的整体性能 |
| 减少网络流量 | 通过将多个SQL操作封装到一个存储过程中，可以减少客户端与服务器之间的通信次数，从而降低网络负载 |
| 促进代码重用 | 存储过程可以被多个应用程序或不同的用户重复使用，避免了代码冗余，提高了开发效率 |
| 简化复杂的操作 | 对于一些复杂的数据处理任务，如多表更新、事务处理等，可以编写存储过程来完成，使这些操作更加简单易懂 |
| 增强安全性 | 可以对存储过程设置权限，使得用户只能通过存储过程访问数据，而不能直接访问底层表。这样既保护了数据的安全性，也便于实现更细粒度的数据访问控制 |
七，触发器
触发器是与表有关的数据库对象，在insert/update/delete操作的前后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助在数据库端确保数据的完整性，日志记录，数据校验等操作。MySQL数据库提供两个别名OLD和NEW来引用触发器中发生变化的记录内容
MySQL目前只支持行级触发器，不支持语句级触发器
| 触发器类型 | NEW | OLD |
|---|---|---|
| INSERT类型触发器 | 表示将要或者已经新增的数据 | 无 |
| UPDATE类型触发器 | 表示将要或已将修改后的数据 | 表示修改之前的数据 |
| DELETE类型触发器 | 无 | 表示将要或已经删除的数据 |
-- 创建触发器
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW
BEGIN...
END;
-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER [schema.name.]触发器名称;
八，锁
MySQL中的锁按照锁的粒度划分
| 分类 | 描述 |
|---|---|
| 全局锁 | 锁定数据库中所有表 |
| 表级锁 | 每次操作锁住整张表 |
| 行级别锁 | 每次操作锁住对应的行数据 |
1.全局锁
全局锁就是对整个数据库实例加锁，加锁后整个数据库实例就处于只读状态，后续的DML语句的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞
-- 加全局锁
FLUSH TABLES WITH READ LOCK;
-- 解锁
UNLOCK TABLES;
2.表级锁
每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在MyISAM，InnoDB，BDB等存储引擎中。表级锁分类如下
（1）表锁
| 分类 | 描述 |
|---|---|
| 表共享读锁（read lock） | 本连接只能进行读操作不能进行写操作，其他连接只能进行读操作，写操作将会阻塞 |
| 表独占写锁（write lock） | 本连接能进行读和写操作，其他连接的读和写操作将会阻塞 |
-- 加锁
LOCK TABLES 表名1, 表名2, ... READ/WRITE;
-- 解锁（注：关闭客户端连接也能达到解锁的效果）
UNLOCK TABLES;
（2）元数据锁 （meta data lock，MDL）
元数据锁的加锁过程是数据库自动控制的，无需显式使用，在访问一张表的时候会自动加上。主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写操作，为了避免DDL和DML冲突，保证读写的正确性。当对一张表进行DML操作的时候，加MDL共享读锁，进行DDL操作的时候，加MDL排他写锁。元数据锁的加锁时机如下
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| LOCK TABLES 表名1, 表名2, … READ/WRITE | SHARED_READ_ONLY/SHARED_NO_READ_WRITE | |
| SELECT、SELECT … LOCK IN SHARE MODE | SHARED_READ | 与SHARED_READ和SHARED_WRITE兼容，与EXECLUSIVE互斥 |
| INSERT、UPDATE、DELETE、SELECT … FOR UPDATE | SHARED_WRITE | 与SHARED_READ和SHARED_WRITE兼容，与EXECLUSIVE互斥 |
| ALTER TABLE … | EXECLUSIVE | 与其他MDL都互斥 |
SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM performance_schema.metadata_locks;
（3）意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB引擎中引入了意向锁，使得表锁不用检查每行数据是否加锁，使得意向锁来减少表锁的检查
| 分类 | 兼容 |
|---|---|
| 意向共享锁（IS） | 与表共享读锁兼容，与表独占写锁互斥 |
| 意向排他锁（IX） | 与表共享读锁和表独占写锁互斥 |
意向锁之间不会互斥，意向锁会在加行锁时自动加上
3.行级锁
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率低，并发度最高，应用在InnoDB引擎中
InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁
-- 查看意向锁及行级锁的加锁情况
SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks;
（1）行锁的模式
| 模式 | 描述 |
|---|---|
| 行锁（Record Lock） | 锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC和RR隔离级别下都支持 |
| 间隙锁（Gap Lock） | 锁定索引的记录间隙（不包含记录），确保索引记录间隙不变，防止其他事务在这个间隙进行插入操作，产生幻读。在RR隔离级别下支持 |
| 临键锁（Next-Key Lock） | 行锁和间隙锁组合，同时锁住数据和数据前面的间隙，在RR隔离级别下支持 |
间隙锁唯一的目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁
默认情况下，InnoDB在RR事务隔离级别运行，InnoDB会使用临键锁（Next-Key Lock）进行搜索和引擎扫描，以防幻读
唯一索引上的等值匹配，对已存在的记录加锁时，将会自动优化为行锁
唯一索引上的等值匹配，对不存在的记录加锁时，将会自动优化为间隙锁
普通索引的等值匹配，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁
（2）行锁的分类
InnoDB实现了以下两种类型的行锁
| 分类 | 说明 |
|---|---|
| 共享锁（S） | 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即与共享锁兼容，与排他锁互斥 |
| 排他锁（X） | 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。即排斥共享锁和排他锁 |
（3）行锁的加锁时机
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSERT … | 排他锁 | 自动加锁 |
| UPDATE … | 排他锁 | 自动加锁 |
| DELETE … | 排他锁 | 自动加锁 |
| SELECT … | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
| SELECT … FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |