MySQL 索引事务

1. 索引

1.1 什么是索引

数据库使用 select 查询的时候

1. 先遍历表
2. 把当前的行给带到条件中，看条件是否成立
3. 条件成立，这样的行就保留，不成立就 跳过

如果表非常大，这样的遍历成本就比较高了
至少是 O(N)
数据库把数据存储在硬盘上，每次读取一个数据，都需要读取硬盘，这个开销本身是很大的

索引 属于针对 查询操作 引入的优化手段，可以通过索引来加快查询的速度，避免针对表进行遍历

索引是能提高查询速度，但是也有代价

1. 占用更多的空间，生成索引是需要一系列的数据结构，以及一系列的额外的数据，来存储到硬盘空间中
2. 但是可能会降低插入修改删除的速度

1.2 索引的相关操作

1.2.1 查看索引

show index from 表名；

一个索引是针对一个列来指定的
只有针对这一列进行条件查询的时候，查询速度才能够被索引优化

1.2.2 创建索引

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

创建索引操作，也是一个危险操作
创建索引的时候，需要针对现有的数据，进行大规模的重新整理

如果当前表是一个空表，或者数据不多，创建索引没有问题
如果这个表本来就很大，创建索引，也很容易把数据库给卡住

一般来说，创建索引，都是在创建表的时候就规划好的

1.2.3 删除索引

drop index 索引名 on 表名;

手动创建的索引，可以手动删除
如果是自动创建的所有（主键/外键，unique），是不能删除的

注意：
主键，unique，外键，都会自动生成索引

1.3 索引的底层逻辑

索引也是通过一定的数据结构来实现的

二叉搜索树（一个普通的二叉搜索树，时间复杂度是O(N)，以内会纯在不平衡的极端情况(单枝树)，如果引入平衡机制(AVL树，红黑树) 可以达到O(logN)）
哈希表 O(1)
以上两个都可以进行查询，但是这两个数据结构都不适合数据库作为索引

对于哈希表来说，只能进行“精准匹配”，无法进行范围查询，更不能进行模糊匹配

对于红黑树来说，是可以精准匹配，也能范围查询，也能模糊匹配
红黑树，也是二叉树，每一个节点，最多两个子树，树的分叉少。此时，表示同样数量的结果集合，树的高度就会更高

如果 数量特别多，那么时间复杂度也会很大

这里我们数据库中引入的索引是一个改进的树型结构 B+树（N 叉搜索树）

为了了解 B+ 树，我们先了解 B 树（B- 树）

每个节点的度都是不确定的
一个节点上保存 N 个 key，就划分出了 N+1 个区间
每个区间都可以衍生出一系列的子树了
由于每个节点是在一个硬盘的区域中的
一次读硬盘就读取出了整个节点（多个 key）
在进行几次比较（赌一次硬盘，相当于 1w 次比较）

这样树的高度就大幅度的降低了

但是一个节点中，虽然是可以保存 N 个 key 的
但也不是无限制的，达到一定的规模，就会触发节点的拆分
当删除元素达到一定的数目，也会触发节点的合并

B+ 树相对于 B 树，又做出了一定的改进，是针对数据库量身定做的

1. B+ 树也是一个 N 叉搜索树，一个节点上存在 N 个 key，可以划分出 N 个区间
2. 每个节点上 N 个 key 中，最后一个，就相当于当前子树的最大值
3. 父节点上的每个 key 都会以最大值的身份在子节点的对应区间中存在（key 可能会重复出现）
   这就使叶子结点这一层，包含了整个树的数据全集
4. B+ 树就会使用 链表 这样的结构，把叶子节点串起来
   此时就可以非常方便的完成数据集合的遍历
   并且也很方便的从数据集合中按照范围取出一个“子集”

B+ 树的优点：

1. N 叉搜索树，树的高度是有限的，降低 IO 的次数
2. 非常擅长范围查询
3. 所有查询最终都是要落到叶子节点，查询和查询之间的是加开销是稳定的
   不会出现这次特别快，下次特别慢的情况
4. 由于叶子节点是全集，会把行数据只存储在叶子节点上，非叶子节点只是存储一个用来排序的 key（比如存个 id）
   数据库是按行组织数据的
   创建索引的时候，是针对这一列进行创建的

上述这是一个比较通用的，比较典型的 B+ 树
具体的数据库，还有一些不同程度的优化

Mysql 索引实现，也是有一些变数的，不是只有 B+ 树这一种情况
MySQL 内部有一个模块，存储引擎
存储引擎是提供了很多版本实现的
lnnodb 当前最常用的 MySQL 存储引擎就是 B+ 树

2. 事务

2.1 什么是事务

在开发中经常会涉及到一些场景，需要“一气呵成”的完成一些操作

比如，在进行转账操作时

如果执行到一半，程序崩溃/数据库崩溃/机器断电，这个时候就会出现问题

这时引入事务就是为了避免上述问题
事务就可以把多个 sql 打包成一个整体
可以保证这些 sql 要么全部执行正确，要么“一个都不执行”

事务把这多个 sql 打包到一起，作为一个整体来执行，这样的特点，称为“原子性”

2.2 事务的操作

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

单独执行的每个 sql，都是自成一派的，此时这些 sql 之间是没有原子性的，只有执行事务之后，才具有原子性

到 commit 的时候，事务就结束了

rollback 是主动触发回滚，rollback 一般是要搭配一些条件判断逻辑来使用的
sql 里也能支持 条件，循环，变量，函数（但是日常开发一般不会这么写，跟多的是搭配其他的编程语言）

2.3 事务的回滚

事务的回滚是怎么做到的？

日志的方式，是记录事务中的关键操作。这样的记录就是回滚的依据。
日志是打印出的内容在文件里，即使主机掉电，也不影响（回滚用的日志已经在文件中了）

2.4 事务的特点

原子性
回滚的方式保证这一系列操作，都能执行正确，或者恢复如初

一致性
事务执行之前，和执行之后，数据不能出现错误
很多时候要靠数据库的约束以及一系列的检查机制来完成的

持久性
事务做出的修改，都是在硬盘上持久保存的。重启服务器，数据依然是存在，事务执行的修改依然是有效的

隔离性
隔离性是数据库并发执行多个事务的时候，涉及到的问题
MySQL 是一个 客户端服务器结构的程序

一个服务器可以给多个客户端提供服务，多个客户端都会让数据库执行事务。很有可能多个客户端提交的数据有交叉，数据库就需要同时处理这两个事务，并发执行

并发程度越高，整体的效率就越高
但是如果希望数据库服务器执行效率高，就希望提高并发程度，但是提高了并发程度之后，肯呢个会存在一些问题，就会导致数据出现一些“错误的情况”

隔离级别，就是在“数据正确” 和 “效率” 之间做权衡
往往提升了效率，就会牺牲正确性，提升了正确性就会牺牲效率

但是在并发执行事务的时候，会出现一些问题：

1. 脏读问题
   一个事务 A 正在写数据的工程中，另一个事务 B 读取了同一个数据
   接下来事务 A 又修改了数据，导致 B 之前读到的数据，是一个无效的数据/过时的数据（也称为脏数据）
   解决脏读问题：核心思路是针对写操作加锁
2. 不可重复读
   并发执行事务工程中，如果事务 A 在内部多次读取同一个数据的时候，出现不同的情况，这种就是不可重复读。事务 A 在两次读的之间，有一个事务 B 修改了数据并提交了事务
   解决不可重复读：需要给操作加锁，并发程度降低，隔离性也进一步提高，效率也进一步降低，数据准确性又提高了
3. 幻读
   上述约定了 读加锁 和 写加锁
   一个事务 A 执行过程中，两次的读取操作，数据内容虽然没变，但是结果集变了，这种称为“幻读”
   这种情况是不是问题，具体场景具体分析
   解决幻读：引入串行化的方式，就可以解决幻读，保持绝对的串行执行事务，此时完全没有并发了，从根本上解决了并发中涉及到的各个问题，效率是最低的，数据是最准确的

MySQL 提供了“隔离级别”的概念，可以直接在 mysql 配置文件中，修改数据库的隔离级别

四种隔离级别，对应到上述的三个问题，根据不同的业务场景，修改配置文件，设置不同的隔离级别

read uncommitted（读未提交）：并发程度最高，速度最快，隔离行最低，准确性最低
read committed（读已提交）：引入了写加锁，只能读 写完之后提交的版本。并发程度降低了，速度降低了；隔离性提高了，准确性提高了
repeatable read（可重复读）：引入了读加锁和写加锁，写的时候不能读，读的时候也不能写，并发程度又进一步降低了，速度降低了；隔离性提高了，准确性提高了（默认级别）
serializable（串行化）：严格的按照串行的凡是，一个一个的执行事务。并发程度最低（没有并发），速度最低，隔离性最高，准确性最高