存储引擎、事务、隔离级别、视图

最新推荐文章于 2024-03-21 10:20:51 发布

Gggghc

最新推荐文章于 2024-03-21 10:20:51 发布

阅读量580

点赞数

分类专栏： MySQL学习记录文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_51421734/article/details/121310298

版权

MySQL学习记录专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. 存储引擎

1.1 什么是存储引擎？

存储引擎是MySQL中特有的一个术语，（oracle中有，不叫这个名字）。

实际上存储引擎是一个表存储/组织数据的方式

不同的存储引擎，表存储数据的方式不同

1.2 怎么指定存储引擎？

show create table 表名;

可以再建表的时候指定存储引擎。

create table t_student(
	no int(11) not null auto_increment,
    nam varchar(255),
    primary key (no),
)engine=InnoDB charset=utf8;

建表的最后：

ENGINE 来指定存储引擎

CHARSET 指定字符编码格式

MySQL默认的存储引擎是 InnoDB，默认的字符编码格式是 utf8

1.3 MySQL支持哪些存储引擎

命令：show engines \G

MySQL支持九大存储引擎。

1.4 MySQL常用存储引擎

MyISAM 存储引擎

特征：

使用三个文件表示每个表：

格式文件 — 存储表结构的定义(mytable.frm)

数据文件 — 存储表行的内容(mytable.MYD)

索引文件 — 存储表上索引(mytable.MYI)

优点：可被转换为压缩、只读表来节省空间

InnoDB 存储引擎

这是mysql默认的存储引擎，也是重量级的存储引擎。

InnoDB支持事务，支持数据库崩溃后自动恢复机制

最主要的特点是：非常安全

特征：

每个InnoDB表在数据库目录中以 .frm 格式文件表示

提供一组用来记录事务性活动的日志文件

用COMMIT、SAVEPOINT、ROLLBACK支持事务管理

提供全ACID兼容

在MySQL崩溃后提供自动回复

多版本和行级锁定

支持外键及引用的完整性，包括级联删除和更新

MEMORY 存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中（一断电就消失），且行的长度固定，这两点使MEMORY非常快。

特征：

在数据库目录内，每个表以 .frm 格式的文件表示

表数据及索引被存储在内存中

表级锁机制

不能包含 TEXT 或 BLOB 字段

MEMORY 以前被称为 HEAP 引擎

2. 事务（重点）

2.1 概述

一个事务其实就是一个完整的业务逻辑。

事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）；

只有DML语句（insert、delete、update）和事务有关系，其他语句和事务无关。

2.2 事务是如何实现的？

InnoDB存储引擎，提供一组用来记录事务性活动的日志文件。

在事务的执行过程中，每一条DML的操作都会记录到文件中。

在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

提交事务标志着事务的结束，并且是一种全部成功的结束。

回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件

回滚事务标志着事务的结束，并且是一种全部失败的结束。

2.3 如何提交和回滚事务

事务（transaction）

提交事务：**commit；**语句

回滚事务：**rollback；**语句（回滚永远都是只能回滚到上一次的提交点！）

测试一下，在MySQL中默认的事务行为

关闭MySQL的事务自动提交机制：start transaction;，不输入这条语句会自动提交。

2.4 事务特性

ACID

A：原子性

说明事务是最小的工作单元，不可再分

C：一致性

所有事物要求，在同一食物当中，所有的操作必须同时成功或者同时失败，以保证数据的一致性

I：隔离性

A事务和B事务之间具有一定的隔离。

D：持久性

事务最终结束的一个保障。事务提交就相当于将没有保存到硬盘的数据保存到硬盘上。

2.5 事务的隔离级别

读未提交：read uncommitted（最低的隔离级别）

事务A可以读取到事务B未提交的数据

这种隔离级别存在的问题就是：脏读现象（Dirty Read）

这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步。

读已提交：read committed

事务A只能读取到事务B提交之后的数据

这种隔离级别解决了脏读的现象

这种隔离级别存在的问题：不可重复读取数据

不可重复读：在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读取到的数据是4条。

这种隔离级别是比较真实的数据，每次读到的数据是绝对的真实

Oracle数据库默认的隔离级别是：读已提交

可重复度：repeatable read

事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变这就是可重复读。

可重复度解决了不可重复读取数据的问题。

可重复度存在的问题：幻读，每一次读取到的数据都是幻象，不真实。

MySQL中默认的数据隔离级别：可重复度

序列化/串行化：serializable（最高的隔离级别）

最高隔离级别，效率最低，解决了所有问题。

表示事务排队，不能并发。

每次读取到的数据都是真实的，并且效率是最低的。

2.6 演示隔离级别

先建表：CREATE TABLE t_user(NAME VARCHAR(255));

打开两个MySQL控制台。

2.6.1 读未提交

select @@tx_isolation // 查看隔离级别
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> set global transaction isolation level read uncommitted; // 设置隔离级别

事务A												事务B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
select * from t_user;
													start transaction;
													insert into t_user values('zhangsan');			
select * from t_user; // 此时B未提交就能查到

+----------+
|name      |
+----------+
| zhangsan |
+----------+

2.6.2 读已提交

验证：read commited
mysql> set global transaction isolation level read committed;
事务A												事务B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
													insert into t_user values('zhangsan');
select * from t_user;
													commit;
select * from t_user;
Empty set ;  // 此时查不到

2.6.3 可重复度

mysql> set global transaction isolation level repeatable read;
事务A												事务B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
													insert into t_user values('lisi');
													insert into t_user values('wangwu');
													commit;
select * from t_user;
// 此时只能查询到 lisi,wangwu 之前的数据，查不到 lisi,wangwu

2.6.4 序列化/串行化

mysql> set global transaction isolation level serializable;
事务A												事务B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
insert into t_user values('abc');
													select * from t_user;
													//此时B卡住，等待A commit后才会执行

3. 索引（index）

3.1 什么是索引？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

MySQL在查询方面主要就是两种方式：全表扫描、索引检索。

注意：索引要排序，因为有了排序才会有区间。并且索引的排序和 TreeSet 数据结构相同。

在MySQL中，索引是一个 B-Tree 数据结构

3.2 索引的实现原理

t_user

id(PK) name 每一行记录在硬盘上的物理存储编号

100 zhangsan 0x1111

120 lisi 0x2222

99 wangwu 0x8888

88 zhaoliu 0x9999

101 jack 0x6666

55 lucy 0x5555

130 tom 0x7777

理解：（只是为了理解，MySQL数据结构并不是这个）
在这里插入图片描述

在任何数据库当中，主键上都会自动添加索引对象。另外在MySQL中，一个字段上如果有unique约束的话，也会自动创建索引对象。
在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM中，索引存储在一个 .MYI 文件中。在InnoDB中，索引存储在一个逻辑名称叫做 tablespace 当中。在MEMORY中，索引被存储在内存中。
不管索引存储在哪里，在MySQL中都是以一个数的形式存在（自平衡二叉树：B-Tree）

3.3 添加索引的条件

MySQL中，主键上，以及unique字段上都会自动添加索引。

什么条件下，会考虑添加索引？

数据量庞大
该字段经常出现在where的后面，以条件的形式存在
该字段很少的DML操作。因为DML之后，索引需要重新排序。

建议不要随意添加索引，因为索引也是需要维护的，太多反而会降低性能。

建议通过主键查询，和unique约束的字段查询，效率是比较高的。

3.4 索引的创建和删除

创建索引：

mysql> create index emp_ename_index on emp(ename);
// 给emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

mysql> drop index emp_ename_index on emp;
// 将emp表上的emp_ename_index索引对象删除。

3.5 MySQL中，怎么查看是否使用索引

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
// 扫描14条记录：说明没有使用索引。type=ALL

3.6 索引的失效

失效的第1种情况：

select * from emp where ename like '%T';

ename上即使添加了索引，也不会走索引，为什么？
原因是因为模糊匹配当中以“%”开头了！
尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略。

失效的第2种情况：

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';

失效的第3种情况：

使用复合索引的时候，没有使用左侧的列查找，索引失效

什么是复合索引？

两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

create index emp_job_sal_index on emp(job,sal); // 联合添加索引
mysql> explain select * from emp where job = 'MANAGER';

失效的第4种情况：

在where当中索引列参加了运算，索引失效。

mysql> create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;

失效的第5种情况：

在where当中索引列使用了函数

explain select * from emp where lower(ename) = 'smith';

…还有很多情况

3.7 索引分类

索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。

单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。
…

注意：唯一性比较弱的字段上添加索引用处不大。

4. 视图

4.1 概述

view:站在不同的角度去看待同一份数据。

4.2 视图创建与删除

// 表复制：
mysql> create table dept2 as select * from dept;
// dept2表中的数据：
mysql> select * from dept2;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+
// 创建视图对象
mysql> create view dept2_view as select * from dept2;
// 删除视图对象
mysql> drop view dept2_view;

注意：只有DQL语句才能以view的形式创建。

create view view_name as 这里的语句必须是DQL语句.

4.3 用视图做什么？

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）

作用：视图对象在实际开发中到底有什么用？《方便，简化开发，利于维护》

演示：

//面向视图查询
select * from dept2_view; 

// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

// 查询原表数据，数据也变了
mysql> select * from dept2;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	|     60 | SALES      | BEIJING  |
	+--------+------------+----------+

// 面向视图删除
mysql> delete from dept2_view;

// 查询原表数据
mysql> select * from dept2;
Empty set (0.00 sec)

// 创建视图对象
create view 
	emp_dept_view
as
	select 
		e.ename,e.sal,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

	// 查询视图对象
mysql> select * from emp_dept_view;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
| SMITH  |  800.00 | RESEARCH   |
| JONES  | 2975.00 | RESEARCH   |
| SCOTT  | 3000.00 | RESEARCH   |
| ADAMS  | 1100.00 | RESEARCH   |
| FORD   | 3000.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| TURNER | 1500.00 | SALES      |
| JAMES  |  950.00 | SALES      |
+--------+---------+------------+
// 面向视图更新
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';

// 原表数据被更新
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？

可以把这条复杂的SQL语句以视图对象的形式新建。

在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。

并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

5. 数据库设计三范式

数据库设计范式：数据库表的设计依据。教你怎么进行数据库表的设计。

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

Gggghc

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储引擎、事务、隔离级别、视图

1. 存储引擎1.1 什么是存储引擎？存储引擎是MySQL中特有的一个术语，（oracle中有，不叫这个名字）。实际上存储引擎是一个表存储/组织数据的方式不同的存储引擎，表存储数据的方式不同1.2 怎么指定存储引擎？show create table 表名;可以再建表的时候指定存储引擎。create table t_student( no int(11) not null auto_increment, nam varchar(255), primary key (no),
复制链接

扫一扫

专栏目录