开发者学堂课程【RDS MySQL 高效设计及性能调优 ：RDS MySQL 高效设计及性能调优（三）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/1209/detail/18176
RDS MySQL 高效设计及性能调优
六、 RDS MySQL 开发规范和建议
该部分将从六个方向进行讲解，从字符集，字段类型的设计、索引设计原则，到库表结构规范，编写 SQL 规范，然后到上线发布的规范。
1. RDS MySQL 字符集
字符集就是字符与二进制字节之间的映射表，RDS MySQL 支持了几十种字符集，如果在前期没有规划好字符集，后期可能会出现部分数据无法正常存储，如果后期需要更换字符集的话，代价也会比较高，而且也存在一定的风险。
如何选择字符集？接下来将从兼容性、可预见性、储存空间、统一字符集这几个方向来讲。首先来看一下兼容性：不同的字符集所支持的字符范围是不同的，建议选择范围广的，比如 utf8 utf8mb4等；可预见性即随着业务的发展，要去了解业务，后面会支持比如一些表情；当业务涉及到海外，要考虑到字符集涵盖的范围。对 MySQL 来说，是可以使用 utf8 和 utf8mb4 的。存储空间上理论应该选择范围更小，可以节省数据空间，但是从存储空间和兼容性和可预见性来相比较的话，会更推荐优先考虑兼容性和可预见性。
统一字符集在生产中建议将关联应用的 MySQL 字符集进行统一，这样能够避免用字符集带来的开发和运维的复杂性。
RDS MySQL 字符集选择：
在字符集的选择上，常用的字形型像 latin1 、gbk 、utf8 、utf8mb4。其实主要会主推 utf8 和 utf8mb4。 utf8 和utf8mb4 相比较而言，更推荐 utf8mb4。因为 MySQL 的 utf8 并不是真正的 UTF8 字符集，他在 MySQL 的 utf8，它只有三个字，虽然节省空间，但不能表达全部的优点。utf8只能支持基本多文种平面，utf8mb4 在生活当中是才是真正支持 UTF 的编码。那么从 MySQL 官网从8.0开始，默认字符集就是 utf8mb4，utf8mb4 只是 MySQL 中的概念，应用程序中使用 UTF-8 即可；不同的 MySQL JDBC 驱动对 utf8mb4 的处理支持方式不同，具体的话可以参考链接 https://dev.mvsal.com/doc/connector-i/5.1/en/connector-i-reference-charsets.htmi 里面的内容，我就不再做具体的讲解，大家如果需要了解的话，可以根据这个链接进行进一步的了解。
2. RDS MySQL 字段类型
(1) 整数类型
MySQL 除了标准的 INT 和SAMLL INT外，还扩展了 TINYINT 、 MEDIUMINT 、 BIGNT。
可以看到这个表是它每个类型所支持的范围。 INT 展示类型它还有一些其他属性，INT括号里面M支持了指定数值显示的宽度，对于存储数据范围和存储空间它是没有影响的；无符号是因为整数还包含了负数范围，如果选择了无符号的话，它会将最大值增加到一倍，主要是用于主键还有自增键。因为主键一般都是从1开始递增长，它不会有负数，所以说它用无符号的属性进行标识。ZEROFILL是零填充，当存储的数据小于M宽度的时候，左侧会用0去填充。使用这个属性的时候，会自动添加无符号的属性。
看一下实验，首先创建了一张表：c字段它是 int（5），5个长度，然后是 ZEROFILL 进行零填充。
进行插入数据可以看得到：第一列数据，c 字段设置了零填充的这个属性，比如1前面还缺失的部分，它会用零进行填充；第二行是大于配置的字符长度，就不需要去填充了。当将 a 字段进行变更，把某值进行变更，如果超过它支持的长度的话，也会提示。
可以看到现在去 set t 表 a 等于这个值，然后 c 等于另一值。Volues 是 a 所带这条数据。对 a 字段跟 c 字段进行更改，可以看到 a 字段它的类型是 int 类型， int 类型的取值范围，在上边已经有了 ，可以看一下 int 的类型，最大值是在2147483647，那去进行 set 值的时候是2147483648，它比这个值还要大1，这个时候它会提示 warings，即 a 的值已经超过了你的范围，再去看一下这个表，执行出来查询之后其实是没有写成功的，那他还是保持阶段显示最大值。但是可以看到c字段因为配置了 ZEROFILL ，其实就会添加无符号属性，它所支持的范围会比 a 字段大一倍。他就可以写成功了。然后接下来看一下无符号的属性，再看这张表，c 字段是自动加了无符号属性。看一下自动属性，建了 t1表，a 字段是有增长字段属性的。当 t 表插入，可以看得到 a 字段是从1 2 3 4分，然后对 b 表的值进行数赋值，1的话会自动进行递增长，这是三个属性的介绍。
(2) 浮点数
接下来看一下字段类型-浮点数。 MySQL 浮点类型包括 FLOAT 、 DOUBLE 、DECIMAL。前两个是近似值，而不是精确的值。 DECIMAL 存储是确切的数值，使用它可以保证精确度，例如可以存储金额数据。这一块主要注意的一个点，比如像金融行业要存储一些与金融相关的内容，一定要使用 DECIMAL 进行存储。下面是一个测试：
可以看到在使用 FLOAT 类型跟 DECIMAL 的类型的精度是2，insert 的值插入之后和两个执行插入的结果是不一样的，a表a字段会显示最后的末尾是00，但是 DECIMAL 类型会将具体值直接展示。看一下 DOUBLE 类型跟 DECIMAL 类型比较：insert 在 b 表里面插入一些值，小数点是三位数，a 表是 DOUBLE 类型，显示 a 字段的值是956.745，进行截取，取两个小数点就会显示956.74；b 字段是956.745，后面有很多的0，因为设置是有10个小数点位。进行截取。会进行四舍五入，变成了956.75，因为进行四舍五入的话，是向会向前进一位。
(3) 字符类型
接下来了解一下字符类型， MySQL 当中的字符类型包含了 CHAR 、 VARCHAR 、BINARY.、 VARBINARY 、 BLOB 、TEXT 、 ENUM 、and SET这几种类型， CHAR 、 VARCHAR 里面的M代表的是字符的个数，并不代表字节数，这也是它的存储限制，还有它的特点和适用的范围。当字符串的长度是固定的话，推荐使用 VARCHAR ，例如名字或者或者手机号、性别，可以用 VARCHAR 类型进行存储，因为它是可以固定长度，而且是可预见长度的。 VARCHAR 类型它更适合于可变长度，需要额外的字节存储长度，并且会保留末尾的空格。 VARCHAR 类型要根据具体习惯选择比较适合的范围，比较无限去扩大，比如预计可能的预算可能是在100个字符之内，但是给了200、300甚至400，其实这是不合适的，可以根据实际业务去尽量设置它的平均长度。 BLOB 、TEXT 用于存储大量的数据，像一些短篇文章。 其实不太建议使用 ENUM ，因为它非常的不灵活，通常会使用 tinyint 、 smallint 进行代替。
可以看一下相关的一些测试，这是测试结果：
CHAR 类型最长是255个字符，无论英文中文都可以看出255个字符，大于255字符它会进行截取。 Utf8下每个中文字符下占用3个字节，可以看到255个中文占用了255×3个字节。、VARCHAR 类型在不同的字符是行格式前提下，存储字符是不同的，因为所支持的字节长度是固定的，所以它每个不同的字符集的字符所占用的字符字节数是不一样的。通过实验可以进行结论查看。
(4) 时间类型
接下来看一下时间类型，时间类型有 DATE 、TIME 、DATETIME 等。在日常使用只是要记录到日期时，可以选择 DATE 类型；但要固定到就某个时间段的话，可以用 DATETIME ；当需要有跨业务场景的话或跨时区的业务场景的话，用 TIMESTAMP 更合适一些。 TIMESTAMP 有比较多的限制，范围只能到2038年的1月19号的时间范围，范围会比较受限。
来看一下使用 TIMESTAMP并进行时区变化。创建一张表，它使用的 TIMESTAMP 和 DATETIME 两个不同类型。
进行当前时间的插入，可以看得到，查询的话，是在2020年3月4号，然后在某个时间点，当去变更它的时区的时候，可以看到 DATETIME （DS 即为 DATETIME）其实是不会有变化的， DATETIME 根据时区设置成不同的话，它的时间是不同的，这里设置的是一个时区，重点更改了一下时区之后，这两个时间值是不一样的。 DATETIME 它还支持当发生更新的时候，会获取当前的时间，这两个属性都支持。
第二个实验来看一下 TIMESTAMP 和 DATETIME 的自动初始化和更新。
DATETIME 和 TIMESTAMP 同时支持默认值和更新时间戳为当前时间戳。当进行插入的时候，可以设置成当前的默认时间，然后如果发生更改的时候，也可以设置当前的时间跟 TIME 有相同的成功支持； DATETIME 和 TIMESTAMP 都支持以默认为当前的时间戳； DATETIME 和 TIMESTAMP 它都支持只指定默认值为常数，其实这个时间它就会全是零点的时； DATETIME 和 TIMESTAMP 它都支持指定默认值为常数，同时指定更新时间戳作为当前时间，可能刚插入是默认的00的时间段，当发生更新的时候会获取当前的时间。 TIMESTAMP 类型除非显示 NULL 的属性，否则 TIMESTAMP 默认值它都是为0的，指定为 NULL 的属性，默认为 NULL。 DATETIME 除非显示为 NOT NULL 的定义取值定义，否则 DATETIME 默认值都为 NULL，指定 NULL 属性之后默认值为0。
RDS MySQL 字段类型-空间数据类型在5.7之前，只有在 MyISAM 引擎上是支持的；到5.7之后，通过 InnoDB 的一些存储引擎，也开始支持原生地理空间数据类型，然后还有新增R树索引，即地理空间的查询；在8.0之后的版本，它对于坐标将拥有单位然后等支持更完善一些，如果要使用空间类型的话，建议进行充分的测试之后再进行使用。
(6) JSON 类型
JSON 类型个体的话目前是从5.7.8版本开始支持，它可以自动验证写入的文档是否是正确的 JSON 格式；在8.0.3之前不支持设置默认值， JSON 类型上，8.0之后的效果会更好一些。大家如果要用 JSON 的格式类型的话，要进行充分的测试。这块就不过多的讲解了。
(7) RDS MySQL 选择合适的数据类型
字段类型长度尽量按实际需要进行分配，不要随意分配很大的容量。整数如无负数需求，存放整型数字使用 UNSIGNEDM 型，比如自增字段，它可以将所支持的数值范围扩大一倍。浮点型高精度要求场次建议使用 DECIMAL 类型；对精度要求不高的性能场景下，可以使用 DOUBLE 类型。字符层面定长或小于255个字符建议是使用 VARCHAR 类型，变长或者最大长度远超于平均长度的话也可以使用；建议使用 TINYINT 来代替 ENUM 类型；尽量避免在线业务使用 BLOB/TEXT 类型，如果要选的话，建议是单独拆分到单表里面去。例如一篇短篇文章的存储，将文章的内容进行存储，会给它加个文章编号。文章它会有很多的属性，比如什么时间创建的、作者是谁等这些属性可以单独建立一张表进行维护，如果查这个文章具体内容，就再关联到文章表进行展示。这样的好处就是比如要增加一些文章相关的属性，在属性表去变更，变更难度会减少很多。时间无特殊要求的话，建议是使用 DATETIME ，因为它有范围的优势， TIMESTAMP 只使用到2038年；有跨时区需求的话，可以选择 TIMESTAMP ；如果只存储日期的话，建议使用 DATE 类型。空间和 JSON 是从5.7之后开始逐渐支持和完善的类型的，如果有需要，要根据实际需求充分验证，再去考虑使用。
3. RDS MySQL 索引
索引是用于数据库系统高效获取数据结构的功能，数据库索引它本质上是以增加额外的写操作用于维护的，以空间存储空间为代价去提升查询效率，所以可以帮助快速定位到数据，而不是每次需要索引的时候都遍历数据库每一行。
索引有它的优点跟缺点：索引可以减少服务器的访问数量，提升查询效率；唯一索引能保证数据的唯一性；利用索引对数据存储的特性，可以查询语句避免排序和创建临时表；索引可以将随机I/O变成顺序I/O。缺点就是索引的创建和维护都会造成额外的工作量；占用的存额外的存储空间；降低增、删、改的性能。因为进行数据的插入会对应在索引的存储，额外也要进行重新的排序。所以这一块是有一定的性能损耗的。
首先在上线之后才添加索引，是一种错误的开发，远远会比在开发设计阶段创建索引花费更多的精力跟代价。例如某一张表，他可能在前期设计的时候业务量没有么大，可能他就只有几千条，进行查询某一个值的时候可能很快就能反馈结果，但是随着时间的推移，这月不会出现，可能在两个月三个月之后，它就是变成 MySQL，会增加整个数据库性能的压力。还有就是过多无用、重复的索引对提升性能这件事情是没有任何好处的，因为重复的索引，再去做写入操作会引起性能损耗，无用或重复的索引可能会需要进行删除或进行优化维护。
数据结构
B-tree索引：是 innoDB 中最常见的索引，是最常用而且是有效的索引。分为 Clustered Index 和 Secondary Index，即聚集索引和非聚集索引。
聚集索引可以理解成是主键索引，因为在 MySQL 当中，数据存储的物理存储规范顺序是按照排序顺序进行存储的。可以看到主键的复制存储架构，主键的索引会进行合理排序，叶子节点里面存放了整张表里面的行记录，像15、30Bob，其实是一行数据，第二行数据、第三行数据、第四行数据是按照主键顺序进行存储的。 Secondary 的二级索引是在创建的时候辅助索引，辅助索引的非叶子节点其实都是索引字段，叶子节点是包含了主键这一块的字段值。当要使用二级索引去查询某一行、某一数据、所有行的值，比如查找 Alice，它会去获取 Alice 相关的图表里面其他字段的信息，他需要去回到这个主表里面进行查找18在哪然后进行获取。辅助索引的查询成本就是自身的成本，再加上聚集索引这一块的查询结果。
B-Tree 索引的策略方面：在索引的选择性上，选择过滤性比较好的字段。查看字段的过滤性可以通过这样的方式：它越接近1，过滤也就越好，因为它的重复性越低。在索引顺序上会涉及到最左匹配原则、前缀索引这些原则去选择索引的顺序，应该是哪个字段排在前，哪个排在后。看一下索引的计算，如果字段有参与计算，是不会给索引的，所以字段在查询过程中是要保持干净的，不能参与任何的计算。最左匹配原则在复合索引中，比如要两个字段组合成一个索引，查询条件当中如果包括最左列的话，是无法使用索引的。对于查询范围只能利用索引的最左列。前缀索引是字段计算前缀部分的选择性比较高，可以使用前缀索引；字段整体选择性不太大；字符类型索引长度默认的话，是不要超过767字节数，覆盖所有索引是当查询条件和返回的字段都在索引中包含时，可以直接从索引表中返回结果，不需要回到主表里面去查。所以说要尽量精简返回要查询的数据，例如这个二级索引表，我只需要查Alice的年纪，例如18是年纪，去查到爱丽丝，条件是名字 =Alice，只要返回 Alice 的名字跟年纪，只在这一张索引表里边能够达到结果，而不需要再回到主表进行查询，这个就是覆盖索引。索引排序对于多列排序，需要所有列的排序方式是一致的，才会用到索引。对于 ORDER BY 、 GROUP BY 、DISTINCT 上的字段需要添加索引来避免排序；冗余和重复索引、未使用索引会增加额外维护成本，浪费存储空间，所以尽量把这类不需要代码删掉。
看一下这些类型中哪些不可以用到索引。这个表里面它的 id 是主键，建了联合索引、复合索引，像 user id、msg id 组合了一个索引。先来 id=5 的索引。 id 在范围5-10之间，范围查询会走主键，所以 id+1=5这种场景它不会用到索引的，因为这是掺合了计算。 use r id=5 msg idBETWEEN 5-10，是会用到索引的，因为它满足最左原则。索引排序是 user id 在前，msg id 在后，正好查询也是 use r id=5 msg idBETWEEN 5-10，这个是可以索引的。再来看一下user id 进行的范围查询，只会用到部分索引，user id 进行范围查找之后，从这个范围查找结果当中再去找 msg id的。在5.7之后 ICP 有相关的优化，userr id 某几列某几个值，按照msg引入，会走二级索引。Msg id=5 是不会走索引的，因为msg id在最右边，最左边的 user id 没有使用上，这种场景下是不会使用索引的。 user id=5 大于 msg id 的时候它是部分使用了，就可以 user id=5 这个部分它是会用到二级索引。 ORDER BY 的 id 符合最左原则，也会使用索引。user id=msg id 正好符合最左原则，user id、msg id 是顺序的。 ORDER BY 、user id 和 msg id，因为都是同时进行，升降序三项是相同的，也会用到索引。user id=5 ORDER BY 进行排序也是会用到索引；user id大于5 ，msg id 进行排序，这个是一部分用到索引；msg id 大于5的话，这个部分会用到索引，像 user id 大于 5 ORDER BY msg id 进行排序，这时用到 user id 和 msg id 索引；单独去对 msg id 进行排序，它不符合最左原则，因为 msg id 是在最右边，user id 在最左边，没有使用上，这种场景它也不会用到 user id 二级索引；然后进行 user id 排序和 msg id 排序，如果它的排序顺序不一样的话，可能只会用到 user id 。
HASH 索引：在 innoDB 存储引擎使用的 Hash 索引是自适应的，并不能够人工创建显示 ；MEMORY 引擎支持 Hash 的，
FULLTEXT 全文索引：从5.6开始支持，目前只支持 CHAR 、 VARCHAT 、 TEXT 类型。缺点是全文索引导致占的磁盘空间会比较大；而且更新字段的话，全文索引是不会自动更新的索引，需要人工的定期维护；使用全文索引不是对应用透明的，如果想要利用全文索引，在查询的语句要加上指定的写法。
R-Tree 索引：是空间类型的索引结构。
4. RDS MySQL 库表结构规范
这里列了一些常见的规范要求，像库名、库表名被希望是跟业务模块挂钩的，因为这样具有关联性，便于识别，能很快的识别出user 库、user_login、user_info 的表是做什么业务的；使用 innoDB 存储引擎；表必须要有主键，推荐使用 BIGINT unsigned AUTO_INCREMENT 这个产品作为主键 PK ，第一个 BIGINT 的范围很长，使用无符号特性，长度扩大了一倍，使用率增长，主键是按顺序增长，进行顺序写入；接下来是结合实际的需求，选择合适的字段类型；同名字段只表示相同的含义，且数据类型保持一致，这个挺重要的。因为通常中会遇到，就比如说a表是记录的user id 是整数类型的，然后b表它的 user id 是 VARCHAR 类型的，导致数据类型是不一样。但是这两张表里面user id 表示的含义是相同的，当进行表连接，就比如说有业务相关是要将两张表的数据进行连接、查询会发现他是无法走索引的，也会影响查询效率，所以要求同一字段，它只表示相同的含义，而且字段类型是保持一致的，需要join关联的字段的话，数据类型也要保持一致。表中所有字段一定要设置为 NOT NULL ，业务可以根据需要定义默认值。表中默认值添加创建时间、修改时间，主要会用于开发运维。库表字段的字符集要保持统一，字段和表分别添加注释，尽量规避使用外键、存储过程、存储函数、视图等非标准化的一些语法。尽量规避使用分区表功能，通常是建议使用分库分表，就是 DRDS 产品。建议对表里面的 blob、text、等大字段随机拆分到其他表里，这样仅需要读这些对象的时候，才去单独的表里查询。禁止在数据库中存储明文密码。
5. RDS MySQL -编写 SQL 规范
编写 SQL 的规范： SELECT 语句必须指定具体字段的一些名称.名称禁止使用 SELECT ，因为 SELECT*会将不同的数据从 MySQL 里面读出来，浪费了系统资源，而且当字段一旦更新业务，model 层没有更新的话，系统层面会报错。Insert语句指定具体的字段名，不要写成insert into t1 values，也不写具体来插入对应的这个字段值，道理是跟上一年讲到的是一样的。尽量避免全表count，特别是大表，我们经常会遇到像手动执行超大表的 count，执行好几天都没有结果，然后最近遇到一个case的话，就只读实例上面执行的大表的状态加了一些锁，然后导致只读实例进行数据同步同步主库的，因为拿 binlog 的进行同步，然后刚好写到这张表的时候被这个大表的大 SQL count 给锁住了，导致它无法进行变更，无法进行更新，因为它是进行容量的更新，所以他一直都阻塞的，然后导致主从数据一直延迟。只读实例的数据持续延迟了好几天，他在业务生产业务层面的话，也会有一些有一定的影响。要避免批量的操作，比如 insert 、in list 进行 update、select，对材料的调整，会容易造成的主从延迟和锁堵塞。避免 UNION ，推挤安十一 UNION ALL ，因为 union aall 不需要去重、节省数据库资源、提升性能。禁止不带 WHERE 条件的 SQL，尽量规避全表扫描。如果大表不带 WHERE 条件进行查询的话，是非常消耗数据库性能、资源。where 条件里等号左右字段类型必须一致，否则会产生隐式转换，无法进行索引，这个也是在日常处理过程中遇到的，例如某个字段是存储手机号的，它设置的字段类型是字符类型，就是 VARCHAR 类型或者或者 CHAR 类型的，但是他在写 SQL 查询语句的时候，手机号等于一串数字串，但是没有带引号，这个时候会认为是整型类型的，但是实际表中存储的类型是 VARCHAR类型的，会导致它无法使用索引而进行全表扫描。其他场景也也有相同的，像这个状态类型是1 2 3 4 5有可能和刚刚说的一样的效果。使用 LIKE 的时候左边不能使用百分号，左边使用百分号就代表了全表里面的数据都能进行扫描，这种场景它等于无法使用索引的，涉及到 order by 如果有多列需求，要么都升序，要么都降序，避免排序索引失效。索引列不要使用函数和表达式，因为这样无法使用到索引的。不建议使用子查询，建议将子查询拆开，或者使用 join 程序多次代替子查询。避免出现三表以上的表关联 ，SQL 性能下降。事务里更新语句尽量基于主键或者 unique key 是唯一索引进行更新的。例如 update where id=xx，否则的话字段等于某部门然后进行更新，它的更新锁定的范围会比较大，然后也会导致性能的下降，产生死锁。避免事务过大、过长，容易造成锁竞争、主从延迟。尽量减少 replace into、on duplicate key update 的使用，重复主键 update 容易造成数据的不一致、锁堵塞。SQL 执行计划
执行计划里面包含了 id，这个 id 的话主要是 select 子句或操作表的顺序，如果是子查询，查询id的序号会递增。id 越大，它的优先级也会越高，会被优先执行，如果id相同的话，可以认为是一组，然后从上往下进行执行。 Select _type 是查询子句中的一个类型，每个 select 子句的类型，只是子查询或者简单的 while 查询。 Table 是表名，Type、类型，Type 类型也就是访问的方式。可以看到从左往右它的效率会越来越高，ALL类型是全表扫描，index是可能用到会用到索引面积、范围等等。 Possible_keys 是指出 MySQL 中能够使用哪个索引在表中找到的行，然后涉及到这个字段的时候是否存在，如果存在的话会将它列出来，在正常操作中不一定会使用到。Key是显示这条 SQL 真实和使用到的索引，对应这个索引的长度。Key_len 是索引中使用的字节数， Ref 表示上述表匹配条件，哪些列或常量常被用于查询所列项的值。Rows 是比较常见估算查询结果集的行数。Extra 是额外的信息的备注。常见的第一个 select 是什么样的类型，有没有一些表连接、子查询等等；还有就是 type 类型是比较常见的，比如说这张表是 ALL 类型，可以分析出这条查询效率怎么样；还有使用到的索引，查询估算要的结果的行数是多少。标红的几个是比较常见的，看它进行分析的 SQL 执行计划的速度、执行效率。
6. RDS MySQL 上线发布规范
在新项目前期规划阶段，要去评估 MySQL 数据库选型的容量规划，选定实际规格，数据库的架构选型是否要进行分库分表等。然后 RDS MySQL 环境的初始化、开发生产进行环境隔离、开发环境的库表设计、功能模块的开发；在上线前需要进行规范检查，开发功能验证是否有问题， SQL review 的 SQL 里面是不是对应都加上索引了，索引是否加的合理等等；上线发布之后，观察数据库的负载、水位，性能表现、慢 SQL 等。然后对应的优化解决。
