开发者学堂课程【数据库常见问题排查：常见问题排查案例】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/68/detail/1168
常见问题排查案例
内容介绍：
一、SQL 优化实践案例
二、主备延迟实践案例
三、空间优化实践案例
一、SQL优化实践案例
1.减少磁盘IO访问
（1）使用索引检索
如上图案例，对这张表建立一个 A，B，C，三列的一个复合索引，在三列复合索引的情况下，下面场景是否都使用到了对应的这个索引。第一列没有问题，第二列也没有问题，它们俩的区别，一个等值查询一个范围查询，他们都使用到索引项里面的A列，第三个案例和第四个案例，他们两个差别在于第三个 A 是等值差距，第四个A是范围查询,当 A 是范围查询的时候，A 的值是不确定的，索引对 B+Tree索引来说，它都有一个最左原则，那当A的值它是不确定的时候。B 是没有办法去明确的，所以在第四个案例里面，只能使用到 A 列，并不能去使用到 B 列。第五个案例，A 是一个固定的。就是 in list，然后在里面的话，A 和 B，因为A的值是固定的，所以 AB 都可以去使用对应的索引。第六个案例，A 值没有，因此 B 是没有办法去再去用的。第七个案例，A 值是确定的，但是 B 是一个 range，C 是没有办法再去继续用到。通过案例可以清晰的认识如何建立索引和避免冗余索引。
（2）使用覆盖索引
下面来结合案例来去看一下，是否能去用到覆盖索引。上面的 ABC 三列的索引。当是 A and B。A 是等值，B 是一个range的情况下，它的非主键索引里面有 ABC 三列，非主键索引的 B+Tree 里面，他是 A，B，C 然后再加上他的主键，作为它的叶子结点里面存放的数据，所以避免对主表的回表，那是能去使用到对应的覆盖索引，因此就是在案例里面，a 和 B 之间的范围查询，是可以使用到覆盖索引的。下面案例的 a 是一个明确的值，然后是可以为对应的覆盖所用的。
2.返回更少数据
只返回需要的字段
Select*from product where company_id=?;
优化：select id,name from product where company_id=?
我们在 SQL 优化里面返回更少数据的优化案例。然后直接以Select*来例举例子。这也是在云数据库使用上面最常见的一种习惯，就是最简单最便捷，Select*不需要再去扩展，直接全部数据都返回，然后再根据真实需要再取具体的列。书写的时候很便利，但带来的问题比较多。
优点：
减少网络传输开销（只返回了必要的列）
减少处理开销（不需要进行对应的转换）
减少客户端内存占用
字段变更时提前发现问题，在业务层面规避，减少程序 BUG
有机会使用覆盖索引，避免回表操作
3.减少交互次数
-col in() 代替 多次 col=? (in不要太多，避免索引失效)
-batch DML 操作
不要过度涉及，当 batch DML 操作过多可能会有锁占用和事务过大。
-SELECT FROM UPDATE 语法
UPDATE 语句返回影响的行数，SELECT FROM UPDATE 避免了两次以上的访问。
-COMMIT ON SUCCESS / ROLLBACK ON FAIL hint 语法业务逻辑优化
ID 等于一的时候，完成这个对 Data 这个修改，同时把 ID 和 Data 的数据，然后直接返回修改，然后减少一次网络上的开销。下面一个案例的话是 COMMIT ON SUCCESS 和 ROLLBACK ON FAIL hint 使用。这两个的语法很容易理解就是提交的时候，如果执行成功了，就直接提交，然后避免再来一次提交，如果失败了，然后就默认提交 you back。对于一些应用来说并没有依赖到他，然后这两个语法或者是减少交互次数优化，是适合于对业务要求高，要一个高吞吐的这个场景的一个持续优化，当然如果这些点都还不能满足的话，或许还没有达到对应的点的话，就要回到这个业务逻辑里面。可以把刚才的这样子优化的思路带到业务里面。
优点：
减少交互次数的网络开销
减少语法/语义分析，执行计划生成过程
减少事务提交次数(两阶段提交成本，IO 成本）
减少锁持有时间
4.减少 CPU 开销
下列查询条件可使用索引（红色部分不能使用索引）
ORDER BY A
ORDER BY A,B
A=5 ORDER BY B [ASC/DESC]
A>5 ORDER BY A [ASC/DESC]
A>5 ORDER BY A,B
ORDER BY B
×ORDER BY A [ASC/DESC],B[DESC/ASC]
第一个的话是他没有符合索引的最左原则，第二个的话，这里要说明一下，如果 A和 B 他俩的排序是不一致的，然后在8.0之前，这里是没有办法去使用到索引的，在8.0和8.0之后的话，然后这里是可以通过索引的一个合理使用。减少 CPU 的开销。
二、主备延迟实践案例
当你发现有主备延迟的时候，然后第一个要做的事情。主库和备库上的一个容量，他的吞吐的一个情况，发现比较多的还是备库的规格和主库的规格不一致。然后备库没有办法支撑到，就随着业务的发展，数据库上的流量越来越大，但是备库规格相对比较低，是没有办法去支撑到主库过来的流量，那这块的话自然会出现，水龙头特别大，然后到后面桶撑不住这样的问题，所以出现主备延迟，优先去检查一下主库和备库的规格是否一致，这是第一个排查的一个点。
第二个排查的点，看一下主备同步状态是否正常的，在主备同步状态都很正常情况下。比如，同步并没有断开，然后在这些情况下，可以去看一下 IO 线程是否有锁等待的情况。如果发现不能去解决掉主备延迟的情况，那就要进行就是比较细致，比较深入的一个排查，就是在主库要进行下面的这些排查，第一个是不是有 DDL 变更，正常的话在做 DDL 变更的时候是有一定时延的。第二个是不是有超大事务。对于超大事务，是一定要在业务层面进行这样的控制，因为主备延迟过大的话，是会影响到整体系统的稳定性。之后要看在这个库表设计上面，是否存在一些库表设计不合理，比如是没有主键表，如果是没有主键表，在主库上面，然后进行的是一个索引维度上的一个更新，但是到备库上面发现没有主键表，那会带来就是同步延迟非常大的一个放大。
三、空间优化实践案例
常见的数据库下的空间，基本上是有三大类，数据文件，临时文件和日志文件。然后结合这第三种文件，看这三种文件下面空间优化如何来进行。
数据文件的就是库表和库表结构，库表存放的数据，库表结构设计，以及存放的数据相关的，如果出现数据文件过大，主键设计是否合理。然后是不是完全有序的，因为有Delete等操作会导致就是碎片放大。
第二个冗余索引，然后索引也是要去占据，我们有提到刚才的就是主键索引，主键索引 B+树，然后非主键索引 B+树，然后如果索引过多的话，一样的会占用数据文件的空间。
第三个，针对刚才提到的如果碎片过多的话，碎片过多的一个判断的话，就是你可以结合的业务维度上面来看，你也可以去看系统文件，然后需要进行定期的optimize 操作。这是对于数据文件常见的优化的三种方式。
接下来看临时文件，临时文件常见的三种的优化方案。第一个通过调大sort_buffer_size 来去避免在这个过程中他的一个入盘带来的性能慢，空间增大等等问题，下面有一个案例，临时文件达到了 2.1T，执行了十几个小时的 MySQL。临时文件 SQL 没有写好，导致临时文件被放到2.1T，整个实例被锁定，然后是地址不能去被写入。临时文件又会回到这个 SQL 优化，对于数据库这个大体来说的话是环环相套。
然后下面一个是创建合适的索引避免排序这里刚才在 CPU 优化那个环节有提到，另外一个，对于统计报表类的一个查询考虑去换存储，MySQL 是适合于 TP 类型的数据库，对于大的多维的以及报表类的一个分析的话，然后建议去采用到 AP 的存储下面，然后合理的去使用。尤其是数据量越来越大的情况下，MySQL 并不是很适合他。这里可以通过前面讲到的 MySQL 的基本原理，随着数据量的增大，还有层高的增大，以及从报表内查询他的 SQL 维度很难再去支撑他，因此就是要考虑换存储。
最后一个是日志文件，在数据库的使用上面如果发现日志文件过大，然后要看是否日志文件里面使用了大字段，大字段是否存放在数据库中适合。这是第一个，第二个的话是对于没有订阅增量的数据，对于没有订阅增量的这个业务来说，可以考虑使用 truncate 来去替代 delete form，然后来去就是避免有大量的这种清空表的语句，然后带来的日志文件上的一个增加。
