mysql数据库

存储引擎

MySQL体系结构

连接层：最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入每个用户端验证它所具有的操作权限。

服务层：第二层架构，主要完成大多数的核心服务功能，如SQL接口并完成缓存的查询、SQL的分析和优化、部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取适合的存储引擎。

存储层：主要是将数据存储在文件系统之上，并完成于存储引擎的交互。

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。

-- 查询建表语句 默认存储引擎 InnoDB
show create table account;
-- 查看当前数据库支持的存储引擎；
show engines;

存储引擎特点

InnoDB

介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

特点

1. DML操作遵循ACID模型，支持事务；
2. 行级锁，提高并发访问性能；
3. 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

文件

xxx.ibd:xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table 决定多张表共用一个共享表空间文件还是每一个表都对应一个表空间文件，MySQL8.0当中这个是开启的，那么代表着每一张表都对应一张表空间

在C:\ProgramData\MySQL\MySQL Server 8.0\Data\itheima路径中打开cmd 使用ibd2sdi account.ibd查看表空间文件

逻辑存储结构

page是磁盘操作的最小单元，大小是固定的16K

extent大小是固定的1M，包含64个page

MyISAM

介绍

MyISAM是MySQL早起的默认存储引擎

特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件

xxx.sdi:存储表结构信息

xxx.MYD:存储数据

xxx.MYI:存储索引

Memory

介绍

Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些报表作为临时表或缓存使用

特点

内存存放

hash索引（默认）

文件

xxx.sdi:存储结构信息

存储引擎选择

根据应用系统的特点选择好合适的存储引擎。

InnoDB（绝大部分选择）：是MySQL的默认存储引擎，支持事务、外键、行锁。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完成性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。（一般被NoSql数据库MongoDB代替）

Memory：将所有的数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory的缺陷就是对表的大小有显示，太大的表无法缓存在内存中，而且无法保证数据的安全性。（一般被NoSql数据库Redis代替）

索引

索引概述

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：如果没有特别指明索引的结构，一般是B+树结构

二叉树

B-Tree（多路平衡查找树）

B+Tree

非叶子节点作为索引，叶子结点存放数据，所有的数据都会在叶子节点中，叶子节点形成一个单向链表

MySQL中的B+Tree

所有的数据都会在叶子节点中，叶子节点形成一个双向链表

每一个节点都是存储在数据块当中的，也叫页，页中不存放数据时可存放的key和指针也就更多，想通数据量的情况下，层级也就更少

hash

为什么InnoDB存储引擎选择使用B+Tree索引结构？

• 相对于二叉树，层级更少，搜索效率更高；
• 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对于Hash，B+Tree支持范围匹配和排序操作

索引分类

以下SQL语句的哪个效率更高？

第一条语句直接到聚集索引中进行对比，直接定位到id=10的数据拿回这一行的数据

第二条语句需要先去二级索引中查找name=arm的数据拿到id主键在进行回表查询，根据聚集索引查询

InnoDB主键索引的B+Tree高度为多高？

假设一行数据大小为1k，一页大小为16k，那么一页可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用8个字节。1k=1024个字节

在高度为2的情况下

非叶子节点可以存储：n*8+（n+1）*6=16*1024，n=1170，n当前节点存储key的数量，n+1为指针的数量

1171*16=18735

高度为3

1171*1171*16=21993929

索引语法

-- 查看user表索引
show index from tb_user;

-- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值,是非空,且唯一的,为该字段创建唯一索引
create unique index idx_user_phone on tb_user(phone);
-- 为profession、age、status创建联合索引 联合索引中字段的顺序是有讲究的
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- 为email建立合适的索引
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

SQL性能分析

要做SQL优化就先需要定位哪一类SQL需要优化，主要优化的是查询语句

查看SQL执行频率

# 查看SQL执行频率
-- 查看当前数据库各类语句访问的频次 下划线代表字段个数
show global status like 'Com_______';

慢查询日志

慢查询日志介绍

慢查询日志记录了所有执行时间超过指定参数的SQL语句。（参数：long_query_time，单位秒，默认10秒）

开启慢查询日志

在MySQL当中慢查询日志是默认不开启的，需要手动开启。

开启方法：在MySQL的配置文件（vi /etc/my.cnf）中添加以下信息

#开启MySQL慢查询日志
slow_query_log=1;
#设置慢查询时间，超过则记录
long_query_time=2

配置完毕之后，需要重启MySQL服务器（systemctl restart mysqld）才能生效，同时会生成慢查询日志文件（cat /var/lib/mysql/localhost-slow.log）

-- 查看慢查询日志是否开启
show variables like 'slow_query_log';

监控慢查询日志

tail -f localhost-slow.log

show profiles

# profile详情
-- 查看MySQL是否支持profile操作
select @@have_profiling;

-- 默认profiling是关闭的，手动开启profiling
set profiling = 1;

执行一系列业务SQL，通过以下指令查看指令的执行耗时

-- 执行业务SQL
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';

-- 查看每一条SQL语句耗时情况
show profiles ;

-- 查看指定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执行计划各字段的含义：

id：select查询的序列号，表示查询中执行select子句或是操作表的顺序（id想通，执行顺序从上到下；id不同执行顺序从大到小）

explain select s.*,c.* from student s,course c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;

# 查询选修了MySQL课程的学生（子查询）
# 1.查询MySQL课程id
select id from course where name = 'MySQL';
# 2.通过MySQL的id在中间表中查询选修了MySQL学生的id
select studentid from student_course sc where sc.courseid = 3;
# 3.通过学生id查询选修了MySQL学生的名字
select * from student where id in (1,2);
# 组装子查询
explain select * from student s where s.id in (select studentid from student_course sc where sc.courseid in (select id from course c where c.name = 'MySQL'));

select_type：表示select的类型，常见的取值有simple（简单表，即不用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等

type：表示连接类型，性能由好到差的连接类型为null（不查询任何表）>system>const（根据id查询）>eq_ref>ref（添加索引前提下，根据非唯一性的索引查询）>range（范围查询）>index（用了索引但是会对索引扫描）>all

possible_key：显示可能应用在这张表上的索引，一个或者多个

key：实际使用的索引，如果为null，则没有使用索引

key_len：表示索引使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

rows：MySQL人为必须要执行查询的行数，在InnoDB引擎的表中是一个估值

filtered：表示返回结果的行数站读取行数的百分比

索引使用规则

索引失效

最左前缀法则

联合索引要遵循最左前缀法则。

左前缀法则是指查询从索引的最左列开始，并且不超过索引中的列。

如果跳过某一列，索引将部分失效（后面的字段索引失效）。

也就是在使用联合索引时要根据创建联合索引时的顺序进行查询，如果跳过某一列则后面字段索引将会失效（如果最左边的列跳过则所有的字段索引都会失效），值得注意的是查询时字段的顺序与是否使用索引无关

# 最左前缀法则
-- 联合索引生效 索引长度为54
explain select * from tb_user where profession='软件工程' and age=31 and status='0';
explain select * from tb_user where age=31 and profession='软件工程' and status='0';
-- 联合索引生效 索引长度为49
explain select * from tb_user where profession='软件工程' and age=31;
-- 联合索引生效 索引长度为47
explain select * from tb_user where profession='软件工程';
-- 部分联合索引生效，age列缺失，后面的列索引失效，索引长度为47
explain select * from tb_user where profession='软件工程' and status='0';
-- 索引失效，最左的profession列缺失
explain select * from tb_user where age=31 and status='0';
-- 索引失效，最左的profession列缺失
explain select * from tb_user where status='0';

范围查询

联合索引中，某一字段出现了范围查询（>,<不包括>=或<=），范围查询右侧的列索引失效

# 范围查询
-- 部分联合索引生效，age字段使用了范围查询，后面的列索引失效 索引长度49
explain select * from tb_user where profession='软件工程' and age>30 and status='0';
-- 联合索引生效，>=不算范围查询，索引长度54
explain select * from tb_user where profession='软件工程' and age>=30 and status='0';

索引列运算

不要再索引列上进行运算操作，否则索引将会失效。

# 索引列运算
-- 索引生效 索引长度为46
explain select * from tb_user where phone = '17799990000';
-- 索引失效 索引列使用了运算符
explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

# 字符串不加引号
-- 索引失效 字符串列未加引号
explain select * from tb_user where phone = 17799990000;
-- 部分联合索引生效，status列未加引号，部分索引失效，索引长度为54
explain select * from tb_user where age=31 and profession='软件工程' and status=0;

模糊查询

如果仅仅是尾部匹配模糊，索引不会失效。

如果是头部匹配模糊，索引失效。

# 模糊查询
-- 联合索引生效 索引长度为47
explain select * from tb_user where profession like '软件%';
-- 索引失效 头部模糊匹配索引失效
explain select * from tb_user where profession like '%工程';

or连接的条件

用or分割开的条件，除非两侧条件均有索引，涉及的索引都不会用到。

# or连接的条件
-- 索引失效 age没有索引，全部索引失效
explain select * from tb_user where id=10 or age=23;
explain select * from tb_user where phone = '177999900000' or age=23;
-- 为age列创建索引 上方语句索引生效
create index idx_user_age on tb_user(age);

数据分布影响

如果MySQL评估使用索引比全表更慢，则不适用索引

is null 和 is not null的索引的生效情况取决于数据分布，如果需要查询的数据只占一小部分则会使用索引，反之不会使用索引

# 数据分布影响
-- 索引生效 类型range
explain select * from tb_user where phone >= '17799990020';
-- 索引失效 类型all
explain select * from tb_user where phone >= '17799990000';
-- 索引失效 类型all
explain select * from tb_user where phone >= '17799990010';
-- 索引生效 类型range
explain select * from tb_user where phone >= '17799990013';
-- is null 和 is not null索引生效情况
-- 联合索引生效 长度47
explain select * from tb_user where profession is null;
-- 联合索引失效
explain select * from tb_user where profession is not null;
-- 将user表的profession字段全部设置为null
update tb_user set profession = null;
-- 联合索引失效
explain select * from tb_user where profession is null;
-- 联合索引生效 长度47
explain select * from tb_user where profession is not null;

SQL提示

SQL提示是优化数据库的一个重要手段，是在SQL语句中加入一些人为的提示来达到优化的操作的目的。

# SQL提示
-- 为profession列创建索引
create index idx_user_pro on tb_user(profession);
-- 联合索引生效 长度47
explain select * from tb_user where profession = '软件工程';
-- 建议数据库使用idx_user_pro单列索引 索引生效 长度47
explain select * from tb_user use index (idx_user_pro) where profession = '软件工程';
-- 告诉数据库不使用idx_user_pro_age_sta联合索引 索引生效 长度47
explain select * from tb_user ignore index (idx_user_pro_age_sta) where profession = '软件工程';
-- 告诉数据库必须使用idx_user_pro_age_sta联合索引 索引生效 长度47
explain select * from tb_user force index (idx_user_pro_age_sta) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *，因为使用select *极易出现回表查询

覆盖索引指

条件中的列需要使用索引，返回的列包含在此索引中

查询时使用了索引并且需要返回的列，在该索引中已经全部能够找到

# 覆盖索引
-- 索引生效 额外信息Using where; Using index，需要查找的数据都能在索引列中找到，不需要回表查询，所以性能高
explain select id,profession,age,status from tb_user where profession='软件工程' and age=31 and status='0';
-- 索引生效 额外信息Using index condition，需要查找的数据有的在索引列中找不到，需要回表查询，所以性能低
explain select id,profession,age,status,name from tb_user where profession='软件工程' and age=31 and status='0';

前缀索引

当前字段类型为字符串（varchar，text等），有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀简历索引，这样可以打打节约索引空间，从而提高索引效率。

语法：create index idx_xxx on table_name(column(n));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

查询流程：

# 前缀索引
-- 求总记录数 24
select * from tb_user;
select count(*) from tb_user;
-- 求email字段不重复的总数 24
select count(distinct email) from tb_user;
-- 求email字段的选择性 1.000
select count(distinct email)/count(*) from tb_user;
-- 截取email前10个字符选择性为1。000
select count(distinct substring(email,1,10))/count(*) from tb_user;
-- 截取email前9个字符选择性为0.9583
select count(distinct substring(email,1,9))/count(*) from tb_user;
-- 截取email前5个字符选择性为0.9583
select count(distinct substring(email,1,5))/count(*) from tb_user;
-- 截取email前4个字符选择性为0.9167
select count(distinct substring(email,1,4))/count(*) from tb_user;
-- 为email字段创建前缀索引 email前缀取5
create index idx_email_5 on tb_user(email(5));
show index from tb_user;
explain select id,email from tb_user where email = 'lvbu666@163.com';

单列索引和联合索引

单例索引：指一个索引只包含单列

联合索引：指一个索引包含多个列，创建时列的顺序对于索引的效率也有影响

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建立使用联合索引而不是单列索引

# 单列索引和联合索引
show index from tb_user;
-- 只有phone和name的单列情况下 phone索引生效 name索引失效 这样就会回表查询
explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';
-- 为phone，name创建唯一联合索引
create unique index idx_user_pho_name on tb_user(phone,name);

索引设计原则

1. 针对数据量（几百万）较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点建立前缀索引。
5. 尽量使用联合索引减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引不是多多益善，索引越多，维护索引结构的代价也就会越大，会影响增删改的效率。
7. 如果索引列不能存储null值，请在创建表是使用not null约束它。当优化器知道每列是否包含null值是，它可以更好的确定哪个索引最有效的用于查询。

SQL优化

插入数据

insert优化

批量插入

手动提交事务

主键顺序插入

大批量插入数据

配置文件（vi /etc/my.cnf）

# 开启加载本地数据功能
local_infile=1

# 重启MySQL服务器
systemctl restart mysqld

# 在连接客户端是添加--local-infile=1选项
mysql --local-infile=1 -u root -p

#加载本地SQL文件
load data local infile '/root/sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data local infile '/root/sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data local infile '/root/sql/tb_sku3.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data local infile '/root/sql/tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data local infile '/root/sql/tb_sku5.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

或者直接使用可以化工局datagrip导入sql文件

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）

一张表默认主键是聚集索引，索引下的数据就是行数据。

页分裂

页是InnoDB存储引擎中磁盘操作的最小单位，大小为16k，页可以为空，也可以填充一半，也可以填充100%。每个页包含2-N行数据（如果一行数据过大就会产生行溢出现象），根据主键排列。

主键乱序插入时，如果一页空间不足以插入一行数据，那么会请求一个新页，然后检索到50%的位置将数据移动到新页中再将数据插入到新页中，从而造成页分裂

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到merge_threshold（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

merge_threshold：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

• 在满足业务需求的情况下，尽量降低主键长度。--聚合索引只有一个，但是二级索引有很多，其中的叶子结点的数据就是主键长度，当主键长度过长时会浪费磁盘的io，降低查询效率
• 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。
• 尽量不要使用UUID做主键，或者是其他自然主键，如身份证号。--这类主键一般为无序且长度过大，插入数据时会降低性能
• 业务操作时，避免对主键的修改。--每次修改主键时需要修改b+树的存储结构，及其不方便

order by优化

using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

using index：通过有序索引，顺序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高。前提是使用了覆盖索引

order by遵循的规则

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（asc/desc）
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认大小256k）

# order by排序优化
show index from tb_user;
drop index idx_user_age on tb_user;
drop index idx_user_pho on tb_user;
-- 没有索引的情况下，Using filesort
-- 有索引的情况下，Using index
explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age,phone;
-- 有索引情况下，Backward index scan(反向扫描索引); Using index
explain select id,age,phone from tb_user order by age desc ,phone desc ;
-- 有索引的情况下，Using index; Using filesort，phone列的索引失效 未遵守最左前缀法则
explain select id,age,phone from tb_user order by phone,age;
-- 有索引的情况下，Using index; Using filesort，phone列的索引失效 需要额外phone排序
-- 在指定了age升序，phone倒序的索引情况下，Using index
explain select id,age,phone from tb_user order by age asc ,phone desc ;
-- 建立age和phone的联合索引
create index idx_user_age_pho on tb_user(age,phone);
-- 建立age升序和phone倒序的联合索引
create index idx_user_age_pho_ad on tb_user(age asc ,phone desc );

group by优化

需要满足最左前缀法则

# group by优化
show index from tb_user;
drop index idx_user_pro_age_sta on tb_user;
drop index idx_user_age_pho on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_pho_ad on tb_user;
-- 没有索引时，Using temporary
-- 有索引时，Using index
explain select profession,count(*) from tb_user group by profession;
-- 创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- Using index; Using temporary
explain select age,count(*) from tb_user group by age;
-- Using index
explain select profession,age,count(*) from tb_user group by profession,age;
-- Using index
explain select age,count(*) from tb_user where profession = '软件工程' group by age;

limit优化

在查询大数据量时例如limit 2000000,10，此时MySQL排序前2000010条记录，仅仅返回2000000-2000010的记录，其他记录被丢弃，查询排序的代价非常大。

优化思路：通过覆盖索引和子查询的方式优化

# limit优化
select count(*) from tb_user;
-- 查询50w后的10个数据效率就会底下了
select * from tb_user limit 500000,10;
-- 覆盖索引
select id from itheima.tb_user limit 500000,10;
-- 子查询 目前MySQL时不支持子查询中使用limit这个语法的
select * from itheima.tb_user s where id in (select id from itheima.tb_user limit 500000,10);
-- 覆盖索引和多表联查的方式解决这个问题
select s.* from itheima.tb_user s ,(select id from itheima.tb_user limit 500000,10) a where s.id = a.id ;

count优化

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候会直接返回总数，因此效率很高。
• InnoDB引擎就麻烦了，他执行count（*）时需要一行一行的从引擎里读出来，然后积累技术，效率时比较低下的。
• 优化思路：自己计数，插入时记录，删除时记录

count的几种用法

count()时一个聚合函数，对于返回的结果集，一行行的判断，如果count函数不是null，累计就+1，否则不加，最后返回累计值。

按照效率排序：count(字段)<count(主键)<count(1)≈count(*)

count(*)

InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值。服务层直接安航进行累加

count(主键)

InnoDB迎请会遍历整张表，把每一行的主键id值都取出来，返回个服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）

count(字段)

没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层直接进行累加。

count(1)

InnoDB引擎遍历整张表，但是不取值。服务层对返回的每一行，放一个数字1进去直接按行进行累加。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，因此在执行update语句时，必须根据索引字段进行修改

-- 无索引对应表锁 有索引对应行锁
事务1 update course set name='java2' where name = 'java1';
事务2 update course set name='java2' where id=1;--表锁被锁住

视图/存储过程/触发器

视图

视图介绍

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图不保存数据，只保存了查询的SQL逻辑。所以在创建视图时主要工作是创建SQL语句上。

视图语法

create [or replace] view viewname [(column)] as selectSQL [ with [ cascaded | local ] check option]

-- 创建视图
create or replace view view_stu as select id,name from student where id<=10;

-- 查询视图
show create view view_stu;
select * from view_stu;
select * from view_stu where id < 3;

-- 修改视图
create or replace view view_stu as select id,name,no from student where id<=10;
alter view view_stu as select id,name,no from student where id<=10;

-- 删除视图
drop view if exists view_stu;

视图的检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除。以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：cascaded和local，默认值为cascaded

# cascade
# 添加了with cascaded check option级联检查选项之后
# 系统会递归检查目标对象及其依赖对象确保所有对象都通过了检查
# 不管其依赖对象是有添加了检查选项
-- v1视图没有定义检查选项，所以不检查v1的id <=20;条件
select * from v1;
create or replace view v1 as select id,name from student where id <=20;
-- 插入成功
insert into v1 values (30,'111');

-- v2视图会检查条件：id>=10&&id<=20 依赖v1视图，级联v1视图条件
select * from v2;
create or replace view v2 as select id,name from v1 where id >=10 with cascaded check option;
-- 插入失败 不满足v1条件id<=20
insert into v2 values (33,'111');
-- 插入成功
insert into v2 values (15,'111');

-- v3视图不会检查选项 但v3视图时基于v2视图建立的 插入数据时会检查v2视图条件 v2视图又依赖v1视图
-- 所以v3视图检查条件：id>=10&&id<=20
create or replace view v3 as select id,name from v2 where id <=15;
-- 插入失败 不满足检查条件id>=10&&id<=20
insert into v3 values (6,'111');
-- 插入成功
insert into v3 values (11,'111');
-- 插入成功
insert into v3 values (17,'111');
-- 插入失败 不满足检查条件id>=10&&id<=20
insert into v3 values (28,'111');

视图的更新

要使视图可更新，视图中的行于基表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或者窗口函数sum(),min(),max(),count()
2. distinct
3. group by
4. having
5. union或者union all

视图的作用

简单：视图不仅可以简化用户对数据的理解，也可以简化操作。那些被经常使用的查询可以被定义为视图，从而是的用户不必为以后得操作每次指定全部的条件。

安全：数据库可以授权，但是不能授权到数据库特定行和特定列上，最小授权单位是表。通过视图用户只能查询和修改他们所能见到的数据。

数据独立：视图可以屏蔽基表的结构改变带来的影响。例如基表的name字段改成了stuName，只需要修改视图的查询语句即可。

视图具体操作

为了保证数据库表的安全性，开发人员在操作user表时，只能看到用户的基本字段，屏蔽手机号和邮箱两个字段。

select * from tb_user;
create view user_v as select id,name,profession,age,gender,status,createtime from tb_user;
select * from user_v;

查询每个学生所选修的课程，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

select s.id,s.name,c.name from student s,course c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;
create or replace view stu_cou_v as select s.no stundet_no,s.name student_name,c.name course_name from student s,course c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;
select * from stu_cou_v;

存储过程

存储过程介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

存储过程特点

• 封装与重用
• 可以接受参数，也可以返回数据
• 减少网络交互，提升访问效率

存储过程语法

-- 创建存储过程
-- 在命令行执行该语句时 会因为分号导致系统误判 提前结束语句
-- 可以使用delimiter $$指定$$为结束符
create procedure p1()
begin
    select count(*) from student;
end;
-- 调用存储过程
call p1();
-- 查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='cast';
show create procedure p1;
-- 删除存储过程
drop procedure if exists p1;

存储过程变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面的。分为全局变量（global）、会话变量（session）

# 系统变量
-- 查看系统变量 默认session级别
show session variables ;
show global variables ;
show global variables like 'auto%';
-- 准确查看某个系统变量的值
select @@global.autocommit;
select @@session.autocommit;

-- 设置系统变量
-- 虽然设置了全局级别 但是重启MySQL服务之后参数会复原
-- 想要永久改变系统变量参数需要在/etc/my.cnf中配置
set global autocommit = 0;
set session autocommit = 0;
-- 系统自动事务提交被关闭 需要手动提交
insert into course values (5,'Oracle');
commit ;

用户变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接'@变量名'使用就可以。其作用于为当前连接。

# 用户变量
-- set赋值 = 和 := 都可以但是建议使用:= 因为MySQL中相等运算符是=，赋值运算符也是=，便于区分使用:=
set @myname = 'itcast';
set @myage := 10;
set @mygender = '男',@myhobby := 'java';
-- select赋值 使用:=赋值
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myage,@myname,@mygender,@myhobby,@mycolor,@mycount;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin end块。

# 局部变量
create procedure p2()
begin
    -- 声明 declare
    declare stu_count int default 0;
    -- 赋值
    set stu_count := 100;
    select count(*) into stu_count from tb_user;
    select stu_count;
end;

call p2();

if语句

#if语句
create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result := '优秀';
    elseif score>=60 && score<85 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;
call p3();

存储过程参数

1.根据传入的参数score，判定当前分数对应的分数等级并返回

create procedure p4(in score int,out result varchar(10))
begin
    if score>=85 then
        set result := '优秀';
    elseif score>=60 && score<85 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;
-- 输出的参数需要用局部变量进行接受
call p4(10,@result);
select @result;

2、将传入的200分制的分数，进行换算，换算成百分制，然后返回

create procedure p5(inout score double)
begin
    set score := score*0.5;
end;
-- inout需要提前给局部变量复制
set @score=728.2;
call p5(@score);
select @score;

case语句

根据传入的月份判断月份所属的季节，要求使用case结构

# case语句
drop procedure p6;
call p6(10);
select @season;
create procedure p6(in month int)
begin
    declare season varchar(10);
    case
        when month>=1&&month<=3 then set season := '春天';
        when month>=4&&month<=6 then set season := '夏天';
        when month>=7&&month<=9 then set season := '秋天';
        when month>=10&&month<=12 then set season := '冬天';
        else set season := '非法参数!';
    end case;
    select concat('您输入的月份为:',month,',所属季节为:',season);
end;

while语句

while是有条件的循环控制语句。满足条件后在执行循环体中的sql语句。

计算从1累加到n的值，n为传入的参数值。

create procedure p7(in n int)
begin
    declare sum int default 0;
    
    while n>=0 do
     set sum := n+sum;
     set n:=n-1;
    end while;
    
    select sum;
end;

call p7(100);

repeat语句

repeat是有条件的循环控制语句，当满足条件的时候退出循环。如果不满足则继续下一次循环

计算从1累加到n的值，n为传入的参数值。

create procedure p8(in n int)
begin
    declare sum int default 0;

    repeat
        set sum := n+sum;
        set n:=n-1;
    until n<=0 end repeat;

    select sum;
end;

call p8(100);

loop语句

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以搭配以下两条语句使用：

• leave:配置循环使用，退出循环。
• iterate:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

1. 计算从1累加到n的值，n为传入的参数值。

create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop
        
        if n<=0 then leave sum;    
        end if;
        
        set total := n+total;
        set n:=n-1;
        
    end loop sum;
    select total;
end;
call p9(100);

1. 计算从1到n偶数累加的值，n为传入的参数值。

create procedure p10(in n int)
begin
    declare total int default 0;
    sum:loop

        if n<=0 then leave sum;
        elseif n%2=0 then set total := n+total;
        end if;
        set n:=n-1;
    end loop sum;
    select total;
end;
call p10(100);
--
create procedure p11(in n int)
begin
    declare total int default 0;
    sum:loop

        if n<=0 then leave sum;
        elseif n%2=1 then set n:=n-1;iterate sum;
        end if;
        set total := n+total;
        set n:=n-1;
    end loop sum;
    select total;
end;
call p11(100);

游标

游标cursor是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch、close，其语法分别如下。

根据传入的参数ugae,来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插到所创建的一张新表（id，name，profession）中

# 游标
-- 声明游标
create procedure p_cursor(in uage int)
begin
    -- 需要先声明普通变量后声明游标
    declare uname varchar(100);
    declare upro varchar(100);
    -- 定义游标记录符合条件的结果集
    declare user_cursor cursor for select name,profession from tb_user where age <= uage;


    -- 创建新表
    drop table if exists tb_user_pro  ;
    create table if not exists tb_user_pro(
      id int primary key auto_increment,
      name varchar(100),
      profession varchar(100)
    );
    -- 开启游标
    open user_cursor;

    -- 循环获取游标的数据
    while true do
    -- 将游标中的数据插入到新表当中
    fetch user_cursor into uname,upro;
    insert into tb_user_pro values (null,uname,upro);
        end while;

    close user_cursor;
end;

这样虽然表可以创建，存储过程也可以创建，但是会报错，因为当游标中的数据被提取完后就没有数据可以提取了，需要条件处理程序停止。

条件处理程序

条件处理程序handler可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

MySQL状态码官方文档：MySQL :: MySQL 5.7 Error Reference :: 2 Server Error Message Reference

# 游标
# 条件处理程序
-- 声明游标
create procedure p_cursor(in uage int)
begin
    -- 需要先声明普通变量后声明游标
    declare uname varchar(100);
    declare upro varchar(100);
    -- 定义游标记录符合条件的结果集
    declare user_cursor cursor for select name,profession from tb_user where age <= uage;
    -- 声明一个条件处理程序,当满足状态码02000时关闭游标然后退出操作
    declare exit handler for sqlstate '02000' close user_cursor;
    -- 或者 not found代表所有02开头的状态码
    declare exit handler for not found close user_cursor;


    -- 创建新表
    drop table if exists tb_user_pro  ;
    create table if not exists tb_user_pro(
      id int primary key auto_increment,
      name varchar(100),
      profession varchar(100)
    );
    -- 开启游标
    open user_cursor;

    -- 循环获取游标的数据
    while true do
    -- 将游标中的数据插入到新表当中
    fetch user_cursor into uname,upro;
    insert into tb_user_pro values (null,uname,upro);
        end while;

    close user_cursor;
end;

call p_cursor(30);

存储函数

存储函数是有返回的值的存储过程，存储函数的参数只能是in类型。具体语法如下：

计算从1累加到n的值，n为传入的参数值。

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>=0 do
        set total := total+n;
        set n:= n-1;
        end while;
    return total;
end;

select fun1(50);

触发器

触发器介绍

触发器是与表有关的数据库对象，指在insert/udpate/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库时相似的。现在触发器还只支持行级触发，不支持语句触发。

触发器语法

-- 准备工作 : 日志表 user_logs
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

-- 查看触发器
show triggers ;
-- 删除触发器
drop trigger user_insert_trigger;

触发器操作

通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加删除修改

insert类型

-- 插入数据的触发器
create trigger user_insert_trigger
    after insert on tb_user for each row
begin
     insert into user_logs values (null,'insert',now(),NEW.id,concat('插入的数据内容为：id=',NEW.id,'name=',NEW.name,'phone',NEW.phone));
end;
-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (27,'2皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

update类型

-- 修改数据的触发器
create trigger user_update_trigger
    after update on tb_user for each row
begin
     insert into user_logs values (null,'update',now(),NEW.id,
concat('更新之前的数据为：id=',NEW.id,'name=',NEW.name,'phone',NEW.phone,
    '更新之后的数据为：id=',OLD.id,'name=',OLD.name,'phone',OLD.phone));
end;
-- 修改数据
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

delete类型

-- 删除数据的触发器
create trigger user_delete_trigger
    after delete on tb_user for each row
begin
     insert into user_logs values (null,'delete',now(),OLD.id,concat('删除的数据内容为：id=',OLD.id,'name=',OLD.name,'phone',OLD.phone));
end;
-- 删除数据
delete from tb_user where id = 26;

锁

锁的介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL的锁按照粒度分为以下三类：

1. 全局锁：锁定数据库中所有表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

全局锁

全局锁介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就只处于只读状态，后续的DML的写语句，DDL语句，以及更新操作的失误提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

全局锁操作--一致性备份

-- 在cmd命令行中连接linux中的mysql数据库
mysql -h192.168.33.131 -uroot -p
-- 进入mysql后开启全局锁
flush tables with read lock;
-- 读取操作可以正常进行
select * from tb_user;
-- 写入操作等待中
insert into tb_user(id,name,phone) value(null,'asd','1234141');
-- 使用mysql提供的mysqldump工具进行数据库备份，在命令行中进行
exit
mysqldump -h 192.168.33.131 -uroot -p1234 cast > D:/cast.sql
mysqldump --single-transaction -h 192.168.33.131 -uroot -p1234 cast > D:/cast.sql

全局锁特点

数据库加全局锁是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制表（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上--single-transaction参数来完成不加锁的一致性数据备份。

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中

对于表级锁，主要分为以下三类：

1. 表锁
2. 元数据锁（meta data lock，MDL）
3. 意向锁

表锁

表锁分类

对于表锁，分为两类：

1. 表共享读锁 read lock

-- 加锁客户端
-- 加锁
lock tables tb_user read;
-- 读操作可以执行
select * from tb_user;
-- 写操作不能执行 报错
update tb_user set name = '1' where id = 28;
-- 解锁后写操作可以执行
unlock tables;

-- 其他客户端
-- 读操作可以执行
select * from tb_user;
-- 写操作等待解锁........
update tb_user set name = '1' where id = 28;

1. 表独占写锁 write lock

-- 加锁客户端
-- 加锁
lock tables tb_user write;
-- 读操作可以执行
select * from tb_user;
-- 写操作可以执行
update tb_user set name = '1' where id = 28;
-- 解锁后其他客户端的读写操作正常
unlock tables;

-- 其他客户端
-- 读操作等待解锁........
select * from tb_user;
-- 写操作等待解锁........
update tb_user set name = '1' where id = 28;

表锁语法

1. 加锁：lock tables tablename read/write。
2. 释放锁：unlock tables / 客户端断开连接

元数据锁

MDL加锁过程是系统自动控制，无序显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。元数据其实就是表数据。

在MySQL5.5中引入了DML，当对一张表进行增删改查的时候，加MDL读锁共享；当对表结构进行变更操作的时候，加MDL写锁（排他）。

-- 原本仅有一个元数据锁
select * from performance_schema.metadata_locks;
-- 开启事务之后 元数据锁增加
begin：
-- 执行sql语句后 在另一个客户端可以查询到 操作的表已经加上了元数据锁
select * from performance_schema.metadata_locks;
-- 提交数据后元数据锁解锁
commit;

-- 客户端1
-- 开启事务
begin;
-- 读操作
select * from tb_user;

-- 客户端2
-- 读操作写操作均可执行
select * from tb_user;
update tb_user set name = 'java' where id =28;
-- 修改表结构语句阻塞 直到事务提交 元数据锁解锁
alter table tb_user add column hobby varchar(10);

意向锁

为了避免DML在执行时,加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据库是否加锁，使用意向锁来减少表锁的检查。

在事务执行加上行锁的时候会继续加上意向锁，这样并发事务就不用逐行检查行锁，只需要根据意向锁的类型判断是否能加表锁即可。

1. 意向共享锁IS：由语句select...lock in share mode添加，与表锁共享锁read兼容，与表锁排他所write互斥。

-- 客户端1
begin;
select * from tb_user lock in share mode;
-- 客户端2
lock tables tb_user read;->ok
unlock tables;
lock tables tb_user write;->阻塞
unlock tables;

select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

1. 意向排他锁IX：由insert、update、delete...select for update添加，与表锁共享锁和排他锁都互斥。意向锁之间不会互斥。

-- 客户端1
begin;
update tb_user set name = 'mysql' where id = 28;
-- 客户端2
lock tables tb_user write;->阻塞
-- 客户端3
lock tables tb_user read;->阻塞

select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定力度最小，发生所冲突的概率最低，并发度最高。应用在InnoDB引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

1. 行锁（record lock）：锁定单个行记录的锁，防止其他事物对此行进行update和delete。在RC（read committed）、RR（repeatable read）隔离级别下都支持。
2. 间隙锁（gap lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事物在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁（next-key lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙gap。在RR隔离级别下支持。

行锁

InnoDB引擎下实现了以下两种类型的行锁：

1. 共享锁s：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
2. 排他锁x：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

在默认情况下，InnoDB在repeatable read 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁/临键锁

默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

student表

1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁.

-- 客户端1
begin;
-- update语句添加行锁排他锁
-- id=5数据不存在添加间隙锁
update student set name='1' where id =5;
-- lock in share mode手动添加行锁共享锁
-- id=5数据不存在添加间隙锁
select * from student where id = 5 lock in share mode;

-- 客户端2
-- student表中id=7行已经添加了排他锁和间隙锁
select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
begin;
--阻塞 被间隙锁锁住
insert into student values(6,'124312','21412');
-- 客户端1
commit;
-- 客户端2
-- 事务提交之后间隙锁解锁 事务正常运行
query ok;

1. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时。临键锁退化为间隙锁。

-- 为student表中的age字段添加普通索引
create index idx_stu_age on student(age);
begin;
-- 普通索引等值查询
select * from student where age = 2 lock in share mode;
select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

1. 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

注：间隙锁的唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁。

InnoDB引擎

逻辑存储结构

表空间（ibd文件）：一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据。

段：分为数据段（leaf node segment）、索引段（Non-leaf node segment）、回滚段（roll back segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）

区：表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中共有64个连续的页。

页：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16K。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区

行：InnoDB存储引擎数据是按行进行存放的。

架构

MySQL5.5版本后，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存结构

缓冲池buffer pool

Buffer Pool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池中没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以page页为单位，底层采用链表数据结构管理page。根据状态，将page分为三种类型（不同颜色代表不同的状态）：

• free page：空闲page，未被使用。
• clean page：被使用page，数据没有被修改过。
• dirty page：脏页，被使用page，数据被修改过，其中数据与磁盘的数据产生了不一致。

更改缓冲池change buffer

change buffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据page没有在buffer pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区change buffer中，在未来数据被读取时，再将数据合并恢复到buffer pool中，再将合并后的数据刷新到磁盘中。

更改缓冲池相当于是缓冲池的缓存

change buffer的意义是什么？

和聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量磁盘IO.有了change buffer之后，我们可以在缓冲池中合并处理，减少磁盘IO

自适应哈希索引Adaptive Hash Index

Adaptive Hash Index：自适应hash索引，用于优化对buffer pool数据的查询。InnoDB存储引擎会监控对表上个索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引无需人工干预，是系统根据监控情况自动完成的。

参数：adaptive_hash_index

日志缓冲区log buffer

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘IO

参数：innodb_log_buffer_size:缓冲区大小

innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

磁盘结构

系统表空间System Tablespace

System Tablespace：系统表空间是更改缓冲区change buffer的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。

参数：innodb_data_file_path

File-Per-Table Tablespaces每张表独立表空间

File-Per-Table Tablespaces：每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件ibd中。

参数：innodb_file_per_table 默认on

General Tablespaces通用表空间

General Tablespaces：通用表空间，需要通过create tablespace语法创建通用表空间，在创建表时，可以指定该表空间。

create tablespace ts_itheima add datafile 'mmyitheima.ibd' engine = innodb;
 create table orders(id int primary key auto_increment,name varchar(10)) engine=innodb tablespace ts_itheima;

Undo Tablespaces撤销表空间

Undo Tablespaces：撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小为16M），用于存储undo log日志。

Temporary Tablespaces临时表空间

Temporary Tablespaces：InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

Doublewrite Buffer Files双写缓冲区

Doublewrite Buffer Files：双写缓冲区，InnoDB引擎将数据页从buffer pool刷新到磁盘前，现将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

Redo log重做日志

Redo log：重做日志，是用来实现事务持久性的。该日志文件有两个部分组成：重做日志缓冲（Redo Log Buffer）以及重做日志文件（Redo Log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘是，发生错误时，进行数据恢复使用。

后台线程

后台线程是在合适的时机将缓冲区中的数据刷新到磁盘当中

Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

IO Thread

在InnoDB存储引擎中大量使用了AIO（异步非阻塞IO）来处理IO请求，这样可以极大的提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

show engine innodb status;

Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

Page Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。

事务原理

事务

事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事物的持久性的。

该日志文件由两部分组成：重做日志缓冲和重做日志文件，前者实在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用。

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

MVCC

MVCC基本概念

当前读

读取的是记录的最新版本，读取时还要保证其他并发实物不能修改当前记录，会对读取的记录进行加锁。对于我们的日常操作，如：select...lock in share mode（共享锁），select...for update、update、insert、delete（排他锁）都是一种当前读。

--1 2
begin;
--2
update student set name='jsp' where id =1;
--1 查询不到更新的数据 事务未提交
select * from student;
--2事务提交
commit;
--1 查询不到更新的数据 rr隔离级别
select * from student;
--1 查询到当前数据 加锁当前读
select * from student lock in share mode/for update;

快照读

简单的select语句（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个select语句才是快照读的地方。
• Serializable：快照读会退化为当前读，每次读都会加锁。

MVCC

全称Muti-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

MVCC实现原理

MVCC隐式字段

ibd2sdi student.ibd查看表结构

undo logo日志

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert时，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete时，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即删除。

undo log版本链

不同的事务或者相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readView读视图

ReadView（读视图）是快照读SQL执行时MVCC提取数据的一句，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中包含了四个核心字段：

原理解析（Read Committed版本）

RC隔离级别下，在事务中每一次执行快照读时生成ReadView

原理解析（Reapeatable Read版本）

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

MySQL管理

系统数据库

常用工具

mysql

mysql -h192.168.33.131 -P3306 -uroot -p1234 cast -e "select * from student"

mysqladmin

mysqlbinlog

mysqlshow