MySQL体系结构
连接层，服务层，引擎层，存储层
存储引擎
简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也被称表类型
创建表时指定存储引擎：
create table 表名( )engine=innodb [comment 表注释];查看当前数据库支持的存储引擎
show engines;
存储引擎特点
InnoDB
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，5.5后为默认引擎
特点：
- DML操作遵循ACID模型，支持事务
- 行级锁，提高并发访问性能
- 支持外键FOREIGN KEY约束，保证数据的完整性和正确性
文件：
- xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
- 参数：innodb_file_per_table
MyISAM
MySQL早期的默认存储引擎
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
Memory
Memory引擎的表数据存储在内存中，作为临时表或缓存使用
- 内存存放
- hash索引
存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
- InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
- MyISAM： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
索引
索引是用来高效获取数据的数据结构
优缺点
优点：
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点：
- 索引列占用大量空间
- 降低更新表的速度，增删改查效率低
索引结构
二叉树：顺序插入时，会形成一个链表，查询性能大大降低，层级较深，检索速度慢
B-Tree
多路平衡查找树
最大度数为5(5阶)，则每个节点最多存储4个key，5个指针，多者则向上分裂
B+Tree
所有的元素都会存储在叶子节点，形成一个单向列表
Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位，然后存储在hash表中
- 只能用于对等比较，不支持范围查询
- 无法利用索引完成排序操作
- 查询效率高
索引分类
- 主键索引：针对表中主键创建的索引，只能有一个，PRIMARY
- 唯一索引：避免同一个表中数据列中值重复，UNIQUE
- 常规索引：快速定位特定数据
- 全文索引：全文索引查找的是文本中的关键词，而不是比较索引的值，FULLTEXT
InnoDB：回表查询
- 聚集索引：将数据存储与索引放到一起，索引结构的叶子节点保存了行数据，有且一个
- 二级索引：将数据与索引分开存储，索引结构的叶子节点关联对应的主键
索引语法
创建索引：
create [unique|fulltext] index index_name on table_name (index_col_name,...);查看索引：
show index from table_name;删除索引：
drop index index_name on table_name;
性能分析
SQL执行频率
show [session|global] status可以提供服务器状态信息
show [session|global] status like 'com___';可以查看当前数据库的增删改查的访问频次
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（etc/my.cnf）中配置
show variables like 'slow_query_log'; 查询慢查询日志情况#开启MySQL慢日志查询开关 slow_query_log=1 #设置慢查询时间 long_query_time=2
profile详情
show profiles能够了解耗费之间去向。通过have_profiling参数，能够看到当前MySQL是否支持profile操作select @@have_profiling；默认关闭，开启方式
select @@profiling; set profiling=1;#查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;
explain执行计划
explain或者DESC命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
直接在语句前加上关键字explain或desc explain select 字段列表 from 表名 where 条件;
- id：表示查询中执行select子句或者操作表的顺序（id相同，从上到下，不同越大先执行）
- select_type：
- simple：简单表，即不用表连接和子查询
- primary：主查询，即外层的查询
- union：union联合查询中的第二个或者后面的语句
- subquery：select/where之后包含了子查询语句
- type：表示连接类型，性能由好到差：null，system，const，eq_ref，ref，range，index，all
- possible_key：显示可能应用在这张表上的索引，一个或多个
- key：实际使用的索引，如果为null，则表示没有使用索引
- key_len：表示索引中使用的字节数，该值为索引字段中最大可能长度，并非实际长度，越短越好
- rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，不准确
- filtered：表示返回结果的行数占需读取行数的百分比，filtered越大越好
使用规则
最左前缀法则
如果索引了多列（联合索引，要遵循最左前缀法则。
根据联合索引执行计划
最左前缀法则指查询从索引的最左列开始，并且不跳过索引中的列。如果跳过，后面的索引将失效
索引失效
- 索引列运算操作
- 字符串无引号
- 头部模糊匹配（尾部模糊匹配不会失效）
数据分布影响
如果MySQL评估使用索引比全表扫描慢，则不使用索引
SQL提示
在SQL语句中加入人为提示来优化操作、
select * from xxx use|ignore|force index(index_xxx) where ...;
- use index：建议
- ignore index：忽略
- force index：强制
覆盖索引
尽量使用覆盖索引：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少
select *
前缀索引
对于字符串字段类型，索引很长，浪费磁盘IO，影响效率
取字符串前缀建立索引，提高效率
create index idx_xxx on table_name (字段列表(n));
n代表选择字段列表的前n个字符建立索引
可以根据索引的选择性决定前缀长度，而选择性指不重复的索引值和数据表的记录总数的总值
唯一索引选择性为1,性能最高
select count(distinct exxx)|count(*) from tb_xxx; select count(distinct substring(exxx,1,5))|count(*) from tb_xxx;
单列索引和联合索引
多个查询条件，建议建立联合索引
SQL优化
插入数据
insert优化：
- 批量插入
- 手动提交事务
- 主键顺序插入
大批量插入数据：
可以使用MySQL数据库提供的
load指令进行插入#客户端连接服务端时，加上参数 --local-infile mysql --local-infile -u root -p #设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile=1 #执行load指令将准备好的数据加载到表结构中 load data local infile '/root/sql' into table 'xxx' fields terminated by ',' lines terminated by '\n';
主键优化
- 尽量降低主键长度
- 插入数据时，尽量选择顺序插入，选择主键自增
auto_increment- 尽量UID或身份证号做主键
- 避免修改主键
排序order by优化
- Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫Filesort排序
- Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
group by优化
建立联合索引后可以利用索引进行分组查询，提高效率
limit分页优化
通过覆盖索引或子查询将大数据limit查询某些数据的排序效率大大提高
count优化
InnoDB是累计计数
优化方式：自己计数
- count(*)：总记录数，按行累加
- count(主键)：总记录数，取主键id返回服务层，直接进行累加
- count(字段)：非null记录数
- count(1)：总记录数，对每一层置1，进行累加
update优化
没有索引，行锁将升级为表锁
视图
视图：虚拟存在的表，行和列数据来自自定义视图的查询中使用的表，并且是在使用视图时动态生成
视图保存了查询的SQL逻辑，不保留查询结果
类似额外存储数据库自选内容，方便快捷
基本用法
创建视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]查询视图
查看创建视图语句
show create view 视图名称;
查看视图数据
select * from 视图名称...;修改视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with|cascaded|local|check option];
alter view 视图名称[(列名列表)] as select语句 [with|cascaded|local|check option];删除视图
drop view [if exists] 视图名称 [,视图名称]...检查选项
使用
with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，以使其符合视图的定义。MySQL允许基于一个视图创建另一个视图。
为了确定检查范围，mysql提供了两个选项：
- cascaded(默认)：级联所有层级的视图限制条件，共享限制条件
- local：仅仅向下级联限制条件
更新及作用
视图更新
视图中的行与基础表中的行必须存在一对一的关系。
下列情况视图不能更新：
- 聚合函数或窗口函数（sum() , min() , max() , count()等）
- distinct
- group by
- having
- union或者union all
作用
- 简单：简化用户对数据的理解，简化操作。常用查询可被定义为视图
- 安全：数据库授权不能授权到特定行和列，通过视图用户只能查询和修改他们所能见到的数据
- 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响
存储过程
事先经过编译并存储子啊数据库中的一段SQL语句集合，减少数据在数据库和应用服务器之间的传输，提高效率。
基本用法
创建
create procedure 存储过程名称([参数列表])
begin
--sql语句
end;调用
call 名称([参数]);查看
查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema='xxx';
查询某个存储过程的定义
show create procedure 存储过程名称删除
drop procedure [if exists] 存储过程名称;在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符
delimiter 结束符号;变量
系统变量
系统变量是MySQL服务器提供，非用户定义，属于服务器层面。分为全局变量(Global)、会话变量(Session)
查看系统变量
show [session|global] variables; 查看所有系统变量 show [session|global] variables like '...'; 模糊匹配系统变量 select @@[session|global] 系统变量名; 查看指定变量
设置系统变量
set [session|global] 系统变量名=值; set @@[session|global] 系统变量名=值;
用户定义变量
用户定义变量不用提前声明，直接‘’@变量名‘’使用，其作用域为当前连接
赋值
set @var_name=expr[,@var_name=expr]...; set @var_name:=expr[,@var_name:=expr]...;select @var_name:=expr[,@var_name:=expr]...; select 字段名 into @var_name from 表名;
使用
select @var_name;
局部变量
局部变量访问之前，需要
declare声明。可作为存储过程内的局部变量和输入参数，范围在begin...end内
声明
declare 变量名 变量类型[default 默认值];
赋值
set 变量名=值; set 变量名:=值; select 字段名 into 变量名 from ...;
流程组成部分
if
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;参数
| 类型 | 含义 | 备注 |
|---|---|---|
| in | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| out | 该类参数作为输出，也就是该参数可以作为返回值 | |
| inout | 既可以作为输入参数，也可以作为输出参数 |
create procedure 存储过程名称([in|out|inout 参数名 参数类型])
begin
...
end;case
语法1
case case_value when when_value1 then ... [else ...] end case;语法2
case when xxx then ... [else ...] end case;
while
while 条件 do
...
end while;repeat
满足条件退出循环
先执行一次循环，判断逻辑是否满足，满足则退出，不满足继续循环
repeat
...
until 条件
end repeat;loop
loop实现简单的循环，可以实现死循环
[begin_label:] loop SQL逻辑... end loop [end_label];
- leave：配合循环使用，退出循环
- iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
游标cursor
游标用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。
游标使用包括游标的声明、open、fetch和close
声明游标
declare 游标名称 cursor for 查询语句;打开游标
open 游标名称；获取游标记录
fetch 游标名称 into 变量;关闭游标
close 游标名称;
条件处理程序
条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
declare handler_action handler for condition_value ... statement;
handler_action
continue:继续执行当前程序
exit:终止执行当前程序
condition_value
sqlstate sqlstate_value:状态码，如02000
sqlwarning:所有以01开头的sqlstate代码的简写
not found:所有以02开头的sqlstate代码的简写
sqlexception:所有没被sqlwarning或not found捕获的sqlstate代码的简写存储函数
存储函数是有返回值的存储过程，存储函数的类型只能是in类型
create function 存储函数名称([参数列表])
return type [characteristic...]
begin
--SQL语句
return...;
end;characteristic说明：
deterministic：相同的输入参数总是产生相同的结果no sql：不包括SQL语句reads sql data：包含读取数据的语句，但不包含写入数据的语句
触发器(log)
触发器是与表有关的数据库对象，指在增删改前后，触发并执行触发器中定义的SQL语句集合。
触发器可协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容。当前触发器只支持行级触发，不支持语句触发
创建
create trigger trigger_name
before|after insert|update|delete
on tbline_name for each row --行级触发器
begin
tigger_stmt
end;查看
show triggers;删除
drop trigger [schema_name.]trigger_name; --未指定schema_name，默认当前数据库锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
保证数据并发访问的一致性、有效性。
- 全局锁：锁定数据库中所有表
- 表级锁：每次操作锁住整张表
- 行级锁：每次操作锁住对应的行数据
全局锁
全局锁将整个数据库实例加锁，加锁后处于只读状态。
典型的应用场景：全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性
加全局锁
flush tables with read lock;
数据备份
mysqldump -h主机地址 -uroot -pxxxx 数据库名称 > 备份地址.sql
解锁
unlock tables;表级锁
表级锁，每次操作锁住整张表，发生锁冲突的概率最高，并发度较低
- 表锁
- 元数据锁
- 意向锁
表锁
- 表共享读锁（read lock）只能读
- 表独占写锁（write lock）读写都阻塞
语法：
- 加锁：
lock tables 表名... read|write- 释放锁：
unlock tables / 客户端断开连接
元数据锁(MDL)
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表时会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作
意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
- 意向共享锁：由语句
select ... lock in share mode添加
- 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
- 意向排他锁：由
insert、update、delete、select ... for update添加
- 与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥
行级锁
行级锁，每次操作锁住对应的行数据。锁定粒度小，发生锁冲突的概率低，并发度最高。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁
- 行锁（record lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete
- 间隙锁（gap lock）：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读
- 临键锁（next-key lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙gap
行锁
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
- 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
- 增删改自动增加 排他锁
- select正常查询不加任何锁
- select…lock in share mode 添加共享锁
- select … for update 添加排他锁
间隙锁和临键锁
默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，防止幻读
- 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
- 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
- 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止
间隙锁唯一目的是防止其他事务插入间隙。
间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁
InnoDB引擎
逻辑存储结构
- 表空间（ibd文件）：一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
- 段，分为数据段、索引段、回滚段，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段为非叶子节点。段用来管理多个Extent(区)。
- 区，表空间的单元结构，每个区大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页
- 页，InnoDB存储引擎磁盘管理的最小单元，默认16k。保证连续性，每次申请4-5个区
- 行，数据按行存放
事务原理
redo log
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲（redo log buffer)以及重做日志文件（(redo log file) ,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
undo log
回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC（多版本并发控制）。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment 回滚段中，内部包含1024个undo log segment。
MVVC
基本概念
当前读
- 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select… lock in share mode(共享锁)，select .. for update、update、insert、delete(排他锁)都是一种当前读。
快照读
- 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed：每次select，都生成一个快照读。
- Repeatable Read：开启事务后第一个select语句才是快照读的地方。
- Serializable：快照读会退化为当前读。
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现
MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
隐藏字段
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段 |
undo log
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undolog日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
undo log版本链
不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
readview
readview（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
四个核心字段：
字段 含义 m_ids 当前活跃的事务id集合 min_trx_id 最小活跃事务id max_trx_id 预分配事务id，当前最大事务id+1 creator_trx_id readview创建者的事务id
系统数据库
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
可以留下你的信息哦(去Github_issues)😀😀😀
GitHub Issues