视图

​ 视图就是将一条sql语句封装起来，之后使用sql时，只需要查询试图即可，视图不保存数据，

-- 定义一个视图
create view view_admin as select * from admin
-- 使用视图
select * from view_admin
-- 删除视图
drop view view_admin

存储过程

​ 将一些逻辑处理代码可以事先存储在数据库中，然后使用时直接调用即可，可以减少应用程序与数据库之间交互的次数。

不需要重启服务器，在数据库中直接修改判断，脚本语言直接更新。

使用地方

​ 保存账号时，使用存储过程验证账号是否存在，并返回一个结果

​ 入参 账号，密码，性别 出参 结果

语法

​ 存储过程实现需要创建好（包含逻辑），可以向存储过程中传入参数，参数分为三种，in(输入参数)，out(输出参数)，inout(既可以输入，亦可以输出)

定义第一个存储过程

create procedure 存储过程名 ([in 变量名 类型，out 参数2，...])
begin [declare 变量名 类型 [default 值];]
存储过程语句块;
end;-- 定义第一个存储过程
DELIMITER $$
CREATE PROCEDURE test2 ()
BEGIN-- 声明变量DECLARE var_num INT DEFAULT 0;SET var_num = 10; -- 变量赋值SELECTvar_num;-- 测试输出语句
END$$
DELIMITER;CALL test4(2)

定义一个有参数的存储过程

-- 定义一个有参数的存储过程
DELIMITER $$
CREATE PROCEDURE test3 (IN var_account INT, OUT var_count INT)
BEGIN-- 把sql中查询的结果赋给变量SELECT COUNT(*) INTO var_count FROM USER WHERE account = var_account;SELECT var_count;
END$$
DELIMITER;CALL test4(2)

流控制语句

-- 流控制语句
DELIMITER $$
CREATE PROCEDURE test4 (IN p_day INT)
BEGINIF p_day = 0THENSELECT"星期天";ELSEIF p_day = 1THENSELECT"星期一";ELSEIF p_day = 2THENSELECT"星期二";ELSESELECT"无效日期";END IF;
END $$CALL test4(2)

函数

函数语法

mysql8 需要设置

SET GLOBAL log_bin_trust_function_creators=TRUE;

-- 创建函数
create function 函数名([参数列表]) returns 数据类型
begindeclare 变量;sql语句;return 值;
end;-- 创建一个函数查询一个表的数量
DELIMITER $$CREATEFUNCTION `schooldb`.`news_count`()RETURNS INTBEGINDECLARE v_num INT DEFAULT 0;SELECT COUNT(0) INTO v_num FROM admire;RETURN v_num;END$$DELIMITER ;SELECT news_count()

以前写mysql连接两个表比较麻烦，经常join on，使用函数可以方便解决

DELIMITER $$CREATE FUNCTION findStudentGradeByid(p_id INT) RETURNS VARCHAR(10)BEGINDECLARE v_name VARCHAR(10) DEFAULT '';SELECT NAME INTO v_name FROM grade WHERE id = p_id;RETURN v_name;END$$DELIMITER ;-- 创建查询判断语句
DELIMITER $$CREATEFUNCTION findStudent(p_id INT) RETURNS VARCHAR(10)BEGINIF p_id = 1 THENRETURN '一年级';ELSE RETURN '二年级';END IF;END$$DELIMITER ;SELECT findStudent(n.gradeid) FROM student n

触发器

​ 触发器是一个特殊的存储过程，不需要用户直接调用，会在对表进行增删改的之前或之后自动触发。

​ 作为事务的一部分，触发器与激活触发器的语句一起作为对一个单一的事务来对来对待，可以从触发器中的任何位置回滚。

create trigger 触发器名称 触发时机(before/after) 触发事件(insert/before/after)
on 表名DELIMITER $$CREATETRIGGER `schooldb`.`delete_student` BEFORE DELETEON studentFOR EACH ROW BEGIN-- old 表示操作表中，原来的数据，old表示你操作的那一行的数据DELETE FROM student_course WHERE studentid = old.id;END$$DELIMITER ;

Mysql架构

连接层：负责接收客户端的连接请求，可以进行认证（验证账号密码）

服务层：接收sql，语言解析，优化，缓存

引擎层：引擎层是真正落实实现的具体方式，不同的存储引擎特点不同

物理文件存储层：使用各种文件用来存储数据，以及各种日志文件

MySQL引擎

存储引擎

​ 存储引擎是具体的操作数据的方式。

​ Innodb：支持事务，支持行级锁（一个事务对某行数据操作时，只会锁定某一行数据，不锁定其他行，效率高），支持外键约束，支持缓存，支持全文索引。不会存储表中的总行数。

​ MyISAM：不支持事务，不支持主外键，不支持行级锁，支持表锁（进行dml操作时会锁定整张表），主要用户查询多，增删改较少的场景。支持全文索引，存储表的总行数。

索引

为什么要用索引？

​ 当数据库查询数据量大，查询效率会降低

​ 索引是类似于书的目录，可以通过目录（索引）快速的定位数据真实位置

​ 是排好序的，快速查找的数据结构（B+树）

​ 在数据库中单独维护一个树，树中的每一个节点存储逐渐和数据的物理地址，可以通过属性结构快速锁定到数据的位置。

优势：

​ 快速定位数据位置，减少IO次数

​ 降低了排序成本

劣势：

​ 维护索引需要占用存储空间

​ 增删改操作时，还需要额外的维护索引树，消耗空间

索引分类

主键索引：alter table 表名 add primary key 表名（列明）；

删除主键：alter table 表名 drop primary key；

-- 单值索引
create index news_title_index on news(title)

删除索引：drop index 索引名 on 表名；

唯一索引：保证列数据不重复，可以为null

​ create unique index 索引名 on 表名（列明）；

组合索引：一个索引对应多个列

​ 要满足最左前缀原则

全文索引：模糊查询like，会导致索引失效。

创建索引原则（面试基本都问）

哪些情况创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引（where后面的语句）
• 查询中与其他表关联的字段，外键关系建立索引
• 查询中排序的字段，外键关系建立索引

哪些情况不建议创建索引

• 表记录太少
• 经常增删改的表，提高了查询速度，同时降低了更新表的速度
• where条件里用不到的字段
• 数据重复且分布均匀的表字段，

索引结构

​ 索引是一个树形结构：B+树；

​ 首先 二叉树，红黑树（自平衡）被排除了，树的高度较高，会影响查询效率

B 树 自平衡多路树，在一个节点中放多个数据，横向扩展，d降低树的高度

B+树，自平衡多路搜索树，排好序，非叶子节点只存储索引，数据都存储在叶子节点，会存在一些数据冗余，叶子节点之间会有一个相互指向的指针（对于自增主键，范围查找非常合适）

聚簇索引和非聚簇索引

聚簇索引：找到了索引，就找到了数据，例如主键索引，innodb引擎下，数据都在叶子节点存储，通过主键查找，找到了主键，也就找到了数据（属于聚簇式）

非聚簇索引：索引和数据是分离的，找到了索引，还没有找到数据，需要根据主键，再次回表查询，才能够查询到数据，通过学号（不是主键），查询学号和姓名，先通过学号找到主键，再通过主键去查找数据，这种场景是非聚簇的。

​ 还有myisam引擎中，除了只查询主键列以外，查询其他列的都是非聚簇索引。

判断是聚簇索引还是非聚簇索引：

聚簇索引：找到了索引，就找到了数据

​ Innodb下：使用主键作为查询条件，就是聚簇索引

​ 使用非主键（title）的列添加索引查询，就是非聚簇的，因为需要通过索引找到主键，然后通过主键二次回表查询，再通过主键找到数据。

​ myIsam：索引文件与数据文件是分开存放的，所以是非聚簇的。

事务

概述

​ 事务当时在mybatis使用过，当时需要提交事务。

​ 事务是数据库为了保证数据操作的原子性，隔离性，持久性，一致性，数据库提供了一套机制，在同一个事务中，如果有多条sql语句执行，事务要确保执行的可靠性，也就是要保证多条sql语句都要执行，才去提交。

原子性：一个事务（transaction）中的所有操作，要不全部完成，要不就全部不完成，不会再中间结束。如果在中间发生错误了，就会回滚(Rollback)到事务开始前的状态。

持久性：事务一旦提交，数据就持久保存在硬盘中

隔离性：数据库允许多个事务同时对数据进行读写和修改，隔离性就是方式在并发读写过程中，一个事务还没有提交，另一个事务将没有提交事物的sql数据读取到了就是脏读（读到的数据是垃圾数据），有读未提交，读已提交，可重复读，串行化

一致性：在数据库的的操作中，数据经过多次操作最终的结果要与预期的结果一致，保证数据的完整性，不能出现转账还没转过去，对方就已经收到了，原子性，隔离性，持久性都是为了保证数据的一致性

隔离性中不同的隔离级别

开启两个窗口，两个窗口进行操作。

AB窗口，刚开始查询是读未提交

在A中开始一条事务，在B中开启事务，

A中执行插入，B中查询可以查到，但是A中还未提交事务，当A中回滚，B中就查询不到了

四种隔离级别：有读未提交，读已提交，可重复读，串行化

查看隔离级别

SELECT @@global.transaction_isolation,@@transaction_isolation;

设置隔离级别

-- 设置左边的
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置右边的
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

读未提交(read uncommitted)： 一个事务可以读取到另一个事务未提交的数据。这会带来脏读(垃圾数据，因为A事务可能会回滚)，幻读，不可重复读问题 ，将隔离级别改为读已提交，可以解决脏读问题。

读已提交(read committed)： 一个事务只能读取另一个事务已经提交的修改。

其避免了脏读，仍然存在不可以重复读和幻读问题。

不可重复读：
A窗口开启事务，B窗口开启事务，B先查询，A改年龄然后提交事务，B查到A改到的数据，在同一个事务里面执行相同的查询操作，读取出来的数据不同，这就是不可重复读

可重复读(repeatable read MySQL 默认隔离级别)： 同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但是除InnoDB 外幻读依然存在。一般不会产生幻读，加了for update之后就会出现幻读。

幻读：可重复读隔离级别：左边查询数据，右边插入一条数据，然后右边提交，左边继续查询，没有查出来这条插入的数据，这个就是没有出现幻读。使用select * from demo for update就会出现幻读，就是可以查询到别的事务已经插入的数据。

串行化(serializable)： 事务串行执行,避免了以上所有问题。当查询的时候，别的事务不能进行操作。即使是读操作，也会等待，是最安全的，速度是最慢的。

-- 设置串行化
SET SESSION TRANSACTION ISOLATION LEVEL serializable;

事务实现原理

​ InnoDB存储引擎提供了两种事务日志，redolog(重做日志)和undolog(回滚日志)，redolog用于保证事务持久性，undolog则是事务原子性和隔离性实现的。

原子性的实现

​ 当事务回滚时能撤销所有已经成功执行的sql语句。原子性依赖的是undolog，undolog属于逻辑日志，里面记录的都是执行过所有的操作的相反的操作，比如执行insert操作时，里面记录的就是delete操作，用于回滚后将已经成功执行的sql语句进行撤销。

持久性的实现

​ redolog叫做重做日志，保证事务持久性的重要机制。用于记录每次执行修改数据操作时，先会将语句保存到redolog中，即使突然断电，正常后也可以从日志中恢复数据。

隔离级别的实现原理(MVCC)

MVCC概述

​ 多版本并发控制 Multi-Version Concurrent Control，是MySQL提高性能的一种方式，就是配合undolog使事务可以并发执行。

​ 主要针对读已提交和可重复读，这两主要通过版本链进行实现

读已提交：只要别的事务提交了，另一个事务就可以看到特点：有可能同一个事务两次查询数据不一致，访问到的是最新的数据。读已提交又称当前读，每次读的时候都会给版本链拍照，所以读到的数据都是最新的（已提交）。​可重复读：B事务开始之后，第一次和之后读到的数据一致，这个过程别的事务已经修改过了。可重复读也叫快照读，第一次读的时候，会给版本拍照，下次读的时候，从版本快照中直接进行读取，所以第一次和第二次读到的数据是一致的。

​ 版本链控制提交操作，执行一次操作，就会在日志中记录相应操作，版本链就会记录一次操作，每个版本中还会包含生成改版本对应的事务id(重要)。

​ 锁主要是在串行化里面实现

锁机制

MySQL支持行锁，间隙锁，表锁

概述

​ 隔离性的要求，InnoDB通过锁机制来保证这一点。

行锁，间隙锁，表锁

​ 行锁就是对当前行进行加锁，行锁又称共享锁和排他锁

行锁：只给操作行加锁，如果两个事务操作的是同一行，那么就一个一个执行，不同行的话就一起执行
​间隙锁：一般指的是范围区间，对某一个区间进行加锁，例如ID>5 and id<10
​表锁：对整个表加锁，myisam默认支持表锁​共享锁：又称读锁，如果我们事务在读数据时，不想让其他事务写，还要让其他事务可以读，那么查询语句可以加共享锁，在语句后直接加lock in share mode
​排他锁：就是独占锁，写操作默认加排他锁，当我们读数据时，要求数据足够准确，可以给读操作加排他锁，在语句后面直接加for update

SQL优化

为什么要对SQL进行优化？

​ 当业务数据量较小时，SQL的执行效率对程序运行效率影响不太明显，但是当业务数据量大的时候，SQL的执行效率对程序的运行效率的影响逐渐增大，此时SQL优化就很有必要。

SQL优化的一些方法

1. 查询SQL尽量不要使用select * ，而是使用具体字段

​ 节省资源、减少网络开销。

​ 可能用到覆盖索引，减少回表，提高查询效率

2. 避免在where子句中使用or来连接条件

​ 使用or可能会使索引失效，从而全表扫描；

​ 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描

3. 尽量使用数值替代字符串类型

​ 例：主键优先使用int类型，性别、状态，使用1，0替代

​ 因为引擎在处理查询和连接的时候会逐个比较字符串中的每一个字符；

​ 对于数字型而言只需要比较一次就够了；

​ 字符会降低查询和连接的性能，并会增加存储开销；

4. 使用varchar代替char

​ varchar变长字段按数据内容实际长度存储，可以节省存储空间；

​ char按声明大小存储，不足补空格；

​ 其次对于查询来说，在一个相对较小的字段内抖索，效率更高；

5. 对查询进行优化，应尽量避免全表扫描，首先应考虑建立索引（针对查询多，增删少）

6. 应尽量避免索引失效

• 在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，例如：select id from t where num is null，可以在num上设置默认值0，确保表中num列没有null值
• 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20
• in 和 not in也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)，对于连续的数值，能用between就不要用in, select id from t where num between 1 and 3
• 模糊查询也将导致全表查询
• 应尽量避免在where子句中对字段进行函数操作，这将

7. inner join 、left join、right join，优先使用 inner join

​ 三种连接如果结果相同，优先使用 inner join

​ inner join 内连接，只保留两张表中完全匹配的结果集；

​ left join 会返回左表所有的行，即使在右表中没有匹配的记录；

​ right join 会返回右表所有的行，即使在左表中没有匹配的记录；

8. 提高 group by 语句的效率

​ 反例:先分组，再过滤

​ 正例:先过滤，后分组

9. 清空表时优先使用 truncate

​ truncate table 比 delete 速度快，且使用的系统和事务日志资源少.

​ delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate

​ table 通过释放存储表数据所用的数据页来删除数据.

10. 表连接不宜太多，索引不宜太多，一般 5 个以内

​ 联的表个数越多，编译的时间和开销也就越大

​ 每次关联内存中都生成一个临时表

​ 应该把连接表拆开成较小的几个执行，可读性更高

11. 避免在索引列上使用内置函数

​ 使用索引列上内置函数，索引失效。

12. 使用 explain 分析你 SQL 执行计划

delete from user dml 逐行删除   
truncate user ddl表结构进行操作

执行计划

​ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 作用

​ 表的读取顺序

​ 数据读取操作的操作类型

​ 哪些索引可以使用

​ 哪些索引被实际使用

​ 表之间的引用

​ 每张表有多少行被优化器查询

EXPLAIN 使用

​ 在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息，

​ 而不是执行 SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

id

SELECT 识别符。这是 SELECT 的查询序列号

id 如果相同，可以认为是一组，从上往下顺序执行；

在所有组中，

id 值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM employee e,dept d WHERE e.deptId = d.id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2WqiBaxN-1687490508331)(C:\Users\qq\AppData\Roaming\Typora\typora-user-images\1678154319138.png)]

EXPLAIN SELECT * FROM employee e WHERE e.deptId = (SELECT id FROM dept d

WHERE d.id = 1)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5mNR7h8r-1687490508331)(C:\Users\qq\AppData\Roaming\Typora\typora-user-images\1678154327897.png)]

select_type

表示查询中每个 select 子句的类型

1.SIMPLE(简单 SELECT，不使用 UNION 或子查询等)

2.PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的

select 被标记为 PRIMARY)

3.SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)

4.DERIVED(派生表的 SELECT, FROM 子句的子查询)

5.UNION(UNION 中的第二个或后面的 SELECT 语句)6.UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有

select)

type

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，

性能从好到差）

.

system: 表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不

计.

const: 表示通过索引一次就找到了，

const 用于比较 primary key 或者 unique

索引。

eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见

于主键或唯一索引扫描.

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访

问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，

所以他应该属于查找和扫描的混合体.

range: 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个

索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种

范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束

语另一点，不用扫描全部索引。

index: Full Index Scan，

index 与 ALL 区别为 index 类型只遍历索引树。这通

常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是

读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）

All: Full Table Scan，将遍历全表以找到匹配的行

一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

possible_keys

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用key

实际使用的索引。如果为 NULL，则没有使用索引,或者索引失效.

ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失

精确性的情况下，长度越短越好.

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于

查找索引列上的值

EXPLAIN SELECT * FROM employee e,dept d,admin a WHERE e.deptId = d.id AND e.adminId=a.id

AND e.age=20

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行

数.

Extra

额外的信息说明

Using filesort: 当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操

作的时候,Mysql 无法利用索引完成排序的操作称为”文件排序”.

Using temporary: 使了用临时表保存中间结果，MySQL 在对查询结果排序时

使用临时表。常见于排序 order by 和分组查询 group by。

Using index

表示相应的 select 操作中使用了索引，避免访问了表的数据行，效率不错！

如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作。

Using where

表示使用到了索引 , 但是也进行了 where 过滤