MySQL企业级开发技术-CSDN博客

本文链接：https://blog.csdn.net/m0_73510821/article/details/130360645

事务

什么是事务

TRANSACTION 是将一系列数据操作捆绑成为一个整体进行统一管理机制多个操作作为一个整体向系统提交，要么都执行、要么都不执行是一个不可分割的工作逻辑单元

事务的四大特性，ACID属性

原子性：

事务是一个完整的操作，事务的各步操作是不可分的(原子的),要么都执行，要么都不执行

一致性：

当事务完成时，数据必须处于一致状态

隔离性：

并发事务之间彼此隔离、独立，不应以任何方式依赖于或影响其他事务

持久性：

事务完成后，他对数据库的修改被永久保持

如何创建事务

MySQL中支持事务的存储引擎

InnoDB支持事务操作

通过UNDO日志和REDO日志实现对事务的支持 UNDO日志复制事务执行前的数据，用于在事务发生异常时回滚数据 REDO日志记录在事务执行中，每条对数据进行更新的操作当事务提交时，该内容将被刷新到磁盘

MyISAM不支持事务操作

实现事务的方式

SQL语句设置自动提交关闭或开启

在执行命令SET autocommit=0，禁止当前会话的自动提交后，后面的SQL语句将作为事务中的语句一同提交

语法：

开始事务

BRGIN;

或

START TRANSACTION

提交事务

COMMIT;

回滚(撤销)事务

ROLLBACK; 数据的回滚，数据会回到操作之前状态

自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务关闭默认提交状态后，可手动开启、关闭事务

SET AUTOCOMMIT =0; 手动提交

SET AUTOCOMMIT =1；自动提交

关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务

使用事务时要遵循的原则

事务尽可能简短

事务启动至结束后在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃

事务中访问的数据量尽量最少

当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

在事务处理过程中尽量不要出现等待用户输入的操作

处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

索引

什么是索引

数据存储在数据表中，而索引是创建在数据库表对象上

由表中的一个字段或多个字段生成的键组成

是对数据库表中一列或多列值进行排列的一种结构

作用

大大提高数据库的检索速度改善数据库性能

MySQL索引按存储类型分类

B-树索引（BTREE）

InnoDB、MyISAM均支持

哈希索引（HASH）

常用索引类型

普通索引基本索引类型，允许在定义索引的列中插入重复值和空值

唯一索引索引列数据不重复，允许有空值

主键索引主键列中的每个值是非空、唯一的，一个主键将自动创建主键索引

复合索引将多个列组合作为索引

全文索引支持值的全文查找，允许重复值和空值

创建删除索引

创建索引

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name #唯一索引、全文索引或空间索引，可选 ON table_name (column_name [length] …);

如果创建索引是未指定创建索引的类型，则创建的索引为普通索引通过CREATE INDEX语句无法创建主键索引，主键索引的创建语句

ALTER TABLE tablename ADD PRIMARY KEY(column)

删除索引

语法：

DROP INDEX index_name ON table_name;

删除表时，该表的所有索引同时会被删除

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除如果组成索引的所有列都被删除，则整个索引将被删除

查看索引

SHOW INDEX FROM 表名；

创建索引的指导原则

按照下列标准选择建立索引的列

频繁搜索的列

经常用作查询选择的列

经常排序、分组的列

经常用作连接的列（主键、外键）

不要使用下面的列创建索引

仅包含几个不同值的列

表中仅包含几行

使用索引时注意事项

查询时减少使用“*”返回表的全部列，不要返回不需要的列索引应该尽量小，在字节数小的列上建立索引 WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前避免在ORDER BY子句中使用表达式根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

视图

什么是视图

视图是一张虚拟表

表示一张表的部分数据或多张表的综合数据

其结构和数据是建立在对表的查询基础上

视图中不存放数据

数据存放在视图所引用的原始表中

一个原始表，根据不同用户的不同需求，可以创建不同的视图

用途

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

将多个物理数据表抽象为一个逻辑数据库

带来的好处

开发人员限制数据检索更容易维护应用程序更方便

最终用户结果更容易理解获得数据更容易

如何创建、查看视图

使用SQL语句创建视图

CREATE VIEW view_name AS <SELECT 语句>;

使用SQL语句删除视图

DROP VIEW [IF EXISTS] view_name;

使用SQL语句查看视图

SELECT 字段1，字段2,.....FROM view_name;

使用视图注意事项

视图中可以使用多个表一个视图可以嵌套另一个视图，但最好不要超过3层对视图数据进行添加、更新和删除操作会直接影响所引用表中的数据当视图数据来自多个表时，不允许添加和删除数据

查看所有视图

USE information_schema; SELECT * FROM views\G;

使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

数据库备份

数据的备份与恢复

数据备份

是容灾的基础指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其他的存储介质的过程 MySQL数据备份的常用方法： mysqldump备份数据库 Navicat备份数据库

数据恢复

是指通过技术手段，将保存在硬盘等存储介质上的丢失的数据进行抢救和恢复的技术

使用mysqldump命令备份数据库

mysqldump命令——MySQL的客户端常用逻辑备份工具

将CREATE和INSERT INTO语句保存到文本文件属于DOS命令

语法：

mysqldump [options] database [table1,[table2]…] > [path]/filename.sql

options的选项参数 –u username: 表示用户名 –h host: 表示登录的主机名称，如本机为主机可省略 –ppassword: 表示登录密码

mysqldump是DOS系统下的命令在使用时，无须进入mysql命令行；否则，将无法执行

常用参数选项

参数	描述
-add-drop-table	在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table取消
--add-locks	该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句
好处：防止记录被再次导入时，其他用户对表进行的操作
-t或-no-create-info	只导出数据，而不添加CREATE TABLE语句
-c或--complete-insert	在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用
-d或--no-data	不写表的任何行信息，只转储表的结构