1. 首页
  2. IT资讯

干货!MySQL 数据库开发规范

“u003Cpu003EJava后端,选择 u003Cu002Fpu003Eu003Cpu003E优质文章,及时送达u003Cu002Fpu003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002FRcsUnd92eoboDR” img_width=”640″ img_height=”29″ alt=”干货!MySQL 数据库开发规范” inline=”0″u003Eu003Cpu003E上篇 | 35 个小细节,提升 Java 代码运行效率u003Cu002Fpu003Eu003Cpu003E1. 所有的数据库对象名称必须使用小写字母并用下划线分割(MySQL大小写敏感,名称要见名知意,最好不超过32字符)u003Cu002Fpu003Eu003Cpu003E2. 所有的数据库对象名称禁止使用MySQL保留关键字(如 desc、range、match、delayed 等,请参考 MySQL官方保留字 )u003Cu002Fpu003Eu003Cpu003E3. 临时库表必须以tmp为前缀并以日期为后缀(tmp_)u003Cu002Fpu003Eu003Cpu003E4. 备份库和库必须以bak为前缀并以日期为后缀(bak_) u003Cu002Fpu003Eu003Cpu003E5.所有存储相同数据的列名和列类型必须一致。(在多个表中的字段如user_id,它们类型必须一致)u003Cu002Fpu003Eu003Cpu003E6. mysql5.5之前默认的存储的引擎是myisam,没有特殊要求,所有的表必须使用innodb(innodb好处支持失误,行级锁,高并发下性能更好,对多核,大内存,ssd等硬件支持更好)u003Cu002Fpu003Eu003Cpu003E7. 数据库和表的字符集尽量统一使用utf8(字符集必须统一,避免由于字符集转换产生的乱码,汉字utf8下占3个字节)u003Cu002Fpu003Eu003Cpu003E8. 所有表和字段都要添加注释COMMENT,从一开始就进行数据字典的维护u003Cu002Fpu003Eu003Cpu003E9. 尽量控制单表数据量的大小在500w以内,超过500w可以使用历史数据归档,分库分表来实现(500万行并不是MySQL数据库的限制。过大对于修改表结构,备份,恢复都会有很大问题。MySQL没有对存储有限制,取决于存储设置和文件系统)u003Cu002Fpu003Eu003Cpu003E10. 谨慎使用mysql分区表(分区表在物理上表现为多个文件,在逻辑上表现为一个表)u003Cu002Fpu003Eu003Cpu003E11. 谨慎选择分区键,跨分区查询效率可能更低u003Cu002Fpu003Eu003Cpu003E12. 建议使用物理分表的方式管理大数据u003Cu002Fpu003Eu003Cpu003E13. 尽量做到冷热数据分离,减小表的宽度(mysql限制最多存储4096列,行数没有限制,但是每一行的字节总数不能超过65535。列限制好处:减少磁盘io,保证热数据的内存缓存命中率,避免读入无用的冷数据)u003Cu002Fpu003Eu003Cpu003E14. 禁止在表中建立预留字段(无法确认存储的数据类型,对预留字段类型进行修改,会对表进行锁定)u003Cu002Fpu003Eu003Cpu003E15. 禁止在数据中存储图片,文件二进制数据(使用文件服务器)u003Cu002Fpu003Eu003Cpu003E16. 禁止在线上做数据库压力测试u003Cu002Fpu003Eu003Cpu003E17. 禁止从开发环境,测试环境直接连生产环境数据库u003Cu002Fpu003Eu003Cpu003E18. 限制每张表上的索引数量,建议单表索引不超过5个(索引会增加查询效率,但是会降低u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E插入u003Cu002Fiu003E和更新的速度)u003Cu002Fpu003Eu003Cpu003E19. 避免建立冗余索引和重复索引(冗余:index(a,b,c) index(a,b) index(a))u003Cu002Fpu003Eu003Cpu003E20. 禁止给表中的每一列都建立单独的索引u003Cu002Fpu003Eu003Cpu003E21. 每个innodb表必须有一个主键,选择自增id(不能使用更新频繁的列作为主键,不适用UUID,MD5,HASH,字符串列作为主键)u003Cu002Fpu003Eu003Cpu003E22. 区分度最高的列放在联合索引的最左侧u003Cu002Fpu003Eu003Cpu003E23. 尽量把字段长度小的列放在联合索引的最左侧u003Cu002Fpu003Eu003Cpu003E24. 尽量避免使用外键(禁止使用物理外键,建议使用逻辑外键)u003Cu002Fpu003Eu003Cpu003E25. 优先选择符合存储需要的最小数据类型u003Cu002Fpu003Eu003Cpu003E26. 优先使用无符号的整形来存储u003Cu002Fpu003Eu003Cpu003E27. 优先选择存储最小的数据类型(varchar(N),N代表的是字符数,而不是字节数,N代表能存储多少个汉字)u003Cu002Fpu003Eu003Cpu003E28. 避免使用Text或是Blob类型u003Cu002Fpu003Eu003Cpu003E29. 避免使用ENUM数据类型(修改ENUM值需要使用ALTER语句,ENUM类型的ORDER BY操作效率低,需要额外操作,禁止使用书值作为ENUM的枚举值u003Cu002Fpu003Eu003Cpu003E30. 尽量把所有的字段定义为NOT (索引需要额外的空间来保存,所以需要暂用更多的内存,进行比较和计算要对值做特别的处理)u003Cu002Fpu003Eu003Cpu003E31. 使用timestamp或datetime类型来存储时间u003Cu002Fpu003Eu003Cpu003E32. 同财务相关的金额数据,采用decimal类型(不丢失精度,禁止使用 float 和 double)u003Cu002Fpu003Eu003Cpu003E33. 避免使用双%号和like,搜索严禁左模糊或者全模糊(如果需要请用搜索引擎来解决。索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索)u003Cu002Fpu003Eu003Cpu003E34. 建议使用预编译语句进行数据库操作u003Cu002Fpu003Eu003Cpu003E35. 禁止跨库查询(为数据迁移和分库分表留出余地,降低耦合度,降低风险)u003Cu002Fpu003Eu003Cpu003E36. 禁止select * 查询(消耗更多的cpu和io及网络带宽资源,无法使用覆盖索引)u003Cu002Fpu003Eu003Cpu003E37. 禁止使用不含字段列表的insert语句(不允许insert into t values(‘a’,‘b’,‘c’)不允许)u003Cu002Fpu003Eu003Cpu003E38. in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内u003Cu002Fpu003Eu003Cpu003E39. 禁止使用order by rand进行随机排序u003Cu002Fpu003Eu003Cpu003E40. 禁止where从句中对列进行函数转换和计算(例如:where date(createtime)=‘20160901’ 会无法使用createtime列上索引。改成 where createtime>=’20160901′ and createtime <‘20160902’)u003Cu002Fpu003Eu003Cpu003E41. 尽量使用 union all 代替 unionu003Cu002Fpu003Eu003Cpu003E42. 拆分复杂的大SQL为多个小SQL( MySQL一个SQL只能使用一个CPU进行计算)u003Cu002Fpu003Eu003Cpu003E43. 尽量避免使用子查询,可以把子查询优化为join操作(子查询的结果集无法使用索引,子查询会产生临时表操作,如果子查询数据量大会影响效率,消耗过多的CPU及IO资源)u003Cu002Fpu003Eu003Cpu003E44. 超过100万行的批量写操作,要分批多次进行操作(大批量操作可能会造成严重的主从延迟,binlog日志为row格式会产生大量的日志,避免产生大事务操作)u003Cu002Fpu003Eu003Cpu003E45. 对于大表使用pt—online-schema-change修改表结构(避免大表修改产生的主从延迟,避免在对表字段进行修改时进行锁表)u003Cu002Fpu003Eu003Cpu003E46. 对于程序连接数据库账号,遵循权限最小原则u003Cu002Fpu003Eu003Cpu003E47. 超过三个表禁止 join。(需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。)u003Cu002Fpu003Eu003Cpu003E48. 在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。u003Cu002Fpu003Eu003Cpu003E49. SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好u003Cu002Fpu003Eu003Cpu003E50. 使用 IS来判断是否为 值。u003Cu002Fpu003Eu003Cpu003E51. 尽量不要使用物理删除(即直接删除,如果要删除的话提前做好备份),而是使用逻辑删除,使用字段delete_flag做逻辑删除,类型为tinyint,0表示未删除,1表示已删除u003Cu002Fpu003Eu003Cpu003E52. 如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合,索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。u003Cu002Fpu003Eu003Cpu003E53. 在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E参考:u003Cu002Fh1u003Eu003Cpu003E[1]《阿里巴巴Java开发手册》[2]《高性能可扩展MySQL数据库设计及架构优化 电商项目》u003Cu002Fpu003Eu003Cpu003Eu003Cstrong toutiao-origin=”span” class=”highlight-text”u003E链接:u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E作者 | 在云端u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E链接 | juejin.imu002Fpostu002F5c15c2b3f265da6u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E1700u003Cu002Fiu003E70613 u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E-END-u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E如果看到这里,说明你喜欢这篇文章,请转发、点赞。u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-1″u003E微信u003Cu002Fiu003E搜索「web_resource」,u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-2″u003E关注u003Cu002Fiu003E后回复「进群」或者扫描下方二维码即可进入无广告交流群。u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E↓扫描二维码进群↓u003Cu002Fpu003E”

原文始发于:干货!MySQL 数据库开发规范

主题测试文章,只做测试使用。发布者:逗乐男神i,转转请注明出处:http://www.cxybcw.com/17820.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code