浅谈MySQL数据库的索引与事务

一、索引

1、什么是索引

• MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
• 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
• 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。 运用在表中某个些字段上，但存储时，独立于表之外

2、为什么用索引

①、无索引

Database是一套管理数据的软件，提供便捷的增删查改的方式，MySQL管理的时候， 一个database 其实就是一个文件夹，一个table其实就是一组文件,我们可以粗略的想象一下这个模型，没张表的字段都被存储在一个数组上

select  * from employee;

遍历硬盘上的这个类似数组的结构，把内容输出

select * from employee where id = 11;

每行长度如果固定的话，直接跳过前10行数据，显示第11行数据即可

select * from employee where name =’黄蓉’;

遍历硬盘上的这个类似数组的结构，挨个对比name， 只到找到所有黄蓉”，最后输出结果，时间复杂度是0(n)

缺点1:随着表中数据的变多，0(n)会越来越慢而MySQL中保存的数据数量级，基本都在百万以上
缺点2:数据保存在磁盘上，磁盘的读写速度远远低于内存的读写速度，是差好几个数量级的。0(n)的慢体现的更明显

②、有索引

• 通过对name建立一个索引树，每次在树里面搜索的时间复杂度将大大降低， 找到黄蓉的信息0(log(n))
• 根据上一步找到的信息，直接访问硬盘上黄蓉的完整数据0(1)

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上,平常说的索引，没有特别指明的话，就是B+树

③、优点和缺点

优点：
提高数据检索效率，降低数据库IO成本
降低数据排序的成本，降低CPU的消耗
缺点：

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

④、使用场景

需要

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引
• 查询中排序的字段，排序字段通过索引访问大幅提高排序速度，查询中统计或分组字段
• 数据量足够大时，(超过10W数据 了，就可以考虑了)
• 查询的频次远远大于增/删/改的频次

不需要

• 表记录太少
• 经常增删改的表，频繁更新的字段不适合创建索引（会加重IO负担）
• 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列–建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
• where条件里用不到的字段不创建索引

⑤、为何不采用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。
而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。
哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

⑥、索引的分类

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

3、如何使用

show index from 表名;  --查看学生表已有的索引
create index 索引名 on 表名(字段名);--创建班级表中，name字段的索引
drop index 索引名 on 表名; --删除索引

二、事务

1、什么是事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句
ACID — 事务基本要素
事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。
A (Atomicity) 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
I (Isolation)隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
D (Durability) 持久性：事务处理结束后，对数据的修改就是永久的， 并不会被回滚

2、为什么用事务

小明像小李进行银行转账操作，转钱完成后，突然网络断了，也就是假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，小明的账户会减少2000，但是小李的账户上就没有了增加的金额。
解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

-- 小明账户减少2000
update accout set money=money-2000 where name = '小明';
-- 小李盗账户增加2000
update accout set money=money+2000 where name = '小李';

操作：
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET UTOCOMMIT=0，用来禁止使用当前会话的自动提交。
🔨手动
①开启事务：begin或者start transaction
②执行多条SQL语句
③回滚或提交：rollback/commit;

3、并发事务处理带来的问题

• 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多
• 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】
  例子：事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。与此同时，事务B把张三的工资改为8000，并提交了事务。随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。
• 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
  例子A将db中all学生的score从数字分数改变为ABCDE等级,但是B就在此时插入了一条具体的分数,当A改完后发现还有一条记录没有改过来,就好像发生了幻觉一样.这就叫幻读

简单总结：脏读是不可容忍的，不可重复读和幻读在一定的情况下是可以的【做统计的肯定就不行】

🚩脏读和不可重复读区别
脏读：读取了前一事务未提交的数据 ;
不可重读：读取了前一事务提交的数据；
🚩幻读和不可重复读的区别：
不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。(因为中间有其他事务提交了插入/删除)
都是读取了另一条已经提交的事务（这点与脏读不同）；

👍解决办法：
并发事务处理带来的问题的解决办法：
“脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：

• 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是数据多版本并发控制（ MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

4、事务隔离级别

数据库事务的隔离级别有4种，由低到高分别为

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

read uncommitted 读未提交，就是一个事务可以读取另一个未提交事务的数据。

• 公司发工资了，领导把5000元打到A的账号(正常工资2千)上，但是该事务并未提交，而A正好去查看账户，发现工资已经到账，是5000元整，非常高兴。
  但是领导随后发现给A的工资发多了，于是迅速回滚了事务，修改金额后，将事务提交，最后A实际的工资只有2000元，A空欢喜一场。

🚩剖析：脏读：“事务A：leader给A发工资”，
“事务B：A查询工资账户”，事务B读取了事务A尚 uncommitted 的数据。当隔离级别设置为Read uncommitted时，就可能出现脏读， 那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

Read committed 读提交 顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

• A拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（A事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

🚩剖析：两个并发的事务，“事务A：A消费”，
“事务B：A的老婆网上转账”，事务A事先读取了数据，紧接着事务B更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。那怎么解决可能的不可重复读问题？Repeatable read ！

Repeatable read 重复读 就是在开始读取数据（事务开启）时，不再允许修改操作。MySQL的默认事务隔离级别

• A拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（A事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了

🚩剖析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
❓什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。那怎么解决幻读问题？Serializable！

Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。