MySQL之索引与事务

一、索引

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

索引主要的目的是为了加快查找速度

作用

1、数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。

2、索引所起的作用类似书籍目录，可用于快速定位、检索数据。

3、索引对于提高数据库的性能有很大的帮助。

使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

1、数据量较大，且经常对这些列进行条件查询。

2、该数据库表的插入操作，及对这些列的修改操作频率较低。

3、索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

使用

创建主键约束（ PRIMARY KEY ）、唯一约束（ UNIQUE ）、外键约束（ FOREIGN KEY）时，会自动创建对应列的索引。

（关于约束请看 MySQL之各种约束类型及其用法（简洁满满干货）-CSDN博客）

查看索引

show index from 表名;

创建索引

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

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

删除索引

drop index 索引名 on 表名;

其他

1、索引保存的数据结构主要为B+树

MySQL默认引擎是 InnoDB 存储引擎 默认的索引结构就只是B+树。

（但是Memory(heap)引擎会有Has索引）

1.作为一个 N 又搜索树, 高度降低下来, 比较的时候,硬盘 IO 次数就比较少了.(同 B 树)

增加查找效率本质就是减少磁盘 IO 次数
2.更适合进行范围查询.
3.所有的査询,都是要落在叶子节点上的,无论查询哪个元素,中间比较的次数差不多查询操作比较均衡.

2、（索引增加了空间的开销，构造索引需要额外的硬盘空间来保存）

3、创建索引, 最好是在表创建之初就把索引给搞好~-否则,如果是针对一个表中已经有很多很多记录的表,来创建索引,也是一个危险操作!!!这个时候,就会吃掉大量的磁盘 IO, 花很长的时间.(可能是几十分钟到几个小时,看数据量)在这段时间里,数据库是无法被正常使用的

4、把索引创建好了之后,不需要手动使用，直接査询的时候就会自动的来走索引.执行引擎会自动评估,哪种方案是成本最低,速度最快的

为什么索引用B+树这个数据结构

B+ 树 就是为了索引这个场景,量身定做的数据结构

为啥不用哈希表和二叉搜索树呢

1、哈希表不适合做数据库的索引,哈希表只能比较相等. 无法进行 < > 这样的范围査询!!!
很明显,数据库经常要范围查询!!

2、二叉搜索树二又意味着当元素个数多了的时候, 树的高度就会比较高,树的高度就决定了查询时候,元素的比较次数。数据库进行比较都是要读硬盘的

N 叉搜索树~~ 每个节点上有多个值,同时有多个分叉.树的高度就降低了~~
其中的一种典型实现,叫做 B树比较次数虽然没咋减少(一个节点上可能需要比较多次了)但是读写硬盘的次数减少了.(每个节点都是在硬盘上的).

但是还不够！！！！！！！！又引入了 B+ 树, 是对 B 树进行了进一步的改进,

1. B+ 树也是一个 N 又搜索树,每个节点上可能包含 N 个 key, N 个key 划分出 N 个区间.最后一个 Key 就相当于最大值了

2. 父元素的 key 会在子元素中重复出现, 并且是以最大值的姿态出现的~

（这样的重复出现, 导致叶子节点就包含了所有数据的全集!!!!非叶子节点中的所有值都会在叶子节点中体现出来）

3. 会把叶子节点, 用类似于链表的方式,首尾相连~

4. 由于所有的 key 都会在叶子节点中体只需要把所有的数据行给放到叶子节点。

疑问：

问题1： 创建索引的表是以B+树数据结构来放入硬盘中吗

数据库里看到的这个表,不一定就是按照连续的空间来组织表这样的结构(不一定)

有很大概率是基于 B+树结构来构造的尤其是带有主键的表~~(主键自带索引)

问题2：那么大概率是基于B+树构建的话，查询一个范围不应该自动有序吗？？

这是无法保证的，可能是这样的树形结构，也可能不是，MySQL没给这个承诺。

问题3：创建索引的表是以B+树数据结构存入硬盘吗

是的，对于带有主键的表,就是按照主键索引的 B+树来组织的~

二、事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向

系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元

事务必须具备以下四个属性，简称 ACID 属性：

*原子性（Atomicity***）

1. 事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

*一致性（Consistency***）

2. 当事务完成时，数据必须处于一致状态。

*隔离性（Isolation***）

3. 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

*持久性（Durability***）

4. 事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

把数据存储在硬盘上（存在内存中就不是持久的）

数据库事务隔离级别中的三个常见问题：

脏读、不可重复读和幻读。

脏读

概念： 一个事务读取了另一个事务尚未提交的数据。
作用： 如果事务A读取了事务B的未提交数据，而B后来回滚，那么A读到的数据就是"脏数据"，不准确且不稳定。
也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据
解决脏读问题的核心思路:降低事务的并发程度,写程序加锁,加锁就意味着释放锁之前是不可访问的。

幻读

概念： 一个事务在多次查询时，得到了不同数量的符合条件的记录，这是由于其他事务插入、删除了符合条件的记录导致的。
作用： 可能导致事务在处理一系列数据时，由于其他事务的插入或删除操作而得到不一致的结果。
也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。
解决: 串行化.即不再进行任何并发,每个事务是串行执行的(执行第1个,然后是2,3个).

不可重复读

概念： 一个事务在多次读取同一数据时，得到的结果不一致，即中间可能有其他事务修改了这个数据。
作用： 可能导致事务在处理相同数据时，由于其他事务的修改而得到不一致的结果。
也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

不可重复读和幻读比较：

两者有些相似，但是前者针对的是update或delete，后者针对的insert。