MySQL日常使用遵循的规范建议
一 . 基础规范
1.必须使用innodb存储引擎
解读:支持事务;支持行级锁;支持mvcc多版本控制;支持外键;死锁自动检测;并发性能更好、cpu及内存缓存页优化使得资源利用率更高。
2. 表字符集推荐使用utf8mb4
解读:utf8 是 mysql 中的一种字符集,只支持最长三个字节的 utf-8字符,也就是 unicode 中的基本多文本平面(bmp)。任何不在基本多文本平面的 unicode字符,都无法使用 mysql 的 utf8 字符集存储。包括 emoji 表情(emoji 是一种特殊的 unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 unicode 字符等等。要在 mysql 中保存 4 字节长度的 utf-8 字符,需要使用 utf8mb4 字符集,utf8mb4是utf8的超集,有存储4字节例如表情符号时,使用它。
3. 数据表、数据字段必须加入中文注释
解读:方便大家对表的理解,方便dba和开发的沟通,方便历史传承。
4. 禁止使用存储过程、视图、触发器、event
解读:a.对数据库性能影响较大,互联网业务,能让站点层和服务层干的事情,不要交到数据库层,数据库擅长存储和索引。
b.调试,排错,迁移都比较困难,扩展性较差。
5. 禁止存储大文件或者大照片
解读:建议存储到专门的类型数据库中。
二 . 命名规范
1. 库名,表名,列名必须用小写,采用下划线分隔
解读:a.数据库服务器端开了大小写敏感,提高数据库服务器性能
b.只有一套标准,避免abc,abc混淆(sql)
2. 库名,表名,列名必须见名知义,长度不要超过32字符
解读:见名知义,便于理解;长度不要过长,便于书写。
三 . 表设计规范
1. 表必须有主键,推荐使用bigint,表数据量少也可使用unsigned int为主键
解读:删除无主键的表,如果是row模式的主从架构,从库会挂住
2. 主从表可使用自增bigint做主键,冗余无业务含义的字段做关联字段,并做非聚集索引
解读:a.如order表自增主键id,冗余order_no字段,order_detail表自增主键id,冗余order_no字段;order\order_detail和通过order_no关联,并对order_no做非聚集索引
b.可有效减少数据文件和索引文件提示查询性能
3. 禁止使用外键,如果要保证完整性,应由应用程式实现
解读:外键使得表之间相互耦合,影响update/delete等sql性能,有可能造成死锁,高并发情况下容易成为数据库瓶颈点
4. 建议将大字段,访问频度低的字段拆分到单独的表中存储,分离冷热数据
解读:会显著提升该表的查询性能
5. 表必须有字段create_by、create_time、modify_by、modify_time、disabled
解读:便于表的维护。
四 . 列设计规范
1. 根据业务区分使用tinyint/int/bigint,分别会占用1/4/8字
解读:数据量大时,表字段的类型会显著影响性能
2. 根据业务区分使用char/varchar
解读:a.字段长度固定,或者长度近似的业务场景,适合使用char,能够减少碎片,查询性能高
b.字段长度相差较大,或者更新较少的业务场景,适合使用varchar,能够减少空间
3. 根据业务区分使用datetime(3)/timestamp(3)
解读:a.前者占用8个字节,后者占用4个字节,存储年使用year,存储日期使用date,存储时间使用datetime(3)
b.如果需要根据时区显示对应时区的时间,使用timestamp
c.datetime(3)/timestamp(3)保存到毫秒级别,程序端select now(3)
4. 必须把字段定义为not null并设默认值
解读:a.null的列使用索引,索引统计,值都更加复杂,mysql更难优化
b.null需要更多的存储空间
c.null只能采用is null或者is not null,不可使用=/!=/in/not in
5. 使用varchar(20)存储手机号,不要使用整数
解读:a.牵扯到国家代号,可能出现+/-/()等字符,例如+86
b.手机号不会用来做数学运算
c.varchar可以模糊查询,例如like ‘138%’
6. 使用tinyint来代替enum
解读:enum增加新值要进行ddl操作
五 . 索引规范
1. 非聚集索引使用idx_[字段名]来命名
2. 单张表索引数量建议控制在5个以内
解读:a.互联网高并发业务,太多索引会影响写性能
b.生成执行计划时,如果索引太多,会降低性能,并可能导致mysql选择不到最优索引
c.异常复杂的查询需求,可以选择其他等更为适合的方式存储
3. 尽量避免\禁止使用唯一索引
解读:只是比非聚集索引快一个cpu查找的时间,但在delete,update时都要重新排序
4. 组合索引字段数不建议超过3个
解读:如果3个字段还不能极大缩小row范围,很可能是设计有问题
5. 不建议在频繁更新的字段上建立索引
6. 非必要不要进行join查询,如果要进行join查询,被join的字段必须类型相同,并建立索引
解读: join字段类型不一致,会导致全表扫描。
7. 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)
六 . sql语句规范
1. 禁止使用select *,只获取必要字段
解读:a.select *会增加cpu/io/内存/带宽的消耗
b.指定字段能有效利用索引覆盖
c.指定字段查询,在表结构变更时,能保证对应用程序无影响
2. insert必须指定字段,禁止使用insert into t values()
解读:指定字段插入,在表结构变更时,能保证对应用程序无影响
3. 隐式类型转换会使索引失效,导致全表扫描
解读:一定要确保声明的类型和索引字段类型一致
4. 禁止在where条件列使用函数或者表达式
解读:导致不能命中索引,全表扫描
5. 禁止%开头的模糊查询
解读:导致不能命中索引,全表扫描
6. 尽可能不使用大表join和子查询
解读:会占用大量的内存、io、cpu,如果使用到要先缩小结果集在做join(超过1000万的表都应该考虑缩小结果集在做join)
7. 区分inner\left\right\full join,合理使用,join尽量不超过3个表
解读:mysql查询优化器很烂,可在测试环境构造不同的写法,查看对应的执行计划,得出最佳的方案,可使用测试环境最佳实践
8. 同一个字段上的or必须改写为in,in的值必须少于50个
9. 应用程序必须捕获sql异常
解读:方便定位线上问题
上一篇: Django中配置自定义日志系统
下一篇: s21day30 python笔记
推荐阅读
-
Mysql数据库中的 Order by 语句的特殊之处(select 中的项目不必出现在order by中)---不建议使用!
-
MySQL日常使用遵循的规范建议
-
[日常] mysql的索引使用情况测试
-
MySQL数据库char与varchar的区别分析及使用建议_MySQL
-
mysql-Azure上Mysql和MongoDB的使用,有什么好的建议?
-
无特殊需求下Innodb建议使用与业务无关的自增ID作为主键_MySQL
-
mysql-Azure上Mysql和MongoDB的使用,有什么好的建议?
-
RDS最佳实践(三)—如何制定相关的流程来规范RDS的使用_MySQL
-
使用Perl DBI操作MySQL的一些建议_MySQL
-
RDS最佳实践(三)—如何制定相关的流程来规范RDS的使用_MySQL