MySQL的索引和事务

索引

索引的基本操作

索引就像一本书的目录一样(index),当你想要查询一个数据的时候就需要遍历表,一条一条的筛选,因此就需要建立索引来提高查找的速度.

• 优点:可以提高查询的效率
• 缺点:1.消耗额外的空间2.有空能会拖慢增删改的速度以为在这三个的过程中都涉及到索引的删除修改以及维护索引(不过很多的业务场景都是以查询操作为主的,所以和适合)

在sql中使用索引

• 查看索引:show index from表名;
• 当一个表中存在主键,unique,外键是就会自动生成一个索引
• 创建一个索引:create index 索引名 on 表名 (添加索引的列);

创建索引这是一个非常危险的操作,当一个表中有很多的数据时,会引起非常大规模的硬盘io操作,使得数据库卡死.

• 删除索引:drop index 索引名 on表名;(只能删除手动创建的索引)

如果确实要给一个有很多的数据的表进行创建删除索引,并且这还是一个生产环境的数据库?

• 数据库服务器往往不是一个单独的服务器,为了维护可靠性,往往会高多个mysql服务器节点,这些节点的数据的数据都是一样的,能够提供相同的服务(某个挂了也不影响),
• 先准备一个新的mysql服务器,把表和数据以及索引都准备好,再把要替换的关闭,把新的替换上去.

索引的底层数据结构

1.mysql的索引结构取决于MySQL使用的是哪个储存引擎(innodb是目前最主流的一种)

2.数据库这边的数据结构都是在硬盘上的,在内存上的访问操作是不明敏感的,但是在硬盘上的数据结构对于访问操作比较敏感,读一次硬盘相当于1w次内存.

3.该使用哪种数据结构来组织数据

• 哈希表:只能用来精准查询,不能用来进行模糊和范围查询.
• 红黑树:红黑树的高度会在数据过多时比较高,会导致很多的io操作
• B树:本质上就是一个N叉搜索树,一个节点上保存多个key,在延伸出N+1给区间.对于硬盘,一个节点上有多个key和一个key,io开销都是一样的,当一个节点上可以保存的数据变多时,树的高度就会大大减少.

查找流程:拿着要查询的数据,从根节点出发,判断是否在根节点上,如果不在就判断落在那个区间上,沿着这个路线往下一个节点上找,到了叶子节点,找到了就存在,没有就没有.

对于B树来说设计到拆分和合并,当一个节点中key过多时就进行拆分.把这个节点中的一部分key以树的子节点重新组织.

B+树

这是最终合适数据库的数据结构,在B树的基础上进行了一系列的改进

特点

• B+树也是一个N叉搜索树,但是N个key只有N个区间,,其节点上的最后一个key就是一个最大值(最小值也可以)
• 父节点的key会在子节点出现,并且是最后一个key最大值,虽然看起来优点浪费空间,但是叶子节点这一层就包含了全部是数据
• 把叶子节点按照链表的方式进行首尾相连,就可以快速找到上一个和下一个,并进行范围查找

优点

• 擅长范围查找
• 所有的查询最后都会落到叶子节点上,次数比较均匀
• 叶子节点上的数据是完整的,一行中的所有数据都可以保存在里面,而非叶子节点就可以只保存一个构建检索的key就可以了
• 由于非叶子节点储存空间非常小,可以在内存中缓存一部分,通过内存进行比较,更快速的找到叶子节点,进一步节省io

事务

事务的本质是将多个sql操作打包到一起

事务的四个特性

原子性

见多个sql语句打包成一个语句,要么不执行要么一起执行.

• sql执行每个操作在日志记录，一旦中间有操作执行失败。
• 执行失败以后，会读取日志将已经执行的操作，反过来执行，这一过程就称为“回滚”。

通过以上操作宏观上看就是一个操作

1. start transaction；开启事务
2. 开始输入sql语句
3. commit（提交事务）rollback(手动触发回滚)，一个事务必须要以这两个操作作为结尾，不然接下来的操作还会被认为是事务的一部分

一致性

事务执行执行之前，和事务执行之后，数据可以对的上

持久性

事务里执行的操作都是持久生效的，一旦事务成功，这里的所有操作产生的修改都会写道硬盘里

隔离性

在并发执行事务的时候隔离性，会在执行效率和数据可靠之前进行权衡。描述同时执行的事务之间相互的影响。隔离性越高，并发性越低，数据越可靠，性能越低

如果两个人同时对一个数据进行操作就会产生麻烦

脏读

当事务A进行写操作时，事务拿到了错误数据，这个错误数据就被称为脏读。当我们给写操作加上一个锁，当我们在写的时候别的事务不可以读。引入了一个写加锁，降低两个事务的并发性，提高了隔离性，降低了效率，使数据更准据

不可重复读

当事务的第一个写操作完成时，别的数据进行读取时，我进行第二次写操作，在进行提交，这时正在读取的数据就发生了改变。这时候我们再给读操作加上一个锁，在事务读的时候不可以进行锁。给一个操作加上读写锁后，隔离性进一步提高了

幻读

我不在原来的地方修改了，我创建一个新的类，这样在读的过程中数据不会发生改变 ，不过结果集变多了，相当于不可重复读的一个特殊情况。解决这个问题就必须要彻底放弃并行操作了，所有的事务都是串行执行。

四个事务的隔离等级

1. read uncommitted（RU）允许读未提交的数据，会发生脏读 ，不可重复读，幻读
2. read committed（RC) 给写进行加锁，解决了脏读
3. repeatble read (RR默认的隔离等级) 加上了读写锁，
4. serializable 事务彻底串行执行