MySQL中的索引与事务

前言：

本篇博客将为大家介绍MySQL中的索引和事务

目录

索引

简介

使用

B+树

事务

简介

使用

特性

---

索引

简介

1. 索引是一种特殊的文件，包含着对数据库里所有记录的引用指针。可以对表中一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
2. 索引是对select查询的优化。对数据库进行select查询是将表进行遍历，把满足条件的结果集取出，而索引则可以加快查询操作，避免对表进行遍历。但是，索引的代价就是会占用更多的空间。因为生成索引需要一系列的数据结构，以及一系列的额外数据储存在硬盘中，可能会降低插入修改删除操作的速度。
3. 是否使用索引一般会考虑以下条件：如果满足第一、二两条且磁盘空间足够的话，考虑对表中的字段插入索引，提高查询效率
   1. 数据量大，且对数据经常进行条件查询
   2. 该数据库的修改删除插入操作的频率较低
   3. 索引会占用额外的磁盘空间

---

使用

1. 查看索引：show index from 表名;
2. 创建索引：create index 索引名 on 表名（列名）;
3. 删除索引：drop index 索引名 on 表名;

注意事项：

1. 创建索引是一件危险的事情。因为创建索引需要针对现有的数据进行大规模的重新排列，如果表中数据太多就会卡住。一般创建索引是在创建表的时候就会规划好的。如果非要中途创建索引的话，就要在弄一台机器，在上面创建一张同样的表，且加上需要新创建的索引，再把之前机器上的Mysql上面的数据导入过去，导入的过程中可以自己控制节奏，避免把数据库搞挂，数据全部导入以后，就可以用这台新的机器来代替之前那台旧的机器了。
2. 和创建索引一样，删除索引同样也是危险操作。Mysql中，创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时会自动创建索引。这类自动创建的索引是无法被删除的，删除索引只能删除我们自己创建的索引。

---

B+树

MySQL索引的实现有很多种方式，其内部的存储引擎提供了很多版本的实现，Innodb是当前MySQL最常用的存储引擎=》主要使用B+树（哈希表适合精准匹配，无法进行模糊匹配，如like"孙%"这种查询就无法做到【针对"%孙"这种查询，是无法使用索引的，因为索引是根据前面的数据查询的而这种类型的模糊匹配无法获取到前面的数据】，不适合MySQL；而二叉搜索树，每一个节点最多两个节点，树的分叉少，表示同样数量的结果集合，树的高度就会更高，树的高度高了，查询的适合IO访问的次数就会变多）

B树没有链式结构，因此需要通过回溯（返回上一个节点去寻找满足条件的节点，然后再从满足条件的节点更进一步地往下去进行更加精确的查询）来寻找满足条件的数据，而每次回溯都会产生一次硬盘开销，B+树有链式结构，是不会产生这种额外的开销的，找到下一个元素最多就需要一次硬盘IO。

---

---

事务

简介

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

---

使用

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

• rollback是全部失败，主动触发回滚；commit是全部成功，事务结束了
• rollback要搭配一些判断逻辑来使用。
• 回滚是如何做到的呢？

以日志的方式来记录回滚的关键操作，这样的记录就是回滚的依据。日志是打印在文件的内容，即使主机断电也不会有影响，因为回滚的日志已经打印在文件里了。一旦重启主机，MySQL也重新启动了就会发现回滚日志中有一些需要回滚的操作，就会开始回滚。

---

特性

事务的特性如下：

1. 原子性：通过回滚的方式保证这一系列的操作都能执行正确或者恢复如初
2. 一致性：执行数据之前和之后，数据都不能离谱。很多时候是要考虑数据库的约束以及一系列检查机制来完成的
3. 持久性：事务做出的修改都是在硬盘上持久保存的。重启服务器数据依然存在，事务执行的修改依然存在
4. 隔离性：多个用户并发访问数据库的时候，数据库要为每一个用户开启事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

下面为大家更加详细地介绍隔离性相关的知识：

MySQL是一个客户端服务器结构的程序，一个服务器可以给多个客户端提供服务，多个客户端都会让数据库执行事务，因此极有可能出现这种情况：一个事务还未执行结束，另外一个事务又来了，此时，数据库就需要同时处理多个事务，并发执行（并发程度越高，其执行效率也就越高）。而在并发执行时，数据库则可能会出现一下问题：脏读，不可重复读和幻读。

脏读：事务A正在写数据的时候，另外一个事务B读取了事务A正在写的数据，之后事务A又修改了数据，导致事务B之前读到的是无效数据/过时的数据/脏数据。

解决办法：对写加锁。写的时候不要读，写完了再读。并发程度降低了，正确性提高了，效率降低了。

不可重复读：并发执行事务的时候，事务A在内部多次读取同一个数据时出现了不同的情况，久称为不可重复读。在事务A两次读取数据的间隔有一个事务B修改了数据且提交了事务。

解决办法：对读加锁。读的时候也不能写。并发程度进一步降低了。

幻读：一个事务A执行的过程中，两次的读取操作，数据内容虽然没有改变，但是出现了不同的结果，这种情况称为幻读（这种情况到底是不是问题要具体情况具体分析）

解决办法：引入串行化的方式。保证绝对的串行化执行事务，这种情况下完全没有并发了，从根本上解决了并发带来的问题，效率最低，数据最准确。

---

隔离级别：（在正确性和效率之间作选择。可以在MySQL的配置文件中修改其隔离级别）

read uncommitted：读未提交。隔离程度最低，并发程度最高，速度最快，准确性最低。

read committed：读已提交。加入了写加锁，只能读写完之后提交的版本。并发程度降低。

repeatable read：可重复读引入了读加锁和写加锁。写的时候不能读，读的时候也不能写，并发程度又一次降低。（MySQL配置中的默认级别）

serializable：串行化。严格按照串行的方式一个一个地执行事务，没有并发，准确性最高。