MySQL 事务、视图、索引

最新推荐文章于 2023-04-10 02:55:51 发布

匡夆

最新推荐文章于 2023-04-10 02:55:51 发布

阅读量241

点赞数

分类专栏： Python 文章标签： mysql python

本文链接：https://blog.csdn.net/weixin_46108954/article/details/104819967

版权

Python 专栏收录该内容

45 篇文章 1 订阅

订阅专栏

MySQL 事务、视图、索引

一、视图

1.为什么使用视图？

安全——不希望用户看到整个数据库的结构，所以对不同权限的用户使用不同的视图隐藏必要数据；
方便——让用户更容易获得、理解数据；

2.什么是视图？

视图是一种查看数据库中一个或多个表中数据的方法，是保存在数据库中的select查询。

视图是一种虚拟表，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据。

3.视图常用来进行下面三种操作

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

4.创建视图格式

1）创建视图格式：
     create view view_Name as <select子句>;

2）删除视图   （如果数据库中已经存在同名视图，先删除）
     drop view if exists view_student;

3）查看视图 
     select * from view_student;

5.创建视图

create view cs_student as
SELECT *
FROM student
WHERE sdept='cs'

查看

select * from cs_student；

在这里插入图片描述

6.使用视图的注意事项

每个视图中可以使用多个表
与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层；
对视图数据进行添加、更新和删除操作直接影响表中的数据；
当数据来自多个表时，不允许添加和删除数据。

7.视图的作用

提高了重用性，就像一个函数
对数据库重构，却不影响数据库的运行
提高了安全性能，可以对不同的用户
让数据更加清晰

二、事务

1.什么是事务？

SQL中，事务是指将一系列数据操作捆绑成为一个整体进行统一管理。

如果一个事务执行成功，该事务中进行的所有数据均会提交，称为数据库中的永久组成部分；

如果事务执行时遇到错误切必须取消or回滚，则数据将全部恢复到操作前的状态，所有更改会被清除。

在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。

2.事务的ACID属性

原子性（Atomicity）：一个事物必须被视为一个不可分割的最小单位，这个事物中的所有操作要么全部提交成功，要么全部回滚失败，对于一个事物来说，不可能只执行其中的一部分操作，这就是事物的原子性。

事务中的所有元素必须作为一个整体提交或回滚。
一致性（Consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。

事务完成时，数据必须处于一致状态。在事务进行中，可能会不一致。如转账操作中的金额总量，甲少了同时乙增加了。
隔离性（isolation）：通常来说，一个事物所做的修改在最终提交以前，对其他事物是不可见得（在执行完之前，对方并不知道执行结果）

对数据进行修改的所有并发事务是彼此隔离的，即事务是独立的。
持久性(durability)：一旦事物提交，则其所做的修改会永久保存到数据库中。（此时，及时系统崩溃，修改的数据也不会丢失）

指不管系统是否发生了故障，事务处理的结果都是永久的。就是说一旦事务被提交，事务的效果会被永久地保留在数据库中。

3.如何执行事务

MySQL的默认存储引擎InnoDB支持事务，InnoDB主要通过UNDO和REDO日志实现

UNDO日志：复制事务执行前的数据，用于在事务发生异常时回滚数据；

REDO日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，内容会一起被刷新到磁盘。

4.事务的执行过程

MySQL的默认设置下，每句SQL语句就是一个事务，即执行SQL语句后自动提交。

（1）开始时，开启事务 or 关闭默认的自动提交：begin; start transaction;

（2）提交事务 commit;

（3）回滚事务 rollback；

5.MySQL中事务的隔离级别

MySQL中事务的默认隔离级别是repeatable-read（可重读）级别

6.编写事务的原则

事务尽可能简短，较大的事务占用的资源很多；
事务中访问的数据量尽量少，这样事务之间对资源的争夺就少；
查询数据不要使用事务；
事务处理的过程中尽量不要出现等待用户输入的操作；

三、索引

创建格式

create [unique|fulltext|spatial|] index index_name

  on tableName(colum_Name[length]...);

1.什么是索引

索引提供指针以指向存储在表中指定列的数据值，再根据指定的排序次序排列这些指针。

索引是创建在数据库表对象上的，由表中的一个字段 or 多个字段生成的键组成。

这些键存储在数据结构（B-树或哈希表）中，因此根据索引的存储类型，B树索引和哈希索引。

InnoDB和MyISAM存储引擎支持B-树索引。

2.索引分类

在存储类型为InnoDB的表中，经常使用唯一索引、普通索引、组合索引来提高查询效率。

（1）普通索引

允许在定义索引的列中插入重复值和空值，唯一任务是加快对数据的访问速度。

因此，应该只为那些最常出现在查询条件或排序条件中的数据列创建索引。

（2）唯一索引

唯一索引不允许两行具有相同的索引值。

若数据中存在重复的键值，一般情况下，所属数据库是不允许创建唯一索引；

若已经创建了唯一索引，当新数据使得表中的键值重复时，数据库也拒绝接受此数据。

创建了唯一索引的列允许有空值。

（3）主键索引

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。

主键索引要求主键中的每个值是非空、唯一的，当在查询中使用主键索引时，它还允许快速访问数据。

（4）复合索引

创建索引时，可以将多个列组合作为索引。

需要注意的是，只有在查询中使用了组合索引最左边的字段时，索引才会被使用。

（5）全文索引

全文索引的作用是在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引可以在char、varchar或text类型的列上创建，主要用于在大量文本文字中搜索字符串，此时使用全文索引的效率将大大高于使用SQL的like关键字的效率。

（6）空间索引

对空间数据类型的列建立的索引，如geometry、point等。

创建空间索引的列，必须为not null，且只能在存储引擎为MyISAM的表中创建。

3.选择建立索引的列

频繁搜索的列；
经常用作查询的列；
经常排序、分组的列；
经常用作连接的列（主键/外键）

四、备份与恢复数据

1.备份数据

格式

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

mysqldump -uroot -p123 数据库名 > backup.sql 
 
mysqldump -uroot -p123 test > backup.sql

2.恢复数据

1)连接mysql,创建新的数据库

2)退出数据库，执行如下命令：

mysql -uroot -p123 新数据库名 < backup.sql 
 
mysql -uroot -p123 test1 < backup.sql

五、MYSQL主从

1.主从同步的定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

2.使用主从同步的好处

1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

2.提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。

3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

3.主从同步的机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGEMASTERTO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）