MySQL(事务处理，数据库对象，数据库设计)

最新推荐文章于 2024-01-22 14:15:00 发布

HeartJade

最新推荐文章于 2024-01-22 14:15:00 发布

阅读量124

点赞数

分类专栏：笔记文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/qq_45762465/article/details/129143986

版权

笔记专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章详细介绍了MySQL中的事务处理，包括ACID特性、事务的隔离级别以及如何设置事务。还讨论了数据库对象如索引，特别是BTree和Hash索引的区别。此外，对比了InnoDB和MyISAM两个表引擎的特点。最后，简述了数据库设计的三范式和建模工具PowerDesigner的使用。

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

事务处理

是由一些SQL语句组成的一个可执行的逻辑单元

mysql是自动提交事务，自动将每个SQL语句都自动封装在一个事务单元中，自动提交。

事务的特性(ACID)

原子性：事务中执行的SQL语句如果有一条失败了，所有SQL语句回滚。要么成功都成功，要么失败都失败。

一致性：当提交事务后，数据库从一个一致性状态进入到另一个一致性性状态。

隔离性：事务在执行时使用的数据对其他并发事务的操作是隔离的。

持久性：事务一旦提交成功，就存储到数据库中不能回滚了。

set autocommit = 0 ; mysql默认是每执行一个SQL语句就创建一个事务，自动提交。set autocommit = 0 ;将当前连接的默认创建事务变成手动创建事务，取消自动提交。

执行sql语句时；将SQL语句的执行结果存储到缓存中；

rollback [to 保存点] ：回滚，取消操作，如果没有保存点，回滚到最初状态(前面的语句全部回滚),

如果向保存点回滚，回滚到保存点的状态。

commit时：提交事务，将缓存中的结果提交到数据库中

事务的隔离级别

并发性事务操作同一个数据时，根据需求要设置事务的隔离级别

并发性事务操作同一个数据时，产生一些问题：

脏读：

不可重复读：

幻读：

事务的隔离级别：

1.read uncommited ：可能出现脏读、不可重复读、幻读

2.read commited : 避免脏读、但不能避免不可重复读和幻读

3.repeatable read : 避免脏读、不可重复读但不能避免幻读（mysql默认的级别）

4.Serializble : 避免脏读、不可重复读和幻读

事务隔离级别越高，事务执行效率越低

查询看隔离级别：select @@tx_isolation

设置连接的隔离级别：set transaction isolation level 隔离级别

共享锁：每执行一次查询语句时，为查询的表加了共享锁一个表可以存着多个共享锁排他锁：更新、删除DML操作时，为表的记录添加了排他锁,不能在表上加共享锁和其他的排他锁

数据库对象

索引(index)

索引是一种数据库对象，可以为表添加索引，当按照索引进行检索时，效率高。

索引是要占用一定的空间，在执行检索语句时如果符合了索引规则，自动按索引进行检索。

当向表DML操作时，索引空间的内容也要改变

在创建索引时，不能对表进行DML操作

MySQL的索引使用两种数据库结构： BTree (B+树)和 Hash

普通索引

create index employee_index1 on employee(first_name,job_id)

主键索引

当指定了主键，主键就自动是主键索引，意味着通过主键查找记录效率高

唯一索引

当为表添加了唯一约束，就自动创建了唯一索引，反之亦然。

全文索引

FULLTEXT

删除索引

alter table 表名 drop index 索引名

注意事项：

1、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。例如在a,b,c列上创建索引，那么查询条件"a=5",“a=5 and b=6”,"a=7 and b=8 and c=6"都会使用该索引；

2、对于使用like的查询，查询如果是‘%aaa’不会使用到索引‘aaa%’会使用到索引；

3、如果是or 必须or条件都是索引才能使用索引检索

4、连接查询时，在连接键上增加索引可以加快速度，同时遵循小表驱动大表原则；

BTree的特点

根节点至少有两个子节点
非根节点包含的关键字个数m/2-1 <= 关键字个数 <= m-1
非根非叶子节点的度数(子节点的个数)=关键字个数+1
所有叶子节点都在同一层
叶子节点中存储数据的地址/主键，叶子和叶子节点有横向链指针。

BTree和Hash索引的区别

Hash索引只能满足 = 、in 、<> 的条件，而 > 或 < 或 like等不能满足，可以使用BTree索引。
Hash是无序的，无法避免数据排序操作。
Hash索引如果是组合索引，只能满足组合查询的条件，单个条件不满足，因为Hash索引是将组合数据的 Hash码存储在Hash表中。

MYSQL表引擎

InnoDB：支持事务处理。

MyISAM：读取操作的速度快，不占用大量的内存和资源，不支持事务处理。

Memory：表数据是存储在内存中，通常一些临时表、要求读写速度快的数据(热数据)

区别：

InnoDB支持事务处理，MyISAM不支持。
InnoDB支持外键约束，MyISAM不支持外键约束。
InnoDB使用的聚集索引，每个表一定有主键，没有设置主键系统会分配一个主键，通过BTree的形式将数据的主键和数据文件存储，如果设置了其他索引，通过索引找到主键，在通过主键查找到元素；MyISAM使用的非聚集索引，索引和数据文件是单独的，在BTree索引的叶子节点存储的就是数据的地址。
InnoDB不会存储记录数，执行select count() from 表操作时，是全文检索；MyISAM会有一个变量单独存储记录数，执行select count() from 表操作时，直接取出变量值
InnoDB在5.6版本后支持全文检索；MyISAM支持全文检索。
InnoDB支持表级锁和行级锁；MyISAM只有表级锁