云原生之数据库:《数据库最佳实践_问题诊断》-阿里云开发者社区

开发者社区> 开发者学习资源库> 正文

云原生之数据库:《数据库最佳实践_问题诊断》

简介: 开发者经常会遇到些数据库的问题,觉得无从下手,这严重影响了开发效率,也影响了开发者对数据库的热情。如何避免这样的窘境,如何降低数据库使用门槛以及运维的成本,如何在较短的时间内用云数据库的技术和理念来武装自己,提升自己。本次课程将由阿里云高级数据库专家郑旦通过实际的场景以及最佳实践出发,带给大家一些数据库问题的通用解决思路和方法,大家会发现数据库不再是一个黑盒,相反它看得见,摸得着,也能够轻松玩得转。

演讲嘉宾简介:郑旦,阿里云高级数据库专家
以下内容根据演讲视频以及PPT整理而成。
观看回放https://yq.aliyun.com/live/3029 本次分享主要围绕以下三个方面:

一、MySQL基本原理
二、库表设计规范
三、诊断实践案例

一、MySQL基本原理

MySQL是非常常见的数据库,适用于多种场景中的应用,其次MySQL活跃的社区使其流行度非常高。同时,MySQL也是云上售卖最多的产品之一。
SQl执行流程
SQL是应用和数据库之间的桥梁,SQL的执行效率对应用来说至关重要。下图展示了一条SQL的执行流程:
1)首先是客户端通过MySQL协议与MySQL Server建立连接,MySQL Server负责建立连接,健全认证和管理连接。在阿里云的RDS上提供了线程池的能力,一旦打开线程池,使得通过认证的用户直接可以获取线程,这适合于大量短链接和高并发的场景。
2)连接到MySQL Server后,进入查询缓存层,如果开启了缓存或者通过语句设置了缓存开关,此时就需要检查SQL中是否包含缓存,如果存在缓存,结果直接返回,如果没有缓存则进入下一阶段。查询缓存阶段需要注意缓存本身和查询缓存都是非常消耗资源的,如果开启缓存需要提前对应用做大量评估,密集型的应用请慎重开启。若必须要开启,可以参考语句级的缓存设置,自由的控制哪些查询需要进入缓存。
3)其次进入词法解析和语法解析阶段。SQL会生成一颗解析树。词法解析阶段会解析关键字,语法解析阶段判断MySQL的语法和库表,以及检查表名和列名是否都存在。同时MySQL会结合自身的规则,进行SQL的改写,如关系代数转换等。
4)之后是进入MySQL最复杂的优化器环节。优化器使用了非常多的优化策略来生成最优的执行计划,MySQL是基于成本的优化器,因此会预测多种优化策略的成本,选择成本最低的执行计划进行执行。
5)明确执行计划之后,存储引擎会调用执行计划,完成最后的SQL执行,并且将执行结果返回给客户端。如果此时开启了查询缓存,执行结果会同时放在缓存阶段。
image.png
索引类型
SQL的执行效率提升是至关重要的,SQL提效方面最常用的是索引策略。索引的基本作用主要是将随机IOh转化为顺序IO,减少IO,并且减少内存计算,如比较、排序等等。索引是快速定位记录的一种数据结构的方法,主要类型有B+Tree索引、Hash索引、空间索引(R-Tree)以及全文索引等。B+Tree索引支持等值、范围检索;Hash索引支持等值检索;空间索引(R-Tree)支持地理数据检索(多维数据);全文索引支持非结构化数据检索。
image.png
B+Tree索引结构
以B+Tree为例,下图展示了B+Tree索引结构。左侧两列的第一列是主键索引,第二列是非主键索引,分别将主键索引和非主键索引全部插入到B+Tree中。两个树的共性都是以page为基本单位,分为根节点、分支节点、叶子节点三层,非叶子节点存放的是叶子节点的索引,叶子节点对应的是数据层,包含完整数据,并且有序,可以在检索的时候提效。非叶子节点上存放的是叶子+主键,也是有序排列,相互指向。B+Tree是一颗平衡树,任一值搜索深度相同。检索深度与IO消耗直接相关。
image.png
层高和数据量
下图创建的table主键是int类型,c1是int类型的非主键索引,c2是一般列,varchar字段。从下图可以得到表结构定义的具体信息,首先是主键的key长度(Clustered index key)是4 bytes,非主键索引key长度(Secondary index key)也是4 bytes,指针(Key pointer)是8个bytes。假设在平均行长度(Average row length)是200个bytes的情况下,page size 是16K,即16384 bytes。节点填充率(Average node occupancy)为70%的情况下,在主键索引中一个page可以存放的数据(Average row per page(Pri Key))是page sizeAverage node occupancy/Average row length≈50行,非主键索引(Average row per page(Sec Key))是page sizeAverage node occupancy/(Secondary index key+Clustered index key)≈1400行,非叶子节点存放的是索引+指针的信息(Non-leaf fanout),所以可以存放的数据为page sizeAverage node occupancy/(Secondary index key+Key pointer)≈1000行。下图表格中给出了不同层高下,主键索引和非主键索引数据量的情况。主键索引下在层高为2时,Non-leaf fanoutAverage row per page(Pri Key)≈50000,非主键索引下也同理Non-leaf fanout*Average row per page(Sec Key)≈140w。其它层高也同理。
image.png
那如果表行数是1000w,分别在主键索引和非主键索引,以及全表扫描下IO消耗情况如何。主键索引1000W对应的层高是3,带来了3次随机IO,非主键索引1000w对应的层高也是3层,但并不能获取到全部表的信息,还需要加上主键的IO消耗,此时等于c1的IO消耗c1数量+回到主键索引的IO消耗=(3c1数量)+1。若按照c2查询,上面没有索引,所以只能从第一行逐步查找(全表扫描),1000w行需要消耗20w的IO。这样的话三种扫描的差距就体现出来了。同时这里补充一下,MySQL可以支撑的数据量与表结构相关,与具体的SQL相关。
查询代价
主键查询优先于二级索引查询,即非主键索引。而二级索引查询优先于全表扫描,单表查询优先于连接查询,表连接数量越少越好,连接查询时的IO消耗等于驱动表的全面扫描*被驱动表的索引消耗,因此可以允许的情况下,尽可能控制join的数量。最后一条是通过使用索引避免排序代价。

二、库表设计规范

表结构设计
第一条设计规范是降低单条记录长度,日高缓存利用率。如果长度太长,每个配置下存放的记录数就会降低,缓存率自然也会跟着降低。可以在业务核心表上降低单表的记录长度。第二条是将访问频率低、大字段拆分,用主键关联,提高缓存命中率。第三条是适当冗余,不要使用多表join查询。第四条是在分库分表场景下,避免数据倾斜。
image.png
索引设计
第一条是选择过滤性高的字段建立索引,即通过distinct(col)和count(*)的比值,判断过滤性。第二条是在Join查询中连接字段建立索引,避免全表扫描。第三条是尽量使用覆盖索引,将Select item后面的列加到非主键索引中,从而避免在非主键索引中回表到主键索引的操作,无需访问主键索引表,避免随机IO。第四条是利用前缀索引,将索引长度变短,单个配置下的索引行数变多,提高缓存率。最后一条是尽量避免建重复索引,提高索引使用率。太多的索引会使得写入性能变差。
image.png
SQL书写
第一条是建议读写都采用主键索引。尽量利用索引排序,避免产生临时表,如order by。避免对查询字段进行计算。避免使用select *,字段少可以配合覆盖索引。避免使用全模糊查询。对表而言,访问的应用非常多,因此访问的SQL也会很多,在索引的设计上好综合考虑,保证核心SQL的访问。
image.png

三、诊断实践案例

在MySQL的实践中有三种案例,一类是SQL优化实践案例,另一类是主备延迟实践案例,还有空间优化实践案例。
SQL优化实践案例
减少磁盘IO访问
下图中对表建立了A、B、C三列的符合索引,前两个案例都使用了A列。第3个和第4个是范围查询.在B+Tree中,如第4个中的A的值不确定时,B的值是无法使用到的。第5个A的值是list,是固定的,A和B都可以被使用到。第6个A值没有,B值也用不到。第7个A值确定,但是B是范围值,因此C值也用不到。
image.png
返回更少数据
以select 为例,直接全部数据返回,对select 语句中写所需的列时,可以带来以下优点。首先是减少网络传输开销,只返回了所需要的列。其次是减少处理开销,还减少了客户端内存占用。还可以在字段变更时提前发现问题,减少程序BUG。最后在转换到Name和ID时,有机会使用到覆盖索引,避免回表数据。
image.png
减少交互次数
Col in()可以代替多次col=?,但注意in的范围不要太多,避免索引失败。第二个是可以使用batch DML操作,此时需要注意平衡,避免锁过大问题。第三个和第四个是阿里云RDS上特有的语法,select from update 和commit on success/rollback on fail hint语法。在,select from update适用与单行update之后获取更新后的场景,避免两次访问的开销。commit on success/rollback on fail hint语法,在提交时直接成功,若失败了可以rollback,适合于高吞吐下的优化。以上方法还是不够的话,可以回到业务逻辑中进行优化。这样的优化可以减少交互次数的网络开销,减少了语法、语义分析,执行计划生成过程中的开销。减少了事务提交次数,两阶段提交成本和IO成本。还可以减少锁持有时间。
image.png
减少CPU开销
下表有A和B列,其中Order by B 不符合符合索引的最左原则。其中最后以个Order by A[ASC/DESC],B[DESC/ASC],如果A和B顺序不一致,在8.0之前就不能使用这个索引,8.0之后可以。通过索引的使用可以减少CPU开销。
image.png
主备延迟实践案例
当发现有主备延迟时,首先要检查主库和备库上的容量,出现的问题一般是主库和备库的资源不一致,备库无法支撑主库的发展,就像水龙头水很大,但是桶不够大。第二个排查点是主库和备库的同步状态,如果符合同步状态,可以检查线程状态是否有锁等待。如果上面的排查不能解决主备延迟问题,还需要进一步深入排查,在主库侧做DDL变更排查,是否有超大事务,这会影响到系统的稳定性。最后在库表设计方面检查是否有不合理的设计,如无主键表,外键约束等。
image.png
空间优化实践案例
空间优化主要从三种文件入手,数据文件,临时文件和日志文件等。数据文件优化方案包括库表结构设计是否合理,检查主键设计是否合理,是否因为delete操作导致碎片放大。还包括冗余索引的检查,还需要定期的对碎片过多问题进行optimize操作。临时文件场景三种优化方案包括通过适当调大sort_buffer_size,避免操作过程中带来性能慢的问题,其次是创建合适的索引避免排序,最后是统计报表类查询考虑换存储。但对数据量大的业务,MySQL并不适合,可以考虑阿里云的RDS。最后是日志文件,首先要检查日志文件里面是否使用了大字段,其次对于没有使用订阅增量的数据,可以考虑使用truncase替代delete from,避免bin log中由于有大量的delete from清空表操作带来的日志文件。
image.png

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

开发者免费资源中心,技术电子书、会议PPT、论文资料持续供应中

官方博客
官网链接