MySQL的索引和事务_mysql:索引的理解、设计、优点,对事务的了解

目录

1.索引（index）

1.1 索引的理解

1.2 查看索引

1.3 创建索引

1.4 删除索引

1.5 索引的数据结构 （B+树）

2.事务

2.1 理解事务

2.2 使用

 2.3 事务的特性

 2.4 MySQL的隔离级别

3. 本篇需理解这几点

---

1.索引（index）

1.1 索引的理解

索引存在的主要意义，就是为了提高查询的效率

索引就相当于目录

大多数情况下，查询的频率是高于增删改的，所以索引的存在是很有必要的

索引付出的代价：

1. 消耗更多的空间

2. 虽然提高了查找效率，但是降低了增 删 改的效率

（比如插入新记录，需要既能够插入硬盘的数据，又要调整索引）

1.2 查看索引

show index from 表名；

1.3 创建索引

create index 索引名 on 表名 (列名);

创建索引操作，也是一个低效操作，如果表中数据少不影响，

如果表中数据多，创建索引操作，就可能会非常耗时，并且带来大量的硬盘IO 

1.4 删除索引

drop index 索引名 on 表名;

删除操作和创建操作都是比较低效的操作 

SQL可以支持一些 定义变量，定义函数，条件，循环，库函数等 但是这些很少用到

SQL 一般都是单纯的去 增删查改 

---

 

1.5 索引的数据结构 （B+树）

索引能够提高查询效率，具体是怎么提高的

索引背后的数据结构是怎么样的

数据结构中，查询效率高的有 哈希表 和 二叉搜索树，但是这两种不适合用于数据库索引

因为

（1）哈希表虽然增删查改都快 O(1)

但是只能查询 值 相等的情况，但是如果是< > between and 这种比较大小的范围查询就不适合了

（2）二叉搜索树，查询速度 O(N)最坏情况

AVL 树 / 红黑树 （比较平衡的二叉搜索树）O(logN)

如果数据库数据特别多时，树的高度就会比较高，高度高了就查询时比较的次数就会多

如果每次要读硬盘，这样就不太行了

所以上面两种都不适合，就有了这样一个专门用于数据库的数据结构 B+ 树

下面先了解一下B树

 B树，是一个N叉搜索树

 这样分成N个叉的作用，就是表示相同元素的数据集合时，比二叉树的高度小很多，IO次数就降低了很多

B+树

---

2.事务

2.1 理解事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要 不全部都执行完，要不就一个都不执行

（ 不是说真的没执行，而是执行一半如果出现问题，能够恢复如初 ）

事务就能保证，当执行过程中出现问题时，自动的把前面的sql执行的效果进行还原，恢复如初   这个操作叫 回滚（rollback）

在事务执行过程中，MYSQL会记录每一步都执行了啥，一旦出现问题就可以根据记录回滚

在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.2 使用

（1）开启事务：start transaction;

（2）执行多条SQL语句

（3）回滚或提交：rollback/commit;        全部失败/全部成功

 2.3 事务的特性

原子性：事务的存在的意义，能够把多个SQL打包成一个整体，要么全部执行完，要么一个都不执行（如果执行过程中出错，则自动回滚）

一致性：事务执行前后，数据处在“一致”的状态（数据能对的上，合情合理）

持久性：事务进行的改动，都是写到硬盘里的，不会随着程序重启/主机重启，而丢失

隔离性：（最复杂）多个事务，并发执行时，事务之间能够保持“隔离”，不互相干扰

(隔离性存在的意义，就是让并发执行事务的过程中，尽量不出问题（问题在可控范围内）)

在并发执行事务下会出现的问题

（1）脏读（读取到脏数据，解决方法：对写操作加锁）

脏读：一个事务A在修改数据，提交之前，另一个事务B读取了数据，此时A极有可能在提交的时候提交的是不同数据（后续又修改了）

此时事务B读到的就是“无效的数据”，就称为 脏读，读取脏数据

解决脏读问题的思路

在提交之前，不能读，提交之后才能读（写完才能读）

相当于对 写操作 加锁

在写加锁之前，我的写操作，和别的事务的读操作，就是完全并发的

并发性是最高的，隔离性最低的

当写加锁之后，我的写操作，别的事务就不能读了

并发性降低了（效率降低）,隔离性提高了（准确性提高了）

（2）不可重复读 （读的过程被修改，解决方法：使用读加锁）

如果对写操作加锁后，当我的写操作执行完后，别的事务开始读取代码

此时如果我再次对代码进行修改，再提交，此时别的事务就发现代码改变了

不可重复读：在一个事务A中，多次读取同一个数据，发现不一样（读的过程被修改）

不可重复读：需要使用 读加锁 来解决

 引入读加锁后，并发程度又进一步降低了（效率降低了）

隔离性又提高了（数据准确性也提高了）

（3）幻读（结构集发生改变，解决方法：串行化） 

对写操作加锁，并且对读加锁，此时如果读文件A时，去改文件B（不影响另一个事务读）

虽然这样操作，对另一个事务读取的数据不影响，但是，两次如果读完后，发现结构集变了

（另一个事务读取时第一次看到只有一个.java文件，下次读的时候突然发现变为两个.java文件了）

这种情况称为“幻读”

为了解决幻读问题，方法就是串行化

也就是另一个事务在读时，我不能进行代码操作

 2.4 MySQL的隔离级别

 MySQL提供了“隔离级别”选项，四个选项

1.read uncommitted 

允许读未提交的数据，并发程度最高，隔离性最低。可能存在脏读/不可重复读/幻读问题

2.read committed 

只能读提交之后的数据，相当于 写加锁，并发程度降低，隔离性提高

解决了脏读，可能存在 不可重复读/幻读问题

3.repeatable read(默认)

相当于读和写都加锁了，并发程度再降低，隔离性再提高，

解决了脏读/不可重复读，可能存在幻读问题

4.serializable

严格执行串行话，并发程度最低，隔离性最高

解决了 脏读/不可重复读/幻读问题，但效率最低

在MySQL的配置文件 my.ini 中进行设置

根据不同的需求场景，就可以分别设置不同的档位了

---

 

3. 本篇需理解这几点

1. 如何理解索引

从这三个方面思考

（1）索引是干啥的（解决了什么问题）

索引相当于目录。提高查询效率

（2）索引付出的代价

索引会占用额外的磁盘空间；

虽然提高了查找效率，但是降低了增 删 改的效率

（3）索引背后的数据结构

B+树（可以思考一下B树和B+树的区别）

2. 如何理解事务 

从这三个方便思考

（1）事务是干啥的（从原子性切入）

事务包含一个或多个业务操作，这些操作要么都执行，要么都不执行。事务常被用来确保数据的一致性。（可联系原子性展开说明，并解释“回滚”）

（2）事务的其他特征还要啥

原子性；持久性；一致性；隔离性；（可展开思考一下）

（3）重点隔离性，在并发执行事务下会有哪些问题，以如何解决

脏读（读取到脏数据，解决方法：对写操作加锁）

不可重复读 （读的过程被修改，解决方法：使用读加锁）

幻读（结构集发生改变，解决方法：串行化） （可详细展开思考）

3. MySQL 隔离级别都有什么

1.read uncommitted 

2.read committed 

3.repeatable read(默认)

4.serializable

从上到下，并发程度逐渐降低，隔离性增高（可从脏读/不可重复读/幻读进行分析）

---