数据库事务索引视图、存储过程

目录

文章目录

一、数据库事务

事务概述：

事务特征：

提交回滚

隔离级别

二、索引

索引创建原则：

存储引擎

单列索引

组合索引

全文索引

空间索引 

三、视图

四、函数和存储过程

1.函数的创建

2.存储过程

3.调用格式

4.两者区别

五、数据库优化

一、数据库事务

事务概述：

事务（Transaction），是由一系列对数据库表中数据，进行访问与更新的 操作，所组成的一个执行逻辑单元。

只有DML语句才会产生事务，其他语句不会产生事务

DML语句执行的时候，如果当前有事务，那么就使用这个事务。如果当前 没有事务，则产生一个新事务 commit、rollback、DDL语句都可以把当前事务给结束掉

commit和DDL语句结束事务的方式是把这个事务给提交了，然后DML操作 永久生效 rollback结束事务的方式是把这个事务给回滚了，默认回滚到事务开始的状 态

mysql默认是开启事务，即 autocommit = 1，自动提交事务。即执行 insert、update、delete操作，立刻提交。

事务特征：

ACID特性

Atomic（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）的英文缩写。

原子性（Atomicity） 一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不 会结束在中间某个环节。事务在执行过程中发生错误，会被回滚 （Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。 如：张三转账给李四钱，转账过程中停电了，应该转账不生效（回退），事务的原子性

一致性（Consistency）  事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一 致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系 统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动 地回滚，系统返回到原始状态。 如：张三转账给李四钱，转帐之前和转账之后，账务总额应该保持不变。

隔离性（Isolation） 指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都 有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务 所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务 修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到 中间状态的数据。 如：张三转账过程，李四看不到张三账户的变化

持久性（Durability） 指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。 即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功 结束时的状态。 如：如果转到错误的人的账户上，钱是退不回来的，事务一旦完成，事务 中所涉及的数据，不能再被此次事务所更改。

提交回滚

commit rollback

要显式的开启一个事务须使用命令 BEGIN 或 START TRANSACTION ，或 者执行命令 SET AUTOCOMMIT=0 ，用来禁止使用当前会话的自动提交。

rollback to 回滚点，此时事务并没结束。这个时候还可以接着 rollback回滚或者commit提交，使事务结束。

可以通过savepoint打标记，指定回滚的位置

隔离级别

MySQL支持多用户并发访问，并且保证多个用户同时访问相同的数据时不会造 成数据的不一致性。

如果不进行任何隔离控制会出现：脏读，不可重复读、幻读

 幻读

修改数据库中的隔离级别

set session transaction isolation level READ COMMITTED ;

二、索引

在 MySQL 中，索引（Index）是一种数据结构，用于提高数据库表的查询性 能。索引可以加速数据的检索，减少查询所需的时间和资源消耗。

索引创建原则：

对where 和 order by 涉及的列上尽量建立索引

更新频繁的列不应设置索引

数据量小的表不要使用索引

重复数据多的字段不应设为索引（如性别，只有男和女），重复数据超 15%就不该建索引

存储引擎

 

单列索引

普通索引：

CREATE TABLE [ IF NOT EXISTS ] tb_name [(

字段 1 数据类型 [ 约束条件 ] [ 默认值 ] [ COMMENT ' 注解 ' ] ,

字段 2 数据类型 [ 约束条件 ] [ 默认值 ] [ COMMENT ' 注解 ' ] ,

字段 3 数据类型 [ 约束条件 ] [ 默认值 ] [ COMMENT ' 注解 ' ] ,

......

[ 表约束条件 ]

index index_name ( col_name... )

...

)][ engine = innodb ] [ default charset = utf8 ] ;

create index index_name on tb_name ( col_name ) ;

alter table tb_name add index index_name ( col_name ) ;

唯一索引：

unique index_name ( name )

create unique index index_name on tea3 ( name ) ;

alter table tea3 add unique index_name ( name ) ;

组合索引

组合索引又叫做复合索引，就是在普通索引的（）中添加多个字段

复合索引有复合最左原则

复合最左原则（Composite Key Leftmost Rule）是在关系型数据库中用于优 化查询性能的一种规则。它指出在使用复合索引时，查询条件中的列应该 按照索引中列的顺序从左到右进行匹配。遵循复合最左原则可以帮助优化 查询性能，提高数据库操作的效率。 具体来说，复合最左原则要求在查询中使用索引时，应该按照索引定义中 列的顺序从左到右依次指定查询条件。如果查询条件中的列不是按照索引 定义的顺序进行匹配，数据库引擎可能无法有效地使用索引，从而导致查 询性能下降。

全文索引

fulltext全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配，

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

数据量较大时，先将数据放入一个没有全局索引的表中，然后再用create index创建fulltext索引，要比先建立一张表（包含fulltext索引），然后再将 数据写入的速度快很多

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度 小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是 说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变 量的区间内。

match ( col_name1,col_name2,... ) against ( expr [ search_modifier ])

 

空间索引 

MySQL 在 5.7 之后的版本支持了 空间索引（了解即可） ，而且支持 OpenGIS 几何数据模型

空间索引是对空间数据类型的字段建立的索引

MYSQL 使用 SPATIAL 关键字进行扩展，使得能够用于创建正规索引类型的语 法创建空间索引

创建空间索引的列，必须将其声明为 NOT NULL

三、视图

在 MySQL 中，视图（View）是一种虚拟的表，它是基于一个或多个表的查询 结果构建而成的。视图提供了一种方便和灵活的方式来处理复杂查询、控制数 据访问和重用查询逻辑。 通过使用视图，可以提高查询的效率和可维护性，并增强数据库的安全性。

CREATE [ OR REPLACE ] VIEW view_name [( 字段列表 )]

AS

select 语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除视图

drop view if exists view_name;

修改视图

alter view view_name

as

select 查询语句

如果是复杂视图，包含下述结构中的任何一种，那么就不能通过它去更新原来 表中数据： 聚合函数（SUM(), MIN(), MAX(), COUNT()等） ，select查询列表有数学表达式 ，DISTINCT ，UNION或UNION ALL ，位于选择列表中的子查询 ，GROUP BY ，HAVING ，JOIN  ，常量视图。

四、函数和存储过程

1.函数的创建

mysql8 增加了一个安全选项，需要执行一下代码才能创建函数

set global log_bin_trust_function_creators=TRUE;

 delimiter 自定义符号

create function 函数名 ( 形参列表 ) returns 返回类型 -- 注意是 retruns

begin

函数体 -- 若干 sql 语句，但是不要直接写查询

return val;

end 自定义符号

-- 格式说明：

delimiter 自定义符号 是为了在函数内写语句方便，制定除了 ; 之外的符号作为函

数书写结束标志，一般用 $$ 或者 //

形参列表：形参名 类型 类型为 mysql 支持类型

返回类型 : 函数返回的数据类型， mysql 支持类型即可

函数体：若干 sql 语句组成

return : 返回指定类型返回值

定义用户变量

-- 定义格式

set @ [ 变量名 ] = 值 ;

-- 使用格式

@ [ 变量名 ]

定义局部变量 

DECLARE variable_name datatype [ DEFAULT initial_value ] ;

-- variable_name 是变量的名称

-- datatype 是变量的数据类型

-- initial_value 是可选的初始值

2.存储过程

delimiter 自定义符号

create procedure 存储过程名 ( 形参列表 )

begin

存储过程 -- sql 语句构成存储过程语句集

end 自定义符号

delimiter ;

-- 说明：

delimiter 自定义符号 是为了在存储过程内写语句方便，制定除了 ; 之外的符号作

为函数书写结束标志

形参列表： [ IN | OUT | INOUT ] 形参名 类型

in 输入

out 输出

inout 可以输入也可以输出

存储过程：若干 sql 语句组成，如果只有一条语句也可以不写 delimiter 和

begin, end

 

3.调用格式

调用存储函数 语法：

select 存储函数名字 ([ 函数参数 [ ,…… ]])

调用存储过程 语法：

call 存储过程名字 ([ 存储过程参数 [ ,…… ]])

使用 show create 语句查看存储过程和函数的定义

show create { procedure | function } 存储过程或存储函数的名称

4.两者区别

 

五、数据库优化

查询优化