MySQL基础——数据库索引与事务

目录

 

1.索引

2.事务

---

1.索引

1.1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。（比较复杂，我们这里仅作了解）

1.2 作用

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

1.3 使用场景

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

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

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

3.索引会占用额外的磁盘空间。 满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。 反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。 （索引会降低增删改的效率，请谨慎增加索引）

比如我们的主键（primary key）因为会被频繁查询所以是默认添加索引的。 

1.4 使用

我们简单看一下索引的用法：

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

查看索引：

show index from 表名;

创建索引

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

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

删除索引：

drop index 索引名 on 表名;

 1.5 注意事项

 在以下情况查询时不会使用索引，效率较低：

当然以上情况是针对我们的单索引，接下来我们要提到联合索引。

例如索引是key index （a,b,c）。可以支持a | a,b| a,c| a,b,c 组合进行查找，但不支持 b| c| b,c进行查找。就算是你弄乱了顺序如 c,b,a，mysql也会自动帮你改为a,b,c，然后用到索引 。这就是mysql最左匹配原则，查询条件里面要有复合索引最左边的那个字段才会用到索引。当最左侧字段是常量引用时，索引就十分有效。

 而联合索引除了以上十种场景会让其失效外，没有遵循最左匹配原则时也会失效。即你在查询时如果想使用联合索引你必须包含给定字段最左边的索引字段。

1.6 索引实现的数据结构 

索引的实现主要有hash和B+树，由于hash我们已经比较熟悉，使用我们这里只是简单介绍一下B+树索引。以下是一棵简单的B+数示意图：

 当然数据是随便写的，大家了解一下这个结构及特点即可。

特点：

1.每个节点上存了几个值，最多就有几棵子树

2.父节点的元素都存在于子节点中，是子节点上最大/最小值

3.最下方的叶子节点以链表形式连接

B+树的优势：

1.查询速度快，类似于二叉搜索树的查询

2.单个节点存储更多数据，树的高度较低，比较次数少

3.所有叶子节点采用链表连接，便于范围查找（叶子节点包含了数据库的全集）

4.每一个数据行只需要保存在叶子节点，非叶子节点只需要存储用来做索引的id即可。所以非叶子节点占用空间很小，使得将非叶子节点（即使是部分）缓存在内存中成为可能，能够大大降低磁盘IO，提升查询效率

2.事务 

2.1 为什么使用事务

举个很简单的例子，比如你的朋友向你借钱，你答应了，给他转了500，但是由于银行的系统故障，你的账户余额减少了500而你的朋友却没有收到钱

解决方案：使用事务来控制，保证以上操作要么全部执行成功，要么全部执行失败。

2.2 事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。 在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。 

2.3 使用

（1）开启事务：start transaction; 

（2）执行多条SQL语句

（3）回滚或提交：rollback/commit;

start transaction;
-- 张三账户减少2000
update accout set money=money-2000 where name = '张三';
-- 李四账户增加2000
update accout set money=money+2000 where name = '李四';
commit;

说明：rollback即是全部失败，commit即是全部成功。 

 2.4 事务的基本特性

1.原子性：最核心的特性，即逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败

2.一致性：保证数据一致，没有纰漏

3.持久性：只要事务执行成功，造成的修改就是可持久化保存的（保存在磁盘/硬盘中）

4.隔离性：描述多个事务并行执行所发生的情况

这几点我们记住即可，接下来我们详细讲解一下并行执行中可能存在的问题和所谓隔离性。 

2.5 并行执行中可能存在的问题

2.5.1 脏读问题

        从字面意思理解就是数据被污染了，读取到的数据不是准确的数据。一个事务A在执行过程中，对数据进行了一系列修改，在提交到数据库之前（完成事务之前），另一个事务B，读取了对应的数据，而此时B读到的数据都是一些临时的结果，后续可能马上就被A修改了，此时B的读取行为就成为”脏读“。

        你可以简单理解为你的同学抄了你的一份未提交（commit）的作业，而你发现作业有错，此时你将作业重做（类比rollback），而此时你的同学抄作业的行为就是”脏读“。 

而为了解决脏读的问题，我们可以做出约定，在事务A未提交前不能被其他事务读取。而次操作相当于给读操作加锁，降低的并发程度和运行效率，同时增加了隔离性 。

2.5.2 不可重复读问题 

相对于脏读问题，不可重复读问题是指事务A提交后，事务B才开始读（此时读是加锁了的）然后在B的执行过程中，A又开启了一次，修改了数据，此时B执行过程中，两次读取的操作就可能不一致，这种问题我们称为不可重复读。

        比如你在GitHub上正在读人家的项目，但是作者心血来潮开始了对项目的更新，你读着读着刷新了一下突然发现好像数据就已经被改动了，这就称为不可重复读。与脏读的区别在于不可重复读的事务是已经提交后又被修改了。 

 为了解决不可重复读问题，我们可以做出约定，在事务A提交前不能被其他事务读取（之前的加锁）且在事务A被读取时不允许被修改。

2.5.3 幻读问题

        幻度问题其实有点类似于不可重复读的一种特殊情况，不可重复读是一个事务里的两次读操作读到的结果不一样（同一个记录），而幻读是一个事务里的两次读操作读到的结果集不一样。

        类似于我们正在GitHub上阅读别人的项目时，原本只有student.java一个文件，但是作者在我们读的过程中突然增加了一个teacher.java文件，这就导致我们所读到的结果集不同，这就是幻读。

        而解决幻读问题的核心就是”串行化“，说人话就是严格要求当事务A被读取时不能执行任何操作。即一个执行完才能执行下一个。 

2.6 并行与隔离 

我们为什么需要并发，因为效率，而我们为什么需要隔离，因为准确。这两个往往是相悖的。在实际开发场景我们需要根据需求自行选择。看场景对于性能更敏感还是对准确性更敏感。当然在MySQL中也提供了四个档位供我们自行选择。

1.read uncommitted 并发能力最强，隔离性最弱

2.read committed 只能读取提交后的数据，解决了脏读问题

3.repeatable read 针对读和写都做出了限制，解决了不可重复读问题（默认该档位）

4.serializable 严格的串行执行，解决了幻读问题，并发能力最低，隔离性最高