MySQL进阶学习

最新推荐文章于 2024-07-14 17:48:48 发布

ILFFQ

最新推荐文章于 2024-07-14 17:48:48 发布

阅读量401

点赞数 1

分类专栏： JavaEE 文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/weixin_63016041/article/details/127148431

版权

JavaEE 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

TOC](MySQL理解进阶)

author ILFFQ

存储引擎简介

MySQL 体系结构

MySQL体系由上到下分为连接层，服务层，引擎层，存储层

连接层最上层是一些客户端和连接服务，主要完成一些类似于连接处理，授权认证，及相关的安全方案。服务器也会为安全接入的每个客户端验证他所具有的操作权限。
服务层第二层架构主要完成大多数的核心服务功能，例如：SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能都在这一层完成，例如过程和函数等等
引擎层存储引擎真正的负责了MySQL 的数据的存储和提取（索引），服务器通过API和存储引擎通信，不同的存储引擎具有不同的功能，可以根据自己的需求来选择合适的存储引擎。
存储层主要是将数据存储在文件系统上，并完成和存储引擎的交互。

存储引擎简介

存储引擎就是存储数据，建立索引，更新和查询数据等技术的实现方式。存储引擎是基于数据库中表的，不是基于数据库的。索引存储引擎也可以被称为表类型（一个数据库中的不同的表可以选择多个存储引擎）。

不同存储引擎的特点

Innodb 存储引擎是一个兼顾高可靠性和高性能的通用存储引擎，MySQL5.5后称为默认存储引擎。
- 优势：1.DML操作遵循ACID模型，支持事务。2.支持行级锁，提高并发性能。3.支持外键约束，保证数据的完整性和正确性。
- 文件：表名.idb 文件是Innodb引擎下每张表都会对应的一个表空间文件。存储该表的表结构(frm,sdi)数据和索引。表名.frm 文件存储表结构。参数：innodb_file_per_table决定多张表共享表空间还是每一个表对应一个表空间文件。默认每个表对应一个表空间。表空间(tablespace)–>段(segment)–>区(extent)–>页(page)–>行(row) 页分为索引页和数据页（联想一下B+Tree结构）。页是磁盘操作的最小空间。区的大小固定 1MB 页大小固定 16K一个区包含64个页。
MyISAM 存储引擎是MySQL 早期的默认引擎。
- 特点：不支持事务，不支持行锁，不支持外键，只支持表锁，访问速度快。
- 表名.sdi ：存储表结构信息。表名.MYD：存储数据。表名.MYI：存储索引。
memory 存储引擎的数据是存放在内存中的，由于收到硬件问题的限制或断电问题的影响，只能将这些表作为临时表，或缓冲文件。
- 内存存放数据，默认为hash索引，表名.sdi：存储表结构信息。索引和数据存放在内存中

索引

索引概念及优缺点

索引（index）是帮助MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引优点提高对数据的检索效率，降低数据库的IO成本，（降低数据排序成本，降低CPU的消耗）
索引缺点索引列要额外的占用磁盘空间。索引提高了对数据查询效率，同时降低了更新表的速度，降低insert update delete （DML）语句的执行效率。
（可以联想一下数组的方便查询，不方便更改的原理）

索引的结构

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

索引结构	描述
B+Tree索引	最常见的索引，大部分引擎都至此B+Tree索引
R-Tree空间索引	空间索引是MyISAM引擎的一个特殊的索引类型，主要用于地理空间数据类型，平时较少使用
Hash索引	底层数据结构是Hash表实现，只有精确匹配索引列的查询才有效，不支持范围查询
Full-text 全文索引	是一种通过建立倒排索引，快速匹配文档的方式。类似Lucene,Solr ES。

索引	Innodb	MyISAM	Memory
B+Tree索引	支持	支持	支持
R-Tree索引	不支持	支持	不支持
Hash索引	不支持	不支持	支持
Full-Text 全文索引	5.6以上支持	支持	不支持

索引：如果没有特别说明，指的都是B+Tree结构的索引。

索引结构

二叉树

二叉树缺点：顺序插入时，会形成一个单向链表，查询能力降低。大数据量的情况下，层级深，检索速度慢

红黑树

红黑树缺点：大数据量的情况下，层级深，检索速度慢

B-Tree(多路平衡查找树)

B-Tree 的度数为 9，即为9阶B-Tree，每个结点最多存储8个数据，9个指针。

B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子结点的链表指针，就形成了带有顺序指针（双向）的B+Tree，提高区间访问的性能。

	假设 B-Tree 的高度为 h,B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，也即索引的B+树层次一般不超过三层，所以查找效率很高）。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

B+Tree的优势

（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。（对于B-Tree，无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值键值减少，指针也会减少，同样要保存大量的数据，只能增加树的高度，导致查询性能下降，也就是降低了区间访问性）

Hash索引

联想一下hashMap和hashSet。

hash索引只能用于对等比较，不支持范围查询（between <,>）无法利用索引完成排序，查询效率一般比较高，通常只需要检索一次（不出现Hash冲突的情况下）效率通常高于B+Tree索引。在MySQL中支持hash索引的是Memory引擎，而Innodb 中具有自适应hash功能，自适应hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中的某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以多个
全文索引	全文索引查找的是文本中的关键字，而不是所i你中的值	可以有多个	FULLTEXT

Innodb引擎中按照存储形式划分

分类	含义	特点
聚集索引（Clustered Index）	将数据和索引放到一块，索引结构的叶子结点保存了行数据	必须有，只有一个
二级索引（Secondary Index）	将数据和索引分开存储，索引结构的叶子结点关联的是对应的主键。	可以存在多个

聚集索引的选取规则

如果表中存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的的唯一索引，则数据引擎就回自动生成一个rowid作为隐藏的聚集索引。

在B+Tree 的索引结构中
聚集索引的叶子结点的下方会存放每个索引值对应的一个记录（一行数据）。
二级索引的叶子节点下方会存放每个索引值对应的主键(该列为聚集索引)数据。

（回表查询） select * from student1 where name='xiao';
当需要通过该二级索引查询相关数据的时候，会先检索二级索引，查找主键(id)数据（可以唯一确定表聚集索引）。通过主键(id)数据 检索聚集索引，查询到该主键(id)数据对应的记录（完整的行数据）。

思考：

Innodb主键索引的B+Tree的存储规模

假设：一行数据为1KB,一页中可以存储16行这样的数据。Innodb的指针占用6Byte的空间，主键为bigint，占用8Byte。

如果B+Tree的高度为2：

n: 为结点中（索引页）键的最大个数，
n+1 : 为结点（索引页）的最大指针个数
通过  n*8+(n+1)6=16*1024  可以得到n+1=1171； 结点的最大指针为1171；
一个指针指向一个数据页，所以，高度为2的B+Tree 最多指向1171个数据页，一个数据页最多存放16个记录
所以高度为2的B+Tree 中最多可以检索  1171*16 =18736  个记录

如果B+Tree的高度为3：

每个指针指向一个索引页，每个从索引页出发的指针 再指向 数据页  最多指向1171*1171 个数据页，一个数据页最多存放16个记录
所以高度为2的B+Tree 中最多可以检索  1171*1171*16=21939856 个记录 千万级

所存储数据太大的情况下考虑分库分表

索引语法

设置索引的几种方式

--创建索引
CREATE INDEX indexName ON table_name (column_name)
--修改表结构（添加索引）
ALTER table tableName ADD INDEX indexName(columnName)
--创建表的时候指定列为索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
--删除索引
DROP INDEX [indexName] ON mytable; 
--查看表中所有的索引
SHOW INDEX FROM table_name

/*
不要对经常变动的表加索引
小数据量的表不要加索引
索引加在常用于查询的字段上
索引的数据结构：InnoDB的默认索引类型是B+Tree
*/

SQL性能分析

1.查看对数据库操作的各种SQL语句的次数

-- 展示各种SQL的执行频率
show [global | session] status like 'Com_______';

2.通过慢查询日志定位效率比较低的SQL语句

--查看慢查询日志是否开启
show variables like 'slow_query_log';
--开启慢查询日志
show_query_log=1
--设置慢查询日志的时间为3秒，SQL语句执行的时间超过三秒，就会被视为慢查询，记录慢查询日志。
long_query_time=3

profile 详情

执行一系列的业务SQL 操作，然后通过如下指令查看指令的执行耗时

--查看当前数据库支持不支持profile
select @@have_profiling
--默认数据库的profiling是关闭的,把它打开
set profile=1;
--查看每一个SQL 的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
查看指定 query_id 的SQL语句CPU的使用情况
show profile cpu for query query_id;

4.explain 执行计划

explain 或 desc 查看SQL语句的执行计划。包括再select 语句执行过程中表如何连接和连接的顺序。

explain select 字段列表 from 表名 where 条件。

explain 执行计划中每个字段的含义

id select 查询的序列号，表示查询中执行的select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同和，值越大，越先执行）。
select_type 表示select 的类型，常见的取值由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的值越大越好。
extra 额外信息。

索引使用规则

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

explain select *
from t_student force index(idx_sex)
where sex='男';
-- use index(索引名称) 建议使用此索引查询
-- ignore index(索引名称) 忽略此索引
-- force index(索引名称)强制使用此索引。

覆盖索引：尽量使用覆盖索引（查询使用的索引，并且需要返回的列，再该索引中能够全部找到）
- 减少select * 的使用（尽可能的减少回表查询）。
- using index condition : 查找使用了索引，并且需要回表查询
- using where using index: 查找使用了索引，但是所需要的数据都可以在索引列中找到，不需要回表查询。
前缀索引：当字段类型为text varchar时，有时候需要索引很长的字符串，这回让索引变得很大，查询时浪费IO资源，影响查询效率，此时可以将字符端的一部分前缀建立索引，这样可以节约索引空间，从而提高索引效率。（重点分析查询过程）

--格式：  n为前缀的大小。
create index idx_sex on table_name(sex(n))
alter table add index idx_sex (sex(n))

单列索引和联合索引：单列索引：一个索引包含一个列。联合索引：一个索引包含多个列。查询多个字段，尽量使用联合索引（最左前缀法则，即第一个字段索引必须存在），避免回表查询。

索引失效

索引列进行函数运算
索引列为字符串时，不加引号隐式将数字串转化为字符串（不再使用此列上的索引），如果不是数字串的话，直接报错。。。
模糊查询：如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。
用or分割的条件：如果or前的条件中有列索引，而后边的列中没有索引，那么涉及到的索引都不会被引用。
如果MySQL评估使用索引比全表查询更慢，就不会使用索引。
最左前缀法则：如果索引了多列，要遵循最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过其中的某一列。最左侧的列只要存在就可以，不一定非得按照顺序来。如果跳跃了其中的某一列，索引将部分失效（后边的字段索引失效）。
范围查询：联合索引中出现范围查询（>,<），范围查询的右侧列索引失效。将（>,<）改为（>=,<=）就可以避免索引失效。

索引设计原则

针对数据量较大，查询比较频繁的表建立索引。
针对常作为查询条件，排序条件，分组条件操作的字段建立索引
尽量选择区分度比较高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
尽量使用联合索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
控制索引数量，索引越多，维护索引结构的开销越大，影响增删改查效率
如果索引不能存储null,则应该在创建表的时候，将该字段设置为not null 约束, 当MySQL优化器知道每列是否包含null值时，优化器可以更好的确定哪一个索引的查询效率更高。

SQL优化

插入数据优化

insert批量插入

insert into t_student (name, sex) values ('小鸟一号','男'),('小鸟二号','女');

手动提交事务

start transaction ;
insert .......
commit;

主键顺序插入
load指令大数据批量插入

select @@local_infile;
load data local infile 'C:\\Users\\20375\\Desktop\\test.txt' into table class fields terminated by ',' lines terminated by '\n';
select * from class;

主键优化

页分裂：主键没有按顺序插入的情况会出现，涉及到页间指针的指向改动（思考原理）

页合并：删除记录的时候出现，页中删除的记录达到merge_threshold(默认值是页中完全存满记录的50%)，Innodb引擎就会开始寻找前后页，尝试将前后的页与当前页合并以节省空间。

order by 优化

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

using Index:通过索引顺序扫描直接返回数据，不需要额外的排序，操作效率高。

根据排序字段建立合适的索引，多字段排序遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序，一个降序，需要注意联合索引在创建时的规则（asc,dess）
不可避免的出现fileSort,大量数据排序时，可以适当的增大排序缓冲区大小（默认缓冲区大小：256KB）

建立联合索引age,name 默认是asc   如果排序order by age desc,name desc  也会走using index,只不过会反着遍历索引B+Tree。           如果排序order by age asc ,name desc  就会走using fileSort,uing index。需要额外的缓冲区进行排序。

group by 优化

分组操作可以通过索引来提高效率，索引的使用满足最左前缀法则

create index idx_name_age on table(name,age)
select firstname,age,count(*)  from stuent  group by age;  extra信息：using index
select firstname,age,count(*)  from stuent  group by age;  extra信息：using temporary
select firstname,age,count(*)  from stuent  group by firstname;  extra信息：using index

limit 优化

通过覆盖索引加子查询的方式优化。（理解）

-- 其中id 为覆盖索引
select * from student where id in(select id from student order by id limit 20000,10;)

count 优化

count(主键): InnoDB 引擎会遍历整张表，把每一行的主键id 都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不能为null）

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

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

count(*): InnoDB引擎不会将全部字段取出来，而是专门做了优化，不取值，服务层直接进行累加。

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

优化方案的话：就是自己计数。

update 优化

InnoDB引擎的行锁是针对索引加的锁，不是针对记录加的锁，一旦索引失效，或者where 条件字段没有索引
（update … where name =‘肖猛’ 其中的name 字段没有索引），就会将行锁升级为表锁。以至于降低并发性。

视图

视图的基本用法

--创建视图
create or replace view  2205_v_1 as select name,sex from t_student where t_student.age<4;
--修改视图
create or replace view  2205_v_1 as select name,sex from t_student where t_student.age<4;
alter view  2205_v_1 as select name ,sex, t_student.age from t_student;
--删除视图
drop view if exists  2205_v_1;
-- 查询视图
select * from 2205_v_1;
-- 查看原表
select * from t_student;

视图检查选项

with cascaded check option

create view v1 as select name,t_student.age  from t_student  where t_student.age<=20 
       (--with cascaded check option v2级联v1,v1逻辑上是有检查选项的);
create view v2 as select name,age  from v1  where v1.age>=10 with cascaded check option ;
检查完v2条件后，会接着检查v1条件。

WITH CASCADED CHECK OPTION的视图检查选项，MySQL检查所有依赖视图的规则。向下级联。

with local check option

create view v1 as select name,t_student.age  from t_student  where t_student.age<=20 
create view v2 as select name,age  from v1  where v1.age>=10 with local check option ;
检查完v2条件后，不会接着检查v1条件。

WITH LOCAL CHECK OPTION，MySQL会检查WITH LOCAL CHECK OPTION和WITH CASCADED CHECK OPTION选项的视图规则。

视图更新

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

聚合函数或窗口函数（sum(),min(),max(),count()等）
distinct
group by
having
union 或 unioin all 等。

create or replace view 2205_v_1 as select count(*) from t_student;
insert into 2205_v_1 values (90);  -- 插入会报错。

视图作用

简单：视图不仅可以简化用户对数据的理解，简化操作，哪些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次都指定全部的条件。
安全：数据库可以授权到表，但不能授权到数据库表中的特定的列和行上，通过视图，用户只能查询和修改他们所能看见的数据。
数据独立：视图可以帮助用户屏蔽真表结构带来的影响。

举例：

-- 使用视图 简化三张表联查的操作
-- 三表联查语句
select s.name, s.no, c.name from student s, student_course sc ,course c
where s.id=sc.studentid and sc.courseid =c.id;
-- 视图操作  as 三级联查语句
create view v3 as ((((    select s.name, s.no, c.name from student s, student_course sc ,course c
where s.id=sc.studentid and sc.courseid =c.id;    ))))  )with cascaded check option;

存储过程

介绍：存储过程时事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的额工作，减少数据库和应用服务器之间的传输，就是数据库SQL 语言层面的代码封装和重用。特点：封装，复用，可以解锁参数，也可以返回数据，减少网路交互，效率提升。

# 创建存储过程
create procedure  func1()
begin
    select * from t_student;
end;
# 调用存储过程
call func1();
# 查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='2205_db';
show create procedure func1;
# 删除存储过程
drop procedure if exists func1;

命令行中执行创建存储过程，需要定义结束符号 delimiter ^^(自定义符号); 结束操作后使用 delimiter ; 修改成正常的形式。

变量

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

-- 系统变量
# 查看系统变量, 默认是查询session 会话级别的变量， 不是全局变量（global）
show session variables ;-- 查询所有会话级别的系统变量
show global variables  like 'auto%'; -- 模糊查询和当前内容相关的会话级别的系统变量
 select @@global.autocommit;   -- 查看指定的全局变量。
 select @@session.autocommit;
# 设置系统变量
set session autocommit  =1;
set @@global.autocommit =1;
# MySQl服务器重启后，系统变量会自动恢复成默认值。

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

# 用户自定义变量
-- 赋值
@Myname ='XM';
@MyHobby:='Java',@MySorce:=100;
select @define:='定义';
select count(*) into @Mycount from t_student;
-- 使用
select @Mycount,@define;

select @NotKnow; --不会报错，创建变量Notknow 并默认赋值为null。

局部变量：定义在局部生效的变量，需要declare 声明。用于存储过程内的局部变量和输入参数，局部变量的使用范围是在begin …end块中。

#局部变量
# 明确作用区间
create procedure  func2()
begin
    declare Stu_count int default 0;
    select count(*) into Stu_count from t_student;
    select Stu_count;
    /*
    declare myCount int default 0;
    set myCount =5;
    select myCount;
    */
end;
call func2;

存储过程语法

if 条件判断

create   procedure func3()
begin
    declare score int default 100;
    declare result varchar(10);
    if score >=80 then
        set result:='优秀';
    elseif  score >=60 and score<80 then
        set result:='及格';
    else  set result:='不及格';
    end if;
    select result;
end;
call func3;

参数

in 该参数作为输入，调用时的传入值（默认）

out 该类参数作为输出，该参数可以作为返回值

inout 既可以作为输入参数，也可以作为输出参数

-- 参数使用
create   procedure func4(in score int,out result varchar(10))
begin
    if score >=80 then
        set result:='优秀';
    elseif  score >=60 and score<80 then
        set result:='及格';
    else  set result:='不及格';
    end if;
end;
call func4(50,@result);
select @result ;

返回200分值转化后的结果

create procedure  func5(in score int ,out result int)
begin
    set result :=score/2;
end;
call func5(50,@result);
select @result ;

case

根据传入月份，判断属于哪一个季度

-- case
create procedure func6(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3 then
        set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else
        set result:='非法参数';
    end case ;
    select concat('输入的月份为',month,'所属的季度为',result);
end;
call func6(11);

while

-- while
create procedure  func7(n int)
begin
    declare result int default 0;
    while n>=1
        do
        set result:=result+n;
        set n:=n-1;
        end while;
    select result ;
end;
call func7(3);

repeat

类似do while

-- repeat  满足条件退出循环。
# 先执行一次，然后判断逻辑是否满足退出条件。
create procedure  func8(n int)
begin
    declare result int default 0;
    repeat
        set result:=result+n;
        set n:=n-1;
    until n<=0 end repeat;
    select result ;
end;
call func8(3);

loop

可以形成简单的死循环，loop 配合下面的两个语句使用：

leave : 退出循环(break) iterate : 必须用在循环体中,跳过当前循环剩下的语句,直接进入下次循环(continue)

1~n 累加

create procedure  func9(n int)
begin
    declare result int default 0;
     sum:loop
        set result:=result+n;
        set n:=n-1;
        if n<=0 then leave sum;
        end if;
    end loop sum;
    select result;
end;
call func9(6);

计算1到n的偶数和

create procedure  func10(n int)
begin
    declare result int default 0;
     sum:loop

        if n%2=0 then
            set result:=result+n;
        end if;
        set n:=n-1;
        if n<0 then leave sum;
        end if;
    end loop sum;
    select result;
end;
drop procedure func10;
call func10(10);

cursor 游标,光标

cursor : 一个存储在mysql服务器上数据库查询，它不是一条select 语句，而是被语句检索出来的结果集。

-- cursor 游标
create procedure func11(Uage int )
begin
    -- 先声明普通变量，再声明游标。
    declare  getName varchar(10);
    declare getSex varchar(2);
    declare getAge int;
    declare  MyCursor cursor for select * from t_student where t_student.age<Uage;
    create table if not exists my_student(
        myName varchar(10) not null,
        mySex varchar(2) not null ,
        myAge int not null auto_increment primary key
    );
    open MyCursor;
    while true do
        fetch MyCursor into getName,getSex,getAge;
        insert into my_student values (getName,getSex,getAge);
        end while;
    close MyCursor;
end;
call func11(4);

handler 条件处理程序

handler 可以用来定义再流程控制结构执行过程中遇到问题时相应的处理步骤。如下所示

declare hander_action handler for  condition_value,[condition_values]...;
hander_action:
       continue:继续执行当前程序
       exit：终止执行当前程序
 condition_values
       SQLSTATE sqlstate_values:状态码，如0200；
       SQLWARNING : 所有以01开头的SQLSTATE代码的缩写
       NOT FOUNd: 所有以02开头的SQLSTATE代码的缩写
       SQLEXCEPTION: 所有没有被SQLWARNING和NOT FOUNd捕获的SQLSTATE代码的缩写。

-- handler 用法
create procedure func12(Uage int )
begin
    -- 先声明普通变量，再声明游标。
    declare  getName varchar(10);
    declare getSex varchar(2);
    declare getAge int;
    declare  MyCursor cursor for select * from t_student where t_student.age<Uage;
    declare exit handler  for not found close MyCursor;
    create table if not exists my_student(
        myName varchar(10) not null,
        mySex varchar(2) not null ,
        myAge int not null auto_increment primary key
    );
    open MyCursor;
    while true do
        fetch MyCursor into getName,getSex,getAge;
        insert into my_student values (getName,getSex,getAge);
        end while;
end;
call func12(4);

存储函数

存储函数是一种特殊的存储过程。存储函数是必须有返回值的存储过程，存储函数的参数只能是IN类型的。

-- 语法结构
create function func13([in 类型 参数])
returns type [characteristic...]
begin
    --sql 语句
    return...;
end;
characteristic 说明：
determinister: 相同的输入参数，总是产生相同的结果
not sql: 不包含sql 语句
reads sql data : 包含读取数据的语句，但不包含写入数据的语句。

案例：计算从1~n的和。

-- 存储函数   （特殊的存储过程）
# 存储函数是必须有返回值的存储过程，存储函数的参数只能是IN类型的。
create function func13(n int)
returns int deterministic
begin
    declare  sum int default 0;
    # 方法1
/*    while n>0 do
        set sum:=sum+n;
        set n:=n-1;
        end while;*/
    # 方法2
/*    repeat
        set sum:=sum+n;
        set n:=n-1;
    until n<=0 end repeat;*/
      # 方法3
    result:loop
        set sum:=sum+n;
         set n:=n-1;
        if n<=0 then
            leave result;
        end if;
    end loop;
     return sum;
end;
drop function func13;
select func13(100);

触发器

简介：触发器始于表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器只支持行级触发，不支持语句级触发。

行级触发器和语句级触发器。.行级触发器会被插入，更新或删除的每一行所激活。. 例如，如果一个表中有100行被插入，更新或删除，则该触发器将为受影响的100行自动调用100次。语句级触发器，当每个事务被执行的时候被激活。

触发器类型	new old
insert型触发器	new 表示将要或者已经新增的数据
update型触发器	old表示修改之前的数据，new 表示将要或已经修改后的数据
delete型触发器	old 表示将要或已经删除的数据

格式

create trigger trigger_name
after/before  insert/update/delete
on tbl_name for each row
begin
    -- 触发器逻辑
end;

create table if not exists t_student_logs
(
    id             int         not null auto_increment,
    operate        varchar(20) not null comment '操作类型,insert/update/delete',
    operate_time   datetime    not null comment '操作时间',
    operate_id     int         not null comment '操作记录的主键标识',
    operate_params varchar(500) comment '操作参数',
    primary key (id)

) engine = innodb,
  charset = utf8;

drop trigger trigger_student_insert;
# 利用触发器记录t_student 表的数据变更日志，将变更日志插入到日志表 t_student_logs中，包含增加，删除和修改。
-- 插入数据触发器
create trigger trigger_student_insert
    after insert
    on t_student
    for each row
begin
    insert into t_student_logs
    values (null, 'insert', now(), NEW.age, concat('插入的数据内容：', 'name=', NEW.name, 'sex=', NEW.sex, 'age=', NEW.age));
end;
insert into t_student
values ('小鸟11号', '男', 95);


-- 修改数据的触发器
drop trigger trigger_student_update;
create trigger trigger_student_update
    after update
    on t_student
    for each row
begin
    insert into t_student_logs
    values (null, 'update', now(), NEW.age, concat('更新之前数据内容：', 'name=', OLD.name, 'sex=', OLD.sex, 'age=', OLD.age,
                                                   '|',
                                                   '更新之后数据内容：', 'name=', NEW.name, 'sex=', NEW.sex, 'age=', NEW.age));
end;
update t_student
set name='哈哈哈'
where age = 3;


-- 删除数据触发器
create trigger trigger_student_delete
    after delete
    on t_student
    for each row
begin
     insert into t_student_logs
    values (null, 'delete', now(), OLD.age, concat('删除的数据内容：', 'name=', OLD.name, 'sex=', OLD.sex, 'age=', OLD.age));
end;
delete
from t_student
where age>30 and age<80;

-- 查看
select *
from t_student;
show triggers;
select *
from t_student_logs;

事务操作（基础知识点见SQL认知与基础）

事务管理（ACID）

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

解释：

1.原子性：原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作

2.一致性：针对一个事务操作前与操作后的状态一致

3.隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

4.持久性：事务一旦提交，则不可逆转，即使服务器断电，服务器重启后数据也会被被持久化到数据库中，若事务未提交时，服务器断电，会回到提交前状态，不改变数据库内容。

隔离性的隐藏问题

脏读：指一个事务读取了另外一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读（huan’du）是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted （读取未提交）性能高，数据安全性差	存在	存在	存在
Read uncommitted （读取并提交）orcle	不存在	存在	存在
Repeatable Read （可重复读） mysql	不存在	不存在	存在
Serializable（串行化）性能低，数据安全性高	不存在	存在	存在

实际操作

-- 以下操作模拟并发事务

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set session transaction isolation level read uncommitted;
-- 创建测试表
create table account(
    id int auto_increment primary key,
    name varchar(10) not null,
    money varchar(10) not null
);
insert into account values (null,'张三',2000);
insert into account values (null,'李四',2000);

-- 读取未提交   存在（脏读，不可重复读，幻读）问题
set session transaction isolation level read uncommitted;
start transaction;
select * from account;
commit ;

-- 读取并提交   存在 (不可重复读，幻读)问题
set session transaction isolation level  read committed;
start transaction;
-- 在一个是事务中 查询结果不一样
select * from account;
-- 另一个事务操作完成后，再次查看本次事务中查询结果
select * from account;
commit;

-- 可重复读(mysql默认)  存在(幻读)问题
set session transaction isolation level  repeatable read;
start transaction;
-- 其他事务操作不影响当前事务内的两次查询结果，即，可重复读。
select * from account;
select *from account;
commit;
-- 另一个事务的操作会在当前事务提交后，生效。
select * from account;

-- 测试可重复读  的幻读问题
set session transaction isolation level  repeatable read;
start transaction;
select * from account
where id=3;
-- 插入失败,另一个事务插入相同主键的数据。
-- Duplicate entry '3' for key 'PRIMARY'
insert into account values (3,'草鸡王五',2000);
commit;

-- 可序列化  安全性高，没有任何问题。性能比较差
set session transaction isolation level serializable ;
start transaction;
# 当前操作 id为4的记录，如果另一个事务也要操作id为4的记录，
# 那么只能等待当前事务提交后才可以执行另一个事务中对id为4的记录的操作。
select * from account
where id=4;
commit;






-- 以下代码执行在另一个会话 （即新建一个连接，模拟两个客户端并发操作）
start transaction;
update account set money=money-1000
where name='张三';
commit;

start transaction;
update account set money=money+1000
where name='张三';
commit;

start transaction;
insert into account values (3,'王五',2000);
commit;

start transaction;
insert into account values (4,'赵六',2000);
commit ;

锁

简介：锁是计算机协调多个进程和线程并发访问某个资源的机制。在数据库中，数据也是一种供多个用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁对数据库非常重要，也更加复杂。

锁类型

全局锁：锁定数据库中的所有表

表级锁：每次操作锁定整张表

行级锁：每次操作锁定对应的记录

全局锁

全局锁就是对整个数据库进行加锁，枷锁后，整个数据库实例就处于只读状态，后续的DML的写语句和DDL语句，以及更新操作的事务提交语句都将被阻塞。一般用于做数据库全库的逻辑备份，对所有的表进行锁定，从而获取一致的视图，保证数据的完整性。

-- 全局锁
flush tables with read lock;
-- 备份操作   图形化界面 达标
--          命令行备份 达标
# 当前系统下  mysqldump -h主机地址 -u用户 -p密码 数据库[数据库 表 表] > 存储地址\name.sql

-- 导入数据库 图形操作  达标
# 在需要导入的数据所在的sql文件的前两行加入 create database 数据库名; use 数据库名;
-- 导入数据库 命令行操作 达标
#  登录的状态下，use 某个数据库，然后source 备份文件.sql 就可以导入数据库中的表，如果是导入数据库，就不用切换到数据库，直接source 备份文件.sql导入
# 不登陆的情况下，mysql -u 用户 -p 密码  (库名)< 备份文件.sql
unlock tables ;

表级锁

表共享读锁和表独占写锁

-- 表级锁
# 表共享读锁
# 当前连接 可读不可写     其他连接不能写(阻塞)，但是能读
lock tables t_student read;
select * from t_student;
update t_student set  name ='小鸟1号'where age =1;
unlock tables ;
#表独占写锁
# 当前连接 可读可写    其他连接不能写(阻塞)，也不能读(阻塞)
lock tables  t_student write ;
update t_student set name ='小鸟2号' where age =2;
unlock tables;

元数据锁

MDL 加锁过程是系统自动控制，不需要显式的使用，在访问每一张表的时候都会自动加上。
MDL锁的主要作用是维护表元数据(表结构)一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
如果某一张表存在没有提交的事务，就不可以对此表进行表结构的修改，避免和DML 和DDL操作冲突。

MySQL5.5 之后引入MDL 当对一个表进行增删改查的时候，加MDL读，写锁（共享）；当对表结构进行变更的时候，加入MDL写锁（排它）

对应SQL	锁类型	说明
lock tables xxx,xxx,xxx read /write	shared_read_only/ shared_no_read_write
select ,select…lock inshare mode	shared_read	与shared_read,shared_write 兼容，与exclusive互斥
insert,update,delete,select…for update	shared_write	与shared_read,shared_write 兼容，与exclusive 互斥
alter table ,drop table	exclusive	与其他的MDL都互斥

-- 在事务中使用 上述表中的sql语句，会对指定的表自动的加上相应的元数据锁。是事务提交后，事务内的元数据锁就会自动释放。
--连接1事务1中 使用 select ,insert 等语句，会给特定的表加上 MDL读写锁（共享）,连接2中的事务2中若使用  select , insert 等语句，操作同一张表，不会阻塞（因为MDL读写锁（共享）相互兼容）；连接3中的事务3如果使用alter drop等DDL修改表结构（这些语句，会给特定的表加上exclusive锁，与其他的MDL互斥），就会阻塞，直到连接1事务1提交，释放 MDL读写锁（共享），连接3事务3中的DDL语句才可以正常执行。

意向锁

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

意向共享锁(IS)：由语句select …lock inshare mode 添加。与表锁的读锁（共享）兼容，与表锁的写锁（共享）互斥。

意向排它锁(IX)：由insert,updatae,delete ,select…fro update 添加。与表锁的读锁（共享）与表锁的写锁（共享）都互斥。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

重点是：意向锁之间不会互斥。

意向锁解决了innodb引擎中表锁和行锁冲突的问题。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

	排他锁	意向排他锁	共享锁	意向共享锁
排他锁	互斥	互斥	互斥	互斥
意向排他锁	互斥	兼容	互斥	兼容
共享锁	互斥	互斥	兼容	兼容
意向共享锁	互斥	兼容	兼容	兼容

行级锁

行级锁的每次操作对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用于InnoDB引擎。 InnoDB的数据是基于索引组织的，行锁是通过索引上的索引项加锁来实现的，而不是针对记录加的锁.

行级锁分类

行锁(Record lock)：锁定单个索引记录的锁，防止其他事务对此进行update和delete。在RC，RR隔离级别下都支持。

间隙锁(Gap lock)：锁定索引记录间隙（不包含此纪录），确保所及索引记录间隙不变，防止其他事务在这个间隙中Insert 数据，产生幻读。在RR隔离级别下都支持。

临键锁(Next -Key lock )：行锁和间隙锁组合，同时锁住记录和记录前的间隙Gap,在RR隔离级别下支持。

行锁

共享锁(S)：允许一个事务读一行，阻止其它事务获得相同数据集的排他锁（互斥），但是允许其他事务获得相同数据集的共享锁（兼容）

排他锁(X): 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁（均互斥）

SQL	行锁类型	说明
insert …	排他锁	auto
update…	排他锁	auto
delete…	排他锁	auto
select…	不加任何锁
select … lock in share mode	共享锁	lock in share mode
select … for update	排他锁	for update

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

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将会对表中的所有记录加锁，此时就会升级称为表锁（表共享读锁[其他连接可看，不可写]）。

间隙锁和临键锁

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

索引上的等值查询（唯一索引[主键]）,给不存在的记录加锁，优化为间隙锁。
索引上的等值查询（普通索引），向右遍历时，最后一个值不满足查询需求时，next -key lock优化成间隙锁
索引上范围查询（唯一索引[主键]），会访问到不满足条件的第一个值为止。

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

锁操作演示

-- 全局锁
flush tables with read lock;
-- 备份操作   图形化界面 达标
--          命令行备份 达标
# 当前系统下  mysqldump -h主机地址 -u用户 -p密码 数据库[数据库 表 表] > 存储地址\name.sql

-- 导入数据库 图形操作  达标
# 在需要导入的数据所在的sql文件的前两行加入 create database 数据库名; use 数据库名;
-- 导入数据库 命令行操作 达标
#  登录的状态下，use 某个数据库，然后source 备份文件.sql 就可以导入数据库中的表，如果是导入数据库，就不用切换到数据库，直接source 备份文件.sql导入
# 不登陆的情况下，mysql -u 用户 -p 密码  (库名)< 备份文件.sql
unlock tables;

-- 表级锁
# 表共享读锁
# 当前连接 可读不可写     其他连接不能写(阻塞)，但是能读
lock tables t_student read;
select *
from t_student;
update t_student
set name ='小鸟1号'
where age = 1;
unlock tables;
#表独占写锁
# 当前连接 可读可写    其他连接不能写(阻塞)，也不能读(阻塞)
lock tables t_student write;
update t_student
set name ='小鸟2'
where age = 2;
unlock tables;

-- 元数据锁(meta data lock,MDL)
#MDL 加锁过程是系统自动控制，不需要显式的使用，在访问每一张表的时候都会自动加上。
# MDL锁的主要作用是维护表元数据(表结构)一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
# 如果某一张表存在没有提交的事务，就不可以对此表进行表结构的修改，避免和DML 和DDL操作 冲突。
set session transaction isolation level repeatable read;
start transaction;
select *
from t_int
where id = 5;
insert into t_int
values (7, '本次7号', 1);
commit;
rollback;
# select *
# from performance_schema.metadata_locks;



-- 意向锁
start transaction;
select *
from t_student
where age = 1 lock in share mode;
commit;

start transaction;
update t_student
set name ='小2号'
where age = 2; -- 自动增加行锁，和意向排他锁
commit;
rollback;
-- 另一个连接上的操作
start transaction;
lock tables t_student write;
lock tables t_student read;
commit;
rollback;



-- 行级锁

-- 行锁  情况1  针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
start transaction;
update  t_int set name='小一号' where id=1;
select * from t_int where id=1 lock in share mode ;
commit;
-- 行锁 情况2 InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，
#           那么InnoDB将会对表中的所有记录加锁，
#           此时就会升级称为表锁（表共享读锁[其他连接可看，不可写]）。
start transaction;
update  t_int set name='1号号' where age=1;-- age字段没设置索引。
commit;
rollback;


-- 间隙锁和临键锁
#1. 索引上的等值查询（唯一索引[主键]）,给不存在的记录加锁，优化为间隙锁。
start transaction;
# 存在age=8,不存在age=11以及中间的主键。锁的是8~9,9~10,10~11 三个间隙  间隙锁
update t_student set name ='11号' where age = 11;
/*
另一个事务执行insert into t_student values ('小鸟9号','男',9);就会阻塞。
直到本次事务提交，释放 间隙锁。

假如哈，就是 id 主键为1，2，3，4，5,然后
update t_student set name ='11号' where age = 11;
就会锁住 5到~正无穷，如果成功的插入了  id为20的记录，那么
update t_student set name ='11号' where age = 11;
就会锁住id 5~20 的间隙。
*/
commit ;

#2. 索引上的等值查询（普通索引），向右遍历时，
# 最后一个值不满足查询需求时，next -key lock优化成间隙锁
# 就是把 普通索引为age=3的这个记录添加行锁，以及(到下一个值不相等的)记录前和记录后的间隙锁
select * from t_int where age=3;--这条SQL不会加任何锁。
select * from t_int where age=3 lock in share mode;--这条SQL不会加S锁和(到下一个值不相等的)记录前和记录后的间隙锁。


#3.  索引上 范围查询（唯一索引[主键]），会访问到不满足条件的第一个值为止。
# 会给最小满足当前条件的记录加临键锁，给满足的范围增加间隙锁。
select * from t_int where id>=5; --同理：这条SQL不会加任何锁；
select * from t_int where id>=5 lock in share mode; --会给最小满足当前条件的记录加临键锁，给满足的范围增加间隙锁。

InnoDB引擎

逻辑存储结构

表空间(idb文件)：一个mysql实例可以对应多个表空间，用于存储记录索引等数据。

段：分为数据段，索引段，回滚段，Innodb是索引组织表，数据段就是B+数的叶子结点，索引段即为B+数的非叶子节点。段用来管理多一个区(Extent)

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

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

行：Innodb 存储引擎存数据是按行进行存放的。隐藏字段：1.Trx_id:每次对某条记录进行改动，都会把事务id赋值给Trx_id隐藏列。2.Roll_pointer:每次对某条记录进行改动时，都会将旧的版本写入到undo日志中，然后这个roll_pointer就相当于一个指针，指向上次被修改的记录位置(undo log缓冲区内)

架构

内存结构

Buffer Pool

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

缓冲池以Page页为单位，底层采用链表数据结构管理Page. 根据状态，将Page分为三种类型：

free page:空闲page,未被使用
clean page:被使用page,数据没有被修改过
dirty page:脏页，被使用page,数据被修改过，页中数据和磁盘中数据产生不一致。

Change Buffer

在MySQL 5.5 ，5.6，5.7版本叫Insert Buffer

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

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

Adaptive Hash Index

自适应hash索引，用于优化对Buffer Pool 数据的查询。InnoDB存储引擎会监控对表上个索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称为自适应hash索引，自适应hash索引，不需要人工干涉，是系统根据自身的情况自动生成的。默认adptive_hash_index是开启状态。

Log Buffer

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

参数： innodb_log_buffer_size:缓冲区大小

innodb_flush_log_at_trx_commit:日志刷新到磁盘时机，分为三种情况

1. 日志在每次事务提交时，写入并刷新到磁盘
2. 每秒将日志写入并刷新到磁盘一次
3.日志在每次事务提交后写入，每秒刷新到磁盘一次

磁盘结构

System Tablespaces

系统表空间：系统表空间是更改缓冲区的存储区域。如果表是系统表空间而不是每个表文件或通用表空间创建的，它也可能包含表和索引数据。（在MySQL5.x版本中还包含Innodb数据字典，undolog等）

参数：innodb_data_path

File_Per_Table Tablespaces

File_Per_Table_Tablespaces:每个表的文件表空间包含单个的Innodb表的数据和索引，并存储在文件系统某个数据文件中。

General Tablespaces

通用表空间：需要通过SQL语句创建，在创建表的时候可以指定该表的表空间。

create tablespace  MyTablespaceName add datafile 'file_name' engine=innodb;

Temporary Tablespaces

临时表空间：存储用户创建的临时表等数据。

Doublewrite Buffer Files

双写缓冲区，innodb引擎将数据页从Buffer Pool 刷新到磁盘前，先将数据页写入到双写缓冲区文件中，便于系统异常时，恢复数据。默认有 A.dblwr和B.dblwr两个文件。

Undo Tablespaces

撤销表空间：mySQL实例在初始化时，会自动创建两个默认的undo 表空间（初始化的小为16M）,用于存储undo log 日志。默认是 undo_001和undo_002;

Redo Log

重做日志：是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲区(redo log Buffer) 和重做日志文件（redo log）,前者是在内存中，后者是在磁盘中。当事务提交后，会把所有修改信息都存到该日志中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用。以循环的方式写入重做日志文件，涉及两个文件：ib_logfile0和ib_logfile1;

保证持久性：将数据页的物理变化记录到redo log buffer 缓冲区，当事务提交后，再通过后台线程写入磁盘中的ib_logfile01文件，如果脏页直接刷新到磁盘中发生错误，可以通过 redoLog 恢复。

后台线程

1. Master Thread

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

2. IOThread

在Innodb引擎中大量使用了AIO 来处理IO请求，这样可以极大的提高数据库的性能，而IOThread主要扶着这些IO请求的回调。

线程类型	默认个数	职责
Read Thread	4	负责写操作
Write Thread	4	负责读操作
Log Thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区刷新到磁盘

3. Purge Thread

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

4. Page Cleaner Thread

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

事务原理

事务的四大特性：原子性，持久性，一致性，隔离性

redo Log

保证事务的持久性：将数据页的物理变化记录到redolog buffer 缓冲区，当事务提交后，再通过后台线程写入磁盘中的ib_logfile01文件，如果脏页直接刷新到磁盘中发生错误，可以通过 redoLog 恢复。

undo Log

回滚日志，用于巨鹿数据被修改之前的信息，作用包含两个：提供回滚和MVCC （多版本并发控制）。

undo log 和 redo log记录物理日志不一样，他是逻辑日志。可以认为当delete 一条记录时，undo log会记录一条对应的insert 记录，反之当update 一条记录时，他记录一条对应相反分update记录。当执行rollback时，就可以从undo log 的逻辑记录读取到相应的内容并进行回滚。

保证事务的原子性；

MVCC

作用：当快照读的时候，通过MVCC查看记录的历史版本。

当前读：读取的是记录的最新版本，读取时还需要保证其他事务不能修改当前记录，会对读取的记录加锁，如 select …lock in share mode(S) ,select …for update ,update,insert ,delete(X) 都是当前读。
快照读：简单的select （不加锁）就是快照读，快照读时记录数据的课件版本，有可能是历史数据，不加锁是非阻塞读。

Read Committed :每次select ，都会生成一个快照读，快照读和当前读读取的是一样的。因为每次select都会去读取最新的快照数据。

Repeatable Read: 开启事务第一个select 语句才是快照读的地方，后续的select 会复用第一个select 执行快照读生成的ReadView ，可能会出现读取的数据不是最新数据的情况

Serializable (串行化)：快照读退化为当前读，默认就加锁。快照读和当前读同时存在就会导致幻读。如果在RR级别实现这个的话，第一次select 就用select …lock in share mode 或 for update 先加锁，变为当前读。

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

隐藏字段

DB_TRX_ID 最近修改事务ID, 记录插入这条记录或最后一次修改该记录的事务ID

DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用户配合undo log ，指向上一个版本。

DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会声称该隐藏字段。

undo log 日志

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

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

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

记录信息：

id	name	age	DB_TRX_ID	DB_ROLL_PTR	ROW_ID（有主键就没它）
1	肖猛	18	(事务)1	(指向undo log 记录版本链中的一项)ox1111	本记录没这一项

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

ReadView

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

ReadView 作用：选取合适的undo log 版本链中的合适的记录副本。

Read View 包含四个核心字段：

字段	含义
m_ids	当前活跃的事务id 集合
min_trx_id	最小活跃事务id
max_trx_id	预分配事务id ,当前最大事务id+1（事务id 是自增的）
creator_trx_id	ReadView创建者的事务 id

版本链数据访问规则
 trx_id ：代表版本链中的任意一个id，可以从大往小去向下匹配，从而确定快照读生成的是哪一个版本的ReadView。
1. trx_id == creator _trx_id  可以访问该版本   说明：数据是当前这个事务更改的。
2. trx_id<min_trx_id  可以访问该版本   说明: 数据已经提交了。
3. trx_id >max_trx_id   不可以访问该版本   说明： 该事务是在ReadView生成后才开启的。
4. min_trx_id <= trx_id<= max_trx_id   而且 trx_id 不在m_ids中 可以访问该版本。

不同的隔离级别生成ReadView 的时机不同
read committed: 在事务中每次执行快照读时，生成ReadView
repeatable read: 仅在事务中第一次执行快照读时，生成ReadView,后续复用该ReadView

总结

undo log 保证事务的原子性
redo log 保证事务的持久性
redo log 和 undo log 保证事务的一致性。
隐藏字段，Undo log版本链，ReadView 完成MVCC ,MVCC结合锁，保证事务隔离性。

MySQL管理

系统数据库

数据库	含义
mysql	存储MySQL服务器正常运行所需要的各种信息(时区，主从，用户，权限等)
information_schema	提供了访问数据库元数据的各种表和视图，包含数据库，表，字段等类型及访问权限
performance_schema	为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys	包含了一系列方便数据库管理员和开发人员利用performance_schme性能调优和诊断的视图