MySQL索引事务及高频面试题

索引

概念

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

索引相等于一本书的目录!!
如果我们需要查找一本书中指定内容!我们先是需要找目录,然后通过目录精准定位到你需要查找的内容!!
显然 目录提高了查找效率!
索引也可以提高我们数据库的查找效率!!!
当想显然一本书如果需要目录那么就需要更多的纸!
而索引便需要更多的内存!!!

作用

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

所以索引对我们数据库的查找效率提高很大!!!

使用场景

• 数据量较大,且经常对这些列进行条件查询.

我们什么时候翻一本书的内容需要先翻目录然后再查找呢?
显然那是因为书的内容比较多!书比较厚!!
如果书很薄,那我们其实可以直接查找内容即可!!!
而数据库中的索引也一样!!
如果数据库表中的字段较少!!那么添加索引只会浪费空间!!
当我们数据量较大时,添加索引可以很好的提高查找效率!!!

• 该数据库表中的插入操作,修改操作比较低.

如果作家斌没有将一本书编辑好,还需要大量更改,那么他肯定不会现将目录创建出来!不然后期要频繁更改目录,甚是麻烦! 如果我们的数据库表,修改和插入频率较低,那么索引表便很好的提高了效率!!!

• 索引会占用额外的磁盘空间!

使用

我们该如何使用索引表呢?
如何给我们的数据库表创建一个索引呢?

创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时，会自动创建对应列的索引。

• 查看索引:
  show index from 表名;
  
• 创建索引表:

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

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

案例：创建班级表中，name字段的索引!
可以看到 我们成功创建了名为indexname的索引!!!

• 删除索引
  drop index 索引名 on 表名;
  我们将刚刚class_7表中的indexname索引删除!
  

索引背后的数据结构

我们想想我们学过的数据结构,啥结构的查找效率高呢,可能大家首先想到的是顺序表!!
显然顺序表,如果按照下标查找效率高!!但是我们数据库中的查找并不是按照下标查找!
这里的查找是按照值查找!!!
值查找那不就是树形结构查找快吗,所以我们索引用的数据结构就是树形结构!
采用多叉树可以减低高度,提高查找效率!!!

B+树

啥是B+树呢?
那么我们不得不介绍一下B树(B-树)这里并不是B减树!!这里只是B树的另外一种写法!

可以看到B树,结构简单,就是一个节点,有多个key值 多个key值,分成n+1个开区间!
我们通过区间逐步查找,直到根节点即可查到我们需要查找的值!!

B+树

• B+就是为了索引而量身打造的!
• 使用B+查找整体的IO次数大大减少,并且查询稳定,因为树的平衡高,并且每个叶子节点都和其他叶子节点连接!!
• 叶子节点用链表连接后,非常适合范围查找!!!
• 所有的数据都保存在叶子节点!!!非叶子节点保存key值即可,占用空间小,甚至可以直接缓存在内存中!!!

事务

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end ransaction语句（或函数调用）来界定。事务由事务开始(begintransaction)和事务结束(end transaction)之间执行的全体操作组成。(百度)

简单讲事务就是MySQL多条操作封装在一起,成为一个事务!!!

也就是多条sql语句就可以组成一个事务!

为什么使用事务

有些MySQL操作,不得不需要多条sql才能执行成功!

比如我们需要转账操作!
显然这样的操作,在mysql需要分两步执行!!!

假设:阿里巴巴需要给四十大盗转账2000元!!

第一步:
阿里巴巴账户余额减少2000!

第二步:
四十大盗账户余额增加2000

如果我们的第一步操作就失败了!显然此时转账失败了!
我们第二步的操作就没有意义了!
所以我们需要将这两个操作打包成一个事务!
如果sql_1执行成功了,那么再执行sql_2!

那如果sql_1执行失败了咋整呢?
放心,我们的mysql数据库有专门的表记录下了sql_1操作,数据库系统会自动将该操作回滚即复原!!!

事务特性:

四大特性:

• 原子性
  因为我们通常认为原子是不可再分的,这里就是认为事务是最小单元了!
• 一致性
  就刚刚的转账操作,账户1和账户2中的余额总数要和转账前相同,不能无故增加或减少!!!
• 隔离性
  多个事务并发之间是隔离的!
  就是当,双方转账的同时,另一方再转账给其中一人,那么此时就不可执行!
• 持久性
  执行该条事务后,如果不进行二次更改,那么该数据就持久有效了!

使用

如何设置执行一个事务呢?

• 开启事务
  sql语句
start transaction;
• 执行多条sql
• 回滚或提交:
  sql语句
rollback/commit;
rollback即是全部失败，commit即是全部成功。
  
  commit事务提交成功!!
  
  rollback撤销该事务

并发执行事务可能带来的问题(面试)

我们知道事务有隔离性的特点!!!
我们知道隔离性越好,那么事务之间的并发性就低!!!

什么是并发执行呢?
我们知道我们的计算机可以同时开启多个进程(程序)!
我们可以听着歌,打开IDEA,打开CSDN写博客!!!
我们计算机是很擅长一心多用!!多个进程并发执行!!!
而如今的计算机CPU也是取向多核发展!!
就是同时并发执行的程序可以更多!!

而事务间也有并发!

联系我们生活,很多事情我们可以同时并发执行!但是有些事情需要先后顺序执行,不然会很多问题!!
事务并发也是如此,有可能出现一些问题!

• 脏读问题

什么是脏读呢?
顾名思义就是读了一个脏数据!就是错误的数据!!

什么情况下会出现脏读呢?

比如高中,考了考试,你正在订正答案,学委在黑板上抄答案,你在你的试卷上改!他写,你抄.你抄完后,发现学委把答案改了,他刚刚抄错答案了! 你刚刚读的答案就是一个脏数据!!!

我们画一个时间轴便于理解!!!
解决方案:
我们可以对写操作进行加锁!!!便可以处理脏读问题!!!

就是说我们等学委将答案全部抄完! 我们再进行读答案!!!

• 不可重复读

当我们学委写完答案,你开始抄答案不久时间后,老师和他说有一个答案有更好的解法!让学委重新写一份!可这时你已经在抄了好多了!你就一整个大无语!!!

解决方案:
给读操作加锁!

我们和学委商量,他写答案的时候我们不看!我们抄答案的时候他就不可以改了!!!

• 幻读问题

当学委写完数学答案后,我们再看数学答案时,他又在写英文答案,此时你就看岔行了!把英文答案看成了数学答案抄了,出现了幻读问题!!!

解决方案:
再次和学委商量,咱抄答案时他不能写!!!我们抄时他不能写!!!

使读和写操作直接串行化了!!!

显然此时的隔离效果最后!! 但是并发最慢!!!

隔离性和并发两者需要平衡!!!