MySQL_进阶
- (一)、存储引擎
- (二)、索引 (Index)
- 1.MySQL安装(Linux安装)
- 2.索引概述
- 3.索引结构_介绍 ⭐
- 4.索引结构_BTree (多路平衡查找树)
- 5.索引结构_B+Tree (BTree的变种)
- 6.索引结构_Hash ⭐(Hash冲突)
- 7.索引结构_思考题
- 8.索引_分类
- 9.索引_思考题 ⭐(回表)
- 10.索引_语法
- 11.SQL性能分析_执行频率 (工具一)
- 12.SQL性能分析_慢查询分析 (工具二)
- 13.SQL性能分析_profiles (工具三)
- 14.SQL性能分析_explain
- 15.索引使用_验证索引效率
- 16.索引使用_使用规则_最左前缀法 ⭐
- 17.索引使用_索引失效
- 18.索引使用_SQL提示 ⭐(索引推荐)
- 19.索引使用_覆盖索引&回表查询
- 20.索引使用_前缀索引
- 21.索引的设计原则
- (三)、SQL优化
- (四)、视图 (View)
- (五)、存储过程 (Procedure)
- (六)、存储函数 (Function)
- (七)、触发器 (Trigger )
- (八)、锁 (Lock)
- (九)、InnoDB 引擎
- (十)、MySQL 管理
(一)、存储引擎
1.MySQL体系结构
(1).连接层
最上层一些客户端和连接服务，主要完成一些类似于连接处理、认证授权、及相关的安全方案。服务器也会为安全接入的每一个客户端验证它所具有的操作权限。
(2).服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
(3).引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己需要，来选取合适的存储引擎。
(4).存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
2.存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
(1).查看某张表的数据引擎
show create table 表名;
(2).展示此版本支持的所有存储引擎
show engines;
(3).创建表my_myisam,并指定MyIASM存储引擎
create table my_myisam(
字段列表
)engine=存储引擎;
(4).存储引擎示列
-- 查询建表语句 (默认存储引擎- INNODB，mysql5.5版本之后都默认INNODB)
show create table account;
-- 查询当前数据库支持的存储引擎
show engines;
-- 创建表my_myisam,并指定MyIASM存储引擎
create table my_myisam(
id int(4),
name varchar(10)
)engine=MyISAM;
-- 查看这张表的数据
show create table my_myisam;
3.存储引擎 _ Innodb
(1).Innodb 介绍
Innodb是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，Innodb是默认的MySQL存储引擎。
(2).Innodb 特点
- DML操作遵循ACID模型，支持事务。
- 行级锁，提高并发性能问题,只锁一行。
- 支持外键 FOREIGN KEY 约束，保证数据的完整性和正确性。
(3).Innodb 磁盘文件
xxx.ibd : xxxx代表的是表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表结构(frm、adi)、数据和索引。
注意参数: innodb_file_per_table 如果是这个参数是打开的话，就说明每张表都会对应着一个表空间文件。(自MySQL8.0+以上版本默认开启)
show variables like 'innodb_file_per_table';
- 假如我们的MySQL是 8.0+ 的话，参数默认开启
假如我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表show variables like ‘innodb_file_per_table’; 1的表结构。
ibd2sdi 表名.idb;
(4).Innodb 逻辑存储结构
- 一个表空间里包含很多段。
- 一个区里面包含很多区。
- 一个区(内存大小1MB)里面包含很多页；也就是说一个区有64个页
- 一个页(内存大小16KB)里面包含很多行。
- 一个行里面包含: ①最后操作事务的id。②指针。③字段.
4.存储引擎 _ MyISAM
(1).MyISAM 介绍
MyISAM是MySQL早期的默认存储引擎。
(2).MyISAM 特点
- 不支持事务，不支持外键。
- 支持表锁，不支持行锁。
- 访问速度快。
(3).MyISAM 磁盘文件
- xxx.sdi : 存储表结构数据。
- xxx.MYD: 存储数据。
- xxx.MYI: 存储引擎。
5.存储引擎 _ Memory
(1).Memory 介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断点问题的影响，只能将这些表作为临时表或缓存使用。
(2).Memory 特点
- 内存存放
- hash索引 (默认)
(3).Meory 磁盘文件
- xxx.sdi: 存储表结构信息
6.三大存储引擎的区别
最主要的区别时: 事务、锁、外键。
(1).Innodb 和 MyISAM 区别
Innodb: 支持事务、支持行锁、支持外键。
MyISAM: 不支持事务、支持表锁、不支持外键。
(2).三大存储引擎总结
7.存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的系统应用系统，还可以根式实际情况选择多种存储引擎进行组合。
(1).Innodb （高并发 + 增删改查）
是MySQL的默认存储引擎，支持事务、外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么Innodb存储引擎是比较合适的选择。
(2).MyISAM (低并发 + 高读插)
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对对事务的完整性、并发性要求不是和高，那么选择这个存储引擎是非常合适的。 _日志、评论。
(3).MEMORY (访问速度快)
将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
(二)、索引 (Index)
1.MySQL安装(Linux安装)
(1).登入并连接阿里云的MySQL
安装之后，我们要进行密码验证。执行Linux命令 cd /www/server/mysql 进入mysql的文件夹。并执行 mysql -u root -p 回车并输入密码。
mysql -u root -p
2.索引概述
(1).索引介绍
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用 (指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
(2).索引演示
- 无索引_演示方式
比如说我们要: select *from user where age=45;。
基本算法就是: 从表头到表尾按照顺序进行遍历查找。时间复杂度为O(n)。
- 有索引_演示方式
比如说我们要: select *from user where age=45;。
基本算法： 从头节点进行二叉树遍历。二叉搜索树特点: 数字比中间的大走右边，数字比中节点小走左边。
(3).索引优缺点
优点：
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。
缺点：
3. 索引列也是要占用空间的。
4. 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete时，效率降低。（二叉树插入顺序插入没有数组快）
3.索引结构_介绍 ⭐
MySQL 的索引是存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:
(1).四种索引结构
- B+Tree索引: 最常见的索引类型，大部分引擎都支持B+树索引。
- Hash索引 : 底层数据结构用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
- R-Tree(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
- Full-text(全文索引):是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucence,Solr,ES。
| 索引 | Innodb | MyISAM | Memory |
|---|---|---|---|
| B+Tree | 支持 | 支持 | 支持 |
| Hash | ❌ | ❌ | 支持 |
| R-Tree | ❌ | 支持 | ❌ |
| Full-text | 5.6版本之后支持 | 支持 | ❌ |
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。
4.索引结构_BTree (多路平衡查找树)
(1).什么是二叉树
理想化二叉树: 我们插入数据和增加删除数据很遍历
非理想化二叉树:成链表状态插入和删除效率很低
二叉树缺点: 顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级越深，检索速度慢。
红黑树: 大数据量情况下，层级较深，检索速度慢。
(2).BTree
以一颗最大度数(max-degree)为5(5阶)的b-tree为列(每个节点最多存储4个key,5个指针)； N阶的tree为N个指针，N-1个key
(3).BTree 动态演示
动态查看一下我们的B-Tree数据:
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
依次插入数据: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250
结论: 我们发现我们的数据首先当达到第五个值的时候向上分裂一次，分裂的数据是五个数据的中间值，然后剩下的4个分成2个模块，依次类推。
5.索引结构_B+Tree (BTree的变种)
(1).B+Tree介绍
B+Tree是B树的变种，有着比B树更高的查询性能。
以一颗最大度数(max-degree) 为 4 (4阶) 的B+Tree为列:
- 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
- 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
(2).B+Tree 动态演示
我们使用数据为: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250。
(3).B+Tree 与 BTree的区别
B+Tree:
- 所有的数据都会出现在叶子节点上。
- 叶子节点形成一个单向链表。
- 向下分裂。
- 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
(4).MySQL下的B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能。
6.索引结构_Hash ⭐(Hash冲突)
(1). Hash索引介绍和Hash冲突
哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值,映射到对应的槽位上，然后存储在Hash表中。
Hash冲突: 哈希冲突是区块链网络中，两个节点间存在的一种特殊的数据交换方式。在区块链系统中，一个节点的状态会同步地传递给其他节点（或区块），每个区块都包含上一个区块的哈希值和本区块的哈希值。因此，当两个或多个节点之间存在数据交换时就会发生碰撞，这种碰撞称为"冲突"。当发生Hash冲突的时候可以使用链表来解决。
(2).Hash索引特点
- Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)。
- 无法利用索引完成排序操作。
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引。
(3).存储引擎支持
在MySQL,支持Hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
7.索引结构_思考题
(1).为什么InnoDB存储引擎选择使用B+Tree索引结构?
- 相对于二叉树或红黑树来说，层级更少，搜索效率高。
- 相对于BTree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对于Hash索引,B+Tree支持范围匹配和排序操作。
8.索引_分类
(1).四种索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以多有个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
| 单列索引 | 一个字段一个索引的 | 可以有多个 | |
| 组合索引 | 多个字段共同一个索引的 | 可以有多个 |
(2).InnoDB下的两种索引存储形式
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一索引(unique)作为聚集索引。
- 如果表中没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
比如 select *from user where name='Arm' 这个SQL语句。
- 假如name字段添加了二级索引。那么二级索引下面直接挂的就是二级索引的这个字段的数据+对应的主键，所以我们只需要通过一次二级索引全部查询到，不需要回表。
- 假如name字段没有添加二级索引。他会先选择走二级索引，因为二级索引存放的是某个字段的主键ID；然后再根据这个ID去聚集索引查找这一行的数据。由二级索引跳到聚集索引的过程我们称为 回表查询。
- 有索引的直接拿取这个字段+主键，没有索引的需要回表。
- 一个索引对应着一个B+Tree。
9.索引_思考题 ⭐(回表)
(1).以下SQL语句，那个执行效率高?为什么?
select *from user where id=10;
select *from user where name='Arm';
备注: id为主键,name字段创建的有索引。
第一个SQL语句执行效率高，因为这个不会触发回表查询，只需要执行一次聚集查询即可。
(2).InnoDB主键索引的B+tree高度为多高?
假设: 一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的高度，主键即使为bigint,占用字节数为8。
n代表key的数量，n+1代表指针的数量。注意1KB=1024B
n×8+6×(n+1)=16×1024,算出一个n约为1170.
1. 求高度为2:
所以得出一个根节点最多有1171个指针，指向下一层的1117个节点，又因为一个节点等于一行数据，
一行数据大小为16，所以如下:
16*1170=18376
2.求高度为3:
1171*1171*16
高度为三的图层:
(3).得出结论:
一个根节点下最多拥有1171个子结点，每一个子结点最多存储16行数据。
高度为m的树，占多少B?
1171^(高度m-1)*16 = 树高度m下的内存
10.索引_语法
(1).创建索引
1.一个索引可以关联多个字段。
一个索引只关联一个字段叫做单列索引；如果一个索引关联多个字段我们叫做联合索引或者组合索引。
2.假如声明unique则称为创建的是唯一索引；fulltext称为全文索引；假如都没有添加我们称为常规索引
create [unique|fulltext] index 索引名字 on 表名(表中哪个字段名称成为索引...);
(2).查看索引
show index from 表名;
(3).删除索引
drop index 索引名字 on 表名
(4).索引语法_演示
- 准备数据
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');
-- 1.name字段为姓名字段，该字段的只可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);
-- 2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
create unique index idx_user_phone on tb_user(phone);
-- 3.为profession、age、status创建联合索引
create index idx_user_pro_age_status on tb_user(profession,age,status);
-- 4. 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
show index from tb_user;
11.SQL性能分析_执行频率 (工具一)
(1).SQL执行频率
MySQL 客户端连接成功后，通过show [session | global] status命令提供服务器状态信息。提高如下质量，可以查看当前数据库的insert、update、delete、select的访问频次:
show global status like 'com_______'; #这里要是七个下划线
(2).SQL执行频率示列
show global status like 'com_______';
select *from emp;
show global status like 'com_______';
12.SQL性能分析_慢查询分析 (工具二)
(1).慢查询日志
慢查询日志记录了所有执行时间查过超过指定参数(long_query_time,单位:秒，默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf或my.ini)中配置如下信息:
(2).window下配置慢日志查询
1.查看慢日志是否开启
show variables like 'slow_query_log';
2.开启MySQL慢日志查询开关
slow_query_log=1
3.设置慢日志的时间为2秒,SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
window下进行配置:
查看我们的慢日志是否配置成功!
配置成功后，会自动生成一个日志文件
(3)Linux下配置慢查询日志
查询我们的Mysql配置文件 : cat /etc/my.cnf
阿里云自动配置的慢查询信息如下:
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
查看我们的慢查询日志信息:
cat /www/server/data/mysql-slow.log
实时查询我们尾部慢查询日志信息
tail -f /www/server/data/mysql-slow.log
由于我们的服务器比较小，所以不查询了。如果超过三秒数据就会在这里展示。
13.SQL性能分析_profiles (工具三)
(1).profile介绍
show profiles 能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持。
(1).查看当前数据库是否支持profile?
select @@have_profiling; #
(2).查看@@profiling是否打开
select @@profiling;
我们发现默认是关闭的，我们需要进行手动打开~~~
(3).开启我们的profils功能
set profiling =1;
(4).profiles 示列应用
1.查看全部语句的具体秒数
show profiles
- 查看指定Query_id语句各个阶段的耗时情况
show profile [cpu] for query 查询编号
未加上cpu的操作
加上cpu的消耗
14.SQL性能分析_explain
(1).explain执行计划
explain 或者 desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
(2).explain执行计划语法
[explain|desc] select 字段列表 from 表名 where 条件;
测试:
explain select *from tb_user;
(3).explain执行计划各个字段含义:
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(如果id相同，执行是顺序从上到下; 假如id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const(主键或唯一索引时候会出现)、eq_ref、ref(非唯一索引时出现)、range、 index、all 。 |
| possible_key | 显示可能应用在这张表上的索引，一个或多个。 |
| key | 实际使用的索引，如果为NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 |
(4).explain执行计划演示
- id标签 (并非是我们主键的id)
多对多表查询需要使用到中间表 student、course、student_course:
a.验证id相同的时候，顺序执行
-- 1.验证id相同的时候顺序执行。(查询所有学生的选课信息)
explain select s.*,c.* from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;
b.验证id不同时候，id越大越先执行。 (子查询实现id不同)
-- 2.验证id不同，id越大越先执行。(查询选修了MySQL课程的学生_子查询)
explain select s.* from student s,student_course sc where sc.courseid=(select course.id from course where course.`name`='MySQL') and s.id=sc.studentid;
结论: 子查询是由内向外的。
15.索引使用_验证索引效率
(1).正常查询一个数据_未使用索引
select *from tb_user; #有1千万条数据，耗时11.03秒 ⭐
(2).使用索引单独查询一条数据
select *frm tb_user where id=1; #耗时0.0秒 ⭐
(3).未使用索引单独查询一条数据
select *frm tb_user where name=''小米; #耗时20.0秒 ⭐
(4).给非索引字段创建一个索引
create index idx_user_name on tb_user(name); #先创建索引(创建的过程耗时很高) ⭐
先给这个字段创建索引再根据这个字段查询的时候，耗时降低很多
select *frm tb_user where name=''小米; #耗时0.0秒 ⭐⭐
结论: 给非主键字段创建好索引之后，再通过这个索引进行查询之后，我们的查询效率会显著提高。
16.索引使用_使用规则_最左前缀法 ⭐
(1).最左前缀法介绍
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列。索引将部分失效(后面的字段索引失效)。通俗的说就是:“我们创建联合索引的第一个字段，必须要存在于搜素字段中,where不在意顺序,order by,group by在意顺序。否则就会出现索引失效”
(2).最左前缀索引示列
我们这里对 age、profession、status这三个字段进行联合索引设置。
# 给字段常见联合索引
create index idx_user_pro_age_status on tb_user(profession,age,status);
1.查看执行计划
explain select *from tb_user where profession='软件工程' and age=31 and status='0';
(3).最左前缀法则索引失效场景
索引全部失效场景: 没有第一个索引字段。索引部分失效，跳过了中间列。
-- 索引全部失效: 因为没有第一个索引的字段
explain select *from tb_user where status='0';
-- 索引全部失效: 因为没有第一个索引的字段
select *from tb_user where status='0' and age=31;
-- 索引部分失效: 因为没有第三个索引的字段(所以只有俩索引)
explain select *from tb_user where profession='软件工程' and age=19;
-- 索引部分失效：因为跳过了中间列(所以只有一个索引)
explain select *from tb_user where profession='软件工程' and status='0';
-- 索引部分失效：因为最左索引在最后一个跳过了中间列 (所以只有一个索引)
explain select *from tb_user where status='0' and profession='软件工程';
-- 索引全部生效：因为每一个联合索引字段都存在(不存在排序)
explain select *from tb_user where age=31 and profession='软件工程' and status='0';
-- 索引： 假如key_len为null的话，那么就代表没有索引
explain select *from tb_user;
验证索引是否存在即 key_len的长度。
17.索引使用_索引失效
(1).范围查询
联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效。
#出现索引失效： 长度由原来的54变成49
explain select *from tb_user where profession='软件工程' and age>30 and status='0';
#恢复索引：长度回复成54，只需要加一个等于
explain select *from tb_user where profession='软件工程' and age>=30 and status='0';
(2).索引列运算
不要再索引列上进行运算操作，索引将失效。
#不使用运算操作
explain select *from tb_user where phone='17799990015';
# 使用运算操作
explain select *from tb_user where substring(phone,10,2)='15';
(3).字符串不加引号
字符串类型字段使用时，不加引号， 会进行自动转换。索引将失效。
# 字符串加引号会走索引
explain select *from tb_user where phone='17799990015';
# 字符串不加引号不会走索引
explain select *from tb_user where phone=17799990015;
(4).头部模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
# 尾部进行模糊匹配，索引不失效
explain select *from tb_user where profession like '软件%';
# 头部进行模糊匹配，索引失效
explain select *from tb_user where profession like '%工程';
(5).or连接条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都失效。
-- 前面有索引，后面没索引 失效
explain select *from tb_user where id=10 or age =23;
-- 前面没索引后面有索引 失效
explain select *from tb_user where age=23 or phone ='17799990017';
-- 前面和后面都有索引 不失效
explain select *from tb_user where id=18 or phone ='17799990017';
(6).数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。(通俗的讲就是假如查询的数据占整张表的小部分，才会使用索引。大部分不会使用)
# 全表24条数据，查询小部分使用索引
explain select *from tb_user where phone >='17799990020';
# 全表24条数据，查询整表效率没有顺序高，所以不走索引
explain select *from tb_user where phone >='17799990000';
# 全表24条数据，查询大部分效率没有顺序高，所以不走索引
explain select *from tb_user where phone >='17799990010';
# 全表24条数据，profession都不为null，所以占小数部分走索引
explain select *from tb_user where profession is null;
# 全表24条数据，profession都不为null，所以占大数部分不走索引
explain select *from tb_user where profession is not null;
18.索引使用_SQL提示 ⭐(索引推荐)
(1).什么是SQL提示?
# 1.查看profession是否已经是联合索引
explain select *from tb_user where profession='软件工程';
# 2再设置单列索引
create index idx_user_pro on tb_user(profession);
# 3.再次查看索引: 我们发现仍然使用的是单列索引
explain select *from tb_user where profession='软件工程';
(2).SQL提示
SQL提示: 是优化数据库的一个重要等后端，简单来说，就是在SQL语句中假如一些认为的提示来达到优化操作的目的。
(3).SQL提示的三种分类
1. use index: 告诉数据库推荐用指定的索引，推荐被介绍与否另说
explain select * from 表名 use index(索引) where 字段 = '软件工程';
2.ignore index: 告诉数据库不使用指定索引
explain select * from 表名 ignore index(索引) where 字段= '软件工程';
3. force index: 告诉数据库必须走这个索引
explain select * from 表名 ignore index(索引) where 字段= '软件工程';
(4).SQL提示测试
-- 推荐使用
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
-- 忽略指定
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
-- 强制使用
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
19.索引使用_覆盖索引&回表查询
(1).覆盖索引
尽量使用覆盖索引(查询条件使用了索引，并且需要返回的字段中，在该索引中全部能够找到)。减少select *。
-
知识小贴士:执行计划中最右侧的Extra。
- using index condition 或 NULL: 查找使用了索引，但是需要回表查询数据。
- using where; using index : 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询。
(2).覆盖索引示列
show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | |
| tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
注意:
1.where后面的条件是聚集索引的话，那么就不会触发回表。
2.where后面的条件是二级索引或辅助索引，那么要看返回的字段是什么。
-- 1. 利用*号 会回表，因为查询的字段有包含非索引字段
explain select *from tb_user where profession='软件工程' and age=31 and status='0';
-- 2. 查询id 和 profession 不会回表因为返回的字段都是索引，索引二级索引下就能获取到
explain select id,profession from tb_user where profession='软件工程' and age=31 and status='0';
--3. 查询id 和 profession 和 status 不会回表因为返回的字段都是索引，索引二级索引下就能获取到
explain select id,profession,status from tb_user where profession='软件工程' and age=31 and status='0';
--4. 查询id 和 profession 和 status 和name 会回表因为name不是索引
explain select id,profession,status,name from tb_user where profession='软件工程' and age=31 and status='0';
注意:
- where后面的条件是聚集索引的话，那么就不会触发回表，直接高效查询。
- where后面的条件是二级索引或辅助索引，那么要看返回的字段是索引字段还是非索引字段，假如包含非索引字段的话，那么就会触发回表，否则直接二级查询返回了。
20.索引使用_前缀索引
(1).前缀索引
当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引边得很大，查询时，浪费大量得磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
(2).前缀索引语法
create index idx_xxxx on table_name(column(n))
(3).前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
(4).前缀索引示列
# 查看不重复邮箱的个数
select count(distinct email) from tb_user;
# 查看 选择性=不重复索引数/总数
select count(distinct email)/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
| 1.0000 |
+--------------------------------+
# 截取前5个字符 分辨率已经是0.95了，已经有很大的分辨度了
select count(distinct substring(email,1,5))/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
| 0.9500 |
+--------------------------------+
# 创建索引 idx_email_5 并截取前缀索引的长度为5
create index idx_email_5 on tb_user(email(5));
# 查看执行计划，会经过回表查询的
explain select *from tb_user where email='jingke123@163.com';
21.索引的设计原则
(1).索引地七大原则
- 针对数据量较大，且
查询比较频繁的表建立索引。 - 针对于常作为
查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。 - 尽量选择
区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 - 如果是
字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表操作，提高查询效率。- 要
控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 - 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它，当优化器直到每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询。
(三)、SQL优化
1.SQL优化_插入数据
(1).建议批量插入
相对于单条SQL语句地插入，我们每次添加地时候都需要连接初始化等操作，这些操作也是消耗时间的。所以我们建议使用批量插入的语句可以降低这些消耗，建议批量插入的数据不超过1000条。
(2).建议手动提交事务
因为相对于自动提交事务而言，我们每次编写一个数据库语句都要进行数据的提交，这样来回的事务提交也是消耗时间的，所以我们建议手动提交事务。
start transaction';
insert into tb_user values(1,'Tom');
...
commit;
(3).建议主键顺序插入
相对于主键乱序插入，主键顺序插入我们进行遍历的时候顺序查找效率更高。
(4).大批量插入数据(三部曲)
如果一次性需要插入大批量数据(2000+)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。
将磁盘文件的信息加载到我们的MySQL中，每个字段用逗号分割且每行的最后一个字段不用加逗号但要换行。
- 第一步: 客户端连接服务端时
加上参数: --local-linfile
mysql --local-linfile -u root -p
- 第二部:设置全局local_linfile为1
设置全局参数开启从本地加载文件都如数据的开关
set global local_infile=1;
- 执行load指令将准备号的数据，加载到表结构中
linux系统下需要使用 '/'。window下需要使用 '//' 指向路径
load data local infile '路径/文件名.log' into table 表名 fields terminated by ';' lines terminated by '\n';
(5).测试大批量插入数据
1.表的框架结构
create table tb_user(
id int(4) not null auto_increment,
username varchar(50) not null,
password varchar(50) not null,
name varchar(20) not null,
birthday date default null,
sex char(1) default null,
primary key(id),
unique key `unique_user_username` (username)
);
# 1. 服务端连接
mysql --local-infile -u root -p
# 2. 查看我们是否自动开启
select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
-- 3.加载test_load.sql文件的数据，每个字段,分割，每一条用换行
load data local infile '/home/test_load.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
-- 4.查询是否插入成功 ⭐
select *from tb_user;
+----+-------------+-------------+----------+------------+------+
| id | username | password | name | birthday | sex |
+----+-------------+-------------+----------+------------+------+
| 1 | sdsdsdsdsd | sddsdsdsds | sdsdsds | 2001-12-17 | |
| 2 | sdsdsdsdsd2 | sddsdsdsd2s | sdsdsds2 | 2001-12-17 | |
+----+-------------+-------------+----------+------------+------+
中文插入不进去:
2.SQL优化_主键优化
(1).数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
(2).页分裂_乱序插入
页可以空，也可以填充一半，也可以填充100%.每个页面包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
主键乱序插入的时候，会出现页分裂。
(3).叶合并_顺序插入
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。(比如说我们常见的主键递增的时候，删除某一行之后，主键不会替换原来的主键值，而是继续累加)。
当页中删除的记录达到 50%，innodb会开始寻找最靠近的页(前或后)看看是否可以将两个页合并并以优化空间使用。
(4).主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用Auto_increment自增主键。
- 尽量不要使用UUID做主键或者其他自然主键，如身份证号等。
3.SQL优化_order by优化
(1). order by优化
- using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区 sort buffer中完成排序操作，
所有不是通过索引直接返回排序结果的排序都叫FileSort排序。 - using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index,不需要额外排序，
操作效率高。
create index idx_user_age_phone on tb_user(age,phone);
-- 1.符合最左前缀法则: 两面都升序:Using index ⭐(除了这个效率高)
explain select id,age,phone from tb_user order by age,phone;
-- 2.符合最左前缀法则: 前面升序,后面降序->Using index; Using filesor
explain select id,age,phone from tb_user order by age ,phone desc;
-- 3.符合最左前缀法则: 前面降序后面升序 ->Using index; Using filesor
explain select id,age,phone from tb_user order by age desc,phone;
-- 4.符合最左前缀法则: 前面和后面都降序->Using index; Using filesor
explain select id,age,phone from tb_user order by age desc,phone desc;
-- 违背最左前缀法则4种情况全是 :Using index; Using file
explain select id,age,phone from tb_user order by phone,age;
-- 再次创建排序索引
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);
-- 假如再次查询，那么发现变成 Using index
explain select id,age,phone from tb_user order by age ,phone desc;
(2).order by 优化总结
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引再创建时的规则(asc/desc)
- 如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size (默认256k)。
4.SQL优化_group by优化
(1).group by介绍
- 在分组操作时，可以通过索引来提高效率。
- 在分组操作时，索引的使用也是满足最左前缀法则的。
(2).group by示列
-- 1.先把所有的索引全部删除，留一个主键索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- 2.查看无索引状态下 执行计划 (效率偏低Using temporary)
explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
-- 3.创建联合索引 执行计划 (效率偏高⭐)
create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
-- 4.未使用最左前缀法则(执行计划效率偏低)
explain select age,count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
-- 5. 使用最左前缀法则 (执行效率偏高⭐)
explain select age,count(*) from tb_user where profession ='软件工程' group by age;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | const | 4 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
5.SQL优化_limit 优化
(1).limit介绍
首页=(页码-1)*10。
一个常见又非常头疼的问题就是 limit 200000,10; 此时需要MySQL排序前2000010记录，仅仅返回 2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案: 主键排序索引+子查询
(2).具体优化
--1.当查询大数据的时候，排序会占用很长的时间 耗时: 19.86s
select *from tb_user limit 100000,10;
优化方案: 子查询+索引
(1)首先查询出id,根据我们的主键索引,
select id from tb_user order by id limit 10000,10;
(2).然后进行子查询的操作
select t_u.* from tb_user t_u,(select id from tb_user order by id limit 10000,10) t2_u2 where t_u.id=t2_u2.id;
6.SQL优化_count 优化
(1).count介绍
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
- InnoDB 引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化思路: redis计数器。
(2).count地几种用法
- count()是一个聚合函数，对于返回地结果集，一行行地判断，如果count函数地参数不是NULL,累计值就加1，否则不加，最后返回累计值。
count(*): innodb引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。count(主键):innodb引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加。count(字段):- 没有not null约束:innodb引擎会遍历整张表把每一行字段都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
- 有not null约束:innodb引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。
count(1): innodb引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
效率排序: count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用count(*)。
7.SQL优化_update优化
(1).update优化_介绍
当我们更新的时候，where后面的判断条件不是索引字段，那么innodb会转变为表锁。如果判断条件是索引字段那么innodb会转化为行锁。
suoy
(2).update优化_示列
- innodb支持行锁，所以能够同时对一张表的不同行进行修改。
- 假如我们更新的时候的判断条件是非索引字段，那么会变成表锁，锁整张表。只要当这个表锁释放后其他数据才能继续改变。
注意:我们在更新数据的时候，所以条件一定要是索引字段，并且该索引字段不能失效，如果失效行锁就会升级为表锁。
(四)、视图 (View)
1.视图
(1).视图介绍
视图是一种虚拟存在的表。视图中的数据并不是在数据库中实际存在，行和列数据来自定义视图的查询中心使用的表，并且是在使用视图时动态生成的。
通俗的讲: 视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在了创建这条SQL查询语句上。
(2).视图的创建
create [or replace] view 视图名称[(列名列表)] as select 语句 [with [cascade | local] check option]
-- 1.创建视图
create or replace view stu_v_1 as select id,name from student where id<=10;
(3).视图的查询
1. 查看创建视图的语句
show create view 视图名称;
2.查看视图数据
select *from 视图名称...;
-- 2.查询视图的结构
show create view stu_v_1;
-- 3.查看视图的数据
select *from stu_v_1;
(4).视图的修改
1.第一种方式: 重载的方式
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
2.第二种方式:非重载的方式
alter view 视图名称[(列名列表)] as select 语句 [with [cascaded | local] check option]
-- 4.重写修改视图
create or replace view stu_v_1 as select id,name,no from student where id<=10;
-- 5.非重写修改视图
alter view stu_v_1 as select id,name,no from student where id<=10;
(5).视图的删除
drop view [if exists] 视图名称 ....
-- 6.删除视图
drop view if EXISTS stu_v_1;
2.视图_检查选项
(1).视图的检查选项
当使用 with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，列如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项: cascased 和 local, 默认是cascaded。
(2).多视图级联 (cascaded)
cascaded: 当视图中有一个视图进行了cascaded，那么基于这个casaded的表或者被这个cascaded表基于的表都要去进行关联。。
-- 1.没有添加检查选项的语句
create view v1 as select id,name from student where id<=20;
-- 2.添加了检查选项的语句 - 会进行约束检查(默认cascaded)
create view v2 as select id,name from where id<=20 with cascaded check option;
三张视图的级联关系...
-- cascaded 级联
-- 1.未创建级联的操作
create or replace view stu_v_1 as select id,name from student where id<=20;
-- 1.1 以下都能够插入成功!!!
insert into stu_v_1 values(5,'tom');
insert into stu_v_1 values(25,'tom');
-- 1.2基于视图stu_v_1 创建另一个视图 stu_v_2
create or replace view stu_v_2 as select id,name from stu_v_1 where id>=10 with cascaded check option;
insert into stu_v_2 values(7,'tom'); # 这里会报错，因为id要大于7
insert into stu_v_2 values(26,'tom'); # 这里会报错，因为视图stu_v_1与stu_v_2级联了，所以视图stu_v_2要同时满足这两个条件
insert into stu_v_2 values(15,'tom15'); #插入成功
-- 1.3基于视图 stu_v_2视图进行创建
create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
insert into stu_v_3 values(11,'tom11'); #能够插入成功
insert into stu_v_3 values(17,'tom17'); # 能够插入因为视图三没有检查约束
insert into stu_v_3 values(28,'tom28'); # 不能够被插入因为不满足视图一的要求
注意: 我们建立视图与视图之间进行级联的时候，级联(stu_v_2)满足被级联(stu_v_1)的视图的检查选项的时候，stu_v_2才会显示对应的视图，如果都不满足的话会先显示null。
结论: stu_v_2添加数据的时候既要满足stu_v_1也要满足stu_v_2。stu_v_3添加数据的时候既要满足stu_v_1和stu_v_2和stu_v_3。
(3).多级视图_(local)
local: 当基于的基表没有添加检索条件的时候，就不去关联；如果基于的基表添加了检索条件但自己没添加检索条件也不去关联；当自己添加了检索条件且基于的基表添加了条件的时候才会去关联。。
-- clocal 级联
-- 1.未创建级联的操作
create or replace view stu_v_4 as select id,name from student where id<=20;
-- 1.1 以下都能够插入成功!!!
insert into stu_v_4 values(5,'tom');
insert into stu_v_4 values(25,'tom');
-- 1.2基于视图stu_v_4 创建另一个视图 stu_v_5
create or replace view stu_v_5 as select id,name from stu_v_4 where id>=10 with local check option;
insert into stu_v_5 values(7,'tom'); # 这里会报错，因为id要大于10
insert into stu_v_5 values(26,'tom'); # 这里会成功，因为基表stu_v_4没有设置级联关系
insert into stu_v_5 values(15,'tom15'); #这里成功
-- 1.3基于视图 stu_v_5视图进行创建 (未创建级联)
create or replace view stu_v_6 as select id,name from stu_v_5 where id<=15;
insert into stu_v_6 values(9,'tom11'); #这里会成功 ⭐
insert into stu_v_6 values(17,'tom17'); # 能够插入因为视图三没有检查约束
insert into stu_v_6 values(28,'tom28'); # 能够被插入因为不满足视图一的要求 ⭐⭐
结论: 视图stu_v_5使用local不会kaolvstu_v_4。 stu_v_6不会有stu_v_5的约束。
(4).非检查选项_视图
我们可以创建出超过20的索引，并不会报。
-- 1根据基表student创建视图
create or replace view stu_v_1 as select id,name from student where id<=20;
-- 2.查看基表的视图
select *from stu_v_1;
-- 3.我们向这个视图插入一个数据，数据实际上是插入基表中而不是视图，视图不存放数据。
insert into stu_v_1 values(6,'tom');
insert into stu_v_1 values(30,'tom');
(5).检查选项_视图 (cascaded 级联)
添加检查索引之后，我们不能添加id超过20的索引了。
-- 1. 以重写的方式修改我们的视图 -> 加上视图检查选项 check ⭐
create or replace view stu_v_1 as select id,name from student where id<=20 with cascaded check option ;
-- 2.查看基表的视图
select *from stu_v_1;
-- 3.我们向这个视图插入一个数据，数据实际上是插入基表中而不是视图，视图不存放数据。
insert into stu_v_1 values(6,'tom');
insert into stu_v_1 values(30,'tom'); # ⭐⭐这里会报错，不让插入
(6).检查选项_视图(local)
-- 1. 以重写的方式修改我们的视图 -> 加上视图检查选项 local ⭐
create or replace view stu_v_1 as select id,name from student where id<=20 with local check option ;
-- 2.查看基表的视图
select *from stu_v_1;
-- 3.我们向这个视图插入一个数据，数据实际上是插入基表中而不是视图，视图不存放数据。
insert into stu_v_1 values(6,'tom');
insert into stu_v_1 values(30,'tom'); # ⭐⭐这里会报错，不让插入
3.视图_更新及作用
(1).视图的更新和插入
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新:
- 聚合函数或窗口函数(sum()、min()、max()、count())等。
- distinct
- group by
- having
- union 或者 union all
-- 创建视图，使用聚合函数
create view stu_v_count as select count(*) from student;
-- 1.插入失败，因为我们的视图用聚合函数创建了
insert into stu_v_count values(10);
(2).视图的作用
- 简单: 视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定的全部条件。比如(一个复杂的查询语句封装到一个视图上)
- 安全: 数据库可以授权，但不能授权到数据库特定和特定的列上。通过视图用户只能查询和修改它们所见到的数据。(Student表，我只给你展示id和name两个字段)
- 数据独立:视图可以帮助用户频闭真实表结构变化带来的影响。比如(在真实的数据库中student表的字段是name我们可以使用studentName替换)
create view stu_v_2 as select id, name as studentName from student;
4.视图_案列
-- 1.为了保证数据库的安全性，开发人员在操作tb_user表时，只能看到用户的基本字段，频闭手机号和邮箱。
create view student_1 as select id,`name`,age from tb_user;
select *from student_1;
-- 2. 查询每个学生所选修的课程(三集联表)，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
create view student_course_1 as select s.`name` as StudentName,c.`name` as courseName from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;
select *from student_course_1;
(五)、存储过程 (Procedure)
1.存储过程介绍
存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据的效率还是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
(1).存储过程_特点
- 封装、复用。
- 可以接收参数，也可以返回数据。
- 减少网络交互，效率提升。
2.存储过程_基本语法
(1).存储过程_创建
create procedure 存储过程名称([参数列表])
begin
--SQL语句
end;
(2).存储过程_调用
call 名称([参数])
(3).存储过程_查看
1.查询指定数据库的存储过程及状态信息
select *from infommation_schema.routines where routine_schema='数据库名';
2.查询某个存储过程的定义
show create procedure 存储过程名称;
(4).存储过程_删除
drop procedure [if exists] 存储过程名称;
(5).存储过程示列
-- 存储过程基本语法
-- 1.创建
create procedure p1()
begin
select count(*) from student;
end;
-- 2.调用
call p1();
-- 3.查看
select *from information_schema.ROUTINES where routine_schema = 'itheima';
show create procedure p1;
-- 4.删除
drop procedure if exists p1;
(6).命令行创建存储过程
注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL的结束符。
delimiter 指定符号; ->以指定符号为结束语句
-- 1.命令行方式不让创建，原因分号不对
create procedure p2()
begin
select count(*) from student;
end;
-- 2. 修改结尾命令符号之后再次进行创建
delimiter $$;
create procedure p2()
begin
select count(*) from student;
end$$
-- 最后一定要恢复成分号
delimiter ;
3.存储过程_系统变量
(1).什么是系统变量
系统变量 是 MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
(2).查看系统变量
1.查看所有系统变量
show [session|global] variables;
2.可以通过like模糊匹配方式查找变量
show [session|global] variables like '...'
3.查看指定变量的值
select @@[session|global] 系统变量名;
(3).设置系统变量
set [session|global] 系统变量名 = 值;
set @@[session|global] 系统变量 = 值；
(4).系统变量演示
-- 变量 系统变量
-- 1.查看所有的系统变量
show variables;
-- 模糊匹配查看系统变量
show session variables like 'auto%';
-- 查找指定变量的值信息
select @@session.autocommit;
-- 2.设置系统变量
set session autocommit =1;
注意:
- 如果没有指定session/global,默认是
session,会话变量。 - mysql服务重启之后，所设置的全局参数会失效，想要不失效的，可以在
my.inf中进行配置。
4.存储过程_用户变量
(1).用户变量_介绍
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名"使用就可以。其作用域为当前连接。两个@是系统变量，一个@是用户变量。
(2).用户变量_赋值
1.使用等号赋值
set @varn_name=expr[,@var_name=expr]...;
2.使用 :=进行赋值
set @var_name:=expr[,@var_name:=expr]...;
3.使用seelect 进行赋值
select @var_name:=expr[,@var_name:=expr]...;
4.将返回的字段放进变量中
select 字段名 into @var_name from 表名;
(3).用户变量_使用
select @var_name;
(4).用户变量_示列
-- 变量: 用户变量
-- 赋值
set @myname = 'itheima';
set @myage := 10;
set @mygender := '男',@myhobby :='java';
select @mycolor := '中国红';
select count(*) into @myId FROM student; #不能是数组，只能是一个单值
-- 使用
select @myname,@myage,@mygender,@myhobby;
注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null。
5.存储过程_局部变量
(1).局部变量_介绍
局部变量 是根据需要定义的局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin ..end块。
(2).局部变量_声明
declare 变量名 变量类型[default ...];
变量类型就是数据库字段类型: int 、bigint、char、varchar、date、time等。
(3).局部变量_赋值
-- 1.第一种
set 变量名=值;
-- 2.第二种
set 变量名:=值;
-- 3.第三种
select 字段名 into 变量名 from 表名...;
(4).局部变量_查看
select 局部变量名;
(5).局部变量_示列
-- 1.声明
create procedure p3()
begin
#声明局部变量 stu_count默认为0
declare stu_count int default 0;
#赋值
set stu_count := 100;
select stu_count;
end;
call p3();
6.存储过程_if (选择结构)
(1).if_语法
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;
(2).if_示列
根据定义的分数score变量，判定当前分数对应的分数等级。
- score >= 85分，等级为优秀。
- score>=60分 且 score <85分，等级为及格。
- score<60分,等级为不及格。
-- if
-- 根据定义的分数score变量，判断当前分数对应的分数等级
create procedure p3()
begin
-- 定义分数并写死
declare `score` int default 58;
-- 定义返回值
declare `result_value` varchar(10);
if score >=85 then
set result_value := '优秀';
elseif score >=60 then
set result_value := '及格';
else
set result_value := '不及格';
end if;
#查询
select result_value;
end;
call p3;
7.存储过程_参数(in,out,inout)
(1).三大参数介绍
| 类型 | 含义 | 默认 |
|---|---|---|
| in | 该参数作为输入，也就是需要调用时传入值 | 备注 |
| out | 该类参数作为输出，也就是该参数可以作为返回值 | |
| inout | 即可以作为输入参数，也可以作为输出参数 |
(2).参数_用法
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
-- SQL语句
end;
(3).参数_示列
- 根据传入参数score,判定当前分数对应的分数等级，并返回。
-- 根据定义的分数score变量，判断当前分数对应的分数等级
#设置传入的参数 和 输出的参数 ⭐
create procedure p4(in `score` int,out result_value varchar(10))
begin
if score >=85 then
set result_value := '优秀';
elseif score >=60 then
set result_value := '及格';
else
set result_value := '不及格';
end if;
end;
# 2.第一个是 传入的值。第二个是 接受的变量(用户变量) ⭐⭐
call p4(70,@result);
-- 3.查看返回的变量
select @result; ⭐⭐⭐
- 将传入的200分制的分数，进行转换为百分制，并返回
-- 1.设置输入输出函数
create procedure p5(inout score float)
begin
set score := score/2;
end;
-- 2.先设置默认值
set @score_a =149;
-- 3.传入参数并接收值
call p5(@score_a);
-- 4.查看返回值
select @score_a;
8.存储过程_case (选择结构)
(1).case_语法
1.第一种语法:
case 表达式
when 条件1 then 执行语句1
[when 条件2 then 执行语句2]...
[else 执行语句n]
end case;
2.第二种语法:
case
when 条件1 then 执行语句1
[when 条件2 then 执行语句2]...
[else 执行语句n]
end case;
(2).case_示列
- 根据传入的月份，判断月份所属的季节(要求采用case结构)。
-- 1-3月份 第一节度。 4-6月份 第二季度 7-9 为第三季度
create procedure p7(in `month` int, out seat varchar(10))
begin
-- 1.设置月份
case
when `month`>=1 and `month`<=3 then
set seat := '第一季度';
when `month`>=4 and `month`<=6 then
set seat := '第二季度';
when `month`>=7 and `month`<=9 then
set seat := '第三季度';
when `month`>=10 and `month`<=12 then
set seat := '第四季度';
end case;
end;
-- 2.调用函数
call p7(5,@seat1);
-- 3.查询用户变量
select @seat1;
9.存储过程_while (循环结构)
(1).while_语法
while 循环时有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。
# 先判定条件，如果条件为true,则执行逻辑，否则，不执行逻辑
while 条件 do
SQL逻辑...
end while;
(2).while_示列
- 计算从1累加到100的值.
-- 1.定义局部变量，
create procedure p9()
begin
# 1.1 创建局部变量
declare count int default 0;
# 1.2循环
while count<=100 do
# 1.3进行累加
set count := `count`+1;
end while;
select count;
end;
#查看
call p9;
10.存储过程_repeat (循环结构)
(1).repeat_语法
repeat是有条件的循环控制语句，当满足条件的时候退出循坏。具体语法:
先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出；如果不满足，则继续下一次循环。 (类似于Java的 do while(){})
repeat
SQL逻辑...;
until 条件 #这里不能有分号
end repeat;
(2).repeat_示列
- 计算从1累加到n得值，n为传入的参数值。
-- 1.函数
create procedure p10(in n int)
begin
-- 2.创建一个局部变量并设置默认值为0
declare total int default 0;
-- 3.设置repeat
repeat
set total :=total +1;
set n := n-1;
until n<=0
end repeat;
-- 查询
select total;
end;
call p10(10);
11.存储过程_loop (循环结构)
(1).loop_语法
loop 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。Loop可以配合一个两个语句使用:
- leave: 配合循环使用，
退出循环。 类似于 break; - iterate: 必须用在循环中，作用是
跳过当前循环剩下的语句，直接进入下一次循环。类似于 continue;
[开始标签]: loop
SQL逻辑...
end loop [结束标签];
leave 标签; -- 退出指定标记的循环体
iterate 标签; -- 直接进入下一次循环
(2).loop_示列
- 计算从1到n偶数累加的值，n为传入的参数值。
create procedure p1(in n int)
begin
-- 1. 声明一个局部变量
declare total int default 0;
-- 2. 进行循环的操作
sum:loop
-- 3. 判断是否小于0 离开
if n<0 then
leave sum;
end if;
-- 4. 假如是奇数的话，跳过
if n%2 = 1 then
set n := n-1;
iterate sum;
end if;
-- 5. 加在一起
set total := total +n;
set n := n-1;
end loop sum;
select total;
end;
call p1(10);
12.存储过程_cursor (游标)
(1).游标_介绍
游标(cursor) 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch 和 close。 相当于说就是能够存储数组了。
(2).游标_语法
1.声明游标
declare 游标名称 cursor for 查询语句;
2.打开游标
open 游标名称;
3.获取游标记录
fetch 游标名称 into 变量 [,变量...];
4.关闭游标
close 游标名称;
(3).游标_示列
根据传入的参数uage,来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession),并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中.。
create procedure p1(in uage int)
begin
-- 1. 声明接受的值。
declare uname varchar(100);
declare uprofession varchar(100);
-- 2.声明游标
declare u_cursor cursor for select `name`,`profession` from tb_user2 where `age`<=uage;
-- 3.如果表不存在就创建
drop table if exists tb_user_proinfo;
create table if not exists tb_user_proinfo(
id int(4) primary key auto_increment,
name varchar(100),
profession varchar(100)
);
-- 4.开启游标
open u_cursor;
-- 5. 循环
WHILE true DO
-- 6.将数据插入信息
fetch u_cursor into uname,uprofession;
-- 7. 插入新建的表中
insert into tb_user_proinfo values (null,uname,uprofession);
END WHILE;
-- 8. 关闭游标
close u_cursor;
end;
call p1(100);
select *from tb_user_proinfo;
1.普通变量要比游标变量先声明
2.执行函数，但会报错。报错的原因当我们从游标读出来之后，还是一直循环的，所以会报错。
3.数据能够加入，但是调用函数的时候会报错
13.存储过程_handler(条件处理程序)
(1).条件处理程序_介绍
条件处理程序(Handler) 可以用来定义在流程控制结构执行过程中遇到的问题时相应的处理步骤。类似于 try catch(){}
(2).条件处理程序_语法
declare 处理程序类型 handler for 条件 [,条件2] ... SQLStatement;
处理程序类型:
1. continue: 继续执行当前程序
2. exit: 终止当前程序
条件:
sqlstate sqlstate_value: 状态码 如02000
1. sqlwarning: 所有以01开头的sql状态码
2. not forun: 所有以02开头的sql状态码
3. sqlexception: 所有没有被01 或02 开头的状态码。
(3).修复游标残留下的问题
create procedure p1(in uage int)
begin
-- 1. 声明接受的值。
declare uname varchar(100);
declare uprofession varchar(100);
-- 2.声明游标
declare u_cursor cursor for select `name`,`profession` from tb_user2 where `age`<=uage;
-- 声明条件处理程序，当状态码为0200的时候退出并关闭游标 ⭐
declare exit handler for sqlstate '02000'close u_cursor;
-- 3.如果表不存在就创建
drop table if exists tb_user_proinfo;
create table if not exists tb_user_proinfo(
id int(4) primary key auto_increment,
name varchar(100),
profession varchar(100)
);
-- 4.开启游标
open u_cursor;
-- 5. 循环
WHILE true DO
-- 6.将数据插入信息
fetch u_cursor into uname,uprofession;
-- 7. 插入新建的表中
insert into tb_user_proinfo values (null,uname,uprofession);
END WHILE;
-- 8. 关闭游标 ⭐⭐
# close u_cursor;
end;
call p1(100);
select *from tb_user_proinfo;
(六)、存储函数 (Function)
1.存储函数
(1).存储函数_介绍
存储函数是由返回值的存储过程，存储函数的参数只能是IN类型的。
(2).存储函数_语法
create function 存储函数名称 ([参数列表])
returns type [可选参数特性...]
begin
-- SQL语句;
return ...;
end;
可选参数特性characteristic说明: mysql8.0+必须要写
1. determinstic: 相同的输入参数总是产生相同的结果
2. not sql : 不包含sql语句
3. reads sql data: 包含读取数据的语句，但不包含写入数据的语句。
(3).存储函数_示列
计算从1累加到n的值，n为传入的参数值。
#默认为in且只能为in
create function fun_1(n int)
returns int
begin
declare total int default 0;
WHILE n>0 DO
set total := total+1;
set n := n-1;
END WHILE;
return total;
end;
select fun_1(10);
(七)、触发器 (Trigger )
1.触发器_介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在 触发器还只是支持行级触发，不支持语句级触发。
行级触发器: 假如执行一条SQL语句，这一条SQL影响了N行，这个触发器会执行N次。
语句级触发器: 假如执行一条SQL语句，这一条影响了N行,这个触发器只会执行1次。
| 触发器类型 | new 和 old |
|---|---|
| insert 型触发器 | new 表示将要或者已经新增的数据 |
| update 型触发器 | old 表示要修改之前的数据，new 表示将要或已经修改后的数据 |
| delete 型触发器 | old 表示将要或者已经删除的数据 |
2.触发器_语法
1.创建触发器
create trigger 触发器名字
[before/after] [什么型触发器]
on 表名 for each row --行级触发器
begin
触发器语法;
end;
2.查看触发器
show triggers;
3.删除触发器
drop trigger [指定数据库名] 触发器名; --如果没有指定触发器，默认当前数据库。
3.触发器_案列 (insert 类型)
通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表 user_logs中，包含增加、修改、删除；
一定要注意日志表的编码要和绑定的表的编码一致。否则报错Incorrect string value: '\xBD\xAA\xD7\xD3\xD1\xC0...' for column 'operate_pa
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=gbk;
-- 在主键自增的SQL表中，如果我们没有在表后面跟着所有的字段，那么我们需要给主键自增赋值为null。 如果在表后面跟了其他字段但没有主键字段，那么就不用写(默认为null)。
-- 1.插入触发器的语法
create trigger tb_user_insert_trigger
-- 给tb_user2表行锁
after insert on tb_user2 for each row
begin
-- 利用new来获取我们新插入的数据
insert into user_logs values(null,'insert',now(),new.id,concat('插入的数据内容为: id=',new.id,'name=',new.name,'phone=',new.phone,'email=',new.email,'profession=',new.profession));
end;
--2.查询
show triggers;
-- 3.插入数据 (这里主键id的字段没写，所以不用添加null给主键，主键默认赋值为null)
INSERT INTO tb_user2 (name, phone, email, profession, age, gender, status,createtime) VALUES ('姜子牙4', '17799990023', '11111111@qq.com', '土木工程', 29,'1', '4', now());
4.触发器_案列 (update 类型)
-- 2.修改触发器
create trigger tb_user_update_trigger
after update on tb_user2 for each row
begin
insert into user_logs values(null,'update',now(),new.id,concat(
'UpdateBefor: id=',new.id,'name=',new.name,'phone=',new.phone,'email=',new.email,'profession=',new.profession,
'updateAfter: id=',old.id,'name=',old.name,'phone=',old.phone,'email=',old.email,'profession=',old.profession
));
end;
update tb_user2 set age='11' where id=20;
5.触发器_案列(delete 类型)
create trigger tb_user_delete_trigger
after delete on tb_user2 for each row
begin
insert into user_logs values(null,'delete',now(),old.id,concat(
'delete: id=',old.id,'name=',old.name,'phone=',old.phone,'email=',old.email,'profession=',old.profession
));
end;
show triggers;
delete from tb_user2 where id = 21;
(八)、锁 (Lock)
1.锁_概述
(1).锁_介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、IO)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
(2).锁_分类
MySQL中的锁，按照锁的粒度分，非为以下三类:
- 全局锁: 锁定数据库中的所有表。
- 表级锁: 每次操作数据锁住整张表。
- 行级锁: 每次操作锁住对应的行数据。
2.锁_全局锁 ⭐
(1).全局锁_介绍
全局锁就是对整个数据库实列加锁，加锁后整个示列就处于只读状态，后续的DML(增加/修改)的写语句，DDL(删除)语句，已经更新操作的事务提交语句都将被阻塞。
典型: 做数据库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
全局锁: 可以查询、不能读取、删除、修改
(2).全局锁_语法
1.加全局锁的操作
flush tables with read lock;
2. 数据备份的操作
mysqldump -uroot -p 123456 itcast> itcast.sql;
3.解锁
unlock tables;
(3).全局锁_实列
运用全局锁的操作进行备份我们的数据库信息。
注意全局锁:锁的是所有数据库的所有表，不是当前数据库的所有表。
1.我们需要在CMD中运行
mysqldump [-h远程服务器IP] -uroot -p121788 itcast > E:/itcast.sql;
2.在cmd中使用mysqldump这个插件，如果显示没有命令，需要全局配置环境
3.配置环境
4.数据库备份成功!
(4).全局锁_特点
数据库中加全局锁，是一个比较重的操作，存在以下问题:
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
在innodb引擎中,我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump --single -transaction -uroot -p121788 itcast > E:/itcast2.sql
3.锁_表级锁 ⭐
(1).表级锁_介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、Innodb、DBD等存储引擎中。
(2).表级锁_分类
- 表锁
- 元数据锁 (meta data lock.MDL)
- 意向锁
4.表级锁_表锁
(1).表锁_分类
- 表共享读锁 (S: share_read) -> (所有客户端都能读取，但是不能增删改，除非解锁)
- 表独占写锁 (X: Exclusive Lock) ->(只有加锁的客户端享有增删改查，其他所有的客户端都没有权力，除非解锁)
(2).表锁_语法
- 加锁
lock tables 表名... read/write。 #读锁或写锁
- 释放锁
unlock tables 或者关闭客户端
如果一个客户端加了锁读锁，那么这个客户端只能读取这个表，不能插入/删除/修改 这个锁。 其他客户端也只能进行读取锁，不能进行插入/删除/修改。 直到这个读锁解开。 (只会阻塞修改/删除/添加)
(3).表锁_示列
1.设置读锁，设置读锁的客户端，修改或者删除表就会报错。另一个客户端可以查询，增删改的时候等待锁的客户端结束才会进行运行否则一直等待不报错。
2.设置写锁: 只有上锁的客户端才能读取和增删改，其他的客户端没有任何权限直到上锁的客户端结束。
(4).表锁_总结
都只能进行访问上锁的表，没上锁的表都不能访问。
5.表级锁_元数据锁
(1).元数据锁_介绍
元数据锁 (meta data lock,MDL) 加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务(未提交的事务)的时候，不可以对元数据进行写入操作。
在MySQL 5.5中引入了MDL,当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。
| 对应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 | exclusive lock (行级排他锁) | 与shared_read、shared_write兼容、与exclusive互斥 |
| alter table … | exclusive (排锁) | 与其他的MDL都互斥 |
(2).元数据锁_示列
1.其他事务运行中，其他客户端可以进行读和取
2. 其他事务运行中，其他客户端不能修改表结构
(3).总结
- 有事务运行的时候，其他客户端不能对表结构进行修改。
- 有事务在运行的时候，其他客户端都可以进行读和写。
- 共享读锁能够与排他锁(行锁)兼容。
1.才查看锁结构 MySQL(5.7+)
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
6.表级锁_意向锁 (LS)
(1).意向锁_介绍
为了你面DML在执行时，加的行锁与表锁的冲突，在innoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
(2).意向锁_分类
- 意向共享锁(IS): 由语句 select … lock in share mode 添加。
- 意向排他锁(IX): 由insert、update、delete、select … from update添加。
| 锁 | 表锁共享锁(S read) | 表锁排他锁 (X write) |
|---|---|---|
| 意向共享锁 | 兼容 | 互斥 |
| 意向排他锁 | 互斥 | 互斥 |
意向锁与意向锁之间不会互斥。
(3).意向锁_示列
- 测试意向共享锁与读锁兼容，写锁不兼容
1.设置行锁且加意向锁
select *from emp where id=1 lock in share mode;
2.监视意向锁和行锁 (8.0+)
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
- 意向排他锁与读/写锁互斥
update tb_user set name='11' where id=1;
7.锁_行级锁 ⭐
(1).行级锁_介绍
行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
(2).行级锁_分类
-
行锁 (Record Lock): 锁定单个行记录的锁，防止其他事务对此进行
update和delete。在RC、RR隔离级别下都支持。
-
间隙锁(Gap Lock): 锁定索引记录间隙(不含该记录),确保
索引记录间隙不变(间隙锁),防止其他事务在这个间隙进行insert,产生幻读。(在RR隔离界别下都支持)。
-
临键锁(Next-key lock): 行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap,(在RR隔离级别下支持)。
8.行级锁_行锁
(1).行锁_分类
在InnoDB实现了以下两种类型的行锁:
- 共享锁(S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。(共享锁与共享锁兼容，共享锁与排他锁互斥)
- 排他锁(X): 允许获取排他锁的事务更新数据，组织其他事务获得相同数据集的共享锁和排他锁。(假如一个事务获取了这行的排他锁，那么不允许其他事务获取这行的排他锁和共享锁)
| 请求锁类型 | S(共享锁 read) | X(排他锁 write) | |
|---|---|---|---|
| 当前锁类型 | |||
| S(共享锁 read) | 兼容 | 互斥 | |
| X(排他锁 write) | 互斥 | 互斥 |
(2).行锁_语法
| SQL | 行锁类型 | 说明 |
|---|---|---|
| Insert … | 排他锁 | 自动加锁 |
| update … | 排他锁 | 自动加锁 |
| delete … | 排他锁 | 自动加锁 |
| select (正常) | 不加任何锁 | |
| select … lock in share mode | 共享锁 | 需要手动在select之后加 lock in share moe |
| select … for update | 排他锁 | 需要手动在select之后加 for update |
(3).行锁_演示
默认情况下，InnoDB在 Repeatable Read 事务隔离级别运行，Innodb使用 next-key 锁进行搜索和索引扫描，防止幻读。
- 针对
唯一索引进行检索时，对已存在的记录进行等值匹配上，将会自动有优化行锁。 - 在innoDB的行锁,不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。
- 共享锁与共享锁兼容
- 共享锁与排他锁互斥
- 检索条件不是索引的时候，行锁会升级为表锁
检索的条件不是id这写索引。
9.行级锁_间隙锁&临键锁
(1).间隙锁_临键锁示列
默认情况下,InnoDB在 Repeattable Read 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 索引上的等值查询(
唯一索引),给不存在的记录加锁时,优化为间隙锁。 - 索引上的等值查询(
二级索引且非唯一),向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁+临键锁。 - 索引上的范围查询(
唯一索引) 会访问到不满足条件的第一个值为止。 退化为临键锁
- 给不存在的记录加锁时，优化为间隙锁
事先删除id为12~15的数据。
- 向右遍历最后一个值不满足的时候 会优化为 临键锁和间隙锁
对age进行添加非唯一索引。
- 范围查找 会转变为间隙锁
从哪开始查就是无穷到哪。
- LS: 代表意向锁。
- S：代表共享锁。
- REC_NOT_GAP: 没有间隙也就是行锁
- GAP：间隙锁
注意: 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
(九)、InnoDB 引擎
1.InnoDB_逻辑存储结构 ⭐
表空间-> 段 -> 区 -> 页 ->行。
(1).逻辑存储结构_表空间 (TableSpace)
表空间(ibd文件),一个mysql实列可以对应多个表空间，用于存储记录、索引等数据。
(2).逻辑存储结构_段(Segment)
段，分为数据段(Left node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)、InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。
(3).逻辑存储结构_区 (Extent)
区，表空间的单元结构，每一个区大小为1MB.默认情况下,InnoDB存储引擎页大小为16K.即一个区中共有64个连续的页。
(4).逻辑存储结构_页(Page)
页,是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16K.为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。
(5).逻辑存储结构_行
行，InnoDB存储引擎数据是按行进行存放的。
- Trx_id: 每次对某条记录进行改动时，都会把对应的事务id复制给Trx_id隐藏列。
- Roll_pointer: 每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
2.InnoDB_架构_内存结构 ⭐
(1).InnoDB_内存结构介绍
MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。
下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。
在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。
(2). InnoDB_内存结构 Buffer Pool (缓冲池)
缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，① 先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），② 然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
- free page：空闲page，未被使用。
- clean page：被使用page，数据没有被修改过。
- dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
(3).InnoDB_内存结构 Change Buffer (更改缓冲区)
Change Buffer 更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
Change Buffer的意义是什么呢?
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO
(4).InnoDB_内存结构 Adaptive Hash Index
自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数: innodb_adaptive_hash_index
show variables like '%hash_index%';
(5).InnoDB_内存结构 Log Buffer (日志缓冲区)
Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。
参数:
-
innodb_log_buffer_size：缓冲区大小
-
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机。取值主要包含以下三个：
- 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
- 0: 每秒将日志写入并刷新到磁盘一次。
- 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。
3.InnoDB_架构_磁盘结构
(1).磁盘结构_System Tablespace (系统表空间)
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)
参数：innodb_data_file_path
show variables like 'innodb_data_file_path';
(2).磁盘结构_File-Per-Table Tablespaces (独立表空间)
如果开启了innodb_file_per_table开关，则每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。
开关参数：innodb_file_per_table ，该参数默认开启。
show variables like 'innodb_file_per_table';
如果开启的就代表: 没一张表都会生成一个表空间文件(ibd);
(3).磁盘结构_General Tablespaces (通用表空间)
通用表空间，需要通过CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。
- 创建表空间
CREATE TABLESPACEt '创建的表空间名' ADD DATAFILE '文件名' ENGINE=引擎名;
- 创建表时指定表空间
create table 创建表语句() tablespace 指定的表空间名;
(4).磁盘结构_Undo Tablespaces (撤销表)
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。
(5).磁盘结构_Temporary Tablespaces (临时表空间)
InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
(6).磁盘结构_Doublewrite Buffer Files (双写缓冲区)
双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
(7).磁盘结构_Redo Log(重做日志)
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo logbuffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。
4.InnoDB_架构_后台线程
后台线程的作用就是将缓冲区的文件加载到磁盘文件中。
在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。
(1).后台线程_ Master Thread (主线程)
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。
(2).后台线程_IO Thread (IO线程)
在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read thread | 4 | 负责读操作 |
| Write thread | 4 | 负责写操作 |
| Log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息。
show engine innodb status;
(3).后台线程_Purge Thread (回收线程)
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。
(4).后台线程_Page Cleaner Thread (清理页线程)
协助Master Thread 刷新脏页到磁盘的线程，它可以减轻Master Thread 的工作压力，减少阻塞。
5.InnoDB_事务 ⭐
(1).事务原理_概述
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
- 原子性: 事务不能在分割，要么全部成功幺二秒全部失败。
- 一致性: 事务开启前后，数据要保持一致性。
- 隔离性: 不同事务之间相互隔离，互不影响。
- 持久性: 事务一旦提交或回滚，他对数据库的改变是永久的。
6.InnoDB_事务_Redo log (解决持久性)
(1).redo log 介绍
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久型。
该日志文件由两部分组成: 重做日志缓冲 (read log buffer)以及重做日志文件 (redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于刷新胀页的磁盘，发生错误时，进行数据恢复使用。
(2).没有redo log 的时候
我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
(3).有redo log 的时候
那么，如何解决上述的问题呢？在InnoDB中提供了一份日志redo log，接下来我们再来分析一下，通过redolog如何解决这个问题。
有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。
那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢?
因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为WAL（Write-Ahead Logging）。
7.InnoDB_事务_Undo log (解决原子性)
(1).undo log 介绍
回滚日志，用于记录数据被修改前的信息, 作用包含两个: 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。
undo log和redo log(物理日志)不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录(旧数据)，反之亦然，当update一条记录时，它记录一条对应相反的update记录(旧数据)。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
-
Undo log销毁：undolog在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
-
Undo log存储：undolog采用段的方式进行管理和记录，存放在前面介绍的rollback segment 回滚段中，内部包含1024个undo log segment。
8.InnoDB_MVVC ⭐
(1).当前读 (读取最新)
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录加锁。对于我们日常的操作，如: select … lock in share mode (共享锁)； select … for updare、insert、delete (排他锁)都是一种当前读。
1. 验证读取最新之: 使用当前读
可以读取另一个事务已提交的数据。
(2).快照读 (读历史数据)
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed：每次select，都生成一个快照读。
- Repeatable Read：开启事务后第一个select语句才是快照读的地方。
- Serializable：快照读会退化为当前读。
1.验证快照读，读的是历史数据
可以读取已提交的历史数据。
(3).MVCC (多版本并发控制)
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
9.InnoDB_MVVC_隐藏字段
(1).记录中的隐藏字段
当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 |
| DB_ROLL_PTP | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。 |
(2).查看记录中的隐藏字段。
查看数据库中 tb_user表的全部信息包括隐藏字段 MySQL8.0+
idb2sdi tb_user.idb
查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有额外的两个字段 分别是：DB_TRX_ID 、 DB_ROLL_PTR ，因为该表有主键，所以没有DB_ROW_ID隐藏字段，如果表中没有主键那么就会显示。
10.InnoDB_MVVC_undo log (回滚日志)
(1).undo log_ 介绍
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
- 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
- 当update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
(2).undo log_版本链
保存的undo log日志，假如客户端有任何一个事务在活动，那么undo log日志不会被立即删除。
当我们执行update/delete的之前会差生undo log日志。
最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
11.InnoDB_MVVC_readview (读视图)
(1).readview_介绍
ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务ID集合 |
| min_trx_id | 最小活跃事务ID |
| max_trx_id | 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的） |
| creator_trx_id | ReadView创建者的事务ID |
(2).readview中就规定版本链数据的四大访问规则
| 条件 | 是否可以访问 | 说明 |
|---|---|---|
| trx_id ==creator_trx_id | ✅ | 成立，说明数据是当前这个事务更改的。 |
| trx_id < min_trx_id | ✅ | 成立，说明数据已经提交了。 |
| trx_id > max_trx_id | ❌ | 成立，说明该事务是在ReadView生成后才开启。 |
| min_trx_id <= trx_id<= max_trx_id | 如果trx_id不在m_ids中，是可以访问该版本的 | 成立，说明数据已经提交。 |
不同的隔离级别，生成ReadView的时机不同：
- READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
- REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
12.InnoDB_MVVC_原理
(1).在RC隔离级别下的ReadView
RC隔离级别下，在事务中每一次执行快照读时生成ReadView。
1.生成的readView
2.版本链和四大访问规则用法:
这里我们看的是第一个读视图与四大访问规则的用法，其他都同理。
总结: 先Commit的事务，后面的事务都能查询到(也就是回滚不到了)。
(2).在RR隔离级别下的ReadView
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。
13.总结
MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。
(十)、MySQL 管理
1.系统数据库_四个默认库
MySQL数据库安装完成后，自带了一下四个数据库，具体作用如下:
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息(时区、主从、用户、权限) |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含(数据库、表、字段类型及访问权限) |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图 |
2.MySQL管理_常用工具
(1).MySQL (CMD客户端)
该mysql不是指mysql服务，而是指mysql的客户端工具。
在cmd窗口下运行
语法 ：
mysql [options] [database]
选项 ：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出
-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。
mysql -uroot –p123456 数据库名 -e "SQL语句";
eg:
mysql -uroot –p123456 db01 -e "select * from stu";
(2). mysqladmin (管理操作客户端)
mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
通过帮助文档查看选项：
mysqladmin --help
语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
实列:
mysqladmin -uroot –p1234 version
(3).mysqlbinlog (二进制客户端)
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。
语法 ：
mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
-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 指定位置间隔内的所有日志。
示列
mysqlbinlog binlog.000008 #查询二进制文件为binlog.000008的数据
(4).mysqlshow (对象查找客户端) ⭐
mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
语法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
示列:
- 查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p1234 --count
2. 查看数据库itheima全部表的统计信息
mysqlshow -uroot -p1234 itheima--count
3. 查看数据库itheima中的指定tb_user表的信息
mysqlshow -uroot -p121788 itheima tb_user --count
- 查看数据库itheima中的tb_user表的id字段的信息
mysqlshow -uroot -p121788 itheima tb_user id --count
(5).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文件，数据文件
示列:
- 备份itheima数据库 (包括表结构和表的内容)
mysqldump -uroot -p1234 itheima> E:/itheima.sql
- 备份itheima数据库 (包括数据，不包括表结构)
mysqldump -uroot -p1234 -t itheima> E:/itheima.sql
- 将db01数据库的表的表结构与数据分开备份(-T)
mysqldump -uroot -p1234 -T e:/ itheima tb_user
执行上述指令，会出错，数据不能完成备份，原因是因为我们所指定的数据存放目录E盘，MySQL认为是不安全的，需要存储在MySQL信任的目录下。那么，哪个目录才是MySQL信任的目录呢，可以查看一下系统变量 secure_file_priv 。执行结果如下：
show variables like '%secure_file_priv%';
mysqldump -uroot -p1234 -T C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ itheima tb_user
(6).mysqlimport/source (数据导入客户端)
- txt文件
mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件(.txt文件)。
语法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 数据库名 /tmp/city.txt
- sql文件 (需要在指定MySQL的库下)
如果需要导入sql文件,可以使用mysql中的source 指令 :
语法 ：
use itheima;
source /root/xxxxx.sql
98
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
