MySQL索引事务

索引

索引 类似于java中的下标 含有共同的含义,但在数据库中的含义是目录,具体就是对表中的每一列或者多列创建索引,我们知道一个大公司的数据库存在的数据量是十分庞大的(千万级别!!!),在查询某个具体的数据利用select是很麻烦的,select 的底层原理是遍历数据库中的每个表每个列来锁定数据的,时间效率非常底 时间复杂度为O(N)级别 ,相比于索引就慢了许多.索引类似于书籍的目录,可用于快速定位,检索数据

语法

索引是MySQL中一种特殊的文件,因此有专门的语法

1. 查看索引
2. 创建索引
3. 删除索引

1. show index from 表名
3. create index 索引名 on 表名
4. drop index 索引名 on 表名

另外,再创建表的时候指定某个列或者多个列 为primary key,foreign key 或unique的时候 此时系统会自动为这些列带上索引,同时在进行删除的时候不能删除上述索引
索引背后所用的数据结构是b树和b+树.

引入索引,就相当于给一本书加上了目录,有了目录就能快速的查询你需求的页码,就提高了查询的速度,却消耗了额外的硬盘空间,这是个小问题,硬盘的成本是非常低的,所以基本上不做考虑.但是又引来了另一个麻烦,会影响到增删改的效率 ,一旦进行了此操作,索引的指向就需要同步的更新维护,此时就要调整索引.实际开发中,也有很多业务场景在查询的频率上比增删改的频率高的很多,所以索引确实一个不错的选择

事务

事务是用来解决一类特定的场景的问题的,例如在某个场景中完成某个操作,需要进行多个sql操作,假设这里有个账户余额表(id,money),表里数据有两行,一行是张三(1,1000) 另一行是李四(2,500),现在要求把id为1的money转500给id为2的 此时需要2条update操作

第一条:
update 表名 set money = money - 500 where id = 1;
第二条:
update 表名 set money = money + 500 where id = 2;

没有意外的话,就会出现一个问题 :假如执行了第一条sql语句后,再第二条sql语句执行前出现了BUG(主机断电等)情况 就会导致一个"整体"操作只执行了一半,此时数据库的内容就会出错了

因此所谓事务就是把多条sql语句打包成一个"整体"操作,要么这个"整体"全部执行了,要么任何一个sql语句都不会执行,这就不会因为某些不可避免的原因而导致数据库丢失.

问题一上面所说的"任何一个sql语句都不会执行" 并不是真的不执行,而是执行了又撤回了,进行了还原,不考虑过程,最终的效果就只体现了没有任何操作或者全部执行
从专业名词来讲这个机制称为 “回滚”(rollback)

问题二 回滚的操作是怎么实现的? 也就是如何撤回数据的执行了? 原因是数据库内部存在一系列的"日志体系",记录到"文件中",因此当开启事务的时候,此时每执行了一步的sql,都对数据进行了那些修改,这些修改都被记录在案,所以执行了回滚的操作,就会参考之前开始记录的位置以后的内容,再进行还原操作!\

事务的代码

start transaction;//开启事务
.......
.......//多个sql
.......
commit;//提交

事务的基本特性

1.原子性:事务中的所有操作,对结果而言:要么全部执行,要么都不执行
2.一致性:事务必须保证数据库从一个一致的状态转移到另一个一致的状态—>不会出现中间状态(临时状态)
3.持久性:一旦事务提交,它对数据库的改变就是永久性的,即使系统发生故障也不会丢失!
4.隔离性: 并发执行的事务不会相互影响

什么是并发执行?

事务的并发执行是指在数据库中,多个事务能够同时进行,而不是一个接一个地依次执行.,因为数据库是一个" 客服端–服务"结构的程序,一个服务器会和多个客服端同时提供服务.所以这种并发执行可以提高数据库的效率和性能

虽然并发执行可以提高数据库的效率,但也可能会带来一些问题;
主要问题包括
1.脏读:一个事务读取了另一个事务未提交的修改数据.如果那个事务最终回滚了,那么读取的数据将是无效的;
2.不可重复读:一个事务重新读取之前已经读取过的数据,发现数据已经被前提事务更改.这就违法了事务的隔离性;
3.幻读 一个事务执行一个查询,由于另一个事务的更新/修改,它读取了之前不存在的行.通常发生在范围查询中

如何解决这些问题呢
1.使用锁机制:对不同的事务进行不同的锁操作,
例如,一个事务在修改的过程中,其他的事务不能读读取它正在修改的数据.
这便给"写操作加锁",解决了"脏读"的情况
例如,一个事务在读取的过程中,其他的事务不能修改它正在读取的数据,
这便给"读操作加锁"解决了"不可重复读"情况
例如,多个客服端同时提交了多个事务过来,但是服务器只能一个一个的执行事务
这里操作便是"串行化"解决了"幻听"情况
2.设置隔离级别
1)读未提交(read uncommitted):一个事务可以读取另一个事务未提交的更改,可能会导致脏读,即读到其他事务未提交的数据; 此时多个事务并发执行程度是最高的,执行速度也是最快的.
2)读已提交(read commited) :一个事务只能读取另一个事务提交之后的数据(给写操作加锁了),可以防止脏读,但可能导致不可重复读,即在同一事务中,多次读取同一数据集合时可能会得到不同的结果,. 此时,多个事务并发执行的程度会降低,执行速度也会变慢,但是事务之间的隔离性会提高,得到的数据就越准
3)可重复读(repeatable read):事务在整个过程中可以看到一致的数据,并且可以重复读取相同的数据集合,相当于给写操作和读操作加锁了,即对其他事务已经提交了对这些数据的更改,防止了脏读和不可重复读问题,但可能导致幻听.此时并发程度 进一步降低,执行速度也进一步变慢,事务之前的隔离性也会更高
4)串行化(serializable):此时,所有的事务都是在服务器上一个接一个执行的,隔离性是最高的,可以防止脏读,不可重复读和幻读,但可能导致性能问题,此时,并发程****度最低,执行速度最慢,隔离性最高,数据最准确

MySQL默认的隔离级别是可重复读~~
在实际业务场景中,需要设定那种隔离级别需要根据实际情况来判别

总结

基于在某个特定场景下,所出现的问题,而怎么解决问题就引出了事务,事务的特性(原子性,一致性,持久性,隔离性),从而衍生出多个事务,在执行多个事务共同完成相应的操作(并发性),但又会出现不同的问题(脏读,不可重复读,幻读)等情况,然后如何解决这些情况呢,就需要设置锁机制和隔离级别;每个隔离级别都有不同的影响效果,有利也有弊,需在不同情况下择取.