MySQL数据库-----索引和事务_解决不可重复度-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_37453637/article/details/115838961

索引

1.概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

2 .作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

3.使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

4.使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
（1）查看索引

show index from 表名;

（2）创建索引
对于非主键、非唯一约束、非外键的字段，可以创建普通索引。

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

（3）删除索引

drop index 索引名 on 表名;

事务

实例：

drop table if exists accout;
create table accout(
 id int primary key auto_increment,
 name varchar(20) comment '账户名称',
 money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元。

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是四十大盗的账户上就没有了增加的金额。

解决方案： 使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

1.概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。也就是说，事务就是把若干个SQL语句打包成一个整体，要么全部执行完成，要么一个都不执行，哪怕执行到一半数据库操作出现了问题，导致系统崩坏，数据库也能把已经改动的数据还原成最初的状态，从用户角度看起来这些操作好像就是没有执行的一样。

2.使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;
说明：rollback即是全部失败，commit即是全部成功。

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

3.事务的特性

原子性( atomicity)
一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。以转账的场景为例，一个账户的余额减少，对应一个账户的增加，这两个一定是同时成功或者同时失败的。全部成功比较简单，问题是如果前面一个操作已经成功了，后面的操作失败了，怎么让它全部失败呢？这个时候我们必须要回滚。
隔离性( isolation)
多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性( durability)
一且事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失。
一致性( consistency)
数据库总是从一个一致性的状态转换到另外一个一致性的状态，具体是指数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。除了数据库自身的完整性约束，还有一个是用户自定义的完整性。比如说转账的这个场景，A账户余额减1000，B账户余额只增加了500，这个时候因为两个操作都成功了，按照我们对原子性的定义，它是满足原子性的，但是它没有满足一致性，因为它导致了账户的不平衡。还有一种情况，A 账户余额为 0，如果这个时候转账成功了，A 账户的余额会变成-1000，虽然它满足了原子性的，但是我们知道，借记卡的余额是不能够小于0的，所以也违反了一致性。用户自定义的完整性通常要在代码中控制。

==> 原子性，隔离性，持久性，最后都是为了实现一致性。

MySQl并发执行事务可能产生的问题

1.脏读（Dirty Reads）
一个事务A在修改数据，另一个事务B就直接读取A正在修改的数据，此时B读到的就是一个脏数据。因为此时B读到的数据很可能在A的后续操作中又被进行了修改，相当于B读到的数据只是一个“中间状态”，读到的这个数据就是“脏数据”，这个动作就是“脏读”。

如何解决脏读问题？
给写操作加锁~~

2.不可重复度（Non-Repeatable Reads）
事务A修改数据后提交，提交完成后，数据B开始读取数据，事务B中包含很多次读操作，如果事务B两次读取操作得到的结果不相同，这就是不可重复度。出现这样的原因是，事务A虽然把数据提交了但是有可能又执行了一次事务A，把这个数据又进行了修改，事务B第一次读操作读到的是事务A第一次提交后的数据，事务B第二次读操作读到的是事务A第二次提交后的数据，导致事务B这两次读操作的数据不一样。

如何解决不可重复度问题？
给读操作加锁~~

3.幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。事务B正在读取数据，为了避免出现脏读和不可重复度问题，我们给数据的读操作和写操作进行加锁，此时事务A对事务B正在操作的数据无法进行其他操作，但是事务A可以插入/删除其他数据，那么此时事务B操作所面对的整体的数据集就发生了变化。

如何解决并发处理事务带来的问题？

MySQL 数据库是通过事务隔离级别来解决上述问题的。

第一个隔离级别：Read Uncommitted（未提交读）
一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做RU，它没有解决任何的问题。

第二个隔离级别：Read Committed（已提交读）
一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

第三个隔离级别：Repeatable Read (可重复读)
它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

第四个隔离级别：Serializable（串行化）
在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

各个隔离级别下是否可能出现事务并发执行所产生的问题，如下图：
在这里插入图片描述
如何设置事务隔离级别？

可以通过以下命令设置事务隔离级别：

set session transaction isolation level read uncommitted  -- 读未提交
set session transaction isolation level read committed    -- 读已提交
set session transaction isolation level repeatalbe read   -- 可重复读
set session transaction isolation level serializable      -- 串行化