MySQL数据库索引和事务图文详解_Mysql

索引

一. 索引的重要性

mysql 的索引就是数据结构，能够对多列的值进行排序来更快的进行查询数据。索引就像是一本书的目录，通过目录我们可以快速定位到数据所在的位置。无需扫描整张表来找数据。

虽然查询速度变快了，但是与之而来的是读写操作增加了更多的io，但是为了提高检索速度是值得的。

二. 理解索引

要想知道为何添加索引之后可以更快的查询数据，我们就得理解索引。

首先明确，mysql的操作都是在内存当中进行的，它的任务就是对数据进行curd操作。那么数据是保存在磁盘的。mysql与磁盘进行io交互时通过一个个page进行的。page是内存与磁盘交互的最小单位。page的目的在于一次性多加载，以达到减少io的目的。相比于要多少加载多少可以有效减少io次数。

那mysql是怎么提升自己的查询速度的呢？

通过索引数据结构。mysql采用b+树的结构，类似与多叉树，根结点存储page目录用于定位所要信息的page处，叶子结点存储数据信息。这些数据是通过索引进行排序的，都是有序的。

整个查找的办法我们可以类比于找数字。例如我现在要找一个数字52，首先我通过树结构先找到50-60范围的起始点50，然后从50向60进行遍历找到52。

根结点存储page页的指针，page页存储着实际存储数据的page指针，接着就是存储数据的page。

三. 索引操作

1. 创建索引

创建索引有三种方式，第一种直接跟在列后面，第二种在最后面指定列，第三种创建完表后对指定列添加索引

主键索引案例：

唯一键索引案例：

普通索引案例：

2. 查询索引

show 【索引】from【表】

案例：

3. 删除索引

删除主键索引

alter table【表】drop primary key；

删除其他索引

alter table 【表】drop 【索引名】

事务

一. 什么是事务

事务是数据库操作的逻辑执行单元，这些操作要么全部实现，要么全部失败回滚。

为的就是保证数据的一致性和完整性。

事务必须满足四个特性（acid）

原子性

事务是一个整体，要么全部成功要么全部失败回滚，例如转账时a账户扣钱和b账户加钱必须同时成功。

一致性

执行前后，数据库状态要保持一致，如转账后两个账户总金额不变。

隔离性

多个事务并发执行，彼此间的操作不能互相干扰

持久性

事务一旦提交，结果就是永久性的，即使系统崩溃数据也不会丢失。

二. 事务提交方式

事务提交就是将事务执行的操作永久保存到数据库并且释放锁，主要有显示提交和隐式提交

显示提交

显示提交是使用了 commit 进行手动提交事务

隐式提交

隐式提交涉及到mysql的autocommit模式，当autocommit = on 时代表隐式模式开启，每条sql语句都会被自动提交。当 autocommit = off 时就默认为显示提交

我们可以查看autocommit并且进行修改

show variables like ‘autocommmit'
set autocommit = （1开 / 0关）

案例：

三. 事务操作方式

正常处理事务

通过begin开始事务，commit结束事务，savepoint【名字】可以暂存点，rollback【名字】可以进行回档。

案例：

当我们开启两台终端时，隔离级别为未读提交，当我们在终端a插入数据但为commit，此时在终端b访问表就无法查看到数据

案例：

当我们对事务进行commit后，此时中断主机a，在主机b上查询表可以发现依然能看到commit前插入的数据。说明commit将数据永久保存在数据库中

案例：

关闭autocommit，当我们向主机a表中插入数据时，主机b也会同步更新表。当主机a在未commit下突然崩溃，表会自动进行回滚

四. 事务隔离级别

隔离性就是多个操作之间互不干扰的状态。

事务的隔离等级分为四种：读未提交，读提交，可重复读，串行化

查看与设置隔离性

查看全局隔离级别

select @@global.transaction_isolation;

查看当前会话全局隔离级别

select @@transaction_isolation;

当前会话只影响当前数据库的隔离级别，全局会话会设置默认的新链接

案例:

设置当前会话隔离级别

set 【session/global】transaction isolation level 【read uncommitted | read committed | repeatable read | serializable】

设置为全局或者当前会话，设置等级为四个

案例：

读未提交

当我们使用读未提交时，主机a和主机b同时begin开始事务，主机a更新数据库一条数据，但是未commit，此时主机b访问表可以实时查看到数据被更改

案例：

一个事务在执行中，读到另一个执行中事务的更新但是未commit数据，这种现象叫做脏读

读提交

我们让主机a和主机b同时开启一项事务，主机a对列更新但未保存，此时主机b访问该列拿到的是久值。主机b倘若对就值进行处理，此时主机a对该列进行commit，就会造成读取到了不同的值

案例：

这种现象就是不可重读读

可重读读

同时开启主机a和主机b的事务，主机a更改某列数据，此时主机b访问表发现表仍然为原先旧数据；主机a进行commit保存数据，主机b再次访问，发现仍然为原先数据；主机b进行commit保存数据，此时再进行访问表，表才进行了更新。

案例：

倘若我们主机a更改列后不进行commit，此时主机b对该列进行修改，那么此时就会卡住，直到主机acommit后主机b更改才能成功。

这也就导致我们插入数据不断查询的过程中值在不断地变化，如同产生幻觉，这就是幻读