SQL中的索引和事务
- 索引
- 概念
- 索引内部数据结构
- 作用
- 使用场景
- 使用
- 查看索引
- 创建索引
- 删除索引
- 事务
- 概念
- 作用
- 使用
- 关于事务的问题
索引
概念
索引是一种特殊的文件,包含着对数据表里所记录的引用指针。可以对表中的一列或者多列创建索引,并指定索引的引用类型,各类索引有各组的数据结构实现。
索引内部数据结构
聚簇索引和非聚簇索引
聚簇索引:把数据的每一行数据都给放到索引结构中
非聚簇索引:把数据的每一行还是按顺序存放在磁盘上存储,索引里面除了存储这个用来构建索引的列之外,再存一个行号/或者主键id
B+树
- 父节点里面的值,会作为子节点的最大值出现(也可以是最小值),这样就可以让所有叶子结点都包含了整体数据的全集
- 叶子节点按照顺序通过链表的方式连接起来,很方便实现范围查找
- 每一行数据的其他列信息,只存在于叶子结点上,非叶子节点仅仅包含索引列的数据。非叶子结点占的内存就比较少,直接可以放在内存中,后续查找的时候,就可以直接读取内存,减少很多次的磁盘磁盘操作
作用
- 数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍的内容、书籍目录的关系
- 索引所起的作用类似于书籍目录,可以快速定位、检索数据。
使用场景
考虑对数据库表的某一列或者某几列创建索引,需要考虑一下几点:
- 数据量较大,且经常对这些列进行条件查询
- 该数据库表的插入操作,及对这些列的修改操作的频率较低
- 索引会占用额外的磁盘空间
使用
创建索引主键约束(primary key)、唯一约束(unique)、外键约束(foreing key)时、会自动创建对应列的索引。
查看索引
show index from 表名;
创建索引
对于非主键、非外键、非唯一约束的字段,可以创建普通索引
create index 索引名 on 表名(字段名);
删除索引
drop index 索引名 on 表名;
事务
概念
事务指逻辑上的一组操作,组成遮住操作的各个单元。要么全部成功,要么全部失败。在不同环境中都可以有事务。对应数据库中,就是数据库事务
作用
控制组成事务的SQL语句全部执行或全部不执行。
使用
(1)开启事务:`start transaction;`
(2)执行多条SQL语句
(3)回滚或提交:rollback/commit
关于事务的问题
- 什么是事务?
事务指逻辑上的一组操作,组成遮住操作的各个单元。要么全部成功,要么全部失败。在不同环境中都可以有事务。对应数据库中,就是数据库事务 - 事务有哪些特征?
- 原子性:事务是不可再分割的部分
2) 持久性:事务执行完毕之后,数据就存到磁盘上了,重启主机数据也依然存在。
3) 隔离性:并发执行事务所产生的问题和解决方案 - MySQL并发执行事务可能产生的问题
1)脏读
一个事务A在修改数据,另一个事务B直接读取A正在修改的数据,此时B读到的就是一个脏数据。因此B读到的数据很可能就在A的后续操作中又进行了修改,相当于读到的数据只是一个“中间状态”,读取脏数据的动作叫做脏读。
解决方法:引入写加锁,在事务A在写数据的时候其他事务不可读,对并发做出了一定的限制,并发程度降低,隔离性提高了。
2)不可重复读
事务A修改数据然后提交,提交完毕之后事务B开始读取数据,事务B包含了很多次的读操作,如果事务B两次的操作的到的结果不一样,就是 不可重复读。
不一样的原因就是事务A虽然把数据已经提交了,但又可能执行了一次事务A,又把数据修改了。
解决方法:引入读加锁,必须是事务A写完才能被事务B读。在事务B读完之后事务A才能写。
3)幻读
事务B正在读取数据,事务A插入了新的数据/删除了某个数据,事务B两次读取到的数据集的个数发生了变化。
解决方案:通过串行化解决
- SQL中事务的隔离级别
四种隔离级别
1)read uncommitted 没有做出任何隔离的限制,因此三种问题都存在
2)read committed 相当于进行了写加锁,并发程度降低了,隔离级别提高了。
3)repeatable read 相当于进行了读写加锁,并发程度又降低了,隔离级别又提高了。
4)Serializable 串行化,并发程度最低,隔离级别最高,三种问题都能得到解决。