MySQL-索引&事务

---

前言

在这里将最近学习的MySQL中索引及事务的知识进行总结。

一、 索引

1.1 概念

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

1.2 作用

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

1.3 使用场景

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

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

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。
注意：索引本身也需要占据硬盘中的内存。

1.4 使用

• 查看索引

show index from 表名;
1

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

show index from student;
1

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

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

案例：创建班级表中，name字段的索引。
注意：创建表的索引也是一个危险操作，如果表本身很大再创建索引，可能会造成大量CPU/硬盘IO消耗，将数据库搞挂掉。但是针对表中数据过多这一点有两种解决办法：(1)你可以在刚建表时就创建索引就可以避免这个问题，但是比较考验表的设计功力。(2)如果说没有那个设计功力想要加索引也是有办法的，将数据库的服务器进行更换。首先拿一个新的服务器创建表和索引此时是空表，之后将就数据库数据导入新机器，然后将应用程序请求再接到新机器即可。第二种方式还有一个好处在于如果新机器出问题可以立刻换回旧机器避免问题扩大化，开发中的高风险操作都可以通过类似方式完成。

create index idx_classes_name on classes(name);
1

• 删除索引

drop index 索引名 on 表名;
1

案例：删除班级表中name字段的索引。
注意：删除索引也是比较危险的操作，可能涉及大量的硬盘的IO操作把数据库搞挂掉。

drop index idx_classes_name on classes;
1

注意：创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。这里还插个题外话，创建外键约束时，父表中被参照列需要是主键或是具有唯一性约束。

1.5 索引的底层数据结构

默认情况下对数据表进行条件查询操作都是遍历，引入索引就是通过希望其它数据结构加快查询速度，减少遍历表的可能。

1.5.1 用来查询的数据结构

• 哈希表

只能查询key相等的情况，无法进行<>这样的范围查询。

• 红黑树

红黑树可以进行范围查询，但是如果想找到下一个后继元素可能要回溯到父节点才行。这个问题可以通过线索化来解决但是会消耗存储空间。当元素变多时，红黑树高度也在不断增加，比较次数增多，数据库数据/索引都是保存在硬盘中，比较增多自然硬盘IO操作就增多了。

哈希表和红黑树这两种数据结构都不太适合给数据库使用。

1.5.2 B树

B树本质上是一个N叉搜索树，每个节点存放多个元素，延伸出多个子树，同样数量的数据，数的高度因此下降。

拿着想要查询的key在节点上比较确实需要比较很多次，但是这里的比较还是比较高效的。

• 每个节点中的key也都是有序排放，可以直接进行二分查找。
• B树也会控制，当节点中的key的数目变多会分裂节点。
• 多个数据都是放在连续空间，进行比较时，一次硬盘IO操作就可以读出整个节点，就可以直接完成比较。（实际比较很多次，但是一个节点进行一次硬盘IO操作）

数据库中的索引数据结构是B+树，这里引出B树是为了后续更好理解。

1.5.3 B+树

B+树同样也是N叉搜索树。

上图为一个3阶B+树。
按照B+树的规则来存储数据，此时叶子节点这一层就包含数据集合全集。另外叶子节点会以类似链表这样的数据结构连接起来方便遍历表中所有数据，也很方便进行范围查询。

B+树相较B树的优点：

• 非常方便遍历和范围查询。
• 当前的任何一次查询操作都是要落到叶子节点进行的，硬盘IO次数相同，讲究一个稳定。
• 非叶子节点仅仅存放数据行的key，完整的数据行都放在key相应的叶子节点，这样非叶子节点占存储空间较小，可以缓冲到内存中，从而提高运行速度。

针对上述优点的第三点，为什么这些非叶子节点本体在硬盘中，何时会放到内存中？
典型的策略：

• 数据库启动的时候，加载。
• 首次针对此表查询时，加载。
• 首次命中该索引，加载。

针对查询表索引的几种情况：

• 查询主键索引：就是最标准的B+树，根据key值找到叶子节点的数据行。
• 查询非主键索引：数据库中会新建一个B+树，这里的B+树的叶子节点存放的主键的key，通过非主键的key找到主键key，之后再通过找到的主键key在为主键建立的B+树进行查找，找到数据行即结束。（每次查找非主键索引都会建立B+树，这也是创建索引操作消耗空间的原因之一）
• 不用索引查询：直接B+树的叶子节点。

如果表中没有建立主键约束，那么数据库会根据自带的隐藏列建立默认的B+树。另外B树存在的前提是MySQL使用了innodb这个存储引擎，这是最常用的。

这里底层数据结构还是学过了B和B+树更加能理解。

二、事务

2.1 为什么使用事务

在平时数据库的操作中，一般都是多个数据库的sql语句进行组合使用，比如说转账的语句可以使用两个update语句。

如果执行过程中出现意外情况，比如程序崩溃等。第一句执行完成，第二句执行失败，相当于你扣除了余额但收款人余额并不增加，这个问题就挺严重。这时就需要使用事务来确保操作是完整的、可靠的。

2.2 事务的概念

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

2.3 事务的性质（ACID）

• 原子性：
  把多个操作打包成一个整体，要么全部执行要么全不执行。这里的全不执行并非是真的不执行，因为sql语句是一条一条执行的，当某一条sql出现问题时数据库会自动将数据恢复到sql执行之前的状态，看起来像没有执行的样子，这个操作称为回滚，为了实现回滚，数据库会在执行事务时用日志记录。如果没有差错，内容可以不要，如果有问题则根据日志记录内容恢复。（只要数据库正常工作，就可以保证事务之前的操作都可以被正确回滚）
• 一致性：
  执行事务之前和之后数据是一致的，或者触发回滚，回滚回去的数据要是对的。
• 持久性：
  执行事务对数据库的修改被保存到硬盘上。
• 隔离性：
  隔离性描述的数据库并发执行事务时的性质。首先并发执行事务会出现三个问题：
  （1）脏读：
  就是比如说并发执行一个修改数据和读取数据的事务，当第一个修改数据的事务还未提交时，第二 个事务就将数据读取，但是后面第一个事务又将数据给修改了，这就称为脏读。这个问题可以通过写加锁来解决，即给第一个事务加锁，只有当第一个事务提交之后才能对数据进行读取。这样事务之间的并发程度降低，隔离性上升。
  （2）不可重复读：
  比如说有三个事务，事务a修改数据，事务b读取数据，事务修改c数据但是是在事务b执行时修改数据，这样就会发生一种情况，如果事务b中有两句读取数据的sql语句，可能两个sql语句读取的数据不一致，因为当第一句sql结束后事务c修改了数据，从而第二句sql读取的数据发生了改变。这个问题可以通过读加锁解决即给事务b加锁，只有当事务b提交后才能执行事务c。通过这个操作并发程度再次降低，隔离性上升。
  （3）幻读：
  类似不可重复读的问题，是不可重复读的特殊情况。它是事务a修改完提交后，事务b在读取过程中，事务c增加对应的表的数据，导致最终的读出来的数据集变大。（已有的数据内容是相同的，但是数目不同）解决幻读问题的方法就是串行化，完全舍弃并发性，事务一个接一个执行，效率低，准确度高，隔离性高。

上述很难做到效率和准确度同时兼顾，需要根据具体场景来选择方法。

MySQL给程序猿提供了四个隔离级别，如下图：

修改以上的隔离级别是通过修改MySQL中的配置文件来完成的。

2.4 事务的使用

（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;
1
2
3
4
5
6