MySQL 索引、事务和视图

🎉🎉🎉点进来你就是我的人了
博主主页：🙈🙈🙈戳一戳,欢迎大佬指点!
人生格言：当你的才华撑不起你的野心的时候,你就应该静下心来学习!

欢迎志同道合的朋友一起加油喔🦾🦾🦾
目标梦想：进大厂，立志成为一个牛掰的Java程序猿，虽然现在还是一个🐒嘿嘿
谢谢你这么帅气美丽还给我点赞！比个心

---

目录

一.事务

1.事务及其特征 

【1】事务的概念

【2】事务的特性

【3】sql展示：使用事务保证转账安全

 2.事务并发问题

2.1脏读（Dirty read） 

2.2不可重复读 

2.3幻读 

​2.4不可重复度和幻读区别： 

3.事务隔离级别 

二.视图

【1】视图的概念

【2】视图的好处

【3】SQL展示：

三.存储过程

【1】什么是存储过程(Stored Procedure)

【2】存储过程的优点

 四. 索引

4.1 创建索引：

4.2 删除索引：使用 DROP INDEX 命令可以删除索引

4.3 查看索引：可以使用 SHOW INDEXES 命令查看一个表的所有索引信息

4.4 在查询中使用索引：

---

---

一.事务

1.事务及其特征 

事务（Transaction）是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。

例子1：
举一个例子来进行说明，例如转账操作：A账户要转账给B账户，那么A账户上减少的钱数和B账户上增加的钱数必须一致，也就是说A账户的转出操作和B账户的转入操作要么全部执行，要么全不执行；如果其中一个操作出现异常而没有执行的话，就会导致账户A和账户B的转入转出金额不一致的情况，为而事实上这种情况是不允许发生的，所以为了防止这种情况的发生，需要使用事务处理。
例子2：
在淘宝购物下订单的时候，商家库存要减少，订单增加记录，付款我的账号少100元...操作要么全部执行，要么全不执行

【1】事务的概念

事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。
目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。

【2】事务的特性

事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。
但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。

 1)原子性
原子是自然界最小的颗粒，具有不可再分的特性。事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。
使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行。通常，某个事务中的操作都具有共同的目标，并且是相互依赖的。如果数据库系统只执行这些操作中的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。
 
2) 一致性
一致性是指事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。
例如：在转账时，只有保证转出和转入的金额一致才能构成事务。也就是说事务发生前和发生后，数据的总额依然匹配。
 
3) 隔离性
隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。
例如：在转账时，只有当A账户中的转出和B账户中转入操作都执行成功后才能看到A账户中的金额减少以及B账户中的金额增多。并且其他的事务对于转账操作的事务是不能产生任何影响的。

4)持久性
持久性指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

【3】sql展示：使用事务保证转账安全

-- 创建账户表：
create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double
);
-- 查看账户表：
select * from account;
-- 在表中插入数据：
insert into account values (null,'丽丽',2000),(null,'小刚',2000);
-- 丽丽给小刚 转200元：
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。
update account set balance = balance - 200 where id = 1;
update account set balance = balance2 + 200 where id = 2;
-- 必须让上面的两个操作控制在一个事务中：
-- 手动开启事务：
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 手动回滚：刚才执行的操作全部取消：
rollback;
-- 手动提交：
commit;
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

 2.事务并发问题

2.1脏读（Dirty read） 

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

2.2不可重复读 

（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

2.3幻读 

（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

2.4不可重复度和幻读区别： 

不可重复读的重点是修改，幻读的重点在于新增或者删除。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表 

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读 

3.事务隔离级别 

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

    事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。

 PS：√  代表会出现问题   ， ×代表不会出现问题 = 解决问题 

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read  
select @@transaction_isolation;  
-- 设置事务的隔离级别   （设置当前会话的隔离级别）
set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  
set session transaction isolation level serializable;  
start transaction ;
select * from account where id = 1;

二.视图

【1】视图的概念

视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

PS:视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

【2】视图的好处

简化用户操作：视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。

对机密数据提供安全保护：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段“salary”）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

【3】SQL展示：

-- 创建/替换单表视图：
create or replace view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20
with check option;

-- 查看视图：
select * from myview01;

-- 在视图中插入数据：
insert into myview01 (empno,ename,job,deptno) values (9999,'lili','CLERK',20);
insert into myview01 (empno,ename,job,deptno) values (8888,'nana','CLERK',30);
insert into myview01 (empno,ename,job,deptno) values (7777,'feifei','CLERK',30); 
-- > 1369 - CHECK OPTION failed 'mytestdb.myview01'

-- 创建/替换多表视图：
create or replace view myview02
as 
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000 ;
select * from myview02;

-- 创建统计视图：
create or replace view myview03
as
select e.deptno,d.dname,avg(sal),min(sal),count(1)
from emp e
join dept d
using(deptno)
group by e.deptno ;
select * from myview03;

-- 创建基于视图的视图：
create or replace view myview04
as
select * from myview03 where deptno = 20;
select * from myview04;

三.存储过程

【1】什么是存储过程(Stored Procedure)

通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。

但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。

在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。

这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。

【2】存储过程的优点

1) 提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

2) 可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
3) 可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

---

 四. 索引

MySQL 中的索引是用来提高数据查询效率的一种数据结构。具体来说，索引可以帮助我们在大量数据中更快地找到需要的记录。其作用和道路指示牌有点类似，可以帮助我们更快地找到目的地。

以下是关于索引的一些重要点：

1. 查询性能提升：索引最主要的作用就是提高数据查询的速度。如果没有索引，数据库需要执行全表扫描，也就是查看每一行数据，来找到我们需要的记录。而如果有了索引，数据库就可以直接定位到我们需要的数据，大大提高查询效率。

2. 索引的类型：在MySQL中，有多种类型的索引，如B-Tree索引（最常用的索引类型，适合全值匹配和范围查询），哈希索引（适合等值查询，但不适合范围查询和排序），全文索引（适合进行全文搜索）等。

3. 索引并非万能：虽然索引可以提高查询速度，但是索引并不是越多越好。因为索引本身也需要存储空间，同时在插入、删除和更新数据时，索引也需要被维护，这将增加写操作的复杂性和时间。因此，设计索引时需要权衡查询效率和存储/维护成本。

4. 选择合适的索引列：不是所有的列都适合创建索引。一般来说，我们应该在查询条件经常出现并且分布均匀（即列的值有很好的区分度）的列上创建索引。

在MySQL中，使用索引主要涉及到以下几个方面的操作：创建索引、删除索引、查看索引以及在查询中使用索引。

4.1 创建索引：

创建索引可以在创建表的时候一起完成，也可以在表创建完后再添加。以下是一些创建索引的语法：

在创建表的时候创建索引:(要对哪个字段创建索引就在括号里填写字段名)

CREATE TABLE my_table (
    id INT NOT NULL,
    lastname VARCHAR(50),
    firstname VARCHAR(40),
    INDEX (lastname)
);

在表创建完后添加索引：

CREATE INDEX idx_lastname
ON my_table (lastname);

创建唯一索引：

CREATE UNIQUE INDEX idx_lastname
ON my_table (lastname);

4.2 删除索引：使用 DROP INDEX 命令可以删除索引

DROP INDEX idx_lastname ON my_table;

4.3 查看索引：可以使用 SHOW INDEXES 命令查看一个表的所有索引信息

SHOW INDEXES FROM my_table;

4.4 在查询中使用索引：

• 当我们在 WHERE 子句中使用到索引列时，MySQL会自动使用对应的索引进行查询优化

SELECT * FROM my_table WHERE lastname = 'Smith';

需要注意的是，虽然索引可以提高查询性能，但是并不是所有列都需要创建索引，且索引的创建和维护也是有成本的。在实际使用中，需要根据具体情况进行判断和选择。 

这期内容到这,我们下期见!!!