注：本篇博客大部分是黑马pdf中的内容，根据我自己学习的实际情况进行修改和补充。

准备工作

准备好虚拟机并安装好mysql

存储引擎

概念：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

MySQL体系结构

参考博客数据库工作原理

100_演示图_01.png

连接层
最上层是一些客户端和链接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
存储层
数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

语法

创建表时指定存储引擎

CREATE TABLE 表名(
	字段1 字段1类型 [ COMMENT 字段1注释 ] ,
	......
	字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = 存储引擎 [ COMMENT 表注释 ] ;

1
2
3

-- MySQL中默认的储存引擎为InnoDB
-- 示例 使用默认的存储引擎创建表
show create table account;

001_存储引擎_01.png

-- 创建表my_myisam,并指定MyISAM储存引擎
create table my_myisam(
                          id int,
                          name varchar(10)
) engine = MyISAM;
-- 以ibd为后缀的都是使用InnoDB引擎的表

001_存储引擎_03.png

查询当前数据库支持的存储引擎

语法：show engines;

001_存储引擎_02.png

可以看到"engine"这一行中InnoDB的"Support"字段为"DEFAULT",也就是默认的意思。

存储引擎的区别和特点

重点讲解InnoDB，MyISAM和Memory存储引擎的特点

介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。
特点
DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。参数：innodb_file_per_table

1	show variables like 'innodb_file_per_table';

001_存储引擎_04.png

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。我们直接打开MySQL的数据存放目录：C:\ProgramData\MySQL\MySQL Server 8.0\Data(按照你MySQL的安装路径修改)，这个目录下有很多文件夹，不同的文件夹代表不同的数据库.任意打开一个自建的文件夹，就会发现里面有许多后缀为.ibd的文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

001_存储引擎_05.png

逻辑存储结构

001_存储引擎_06.png

表空间(Tablespace): InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
段(Segment): 表空间是由各个段组成的，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段
（Rollback segment）等,InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
区(Extent): 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
页(Page): 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行(Row): InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段.一个是Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。；另一个是Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

介绍
MyISAM是MySQL早期的默认存储引擎。
特点
不支持事务，不支持外键;支持表锁，不支持行锁;访问速度快
文件
xxx.sdi：存储表结构信息；xxx.MYD: 存储数据；xxx.MYI: 存储索引

介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点
内存存放；hash索引（默认）
文件
xxx.sdi：存储表结构信息

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6版本之后)	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

具体其他细节可以参考官方文档：MySQL :: MySQL 8.0 Reference Manual :: 17.1 Introduction to InnoDB

存储引擎的选择

很多情况下技术的选择都具有局限性和折中性，不存在完美的选择，只有相对好的选择。

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

索引

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

有索引和无索引搜索时间比较

002_存储索引_01.png

在表中数据较少的时候相差不大，但如果是千万级别的数据，差距就尤其明显。这是因为在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，其性能很低。如果我们针对于这张表建立了索引 (比如二叉树) ,查询时就会按照二叉树的索引进行查找，二叉树搜索和穷举法哪个时间复杂度更小，不用我多说了吧（￣︶￣）。

索引的优点在于提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗，其缺点是索引列也是要占用空间的；索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

不同的存储引擎支持的索引结构不同，主要包含以下几种。

索引结构	描述
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+数结构组织的索引

二叉树

以下内容涉及到数据结构，有数据结构基础的读者会更容易理解。

缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。使用红黑树也会存在层级较深的问题。

B-Tree与B+Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针

100_演示图_02.png

读者可以通过此网站观看B树数据插入的方式和节点的变化。B-Tree Visualization 就会发现

5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图

100_演示图_03.png

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。这样看来B+树和B-树有以下区别

所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

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

100_演示图_04.png

Hash

概念：哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

100_演示图_05.png

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

100_演示图_06.png

特点

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
无法利用索引完成排序操作
查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持
在MySQL中，支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

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

聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种

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

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

100_演示图_07.png

聚集索引的叶子节点下挂的是这一行的数据 ,二级索引的叶子节点下挂的是该字段值对应的主键值.

其具体的查找过程如下

100_演示图_08.png

由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
最终拿到这一行的数据，直接返回即可。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。由此我们可以推断，聚集索引搜索比二级索引搜索效率要高。

计算题：计算InnoDB主键索引的B+Tree高度。

查看答案

解：设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
当树的高度为2：n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170，得1171* 16 = 18736
也就是说，如果树的高度为2，则可以存储 18000 多条记录。

索引语法

查看索引

语法：show index from table_name;

1
2
3

-- 展示索引
show index from tb_user;
-- 在linux中输出结果表格断行，建议末尾加上\G,其他情况的表格断行也可以使用

002_存储索引_02.png

创建索引

语法：CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

1 2	-- 创建一般索引 create index idx_user_name on tb_user(name);

002_存储索引_03.png

1 2	-- 创建唯一索引 create unique index idx_user_phone on tb_user(phone);

002_存储索引_04.png

1 2	-- 创建联合索引 create index idx_user_pro_age_sta on tb_user(profession,age,status);

002_存储索引_05.png

删除索引

语法：DROP INDEX index_name ON table_name

1 2	-- 删除索引 drop index idx_user_name on tb_user(name);

SQL性能分析

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

-- sql执行频率
-- session 是查看当前会话
-- global 是查询全局数据
show global status like 'Com_______';

003_SQL性能分析_01.png

这其中Com_delete是删除次数 Com_insert是插入次数 Com_select是查询次数 Com_update是更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

show variable like "slow_query_log"
-- 结果为
-- +----------------+-------+
-- | Variable_name  | Value |
-- +----------------+-------+
-- | slow_query_log | OFF    |
-- +----------------+-------+

在MySQL中慢查询默认关闭，如果要开启慢日志，需要先退出MySQL(quit)在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

-- 开启MySQL慢日志查询开关
slow_query_log=1
-- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

003_SQL性能分析_02.png

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

1
2
3

-- 重启MySQL服务器
systemctl restart mysqld
-- 其慢查询文件放在cd /var/lib/mysql 下，文件名为localhost-slow.log(我这里是hspEdu01-slow.log)

再次查询后就会发现OFF变成了ON

这样当以后查询的时间超过2秒的时候，就会视为慢查询，然后记录在慢查询日志里，如下

003_SQL性能分析_04.png

profile

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作

1 2	-- 查看是否支持profile select @@have_profiling;

003_SQL性能分析_05.png

-- 查看profile是否开启
select @@profiling;
-- 结果
-- +-------------+
-- | @@profiling |
-- +-------------+
-- |           0 |
-- +-------------+

可以看到MySQL支持profile操作但是没有开启,可以通过set语句在(session/global级别开启profiling)

1 2	-- 开启profile set profiling = 1;

测试一下

-- 测试
select * from tb_user where id =1;
select * from tb_user where name='白起';
-- 查看每一条SQL的耗时基本情况
show profiles ;

003_SQL性能分析_06.png

1 2	-- 查看指定ID的耗时情况 show profile for query 55;

003_SQL性能分析_07.png

1 2	-- 查看指定ID的占用CPU的情况 show profile cpu for query 92;

003_SQL性能分析_08.png

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
-- 示例
-- 在语句前添加explain就行
explain select * from tb_user where id = 1;

003_SQL性能分析_09.png

解释

字段	含义
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 的值越大越好。

索引使用

演示索引

1 2	-- 演示 explain select s.,c. from student s,course c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;

003_SQL性能分析_10.png

-- 嵌套语句的分析
explain select * from student s where s.id in (
    select studentid from student_course sc where sc.courseid = (
        select id from course c where c.name = 'MySQL'
    )
);

003_SQL性能分析_11.png

如果是数据量大的数据库，在查询的时候有索引和没有索引是两个效率，所以对于数据量大、经常被查询的表，创建索引是非常有效的方法。

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

1 2	-- 查询从索引的最左列开始，并且不跳过索引中的列 explain select * from tb_user where age = 31 and status = '0';

003_SQL性能分析_12.png

讲解：假设存在一张tb_user 表，其中有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。比如上面的在查询的时候只有age和status 没有profession 所以索引失效(NULL).

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

范围查询

联合索引中，出现范围查询，则范围查询右侧的列索引失效

1 2	-- 范围查询 explain select * from tb_user where profession = '软件工程' and age>30 and status = '0';

003_SQL性能分析_13.png

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <.

不要在索引列上进行运算操作，否则索引失效

1	explain select * from tb_user where substring(phone,10,2) = '15';

003_SQL性能分析_14.png

字符串不加引号会失效:原因 存在隐式类型转换

1	explain select * from tb_user where phone = 17799990013;

003_SQL性能分析_15.png

头部模糊匹配会导致索引失效

1	explain select * from tb_user where profession like '%工程';

003_SQL性能分析_16.png

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

1	explain select * from tb_user where id = 10 or age = 23;

003_SQL性能分析_17.png

当mysql评估使用索引比全表搜索慢，就不会使用索引

1	explain select * from tb_user where phone >='17799990000';

003_SQL性能分析_18.png

还有一种情况是使用is null 和is not null 是否走索引并不固定

SQL提示

-- SQL提示
-- 准备索引
create index idx_user_pro on tb_user(profession);
-- 一般情况下此SQL语句使用的索引
explain select * from tb_user where profession = '软件工程';

003_SQL性能分析_19.png

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示(指定MySQL使用哪些索引)来达到优化操作的目的。

建议MySQL使用指定索引： use index; 有可能MySQL不接受

1 2	-- 使用单列索引某些情况下mysql可能不接受 explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

003_SQL性能分析_20.png

忽略指定索引：ignore index;

1 2	-- 忽略某个索引 explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

003_SQL性能分析_21.png

强制使用指定索引：force index;

1 2	-- 强制使用某个索引 explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

1
2

-- 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。也尽量减少使用select *
explain select id,profession,age,status,name from tb_user where profession = '软件工程' and age = 31 and status = '0';

003_SQL性能分析_22.png

其中我们重点关注Extra

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引），所以不建议使用select * 进行全字段查询。

演示覆盖索引与回表查询

100_演示图_09.png

其中id是主键，是一个聚集索引。 name字段建立了普通索引，是一个二级索引（辅助索引）。

100_演示图_10.png

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

100_演示图_11.png

虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索
引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

100_演示图_12.png

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

前缀索引

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

语法:create index idx_xxxx on table_name(column(n)) ;

1 2	-- 创建前缀索引 create index idx_email_5 on tb_user(email(5));

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

1 2	-- 选择性当选择性趋于1(也就是唯一索引)的时候效率最高 select count(distinct substring(email,1,10))/count(*) from tb_user;

003_SQL性能分析_23.png

展示索引

1 2	-- 展示索引 show index from tb_user;

003_SQL性能分析_24.png

前缀索引的查询流程

100_演示图_13.png

单列索引与联合索引

单列索引：一个索引只包含单个列；联合索引：一个索引包含多个列。

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

索引设置原则

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

SQL优化

插入优化

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

使用批量插入

-- 优化前
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

-- 优化后
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;

主键顺序插入

主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89 效率要高于乱序

大批量数据插入(万级别)

使用MySQL的load指令，也就是读取sql文件。

-- 1. 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 2. 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 3. 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.sql(数据的路径)' into table tb_user(指定的表) fields terminated by ',' lines terminated by '\n' ; -- 读取数据时以逗号为间隔读取单个数据，当读取到\n自动换行。

003_SQL性能分析_25.png

主键优化

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

见图100_演示图_04 其逻辑结构图见图 001_存储引擎_06

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

此处留空:做三个manim动画演示顺序、乱序、页合并的步骤

顺序插入步骤如下

从磁盘中申请页，主键顺序插入
第一个页没有满，继续往第一页插入
当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
当第二页写满了，再往第三页写入

乱序插入步骤如下

假如1、2页已满
再插入一个id为1、2页之中的数据，会直接开辟一个页(页3).
然后将1页后一半的数据放在刚开辟的页，再插入数据。
然后设置链表指针，为1->3->2

页合并：

持续删除某个页的记录，看是否达到MERGE_THRESHOLD（默认为页的50%)
如果达到界限，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
然后删除数据，并将页合并之后，如果再次插入新的数据，就会在后面的页面添加

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

索引设置原则

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改

order by优化

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

测试

1 2	-- sql优化，由于字段没有索引，所以用filesort explain select id, age,phone from tb_user order by age;

004_SQL优化_01.png

1
2
3

-- 创建索引
create index idx_user_phone on tb_user(phone);
explain select id,age,phone from tb_user order by phone,age;

建立索引之后，再次进行排序查询，就由原来的Using filesort，变为了 Using index，性能就是比较高的了。

排序时,也需要满足最左前缀法则,否则也会出现 filesort。

order by优化原则

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

group by优化

没有索引情况下的查询

1 2	-- 没有索引情况下的查询 explain select profession,count(*) from tb_user group by profession;

004_SQL优化_02.png

对于分组操作，在联合索引中，也是符合最左前缀法则的。

有索引情况下的查询

1
2
3

-- 创建联合索引之后再次执行
create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select profession,count(*) from tb_user group by profession;

004_SQL优化_03.png

group by优化原则

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

1 2	-- 在给联合索引命名时注意从左到右命名，方便查看是否符合最左前缀法则，如下 explain select age,count(*) from tb_user where profession = '软件工程' group by age;

004_SQL优化_04.png

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

-- 优化前
select * from tb_user order by id limit 10,10;
-- 优化后，页数越多优化效果越明显
explain select s.* from tb_user s,(select id from tb_user order by id limit 10,10) a where s.id = a.id;

count优化

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

count的用法

count用法	含义
count(主键)	InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)	没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(数字)	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

update优化

1	update course set name = 'javaEE' where id = 1;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放

当我们在执行如下SQL时

1	update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁。

视图

语法

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

创建视图

语法：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ] ;

1
2
3

-- 创建视图 会生成一个视图的文件夹，里面存放视图
-- or replace 是如果有就覆盖
create or replace view stu_v_1 as select id,name from student where id <=10;

查询视图

语法：查看创建视图语句：SHOW CREATE VIEW 视图名称; 查看视图数据：SELECT * FROM 视图名称 ...... ;

-- 查询创建视图语句
show create view stu_v_1;
-- 查看视图数据
select * from stu_v_1;

005_视图_01.png

修改视图

语法：方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]; 方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ] ;

-- 修改视图
-- 方式1
create or replace view stu_v_1 as select id,name,no from student where id <=10;
-- 方式2
alter view stu_v_1 as select id,name from student where id <=10;

005_视图_02.png

删除视图

语法：DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ... ;

1 2	-- 删除视图 drop view if exists stu_v_1;

视图检查

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

CASCADED(级联)
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
LOCAL(本地)

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创
建时未指定检查选项。则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

视图更新

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

聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION 或者 UNION

-- 视图的更新和作用
-- 前提：视图中的行与基础表中的行之间必须存在一一对应的关系。
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);

005_视图_05.png

-- 创建一个视图
create or replace view stu_v_1 as select id,name from student where id <=20 with cascaded check option ;
select * from learning.stu_v_1;
insert into learning.stu_v_1 values(6,'Tom');-- 视图本身不能插入数据，会将数据插入原来的表中
-- 检查30>20 所以报错
insert into learning.stu_v_1 values(30,'Tom');

005_视图_03.png

-- 基于视图创建视图
create or replace view stu_v_2 as select id,name from learning.stu_v_1 where id<=10;
-- 测试插入数据
insert into stu_v_2 values(18,'Jerry');
-- 也就是说，如果基于视图创建视图但不添加检查选项时，就会依赖于父视图进行检查。

005_视图_04.png

视图的优点

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

视图的应用

举例

-- 视图有点像是定制化的备份
-- 案例
-- 屏蔽手机号和邮箱
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
select * from tb_user_view;

005_视图_06.png

1
2
3

-- 通过视图实现三表联查
create view tb_stu_course_view as select s.name student_name,s.no student_no,c.name course_name from student s,student_course sc,course c where s.id = sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

005_视图_07.png

存储过程

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

特点：

封装，复用。可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
可以接收参数，也可以返回数据。在存储过程中，可以传递参数，也可以接收返回值。
减少网络交互，效率提升。如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。
创建存储过程

语法：

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
	-- SQL语句
END ;

-- 存储过程
create procedure p1()
begin
    select count(*) from student;
end;

006_储存过程_01.png

调用存储过程

语法：CALL 名称 ([ 参数 ]);

1 2	-- 调用 call p1();

006_储存过程_02.png

查看存储过程

语法：

1
2

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

1 2	-- 查看数据库的存储过程 select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'learning';

006_储存过程_03.png

1 2	-- 查看存储过程的创建语句 show create procedure p1;

006_储存过程_04.png

删除存储过程

语法：DROP PROCEDURE [ IF EXISTS ] 存储过程名称;

1 2	-- 删除存储过程 drop procedure if exists p1;

注意:在命令行中(Linux使用MySQL)，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

delimiter $$
create procedure p1()
begin
    select count(*) from student;
end$$

006_储存过程_05.png

变量

系统变量

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

查看系统变量

语法:

1
2
3

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

-- 查看系统变量
show session variables ;
-- 模糊匹配
show global variables like 'auto%';
-- 查看某一个变量
select @@autocommit;

007_变量_01.png

设置系统变量

语法:

1 2	SET [ SESSION \| GLOBAL ] 系统变量名 = 值 ; SET @@[SESSION \| GLOBAL]系统变量名 = 值 ;

1
2
3

-- 设置系统变量
set session autocommit = 0;
-- mysql服务重新启动后所设置的全局参数会失效，如果要永久改变需要在/etc/my.cnf中配置

概念：

全局变量(GLOBAL): 全局变量针对于所有的会话。

会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

未指定session或者global的时候默认是session

用户定义变量

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

赋值

语法1

1 2	SET @var_name = expr [, @var_name = expr] ... ; SET @var_name := expr [, @var_name := expr] ... ;

1
2
3

-- 自定义变量 冒号可加可不加,建议表示赋值的时候加
set @myname = 'Lloyd';
set @myage := 18;

语法2

1 2	SELECT @var_name := expr [, @var_name := expr] ... ; SELECT 字段名 INTO @var_name FROM 表名;

使用自定义变量

-- 使用
select @myname,@myage;
-- 将查询的结果赋值给某变量
select count(*) into @mycount from tb_user;

007_变量_02.png

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

局部变量

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

声明

语法：DECLARE 变量名变量类型 [DEFAULT ... ] ;

-- 声明 使用关键字declare 一旦超出范围无法使用
create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;

call p2();

赋值

语法：

1
2
3

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

常使用的“关键字”

if

和编程语言一样。

语法

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN -- 可选
	.....
ELSE -- 可选
	.....
END IF;

示例

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

call p3();

参数

类型如下

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

语法

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

示例

-- 存储过程的参数
-- 输入的参数是score 输出的参数是result
create procedure p4(in score int,out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(68,@result);
select @result;-- 返回结果为及格

case

和C语言的case基本一样

语法1

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
CASE case_value
	WHEN when_value1 THEN statement_list1
	[ WHEN when_value2 THEN statement_list2] ...
	[ ELSE statement_list ]
END CASE;

语法2

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2] ...
	[ELSE statement_list]
END CASE;

示例

-- case
-- 案例：按照季度展示数据
create procedure p6(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 p6(4);

while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

示例

-- while
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)
begin
    declare total int default 0;
    while n > 0 do
        set total := total + n;
        set n:= n-1;
    end while;
    select total;
end;
-- 从1到10累加循环
call p7(10);

008_循环_01.png

repeat

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。和do ...while 很像。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑...
	UNTIL 条件
END REPEAT;

示例

-- repeat
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
    declare total int default 0;

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

    select total;
end;

call p8(10);-- 结果同图008_循环_01 为55

loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

语法

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];
-- 
-- 以上所有的label都是自定义的标记
LEAVE label; -- 退出指定标记的循环体 相当于break
ITERATE label; -- 直接进入下一次循环 相当于continue

示例

-- loop
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p9(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then  -- 终止条件
            leave sum;
        end if;

        -- 判断是否为奇数
        if n%2 = 1 then
            set n := n-1;
            iterate sum;-- iterate 表示进入下一次循环，相当于continue
        end if;

        set total := total + n;
        set n := n-1;
    end loop sum;
    select total;
end;

-- 累加偶数
call p9(10); -- 结果为 30

游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法为

声明游标

语法：DECLARE 游标名称 CURSOR FOR 查询语句 ;

打开游标

语法：OPEN 游标名称;

获取游标记录

语法：FETCH 游标名称 INTO 变量 [, 变量 ] ;

关闭游标

语法：CLOSE 游标名称;

以上语句在不使用条件处理程序的时候虽然能实现需求，但是MySQL会报错，在逻辑上是有漏洞的，需要增加条件处理程序进行修补。

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤(相当于try.. default)

语法为

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代码的简写

示例

-- 储存查询结果集的数据类型
-- 声明游标
-- 添加条件处理程序
-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p10(in uage int)
begin
    -- 声明一个游标并记录查询结果的表
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    -- 当状态码为02000时 将游标关闭
    declare exit handler for SQLSTATE '02000' close u_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 u_cursor;
    -- 循环获取表中的记录
    while true do
        -- 获取游标中的记录并复制给相应的变量
        fetch u_cursor into uname,upro;
        -- 插入到表中
        insert into tb_user_pro values(null,uname,upro);
    end while;
    -- 关闭游标
    close u_cursor;
end;
-- 查询年龄小于40的用户并将其插入表中
call p10(40);

错误状态码参考网站

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法为

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
	-- SQL语句
	RETURN ...;
END ;
-- characteristic说明
-- DETERMINISTIC：相同的输入参数总是产生相同的结果
-- NO SQL ：不包含 SQL 语句
-- READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

示例

-- 存储函数
create function fun1(n int) -- 不写默认为in
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;
-- 如果报错就登录Linux的mysql，然后输入set global log_bin_trust_function_creators = 1;即可

-- 调用函数
select fun1(50);

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定characteristic特性，否则就会报错

触发器

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

类型

触发器类型	NEW 和 OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

创建

语法：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;

-- 建表时创建触发器
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;
-- 插入时创建触发器
-- 插入时的触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
        (null,'insert',now(),NEW.id,concat('插入的数据内容为:id=',NEW.id,',name=',NEW.name,',phone = ',NEW.phone,',email=', NEW.email,',profession=',NEW.profession));
end;

查看

语法：SHOW TRIGGERS;

1 2	-- 查看触发器 show triggers ;

009_触发器_01.png

删除

语法：DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库;

1 2	-- 删除触发器 drop trigger tb_user_insert_trigger;

示例，测试触发器是否在插入的时候正常出发

-- 测试-插入数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());



-- 测试-更新数据
-- 修改数据的触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
        (null,'update',now(),NEW.id,concat('更新前的数据内容为:id=',OLD.id,',name=',OLD.name,',phone = ',OLD.phone,',email=', OLD.email,',profession=',OLD.profession,'
           | 更新后的数据内容为:id=',NEW.id,',name=',NEW.name,',phone = ',NEW.phone,',email=', NEW.email,',profession=',NEW.profession));
end;

update tb_user set age = 20 where id = 23;



-- 测试-删除数据
-- 删除数据触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
        (null,'delete',now(),OLD.id,concat('删除前的数据内容为:id=',OLD.id,',name=',OLD.name,',phone = ',OLD.phone,',email=', OLD.email,',profession=',OLD.profession));
end;

delete from tb_user where id = 25;

插入后

009_触发器_02.png

更新后

009_触发器_03.png

删除后

009_触发器_04.png

锁

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

分类为

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

全局锁

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

原理：比如先备份了库存表，在备份订单表之前就被修改了，然后导致备份的订单表和之前备份的库存表对不上。

解决方案：对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

加全局锁

语法：flush tables with read lock;

1 2	-- 添加全局锁 flush tables with read lock ;

数据备份

语法：mysqldump -uroot –p用户密码指定的表 > 指定名字.sql ;

1 2	-- 备份(windows系统) -- mysqldump -h虚拟机的IP -uroot -p你的密码 learning > D:/copy/learning.sql 要保存到的路径

释放锁

语法：unlock tables;

加全局锁会有以下问题

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

010_锁_01.png

当解锁之后才能进行更新

010_锁_02.png

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

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

1 2	-- InnoDB引擎中，不加锁的一次性备份 -- mysqldump --single-transaction -h虚拟机的IP -uroot -p你的密码 learning >D:/code/remove/learning.sql

表级锁

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

分类

表锁
- 表共享读锁(read lcok)
- 表独占写锁(write lock)
元数据锁(meta data lcok,MDL)
意向锁

表锁

添加锁：

语法：lock tables 表名... read/write;

-- 添加只读锁
lock tables course read;
-- DQL语句
select * from course where name = 'Java';

010_锁_03.png

在设置共享读锁的时候只能读不能写

1 2	-- 不能写 insert into course values (5,'Linux');-- 如果是其他的进程尝试写会阻塞

010_锁_04.png

释放锁：unlock tables;

1 2	-- 释放表锁 unlock tables ;

010_锁_05.png

示例

-- 添加独写锁
lock tables course write ;
-- 可读可写
insert into course values (5,'Linux');-- 其他的进程尝试读写会阻塞
-- 释放
unlock tables ;

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

meta data lock , 元数据锁，简写MDL。MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。
常见的SQL操作时，所添加的元数据锁

对应SQL	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode	SHARED_READ	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
insert 、update、 delete、select … for update	SHARED_WRITE	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
alter table …	EXCLUSIVE	与其他的MDL都互斥

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ /SHARED_WRITE），之间是兼容的，也就是允许并行开发。

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

010_锁_06.png

事务提交之后

010_锁_07.png

查看数据库中的元数据锁

1 2	-- 查看元数据锁 select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

010_锁_08.png

意向锁

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

开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

分类

意向共享锁(IS): 由语句select … lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
意向排他锁(IX): 由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

查看意向锁及行锁的加锁情况

1 2	select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

010_锁_09.png

注意：意向共享锁与表读锁是兼容的、意向排他锁与表读锁、写锁都是互斥的

行锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的
锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

100_演示图_15.png

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

100_演示图_16.png

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

100_演示图_17.png

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

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他
锁

当前锁类型\请求锁类型	S(共享锁)	X(排他锁)
S(共享锁)	兼容	冲突
X(排他锁)	冲突	冲突

常见的SQL语句，在执行时，所加的行锁如下：

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 锁进行搜索和索引扫描，以防止幻读。

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

示例

010_锁_10.png

兼容性

010_锁_11.png

010_锁_12.png

010_锁_13.png

010_锁_14.png

010_锁_15.png

如果没有索引，更新语句行锁会升级为表锁(因为行锁是对索引项加的锁，而某字段没有索引) ，这样我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。

间隙锁和临键锁

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

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

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

010_锁_16.png

图中方框中的GAP就是间隙锁

常见的上锁情况

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。

分析

我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁。

索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。比如查询的条件为id>=19，并添加共享锁。此时我们可以根据数据库表中现有的数据，将数据分为三个部分：[19]、 (19,25]、 (25,+∞] 所以数据库数据在加锁时，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

InnoDB引擎

逻辑存储结构见001_存储引擎_06.png和逻辑存储结构部分

参考黑马和这篇博客InnoDB引擎：底层结构及底层原理，讲解得足够详细。

这一部分的重点在于理解其实现原理，而不是死记硬背。

MySQL管理

系统自带的数据库

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

常用工具

mysql的客户端工具

语法：mysql [options] [database] 在命令行中输入

option

-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便

012_mysql管理_02.png

mysqladmin

是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

语法：mysqladmin --help

012_mysql管理_03.png

语法：mysqlamin [options] command

options:

-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口

示例

1 2	mysqladmin -uroot –p1234 drop 'test01'; mysqladmin -uroot –p1234 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

语法：mysqlbinlog [options] log-files1 log-files2 ...

options:

-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,–result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式，省略掉一些信息。
–start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
–start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

012_mysql管理_04.png

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：mysqlshow [options] [db_name [table_name [col_name]]]

options

–count 显示数据库及表的统计信息（数据库，表均可以不指定）
-i 显示指定数据库或者指定表的状态信息

#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

012_mysql管理_05.png

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

语法

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

连接选项 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项：
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件