数据库设计规范
数据库命名规范
| 规范 |
备注 |
| 数据库对象名称统一使用小写字母并用下划线分割 |
eg：mkt_tool |
| 数据库对象名称禁止使用 MySQL 保留关键字 |
eg:password,from等 |
| 数据库对象名称做到见名思义，最长不超过32个字符 |
尽可能的精简、明确 |
| 临时表以tmp为前缀，日期为后缀 |
eg：mp_user_20180921 |
| 备份表以bak为前缀，日期为后缀 |
eg：bak_user_20180921 |
| 所有存储相同数据的列名和列类型必须一致 |
eg：a表的uid与b表的uid数据类型与列类型一致 |
数据库基本设计规范
| 规范 |
备注 |
| 所有数据表使用innodb作为存储引擎 |
5.6以后作为默认引擎，支持事务、行级锁、更好的恢复性，高并发下性能更好 |
| 数据库和表的字符集统一使用UTF8 |
主要是因为避免由于字符集转换产生的乱码，其次utf8包含的字符更多，一个汉字占3个字节 |
| 数据表和字段需要添加注释 |
后续维护方便 |
| 单表数据量控制在500万以内 |
单表存储的数据量大小限制取决于存储设置和文件系统 |
| 尽量做到冷热数据分离，减小表的宽度 |
避免查询无用的数据 |
| 禁止在表中预留字段，禁止在数据库中存储图片及文件等二进制数据 |
按需增加，避免表数据快速增长 |
| 禁止在线上做数据库压力测试 |
|
| 禁止开发环境直接连接生产环境数据库 |
避免数据脏乱 |
数据库索引规范
| 规范 |
备注 |
| 限制每张表索引的数量，单表索引不超过5个 |
索引不是越多越好，增加查询效率的同时，会降低更新的效率 |
| 每个innodb表必须有一个主键 |
不使用频繁更新的键，不使用hash，字符串，MD5作为主键，优先选取自动增长的列作为主键 |
| 如何选择联合索引的顺序 |
区分度最高的放在索引的最左侧；字段长度最小的放在最左侧；使用度最频繁的列放在最左侧 |
| 避免建立冗余度和重复列索引增加了优化器生成查询计划的时间 |
对于冗余索引并不会提高索引的性能，反而影响查询计划的生成 |
| 对于频繁的查询优先考虑使用覆盖索引 |
避免Innodb进行索引的二次查找 |
| 尽量避免使用外键 |
外键用于保证参照完整性，建议在业务端实现外键的约束，因为每次写操作都需要检测外键约束从而降低性能。 |
数据库字段规范
| 规范 |
备注 |
| 优先选择存储需要的最小的数据类型 |
int类型相比字符串类型存储空间更小，优先使用无符号的整型存储 |
| 避免使用text、bolb、enum类型 |
使用text类型的时候注意只能使用前缀索引，不能有默认值 |
| 尽可能的定义为NOT NULL |
索引NULL需要额外的空间来保存,进行比较和计算时需要对NULL进行特殊处理 |
| 避免使用字符串存储日期型的数据,使用TIMESTAMP或DATETIME存储 |
1、无法使用日期函数进行比较和计算；2、用字符串存储需要占用更多的空间 |
| 使用decimal类型存储金额类数据 |
1、decimal在计算时不丢失精度，占用的空间由定义的宽度决定；2、可用于存储比bigint更大的数据 |
数据库 SQL 开发规范
| 规范 |
备注 |
| 使用预编译进行数据库操作 |
1、一次解析，多次使用重复使用执行计划；2、避免动态SQL带来的SQL注入； |
| 避免数据类型的隐式转换 |
隐式转换、会导致索引失效 eg： select * from test where id = '1'
|
| 充分利用已经存在的索引 |
1、避免使用双%的查询，使用后%进行替换；2、一个SQL只能利用到复合索引中的一列进行范围查询；3、使用left join或not exists来优化not in操作 |
| 禁止使用select * 查询 |
1、消耗更多的CPU和io资源；2、无法使用到覆盖索引； |
| 禁止使用不含字段列表的INSERT语句 |
明确指定insert的字段 |
| 避免使用子查询，可以把子查询优化为join操作 |
1、子查询的结果集无法使用到索引；2、子查询产生临时表操作，数据量大会影响效率；3、产生的临时表消耗过多的CPU和IO |
| 避免使用JOIN关联太多的表 |
1、每join一个表会占用一部分内存(join_buffer_size)；2、会产生临时表操作，影响查询效率；3、MySQL允许关联61个表，最多不超过5个 |
| 减少同数据库交互的次数 |
1、数据库适合批量处理，避免多次请求数据库；2、合并多个相同的操作到一起，如一次修改多个字段 |
| 使用in代替on |
in的值不超过500个,in可以有效的利用索引的 |
| 禁止使用order by rand()排序 |
order by rand()会把所有符合条件的数据装到内存中 排序 获取 |
| where从句中禁止对列进行函数转换和计算 |
对列进行函数转换会导致无法使用到索引 eg: where date(
time ) = '20180123'
|
| 明显不会有重复值的情况下使用UNION ALL |
UNION会把数据放到临时表进行去重 |
| 拆分复杂的大SQL为多个小SQL |
MySQL一个SQL只能使用一个CPU计算，SQL拆分后可以通过并行执行提高效率 |
数据库操作行为规范
| 规范 |
备注 |
| 超过100万行的数据批量写操作，需要分批多次操作 |
1、大批量的写操作，容易造成主从延迟 2、产生大量的日志 3、避免产生大事务操作 |
| 大表结构修改 |
直接修改大表容易进行锁表，使用pt-online-schema-change修改表结构 |
| 禁止为程序使用的账户授予root权限，遵循权限最小原则 |
程序使用的账户不准有drop权限，程序使用的账户只能连接到一个数据库 |
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
关注我们，获取更多IT资讯^_^
查看所有标签
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
