零、回顾准备
进阶：
- 存储引擎
- 索引
- SQL优化
- 视图/存储过程/触发器
- 锁
- InnoDB核心
- MySQL管理
一、存储引擎
1、MySQL体系结构
- 连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限；
- 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等；
- 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎；
- 存储层：主要是将数据存储在文件系统上，并完成与存储引擎的交互；
2、存储引擎简介
- 存储引擎：存储引擎就是存储数据、建立索引、更新/查询数据数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎可也被称为表类型。
①在创建表时，指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释]，
......
字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = INNODB [COMMENT 表注释];
②查看当前数据库支持的存储引擎
SHOW ENGINES;3、存储引擎特点
InnoDB
- 介绍：
- InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。
- 特点：
- DML操作遵循ACID模型，支持事务；
- 行级锁，提高并发访问性能；
- 支持外键 FOREIGN KEY约束，保证数据的完整性和正确性；
- 文件
- xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数：innodb_file_per_table。
- 逻辑存储结构
- TableSpace：表空间
- Segment：段
- Extent：区（1M）
- Page：页（16K）
- Row：行
MyISAM
- 介绍：
- MyISAM是MySQL早期的默认存储引擎
- 特点：
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
- 文件：
- xxx.sdi：存储表结构信息
- xxx.MYD：存储数据
- xxx.MYI：存储索引
Memory
- 介绍：
- Memory引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用
- 特点：
- 内存存放
- hash索引（默认）
- 文件：
- xxx.sdi：存储表结构信息
各引擎对比
| 特点 | InnoDB | MyISAM | Meomory |
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 支持（5.6版本后） | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
4、存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
- InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
- MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
二、索引
1、索引概述
① 介绍：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
②优缺点
| 优势 | 劣势 |
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低 |
2、索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
| 索引结构 | 描述 |
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text（全文索引） | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES |
| 索引 | InnoDB | MyISAM | Memory |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
①二叉树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树：大数据量的情况下，层级较深，检索速度慢
②B-Tree（多路查找树）
以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）
③B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
- 相对于B-Tree区别：
- a、所有的数据都会出现在叶子节点
- b、叶子节点形成一个单向链表
④Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）链值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
- hash索引特点
- hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
- 存储引擎支持
- 在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在制定条件下自动构建的。
思考题 ：为什么InnoDB存储引擎选在使用B+tree索引结构？
答：①相对于二叉树，层级更少，搜索效率高；
②对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加数的高度，导致性能降低；
③相对于Hash索引，B+tree支持范围匹配及排序操作；
3、索引分类
| 分类 | 含义 | 特点 | 关键字 |
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
- 在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
| 聚焦索引 | 将数据存储与索引放在一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构叶子节点关联的是对应的主键 | 可以存在多个 |
- 聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个 rowid 作为隐藏的聚集索引
4、索引语法
①创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)
②查看索引
SHOW INDEX FROM table_name;
③删除索引
DROP INDEX index_name ON table_name;5、SQL性能分析
①SQL执行频率
MySQL客户端链接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次
SHOW GLOBAL STATUS LIKE 'Com______'② 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
MySQL的查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2③ profile详情
show profiles能够在做优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
select @@have_profiling;默认profiling是关闭的， 可以通过set语句在session/global级别开启profiling
SET profiling = 1;执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时
# 查看每一条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语句执行过程中表如何连接和连接的顺序
# 直接在select语句之前加上关键字 explain/desc
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
- 表是返回结果的行数占需读取行数的百分比，值越大越好
6、索引使用
索引失效情况
①最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）
②范围查询
联合缩影中，出现范围查询（<,>），范围查询右侧的列索引失效，使用（>=或<=可以避免失效）
③索引列运算
不要在索引列上进行运算操作，索引将失效
④字符串不加引号
字符串类型字段使用时，不加引号，索引将失效
⑤模糊查询
如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。
⑥or连接的条件
用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
⑦数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引
⑧SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的（建议/忽视/强制）
use/ignore/force index:
explain select * from tb_user use/ignore/force index(idx_user_pro) where profeesion = '软件工程';⑨覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。
using index condition : 查找使用了索引，但是需要回表查询数据
using where;using index : 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
⑩前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
create index idx_xxxx on table_name(column(n));- 前缀长度
- 可以根据索引的选择性来确定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;⑪单列索引和联合索引
单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询
7、索引设计原则
①针对于数据量较大，且查询比较频繁的表建立索引；
②针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
③尽量选择区分度较高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
④如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
⑤尽量使用联合索引，减少单列查询，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
⑥要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
⑦如果索引列不能存储NULL值，请在创建表时使用NUT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
三、SQL优化
1、插入数据
①insert 优化
- 批量插入
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');- 手动提交事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;- 主键顺序插入
主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89
- 大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p;
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/xx/xx/xx.log' into table tb_user fields terminated by ',' lines terminated by '/n';2、主键优化
①数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table，IOT）
②页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列
③页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
④主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID（随机字符串）做主键或者是其他自然主键，如身份证号
- 业务操作时，避免对主键的修改
3、order by 优化
①Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
②Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
③优化原则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）
4、group by 优化
①优化原则
- 在分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀法则
5、limit 优化
优化思路：一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
6、count 优化
①不同引擎的count
- MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高
- InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数
②count的几种用法
- count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值
- 用法：count(*)、count(主键)、count(字段)、count(1)
- 排序效率：count(字段) < count(主键) < count(1) ≈ count(*)，尽量使用count(*)
- count(主键)
- InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
- count(字段)
- 没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
- 有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
- count(1)
- InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，在非null行放一个数字‘1’’进去，直接按行进行累加
- count(*)
- InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
- count(主键)
7、update 优化
- InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁
四、视图/存储过程/触发器
1、视图
①介绍
视图（View）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条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 [id exists] 视图名称 [,视图名称]...②视图的检查选项
当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认值为cascaded。cascaded具有传递性，local只针对当前视图
③视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
- 聚合函数或窗口函数（sum(),min(),max(),count()）
- distinct
- group by
- having
- union 或者 union all
④视图的作用
- 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
- 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
- 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响。
2、存储过程
①介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
②特点
- 封装，复用
- 可以接受参数，也可以返回数据
- 减少网络交互，效率提升
③基本语法
- 创建
create procedure 存储过程名称([参数列表])
begin
-- sql语句
end;- 调用
CALL 名称 ([参数]);- 查看
-- 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'xxx';
-- 查询某个存储过程的定义
show create procedure 存储过程名称;- 删除
drop procedure [if exists] 存储过程名称;④变量
- 系统变量
- 系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）
查看系统变量
# 查看所有系统变量
show [ session|global ] variables;
# 可以通过like模糊匹配方式查找变量
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...];*变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
赋值
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名...;⑤if
语法
if 条件1 then
......
elseif 条件2 then --可选
......
else --可选
......
end if;⑥参数
| 类型 | 含义 | 备注 |
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin
--SQL语句
end;⑦case
case case_value
when when_value1 then statement_list1
[when when_value2 then statement_list2]...
[else statement_list]
end case;case
when search_condition1 then statement_list1
[when search_condition2 then statement_list2]...
[else statement_list]
end case;⑧while
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为
# 先判定条件，如果条件为trye，则执行逻辑，否则，不执行逻辑
while 条件 do
SQL逻辑...
end while;⑨repeat
repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为
# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
SQL逻辑...
until 条件
end repeat;⑩ loop
loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用：
- leave：配合循环使用，退出循环
- iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
[begin_label:]loop
SQL逻辑...
end loop [end_label];leave label; --退出指定标记的循环体
iterate label; --直接进入下一次循环⑪游标-cursor
游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法如下。
- 声明游标
declare 游标名称 cursor for 查询语句;- 打开游标
open 游标名称;
- 获取游标记录
fetch 游标名称 into 变量[,变量];- 关闭游标
close 游标名称;⑫ 条件处理程序
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
declare handler_action handler for condition_value [,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代码的简写3、存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型。具体语法如下：
create function 存储函数名称([参数列表])
returns type [characteristic...]
begin
--SQL语句
return ...;
end;
characteristic说明：
deterministic：相同的输入参数总室产生相同的结果
no sql：不包含sql语句
reads sql data：包含读取数据的语句，但不包含写入数据的语句4、触发器
①介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
②触发器类型
| 触发器类型 | NEW和OLD |
| INSERT型触发器 | NEW表示将要或者已经新增的数据 |
| UODATE型触发器 | OLD表示修改之后的数据，NEW表示将要或已经修改后的数据 |
| DELETE型触发器 | OLD表示将要或者已经删除的数据 |
③语法
创建
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row --行级触发器
begin
trigger_stmt;
end;查看
show trigger;删除
drop trigger [schema_name.] trigger_name; --如果没有制定schema_name，默认为当前数据库五、锁
1、概述
①介绍
锁是计算机协调多个进程或线程并访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如果保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
②分类
- 全局锁：锁定数据库中的所有表
- 表级锁：每次操作锁住增长表
- 行级锁：每次操作锁住对应的行数据
2、全局锁
①介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续DML的写语句、DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
②代码
# 添加全局锁
flush tables with read lock;
# 数据备份
# 不是sql语句，在win界面下即可备份
mysqldump -uroot -p1234 itcast > itcast.sql;
# 解锁
unlock tables;③特点
数据库中加全局锁，是一个比较重的操作，存在以下问题：
a、如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
b、如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
3、表级锁
①介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为三类：表锁、元数据锁（meta data lock，MDL）、意向锁
②表锁
对于表锁，分为两类：表共享读锁（read lock）、表独占写锁（write lock）
# 加锁
lock tables 表名... read/write;
# 释放锁
unlock tables / 客户端断开连接读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写
③元数据锁（meta data lcok，MDL）
MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。
在MySQL5.5中引入MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）
| 对应SQL | 锁类型 | 说明 |
| lock tables xxx read / write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | |
| select、select ... lock in share model | SHARED_READ | 与SHARE_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| insert、upadate、delete、select ... for upadate | SHARED_WRITE | 与SHARE_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| alter table ... | EXCLUSIVE | 与其他的MDL都互斥 |
# 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;④意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
意向共享锁（IS）：由语句select ... lock in share model添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
意向排他锁（IX）：insert、upadate、delete、select ... for upadate添加，与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥
# 查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_data from performance_schema.data_locks;4、行级锁
①介绍
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类
a、行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。
b、间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
c、临键锁（Next-Key Lock）：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持。
②类型
a、共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
b、排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。
| S（共享锁） | X（排他锁） | |
| S（共享锁） | 兼容 | 冲突 |
| X（排他锁） | 冲突 | 冲突 |
| SQL | 行锁类型 | 说明 |
| INSERT ... | 排他锁 | 自动加锁 |
| UPDATE ... | 排他锁 | 自动加锁 |
| DELETE ... | 排他锁 | 自动加锁 |
| SELECT（正常） | 不加任何锁 | |
| SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
| SELECT ... FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
③行锁
默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读
a、针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
b、InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁
④间隙锁/临键锁
默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读
a、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
b、索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key 锁退化为间隙锁
c、索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁
六、InnoDB引擎（确实看不懂）
1、逻辑存储结构
①表空间（ibd文件）
一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
②段
分为数据段（Leaf node segmrnt）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）
③区
表空间的单元结构，每个区的大小为1M，默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页
④页
是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区
⑤行
InnoDB存储引擎数据是按行进行存放的
2、架构
内存结构
①Buffer Pool
缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
a、free page：空闲page，未被使用
b、clean page：被使用page，数据没有被修改过
c、dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生了不一致
②Change Buffer
更改缓冲区（针对于菲唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再讲合并后的数据刷新到磁盘中
③Adaptive Hash Index
自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。无需人工干预，是系统根据情况自动完成
④Log Buffer
日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O
磁盘结构
①System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据
②File-Per-Table Tablespaces
每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中
③General Tablespaces
通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间
④Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志
⑤Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据
⑥Doublewrite Buffer Files
双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据
⑦Redo Log
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存在该日志中，用于在刷新脏页到磁盘时、发生错误时，进行数据恢复使用
后台线程
①Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收
②IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调
| 线程类型 | 默认个数 | 职责 |
| Read thread | 4 | 负责读操作 |
| Write thread | 4 | 负责写操作 |
| Log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
③ Purge Thread
主要用于回收事务已经提交了的udno log，在事务提交之后，undo log可能不用了，就用它来回收
④Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞
3、事务原理
①事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
②特性
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据改变就是永久的。
②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 logsegment
4、MVCC（未完，看不懂，后续研究下再写）
基本概念
①当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode（共享锁）、select ... for update、update、insert、delete（排他锁）都是一种当前读
②快照读
简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
Read Committed：每次select，都生成一个快照读
Repeatable Read：开启事务后第一个select语句才是快照读的地方
Serializable：快照读会退化为当前读
③MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readview
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
