MySQL索引事务

一、索引

1.概念

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

2.作用

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

如果想找id为8的学生的信息，没有索引，此时的查找过程就相当于一个“顺序表查找”。
如果是针对顺序表查找，顺序表也是在内存当中，内存访问的速度快，并且数据也没那么多的时候，还可以勉强接受。

如果是针对数据库顺序查找，数据库的数据是在磁盘上，磁盘访问的速度更慢，并且数据量也可能非常多。这样速度就可能会变慢。

索引就是为了避免数据库进行顺序查找，提高查找效率。

3.索引背后的数据结构

3.1 索引可以考虑的数据结构

索引可以考虑的数据结构有两种：1.哈希表 2.二叉搜索树 ，因为它们查找的速度都比较快。哈希表查找的时间复杂度O(1)，二叉搜索树查找的时间复杂度O(logN)。

但是索引用这两种作为数据结构会有些缺点：

例如查找id<6并且>3的学生的信息。select * from student where id<6 and id>3;
哈希表用来作为索引数据结构的缺点：
我们都知道哈希表下的每个结点都是由链表连接起来的，哈希表的查找过程是把key代入哈希函数，计算得到下标，再根据下标取到对应的链表，再去遍历比较key是否相等。因此哈希表只能处理相等的情况，不能处理其它的逻辑(> >= < <= , between…and)等

二叉搜索树用来作为索引数据结构的缺点：
相比于哈希表，二叉搜索树虽然能处理范围查找，但是处理效率不高。
1.二叉搜索数每个结点最多两个叉，当数据量比较大的时候，树的高度就会较高，最终的操作效率也就会越低。
2.二叉搜索树直接获取到中序遍历也不是很高效O(N)

二叉搜索树的其它缺点：

因此：索引的数据结构是B+树，但要了解B+树之前要先了解B树。

3.2 B树

B树也称为B-树（不是B减数），它是基于二叉搜索树来进行优化的。
叶子和非叶子都是由存储数据的，，都要放到磁盘上。

它是一棵N叉树，因此它的高度肯定会比二叉树的高度低，因此查找的效率更快。
它跟二叉树相比最大的差异：
1.每个结点不是2叉，而是n叉。
2.每个结点不是存一个数据了，而是可能存多个。

仔细观察能够发现，每个结点的存的数据的个数和该结点的度是相关的。
度=存的个数+1.

将B树中的一小部分截取下来：

3.3 B+树

B+树的存储结构：

非叶子结点上的数据相当于id，它能够快速找到叶子结点中对应的id，并获取id对应的数据。

和B树相比，主要是两个地方发生了变化：
1.每一层的元素之间都链接到了一起。
2.数据只在叶子结点上保存，非叶子结点上只保存一些辅助查找的边界信息。

B+树更多的优点：

4.索引的分类

索引起到的作用：加快查找效率，减慢插入和删除，修改效率(需要同步调整索引结果)

索引也会占到额外的内存空间（本质上用时间换取空间）

给具体的表中某列加索引的时候，加在主键上的索引（主键索引）和加在其它列上的索引是截然不同的。

主键索引的叶子结点存的是数据的完整记录，其它索引的叶子结点存的是主键的id。
例如：我们在创建表的时候给id加上主键，那主键索引就为id。此时要查找名字为李四的id，因为其它索引是存的id值。因此会先找到李四对应的主键id值，**再根据主键id去主键索引中查找id为1的全部数据，根据主键id在表中查找主键索引id的操作称为回表。**每条数据可能是一条长记录。

5.索引的应用场景

索引的应用场景主要是应用在查找很频繁，但是插入，删除，修改都不频繁的场景，这种场景非常常见。

需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

6.使用

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

如：当我们的一个student表的id要关联于class表中的学生的id，创建的主键id是在class表当中的。因此这样才能确定该班中的学生有谁。

• 查看索引

show index from 表名;

案例：查看学生表已有的索引

show index from student;

• 创建索引

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

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

案例：创建学生中name字段的索引

create index index_name_student on student(name);

• 删除索引

drop index 索引名 on 表名;

案例：删除班级表中name字段的索引

drop index index_name_student on student;

索引的创建和删除都是耗时操作，因此不必要的时候已经创建好索引的情况下尽量少去自己创建索引，并且不要在生产环境中创建索引。

面试问题总结：
1.索引是啥？
2.索引要解决的问题
3.索引的应用场景
4.索引的数据结构
a) 为什么不用哈希表
b) 为什么不用二叉搜索树
c) 啥是B树，相较上面两个有什么优势
d) 啥是B+树，为什么用它来作索引的数据结构
5.更详细的说下索引的细节方面

二、事务

1.事务的概念

事务解决的问题：
例如有个数据表，保存了一些人的银行账户余额，接下来需要进行A->B转账3000块钱。

可以分为两个步骤：
1.A的账户余额减3000
2.B的账户余额加3000

但是，如果1执行成功了，执行2的时候出了问题，此时A 的钱减少了而B的没有增加，3000块钱是不是就凭空消失了呢?

因此事务的概念：
把一组操作封装到一起，称为一个共同的执行单元，此时执行整个事务就能避免上面的问题。

2.事务的特性

事务的特性总称为ACID。

a) 原子性：事务中的若干个操作，要么全部执行成功，要么全部都不执行。但此处的不执行并不是真正的不执行，而是一旦中间某个步骤执行出错，就把前面已经执行完毕的步骤回滚(rollback)回去。步骤回滚要借助逆向操作，目的是把原来操作造成的影响进行还原。例如：减3000的逆操作就是加3000.

b) 一致性：执行事务的前后，数据始终处于一种合法的状态，例如转账操作，减账户余额的时候，不能账户余额减成负数。

c) 持久性：事务一旦执行完毕，此时对于数据的修改就是持久生效的（写入磁盘了）。注：数据存到磁盘中就是持久的，数据存到内存中就是不持久的（重启就没了）

d) 隔离性：比较复杂，设计到“并发执行事务”，它能够引发一系列的问题：脏读、不可重复读、幻读 => 解决方案 => 数据库隔离级别 。

3.使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

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

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;