MYSQL的事务、视图、索引、数据备份和恢复

向右看齐 2024-04-07 12:04 81阅读 0赞

**目录**

一、事务

1、事务的四大属性：简称ACID

2、MYSQL事务处理

3、并发事务问题

4、事务隔离级别

二、视图

三、索引

1、常见的所引类型

2、创建索引的指导原则

四、SQL语句优化

五、MYSQL的备份

--------------------

### 一、事务 ###

事务：事务（TRANSACTION）是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库操作命令要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元 。

例如：银行转账业务

假定给张三开卡，卡号4001，并在其账户上存1000元，给李四开卡，卡号4002，账户上存1元。

CREATE TABLE IF NOT EXISTS mybank(
        cid CHAR(4), #卡号
        cname VARCHAR(30),#用户名
        balance DECIMAL(10,2)  #当前余额  );
    /*插入数据*/
    INSERT INTO mybank(cid,cname,balance)VALUES(4001,'张三',1000);
    INSERT INTO mybank(cid,cname,balance)VALUES(4002,‘李四',1);

模拟实现转账：从张三的账户转账500元到李四的账户

/*--转账测试:张三希望通过转账,直接汇钱给李四500元--*/
    #张三的账户减500元,李四的账户增500元
    UPDATE `mybank` SET balance=balance-500   WHERE cid=4001;
    UPDATE `mybank` SET balance=balance+500  WHERE cid=4002;

转账过程就是一个整体，它需要两条UPDATE语句来完成，这两条语句是一个整体

如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元

#### 1、事务的四大属性：简称ACID ####

1、原子性（Atomicity）

事物的各步操作是不可分割的（原子的），要么都执行，要么都不执行。

2、一致性（Consistency）

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

3、隔离性（Isolation）

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

4、持久性（Durability）

事务一旦提交或回滚，他对数据库的修改被永久的。

#### 2、MYSQL事务处理 ####

MySQL中支持事务的存储引擎有InnoDB和BDB**。**

方法1：用 BEGIN, COMMIT,ROLLBACK来实现。

BEGIN 或者 START TRANSACTION;   #开始事务，显式地标记一个事务的起始点
    COMMIT ; #提交事务，标志一个事务成功提交。
    ROLLBACK; #回滚事务，清除自事务起始点至该语句所做的所有数据更新，将数据状态回滚到事务开始前，并释放由事务控制的资源。

方法2：直接用 SET 来改变 MySQL 的自动提交模式。

默认情况下，每条单独的SQL语句视为一个事务
    关闭默认提交状态后，可手动开启、关闭事务
     SET autocommit = 0|1;
    值为0：关闭自动提交
    值为1：开启自动提交

使用事务解决银行转账问题

SET autocommit=0; #关闭自动提交
    #从下一条开始一个事务
    UPDATE mybank SET balance=balance-500 WHERE cid=5001;
    UPDATE mybank SET balance=balance+500 WHERE cid=5002;
    COMMIT;
    UPDATE mybank SET balance=balance-1000
    WHERE cid=5001;
    #回滚事务
    ROLLBACK;
    #开启自动提交
    SET autocommit=1;

#### 3、并发事务问题 ####

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>问题</td> 
   <td>描述</td> 
  </tr> 
  <tr> 
   <td>脏读</td> 
   <td>一个事务读到另一个事务还没有提交的数据</td> 
  </tr> 
  <tr> 
   <td>不可重复度</td> 
   <td>一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复度</td> 
  </tr> 
  <tr> 
   <td>幻读</td> 
   <td>一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时又发现数据已经存在，好像出现了‘幻影’</td> 
  </tr> 
 </tbody> 
</table>

#### 4、事务隔离级别 ####

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>隔离级别</td> 
   <td>脏读</td> 
   <td>不可重复度</td> 
   <td>幻读</td> 
  </tr> 
  <tr> 
   <td>Read uncommitted</td> 
   <td>会出现</td> 
   <td>会出现</td> 
   <td>会出现</td> 
  </tr> 
  <tr> 
   <td>Read committed</td> 
   <td>不会出现</td> 
   <td>会出现</td> 
   <td>会出现</td> 
  </tr> 
  <tr> 
   <td>Repeatable Read（默认）</td> 
   <td>不会出现</td> 
   <td>不会出现</td> 
   <td>会出现</td> 
  </tr> 
  <tr> 
   <td>Serializable</td> 
   <td>不会出现</td> 
   <td>不会出现</td> 
   <td>不会出现</td> 
  </tr> 
 </tbody> 
</table>

查看事务隔离级别

-- 高版本mysql使用
    select @@transaction_isolation;
    -- 低版本mysql使用
    select @@tx_isolation;

设置事务隔离级别

语法：

-- 设置隔离级别
    set [session | global] transaction isolation level {Read uncommitted |Read committed | Repeatable Read | Serializable};

例如：

-- 设置隔离级别
    set session transaction isolation level repeatable read ;

注意：事务隔离级别越高，数据越安全，但是性能越低。

### 二、视图 ###

视图是一张虚拟表，表示一张表的部分数据或多张表的综合数据。其结构和数据是建立在对表的查询基础上视图中不存放数据，数据存放在视图所引用的原始表中一个原始表，根据不同用户的不同需求，可以创建不同的视图

视图的用途：筛选表中的行；防止未经许可的用户访问敏感数据；将多个物理数据库表抽象为一个逻辑数据库表

使用SQL语句创建视图

CREATE VIEW view_name  
    AS
    <SELECT 语句>;

使用SQL语句查看视图

SELECT 字段1, 字段2, …… FROM view_name;

使用SQL语句删除视图

DROP VIEW [IF EXISTS] view_name;

实例：

#创建方便教师查看成绩的视图
    DROP VIEW IF EXISTS `v_result`;
    CREATE VIEW v_result
    AS
    SELECT s.studentno AS sno,studentname AS sname
    ,studentresult AS score
    FROM student AS s
    LEFT JOIN 
    result AS r
    ON s.`studentno`=r.`studentno`
    
    #使用视图
    SELECT sno,sname,score FROM v_result;

注意：

1、视图中可以使用多个表

2、一个视图可以嵌套另一个视图

3、对视图数据进行添加、更新和删除操作直接影响所引用表中的数据

4、当视图数据来自多个表时，不允许添加和删除数据

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

### 三、索引 ###

索引是一种有效组合数据的方式，为快速查找到指定记录。

**作用：**大大提高数据库的检索速度；改善数据库性能

MySQL索引按存储类型分类：B-树索引：InnoDB、MyISAM均支持；哈希索引

#### 1、常见的所引类型 ####

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>普通索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>基本索引类型</strong> 
    </div> 
    <div style="margin-left:.38in;">
      • 
     <strong>允许在定义索引的列中插入重复值和空值</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>唯一索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>索引列数据不重复</strong> 
    </div> 
    <div style="margin-left:.38in;">
      • 
     <strong>允许有空值，创建唯一约束时自动创建唯一索引</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>主键索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>主键列中的每个值是非空、唯一的</strong> 
    </div> 
    <div style="margin-left:.38in;">
      • 
     <strong>一个主键将自动创建主键索引</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>复合索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>将多个列组合作为索引</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>支持值的全文查找</strong> 
    </div> 
    <div style="margin-left:.38in;">
      • 
     <strong>允许重复值和空值</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>空间索引</td> 
   <td> 
    <div style="margin-left:.38in;">
      • 
     <strong>对空间数据类型的列建立的索引</strong> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

使用SQL语句创建索引

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    ON table_name (column_name[length]…);

使用SQL语句删除视图

语法：

DROP  INDEX index_name ON table_name;

实例：

#student表的studentName列创建普通索引
    CREATE  INDEX `index_student_studentname`  ON `student`(`studentname`);

#### **2、创建索引的指导原则** ####

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

1、频繁搜索的列

2、经常用作查询选择的列

3、经常排序、分组的列

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

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

1、仅包含几个不同值的列

2、表中仅包含几行

使用索引时不能模糊查询，不能使用函数，因为这样会不走索引，不要随便创建索引，用到的时候在建，因为索引也是占表内存的，每个表最多只能创建249个索引

### **四、SQL语句优化** ###

1、查询时减少使用\*返回全部列，不要返回不需要的列；

2、索引应该尽量小，在字节数小的列上建立索引；

3、WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前；

4、避免在ORDER BY子句中使用表达式。

5、根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理。

### **五、MYSQL的备份** ###

数据库备份必要性：保证重要数据不丢失；数据转移

**MySQL****数据库备份方法：**

数据库管理工具，如SQLyog

mysqldump备份工具

直接拷贝数据库文件和相关配置文件

语法：

-- 数据备份  /path/db_name.sql 为保存路径及保存文件名
    mysql –u root –p  dbname  >  /path/db_name.sql;
    
    --数据恢复
    
    mysql –u root –p  dbname  <  /path/db_name.sql;

使用Navicat 备份文件

点击你要备份的数据库右键

![cf4578f3a3f34aa0ba7856166225d4fd.png][]

2、点击转储SQL文件，选择结构和数据

![d60533962f724b4d9d8542dfcb2df382.png][]

[cf4578f3a3f34aa0ba7856166225d4fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d0da951c8e3b40e4bc50df25d598fc95.png
[d60533962f724b4d9d8542dfcb2df382.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d5d523ccec734963b75e735901b2e299.png