数据库MySql--day8 索引+事务

索引

1.以搜索数据结构（通常是B+树）组织起来的一类数据，保存在硬盘上

2.提升查找效率，增加了修改成本，带来空间占用

3.适合索引的场景：数据量有一定规模，并且读多写少的业务

简介B+树

B+树，是一种多叉平衡搜索树，树的一个节点允许有多个孩子

b+树的核心是结点分裂，并且维护着key的有序性

以插入为例，若其结点最多能保存4个key，那么当保存的key>4，那么就会引起结点的分裂，实际应用中这个最多保存key个数很大，一般为1000-2000这个级别

我们随机给出一些key值： 5  7  9   19   12   362     36     148   123   89   65  33   94  145  998  11  

一开始节点中只能保存4个key ，且按升序排列， 那么即为：

此时 4个key将整体数据分为了5块，分别为负无穷到5，5到7，7到9，9到19，19到正无穷

当保存第五个key12时，破坏了最大key的规则，导致该结点进行分裂，加入后变为

此时中间那个key分裂到父节点中，变为

 

 而新插入key时，必须先插入叶子节点中

 接下来的key362就要插入在19的后面，再判断是否>4了，以此类推。

 扩展：为什么数据库使用B-树而不是用红黑树AVL树？

因为针对N的key，B-树的高度小于二叉平衡搜索树，为什么高度低就好用呢？因为硬盘的存储特性为顺序IO的速度高于随机IO的速度。

索引的hit与miss问题

该问题属于数据库优化下的一个小问题

我们假如有个表如下，并且索引做出：

 1. select * from 表 where 姓名 = ‘....’;    大概率会用上idx_name索引

     select * from 表 where 生日 = ‘....’;     用不上索引

     select * from 表 where id = ‘....’;   主键索引

我们知道，使用索引的时候，只有针对指定的字段作为条件才能用上。

2.如果为字符串，假设按字符的大小比较

select * from 表 where 姓名 > ‘张三’; 

select * from 表 where 姓名 = ‘张三’; 、

select * from 表 where 姓名 between ‘张三’ and ‘李四’; 

以上都可能用上索引

select * from 表 where 姓名 != ‘张三’;   

不一定用得上索引，因为你要判断不是张三  你需要全部判断一遍，次数差不多

3.好的索引应该带来数据的区分度，如果一个索引为性别，内容为男/女，那么区分度就很低，

select * from 表 where 性别 = ‘男’，大概率就用不上，因为索引并不会带来很大提升

4. order by 也会影响mysql会不会使用索引

5.聚簇索引与二级索引

这是Mysql  InnoDB下专有的概念，因为不同的引擎其存储数据的方式不同

通过一个B+树来保存数据记录本身，key就是主键，value就是整条记录，这就是聚簇索引，简单来说主键索引就是聚簇索引。

普通索引也就是二级索引，也由B+树维护，key对应其value

6.最左匹配原则

当我们搜索的where条件中内容靠左，则大概率可以用上索引，但为中间或靠后，那么肯定用不上，因为有序性的原因，只可能判断最左边的内容，不可能判断的了右边以及中间的内容。

select * from 表 where 姓名 = ‘aa’;

select * from 表 where 姓名 like  ‘aa%’;  

大概率用的上

select * from 表 where 姓名 like ‘%aa%’; 

select * from 表 where 姓名 like ‘%aa’;

肯定用不上 

扩展复合索引：两个或两个以上的字段组合起来作为索引的key

得出结论：我们只能熟悉这些规则尽量让sql影响mysql去使用索引，但具体用不用，用哪个我们无法决定。我们可以使用explain命令 得到MySQL 的执行计划，进而分析我们的sql是否值得优化。

事务（Transaction）

数据库场景下，事务可以分为隐式事务和显式事务

显示事务

我们知道完成一个业务动作需要多条SQL一起完成，例如借书，无论结果成功与否，其都包含几条SQL来完成这个动作，其sql组成的就是借书这个整体。

那么我们怎么让RDBMS知道哪些SQL是一个整体呢？

我们通过分界线来告诉：业务开头用begin，业务结束用commit（成功后提交）或者rollback（手动控制失败，回滚）begin commit  和rollback只是类似标签并不产生实际效果

而讨论事务之前，我们要讨论其数据一致性问题。RDBMS事务有四大特性：一致性，原子性，持久性，隔离性。后三者为一致性服务。

通俗的来说，数据的一致性问题类似于对账，例如我有100块，你有200块，你借我50 ，那么结果就为我150 你150，而不能你借了我50我没收到，我是100，你变成150了，这样数据就不一致了，在完成借钱这个动作之后，总体的钱是不能变的，还要为300.

而针对不同的业务，就有不同的约束来保证数据一致性，上个例子中的约束就为总体的钱不能变。约束一旦破坏，数据就等于被损坏了。

而为了满足数据的一致性，我们有以下三个方面

1.原子性：整个事务应该看作一个整体不可再分，要么sql全部成功，要么sql全部失败！

2.持久性：如果RDBMS在业务方发送sql执行后成功，那么反馈为成功后就为持久下来了，一旦发生故障或者没有生效，即为持久性被破坏

3.隔离性：将各个业务隔离，彼此之间不产生任何影响，但是实际上不可能这么做。

而因为原子性的缘故，除了手动回滚rollback以外，若在commit之前产生故障等也会被动回滚。

此处详细讨论隔离性的问题：若要做到完全的隔离性，只能串行处理数据，性能非常差，因此在SQL  92 标准提供了四种隔离性标准，来供使用需求选择：

A.读未提交：一个事务中可以看到其他事务中未提交的内容，类似于看到草稿箱的内容，这个现象叫脏读，因为未提交的内容是可以修改回滚的。

B.读已提交：一个事务中可以看到其他事务中已提交的内容，但会出现不可重复读的现象：

C.可重复读：通过一定机制来规避了不可重复读现象，但在以前会存在幻读化现象，现在已经很少存在了

D.串行化：一个一个事务进行处理

A—>D  从并发高/隔离差 到并发低/隔离好

隐式事务

其实隐式事务就是每一条sql执行完都会自动提交，我们可以关闭这条指令让其不自动提交。

事务小结

事务：一个业务动作，可能为一条SQL组成（隐式事务），也可能为多条SQL组成（显式事务）

四大特性：一致性    原子性 持久性 隔离性 后三者为RDBMS职责，一致性需要开发员与RDBMS一起维护