阿里巴巴开发手册的MySQL编程规范

提供了阿里巴巴手册中MySQL部分的详细解释。如果有什么非常明确的约定不需要太多解释，本文将不再解释。

本文分为四个部分：

• 表创建规范
• 索引规范
• SQL 命令
• ORM 映射创建 ❙1 表创建规范 [必填] 表示是或概念的字段no 。它们必须以 is_xxx 方式命名，数据类型为 unsignedtinyint （1 表示是，0 表示否）。
  • 注意：如果每个字段是非负数，则必须无符号。
  • 注意：不要在 POJO 类中的任何布尔变量前加上 is 前缀，因此必须设置 is_xxx 到 Xxx 的映射关系。数据库使用tinyint 类型表示是或否值。遵循 is_xxx 命名约定以阐明其含义和值范围。
  • 正例：表示逻辑删除的字段名is_deleted，1表示删除，0表示不删除。
  • 解读：从优化的角度来看，应该根据领域的目的定义合适的类型。长度为 1 个字节的tinyint 足以表达是或否。

  【必填】表名和字段名必须使用小写字母或数字。禁止以数字开头，并且禁止两个下划线之间只有数字。编辑数据库字段名称的成本非常高，因为不可能进行预发布，因此必须仔细考虑字段名称。

  • 注意：MySQL 在 Windows 上不区分大小写，但在 Linux 上默认区分大小写。因此，数据库名、表名和字段名中不允许使用大写字母，以避免不必要的复杂化。
  • 正例：aliyun_admin,rdc_config,level3_name 反例：AliyunAdmin,rdcConfig,level_3_name
  • 解释：在Win环境下开发，本地代码中使用的表名使用小写字母，因此本地代码中不存在Win环境。问题，但是发布到linux环境会出现问题。 Linux 上安装 MySQL 后，默认为：表名区分大小写，列名不区分大小写

  Linux 上 MySQL 数据库名、表名、列名、别名区分大小写的规则：

  ( 1) 数据库名称和表名严格区分大小写

  (2) 表别名严格区分大小写

  (3) 列名和列别名并非在所有情况下都区分大小写

  (4) 变量名也严格区分大小写严格区分大小写 -

  [强制] 表名中不要使用复数名词。

  • 注意：表名只能代表表中的实体内容，不能代表实体的数量。对应的DO类名也是单数，符合表达式约定。

  【必填】禁用descent、range、match、delay等保留字。查看MySQL官方保留字。

  【必填】主键索引名称为pk_field名称；唯一索引名称为uk_field名称；通用索引名称是idx_field name。

  • 说明：pk_为主键； uk_ 是唯一键； idx_ 是索引的缩写。

  【必填】Decimal类型为十进制，禁止float和double。

  • 注意：存储浮点型和双精度型时，存在精度损失的问题，并且在比较值时可能会得到不正确的结果。如果存储的数据范围超出小数范围，建议将数据拆分为整数和小数分别存储。
  • 解释：float和double都是浮点类型，而decimal是定点类型。 MySQL 浮点和定点类型可以通过在类型名称后附加 (M, D) 来表示。 M表示值的总长度，D表示小数点后的长度。如果未指定精度，则

  FLOAT 和 DOUBLE 默认为真精度，而如果未指定精度，DECIMAL 默认为整数 10，小数默认为 0。因此，建议在定义表时定义(M,D)。

  当设置的值超过定义的长度时，float 和 double 会自动四舍五入，小数会被截断并显示警告。

  精度丢失问题：浮点数和双列在求和计算中会丢失精度，而小数位数会准确计算。

  【必选】如果存储的字符串长度几乎相同，则使用定长char字符串。

  • 解读：从优化的角度来看，如果表中所有字段都有固定长度，那么每条数据也有固定长度，数据库可以直接计算出下一部分数据的移位，并且查询速度会更高。

  【必选】varchar为变长字符串，不预先分配存储，长度不能超过5000。如果存储长度大于该值，则定义字段类型为文本，单独创建表，使用主键要避免影响其他字段的索引效率。

  • 解释：MySQL 5.0版本以上，varchar最多可以存储65535字节的数据（1-2个字节用于存储内容开头的长度信息，超过255时用两个字节，所以最大值为 65535)。

  通常我们设置编码为U8，每个字符最多占用3个字节，所以最大长度不能超过21845。

  如果定义超过上述限制，varchar字段会被强制转换为text类型，并且a生成警告。

  另外，由于MYSQL的行长度限制，MySQL要求定义的行长度不能超过65535。如果定义的表长度超过这个值，会显示ERROR 1118 (42000): Row size is Too Large。数据库中定义的

  Varchar(20)指的是20个字符。

  5000左右的建议：由于通常定义每个U8字符占用3个字节，那么5000个字符需要15000个字节。考虑到行长度和其他列的最大限制以及查询性能，建议5000。

  【必填】表必须有三个字段：id、gmt_create、gmt_modified。

  • 注意：ID必须为主键，类型为bigint unsigned，一张表自增，步长为1。类型gmt_create和gmt_modified都是日期和时间类型。第一个表示现在时的主动创造，第二个的过去分词表示被动更新。

  【建议】最好使用“business_name_table_function”函数来命名表。

  • 正例：alipay_task/force_project/trade_config

  【建议】库名和应用名尽量一致。

  【推荐】如果更改字段含义或者为字段添加状态，一定要及时更新字段注释。

  【推荐】字段允许合理的冗余，以提高查询性能，但要考虑数据的一致性。冗余字段应遵循：

  1) 不经常修改的字段。

  2）它不是一个varchar超长字段，更不用说文本字段了。

  • 正例：产品类别名称使用频繁，字段长度较短，名称基本不变。类别名称可以冗余存储在关联表中，以避免相关查询。

  【建议】只有当一张表的行数超过500万行或者一张表的容量超过2GB时，才建议共享库表。

  • 注意：如果预计三年内数据量不会达到这个水平，建表时不要分库分表。
  • 解读：超过限制会对各方面表现产生较大影响。淘新闻曾经遇到过因桌子过大导致的故障。

  [链接]适当的字符存储长度不仅可以节省数据库表空间和索引存储，更重要的是可以提高检索速度。

  • 正例：如下表所示，无符号值可以避免负数存储错误，扩大显示范围

  Type	Age	Field❙Length❙❓FieldLength 取值范围
  People	长达150年	tinyint unsigned	1	无符号值：0~255

  2业务规范索引字段❙2即多个字段组合也必须构建成唯一的指数。

  • 注意：不要认为唯一索引影响插入速度。这个速度损失可以忽略不计，但是搜索速度的提升是明显的；另外，即使在应用层进行了非常完整的验证检查，只要没有唯一索引，根据莫费定律，就一定会产生脏数据。
  • 解释：墨菲定律：如果某件事情有可能出错，无论机会多么小，它总会发生。

  【强制】禁止组合三张以上的桌子。待拼接字段的数据类型必须绝对一致；在进行多表相关查询时，保证相关字段必须有索引。

  • 注意：即使连接两个表，也需要注意表索引和SQL性能。

  【必选】在varchar字段创建索引时，必须指定索引的长度。没有必要对整个数组进行索引。索引的长度可以根据文本的实际分辨率来确定。

  • 注：索引长度和分辨率是一对矛盾。一般来说，对于字符串数据，对于长度为20的索引，分辨率可达90%或更高。您可以使用分辨率count(distinctleft(column_name, index_length)))/count(*)。
  • 解释：分辨率是指数据表中唯一索引值占总记录数的比例，范围从（0.1）开始，值越高，查询效率越高。

  对于 blob、text 和 varchar 列 必须使用前缀索引，MySQL 不允许索引这些列的全长。long（以节省空间）。

  [必填] 严格禁止搜索页面 左模糊或全模糊，如果需要的话请搜索引擎解决。

  注意：索引文件具有B树最左边的前缀匹配功能，如果不指定左边的值，则不能使用该索引。

  【推荐】如果有按照场景排序的情况，请注意索引的顺序，最后order-by字段是组合索引的一部分，放在索引组合顺序的最后，以避免file_sort 影响查询性能。

  • 正例：其中a=？和b=？根据c订购；索引：a_b_c
  • 反例：如果索引中有范围搜索，则不能使用索引排序如：WHERE a>10 ORDER BY b;索引 a_b 无法排序。
  • 解释：file_sort 是在 sort by 期间发生的文件排序。如果没有使用索引进行排序，则生成file_sort。只有当索引列的顺序与排序 order by 子句完全一致且所有列的排序方向相同时，才能使用索引排序。如果查询必须涉及多个表，则只有 order 子句引用的所有字段都在第一个表中时才能使用索引排序。

  【推荐】使用覆盖索引进行查询操作，避免返表。

  • 说明：如果这本书需要知道第11章的标题，是否会转到第11章对应的页面？只需滚动浏览目录即可。该目录用作封面索引。
  • 正例：可以创建三种类型的索引：主键索引、唯一索引、普通索引。覆盖索引只是查询的一个效果。使用解释的结果，显示另一列：使用索引。
  • 解释：如果索引包含了所有需要查询的字段的值，则称为“覆盖索引”。

  由于覆盖索引必须存储索引列的值，因此哈希索引、空间索引和全文索引不存储列值。 MySQL只有B-Strom索引可以用作覆盖索引。例如：对id、name、title这三个字段创建索引，这三列的值就会存储在索引中。如果你问： select id, name, title from table where id 如果查询 select * from table where id 【推荐】使用延迟关联或子查询来优化多页面场景。

  • 注意：MySQL的limit请求并不是跳过偏移行，而是取+N偏移行，然后在放弃并返回N行之前返回偏移行。当偏移量特别大时，效率很低，或者检查返回的总页数或者对超过一定阈值的页进行SQL重写。
  • 解释：案例

  select count(*) from user_game_info; // 总计 956176 条数据

  select * from user_game_info limit 900000, 20; // 该查询需要 0.547S

  select t1. * from user_game_info t1, (select id from user_game_info limit 900000, 20) t2 where t1.id = t2.id; // 优化后需要0.178S

  【推荐】SQL性能优化目标：至少达到scope级别，要求是ref级别，能const最好。

  • 注：

  1）常量一张表中最多有1个匹配行（主键或唯一索引），并且在优化阶段可以读取数据。

  2) ref 指的是使用普通索引。

  3) range 在索引中执行范围搜索。

  • 反例：解释表type=index会导致对物理索引文件进行全扫描，速度非常慢。这个索引级别低于范围，与全表扫描相比相形见绌。
  • 说明：我们这里说的是说明中的字段类型（连接类型）。

  常见类型有：all、index、range、ref、eq_ref、const，效率从左到右递增。

  1）全部：全表扫描

  2）索引：先扫描所有索引，然后返回表获取数据。性能并不比所有都好

  3) 范围：远程索引扫描，Between/and /> /？和=？那么，即使c的分辨率再高，d也必须放在索引的前面，即idx_d_c索引。

【建议】避免因字段类型不同引起的隐式转换，导致索引失败。

• 解释：比如上面tb_user_account表的username（varchar）字段添加索引。由于数组是 varchar，因此上图中的查询类型匹配并且索引被命中。下图使用int类型进行匹配。 ，索引无法命中。

以字符串形式查找，找到索引

因隐式转换导致索引缺失

隐式转换规则：

• 当两个参数至少有一个为 NULL 时，比较结果也为 NULL，比较时出现异常两个 NULL 值使用 返回 1。在这两种情况下，不需要类型转换
• 两个参数都是字符串，将作为字符串进行比较，无需类型转换
• 两个参数都是整数，将作为不带类型的整数进行比较转换
• 十六进制值与非数字值比较时，将被视为二进制字符串
• 一个参数是 TIMESTAMP 或 DATETIME，另一个参数是常量，常量转换为时间戳
• 一个参数是十进制类型。如果第二个参数是小数或整数，则将整数转换为小数进行比较。如果第二个参数是浮点数，则将比较的小数点转换为浮点数
• 其他情况，将两个参数转换为浮点数进行比较

[链接]创建索引时要避免以下极易被误解的情况：

• 永远不要做太多的短缺。假设查询需要索引。
• 少比多好。人们认为索引会占用空间并严重减慢更新和新添加的速度。
• 抵制唯一索引。认为业务的唯一性必须在应用层以“先检查后插入”的方式解决。 ？它与数据库无关，与NULL和非NULL无关。
  • 注意：count(*) 会计算具有 NULL 值的行，但 count(column name) 不会计算该列中具有 NULL 值的行。

  【必填】 count (distinct col) 计算该列中唯一的非 NULL 行的数量。注意 count(distinctcol1, col2) 如果其中一列为 NULL，即使另一列的值不同，也会返回为 0。

  [必填] 当某一列的值全部为 NULL 时，count(col)的返回结果为0，而sum(col)的返回结果为NULL，所以使用sum()时需要注意NPE问题。

  • 正例：为了避免求和的NPE问题，可以使用以下方法： SELECT IF(ISNULL(SUM(g)),0,SUM(g))FROM table;

  【必选】使用ISNULL( ) 判断是否为NULL值。

  • 解释：NULL 与任何值直接比较都是 NULL。

  1) 结果 NULLNULL 是 NULL，不是 false。

  2）结果NULL=NULL是NULL，不是true。

  3）结果NULL1是NULL，不是true。

  【必选】在代码中编写分页查询逻辑时，如果count为0，则应直接返回，避免执行后续的分页命令。

  • 解释：先查询COUNT，再查询分页数据

  【必填】不允许外键和级联。所有外键概念都必须在应用层解决。

  • 说明：以学生与成绩的关系为例。 Students 表中的student_id 是主键，则grades 表中的student_id 是外键。如果更新students表中的学生标识符，同时更新grades表中的学生标识符，则这是级联更新。外键和级联更新适合单机低并发，不适合分布式、高并发集群；级联更新强烈阻塞，存在数据库更新风暴的风险；外键影响数据库插入速度。

  【必选】禁止使用存储过程。存储过程难以调试和扩展并且不可移植。

  【强制】在更正数据时（尤其是删除、编辑记录时），必须先选择避免误删除，确认无误后再进行更新声明。

  • 解释：手动执行SQL修改或删除数据时，先使用后重选条件，确认数据正确后，再执行更新或删除。

  【推荐】如果可以避开交通，就避开它。如果无法避免，必须仔细评估输入后集合的元素数量，控制在1000以内。

  【链接】如果需要国际化，所有存储和表示字符均采用utf-8编码。注意角色统计功能的差异。

  • 描述：选择长度（“简单工作”）；返回12，统计字节数。 SELECT CHARACTER_LENGTH ("简单的工作");返回4，统计字符数。如果需要保存表情请选择utf8mb4保存。请注意与 utf-8 编码的区别。
  • 解释：utf8可以存储3个字节的数据，utf8mb4可以存储4个字节，是专门为了兼容4字节的unicode而设计的。 utf8mb4 是 utf8 的超集。无需将编码从 utf8 更改为 utf8mb4。转变。

  表情符号不在 utf8 表示的 3 字节范围内，可以存储在 utf8mb4 中。

  [参考] TRUNCATE TABLE 比 DELETE 更快，并且使用更少的系统和事务日志资源。但是，TRUNCATE 没有事务，也不会触发可能导致崩溃的触发器。因此，不建议在开发代码中使用该语句。说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 相同。

  4 ORM 映射

  【必选】表查询中不要使用 * 作为查询字段列表。必须明确说明哪些字段是必填字段。

  • 使用说明：

  1）增加查询分析器分析成本。

  2）添加或删除字段很容易与resultMap配置冲突。

  3）不必要的字段会增加网络消耗，尤其是文本类型字段。

  • 解读：可以更好地利用“覆盖索引”。

  【必填】POJO类boolean属性不能加is，但数据库字段必须加is_，这就需要resultMap中字段和属性的映射。

  • 注：参见POJO类定义和数据库字段定义。需要在其中添加映射。必须对MyBatis生成器生成的代码进行适当的修改。

  【必选】不要使用resultClass作为返回参数。虽然所有类属性名称都对应数据库字段，但需要定义它们；相反，每个表必须有一个对应的 POJO 类。

  • 描述：配置映射关系，将字段与DO类分开，方便维护。

  【必选】使用sql.xml配置参数：#{}、#param#不要使用${}。此方法容易受到 SQL 注入攻击。

  • 解释：#和$的区别：预编译中的处理不同。 #{} 在预处理过程中，参数部分会被替换为 ? 占位符，如：

  select * from user where name = ?;

  ，而 ${} 只是简单的字符串替换。动态解析阶段，sql语句被解析为

  select * from user where name = 'zhangsan';

  上面，参数替换 #{} 发生在 DBMS 中，而 ${} 发生在动态解析中过程。

  【必选】不推荐iBATIS自带的QueryForList(String statementsName, int start, int size)。

  • 说明：实现方法是获取数据库中所有与Name语句匹配的SQL语句记录，然后通过子列表获取子集start、size。
  • 正例：Map map = new HashMap();map.put("start", start);map.put("size", size);

  【必填】否允许直接使用HashMap和Hashtable作为查询结果集的输出。

  • 注：resultClass="Hashtable"插入字段名和属性值，但值类型不受控制。

  【推荐】不要编写庞大复杂的接口来更新数据。如果作为POJO类传递，无论是否是自己的目标更新数组，更新表集c1=value1,c2=value2,c3=value3;这是不正确的。执行 SQL 时不要更新原始字段。第一，容易出错；第二，效率低下；第三，binlog存储量将会增加。

  【参考】不要滥用@Transaction交易。事务会影响数据库的QPS。另外，在使用事务的地方，需要考虑各种回滚解决方案，包括缓存回滚、回滚到搜索引擎、消息偏移、统计修补等。属性值，通常是数字。当表示它们相同时使用此条件；当表示不为空且不为null时执行；当它表示它不是空值时执行。

  作者：Mr.玉魔青山
  链接：https://juejin.im/post/5c8bb168f265da2da23d73f0
  来源版权：掘金作者：掘金商业转载请联系作者授权。非商业转载请注明出处。