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, 赶集等规范
Дипломы с официальной регистрацией: как это возможно? В наше время все больше людей обращаются к услугам по изготовлению дипломов на заказ. Это вызвано различными причинами, но главное из них – это удобство и возможность получить качественный документ без лишних заморочек. Давайте рассмотрим несколько преимуществ изготовления диплома на заказ. Первое и, пожалуй, самое важное преимущество – это время. Изготовление диплома купить диплом с занесением в реестр москва на заказ позволяет сэкономить массу времени, которое вы бы потратили на поиск информации, написание работы, корректировку и так далее. Вместо того чтобы тратить месяцы на подготовку диплома, вы можете всего за несколько дней получить готовый документ. Второе преимущество – это перейти и ознакомиться качество работы. Компании, занимающиеся изготовлением дипломов на заказ, обычно имеют профессиональных авторов, которые специализируются на написании научных работ. Это гарантирует высокий уровень качества и соответствие всех требований учебного заведения. Третье преимущество – это индивидуальный подход. При заказе диплома на сайте вы можете указать все ваши требования и пожелания, а также обсудить детали работы с автором. Это позволяет получить именно тот диплом, который будет соответствовать вашим ожиданиям и требованиям. Четвертое преимущество – это конфиденциальность. Компании, занимающиеся изготовлением дипломов на заказ, гарантируют полную конфиденциальность ваших данных и информации. Вам не нужно беспокоиться о том, что кто-то узнает о том, что вы заказали диплом. Пятое преимущество – это экономия денег. На первый взгляд, изготовление диплома на заказ может показаться дорогим удовольствием, однако если учесть все затраты на подготовку работы самостоятельно (время, усилия, возможные ошибки), то вы поймете, что заказ диплома на сайте – это действительно экономичное решение. Шестое преимущество – это гарантия успешной защиты. Поскольку диплом будет написан профессионалами, у которых большой опыт работы с научными текстами, вы можете быть уверены в том, что ваша работа будет успешно защищена и принята преподавателями. Седьмое преимущество – это возможность заказать диплом на любую тему. Независимо от того, какая у вас специальность и какая тема дипломной работы, вы всегда сможете найти профессионалов, которые напишут вам качественный и уникальный диплом. Восьмое преимущество – это уверенность в том, что ваш диплом не будет содержать ошибок и плагиата. Авторы, работающие на сервисах по изготовлению дипломов на заказ, тщательно проверяют свои работы на наличие ошибок и плагиата, что гарантирует вам получение качественного и оригинального документа. В заключение можно сказать, что изготовление диплома на заказ – это удобное, быстрое и надежное решение для тех, кто хочет получить качественный диплом без лишних забот и хлопот. Благодаря профессиональным авторам, индивидуальному подходу и высокому качеству работы, заказ диплома на сайте становится оптимальным выбором для многих студентов.
Купите диплом и получите преимущество на рынке труда! В современном мире все больше людей обращаются к услугам покупки дипломов. И хотя это может вызвать множество споров и разногласий, есть несколько позитивных аспектов, которые могут побудить человека к такому решению. Прежде всего, стоит отметить, что многие люди, решившие купить диплом, делают это не из-за лени или желания обмануть кого-то, а из-за стремления улучшить свою жизнь. Не всегда образование, полученное официальным путем, отвечает потребностям человека. Некоторые специальности требуют наличия определенного диплома, http://archive-diploms24.com/ но не всегда обучение в университете приносит желаемый результат. Поэтому люди обращаются к альтернативным способам получения образования, чтобы достичь своих целей. Кроме того, покупка диплома может быть оправдана в случаях, когда у человека уже есть определенные знания и навыки, но ему необходимо документально подтвердить свою квалификацию. Например, кто-то работает в определенной сфере деятельности уже много лет, но без диплома http://archive-diploms24.com/ не может продвигаться по карьерной лестнице. В этом случае приобретение диплома может стать решающим шагом для дальнейшего развития профессиональной карьеры. Кроме того, покупка диплома может быть оправдана в случаях, когда человек сталкивается с трудностями в получении образования из-за различных причин. Например, это может быть связано с финансовыми проблемами, семейными обстоятельствами или другими обстоятельствами, которые мешают регулярно посещать занятия в университете. В таких случаях покупка диплома становится единственным способом получить необходимую квалификацию. Кроме того, покупка диплома может быть оправдана в случаях, когда человек хочет сэкономить время и усилия, которые требуются для получения образования официальным путем. Не всегда у людей есть возможность посвятить несколько лет учебе в университете, особенно если они уже работают или имеют другие обязанности. В таких случаях покупка диплома может стать оптимальным вариантом для быстрого достижения поставленных целей. Таким образом, покупка диплома может быть оправдана в ряде случаев, когда у человека есть стремление улучшить свою жизнь, но обычный путь получения образования не подходит. Это может быть связано как с профессиональными целями, так и с личными обстоятельствами, которые мешают получению образования официальным путем. Важно помнить, что каждый случай уникален и требует индивидуального подхода, и что покупка диплома не всегда является негативным явлением, а может быть оправданной в определенных ситуациях.
Купите диплом и получите преимущество на рынке труда! Когда я узнал о возможности купить диплом с занесением в реестр, я был приятно удивлен. Это означало, что я могу получить нужное мне образование без лишних хлопот и траты времени. Я решил попробовать эту услугу и не пожалел ни разу. Первое, что меня порадовало, это быстрая и качественная работа специалистов. Они оперативно оформили все необходимые документы и сделали так, чтобы мой диплом http://archive-diploms24.com/ был внесен в реестр. Я получил свидетельство об образовании, которое выглядит абсолютно официально и не вызывает никаких подозрений. Другой плюс данной услуги – это возможность выбора уровня образования. Я мог выбрать диплом бакалавра, магистра или даже доктора наук. Это дало мне возможность получить необходимую квалификацию и поднять свой уровень образования. Большим плюсом было то, что я мог выбрать специальность и университет, от имени которого будет выдан мой диплом смотреть на сайте . Это позволило мне получить образование в нужной мне области и от уважаемого образовательного учреждения. Кроме того, услуга купить диплом с занесением в реестр позволила мне сэкономить значительное количество времени и денег. Я не тратил месяцы на изучение предметов и сдачу экзаменов, а сразу получил документ, подтверждающий мое образование. Это очень удобно для занятых людей, которым не хватает времени на обучение. Кроме того, я смог избежать стресса, связанного с сдачей экзаменов и защитой дипломной работы. Я был уверен, что мой диплом будет выглядеть абсолютно официально и не вызовет никаких подозрений. В итоге, я получил отличное образование, которое открывает передо мной множество возможностей. Я могу устроиться на работу по специальности, которая мне нравится, и достичь успеха в своей карьере. В заключение, хочу сказать, что услуга купить диплом с занесением в реестр – это отличная возможность для тех, кто хочет получить образование быстро и без лишних хлопот. Я очень доволен результатом и рекомендую эту услугу всем, кто ценит свое время и хочет достичь успеха в своей карьере.