专栏首页面试经验贴[别被脱裤系列]2 还没深入数据库就浅出了

[别被脱裤系列]2 还没深入数据库就浅出了

上一部分一起学习了数据库中的ACID等特性。数据库面试系列第二部分分享数据库的调优常用步骤,索引底层原理,页机制等。总体思维导图如下所示。

1 定位

主要从三个方面进行定位,用户的反馈,日志记录以及服务器内网监控。

  • 用户反馈

用户是最直接的反馈者,用户的反馈是推向系统进一步优化的重要一步。

  • 服务器资源监控

尽量不要等着用户反馈的时候手忙脚乱。通常项目中都会有一套比较完整的服务端监控体系,所谓"监控不到位,领导两行泪"。那监控一般都是哪些内容呢?服务器本身CPU,IO等基础指标以外,通常会通过访问趋势表展示服务整体的访问量、响应供应时间情况,错误数量等。通过性能报表展示哪个资源或者服务出现问题。

监控指标

  • 日志分析

除了Linux系统日志,还有数据库日志,根据他们定位问题所在。

Linux日志

数据调优的几个方向

  • 数据库的选择

根据应用,业务的需要选择不同的数据库。是否考虑事务,行存储还是列存储等。

  • 优化表

(1) 如果查询分析较多,可采用空间换时间的方式增加冗余字段提高查询效率。

(2) 不同字段的数据类型直接关系到查询效率的高低和存储的大小。

(3) 采用第三范式让结构更加清晰,减少冗余字段。

  • 合理使用索引

(1) 不是索引越多越好,索引也需占用存储空间,同时也会增加筛选索引的计算时间。

(2) 数据的重复度过高不宜使用索引。

(3) 索引列处于不同的位置对索引影响比较大。比如在WHERE子句中,对索引字段进行计算会造成索引失效。

  • 使用redis等作为缓存

缓存分为静态缓存,分布式缓存,热点缓存。"缓存"我的理解是解决不同硬件速度的差异性,协调且充分利用硬件的资源。使用缓存的案例无处不在,不管是Linux内核管理TLB,还是HTTP的缓存机制。总之很多地方都会通过使用缓存来提高访问速度,尽量减少和数据库的直接交互。

  • 库级优化

(1) 在读写都比较多情况下,通过采用读写分离的方式降低数据库的负载。

(2) 数据库的分库分表。切分数据库到多台服务器。

2 索引的原理

索引看做字典的目录,根据目录能快速定位内容,不用从头到尾花费时间找。是不是加了索引就是起飞了?那是不一定的。

(1) 数据量小的情况,不加索引

(2) 数据流大的情况,考虑加索引

索引的种类

(1)普通索引

没有约束

(2)唯一性索引

增加唯一性约束,一张数据表可以多个唯一索引

(3)主键索引

在唯一性索引基础上,增加不为空的约束。

(4)全文索引

Mysql自带全文索引支持英文,通常使用ES等代替

聚集索引与非聚集索引

(1) 聚集索引

通过索引位置直接找到需要的值。

(2) 非聚集索引

索引项顺序存储,但是指向内容为随机。所以第一次找到索引,还需要第二次去找到索引对应的位置从而取出数据行。

(3) 两者不同点

  • 聚集索引叶子节点存放数据值。非聚集索引叶子节点存放数据行的位置
  • 一个表只能一个聚集索引但是可以有多个非聚集索引
  • 聚集索引查询效率高,非聚集索引查询效率低

适合加索引

  • 字段唯一性性质

我们知道唯一性索引和主键索引都具有唯一性的约束,如果某字段唯一则可以考虑

  • 需要经常Group by和ORDER by的情况
  • 索引是让数据按照某种顺序进行存储和检索。
  • distinct字段需要创建索引

>

什么时候不需要创建索引

  • WHERE条件中用不到的字段不需要创建索引
  • 表记录太少
  • 字段中大量重复
  • 频繁更新的字段。更新字段也更新索引,索引多,更新索引的时候会成为负担。

失效的索引

  • 使用表达式比如EXPLAIN查看表执行计划时索引会失效

EXPLAIN SELECT name from..

  • 对索引使用函数也会失效
  • 使用"like"进行模糊查询的时候不要使用"%",不然也会失效

二叉树

二分查找是一种高效的检索方式,时间复杂度为O(log2n),但是在特殊的情况退化为链表从而导致时间复杂度为O(n)。随后提出平衡二叉树的概念,但是二叉树中树的深度还是O(log2n),数据查询依赖于磁盘IO,从而改造了M叉树。比如B树,对于一个1000阶的B树,只需要三层就可以存储1000W的索引数据,因为高度比二叉树低很多。为了提高查询的稳定性,出现B+树。

这里也就出现一个面试题

B树和B+树

  • B+树查询更稳定,因为在查询过程中都是在叶子节点才能找到数据。B树中非叶子节点也会存储数据
  • B+树更矮胖,查询时所需磁盘IO更少。相同的磁盘页,B+树能存放更多节点关键字。

3 页结构

在数据库中,不管是读取一行还是多行都是将所在的页进行加载。页是数据库管理存储的空间的基本单位。

在数据库中存在页,区,段等概念,他们之间的关系如下图所示。

数据库页区段

从上图我们知道一个表空间存在多个段,其中一个段包含多个区,一个区存在多个页,每个页多行记录。那具体都是干啥的呢?

在Innodb中,一个区分配64连续的页,页大小默认为16KB,所以一个区大小为64*16KB=1MB

段是由多个区组成,不同数据库对象不同段。创建一张表的时候创建一个表段。创建索引则为索引段。

表空间

逻辑容器。其中包含很多段,但是一个段只能属于一个表空间。一个数据库由多个表空间组成,其中包含系统表空间,用户表空间等。

数据库IO操作最小单位为页。页的具体结构如下图所示。

为了知道页中各个字段是什么意思,总结了一个图表如下

上面的文件头和文件尾对页内容进行封装,通过校验的方式保证页的完整性。同时通过链表的方式将各个页连接在一起。如下图所示。

再看记录部分。其中记录部分包含了最大,最小记录和用户记录,另外还有可变的空闲空间方便灵活的分配新的记录。

索引部分

在页中记录按照单链表的方式存储。我们知道单链表的插入和删除方便,但是查找就不是很有好了。所以在此引入页目录,页目录提供二分查找的方式提高记录的检索效率。那具体是怎么样的呢?

  • 先对记录分组,第一组只有一个记录,最后一组为最大记录
  • 每一组最后一条记录存储一共多少条记录。
  • 页目录存储最后一条记录的地址偏移量,也叫做槽,其指针指向组的最后一个记录。

假设查找键为6的用户,页目录下标从0开始,采用二分查找进行。 (1) mid=(low+high)/2=1,此时取出槽1最大记录为4,4<6则在[mid,high]中寻找

(2) mid=(mid+high)/2=2,此时取出槽2最大记录为8,8>6,直接在槽2查找,遍历取出即可。

3 悲观锁与乐观锁

锁运用到很多地方,我们熟知的多线程,线程同步等都可能用到锁,通过锁来调整运行顺序和保持一致性。在数据库中,按照粒度划分为行锁,表锁和页锁。

(1) 行锁

优点:锁粒度较小,锁冲突概率小,并发度较高。缺点:锁开销大,容易出现死锁

(2) 页锁

从前面总结我们知道页中包含行,那么页锁数据资源比行锁多。开销在行和表锁之间,会出现死锁。

(3) 表锁

优点:锁使用开销小,加锁快。缺点:锁定力度大,发生锁冲突概率大。

从数据库的管理角度来区分,分为共享锁和排它锁

(1) 共享锁

可以被用户读取,但是不能修改。

(2)排它锁

也叫做独占锁,写锁或者X 锁。只允许进行锁定的事务使用,其他事务无法对其修改或者查询。所以我们在使用更新操作的时候,为了防止其他事物的更改,就会使用排它锁。

从开发者角度分为乐观锁与悲观锁

乐观锁:通过自身采用时间戳或者版本机制进行控制。悲观锁:通过数据库自身机制保证数据操作的排他性

4 SQL分析常用步骤

(1) 检查有没有出现有没有周期性的规律,如果有可以考虑更新缓存的策略或者加缓存

(2) 如果不是,考虑是不是查询语句本身问题,从而分析查询语句。之前介绍的几种查询优化的方法都可以尝试。同时引入慢查询可以知道执行慢的语句有哪些

(3) 找到了执行慢的语句就可以引入explain查看SQL执行计划,通过expalin可以知道数据表读取顺序,实际使用的索引,被优化行的数量等。

(4) 最后使用show profile了解执行成本。默认是关着的,使用set 'profiling'="on"打开。

这一篇就到尾声了,谢谢大家的查看,也麻烦大家文末点个在看。再见,下一期常见面试题汇总见。

参考链接:

https://dev.mysql.com/doc/

https://time.geekbang.org/column/intro/139

http://www.redis.cn/

https://time.geekbang.org/column/intro/192

https://blog.csdn.net/gengkui9897/article/details/89294936

本文分享自微信公众号 - 我是程序员小贱(Lanj1995Q),作者:L的存在

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 「面试」破(B)站之旅

    当一个线程在获取锁的时候,如果这个锁已经被其他线程获取,那么这个线程不会破门而入,而是循环等待,但是嗷嗷待哺,需要不断地嗷嗷叫判断锁是否被成功获取,直到获取到锁...

    我是程序员小贱
  • 惊讶!听说你的商品数据都放内存!

    假设将CPU比喻为计算机中的"大脑",那存储器就是其附身了,存储空间有限但是非常的快。为了记忆部分数据就出现了CPU Cache,它用的是一种叫做SRAM的芯片...

    我是程序员小贱
  • 你到底是对称还是不对称?

    至此,总结下,大部分情况下使用对称加密,具有比较不错的安全性。如果需要分布式进行秘钥分发,考虑非对称。如果不需要可逆计算则散列算法。因为这段时间有这方面需求,就...

    我是程序员小贱
  • 数据库性能优化(MySQL)

    序: 即使有较长的缓存有效期和较理想的缓存命中率,但是缓存的创建和缓存过期后的重建都是需要访问数据库的。对数据库写操作不是很容易引入缓存策略。 11.1...

    李海彬
  • 数据库字段及索引设计规范

    原因:列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的 IO 次数也就越多,索引的性能也就越差。

    happyJared
  • MySQL 索引

    数据库的索引是一个要点, 无论是面试还是在工作中, 这个知识点都很常会用到, 你可能只是用过索引, 知道加了索引可以提高查询的性能, 但不知道为什么这样, 今天...

    一份执着✘
  • 从新华字典到数据库索引

    学过服务器端开发的朋友一定知道,程序没有数据库索引也可以运行。但是所有学习数据库的资料、教程,一定会有大量的篇幅在介绍数据库索引,各种后端开发工作的面试也一定绕...

    一个优秀的废人
  • 知识查差缺不漏贴:索引的分类

    数据饕餮
  • 数据仓库中如何使用索引

    数据仓库的索引是个棘手的问题。如果索引太多,数据插入很快但是查询响应就会很慢。如果太多索引,数据导入就很慢并且数据存储空间更大,但是查询响应更快。数据库中索引的...

    用户1217611
  • 【DB笔试面试430】在一个表中可以建立()

    ● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/

    小麦苗DBA宝典

扫码关注云+社区

领取腾讯云代金券