MySQL-convention
version 1.0.1, 2022-01-04 12:37
基础规范
-
表存储引擎必须使用InnoDB
-
表字符集默认utf8mb4, 而不是utf8或其它
utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character. utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character. utf8: An alias for utf8mb3. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references. 参考: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
-
禁止使用存储过程, 视图, 触发器, event, 外键
-
禁止在数据库中存储大文件, 例如照片, 可以将大文件存储在对象存储系统, 数据库中存储路径
-
禁止在线上环境做数据库压力测试
-
测试, 开发, 线上数据库环境必须隔离
命名规范
-
库名, 表名, 列名必须用小写, 采用下划线分隔表名、字段名必须使用小写字母或数字, 禁止出现数字开头, 禁止两个下划线中间只出现数字, 长度不要超过32字符
-
禁用保留字, 如desc、range、match、delayed等, 请参考MySQL官方保留字
-
库备份必须以bak为前缀, 以日期为后缀
-
从库必须以-s为后缀, 备库必须以-ss为后缀
-
库名与应用名称尽量一致
-
表名不使用复数名词
说明: 表名应该仅仅表示表里面的实体内容, 不应该表示实体数量, 对应于DO类名也是单数形式, 符合表达习惯。
-
表达是与否概念的字段, 必须使用is_xxx的方式命名, 数据类型是unsigned tinyint (1表示是, 0 表示否)
说明: 任何字段如果为非负数, 必须是unsigned。 正例: 表达逻辑删除的字段名is_deleted, 1表示删除, 0表示未删除。
-
主键索引名为pk_字段名; 唯一索引名为uk_字段名; 普通索引名则为idx_字段名
说明: pk即primary key; uk即unique key; idx即index。
库表设计规范
-
单实例表个数必须控制在2000个以内
-
单表分表个数必须控制在1024个以内
-
【推荐】单表行数超过500万行或者单表容量超过2GB, 才推荐进行分库分表
说明: 如果预计三年后的数据量根本达不到这个级别, 请不要在创建表时就分库分表。
-
【强制】表必备三字段: id, gmt_create, gmt_modified
说明: 其中id必为主键, 类型为unsigned int/unsigned bigint、单表时自增、步长为 1。 gmt_create, gmt_modified 的类型均为date_time类型, 前者现在时表示主动创建, 后者过去分词表示被动更新。
-
建议将大字段, 访问频度低的字段拆分到单独的表中存储, 分离冷热数据
-
【参考】合适的字符存储长度, 不但节约数据库表空间、节约索引存储, 更重要的是提升检索速度
正例: 如无符号值可以避免误存负数, 且扩大了表示范围。
-
【推荐】字段允许适当冗余, 以提高查询性能, 但必须考虑数据一致
冗余字段应遵循: 1) 不是频繁修改的字段。 2) 不是varchar超长字段, 更不能是text字段。 正例: 商品类目名称使用频率高, 字段长度短, 名称基本一成不变, 可在相关联的表中冗余存储类目名称, 避免关联查询。
列/字段设计规范
-
必须把字段定义为NOT NULL并设默认值
解读: (1) NULL的列使用索引, 索引统计, 值都更加复杂, MySQL更难优化 (2) NULL需要更多的存储空间 (3) NULL只能采用IS NULL或者IS NOT NULL, 而在=/!=/in/not in时有大坑 https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
-
据业务区分使用tiny/small/medium/int/bigint, 分别会占用1/2/3/4/8 字节
-
小数类型为decimal, 禁止使用float和double
说明: float和double在存储的时候, 存在精度损失的问题, 很可能在值的比较时, 得到不正确的结果。 如果存储的数据范围超过decimal的范围, 建议将数据拆成整数和小数分开存储。
-
根据业务区分使用char/varchar
字段长度固定, 或者长度近似的业务场景适合使用char, 能够减少碎片, 查询性能高; 字段长度相差较大, 或者更新较少的业务场景, 适合使用varchar, 能够减少空间; varchar是可变长字符串, 不预先分配存储空间, 长度不要超过5000; 如果存储长度大于此值, 定义字段类型为text, 独立出一张表用主键来对应, 避免影响其它字段索引效率
-
根据业务区分使用datetime/timestamp
解读: a) DATETIME默认值为null; TIMESTAMP的字段默认为当前时(CURRENT_TIMESTAMP)。 b) DATETIME使用8字节的存储空间, TIMESTAMP的存储空间为4字节。https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html c) 存储方式不一样, TIMESTAMP把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。 查询时, 将其又转化为客户端当前时区进行返回。DATETIME基本上是原样输入和输出。 d) 存储的时间范围不一样 timestamp: '1970-01-01 00:00:01.000000'-'2038-01-19 03:14:07.999999' datetime: '1000-01-01 00:00:00.000000'-'9999-12-31 23:59:59.999999'
-
使用INT UNSIGNED存储 IPv4, 不要用char(15)
-
使用varchar(20)存储手机号, 不要使用整数
解读: (1) 牵扯到国家代号, 可能出现+/-/()等字符, 例如+86 (2) 手机号不会用来做数学运算 (3) varchar可以模糊查询, 例如like '138%'
-
使用TINYINT来代替ENUM
解读: ENUM增加新值要进行DDL操作
-
尽量不要使用BLOB和TEXT
-
谨慎使用JSON类型
解读: 5.7.8版本开始支持JSON, 推荐使用8.0以上版本: https://mydbops.wordpress.com/2019/02/26/presentation-json-improvements-in-mysql-8-0/ JSON类型的优点是提供了类似文档数据模型的灵活Schema的机制, 同时JSON也是统一的数据交换格式; JSON平均空间大约比正常情况多一倍; 需要尽量避免全表扫表, 可能需要为某些属性建立索引 https://stackoverflow.com/questions/33660866/native-json-support-in-mysql-5-7-what-are-the-pros-and-cons-of-json-data-type https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong
索引规范
-
单张表索引数量建议控制在5个以内
解读: (1) 互联网高并发业务, 太多索引会影响写性能 (2) 生成执行计划时, 索引太多会降低性能, 并可能选择不到最优索引 (3) 异常复杂的查询需求, 可以选择 ES 等更为适合的方式存储
-
不建议在频繁更新的字段上建立索引
-
组合索引字段数不建议超过5个
解读: 如果5个字段还不能极大缩小row范围, 八成是设计有问题
-
非必要不要进行JOIN查询, 如果要进行JOIN查询, 被JOIN的字段必须类型相同, 并建立索引
解读: 踩过因为JOIN字段类型不一致, 而导致全表扫描的坑么？
-
理解组合索引最左前缀原则, 避免重复建设索引, 如果建立了(a,b,c), 相当于建立了(a), (a,b), (a,b,c)
-
业务上具有唯一特性的字段, 即使是多个字段的组合, 也必须建成唯一索引。
说明: 不要以为唯一索引影响了insert速度, 这个速度损耗可以忽略, 但提高查找速度是明显的; 另外, 即使在应用层做了非常完善的校验控制, 只要没有唯一索引, 根据墨菲定律, 必然有脏数据产生。
-
超过三个表禁止join。需要join的字段, 数据类型必须绝对一致; 多表关联查询时, 保证被关联的字段需要有索引
说明: 即使双表join也要注意表索引、SQL性能。
-
在varchar字段上建立索引时, 必须指定索引长度, 没必要对全字段建立索引, 根据实际文本区分度决定索引长度即可
说明: 索引的长度与区分度是一对矛盾体, 一般对字符串类型数据, 长度为20的索引, 区分度会高达90%以上, 可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
-
页面搜索严禁左模糊或者全模糊, 如果需要请走搜索引擎来解决。
说明: 索引文件具有B-Tree 的最左前缀匹配特性, 如果左边的值未确定, 那么无法使用此索引
-
【推荐】如果有order by 的场景, 请注意利用索引的有序性。order by最后的字段是组合索引的一部分, 并且放在索引组合顺序的最后, 避免出现file_sort的情况, 影响查询性能。
正例: where a=? and b=? order by c; 索引: a_b_c 反例: 索引中有范围查找, 那么索引有序性无法利用, 如: WHERE a>10 ORDER BY b; 索引a_b无法排序。
-
【推荐】利用覆盖索引来进行查询操作, 避免回表
-
【推荐】利用延迟关联或者子查询优化超多分页场景
说明: MySQL并不是跳过offset行, 而是取offset+N行, 然后返回放弃前offset行, 返回N行 当offset特别大的时候, 效率就非常的低下, 要么控制返回的总页数, 要么对超过特定阈值的页数进行SQL改写。 正例: 先快速定位需要获取的id段，然后再关联: SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20) b where a.id=b.id
-
【推荐】SQL性能优化的目标: 至少要达到range级别, 要求是ref级别, 如果可以是consts最好
说明: 1) consts单表中最多只有一个匹配行(主键或者唯一索引), 在优化阶段即可读取到数据。 2) ref指的是使用普通的索引(normal index)。 3) range对索引进行范围检索。 反例: explain表的结果, type=index, 索引物理文件全扫描, 速度非常慢, 这个index级别比较range还低, 与全表扫描是小巫见大巫。
-
【推荐】建组合索引的时候, 区分度最高的在最左边
正例: 如果where a=? and b=?, a列的几乎接近于唯一值, 那么只需要单建idx_a索引即可 说明: 存在非等号和等号混合判断条件时, 在建索引时, 请把等号条件的列前置。 如: where a>? and b=? 那么即使a的区分度更高, 也必须把b放在索引的最前列。
-
【推荐】防止因字段类型不同造成的隐式转换, 导致索引失效。
-
【参考】创建索引时避免有如下极端误解:
1) 认为一个查询就需要建一个索引。 2) 认为索引会消耗空间、严重拖慢更新和新增速度。 3) 抵制唯一索引, 认为业务的唯一性一律需要在应用层通过"先查后插"方式解决。
SQL规范
-
不要使用count(列名)或count(常量)来替代count(), count()是SQL92定义的标准统计行数的语法, 跟数据库无关, 跟NULL和非NULL无关
说明: count(*)会统计值为NULL的行, 而count(列名)不会统计此列为NULL值的行。
-
count(distinct col)计算该列除NULL之外的不重复行数, 注意count(distinct col1, col2)如果其中一列全为NULL, 那么即使另一列有不同的值, 也返回为0
-
当某一列的值全是NULL时, count(col)的返回结果为0, 但sum(col)的返回结果为NULL, 因此使用sum()时需注意NPE问题
正例: 可以使用如下方式来避免sum的NPE问题: SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
-
使用ISNULL()来判断是否为NULL值
说明: NULL与任何值的直接比较都为 NULL: 1) NULL<>NULL的返回结果是NULL, 而不是false。 2) NULL=NULL的返回结果是NULL, 而不是true。 3) NULL<>1的返回结果是NULL, 而不是true。
-
在代码中写分页查询逻辑时, 若count为0应直接返回, 避免执行后面的分页语句
-
数据订正时, 删除和修改记录时, 要先select, 避免出现误删除, 确认无误才能执行更新语句。
-
【推荐】in操作能避免则避免, 若实在避免不了, 需要仔细评估in后边的集合元素数量, 控制在1000个之内。
-
【参考】TRUNCATE TABLE比DELETE速度快, 且使用的系统和事务日志资源少, 但TRUNCATE无事务且不触发trigger, 有可能造成事故, 故不建议在开发代码中使用此语句。
说明: TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同。
-
禁止使用select *, 只获取必要字段
解读: (1) select *会增加cpu/io/内存/带宽的消耗 (2) 指定字段能有效利用索引覆盖 (3) 指定字段查询, 在表结构变更时, 能保证对应用程序无影响
-
隐式类型转换会使索引失效, 导致全表扫描
-
禁止在 where条件列使用函数或者表达式(导致不能命中索引, 全表扫描)
-
禁止负向查询以及%开头的模糊查询(导致不能命中索引, 全表扫描)
-
禁止大表JOIN和子查询
-
同一个字段上的OR必须改写为IN, IN的值必须少于50个
-
应用程序必须捕获SQL异常, 方便定位线上问题
操作规范
-
超过100万行的批量写(update, delete, insert)操作, 要分批多次进行操作
解读: (1) 大批量操作可能会造成严重的主从延迟 (2) binlog格式为row格式时会产生大量的日志 (3) 避免产生大事务操作
-
对于大表使用pt-online-schema-change修改表结构
解读: (1) 避免大表修改时产生的主从延迟 (2) 避免对表字段进行修改时进行锁表 (3) pt-online-schema-change会先建立一个与原表结构相同的新表, 并且在新表上进行表结构的修改, 然后把原表中的数据复制到新表中。
-
禁止为程序使用的账号赋予super, 遵循权限最小原则
参考
-
阿里, 58, 赶集等规范
Mifepristone, also recognized as RU-486, is a consequence that is in use accustomed to for the purpose medical abortion. It was developed in the 1980s via French scientists and has been approved for use in uncountable countries, including the Collaborative States.
Mifepristone acts as an antiprogesterone, which means that it blocks the engagement of progesterone, a hormone that is needed to prolong pregnancy. The medicine causes fetal cold shoulder http://mihi.co.kr/bbs/board.php?bo_table=free&wr_id=626890 and leads to abortion. As common it is charmed together with another medication called misoprostol, which helps suddenness up and improve development of abortion.
Mifepristone has its own side effects, which may categorize nausea, vomiting, migraines, bleeding and diarrhea. In some cases, qualified medical intervention may be required if complications occur.
Teeth of the actuality that mifepristone is considered Acheter Mifegyne en ligne a non-hazardous and operative method of abortion, it restful causes a piles of powwow and argument quantity the available and medical specialists. Some people observe it a opportune and more handsome other feeling of abortion, when how others afraid of its workable risks and adversary consequences.
Whole, mifepristone is an substantial means in the arsenal of medical techniques in support of terminating a pregnancy. It gives women choice and restraint to their bodies. It is weighty that every bride about with a medical professional all possible options and risks before deciding to terminate a pregnancy with mifepristone.
Mifepristone, also recognized as RU-486, is a outcome that is utilized for the duration of medical abortion. It was developed in the 1980s alongside French scientists and has been approved instead of consume in many countries, including the Collaborative States.
Mifepristone acts as an antiprogesterone, which means that it blocks the action of progesterone, a hormone that is needed to retain pregnancy. The analgesic causes fetal dismissal http://www.insem.co.kr/gn/bbs/board.php?bo_table=free&wr_id=1347462 and leads to abortion. As usual it is taken together with another medication called misoprostol, which helps alacrity up and improve treat of abortion.
Mifepristone has its own side effects, which may categorize nausea, vomiting, migraines, bleeding and diarrhea. In some cases, professional medical intervention may be required if complications occur.
Notwithstanding the certainty that mifepristone is considered Acheter Mifegyne en ligne a non-hazardous and remarkable method of abortion, it restful causes a piles of chin-wag and argument quantity the public and medical specialists. Some people study it a opportune and more captivating other habit of abortion, when how others cowardly of its possible risks and adversary consequences.
Whole, mifepristone is an important means in the arsenal of medical techniques for terminating a pregnancy. It gives women pick and restraint over their bodies. It is weighty that every bride deliberate over with a medical thorough all reachable options and risks preceding the time when deciding to terminate a pregnancy with mifepristone.
Mifepristone, also recognized as RU-486, is a product that is used on account of medical abortion. It was developed in the 1980s via French scientists and has been approved someone is concerned manipulate in diverse countries, including the United States.
Mifepristone acts as an antiprogesterone, which means that it blocks the engagement of progesterone, a hormone that is needed to profess pregnancy. The upper causes fetal cold shoulder http://mail.newlifekpc.org/bbs/board.php?bo_table=free&wr_id=5608005 and leads to abortion. As common it is charmed together with another medication called misoprostol, which helps alacrity up and correct activity of abortion.
Mifepristone has its own side effects, which may list nausea, vomiting, migraines, bleeding and diarrhea. In some cases, expert medical intervention may be required if complications occur.
Teeth of the fact that mifepristone is considered Acheter Mifegyne en ligne a non-hazardous and useful method of abortion, it stillness causes a piles of powwow and argument centre of the available and medical specialists. Some people consider it a opportune and more alluring other respect of abortion, when how others panic-stricken of its reasonable risks and negative consequences.
Whole, mifepristone is an important mechanism in the arsenal of medical techniques in support of terminating a pregnancy. It gives women choice and control over their bodies. It is important that every mate deliberate over with a medical professional all reachable options and risks before deciding to finish a pregnancy with mifepristone.
Mifepristone, also recognized as RU-486, is a product that is used on account of medical abortion. It was developed in the 1980s via French scientists and has been approved someone is concerned manipulate in diverse countries, including the Collaborative States.
Mifepristone acts as an antiprogesterone, which means that it blocks the enterprise of progesterone, a hormone that is needed to retain pregnancy. The upper causes fetal cold shoulder https://formacion.somoscodenautas.com/forums/topic/abortion-pills/ and leads to abortion. As common it is taken together with another medication called misoprostol, which helps alacrity up and refurbish process of abortion.
Mifepristone has its own side effects, which may include nausea, vomiting, migraines, bleeding and diarrhea. In some cases, masterful medical intervention may be required if complications occur.
Without considering the fact that mifepristone is considered Acheter Mifegyne en ligne a non-hazardous and operative method of abortion, it restful causes a piles of chin-wag and confrontation centre of the open and medical specialists. Some people observe it a close at hand and more attractive other way of abortion, when how others weak-kneed of its reasonable risks and annulling consequences.
Whole, mifepristone is an important tool in the arsenal of medical techniques for the benefit of terminating a pregnancy. It gives women pick and control over with their bodies. It is weighty that every mate deliberate over with a medical professional all possible options and risks in advance deciding to terminate a pregnancy with mifepristone.