MySQL索引事务

---

---

一、索引

1.1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
索引就相当于一本书的目录，根据目录中每个章节对应的页码，就能快速的找到对应的章节。MySQL的索引也是一样的，当从数据库中进行查找的时候,例如按照一定的条件来查找。

1.2 作用

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

查找可以遍历表，但是遍历表操作,比较低效，就需要想办法尽量的避免遍历，可以通过一些特殊的数据结构,来表示一些记录的特征,通过这些特征来减少比较次数,加快比较的效率。
索引主要的意义就是进行查找要提高查找的效率。查找效率是提高了,但是同时也会付出一些代价。
比如：书的目录也是费纸的，数据库的索引,也是需要消耗一定的额外存储空间的，数据量越大，索引消耗的额外空间就越多。书的目录如果确定了，后续每次对书的内容调整,都可能会影响到目录的准确性,就需要重新调整目录；数据库的索引也是一样，当进行增删改的时候，往往也需要同步的调整索引的结构。

索引带来的好处：提高了查找的速度.
索引带来的坏处：占用了更多的空间,拖慢了增删改的速度.
在实际需求场景中，查找往往是最高频的操作。

有了索引之后,对于查找的效率的提升是非常大的，当mysql里面的数据量级达到千万级别的时候(一个表里有几千万,上亿的数据)再去遍历表,就会非常非常低效。
这个查找是纯内存中的比较，mysql中的比较是在硬盘上的，硬盘IO的速度比内存的速度慢3-4个数量级。

1.3 和索引相关的sql

1.3.1 查看索引

show index from 表名；

直接查看student表,发现里面其实已经自带了一个索引，这个自带的索引就是primary key这个主键约束带来的，查询的时候,如果查询条件指定了根据主键查询,这个时候查询速度就会非常快。
unique 也是要带索引。

1.3.2 创建索引

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

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

创建索引这件事情是一个非常低效的事情,尤其是当前表里面已经有很多数据的时候,后面针对线上的数据库,如果这个表没有索引,不要贸然去创建索引。

1.3.3 删除索引

drop index 索引名 on 表名;

创建表的过程中，就应该把索引规划好，以后在工作的过程中，一定要谨慎使用索引。

为什么顺序表按照下标访问的速度就快呢?
顺序表是在连续内存空间上，内存支持随机访问操作(访问任意地址上的数据,速度都是极快的)。

1.4 索引背后的数据结构

索引中得数据结构需要加快查找的速度。
对于以前学过的数据结构：
AVL:要求任意节点左右子树高度差不超过1。
红黑树:要求更宽松的平衡二叉树。
树的高度太高，导致IO访问此时更多。
二叉树：最大的问题,就是当元素多了的时候,高度就高了(高度就对应着比较次数)，对于数据库来说,每次比较都意味着磁盘IO。
哈希表：虽然哈希表查找速度很快O(1)，但哈希表只能针对“相等"进行判定,不能对“大于小于"，以及范围查找进行判定。
堆更不适合作为索引，堆只能找最大/最小。
上述数据结构都不适合用作索引。最适合做索引的,还得是树形结构,只不过就不再是二叉树了，如果我们使用"多叉搜索树”，高度自然就下降了。
在数据库中使用的这个多叉搜索树,又不太一样，是一个很特殊的树称为B+树(这个是数据库索引中最常见的数据结构)。
要想理解B＋树,需要先理解它的前身,B树(有的资料上也写作B-树，它是B树的另外一个写法，而不是B减树)。

数据库有很多种,每个数据库底层又支持多种存储引擎(实现了数据具体按照啥结构来存储的程序)，每个存储引擎存储数据的结构可能都不一样,背后的索引数据结构可能也不同。

B树（B-树）

B树的每个节点上,都会存储N个key 值，N个key 值就划分出了N＋1个区间，每个区间都对应到一个子树。

在B树中查找元素,过程就和二叉搜索树非常相似：先从根节点出发，根据待比较的元素,确定一个区间。
在确定区间的时候,不也是多次比较嘛，这里比较多次和二叉搜索树相比,优势体现在哪里呢?
二叉搜索树,每个节点比一次，比较的次数是和高度相关。但是B树,高度是少了,但是每个节点比较多次了。相比于比较次数来说, IO次数是更关键的!即减少了IO的比较次数。
是以节点为单位进行磁盘IO的。

B树只是B+树的前身，B+树相比于B树又做出了一些改进：

B+树

B＋树也是一个N叉搜索树，每个节点上都包含多个key值，每个节点如果有N个key，就分成了N个区间。
父节点的值,都会在子节点中体现.（非叶子节点中的每个值,最终都会在叶子节点中体现出来.）
父节点中的值,会作为子节点中的最大值(最小值)，下面这个图画的是最大值的情况。
最下面的叶子节点,就使用链表进行按顺序连接。

B+树就是为了数据库索引量身打造的：
1.使用B＋树进行查找的时候,整体的IO次数也是比较少
2.所有的查询最终都会落到叶子节点上,每次查询的IO次数都是差不多,查询速度稳定。
3.叶子结点用链表连接之后，非常适合进行范围查找；例如，要找到>= 5<=11的值。
4.所有的数据存储(载荷)都是放到叶子节点上的,非叶子节点中只保存 key值可，因此非叶子节点整体占用的空间较小，甚至可以缓存到内存中（一旦能够全放内存里,这个时候,磁盘IO几乎就没了）。

二、事务

2.1 为什么使用事务

事务是要把多个SQL打包成一个，要么全部执行成功，要么全部执行失败。
事务的应用场景：典型的就是转账。
事务诞生的目的就是为了把若干个独立的操作给打包成一个整体。在SQL中,有的复杂的任务需要多个SQL来执行，这时,也同样需要打包在一起；前一个SQL是为了给后一个SQL提供支持，如果后一个SQL不执行了或者执行出问题了,前一个SQL也就失去意义了。

原子性:要么全都执行完，要么一个都不执行，任务不可以被再细分了。

这里举一个例子：
比如A向B转账500：

账户表(name,balance)
		A    1000
		B    1000

A这个账户的余额减500

update 账户表 set balance = balance - 500 where name = 'A';

B这个账户接收500：

update 账户表 set balance = balance + 500 where name = 'B';

如果在转账过程中，出现问题，考虑一种极端情况,执行完第一个SQL之后，在执行第二个SQL之前,数据库崩了/程序崩了/机器崩了/机器断电.……等。这样就导致账户A 少了500元，而账户B并没有收到这500元；显然,这种中间状态是不科学的，事务的原子性,就能避免出现这种中间状态。

事物的原子性是无法预知失败的，该执行还是得执行，当出现执行失败之后，由数据库自动执行一些"还原"性的工作，来消除前面的SQL带来的影响，这就是回滚(rollback)。回滚的依据就是mysql要记录之前执行的一些过程。

这样当遇到上面例子中所述的情况，数据库就会还原之前操作，也就是把之前改动过的数据给还原回去(上个操作是-，现在就是＋)。

2.2 事务的四个基本特性

原子性

事务要么全部执行成功，要么一个都不执行。（事务的核心）

一致性

在事务执行之前,和执行之后,数据库中的数据都得是合理合法的。
例如，你转账完了之后,不能够出现这种账户为负数的情况。

持久性

事务一旦提交了之后,数据就持久化存储起来了。数据就写入到硬盘了。

隔离性

隔离性描述的是事务并发执行的时候，产生的情况。

当并发执行多个事务,尤其是这多个事务在尝试修改/读取同一份数据，这个时候就容易出现一些问题，事务的隔离性,就是在解决上述问题。

并发执行事务可能带来的问题

1. 脏读问题

事务A在对某个数据进行修改，修改的同时，事务B去读取了这个数据，此时事务B读到的很可能是一个"脏数据"(这个数据是一个临时的结果,而不是最终的结果)。
处理脏读:给写操作加锁
在修改的过程中,别人不能读了．(加锁的状态)
等修改完了之后,别人才能读. (接触加锁)
给事务加锁之后，意味着事务的隔离性就高了，并发性就降低了。

2. 不可重复读问题

在上面的约定中：给写操作加锁，写的过程中不能读，但是没有约定读的过程中不能写。
就可能出现下面的情况：导致读到的东西出现两个版本，这时候就应该将读操作也以以约定：给读操作也加锁。

下图展示的是只给写操作加锁：在写的过程中，别人不能读。

下图展示的是给写操作加锁，给读操作也加锁：

此时，事务的并发性又降低了，隔离性提高了。

3. 幻读问题

事务虽然在提高隔离性的时候要进行一系列加锁,但是这个锁也不是把整个数据库都给锁定了,还可以改其他的表,甚至说改这个表的其他的行。
如下图所示：

在上图所示的事务执行的过程中，本来只读到A.java,在读的过程中又出现了B.java，这就是幻读问题：一个事务执行过程中进行多次查询,多次查询的结果集不一样(多了一条或者少了一条，上述示例就是读多了)这个操作算是一种特殊的不可重复读。

解决幻读问题：那就彻底串行化执行。此时，隔离性最高,并发程度最低，数据最可靠,速度最慢。

并发(快)和隔离(准)是不能兼得的，这样就可以根据实际需要来调整数据库的隔离级别，通过不同的隔离级别，也就控制了事务之间的隔离性,也就控制了并发程度。

MySQL中事务的隔离级别

MySQL中事务的隔离级别,提供了这么几种:

1. read uncommitted:允许读取未提交的数据，并发程度最高，隔离程度最低，会引入脏读+不可重复读+幻读问题。
2. read committed:只允许读取提交之后的数据. 相当于写加锁，并发程度降低了一些,隔离程度提高了一些，解决了脏读,会引入不可重复读＋幻读.
3. repeatable read:相当于给读和写都加锁.并发程度又降低了.隔离程度又提高了.解决了脏读和不可重复度,会引入幻读。
   4.serializable :串行化，并发程度最低(串行执行)隔离程度最高.解决了脏读,不可重复度,幻读问题,但是执行速度最慢。

---

以上。