数据库总结

目录:

1 从sql查询语句说起 2 sql更新语句和redolog 2.1 Buffer Pool 2.2 redolog 2.3 刷脏页 3 事务和锁 3.1 基本概念 3.2 ACID 3.3 隔离级别 3.4 MVCC 3.5 锁 4 索引 4.1 为什么要加索引 4.2 数据结构优化过程 4.2 B+树 4.3 聚集索引 5 调优 5.1 SQL和索引优化 5.2 其他 参考

1 从sql查询语句说起

之所以从架构开始说起,作为一个科班出身的同学,一条sql语句为什么能返回我想要的结果是不是应该了解?

img

就是这张被用烂的图哈。

  • 客户端缓存:客户端的一条sql语句会先在客户端的缓存搜一遍,如果之前查过了,直接从缓存的key-value中返回结果。其中key是sql语句,value就是之前的查询结果。把缓存放在客户端是比放在服务端科学一些哈(mysql 5.6之后)。
  • 连接器:如果缓存中没有找到,客户端通过连接器连接到mysql服务器。连接器是一个半双工的通信,其次加上一些用户密码验证的作用。
  • 分析器:对sql语句进行词法、语法分析。
  • 优化器:优化器会对mysql语句做简单的优化。举个例子:多重索引的查询,本来是只支持左连接的,如果我的sql语句没有按照左连接来写,优化器会帮我改成左连接的形式进行查询。
  • 执行器:执行器会调用存储引擎的接口,去进行查询操作,把所有查询到的结果整理之后作为结果集返回给客户端。
  • 存储引擎:InnoDB和MyIsam两种。

那么sql的更新语句跟查询语句在执行过程中有什么区别呢?

2 sql更新语句和redolog

当时在听mysql的课的时候就提到了这几个日志,他们的作用分别是什么?跟更新语句的执行又有什么关系?

2.1 Buffer Pool

上面提到了一个查询sql语句最后会通过执行器调用存储引擎的接口查询数据。那更新sql的语句也是一样的吗?因为更新涉及到读写的问题,一旦有读写的问题就要考虑时间和花销。为了提高效率,所有的sql更新语句都是在内存中完成的。

如下图所示,sql执行器不是直接在磁盘上修改数据,而是在buffer pool修改数据页。这个数据页(一般是16kb)在内存里,数据页的数据也是从磁盘中读出来的,一般要读某一行的数据,把相邻的都读到内存里,这样就减少了磁盘消耗。直接在内存修改好了之后,写到redolog里。这里知道更新操作是在内存中进行的就行了,下面介绍一下redolog。

img

2.2 redolog

形象理解redolog:在一篇参考文章中把redolog形象比喻为酒馆老板记录赊账的黑板。如果每次都去账本上记录赊账情况,效率太低了,最好的方式先记在黑板上,等晚上打烊了再把黑板上的内容统一整理到账本上。再理解一下,记账的过程其实就是一个更新的过程,所以这才引入了redolog。区别于内存缓冲区,redolog是磁盘上以一个独立文件的形式存在的。

redolog中的redo,重做的思想其实是考虑到一些数据在buffer pool里面已经更新,但是没有来得及写到磁盘里,这个时候如果mysql挂了怎么办?也就是老板如果只用脑子记的话很容易忘了,所以老板想了一个好的策略,每次脑子里有一笔记录就写在黑板上面。这样哪怕老板忘了也可以通过黑板把这一天的赊账都记下来。

2.3 刷脏页

把buffer pool里的数据写到磁盘里的过程叫做刷脏页。

形象理解脏页:而上面提到的buffer pool其实可以理解为老板直接在脑子里记一下有哪些需要更新的数据,这部分数据和账本不一样。之前说到酒馆打烊的时候更新账本,其实还有一些其他的情况:

  • mysql空闲的时候(打烊)
  • redolog写满了(黑板没空间了)
  • 内存满了(老板记不住了)
  • mysql关闭的时候(酒馆放假)

至此,一条更新的sql语句的执行过程也比较清晰了。这里又出现了一个新的问题:那buffer pool里更新的数据和磁盘数据不一样,如果有人查询这个数据是哪个作为标准呢?这就牵扯到了数据库的另一个重要的概念:事务。

3 事务和锁

3.1 基本概念

事务概念:一组sql操作命令,是一个不可分割的工作逻辑单元。

概念我知道,那数据库里的事务长什么样子?

  • BEGIN 或 START TRANSACTION 显式地开启一个事务;
  • COMMIT / COMMIT WORK二者是等价的。提交事务,并使已对数据库进行的所有修改成为永久性的;
  • ROLLBACK / ROLLBACK WORK。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;

3.2 ACID

一提到事务就想到ACID,这几种特性分别是:

  • Atomicity:事务是一个完整的操作,要么全部执行要么全部撤销
  • Consistency:数据库只能从一个状态到另一个状态,不可能同一时间有两个状态哈。
  • Isolation:隔离机制和并发有关系,一会儿会重点介绍
  • Durability:持久性,这个和redolog有关系。

其中最值得拿出来说的有两个:一致性和隔离性。

对于一致性来说,看网上大家都不太好理解,有些说跟业务有关系,比如银行转账必须一个加一个减。我觉得这个不够准确。无论commit、undo还是redo都是为了保证一致性的问题。当前数据库的状态必须是一个唯一的状态。所以一致性是依赖了原子性、隔离性和持久性最终的结果,难理解是正常的哈。

隔离性,接下来重点介绍一下。

3.3 隔离级别

先说说为什么会有隔离级别这个问题,这都跟数据库并发有关系:

  • 读 - 读:有问题吗?没有
  • 读 - 写:有问题吗?有问题
  • 写 - 写:有问题吗?当然也有问题

这里以“读 - 写”来举例子:有两个人A和B在一个事务时间内对数据库进行操作,这里假设A一直在执行查询操作,B一直在捣乱(update | insert | delete):

  1. A先进行了查询,然后B对相同的数据进行了update操作,但是没有commit,这个时候A再查询数据不一样了。– 脏读
  2. A先查询,然后B还是update操作,但是commit了,然后A再查询。–不可重复读
  3. A先查询,然后B进行了delete或者insert操作,A再查询。–幻读

针对“不可重复读”这种情况一想,B已经commit了,这还是一个问题吗?网上对于这个问题也没有好的答案哈。现在我只能理解:A这个事务可能还没有完成,但是B改了而且commit了,这是不符合隔离思想的。

这上面的几种问题根据业务的不同可以自己决定解决到哪部分,于是有了SQL标准制定的几种隔离级别:

  • 未提交读:一听就是脏读都可能出现。
  • 提交读:不可重复读可能出现
  • 可重复读:未解决幻读问题
  • 串行化:一个一个排队执行哈,效率就很低。

从上到下其并发效率递减。其中可重复读是MySQL的默认级别。但是又能暴力串行化降低效率,MySQL是怎么解决重复读还有幻读的问题的呢?这就要提到MVCC和锁机制了。

3.4 MVCC

MVCC全称Multi-Version Concurrency Control,其实就是一个快照的概念。所有在特定时刻(版本)进行的操作都保存在快照中,如果是在特定时刻(版本)之后进行的所有操作都不管,我只读我保存的快照懂吧?

MVCC能解决幻读的问题吗?先说结论:不能。看网上的说法,“读- 写”的情况MVCC是可以避免幻读的,但是“写 - 写”的情况因为每次update都会读取当前版本,故“写 - 写”的情况还是会出现幻读的情况。

那么怎么进一步解决幻读的问题呢?锁他来了。

3.5 锁

  • 根据锁的粒度可以分为:表锁、行锁。 其中MyIsam的默认锁粒度是表锁,InnoDB是行锁。注意:行锁是应用在索引上的,所以并不是真的锁了一行哈。
  • 根据锁的级别可以分为:共享锁、排它锁。 其中共享锁其他事务还是可以读被锁的内容,但是排它锁独占该数据,其他人读都不行。
  • 根据锁的使用可以分为:悲观锁、乐观锁。 其中悲观锁每次拿数据的时候都会上锁,乐观锁在更新的时候才去判断数据是否在更新期间有变化。 适用场景:悲观锁(并发要求不高但是对数据一致性要求高)、乐观锁(高并发、多读少写)
  • 根据锁的范围可以分为:记录锁和临键锁 当检索条件不是单一值而是一个范围的时候用到间隙锁和临键锁。

加锁方式:

  • update、insert、delete会自动加上排它锁
  • select …… lock in share mode 共享锁
  • select for update 排它锁

4 索引

4.1 为什么要加索引

网上有很多答案把字典的目录比喻为索引,我就是理解不过来。我觉得这是不对的,目录是抽取了部分数据的共同特性,浓缩成了一个抽象的列表。而索引是为了加快检索速度的数据结构式的方法,两者有着明显的区别。

那么为什么要添加索引,要从不加索引的情况说起。如果不加索引,假设我执行一个查询的sql语句,执行器就会进行全表查询,一行一行检查是不是我需要的数据。如果第一个就命中了还不能停,因为要检索所有的行把所有满足条件的数据放到结果集里面再返回出来。如果数据比较少,比如就几条几十条,那肯定是做全表查询,加索引反而没什么效果。但是如果数据比较大的情况,比如上万数万条数据,一行一行比较效率就太低了。

4.2 数据结构优化过程

二叉树:

这个时候,有人想到了用二分法的形式来加快检索速度。二分法的本质可以说是二叉树,如果我就用二叉树有问题吗?有,因为二叉树不稳定,有可能因为节点插入的顺序退化成链表,这个时候又变成了全表扫描。

平衡二叉树:

把原来的二叉树变成平衡二叉树,即每个节点的左右子树的高度不能相差1,这样就能保证不出现退化成全表扫描的情况。这样还没有结束,还有两个问题没有解决:

  1. 因为索引是放在磁盘里的,每次访问二叉树的节点都需要进行一次I/O操作。节点越多,二叉树的高度就会越高,这样检索起来有需要大量的I/O,虽然相比全表查询已经少一些了。
  2. 每个节点占据一个磁盘空间(页),如果这个节点只存放一份key-value,这对磁盘资源是一种极大的浪费。

怎么解决?把二叉树变成M叉树呗,把一颗高高瘦瘦的树变成一个矮胖的树。于是,B树它来了。

B树:

B树有自己的定义哈,臭长臭长的不想理会:

  • 根结点至少有两个子女;
  • 每个非根节点所包含的关键字个数 j 满足:⌈m/2⌉ - 1 <= j <= m - 1.(⌈⌉表示向上取整)
  • 有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子。
  • 所有的叶子结点都位于同一层。

总而言之,B树克服了平衡二叉树上面提到的问题。一颗简单的B树来看一下:

img

我们都知道MySQL索引的数据结构是B+树,B树已经很好了为什么又多出来一个B+树?

4.2 B+树

废话不多说,直接列出B+树比B树特性更优的几个点:

  • 所有的数据都保存在叶子节点
  • 叶子节点之间添加了双向指针
  • 子树指针和关键字个数相同(这里注意到B书的子树指针个数比关键字个数多一个)

这里只说一下第一点和第二点的优势。

第一,所有的非叶子节点都不存储数据。这样非叶子节点就能保存更多的关键字,毕竟一个数据页16k能保存的数据有限。这样B+树就能变得更矮胖。

第二,叶子节点之间连接。一颗B+树,其叶子节点一定是顺序排列的。对相邻的叶子节点之间添加链接形成一个链表,这样做有什么好处呢?想象一下如果我是范围查询,我只要定位到一个位置,然后直接从链表中顺序获取其他的数据不就可以了吗?是不是方便很多

4.3 聚集索引

聚集索引:以主键作为B+树关键字构造的索引被称之为聚集索引

非聚集索引:以主键以外的列值作为键值构造B+树的索引。

那么为什么要分成这两种呢?有一个例子举得好像还挺好的:

id username score
1 小明 90
2 小红 80
3 小华 92
.. .. ..
256 小英 70

这个表可以设置聚集索引clustered index(id)和非聚集索引index(username)

如果我进行查询:

select username from t1 where username = '小明'
select username, score from t1 where username = '小明'

查第一句肯定是非聚集索引更快啊。查第二句的话,就需要搞一个二次查询了,为了解决这个二次查询的问题,还提出了联合索引,也就是吧username和score作为一个键值创建索引,搞一个index(username, score)。这里不展开了。

5 调优

如何查看sql语句的具体执行时间?用explain哈

5.1 SQL和索引优化

语句优化:

  1. 使用limit对查询结果的记录进行限定
  2. 避免select *,将需要查找的字段列出来
  3. 使用连接(join)来代替子查询
  4. 拆分大的delete或insert语句
  5. 避免在 where 子句中使用!=或<>操作符,否则不使用索引而进行全表扫描

Explain:

使用Explain查看sql语句执行情况,这里注意explain的三个重要的字段:type、keys和Extra。

explain select * from news;

其中type显示连接使用的是什么类型,key表示实际使用的索引,Extra显示一些备注信息,例如“Using index”

慢查询日志:

mysql> show variables like "slow_query_log"; 开启慢查询日志,也可以使用mysql自带的mysqldunpslow命令查看哪些语句执行比较慢。

最左匹配原则:

img

如果创建一个index(a,b),可以看到a是有顺序的,而b是无序的。也就是说select * from t where b=2这样的语句是没有办法使用索引的。

5.2 其他

表结构层面:分库

架构优化层面:主从分离读写

应用层面:双十一、银行的案例

参考

https://www.cnblogs.com/wyq178/p/11576065.html (现在的mysql缓存都放客户端了)

https://blog.csdn.net/qq_32460819/article/details/119512980 (酒馆黑板-redolog)

https://www.cnblogs.com/s-b-b/p/8334593.html (聚集索引)

https://blog.csdn.net/sinat_41917109/article/details/88944290 (最左匹配原则)