写在前面:无规矩不成方圆。对于刚加入互联网的朋友们,肯定会接触到MySQL,MySQL作为互联网最流行的关系型数据库产品,它有它擅长的地方,也有它不足的短板,针对它的特性,结合互联网大多应用的特点,笔者根据自己多年互联网公司的MySQL DBA经验,现总结出互联网MySQL的一些开发规范,仅供参考。
一、基础规范
(1) 使用INNODB存储引擎
(2) 表字符集使用UTF8
(3) 所有表都需要添加注释
(4) 单表数据量建议控制在5000W以内
(5) 不在数据库中存储图⽚、文件等大数据
(6) 禁止在线上做数据库压力测试
(7) 禁⽌从测试、开发环境直连数据库
二、命名规范
(1) 库名表名字段名必须有固定的命名长度,12个字符以内
(2) 库名、表名、字段名禁⽌止超过32个字符。须见名之意
(3) 库名、表名、字段名禁⽌止使⽤用MySQL保留字
(4) 临时库、表名必须以tmp为前缀,并以⽇日期为后缀
(5) 备份库、表必须以bak为前缀,并以日期为后缀
三、库、表、字段开发设计规范
(1) 禁⽌使用分区表
(2) 拆分大字段和访问频率低的字段,分离冷热数据
(3) 用HASH进⾏散表,表名后缀使⽤⼗进制数,下标从0开始
(4) 按日期时间分表需符合YYYY[MM][DD][HH]格式
(5) 采用合适的分库分表策略。例如千库十表、十库百表等
(6) 尽可能不使用TEXT、BLOB类型
(7) 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
(8) 越简单越好:将字符转化为数字、使用TINYINT来代替ENUM类型
(9) 所有字段均定义为NOT NULL
(10) 使用UNSIGNED存储非负整数
(11) INT类型固定占用4字节存储
(12) 使用timestamp存储时间
(13) 使用INT UNSIGNED存储IPV4
(14) 使用VARBINARY存储大小写敏感的变长字符串
(15) 禁止在数据库中存储明文密码,把密码加密后存储
(16) 用好数值类型字段
Tinyint (1Byte)
smallint (2Byte)
mediumint (3Byte)
int (4Byte)
bigint (8Byte)
类型 |
字节 |
最小值 |
最大值 |
(带符号的/无符号的) |
(带符号的/无符号的) |
||
TINYINT |
1 |
-128 |
127 |
无符号 |
0 |
255 |
|
SMALLINT |
2 |
-32768 |
32767 |
无符号 |
0 |
65535 |
|
MEDIUMINT |
3 |
-8388608 |
8388607 |
无符号 |
0 |
16777215 |
|
INT |
4 |
-2147483648 |
2147483647 |
无符号 |
0 |
4294967295 |
|
BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
无符号 |
0 |
18446744073709551615 |
如果数值字段没有那么大,就不要用 bigint
(17) 存储ip最好用int存储而非char(15)
(18) 不允许使用ENUM
(19) 避免使用NULL字段
NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效
(20) 少用text/blob,varchar的性能会比text高很多,实在避免不了blob,请拆表
(21) 数据库中不允许存储大文件,或者照片,可以将大对象放到磁盘上,数据库中存储它的路径
四、索引规范
1、索引的数量要控制:
(1) 单张表中索引数量不超过5个
(2) 单个索引中的字段数不超过5个
(3) 对字符串使⽤用前缀索引,前缀索引长度不超过8个字符
(4) 建议优先考虑前缀索引,必要时可添加伪列并建立索引
2、主键准则
(1) 表必须有主键
(2) 不使用更新频繁的列作为主键
(3) 尽量不选择字符串列作为主键
(4) 不使用UUID MD5 HASH这些作为主键(数值太离散了)
(5) 默认使⽤非空的唯一键作为主键
(6) 建议选择自增或发号器
3、重要的SQL必须被索引,比如:
(1) UPDATE、DELETE语句的WHERE条件列
(2) ORDER BY、GROUP BY、DISTINCT的字段
4、多表JOIN的字段注意以下:
(1) 区分度最大的字段放在前面
(2) 核⼼SQL优先考虑覆盖索引
(3) 避免冗余和重复索引
(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例
5、索引禁忌
(1) 不在低基数列上建立索引,例如“性别”
(2) 不在索引列进行数学运算和函数运算
6、尽量不使用外键
(1) 外键用来保护参照完整性,可在业务端实现
(2) 对父表和子表的操作会相互影响,降低可用性
7、索引命名:非唯一索引必须以 idx_字段1_字段2命名,唯一所以必须以uniq_字段1_字段2命名,索引名称必须全部小写
8、新建的唯一索引必须不能和主键重复
9、索引字段的默认值不能为NULL,要改为其他的default或者空。NULL非常影响索引的查询效率
10、反复查看与表相关的SQL,符合最左前缀的特点建立索引。多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,减少索引数量
11、能使用唯一索引就要使用唯一索引,提高查询效率
12、研发要经常使用explain,如果发现索引选择性差,必须让他们学会使用hint
五、SQL规范
(1) sql语句尽可能简单
大的sql想办法拆成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)
(2) 事务要简单,整个事务的时间长度不要太长
(3) 避免使用触发器、函数、存储过程
(4) 降低业务耦合度,为sacle out、sharding留有余地
(5) 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
(4) 不要用select *,查询哪几个字段就select 这几个字段
(5) sql中使用到OR的改写为用 IN() (or的效率没有in的效率高)
(6) in里面数字的个数建议控制在1000以内
(7) limit分页注意效率。Limit越大,效率越低。可以改写limit,比如例子改写:
select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;
(9) 使用union all替代union
(10) 避免使⽤大表的JOIN
(11) 使用group by 分组、自动排序
(12) 对数据的更新要打散后批量更新,不要一次更新太多数据
(13) 减少与数据库的交互次数
(13) 注意使用性能分析工具
Sql explain / showprofile / mysqlsla
(14) SQL语句要求所有研发,SQL关键字全部是大写,每个词只允许有一个空格
(15) SQL语句不可以出现隐式转换,比如 select id from 表 where id='1'
(16) IN条件里面的数据数量要少,我记得应该是500个以内,要学会使用exist代替in,exist在一些场景查询会比in快
(17) 能不用NOT IN就不用NOTIN,坑太多了。。会把空和NULL给查出来
(18) 在SQL语句中,禁止使用前缀是%的like
(19) 不使用负向查询,如not in/like
(19) 关于分页查询:程序里建议合理使用分页来提高效率limit,offset较大要配合子查询使用
(20) 禁止在数据库中跑大查询
(21) 使⽤预编译语句,只传参数,比传递SQL语句更高效;一次解析,多次使用;降低SQL注入概率
(22) 禁止使⽤order by rand()
(23) 禁⽌单条SQL语句同时更新多个表
六、流程规范
(1) 所有的建表操作需要提前告知该表涉及的查询sql;
(2) 所有的建表需要确定建立哪些索引后才可以建表上线;
(3) 所有的改表结构、加索引操作都需要将涉及到所改表的查询sql发出来告知DBA等相关人员;
(4) 在建新表加字段之前,要求研发至少要提前3天邮件出来,给dba们评估、优化和审核的时间
(5)批量导入、导出数据必须提前通知DBA协助观察
(6) 禁⽌在线上从库执行后台管理和统计类查询
(7) 禁⽌有super权限的应用程序账号存在
(8) 推广活动或上线新功能必须提前通知DBA进⾏行流量评估
(9) 不在业务高峰期批量更新、查询数据库
相关推荐
### 阿里巴巴MySQL开发规范详解 #### 一、概述 阿里巴巴MySQL开发规范是一套针对MySQL数据库设计、实现及优化的最佳实践指南。这套规范旨在提高数据库应用的稳定性、性能和可维护性,同时降低潜在的风险。规范主要...
MySQL数据库开发规范MySQL数据库开发规范MySQL数据库开发规范MySQL数据库开发规范
MySQL作为一款广泛使用的开源关系型数据库管理系统,其开发规范包括了命名规则、SQL编写规范、性能优化、架构设计优化以及数据类型的选择等多个方面。 首先,在架构设计方面,80%以上的性能优化应该来源于对整体...
解读阿里巴巴开发规范之MySQL - 知乎
MySQL数据库开发规范方案.pdf
去哪儿MySQL开发规范-完整版,MySQL DBA必须看看
MySQL开发规范总结是针对数据库设计和管理的一套标准,旨在提升效率、标准化开发流程并方便数据库的统一管理。本规范适用于平安科技所有涉及MySQL的开发人员、DBA和运营人员。 1. 引言 - 背景与目的:随着业务的...
MySQL开发规范和原则大全
MySQL开发规范和优化指南为数据库开发人员和管理员提供了一套详细的标准和建议,旨在规范化和标准化MySQL开发设计,以及指导合理使用MySQL以获得最优性能。本文档适用于MySQL 5.0至MySQL 5.6版本,并详细介绍了权限...
mysql数据库开发规范PPT讲义,用于培训新入职开发人员或者学校课堂讲课使用。
MySQL数据库开发规范.pdf
MySQL 设计规范建议根据业务需求选择合适的表类型,避免使用过大的表类型,使用 Partition table 等。 运算与检索 运算与检索是 MySQL 设计规范的一部分。该规范建议使用合适的运算符,避免使用 OR 运算符,使用 ...
随着漫道金服业务的发展,使用MySQL数据库的系统和应用数量不断扩大,为了提高数据库效率,实现标准化开发及便于数据库的统一管理,制定本规范。 本规范适用于所有与MySQL相关的开发人员、数据库管理员与运营人员。
mysql开发规范-网易技术部的MySQL中文资料
该文档涵盖了Oracle和MySQL两大主流数据库系统的具体规范,并针对每种数据库的特点提出了相应的设计和开发建议。 #### 二、数据库整体设计规范 ##### (一)Oracle **1. 设计** - **避免多数据库间通过DBLINK...
资源名称:MySQL开发规范资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
持续借鉴、收集并整理一些开发规范和技巧,期望能更充分利用MySQL的特性,得到更好的性能。 规范是死的,人是活的。 现在定义的规范,是为以后推翻准备的。 目的 提供给开发人员参考,方便写成更有效率的开发。 范围...