MYSQL之索引事务_mysql加索引会提交事务嘛为什么

目录

索引

作用

使用

查看索引

创建索引

删除索引

索引在MYSQL当中的数据结构

1.哈希表

2.二叉搜索树

3.N叉搜索树

4.B+树

事务

概念

使用

事务四大特性

1.原子性:

2.一致性:

3.持久性:

4.隔离性:

MYSQL 的四个隔离级别

---

索引

作用

使用

查看索引

MYSQL在我们创建主键约束(primary key),唯一约束(unique),外键约束(foreign key)时,会自动创建对应的索引

现在让我们来看看如何查看索引

-- 查看索引的语句
show index from 表名;
 
-- 实际演示
show index from student;

可以看到student这个表的id有一个索引

下面我们来看看如何创建索引

创建索引

-- 创建普通索引 
CREATE INDEX index_name ON table_name(col_name);
 
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
 
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
 
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

演示普通的索引创建,剩下的可以自己去试试

 观察上图我们可以发现,key_name显示的是我们自定义的名字,并不是主键,唯一,外键这种名字

Column_name显示我们是把索引创建在了name上面,而不是其他的东西上面

注意:

1.一般索引的创建是在表的创建伊始,而不是像我这样中途添加,因为如果你要加索引的表有很多很多 的内容,此时会吃掉大量的磁盘IO,导致花费大量的时间(因为你要创建的索引此时就像周星驰的电影里面,韦小宝要练的'绝世武功目录'一样厚.具体你的'绝世武功目录'有多厚,就取决于你的'绝世武功'有多少了)在你创建索引的这段时间,你的数据库是无法正常使用的,所以这是一个非常危险的操作

2.索引是为了加快搜索速度二创建,但是他不是万能钥匙,任何问题都可以解决,比如:当你要录入姓名的时候,遇到了同名同姓的张伟,你加上了索引,他不会报错,此时还是很快,但是张伟多起来的时候,就不一定了

如果你是针对性别(还有年龄这种大量重复)这种列加索引,无法提高查找的速度

3.不加ORDER BY的索引,结果不分先后

删除索引

drop index index_student_name on student;

删完再次查看,无索引

和中途添加索引一样,删除索引也是危险的操作,因为他会吃磁盘的IO,所以需要我们在一开始设计数据库的时候就规划好,尤其是当你的'绝世武功'变多了,就要慎重了

索引在MYSQL当中的数据结构

1.哈希表

先说结论:不行

原因:

哈希表查找元素的时间复杂度是O(1)

但是,他无法范围查询,他只能比较是否相等,无法做出小于,大于(<,>),这样的范围查询,而我们的数据库需要经常进行范围查询,所以淘汰了

2.二叉搜索树

先说结论:可以,但是没必要

二叉搜索树,查询元素的时间复杂度是O(N)

二叉搜索树也满足范围查询的要求,但是数据库并没有使用二叉搜索树

原因:

当二叉搜索树中的元素变多的时候,二叉搜索树会变高,二叉搜索树的高度决定查询的时候,元素的比较次数.因此数据库的海量数据会导致二叉搜索树高度变高,元素的读取速度变慢,

所以没有采用二叉搜索树

3.N叉搜索树

先说结论:可以,但不完美

每个节点上有多个值,同时有多个分叉,有效降低树的高度

代表作:B树(下图为示意图)

 通过上图我们可以看出,比较的次数虽然没有减少(一个节点可能需要多次比较)

但是读写硬盘的次数变少了(每个节点都在硬盘上)

4.B+树

先说结论:可以

虽然B树已经比二叉树的更加适合做数据库的索引,但是还不够,于是引进了B+树,就是为了索引而量身定做的数据结构

 1.可以看到B+树也是一个N叉搜索树,每个节点都可能包含N个Key,N个Key划分出N个区间,最后一个Key就相当于最大值

2.父元素的Key会在子元素出现,并且是最大值形式出现,这样的重复出现导致叶子结点就包含了所有数据的全集,非叶子节点中的所有值都会在叶子结点中体现出来

3.会把叶子结点用类似链表的方式首尾相连

上述B+树的特点,就带来了一些好处

1.作为一个N叉搜索树,高度降低了,比较的时候,硬盘IO次数下降(同B树)

2.更适合进行范围查询

3.所有的查询,都是落在叶子结点的,无论查那个元素,中间比较的次数差不多,查询操作比较均衡

B树查询不均匀有的快,有的慢

B+树大家都一样

4.由于所有的Key都会在叶子结点出现,因此非叶子节点,不必存表的真实记录(不需要存数据行),只需要把所有的数据给放到叶子结点就行,非叶子结点只需要存索引的值(如id)

例子演示:

需要找到上图中2到20的范围

只需要找叶子结点就可以了

 由于非叶子节点只存了简单的id,没有存一整行,这就意味着非叶子节点,占用的空间是大大降低的,有可能在内存中可以放进去缓存,更进一步降低了硬盘的IO,提高查询速度,本质上就是在降低硬盘IO次数.

他有可能是这种存储方式(树形组织结构)

 也可能是这种存储方式(表组织结构)

 对于带有主键的表,就是按照主键索引的B+树来组织索引的,有的表,不只是主键索引,还有别的主键列,也有索引.

这种情况就是构造另一个B+树,B+树非叶子节点里面都是存这一列的Key(比如学生姓名),到了叶子结点这一层,他不存之前的完整的数据行而是存主键id,使用主键列来查询,只需要查询一次B+树

如果使用非主键列的索引来查询,则需要先查一遍索引列的B+树,再查一遍主键列的B+树

当前B+树这个结构,只是针对MYSQL的LnnoDB这个数据库引擎,里面所典型使用的数据结构

不同的数据库,不同的引擎,里面存的数据的结构可能存在差异

就像前面所说的哈希表这样的数据结构,不太适合做索引,也不是完全没有,有的特殊的存储引擎,为了应对特定场景,也是可能使用哈希表的

事务

概念

说人话就是:事务可以把多个SQL打包成一个整体,让他们变成一根绳上的蚂蚱,一荣俱荣一损俱损(要成功都成功,要失败都失败)

举个生活中的例子就是转账

张三有100块钱,李四也有100,张三要给李四转50块钱过去,此时如果没有发生故障,李四应该收到张三转过来的50,李四的钱变成150,张三因为转了50给李四,张三的钱应该变成50

但是此时发生了故障,张三还是转给了李四50,但是李四没有收到张三转过来的50,李四的钱还是100

但是张三的100变成了50,这就是张三进行了转钱的操作,但是李四没有收到

 如果我们使用了事务,把张三转钱给李四和李四收到张三转钱的事情变成一个整体,就不会出现这种状况,就只会出现两种情况

1.张三成功转钱,只剩50元,李四成功收钱,李四的钱变成15元

2.张三转钱失败,李四也没有收到钱,但是张三的钱还是100,李四的钱也是100,双方的钱都没有发生变化

打包成整体这个操作就是'原子性'.这个是事务的最核心的特征

一条指令出错,全部都执行失败,不会有的成功有的失败

注意:

这里不是真的一条都没有执行,他只是自动恢复成了执行之前的样子,看起来像是没有执行过

涉及到一个关键操作(roollback),就是电脑上面的CTRL+Z.

虽然有'回滚',但是这个操作是有很大的开销的,你可以保存但是不可能无限保存,最多就是把正在执行的事务保存下来,额外的东西不在保存.

那么有了这个东西,我们是不是就可以不在担心:不小心删库和删表了呢?

答案是当然是:NO,删库和删表亦然是危险的操作 ! ! !

假如数据库里面 此时有数以亿计的数据,占据了几百个G的空间,,你不可能花个几T的空间来记录这数以亿计的数据到底是怎么来的.

使用

(1)开启事务:start transaction

(2)执行多条SQL语句

(3)回滚或提交:rollback/commit

start transaction来开启事务

中间写多个SQL语句

commit:开启事务后,中间的sql不会立刻执行,而是先攒着,等commit再统一执行,保证(原子性)

rollback :会在事务的其中任意一条SQL执行失败后主动回滚(恢复如初)

事务四大特性

1.原子性:

事务的初心,也可以理解为事务诞生的原因

2.一致性:

事务执行前/执行后,都要是数据合法的状态.比如上面举例的转账,不能说转的过程出错了,导致出现 钱 转丢了的结果

3.持久性:

(持久>=硬盘)事务产生的修改,都是会写入硬盘的,即使程序重启/主机重启/掉电,事务都可以正常工作,保证修改是生效的

4.隔离性:

一个数据库服务器,同时执行多个事务的时候,事物之间,'相互影响的程度'.

mysql服务器同时要给多个客户端提供服务,此时多个客户端之间,可能会同时发起事务,尤其是这多个事务在操作同一个数据库的同一个表的时候,就很有可能引起麻烦

隔离性越高,就意味着事物之间的并发程度越低,执行效率是越慢,但是数据的准确性越高

隔离性越低,就意味着事物之间的并发程度越高,执行效率是越快,但是数据的准确性越低

MYSQL给我们提供了不同的档位,可以控制隔离性的高低/并发程度的高低/执行效率的高低/数据的准确性的高低

有的时候希望执行效率快点,但是准确性不高的比如播放量,对于几十万播放量,差几百是可以接受的

有的时候希望执行效率慢点,但是准确性高的比如算钱,一定不能出差错

具体应该使用那种,具体问题,具体分析

脏读:

脏读,不是读的东西脏了,下面将举个例子来理解 脏读 问题

老师布置了作业,然后我在写作业,这个时候某某某路过,瞄了一眼的我的作业,他看到我的五个选择题都选了A ,然后他就把这个答案借鉴到了自己作业上面,但是这个时候我突然发现,不对,我选错了,我改掉了一个答案,变成了一个B四个A,但是他不知道我对答案进行了修改,这个时候就发生了 脏读

 在上面的场景中,我和某某某的事务就是并发的,没有任何限制,在这个前提下,就容易发生脏读问题

解决脏读问题的办法就是降低并发性,提高隔离性,具体来说就是加锁

我在写的时候,某某某不要借鉴我的答案,等我写完,给他了,他在去借鉴

进行加锁以后,我写的时候,某某某就不能去借鉴,相当于降低了并发程度,提高了隔离性 ,降低了一定的效率,但是提高了准确性

'不可重复读'

现在我们约定了写加锁,我这边写的时候,某某某不能看,等我写完.把作业给他借鉴以后,他开始借鉴,这个时候突然路过一个某某同学,我和他讨论作业突然发现,有一道题还是做错了,需要改答案,我就把去找某某某,把答案改掉了,某某某说,你是不是在耍我玩,怎么还改东西

这就是'不可重复读',同一个事务中,连续两次读到的数据不一致,解决办法就是继续加锁,只不过不是给读加锁,是给写也加上锁,这样,读的时候不能写,写的时候不能读

幻读

现在是两把锁,分别是写加锁和读加锁,读的时候不能写,写的时候不能读

我的作业正在被借鉴我不能对其进行修改但是我可以写别的作业啊,此时某某某借鉴的作业内容虽然没有发生变化,但是他可以借鉴的数量变多了

我没有对他正在借鉴的内容进行修改,但是我可以写其他的作业,这样他要借鉴的就不是这一门作业而是更多门的作业

这个就是幻读

在同一个事务中,两次读到的结果集不同

要想解决幻读问题,就要彻底舍弃并发,只要某某某在借鉴,我就只能出去玩

MYSQL 的四个隔离级别

1. read uncommitted

不做任何修改,事物之间都是随意并发执行的,并发程度最高,隔离性最低 会产生脏读+不可重复读+幻读

2. read committed

对写操作加锁,并发程度降低,隔离性提高 解决了脏读,仍然存在不可重复读+幻读

3. repeatable read

对写和读都加锁,并发程度继续降低,隔离性继续提高 解决了脏读+不可重复读,仍然存在幻读

4. serializable

严格串行化,并发程度最低(串行执行),隔离性最高 解决了脏读+不可重复读+幻读,执行速度最慢

我们的MYSQL默认档位是三挡,可以根据需求进行换挡操作

本文完,感谢观看