mysql核心知识

程序J

于 2023-03-13 15:46:07 发布

阅读量367

点赞数 1

分类专栏： java进阶文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/wowboom/article/details/129495790

版权

java进阶专栏收录该内容

7 篇文章 0 订阅

订阅专栏

视图

视图就是将一条Sql语句封装起来，之后我们使用sql时，使用视图查询即可。

视图并不保存数据，数据还是在表中。

定义视图

CREATE VIEW  视图名 AS SELECT 列1 FROM 表
CREATE VIEW view_admin AS  SELECT * FROM  admin;

使用视图

SELECT * FROM 视图名
SELECT * FROM  view_admin

删除视图

drop view 视图名
DROP VIEW view_admin

存储过程

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

存储过程类似于 JAVA 语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为 IN、OUT、INOUT 类型三种类型。IN 类型的参数表示接收调用者传入的数据，OUT 类型的参数表示向调用者返回数据，INOUT类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据

语法：

create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]）
begin
[declare 变量名 类型 [DEFAULT 值];]
存储过程语句块;
end;

创建存储过程

简单存储过程

CREATE
    PROCEDURE `ssmdb`.`s`()
	BEGIN
	DECLARE  NAME INT  DEFAULT 0;
	SELECT NAME;

	END$$

有参数的存储过程

CREATE
    PROCEDURE `ssmdb`.`s1`(IN p_no INT ,OUT p_sn INT )  
	BEGIN
           SELECT COUNT(*) INTO p_sn FROM admin   WHERE TYPE=p_no;
           SELECT p_sn;
	END$$

流程控制类型

CREATE
   
    PROCEDURE `ssmdb`.`s2`(IN p_day INT)
	BEGIN
	IF p_day=0 THEN
	SELECT "星期天";
	ELSEIF p_day=1 THEN
	SELECT "星期一";
	ELSE
	SELECT "无效日期";
	END IF;

	END$$

case when

CREATE
    PROCEDURE `ssmdb`.`s3`(IN p_day INT)
    
	BEGIN
          CASE WHEN p_day=0 THEN
            SELECT "星期天";
            ELSE
            SELECT "星期一";
            END CASE;
	END$$

循环

CREATE
 
    PROCEDURE `ssmdb`.`s4`()
    
	BEGIN
	DECLARE v_num INT DEFAULT 0;
	andnum:LOOP
	 SET v_num=v_num+1;
	 IF v_num=10 THEN
	  LEAVE andnum;
	  END IF;
	  END LOOP;
	  SELECT v_num;

	END$$

使用存储过程插入信息

CREATE
    PROCEDURE `ssmdb`.`s5`(IN p_account VARCHAR(20),IN p_gender VARCHAR(2),OUT p_res INT)
   
	BEGIN
	 DECLARE v_count INT  DEFAULT 0;
	 SELECT COUNT(*) INTO v_count FROM admin WHERE account=p_account;
	 IF v_count=1 THEN
	 INSERT INTO admin(account,gender) VALUES(p_account,p_gender);
	 ELSE
	 SET p_res=1;
	END IF; 
	END$$

函数

语法：

create function 函数名([参数列表]) returns 数据类型
begin
DECLARE 变量；
sql 语句;
return 值;
end;

设置函数可以没有参数 :SET GLOBAL log_bin_trust_function_creators=TRUE;

创建函数

无参函数

CREATE
    FUNCTION `ssmdb`.`s1`()
    RETURNS INT     
    BEGIN
     DECLARE v_num INT;
    SELECT COUNT(*)INTO v_num FROM admin;
     RETURN v_num;
    END$$

有参函数

CREATE
    FUNCTION `ssmdb`.`t1`(p_id INT)
    RETURNS VARCHAR(10)
    
    BEGIN
    DECLARE v_name VARCHAR(10);
     SELECT account INTO v_name FROM admin WHERE id=p_id;
       RETURN v_name;
    END$$

触发器

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

语法：

CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW -- 行级触发
BEGIN
语句
END;

创建触发器

CREATE
    TRIGGER `ssmdb`.`w1` BEFORE DELETE
    ON admin
    FOR EACH ROW BEGIN
     DELETE FROM admin_role WHERE adminid=old.id;
         END$$

mysql架构

在这里插入图片描述

连接层: 负责接收客户端的连接请求,可以进行认证(验证账号密码)

服务层: 接收sql,语言解析,优化,缓存

引擎层: 引擎层是真正落地实现的具体方式,不同的存储引擎特点不同.存储引擎真正的负责了 MysQL 中数据的存储和提取。

物理文件存储层: 使用各种文件用来存储数据,以及各种日志文件，并完成与存储引擎的交互

MySql引擎

mySql引擎是用来存储、处理、保护数据的核心服务

主要的存储引擎有： InnoDB、MyISAM.

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

InnoDB

InnoDB是mySQL中默认使用的存储引擎，它支持事物、支持全文索引、支持外键、支持数据缓存，存储最大为64TB,不存储表的总行数，支持行级锁

MYSIAM

MYSIAM不支持事物，不支持外键、不支持数据缓存、它支持全文索引和数索引，储存最大为256TB,支持表锁，

比较安全，用于查询多，增删少的情况，会存储表的总行数

索引

索引是一种数据结构用于快速查找数据，就像书中的目录一样。

索引的结构是B+树，这种结构可以更快的查找到想要的数据。

为什么不使用红黑树来作为索引呢？

红黑树是一种自平衡的二叉树，它虽然解决了二叉树在一些情况下层数很高的情况，但是由于它一个节点中只能存储一个数据，最后树的高度还是很高，影响查询的效率，但b+树就解决了这个问题，b+树通过纵向扩容，在一个节点中存储多个数据，来降低树高，大大提高了查询效率。

索引的优势：快速定位数据位置，减少IO次数

劣势：由于索引也是一张表，存储了索引和主键字段，在我们对表进行更新时，索引也会随之更新，消耗时间

索引分类：索引分为主键索引、单值索引、唯一索引、组合索引、全文索引

主键索引：

在表中设置主键时会自动创建主键索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名)

删除主键索引

ALTER TABLE 表名 drop  PRIMARY KEY

单值索引：

即一个索引中只包含一个列，一个表可以有多个单值索引

创建单值索引

CREATE INDEX 索引名 ON 表名（列名）;

删除索引

DROP INDEX 索引名；

唯一索引：索引列中的值必须唯一，可以为null

CREATE UNIQUE INDEX 索引名 on 表名（列名）

删除索引

DROP INDEX 索引名  ON 表名

组合索引：
即一个索引包含多个列

创建复合索引

CREATE INDEX 索引名 ON 表名(列 1,列 2...);

删除索引：

DROP INDEX 索引名 ON 表名;

删除索引：
DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则

当创建了组合索引时，组合索引在使用时，需要满足最左前缀原则，即使用组合索引的列作为条件时，最左侧的列必须要出现，负责索引不生效

例如一个表有 a b c三列,为 a b两列创建组合索引，那么使用时，a必须出现

select * from table where a=’’and b=’’索引生效

select * from table where a=’’and c=’’索引生效

select * from table where c=’’and b=’’索引失效

全文索引

在模糊查询时，索引会失效，就需要使用全文索引

创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词’）

索引的创建原则

哪些情况需要创建索引：主键自动创建索引、频繁作为查询条件的字段、外键关系作为索引、分组的字段创建索引、查询排序的字段可以创建索引

哪些情况不要创建索引：表中的数据太少，表频繁的进行更新，数据重复且分布平均的字段

聚簇索引和非聚簇索引

聚簇索引

找到索引找到了数据，这就是聚簇索引，主键索引是聚簇索引

例如：在学生表中，我们根据id查询学生信息，由于主键索引中节点包含学生信息，不需要进行回表查询，所以该操作是聚簇索引

非聚簇索引

找到索引，但没找到数据，需要根据索引上的值回到表中进行查询。

例如：在学生表中，学生的编号是唯一索引，根据学生的编号和查询编号和姓名，该操作中查讯的列有编号和姓名，但是学生的编号索引中节点存储的数据是主键ID，这是还需要根据主键iD作为条件会学生表中查询姓名，这种查询就是非聚簇索引

事务

事务是在数据库中执行多条sql语句对数据进行操作时，为了保证数据库的完成性，要么多条语句同时执行，要么全部都不执行。

事务的特性

事务的四个特性（ACID）:原子性、隔离性、持久性、一致性。

原子性：一个事务中的全部操作，要么全部执行成功，要么都不执行。事务在执行过程中发生错误时，就会回滚到事务执行开始之前的状态。

持久性：事务执行完成后，对数据的修改是永久的。

隔离性：为了防止多个事务同时执行由于交叉执行导致数据不一致的情况。事务隔离分为不同级别，包括读未提交、读提交、可重复读、串行化。

事务设置

默认情况下，mysql开启自动提交模式。

可以通过设置：SET GLOBAL autocommit=0; 禁止自动提交

事务隔离级别

为什么要设置事务隔离级别：

在数据库并发执行事务过程中，为了保证事务的隔离性，理论上多个事务应该进行排序依次执行，只有一个事务执行完毕，下一个事务才能执行，但是这样会使得性能大大降低，为了解决这个问题来设置事务隔离级别。

查看隔离级别：

SELECT @@global.transaction_isolation,@@transaction_isolation；

全局设置隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别（ READ UNCOMMITED）

会话设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别（ READ UNCOMMITED）

设置事务隔离级别：

**读未提交(read uncommitted)：**一个事务可以读取到另一个事务未提交的修改。这会带来脏读，幻读，不可重复读问题。

**读已提交(read committed)：**一个事务只能读取另一个事务已经提交的修改。其避免了脏读，仍然存在不可以重复读和幻读问题。

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

幻读问题是指，在B事务执行的过程中，A事物中增加一了行数据，B事务在查询全表数据时查到了新插入的数据

在InnoDB中幻读问题一般不存在

只有在 select…for update 时才会出线

select…for update也是一个查询语句，后加for update时，在事务中会将此操作视为insert、update、delete操作。

**可串行化：**可串行化解决了幻读的问题，它相当于加锁，当有事务进行操作时，其他任何事务都不能执行，只有当该事务执行完成后，其他事务才能执行，是最高级别的隔离操作，但是它的效率很低。

事务的实现原理

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

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

原子性实现

事务对数据库进行修改时，undolog会存储与事务相反的操作，例如事务执行插入操作，undolog会存储相应的删除操作。当事务失败或回滚时，就会利用undolog中的信息将数据还原到修改之前的样子

持久性实现

当执行修改数据的操作时，会将语句先保存到redolog 中。即使机器崩溃或宕机，数据也不会丢失

隔离级别实现原理（MVCC）

MVCC(多版本并发控制)，是MySQL提高性能的一种方式，使得不同事务的读写操作可以并发执行。

MVCC实现读已提交和可重复读隔离级别

MVCC实现原理：

MVCC通过表中每行记录中的两个隐藏列实现的，这两个列一个记录事务ID(trx_id),一个保存了行的回滚指针(roll_pt).

trx_id:每次修改该行数据时，就把对应事务的id存入trx_id；

roll_pt:每次修改数据时，都会把修改之前的数据版本写入undolog日志中，再将把版本编号存入roll_pt中。

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

随着数据更新次数的增多，所有的版本会被roll_pt连接成一个链表，这个链表就称为版本链。

ReadView

ReadView 快照读，调用readView时，会将数据进行快照。

ReadView 是一个数据结构，包含：

m_ids:当前活跃的事务编号集合

min_trx_id:最小活跃事务编号

max_trx_id:预分配事务编号

creator_trx_id:ReadView创建者的事务编号

读已提交：称为当前读，当每个事务每次读取时，会生成一个readView，读取的是最新数据

可重复读级别：称为快照读，当事务进行第一次查询时，会生成一个readView，第二次查询时还会从当前readView中读数据

锁机制

事务在进行修改时，需要获取锁，才能修改数据，事务操作期间，数据是锁定的。其他事务需要修改数据，需要等待当前事务提交完成。
按照粒度，锁可以分为表锁、行锁、间隙锁。其中MyIsam只支持表锁，InnoDB支持表锁和行锁。

行锁

只针对当前操作的行进行加锁。行锁加锁粒度最小，加锁的开销最大.

特点：开销大，加锁慢；会出现死锁；发生锁冲突的概率低，并发度最高

间隙锁

锁的是一个区间。在我们用范围条件检索数据时，InnoDB会给符合条件的已有数据记录的索引项加锁。

表锁

对整张表进行加锁，锁粒度最大。表锁分为表共享锁和表排他锁

特点;开销小，加锁开，不会出现死锁，锁冲突概率最高，并发度低

共享锁(s)

又称读锁。当对数据对象加上读锁时，事务只能读取数据，不能修改数据

排他锁（X）

又称为写锁，当事务数据加X锁，该事务可以对数据进行查看和修改。其他事务在加锁事务执行结束之前，无法对数据进行任何操作。update，delete,insert都会给涉及的数据自动加X锁。

select语句默认无锁类型，可以给select语句加排他锁 select…for upadte,加共享锁 select…lock in share mode语句。

sql优化

1.减少使用select* 查询,原因select*可能会用到覆盖索引，造成回表查询

2.避免在where字句中使用or来连接条件，or可能会导致索引失效，进行全表扫描。

3.尽量使用数值来代替字符串类型，例如：性别，类型，数字只需要比较一次，字符串每个字符串都需要比较

4.使用varchar代替char,varchar按数据内容实际长度存储，节省空间

5.可以建立索引

6.避免索引失效

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

8.group by语句，先分组再过滤

9.清空表时优先使用 truncate，delete一行一行删除，truncate删除表存储数据的数据页。

10.表连接不要太多，索引也不要太多

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

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

执行计划

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

Explain

使用 Explain关键字可以查询执行计划的信息

EXPLAIN 作用

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

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

EXPLAIN 使用

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

而不是执行 SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

expain 出来的信息有 12 列，分别是:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

概要描述

id:选择标识符，

select_type:表示查询的类型。

table:输出结果集的表

partitions:匹配的分区

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比

Extra:执行情况的描述和说明

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

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

在所有组中，

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

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 无法利用索引完成排序的操作称为”文件排序”.

程序J

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
1
评论
mysql核心知识

事务，mysql执行引擎，锁机制
复制链接

扫一扫

专栏目录