MySQL速览——存储引擎|事务|索引|范式

最新推荐文章于 2024-10-13 21:08:42 发布

秃头青年Juniors

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量1.6k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_41010363/article/details/121343913

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的存储引擎，包括MyISAM、InnoDB和MEMORY的特点和使用场景，强调了InnoDB对事务的支持。同时，文章讨论了事务的四大特性、隔离级别以及如何创建、提交和回滚事务。此外，还提到了索引的概念、类型和优化，并概述了数据库设计的三范式。

摘要由CSDN通过智能技术生成

存储引擎

概念

是MySQL特有的术语

不同的存储引擎，表存储/组织的数据的方式不同。

如何添加或指定存储引擎

可以在建表的时候在最后括号后使用：

ENGINE 来指定存储引擎
CHARSET 来指定这张表的字符编码方式
AUTO_INCREMENT 自增步长

默认存储引擎：InnoDB

默认字符编码方式：utf-8

查看MySQL所支持的存储引擎

>show engines \G

MySQL 支持九大存储引擎

重要的存储引擎

MyISAM

它管理表具有一下特征：

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

格式文件：存储表结构的定义(mytable.frm)
数据文件：存储表行的内容(mytable.MYD)
索引文件：存储表上索引(mytable.MYI)
对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引

MyISAM 优势：

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

InnoDB

这是MySQL默认的存储引擎，同时也是一个重量级的存储引擎。

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

主要特点：非常安全

它管理的表有以下特征：

每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablebase被用于存储表的内容(表空间为逻辑概念)
提供一组用来记录事务性活动的日志文件
用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
提供全 ACID 兼容
在 MySQL 服务器崩溃后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用和完整性，包括级联删除和更新

InnoDB 最大的特点：支持事务：

以保证数据的安全，因此效率不是很高，并且也不能压缩，也不能转换为只读，不能很好的节省空间。

MEMORY

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定

这两个特点使得 MEMORY 存储引擎非常快

MEMORY 有以下特征：

在数据库目录中，每个表均以.frm格式的文件表示
表数据及索引被存储在内存中
表级锁机制
不能包含 TEXT/BLOB 字段

优点：查询效率最高

缺点：不安全，关机之后数据消失

事务

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

只有 DML(Insert,Delete,Update)语句才会有事务，其他与事务无关

创建事务

start transaction;

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

在事务的执行过程中，每一条DML的操作都会记录到"事务性活动的日志文件"中，我们可以提交事务，和回滚事务。

提交事务

>commit;

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

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

-mysql默认情况下是支持自动提交事务的。

回滚事务

>rollback;

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

事务特性

A:原子性 - 说明事务是最小的工作单元

C:一致性 - 所有事务要求，同一个事务当中，要么成功，要么失败，以保证数据一致性

I:隔离性 - A事务和B事务之间具有一定的互斥性

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

事务隔离级别

读未提交：read uncommited (最低)
- 事务A可以读到事务B未提交的数据
  - 存在问题：脏读(Dirty Read)(理论,几乎所有数据库的隔离级别都是二档起步)
读已提交：read commited
- 事务A只能读取到事务B提交之后的数据
  - 解决了脏读现象
    - 存在着不可重复读取数据的问题(每次读取的数据可能不同)
可重复读：repeatable read (MySQL默认的事务隔离级别)
- 事务A开启之后，不管多久，每次事务A读取事务B已修改的数据，仍然没有发生改变，这就是可重复读
  - 解决了不可重复读的问题
    - 可能会出现幻读(每次读取的数据都是幻象，不够真实)
串行化/序列化：serializable (最高)

这是最高的隔离级别，因此效率最低，解决了所有问题。

这种隔离级别表示事务排队，不能并发

类似 synchronized

查看隔离级别：

SELECT @@tx_isolation

设置隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

索引

相当于目录，快速检索

索引实现原理

提醒：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，unique约束的话，也会自动创建索引对象

索引在mysql当中都是以自平衡二叉树:B-tree 存在

添加索引场景

数据量庞大
该字段经常出现在where后面
该字段很少DML操作(因为DML之后，索引需要重新排序)

创建/删除/查看索引

mysql>create index emp_name_index on emp(ename);

mysql>drop index emp_name_index on emp(ename);

mysql>explain select * from emp where ename = "Juniors";

索引失效

第一种失效情况：

mysql>select * from emp where ename like "%T";

此时 ename 上即使添加了索引，也不会走索引

原因：是因为模糊匹配当中以"%"开头

解决方案:尽量避免模糊查询的时候以"%"开始

第二种索引失效：

mysql>select * from emp where ename = "KING" or ename = "KOBE";

使用 or 的时候会失效，如果使用or那么要求两边的条件字段都要有索引，才会走索引，否则不会走索引。

第三种索引失效：

mysql>create index emp_job_sal_index on emp(job,sal);
mysql>select * from emp where job = 'MANAGER';

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

第四种索引失效：

在where语句中索引参加了运算，索引会失效

mysql>explain select * from emp where sal + 1 = 800;

第五种索引失效：

在where语句中索引列使用了函数

mysql>explain select * from emp where lower(ename);

索引分类

单一索引：单个字段
复合索引：多个字段
主键索引：主键上添加
唯一索引：具有unique约束的字段上添加

字段越唯一效率越高。

数据库设计三范式

3种。

第一范式：

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

最核心，最重要的范式，所有的表的设计都需要满足。

1）必须有主键

2）每一个字段都是原子性不可再分

第二范式：

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

(学生编号和教师编号，两个字段联合做主键，复合主键(PK=sid+tid))

但学生姓名依赖sid 老师姓名依赖tid ，显然产生了部分依赖，因此产生了数据冗余

多对多，三张表，关系表两外键

第三范式：

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

按照以上的范式进行，可以避免表中的数据冗余，空间的浪费

一对多，两张表，多的表加外键