全面深入讲解MySQL数据库优化01

最新推荐文章于 2023-04-21 23:38:45 发布

Fengshana

最新推荐文章于 2023-04-21 23:38:45 发布

阅读量900

点赞数

分类专栏： MySql 听课笔记

本文链接：https://blog.csdn.net/qq_43409111/article/details/115195215

版权

听课笔记同时被 2 个专栏收录

25 篇文章 2 订阅

订阅专栏

MySql

6 篇文章 1 订阅

订阅专栏

全面深入讲解MySQL数据库优化01

前言

观看笔记：https://www.bilibili.com/video/BV1fJ41127Rj?from=search&seid=13531213796559959972

本课程作为MySQL高级课程，主要讲解了：

MySQL中的视图/存储过程/触发器/索引等对象的使用
常见的SQL语句优化的技巧
应用优化
数据库优化
数据库日志

等方面的知识，并通过综合案例，对课程中的知识进行一个整合应用。

本课程旨在通过MySQl高级部分内容，可以在满足现有业务需求基础上，对MySQL底层的体系结构，及底层的优化有一个深入的理解，对系统的整体性能进行提升。

课程内容介绍

Mysql高级-day01

MySQL高级课程简介

Linux系统安装MySQL
索引
视图
存储过程和函数
触发器

MySQL高级课程简介

序号	Day01	Day02	Day03	Day04
1	Linux系统安装MySQL	体系结构	应用优化	MySQL常用工具
2	索引	存储引擎	查询缓存优化	MySQL日志
3	视图	优化SQL步骤	内存管理及优化	MySQl主从复制
4	存储过程和函数	索引使用	MySQL锁问题	综合案例
5	触发器	SQL优化	常用SQL技巧

Linux上安装MySQL

下载Linux安装包

https://dev.mysql.com/downloads/mysql/5.7.html#downloads

安装MySQL

卸载centos中预安装的mysql

rpm -qa | grep -i mysql # 查询服务器当中mysql的安装包是否存在，存在则显示出存在安装包的名称

rpm -e mysql-libs-5.1.71-1.e16.x86_64 --nodeps # 对已存在的安装包(mysql-libs-5.1.71-1.e16.x86_64)进行卸载

上传mysql的安装包

alt + p ------->put E:/tset/MySQL-5.6.22-1.e16.i686.rpm-bundle.tar（MySQL安装包的目录）

# 使用SecureCRT远程连接服务器工具，alt+p命令进入sftp模式；再执行put （MySQL安装包的目录）[ 上传 ]

解压mysql的安装包

mkdir mysql

tar -xvf MySQL-5.6.22-1.e16.i686.rpm-bundle.tar -c /root/mysql

安装依赖包

# 安装第三方的依赖库；yum -y 在线安装需连接网络
yum -y install libaio.so.1 libgc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=false

yum update listdc++-4.4.7-4.e16.x86_64 # 更新第三方类包

安装mysql-client

rpm -ivh MySQL-client-5.6.22-1.e16.i686.rpm

安装mysql-server

rpm -ivh MySQL-server-5.6.22-1.e16.i686.rpm

Linux

ipconfig # 查看本机ip地址，内网地址

启动及登录MySQL

启动mysql命令

service mysql status  # 查看mysql服务状态
service mysql start   # 启动mysql服务
service mysql stop    # 停止mysql服务

登录mysql

mysql 安装完成之后，会自动生成一个随机密码，并且保存在一个密码文件中：/root/.mysql_secret；

查看随机密码

cat /root/.mysql_secret

登录命令

mysql -u root -p
# -u 指的是用户
# -p 指的是该用户所对应的密码

登录之后，修改密码命令

set password = password('itcast');
# itcast即明文密码

授权远程访问

grant all privileges on *.* to 'root'@'%' identified by 'itcast';
flush privileges;

防火墙

service iptables status # 查看防火墙状态
service iptables stop # 关闭防火墙

索引 - 概述

MySQL官方对索引的定义为：

索引（index）是帮助 MySQL 高效获取数据 的 数据结构（有序）。

在数据之外，数据库系统 还维护着满足 特定查找 算法的 数据结构，
这些 数据结构 以某种方式引用（指向）数据，
这样就可以在这些 数据结构 上实现 高级查找算法，
这种 数据结构 就是索引。
如下面的示意图所示：

左边是数据库，一共有两列七条记录，最左边的是数据记录的物理地址
（注意 逻辑上相邻的记录 在磁盘上 也 不是一定物理相邻 的）。

为了加快Col2的查找，可以维护右边所示的二叉查找树，
每个结点分别包含 索引键值 和一个指向 对应数据记录 物理地址 的指针，
这样就可以运用 二叉查找 快速后去到相应数据。

一般来说 索引本身也很大，
不可能全部存储在内存 中，
因此索引往往以 索引文件 的形式 存储在磁盘 上，
索引是数据库中用来 提供性能 的最常用的工具。

mspaint # windows打开画板

物理地址	Col1	Col2
0x07	1	34
0x56	2	77
0x6A	3	5
0xF3	4	91
0x90	5	22
0x77	6	59
0xD1	7	3

当前该为一张数据库表；
在数据库表的左侧展示的是每一条记录的一个物理地址；
右侧则是两列；
一列字段Col1是序号；
Col2字段数据可以认为就是主键数据；
数据库表结构有了，接下来查询数据；

（1）第一种情况：
查询主键Col2为34的数据；
因为这个时候只存在表，没有设置索引这些方便查询的数据结构；
所以这个时候就会在表当中 自上而下 的进行遍历；
这个时候就会去遍历到第一个，发现第一个的主键id，Col2的值就是34；此时即拿到数据；
这样的话只需要 查询一条记录；
而且查询第一条记录就是，那么这样的话查询速度是很快的；
这只是第一种情况；

（2）第二种情况：
查询主键Col2 id为91的记录行数据；
那么还是需要自上而下从id为34的记录行数据开始遍历；
那么第一条记录不是；
第二条记录不是；
第三条记录也不是；
直到第四条记录的时候发现数据行id是91；
那么这个时候也就是需要去遍历列表4次，查询4次才能够得到结果；

（3）第三种情况：
查询主键Col2 id为3的记录行数据；
这个时候再来看，如果说遍历查询的元素id是3；
那么这个时候依然是 从上往下 进行遍历，一直遍历到列表的最底部；
才能将3查找出来；那么这个时候就相当于需要进行一个全表扫描的这样一个操作；
对于当前该表当中只有6、7条数据；
但是如果在实际开发当中数据量比较大；
一张表当中存储了几百万上千万的记录；
那么这个时候如果只有表中的记录，而没有索引的话；
这个时候就会造成一种现象，在查找的时候从上往下进行整表扫描，所以这种查询效率是非常慢的；

以上就是第一种情况，即没有建立索引的情况；
查询效率很慢；

下面分析有了索引之后，又是如何进行查找的；
此时有了表结构还需要对表结构当中的字段进行建立索引；
索引就是一种数据结构；
那么此时就可以使用数据结构来表示这种表当中的索引；
这里使用一种 数据结构二叉树；

既然是 二叉树；
那么二叉树当中的第一个结点，也就是34；
即根节点；
34即作为当前该树状结构的根结点；

接下来第二行记录是77；

对于二叉树来说，二叉树有一个特点；
即当根结点已经存在的时候，再插入下一个结点；
则需要将 下一个结点的数值与根结点的数值 进行 比较大小；
如果比根结点小则放在左边，称之为生成一个左子树；
如果比根结点大则放在右边，称之为生成一个右子树，作为右子树存在；

这个时候77是比34要大的，所以放在34的右边，即77为34根结点的一个子节点；

接下来第三行是5，需要对5也要进行插入到二叉树该数据结构当中，那么此时也是需要进行与34根结点进行比较的；
此时5比34要小；那么则放在根结点34的左边，所以此时34根结点又有一个子节点5；

当5插入到二叉树结构当中之后，接着下一行记录91；
则首先将91与34根结点进行比较大小；
显然91比34要大，则处于根结点34的右边；

当前使用的是二叉树的这种数据结构，也就意味着 每一个结点 最多都只能有 2个分支；所以称之为二叉树；

所以当此时34 根结点 已经有了两个分支之后，那么此时91比34大，但是34的右边已经有元素77了，所以这个时候91就需要去和77去进行比较；
77与91进行比较，91显然要大，所以放在77的右侧，作为77的一个右子节点存在；

接下来是第5行记录数据集22；22比34小，显然要放在根结点的左侧；
但是根结点的左侧已经有左子节点也就是5；
那么此时22就去与5进行比较，显然22比5大；也就放在5的右边；称为5的右子结点；

最后还剩下最后两条记录，一个是59一个是3；
快速过一下；59首先和34这个元素进行比较；
显然59比34大需要放在34的右侧；
然34已经有右子结点77了，那么这个时候59和77进行比较；
显然77大于59，那么这个时候就将59放置在77的左侧，叶子结点91的左边，成为77的左子节点；

同理最后一个元素，3也是同样的方式；
3与34进行比较；显然3比34要小；
然此时34是存在有左子节点5的；
那么这个时候就将3与5进行比较；显然3比5要小；
那么这个时候；就把3放在5的左侧；
成为5的左子节点，叶子结点22的左侧；

此时到此为止二叉树已经构建完毕；二叉树构建完毕之后再来进行分析；
构建了一棵二叉树之后就能够提高查询效率吗？

（
我认为；
在二叉树一定的深度下，二叉树依然会存在查询效率慢的情况；
）

二叉树构建好了之后，再来进行搜索；
如果按照刚才的查询情况；
先进行搜索34这一数值；

搜索二叉树；那么就需要到索引的结构二叉树的数据结构当中来进行搜索；
在进行二叉树搜索的时候，是从根结点处开始进行查询匹配数值；
即树状结构当中最顶层的这一节点即为根结点；
最底层的结点，即3、22、59、91这四个结点称之为叶子结点；
那么二叉树的搜索是从根节点开始进行搜索的；

走根结点34；这个时候就已经将34查询到了，那么这个时候就可以直接进行返回即可；
但是如果这一块当中查询的并不是34而是3；
那么此时仍然先会去搜索根结点34，将要查询搜索的值3去与根节点34进行比较；
发现3比根结点要小；
所以此时就会去根结点的左侧，即左子树当中去进行查找3；

二叉树当中根结点的左边的称之为左子树；右边则称之为右子树；

那么在左子树当中通过3去跟根结点的左子节点5进行比较，那么此时5比3要大；
所以紧接着，让3去跟结点5的左子节点3去进行比较；
所以在这个时候就找到了3这个元素；
而找到了3之后，二叉树的这种数据结构是如何关联到表当中具体行数当中的具体数据的呢？
这个时候就需要看笔记当中提到的一句话：

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

那也就意味着，二叉树这种数据结构当中的每一个结点都是需要指向具体表当中具体行数的具体数据；
那么从刚才的查询规律当中从上而下进行查找3，则从根结点34到左子节点5，再从5到5的左子节点3查找到了3这一元素的指向/引用即可找到在具体某表当中的具体某一行的数据；
那么这样的话就查找到了数据；

物理地址	Col1	Col2
0x07	1	34
0x56	2	77
0x6A	3	5
0xF3	4	91
0x90	5	22
0x77	6	59
0xD1	7	3

分析：没有建立索引 的表与 建立了索引 的表当中，建立了索引之后性能的一个提升；

没有建立索引 的表：
在没有建立索引的情况下，如果是要进行查询数据元素3；
那么则需要把整张表当中所有的数据都需要进行遍历一遍即 全表扫描 ，直到找到3这个id数据；
建立了索引 的表：
如果是在建立了索引的这种情况之下；
那么同样还是查找元素3；
只需要查找第一次34；第二次5；第三次3即可；三次即可；
那么这样的话这个时候通过索引由 二叉树 这种 数据结构 实现的话就 不再需要去遍历整张表的数据 了；
通过 二叉树 这种 数据结构 就可以快速的定位到3这个查询的数据元素；
那么这就是索引；索引的目的 即在于快速的来 查找数据；

索引 - 优势和劣势

优势
1）类似于书籍的 目录索引 ， 提高数据检索的效率 ， 降低数据库的IO成本 ；
2）通过 索引列 对数据进行排序， 降低数据排序的成本 ，降低CPU的消耗 ；

劣势
1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录， 索引列 也是要 占用空间 的；（并不放在内存当中而是 放在磁盘当中 ， 索引占用磁盘空间 ）
2）虽然索引 大大提高了查询效率 ，同时却也降低了 更新表的速度 ，如对表进行 INSERT、UPDATE、DELETE （都需要 对索引进行维护，也就是相关实现该索引的数据结构当中存储的数据也要进行变动）。因为 更新表 时，MySQL不仅要 保存数据 ，还要保存一下 索引文件 每次 更新添加 了 索引列的字段 ，都会因为更新所带来的的 键值变化 后的索引信息。（所以 索引的字段并不是越多越好也会影响到性能 ）

索引 - 索引结构

索引是在MySQL的 存储引擎 层中实现的，而不是在服务器层实现的。

所以每种存储引擎的索引都不一定完全相同；也不是所有的存储引擎都支持所有的索引类型。

MySQL目前提供了以下4中索引：

BTREE索引：最常见的索引类型，大部分索引都支持 B树索引 ；
HASH索引：只有 Memory引擎 支持，
使用场景简单（刚刚看了下我当前MySQL数据库现在InnoDB也支持HASH哦）；
R-tree索引（空间索引）：空间索引 是 MyISAM引擎 的一个 特殊索引类型 ，
主要用于 地理空间数据类型 ，
通常使用较少，不做特别介绍；
Full-text（全文索引）：全文索引 也是 MyISAM 的一个 特殊索引类型，
主要用于 全文索引 ，
InnoDB 从 MySQL5.6 版本开始支持 全文索引 ；

这个统计应该也有些时候了；不是一定正确的哦；
需要结合当前不同版本的MySQL来说明；
我还是贴一下吧；

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本之后支持	支持	不支持

平常所说的索引，如果没有特别指明，都是指 B+树 （ 多路搜索树 ，并不一定是二叉的）结构组织的索引。
其中 聚集索引、 复合索引 、 前缀索引 、 唯一索引 默认都是使用 B+tree树索引 ，统称为索引。

索引 - 索引结构 - BTREE

BTree 又叫做 多路平衡搜索树 ，一棵 m 叉的 BTree 特性如下：

树中每个结点最多包含 m 个孩子；
除根结点与叶子结点外，
每个结点至少有[ cell ( m / 2 ) ]（cell向上取整）个孩子；
若根结点不是叶子结点，则至少有两个孩子；
所有的叶子结点都在同一层；
每个非叶子结点由 n 个 key 与 n + 1 个指针组成，
其中 [cell ( m / 2 ) -1 ] < = n < = m - 1

（对于二叉树来说，树中的每个结点 最多有2个分支 ）

以5叉BTree为例（当前根结点下最多有5个分支，即每个结点下最多包含5个孩子），key的数量：公式推导[ cell ( m / 2 ) - 1 ] < = n < = m-1 ；所以 2 < = n < = 4。
当n>4时，中间节点分裂到父节点，两边结点分裂。
插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例；
1）插入前4个字母C N G A；

（按照26个字母排队顺序，字母A在C前，C排在G前，G排在N前的顺序进行排列）

2）插入H ，n > 4，中间元素G字母向上分裂到新的结点；

所以演变成中间元素G字母向上分裂到新的结点，即G分裂到父节点；

G
A	C		H	N

			G
			指针	指针

A C					H N
指针	指针	指针			指针	指针	指针

新插入的H结点会在元素G之后，那么排序也就是 A C G H N 这个样子的一个排序；那么此时的中间的结点就是G元素了；那么按照规则，当n>4时，中间节点分裂到父节点；此时的G元素结点就将会进行一次分裂；分裂至父节点（H插入至BTree则G分裂至父节点）；而此时两边结点进行一次分裂；也就是原本的中间元素中间节点G左边的A、C以及右边的（新插入的）H、N就将会被分裂成父节点G的两边结点；左边的A、C分裂至父节点G的左子节点；右边的H、N分裂至父节点G的右子结点；

接下来继续进行插入操作；插入元素E；
插入的E首先进行跟根结点G进行比较前后顺序；比较完成之后，E应该是在根结点G的排位在前的；所以应该被放在根结点G的左侧；也就是结点A、C该侧，A、C、E这三个元素也是需要进行排出一个顺序来的，那么也就是A、C、E这样的一个顺序排列放置；插入的是左边；由于左边元素未构成5个，所以不做任何的（分裂）变化等；

接下来再继续插入元素K；
在K插入元素的时候就需要拿G与K元素的前后排位顺序进行比较，K的排位顺序比G大，所以插入到根结点G的右侧，也就是H、N所在该侧；插入的是右边，即插入时也需要与H、N这两个元素相进行比较排位顺序，也就是K元素插入到H、N这两个元素的中间；那么此时右侧的元素未构成5个所以不作任何变化；

接下来继续插入Q；
插入Q也是需要与根节点G相进行比较排位顺序的；得出Q是在G之后，那么就将Q放在G根节点的右侧，然后将Q进行与节点元素H、K、N相进行比较；得到排列顺序H、K、N、Q这样一个排列顺序，当然此时仍然不需要做出（分裂）变化；因为此时n并没有超过4；只有当n超过4的时候才会发生分裂的变化；分叉小于等于4的情况下并不做任何的分裂改变只进行结点元素间的大小排位；

在此之前一直在说分裂；当前已经分裂过一次；也就是A、C、G、H、N的时候进行将G分裂至父节点；A、C结点与H、N结点进行两边结点的分裂；并且插入了三个新的元素E、K、Q；

此时需要说明的是：

每个非叶子结点由 n 个 key 与 n + 1 个指针组成，其中[ cell( m / 2) -1 ] < = n < = m-1

n+1个指针值的就是每个结点下都有两个方块，这两个方块都是指针存储区域；
指针的含义：当进行插入一个元素的时候，比如说插入元素E；当插入E元素的时候会进行与根节点G进行一个前后顺序排位的一个对比；如果E比G要小；那么E元素就需要放在G的左侧，指向左侧；成为根结点当中的左子节点当中的一个；那么这个时候就由G结点的左指针指向左子节点的方向；通过左边的这个指针指向左边的这个数据块；小于G那么这个指针就指向的是小于G的这个数据块（也就是左侧的左子结点）；大于G那么指向的就是大于G的这个数据块（也就是右侧的右子结点）；

即

				G
				指针	指针

A C E						H K N Q
指针	指针	指针	指针			指针	指针	指针	指针	指针

接下来继续插入M元素；M与G根结点相进行比较，应该是属于在G的后面的；所以应该排放在G的右侧；通过G的右指针指向右边的数据块；M插入进来之后，应该要介于K和N之间；也就是说此时G的右子结点当中的排列顺序为H、K、M、N、Q这样一个排列顺序；这个时候就需要将M插入到K和N之间；但是当M插入到K和N之间之后，此时右子结点当中的元素已经超过了4个，即5个了；超过4个元素那么此时就要发生分裂变化，M结点刚好是中间节点；即会要发生中间节点（新插入的元素）M结点元素最终需要向上分裂到G父节点当中去，以及同时左右两边的元素H、K以及H、Q需要分裂变化成两块；

问题：明白没有？小朋友？
答：明白了！(ಥ_ಥ)

经过分裂之后再来看其指针；那么根结点当中的三个指针分别指的是；第一个指针指的是小于根节点当中的G元素则指针指向左侧第一个数据元素块A、C、E；第二个指针，则因为根节点当中存在两个元素；所以中间这个第二个指针用于大于根节点当中的G元素而小于根节点当中的M元素则指向中间的H、K的这个数据块；最后根节点当中的第三个指针用于大于根节点当中M元素的指向第三个数据元素块即N、Q；

问题：明白？小朋友？
答：明白了！o(ﾟДﾟ)っ！

当前为插入M的数据结构；

G M

指针

A C E

H K

N Q

指针

当前M插入完成之后，继续插入F、W、L、T这四个数据元素；
那么此时插入F、W、L、T这四个数据元素时是不需要进行分裂变化的；因为当这四个元素插入进来之后，各个数据块当中的key 结点都没有超过4；所以是不需要进行分裂变化处理的；

G M

指针

A C E F

H K L

N Q T W

指针

上述F、W、L、T这四个元素插入完成之后，接着插入元素Z；
插入Z的时候，首先会进行与根节点的G和M相进行比较；Z元素比G、M都要大；所以就由根结点的第三个指针指向第三个数据块，也就是N、Q、T、W；走向第三个数据块时又会与第三个数据块当中的元素相进行比较得到其元素排列顺序N、Q、T、W、Z这样一个排列摆放位置；所以此时将Z插入第三个数据块当中的最后一个位置，当Z元素出现在W元素之后的时候，此时观察第三个数据库块当中的元素个数已经超过了4，即已经成为5个了，那么此时这5个元素当中中间元素需要进行向上分裂，分裂至父节点，然后其左右两边需要分裂成两个数据块；该第三个数据块当中的中间元素为T；T元素此时应当符合条件需要分裂至上一节点父节点当中去；以及T元素在分裂至父节点之前原来两边的结点需要进行分裂成两块也就是N、Q结点以及W、Z结点这两大块进行分裂开来称为单独的结点；

G M T

指针

A C E F

H K L

N Q

W Z

指针

当元素Z插入完成之后，接下来进行插入元素D；
元素D和根结点当中的G、M、T相比，显然是都小于这三者的；所以由根结点当中的第一个指针进行指向第一个数据块，也就是A、C、E、F这个数据块当中来；并且D元素需要进行与数据块元素当中的A、C、E、F这四个元素需要进行比较排序顺序大小；即通过排序顺序可得A、C、D、E、F；插入进来之后，此时该数据块当中的key元素大于4个了，即出现了5个；那么此时就需要将该数据块元素当中的那个中间元素（D作为该数据块当中的中间元素存在），也就是刚刚新插入的元素D进行向上分裂至父节点G、M、T当中去，且分裂至父节点的元素D也需要与G、M、T进行一个排序，排序得到D、G、M、T这样一个排列顺序；那么此时分裂至父节点的元素D的两边的元素同样也需要进行分裂成两个结点；即A、C与E、F进行分裂开来；
最终出现的数据结构：

D G M T

指针

A C

E F

H K L

N Q

W Z

指针

当元素D插入完成之后，接着进行插入元素P、R、X、Y；
插入P、R、X、Y时并不需要进行相应的变化分裂；因为插入到数据块当中元素key并没有满足 > 4 的这样一个条件；所以并不需要做出分裂；

D G M T

指针

A C

E F

H K L

N P Q R

W X Y Z

指针

当插入元素P、R、X、Y这四个元素之后，接下来进行插入最后一个元素S；
S首先和根结点当中的是个元素D、G、M、T进行比较；显然介于M与T之间；即大于M而小于T这样一个范围；从而使得根结点的第四个指针指向第四个数据块，即N、P、Q、R这一个数据块当中；而当S插入到第四个数据块当中首先也是需要进行比较排序顺序大小的；有顺序可得；得到顺序为N、P、Q、R、S这样一个顺序排列；将S插入到该数据块当中来；此时第四个数据块当中的元素个数已经达到了5个，也就是超过了4个就需要进行分裂了；那么此时；该数据块当中的中间元素为Q；也就是Q元素需要进行向上分裂至父节点D、G、M、T该数据块当中去；以及Q元素两边的元素需要进行分裂，也就是N、P与R、S这两块分裂开来；
但是Q如果此时向上进行分裂，而此时父节点当中的元素对向上分裂来的Q元素进行排序得到D、G、M、Q、T；父节点插入元素Q；父节点当中的元素也达到了了5个；也需要进行分裂；其父节点当中的中间元素为M元素，则此时M元素继续向上进行分裂，则M更新称为该BTree的根节点；而M两边的元素，即D、G两元素与Q、T两元素则这两块进行分裂开来，D、G块称为根结点M的左子节点，Q、T块称为M根结点的右子结点；这就是所提到的五叉BTree结构的一个演变过程；

指针

D G

Q T

指针

A C

E F

H K L

N P

R S

W X Y Z

指针

了解了五叉BTree数据结构的一个演变过程之后回过头来再看；一棵m叉的BTree特性；
如果是一个五叉的BTree；
则：

树中每个结点最多包含m个孩子；即树中每个结点最多包含5个孩子；不能多于5个，即不能多于m个；

举例

D G M T

指针

A C

E F

H K L

N P Q R

W X Y Z

指针

除根结点与叶子结点外，每个结点至少有 [ cell( m / 2 )]个孩子；即每个结点至少有5/2=3（向上取整）个孩子；M为根节点；AC块、EF块、HKL块、NP块、RS块、WXYZ块为叶子结点；所以仅剩DG块与QT块非叶子结点也非根节点；这两块分别有三个字节点，DG块的孩子即AC块、EF块、HKL块；QT块的孩子即NP块、RS块、WXYZ块；

举例

指针

D G

Q T

指针

A C

E F

H K L

N P

R S

W X Y Z

指针

若根结点不是叶子结点，则至少有两个孩子；

举例

指针

D G

Q T

指针

A C

E F

H K L

N P

R S

W X Y Z

指针

所有的叶子结点都在同一层；

举例

指针

D G

Q T

指针

A C

E F

H K L

N P

R S

W X Y Z

指针

每个非叶子结点由n个key与n+1个指针组成；其中[ cell( m / 2)-1] < = n < = m - 1；即

key即指的每一个结点当中的元素；指针即用来指向数据块

G M

指针

A C E F

H K L

N Q T W

指针

到此已经提到了两种数据结构，一种是二叉树；一种是BTree；这两个数据结构可以拿过来相互比较一下优劣；

BTree数据结构相对于二叉树相比；存在有多个分支；而二叉树一个结点仅仅只存在有两个结点；那么对于相同数据量来说，二叉树的数据层次，也就是二叉树的深度要更高一些；一旦层次更深也就意味着查找数据的时候；磁盘的IO的次数也就更多；从而导致查询的效率也就比较低；
到此，该BTree树就已经构建完成了，BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层次结构相比二叉树小，因此搜索速度更快；

索引 - 索引结构 - B+TREE

B+Tree为BTree的变种，B+Tree与BTree的区别为：
1） n叉B+Tree最多含有n个key，而BTree最多含有n-1个key；
2） B+Tree的叶子结点保存所有的key信息，依照key大小顺序排列；
3）所有的非叶子结点都可以看做是key的索引部分；

没有子节点的结点称之为叶子结点；叶子结点当中包含了所有key的信息；叶子结点当中的key才是完整的包含了所有的key的信息；包含了所有的数据信息；根结点当中以及非叶子结点当中的key起到的是一个索引的作用；索引就是为了查找到叶子结点当中的key；而且对于B+树来说所有的数据都是存储在叶子结点上面了；非叶子结点是不存在数据信息的；B树当中的所有数据是跟结点挂钩的；B+树当中所有的key存储在叶子结点所有的数据也都存储在叶子结点上了；
这也是B+树与B树的一个区别；

B+Tree相对于BTree的一个优势：
由于B+Tree只有叶子结点保存key信息，查询任何key都要从root走到叶子（非叶子结点上的key都是起到一个索引的作用并没有数据，叶子结点上的key才关联着相关的数据信息），所以B+Tree的查询效率更加稳定；

索引 - 索引结构 - MySQL中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。
在原B+Tree的基础上，增加一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能；
问题：为什么要增加这样一个指针？
答：方便进行查询；

增加该指针的原因在于：方便进行范围搜索；

举例：如果想要进行搜索id为9~15的数据；由于有该指针相连所以从9一直查询链表下一结点到15即可；
所以该链表指针的作用主要是用来提高区间访问的性能；

索引 - 索引分类

1）单值索引：即一个索引值包含单个列，一个表可以有多个单列索引(一个索引当中只包含一个列；比如现在需要对某一张表当中建立索引；该表当中有多个字段name、gender、age、help_category_id等字段，但是只针对name该字段该列进行建立索引；help_category_id也可以建立个索引；则当前存在两个索引，但是每个索引只对应了相对应的一列；这个叫做单列索引或者是单值索引；一个表当中可以包含有多个单列索引)
2）唯一索引：索引列的值必须唯一，但允许有空值；（当前表当中某一个字段或者是某一列当中其值都是唯一的；也就是说如果存在有列当中的值都是唯一的；那么必要情况下就可以针对该列建立一个唯一索引，在唯一索引的该列当中允许包括有多个NULL值；）
3）复合索引：即一个索引包含多个列（比如：经常在查询当中需要对name字段以及parent_category_id或者是说age字段进行联合查询，那么此时可以将name字段以及age字段组合起来建立一个复合索引，即一个索引当中包含了两个列或者说多个列；那么这种索引称之为复合索引）；

索引 - 索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引；
准备环境：

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE 'city'(
    'city_id' int(11) NOT NULL AUTO_INCREMENT;
    'city_name' varchar(50) NOT NULL;
    'country_id' int(11) NOT NULL;
    PRIMARY KEY ('city_id')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE 'country'(
    'country_id' int(11) NOT NULL AUTO_INCREMENT;
    'country_name' varchar(100) NOT NULL;
    PRIMARY KEY ('country_id')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into 'city'('city_id','city_name','country_id') values(1,'西安',1);
insert into 'city'('city_id','city_name','country_id') values(2,'NewYork',2);
insert into 'city'('city_id','city_name','country_id') values(3,'北京',1);
insert into 'city'('city_id','city_name','country_id') values(4,'上海',1);

insert into 'country'('country_id','country_name') values(1,'China');
insert into 'country'('country_id','country_name') values(2,'America');
insert into 'country'('country_id','country_name') values(3,'Japan');
insert into 'country'('country_id','country_name') values(4,'UK');

创建索引

语法：

CREATE [ UNIQUE | FULL TEXT |SPATIAL] INDEX index_name
[ using index_type ]
ON tb1_name(index_col_name, .... )

index_col_name : column_name[ ( length ) ] [ ASC | DESC ]

索引的类型：UNIQUE唯一索引、FULLTEXT全文索引、SPATIAL；索引类型可选；可以不进行指定；
index_name：索引的名称；
using index_type：索引所使用的哪一个类型；如果不指定；则使用，默认的索引类型即B+树索引类型；
ON tb1_name(index_col_name, …)：进行指定对哪一张表的哪几个字段创建索引；

在MySQL当中，如果当前该字段是主键的话；则默认该字段默认有一个索引叫做主键索引；

所以此时在city表当中对city_id字段的索引不必再进行创建；可以在city_name字段上创建索引；

create index index_city_name on city(city_name);#此时在city_name字段上创建的该索引为普通索引；

示例：为city表中的city_name 字段创建索引：

create index idx_city_name on city(city_name);

查看索引

语法：

show index from table_name; #table_name，索引所建立在该字段，该字段所处的表的名称

示例：查看city表中的索引信息：

show index from city\G; #\G格式化

在MySQL当中会默认对主键city_id字段创建一个索引就称作是主键索引key_name：primary；
而刚刚对city_name创建的索引则为key_name：idx_city_name；
主键索引primary以及刚刚没有指定索引类型的idx_city_name的索引类型都是BTREE类型；即B+Tree索引，BTree索引；

删除索引

语法：

DROP INDEX index_name ON tb1_name;
# index_name索引的名称
# 该index_name索引所具体在的表的名称

示例：想要删除city表上的索引[ idx_city_name ]，可以操作如下：

drop index idx_city_name on city;

删除之后可以通过以下命令来进行查看删除的索引是否仍然存在；

show index from table_name\G;

ALTER指令

# alter 修改
# 该语句添加一个主键，这意味着索引值必须唯一，且不能为NULL；
1. alter table tb_name add primary key(column_list);

# unique 唯一的
# 这条语句创建索引的值必须是唯一的（除了NULL意外，NULL可能会出现多次）
# Non_unique:0 #Non不，unique唯一；0：否定；双重否定等于肯定；即唯一
# 当创建普通索引时其Non_unique:1；即不唯一；  
2. alter table tb_name add unique index_name(column_list);


# 添加普通索引，索引值可以出现多次
3. alter table tb_name add index index_name(column_list);


# 该语句指定了索引为FULLTEXT，用于全文索引
4. alter table tb_name add fulltext index_name(column_list);

示例：添加唯一索引

alter table city add  unique  idx_city_name(city_name);

索引 - 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引；

对查询频次较高，且数据量比较大的表建立索引；
索引字段的选择，最佳候选列应当从where子句的条件中提取（即查询条件当中的字段），如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高；
索引可以有效的提升查询数据的效率，但是索引数量不是多多益善；索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价（维护索引的数据结构），降低DML操作的效率，增加响应操作的时间消耗；另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但是无疑提高了选择的代价；
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，乡音的可以有效地提升MySQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升性能；

创建复合索引：

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
      对name 创建了索引；
      对name , email 创建了索引；
      对name , email , status 创建了索引；
只需要创建这么一个索引，在查询name单个字段的时候就将会走索引；在查询name，email这两个字段的时候也会走索引；在查询name,email,status这三个字段的时候也会走索引；  
最左索引：最左指的是复合索引当中的第一个字段，只要查询的时候包含第一个字段，那么这个时候就会走索引；

视图 - 创建及修改视图

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

视图相对于普通表的又是主要包括以下几项：

简单：使用视图的用户完全不需要关心后面对应表的结构、关联条件和筛选条件，对用户来说已经是过滤好的符合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果喜，对表的权限管理并不能先知道某个行某个列，但是通过视图就可以简单的实现；
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响；

创建或者修改视图

创建视图的语法为

CREATE [ OR REPLACE] [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE } ]

VIEW view_name [ (column_list) ]

AS select_statement

[ WITH [CASCADED | LOCAL ] CHECK OPTION ]

修改视图的语法

ALTER [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE } ]

VIEW view_name [ (column_list) ]

AS select_statement

[ WITE [ CASCADED | LOCAL ] CHECK OPTION ]

选项：
    WITH [ CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不在满足视图的条件；
    LOCAL 只要满足本视图的条件就可以更新
    CASCADED 必须满足所有针对该视图的所有视图的条件才可以更新（默认值）

    CREATE [ OR REPLACE] 创建或者是替换
    AS select_statement select_statement即select语句

示例：在视图当中展示国家以及其相关城市的信息

show tables; # 显示在该数据库下所有的表
desc table_name; # 显示该table_name当中的表结构
desc city;
select * from city c,country t where c.country_id=t.country_id;
select c.*,t.country_name from city c,country t where c.country_id=t.country_id;

create view view_city_country as select c.*,t.country_name from city c,country t where c.country_id=t.country_id;

select * from view_city_country;# 查询视图与表操作一致
update view_city_country set city_name='西安市' where city_id=1;#修改视图与表操作一致

select * from city; # 这个时候基础表当中的数据也会被更新掉；也就是说此时的视图修改操作是会影响到基础表当中的数据；  
虽然视图能够做到更新，但是并不建议去更新数据；因为视图就是用来简化查询操作的，不建议更新视图；

视图 - 查看及删除视图

查看视图

从MySQL5.1版本开始，使用SHOW TABLES命令的时候不仅会显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOW VIEWS命令。

show tables;# 显示表以及视图的名称

同样，在使用SHOW TABLE STATUS命令的时候，不但可以显示表的信息，同时也可以显示视图的信息；

show table status; # 显示表以及视图的信息
show table status like 'city_country_view'\G; # 示例查看视图city_country_view

show create view view_city_country; # 查看创建视图时所使用的的SQL语句是什么

删除视图

语法：

DROP VIEW [ IF EXISTS ] view_name [, view_name ] ... [ RESTRICT | CASCADE ]

示例，删除视图city_country_view:

DROP VIEW city_country_view;# or DROP VIEW if exists city_country_view;

存储过程 - 概述

存储过程和函数是实现经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率时有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有；
函数：是一个有返回值的过程；
存储过程：是一个没有返回值的函数；

存储过程 - 创建调用查询删除语法

创建存储过程

CREATE PROCEDURE procedure_name ( [proc_parameter[,.....]])
begin
    -- SQL语句
end;

示例：

delimiter $

create procedure pro_test()
begin
    select 'Hello MySQL';
end$

delimiter ;

知识小贴士

DELEMITER
该关键字用来声明SQL语句的分隔符，告诉MySQL解释器，改短命令是否已经结束了，mysql是否可以执行了，默认情况下，delimiter是分号;
在命令行客户端中，如果一行命令以分号结束，那么回车后，mysql将会执行该命令；

调用存储过程

call procedure_name();

查看存储过程

-- 查询db_name 数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
select name from mysql.proc where db='demo_01'$ #示例

-- 查询存储过程的状态信息
show procedure status;
show procedure status$ # 示例

-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;
show create procedure pro_test\G$ #示例

删除存储过程

DROP PROCEDURE [ IF EXISTS ] sp_name;

drop procedure pro_test1$ # 示例

存储过程 - 语法 - 变量

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。(使得SQL具有过程化语言的处理能力)

变量

DECLARE

通过DECLARE可以定义一个局部变量，该变量的作用返回只能在BEGIN…END块中。

DECLARE var_name[ ... ] type [DEFAULT value]

示例：

delimiter $

create procedure pro_test2()
begin
    declare num int default 5;
    select num+ 10;
end$

create procedure pro_test1()
begin
    declare num int default 10;
    select concat('num的值为',num);
end$

call pro_test1()$
call pro_test2()$

delimiter $

直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [ , var_name=expr ] ....

示例：

DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE NAME VARCHAR(20);
    SET NAME = 'MYSQL';
    SELECT NAME ;
END$

CREATE PROCEDURE pro_demo()
begin
    declare num int default 0;
    set num = num +10;
    select num;
end$

call pro_test3()$
call pro_demo()$

DELIMITER ;

也可以通过 select…into 方式进行赋值操作；

DELIMITER $

CREATE PROCEDURE pro_test5()
BEGIN
    DECLARE countnum int;
    select count(*) into countnum from city;
    select countnum;
END$

CREATE PPROCEDURE pro_demo()
begin
    declare num int;
    select count(*) into num from city;
    select concat('city表中的记录数为',num);
end$

call pro_test5()$
call pro_demo()$

DELIMITER $

存储过程 - 语法 - if判断

if条件判断

语法结构：

if search_condition then statement_list
      [ elseif search_condition then statement_list ] ...
      [ else statementlist ]
end if;

需求：

根据定义的身高变量，判定当前身高的所属的身材类型

180及以上----------》身材高挑
170 ~ 180----------》标准身材
170一下------------》一般身材

示例：

delimiter &
create procedure pro_test4()
begin
    declare height int default 175;
    declare description varchar(50) default '';
    if hegith >= 180 then
      set description='身材高挑';
    elseif height >=170 and height < 180 then
      set description='标准身材';
    else
      set description='一般身材';
    end if;
    select concat('身高',height,'对应的身材类型为',description);
end$

call pro_test()$

delimiter ;

存储过程 - 语法 - 输入参数

传递参数

语法格式：

create procedure procedure_name([ in / out / inout ]参数名称 参数类型)
...

# IN 该参数可以作为输入，也就是需要调用方传入值，默认
# OUT 该参数作为输出，也就是该参数可以作为返回值
# INOUT 既可以作为输入参数，也可以作为输出参数

IN - 输入

需求：

根据定义的身高变量，判定当前身高的所属的身材类型

示例：

delimiter $

create procedure pro_test5(in height int)
begin
    declare description varchar(50) default '';
    if hegith >= 180 then
      set description='身材高挑';
    elseif height >=170 and height < 180 then
      set description='标准身材';
    else
      set description='一般身材';
    end if;
    select concat('身高',height,'对应的身材类型为',description);
end$

call pro_test5(198)$
call pro_test5(178)$
call pro_test5(158)$

delimiter ;

存储过程 - 语法 - 输出参数

需求：

根据传入的身高变量，获取当前身高的所属的身材类型

示例：

create procedure pro_test5(in height int ,out description varchar(10))
begin
    if height >=180 then
        set description='身材高挑';
    elseif height >= 170 and height < 180 then
        set description='身材标准';
    else
        set description='一般身材';
    end if;
end$

call pro_test5(188,@description)$
# @description 代表用户的变量；用户的会话变量

select @description$ # 查询用户会话变量取值

delimiter ;

小知识
@description：这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程它都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size:这种在变量前加上“@@”，叫做系统变量；

set @name='itcast'$

select @name$

存储过程 - 语法 - case结构

语法结构：

方式一：  
CASE case_value # case_value 条件
    WHEN when_value THEN statement_list # statement_list SQL语句
    [ WHEN when_value THEN statement_list ] ...
    [ ELSE statement_list ]
END CASE;


方拾二：  
CASE
    WHEN search_condition THEN statement_list # search_condition 条件表达式；如果该表达式为真只执行THEN后的statement_list SQL语句
    [ WHEN search_condition THEN statement_list ] ...
    [ ELSE statement_list ]
END CASE;

需求：

给定一个月份，然后计算出所在的季度

示例：

delimiter $

create procedure pro_test7( mon int)
begin
    declare result varchar(10);
    case
      when mou>=1 and mon <=2 then
        set result='第一季度';
      when mou>=4 and mon <=6 then
        set result='第二季度';
      when mou>=7 and mon <=9 then
        set result='第三季度';
      else
        set result='第四季度';
    end case;
    select concat('传递的月份为',mon,'， 计算出的结果为：',result) as content;
end$

call pro_test7(7)$
call pro_test7(2)$
call pro_test7(11)$
call pro_test7(9)$

delimiter ;

存储过程 - 语法 - while循环

语法结构：

while search_condition do # search_condition 条件成立执行循环；条件不成立则跳出循环
    statement_list
end while;

需求：

计算从1加到n的值

示例:

delimiter $

create procedure pro_test8(n int)
begin
    declare total int default 0;
    declare start int default 0;

    while start <= n do
        set total=total+start;
        start=start+1;
    end while;
    select total;
end$

call pro_test8(3)$
call pro_test8(6)$
call pro_test8(100)$

delimiter ;

存储过程 - 语法 - repeat循环

有条件的循环控制语句，当满足条件的时候退出循环。
while是满足条件才执行，repeat是满足条件就退出循环。

语法结构：

REPEAT
    statement_list

    UNTIL search_condition
END REPEAT;

需求：

计算从1加到n的值

示例：

delimiter $

create procedure pro_test9(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 pro_test10(50)$

delimiter ;

存储过程 - 语法 - loop循环

loop语句

LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：

[ begin_label: ] LOOP
    statement_list
END LOOP [ end_label ]
]

如果不在 statement_list 中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环

leave语句

用来从标注的流程构造中退出，通常和BEGIN…END或者循环一起使用。
下面是一个使用LOOP和LEAVE的简单例子，退出循环；

delimiter $

CREATE PROCEDURE pro_test10(n int)
BEGIN
    declare total int default 0;

    ins:LOOP
        IF n <= 0 then
          leave ins;
        ELSE
          SET total=total+n;
          SET n=n-1;
        END IF;
    END LOOP ins;
    select total;
END$


create procedure pro_test10(n int)
begin
    declare total int default 0;

    c:loop
        set total=total+n;
        set n=n-1;
        if n<0 then
            leave c; # 满足条件跳出循环
        end if;
    end loop c;
    select total;
end$

call pro_test10(100)$
call pro_test10(99)$
call pro_test10(3)$

delimiter ;

存储过程 - 语法 - 游标介绍

游标是用来存储查询结果集的数据类型，唉存储过程和函数中可以使用光标对结果集进行循环的处理。
光标的使用包括光标的声明、OPEN、FETCH和CLOSE。

存储过程 - 语法 - 游标基本操作

其语法分别如下：

声明光标：

DECLARE cursor_name CURSOR for select_statement;

OPEN光标：

OPEN cursor_name;

FETCH光标：

FETCH cursor_name INTO var_name [ , var_name ] ... # 每抓取一次，即获取结果集当中每一行数据；调用一次指针往下指向一次；调用一次指针往下指向一次；当指针指到最后没有数据的那一行时，即此时就应该退出游标了；

CLOSE光标：

CLOSE cursor_name;

示例：
初始化脚本:

create table emp(
  id int(11) not null auto_increment,
  name varchar(50) not null comment '姓名',
  age int(11) comment '年龄',
  salary int(11) comment '薪水',
  primary key('id')
)engine=innodb default charset=utf8;

insert into emp(id,name,age,salary) values (null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4400),(null,'青翼蝠王',38,2800),(null,'素衫龙王',42,1800);

需求：

-- 查询emp表中数据，并逐行获取进行显示

示例：

delimiter $
create procedure pro_test11()
begin
    declare e_id int(11);
    declare e_name varchar(50);
    decalre e_age int(11);
    declare e_salary int(11);
    declare emp_result cursor fro select * from emp;

    open emp_result;

    fetch emp_result into e_id,e_naem,e_age,e_salary;
    select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);

    fetch emp_result into e_id,e_naem,e_age,e_salary;
    select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);

    fetch emp_result into e_id,e_naem,e_age,e_salary;
    select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);

    fetch emp_result into e_id,e_naem,e_age,e_salary;
    select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);

    fetch emp_result into e_id,e_naem,e_age,e_salary;
    select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);

    close emp_result;
end$

# emp表当中只有四条数据，如果fetch的次数大于表当中的数据，那么fetch表中总行数依然可以打印出来，一旦fetch大于表中的总行数则进行报错；

# 通过fetch一行一行进行抓取记录编写的效率十分低；

call pro_test11();
delimiter ;

存储过程 - 语法 - 循环获取游标

循环当中，游标的fetch退出判断

1） count(*) into num; num –
2） mysql存在机制，变量，通过改变该变量取值状态及退出fetch

示例：

delimiter $

create procedure pro_test12()
begin
    declare e_id int(11);
    decalre e_name varchar(50);
    decalre e_age int(11);
    decalre e_salary int(11);
    declare has_data int default 1;#当has_data为1时表示还有数据；当has_data为0时表示没有数据

    declare emp_result cursor for select * from emp;
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;#退出当前游标指向程序并设置has_data为0；此为mysql提供的一种机制；同时该句话需要声明在声明游标的下方；当游标进行fetch获取不到数据的时候，此时会进行触发；

    open emp_reuslt;

    repeat
        fetch emp_result into e_id,e_name,e_age,e_salary;
        select concat('id为：' , e_id,', 名字为: ' , e_name , ', 年龄为：' , e_age , ', 薪资为：' , e_salary);
        until has_data=0;
    end repeat;
    close emp_result;
end

call pro_test12()$

delimiter ;

存储函数

语法结构：

CREATE FUNCTION function_name([ param type ... ])
RETURNS type
BEGIN
    .....
END;

案例：
定义一个存储过程，请求满足条件的总记录数：

delimiter $

create function count_city(countryId int)
returns int # 此处不需要分号
begin
    declare cnum int;

    select count(*) into cnum from city where country_id = countryId;

    return cnum;
end$

delimiter ;

# 存储过程虽然没有返回值；但是依然可以返回结果；因为其参数列表当中有in、out（返回值）、inout；存储函数能做的事情；存储过程也可以做；

调用存储函数命令

select count_city(1)$
select count_city(2)$

删除存储函数命令

drop function count_city$

触发器 - 介绍

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

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

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

触发器 - 创建及应用

创建触发器

语法结构：

create trigger trigger_name

before/after insert/update/delete

# before 在...之前/after 在...之后

on tb1_name

[ for each row ] -- 当前操作的是一个行级触发器;oracle数据库当中既有行级触发器也有语句级的触发器；而mysql数据库当中只支持行级触发器现还不支持语句级触发器

begin
  trigger_stmt;--触发的内容
end;

需求：

通过触发器记录 emp表的数据变更日志，包含新增、修改、删除；

示例：

首先创建一张日志表:

create table emp_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型，insert/update/delete',
    operate_time datatime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的id',
    operate_params varchar(500) comment '操作参数',
    primary key('id')
)engine=innodb default charset=utf8;

show tables$

创建insert性触发器，完成插入数据时的日志记录；

DELIMITER $

CREATE trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
    insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(
      null,'insert',now(),new.id,concat( '插入后 ( id：', new.id , ', name: ' , new.name , ' ,  age: ' ,  new.age , ' , salary: ' , new.salary , ')')
    );
    # 此处的new.id即新插入emp表中数据的记录的id，即emp当中的id字段；
end$

insert into emp(id,name,age,salary) values(null,'光明左使',30,3500);

select * from emp$
select * from emp_logs$

insert into emp(id,name,age,salary) values(null,'光明右使',35,3800);


CREATE trigger emp_logs_update_trigger
after update
on emp
for each row
begin
    insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(
      null,'update',now(),new.id,
      concat( 
        '修改前 ( id：', old.id ,
        ',name: ' , old.name ,
        ',age: ' ,  old.age ,
        ',salary: ' , old.salary ,
        ') 修改后( id：', new.id ,
        ',name: ' , new.name ,
        ',age: ' ,  new.age ,
        ',salary: ' , new.salary ,
        ')'  
        )
    );
    # 此处的new.id即新插入emp表中数据的记录的id，即emp当中的id字段；
end$

update emp set age=39 where id=3;

select * from emp$
select * from emp_logs$

CREATE trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
    insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(
      null,'delete',now(),old.id,
      concat(
        '删除前( id：', old.id ,
        ',name: ' , old.name ,
        ',age: ' ,  old.age ,
        ',salary: ' , old.salary ,
        ')'  
        )
    );
end$

delete from emp where id=5;

select * from emp$
select * from emp_logs$

delete from emp where id=5;
select * from emp$
select * from emp_logs$

DELIMITER ;

触发器 - 查看及删除

删除触发器

语法结构：

drop trigger [ schema_name.] trigger_name

如果没有指定schema_name，默认为当前数据库。

查看触发器

可以通过执行SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
语法结构：

show triggers;

# Event 触发器监听的事件 INSERT/UPDATE/DELETE
# Table 对于哪一张表监听的触发器
# Timing 触发器是前置触发BEFORE还是后置触发AFTER
# Statement 触发器定义

Fengshana

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
全面深入讲解MySQL数据库优化01

全面深入讲解MySQL数据库优化01前言观看笔记：https://www.bilibili.com/video/BV1fJ41127Rj?from=search&seid=13531213796559959972本课程作为MySQL高级课程，主要讲解了：MySQL中的视图/存储过程/触发器/索引等对象的使用常见的SQL语句优化的技巧应用优化数据库优化数据库日志等方面的知识，并通过综合案例，对课程中的知识进行一个整合应用。本课程旨在通过MySQl高级部分内容，可以在满足现有业务
复制链接

扫一扫