开发者学堂课程【RDS MySQL 高效设计及性能调优 ：RDS MySQL 高效设计及性能调优（四）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/1209/detail/18176
RDS MySQL 高效设计及性能调优
七、RDS MySQL 使用经验
使用经验将列举七个比较常见的场景进行讲解、分享。
1. 主键设计原则
Inno DB 表是具值索引表，因此对于 Inno DB 而言的话，主键是比较有特殊意义的，因为 MySQL 的数据存储它是通过主键进行数据存储，通过主键直接定位到对应某一行数据的物理位置。所以 Inno DB 的任意一张表都应该有主键。如果没有显示的定义主键， Inno DB 也会选择比较符合条件的列作为主键：首先要判断一下表中是否有非空的唯一索引，如果有的话，他会当即作为主键；如果当表中存在多个非空的唯一索引，那么建表时第一个非空唯一索引是作为主键的。如果上述条件都不符合， Inno DB 会自动创建6字节大小的指针。 RDS MySQL 使用了参数去控制是否针对用户没有建 PK 的表，默认创建隐含主键。主键设计原则：第一，比较强烈推荐的使用整性类型，并且是无符号的一些递增长的作为主键， INT和 BIGINT ；但不推荐使用 VARCHAR 类型作为主键，也不要使用有业务含义的字段作为主键。
(1) 为什么会推荐用 INT 和 BIGINT 和无符号自增键作为主键？
因为 Inno DB 表前面也讲到了，它是聚集索引表，表中的数据按照主键顺序存放，如果主键是 AUTO_INCREMENT 是增长的，每次插入数据的话，就像第一页中的数据1 2 3 4 ~100，第二页101-104，当需要新增数据的时候，主键是递增长的，直接在103之后新增到104，直接插入到这个位置，它是顺序写入的过程。如果是非有序的话，先插入的10、100等，在接下来要插入S007主键对应的值，它需要在S001和S010中间去插入数据，这样插入数据的话会导致的新记录插入到合适位置而频繁移动数据，频繁移动会导致大量的碎片，碎片会影响查询的性能，到最后会越来越慢，所以一般是推荐是使用 AUT_INCREMENT 的递增长属性。如果不想用 AUT_INCREMENT 的话，就需要递增长的功能进行序列化，去递增长主键。还有无符号类型主要是增加支持范围。使用无符号之后，比如说BIGINT，它如果设置无符号，支持范围就会扩大更多的一倍。
所以总结来说，第一，主键被希望是递增长、有序的属性；如果使用无符号的话，能够扩大它的一倍，增大它自己的范围。
(2) 为什么不推荐使用 VARCHAR 作为主键？
在存储空间上 VARCHAR 类型占用n的变化类型， BIGINT 始终都占用八个字节。而且二级索引中包含了二级索引和主键列，主键越大，二级索引也会越大。 BP命中率因为数据量的增加会导致BP命中率下降， VARCHAR 会导致 PK 的非叶数节点数量增大，且非叶体节点一般都是热点数据，所以也会导致它的命中率下降。在插入的效率方面， VARCHAR 主键插入一般是随机插入，会引起比较大量的节点分裂，性能比较差， BIGINT 自增主键作为顺序插入，它的性能也会比较好。
下面是实验，在实验中可以看得到相同的数据量，这张表里边的方式、空间其实是不一样的， INT 类型用整型类型就是在1.9G， VARCHAR 类型它占用了2.3G。
接下来看一下这四个表哪个表会是更优的。
第一个表 user id是 VARCHAR 类型，刚刚说了不建议使用 VARCHAR 类型。第二个user id 是整型类型，但是它没有递增长，而且它的主键使用的是联合主键。第三个 id是无业务属性的，id包含了业务水平， id是无业务属性，而且是使用无符号，使用的 BIGINT ，而且是不为空，也是递增长的属性，是比较符合刚刚讲到的一些规则的。第四个表主要就是没有用到无符号类型。这两个表相比较的话，它的主键支持的范围会比小一倍。所以在这样子比较下来看的话，第三个表黄色的是比较最优的表结构设计。
2. Online DDL
Online DDL 对于日常的维护运维来说，是比较重要的一点。比如业务需要增加字段或者这些操作的时候，其实Online DDL 能在增加过程中不只是正常的 DML 的显露。现在从 MySQL 版本新版本也都支持 Online DDL 。像类型、Copy 表副本进行原表 Copy，类似的INPLACE 是在原表操作，避免复制表，这种场景下一般是允许并发 DML 的，一般是在开始和结束阶段会获取短暂的获取元数据锁。INSTANT 主要是修改原数据中的元数据，避免了复制表和元数据所、锁，操作速度也会非常快，只是针对表构建系统的变更，允许并发 DML ，这是从8.0版本后才开始支持。接下来是需要设置的锁，在 DDL 期间允许并发执行 DML 操作，不堵塞 DML 。 SHARED 是在 DDL 期间允许读，不允许写操作。 EXCLUSIVE 是 DDL 期间不允许任何操作。 DEFAULT 是 MySQL 自己判断使用的模式，要尽可能不堵塞 DML 操作。原生 Online DDL 不兼容的变更，可以使用 DMS 提供的无锁结构变更、pt-osc、gh-ost 等工具，pt工具是用于开源的。
判断是否允许并发 DML
可以在官网当中看对应的 Online DDL 的操作。
举例看一下主键的变更，增加主键的时候，其实是采用了 INPLACE 尽量避免复制表，可以允许 DML 的操作，它会创建新表，然后它会允许 DML 的操作等。可以根据表里面标识的 yes 和 no 进行判断。job 主键是不允许写操作的。再来看一下从键的索引，它其实是能够允许去做 DML 的操作。
3. 分页查询
在 MySQL 当中查询一张表，然后 ORDER BY id，进行 limit，然后去翻页查询，我们可以知道在 MySQL 当中，翻页到最后性能会越来越慢，因为 MySQL 当中读取的是表前 m+n 条数据， m 越大性能也会越差，因为它所查询的数据量也越高。来看一下 select *from 的一张表和 id 大于 max id 、order by id 这种是适合顺序翻页的场景，每次记录上页，将 max id 带入到下一次查询当中，主要是减少 m 即前面已经查询过的数据，不要再去查询，从而减少这个查询的量。然后第二个 select 是适合于 id 进行分页，且没有 where 条件，将主键 id 放在子查询里面进行查询，原先查询主键在哪个值，然后再返回表里面去获取对应的这些id字段所需要的所有数据，这个可以提升产品效率。然后是带 where 条件的，同时也是在主键当中先将主键 id 查询出来，然后取出所需要对应的查询 id，然后在原表进行join连接，这种也会节约查询效率，所以说在 where 条件下创建二级索引可以加速查询效率。
来看一下实验
SELECT 的一张表，100-10查询效率是很快的，当查询1,000万的时候，这个速度立马降下来了。然后第三个是 select id大于1000，这个查询效率也会非常快，因为就直接定位到第一千万行的这条数据，之前1,000万行数据都不会进行查询，但是上一个的话会进行查询。
接下来来看一下带 where 条件的，带 where 条件的只查询id某值，然后 order by 取1,000万之后的10条数据，让他查询了2.48秒， select 进行整个 a 表所有数据和刚刚上面查询的结果进行 join，查询实际效率在两秒之内就查询出结果了，因为它主要时间还是耗在查询当中，然后再跟这个原表进行主键关联招引，然后反映出结果。第三个也是查询 id，然后返回这个值，然后进行 join 连接，这个是全角扫，下面这个它其实是进行了 where 条件的查询。可以看到有 where 条件，并且查所有数据，翻页查询的时候已经耗时了12.73秒。先在子查询中查询主键的结果再进行 join ，获取主键下面行的数据其实时间会节约到很多，可以看到提升了有差不多六倍。
4. 大标数据清理
大表数据的清理场景有历史数据的清理，通常是按照条件，如时间范围进行 delete 数据。但有两个问题，第一就是单次 delete 的行数过多，执行效率过慢会造成锁堵塞，事务过大主从复制延迟，影响线上业务；而且进行 delete 是不会直接立马释放磁盘空间。建议的话是使用这样的方案：根据主键最大值和最小值进行分批删除，并且根据时间处理。例如id大于最小值，然后最小值直接加1000，然后查询某个时间段每次只查询在1000行里边的主键数据进行删除，然后定期进行回收碎片。这样的话主要是降低每一次扫描的行数和删除的数量，然后进行优化。全表数据清理，建议是用 Truncate 进行整张表的删除，比如大表的话， Truncate 的话一定会发生 io 抖动，建议使用 RDS MySQL 异步清理的功能。
5. replace into 问题
replace into 的逻辑是先 delete ，再 insert 。它会产生一些问题。比如现在列出来的PK的冲突，此时如果 auto_increment 并不增长，主键 id 与元数据 id 保持一致；UK 冲突是 auto_increment 增长，主键 id 会增长，但 binlog 记录的是 update 语句。存在的问题是当 UK 冲突的时候，主库的 auto_increment 值增加，同时 binlog 记录的是 update ，当同步到备库的时候，备库执行 update 并不会改变在备库上的 auto_increment 的值，于是造成了备库在此场景上会存在不一致。我们进行一个测试，先进行数据插入，插入完之后进行 replace into，将 id=3 的值进行更新，更新的时候会发现它的主键递增长的 id从3变成了4，然后再去看 auto_increment 这个值已经变成了5。因为刚刚更新完它是4然后也变成5，看一下 binlog，其中会进入 update ，原来的这条数据做了变更，从3变成了4，然后 v 里边的3变成了1-10的值。然后再来看一下备库里边的表结构，它的这 auto_increment =4是没有做变更的，像主键的这个是=5，这个时候如果发生了主备切换，这个表就会变成主库，主库再插入一条数据的时候，将会从4开始递增长，这个时候就会出现主备冲突了。因为在这个表当主键它再插入一条数据的时候，主键 id 是会赋成4的的，而不是5。4的话在这个表已经变成了备库，备库中其实4已经存在了。主库写完成功之后，同步到备库的时候，备库就会变成了就会发生主键冲突。所以在 replace into 下，是不建议在生产业务上进行覆盖写入的。
6. RDS 只读实例复制延迟
RDS MySQL 只读实例延迟是基于原生的 binlog 复制技术一部分进行同步的，有异步复制或者半同步复制。在业务层面出现大事务等这种场景下，之后肯定会造成备库存在延迟，延迟会导致只读实例和主实例的数据出现不一致，如果业务去访问只读实例，数据存在不同步的话，可能会存在问题。延迟过大也会引起 binlog 的堆积，导致只读实例的空间会迅速被消耗。比如主实例当前正在产生大量的 binlog ，写入速度跟主库相比的话会比较慢，它所占用的空间会越来越大，也会导致只读实例因为空间满而被锁定。常见的延迟原因和解决方案：第一个，只读实例规格过小了，因为过小之后同步主库的速度的话会比较慢，会造成延迟。所以一般是建议只读实例规格避免较小，导致延时。推荐是跟只读实例大于或者等于主实例的配置；主实例的 TPS 过高，排查 TPS 可以考虑业务进行优化或者拆分，保证主实例的 TPS 不会导致只读实例出现延迟；主实例的大事务，建议将大事务拆分成小事务，例如 delete 语句，可能 delete 差不多几千万或者几十万的数据，这个数据已经算是大数，会造成锁堵塞，重复性更新的时候也会更新过慢。限制每次删除的数据量，然后将每一次的删除拆分为多次较小的删除，来提升单事务的执行效率。主库的 DDL 造成只读延迟的话，建议在业务低峰期进行，或者采用 DMS 无锁表的结构设计功能。无主键表建议是给每张表添加主键字段，因为在 RDS MySQL当中，目前已经支持对表添加隐式索引，但是对于历史创建的一些表需要进行重建才能支持隐式主键。
为什么推荐给每张表都建附件？因为主库里边它可能没有主键的表，可能去做一些动作的时候是大量一次性扫描，但是在备库就不是了，备库每同步一次，做一次执行，是每条数据进行更新，每条都会进行全表扫，就会导致备库的执行效率非常低，所以是非常建议每张表都要添加主键字段。
7. RDS MySQL 扩展性
RDS MySQL 扩展性可以有横向扩展，通过增加处理节点的方式来提高整体处理性能，例如 RDS MySQL 可以通过添加多个只读实例横向扩展能力，这种场景比较适合读多写少，大部分读可以接受偶尔的延迟。
纵向扩展项目可以增加当前处理节点的处理能力，提升整体的处理能力，像 RDS 进行规格的分配，然后提升读写能力。适用场景就是读写比较高，同时对读时效性也非常高。
垂直拆分：比如像上面讲的两种方式都无法满足业务的时候，可以考虑按照业务进行垂直拆分。例如可以按照会员、交易、库存、营销进行分实例进行存储，还要进行提供业务。
水平拆分： DRDS 支持自动水平拆分，提升数据存储容量、并发吞吐、复杂计算等方面的需求。它高度兼容 MySQL 协议和语法，接入成本也会比较低。这种场景比较适合超高并发，大规模的数据存储，互联网在线事务类的业务，数据不断扩充，急需更强计算能力在线事务型数据库。
