MySQL05-事务，索引和视图-（提高篇）

最新推荐文章于 2024-04-27 08:43:32 发布

InnerXuan

最新推荐文章于 2024-04-27 08:43:32 发布

阅读量136

点赞数

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

本文链接：https://blog.csdn.net/Nevermind_goon/article/details/105486530

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

存储引擎（了解）

什么是存储引擎？

存储引擎这个名字只有在mysql中存在（Oracle中有对应的机制，但是不叫做搜索引擎，oracle中没有特殊的名字，就是表的存储方式）

mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式

每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

完整的建表语句其实的这样的

create table `表名`(
  `id` int(11) default NULL
)ENGINE=INNODE DEFAULT CHARSET=utf8

mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF8

事务(Transaction)

什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分

和事务相关的只有DML语句（insert delete updata）

因为这三个语句都是和数据库表当中的“数据”相关的，事务的存在是为了保证数据的完整性，安全性

比如：银行账号转账，从A账户向B账户转账10000,需要执行两条update语句

updata t_act set balance = balance-10000 where actno = 'act-001'
updata t_act set balance = balance +10000 where actno = 'act-002'

以上两条DML语句必须同时成功或者同时失败，不允许出现一条成功，一条失败

要保证以上两条DML语句同时成功或同时失败，那么就需要使用数据库的“事务机制”

假设一个业务，需要先执行一条insert 再执行一条updata，最后执行一条delete，才算完成这个业务

开启事务机制(开始)

1.执行insert语句–>inset…

这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。

2.执行updata语句---->

这个执行也是记录一下历史操作，不会真正修改硬盘上的数据

3.执行delete语句—delete

这个执行也是记录历史操作，记录到缓存，不会真正修改硬盘上的数据

提交事务或者回滚事务

事务包括四大特性：ACID

A : 原子性：事务是最小的工作单元，不可再分。

C: 一致性：事务必须保证多条DML语句同时成功或同时失败。

I: 隔离性：事务A 与事务B 之间具有隔离。

D:持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

事务的隔离性：

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读（Dirty Read）现象，· 表示读到了脏的数据

第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以读取到。

这种隔离级别解决了：脏读现象没有了

第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题

这种隔离级别存在的问题是：读取到的数据是幻象
第四级别：序列化读/串性化读

解决了所有问题

效率低，需要事务排队。

oracle数据库默认的隔离级别是：读已提交。

mysql数据库默认的隔离级别是：可重复读。

使用事务

开启事务

start transation;

提交事务

commit;

回滚事务

rollback;

演示（使用默认的隔离级别：可重复读）

#开启了事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#插入
mysql> insert into t_user(username,password)values('wangwu','sdfdsf');
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from t_user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | lisi     | qwe      |
|  2 | wsi      | qwe      |
|  3 | wangwu   | sdfdsf   |
+----+----------+----------+
3 rows in set (0.00 sec)
#插入
mysql> insert into t_user(username,password)values('wengwu','sdfdsf');
Query OK, 1 row affected (0.00 sec)
#插入
mysql> insert into t_user(username,password)values('wgwu','ssf');
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from t_user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | lisi     | qwe      |
|  2 | wsi      | qwe      |
|  3 | wangwu   | sdfdsf   |
|  4 | wengwu   | sdfdsf   |
|  5 | wgwu     | ssf      |
+----+----------+----------+
5 rows in set (0.00 sec)
#回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
#恢复了数据
mysql> select * from t_user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | lisi     | qwe      |
|  2 | wsi      | qwe      |
+----+----------+----------+
2 rows in set (0.00 sec)
#提交了事务，事务结束
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

设置事务的隔离等级

set global transaction isolation level read uncommitted;

查看事务的全局隔离等级：

select @@global.tx isolation

索引

什么是索引？

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

在数据库方面，查询一张表的时候有两种方式。

第一种方式：全表扫描

第二种方式：根据索引检索（效率高）

索引为什么可以提高检索的效率呢？

缩小了检索的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库的维护，是有维护成本的，比如，表中的数据经常被修改这样就不合适添加索引，因为数据一旦被修改，索引需要重新排序，进行维护。

添加索引是给某个字段，或者说某些字段添加索引。

怎么创建索引？

create index 索引名称 on 表名(字段名);

怎么删除索引？

drop index 索引名称 on 表名;

什么时候考虑给字段添加索引？

数据量庞大
该字段很少的DML操作。
该字段经常出现在where子句中

主键和具有unique约束的字段自动添加索引。

查看sql语句的执行计划：

explain select ename,sal from emp where sal=5000;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

给sal字段添加index之后

create index sal_emp_index on emp(sal);

再次查询

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | sal_emp_index | sal_emp_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

可以看到type 变成了ref

索引底层采用的数据结构：B+Tree

索引的实现原理？

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，

最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位表中的数据，效率高

select ename from emp where ename ="smith"

转化为：

select ename from emp where "物理地址"=0x3234

索引什么时候失效？

select ename from emp where ename like '%A%'

模糊查询的时候，第一个统配符使用的是%,这个时候索引是失效的

视图

什么是视图？

同一张表，通过不同的角度去看待

怎么创建删除视图

create view myview as select empno,ename from emp;
drop view myview;

使用视图？

create table_emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp_bak;
updata myview1 set ename='hehe',sal=1,where empno=7369;
delete from myview1 where empno=7369;

对视图的增删查改，会影响到原表数据

视图的作用？

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，程序员只对视图对象进行CRUD.

InnerXuan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL05-事务，索引和视图-（提高篇）

存储引擎（了解）什么是存储引擎？存储引擎这个名字只有在mysql中存在（Oracle中有对应的机制，但是不叫做搜索引擎，oracle中没有特殊的名字，就是表的存储方式）mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。完整的建表语句其实的这样的create table `表名`( `id` int(...
复制链接

扫一扫