数据库命令规范
数据库基本设计规范1. 所有表必须使用Innodb存储引擎2. 数据库和表的字符集统一使用UTF8兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效,如果数据库中有存储emoji表情的需要,字符集需要采用utf8mb4字符集。 3. 所有表和字段都需要添加注释使用comment从句添加表和列的备注,从一开始就进行数据字典的维护 4. 尽量控制单表数据量的大小,建议控制在500万以内。可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小 5. 谨慎使用Mysql分区表建议采用物理分表的方式管理大数据。 6. 尽量做到冷热数据分离,减小表的宽度
经常一起使用的列放到一个表中(避免更多的关联操作)。 7. 禁止在表中建立预留字段对预留字段类型的修改,会对表进行锁定。 8. 禁止在数据库中存储图片,文件等大的二进制数据通常存储于文件服务器,数据库只存储文件地址信息 9. 禁止在线上做数据库压力测试10. 禁止从开发环境,测试环境直接连接生成环境数据库数据库字段设计规范1. 优先选择符合存储需要的最小的数据类型
UNSIGNED INT 0~4294967295 VARCHAR(N)中的N代表的是字符数,而不是字节数,使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存。 2. 避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据
Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。 而且对于这种数据,Mysql还是要进行二次查询,会使sql性能变得很差,但是不是说一定不能使用这样的数据类型。 如果一定要使用,建议把BLOB或是TEXT列分离到单独的扩展表中,查询时一定不要使用select * 而只需要取出必要的列,不需要TEXT列的数据时不要对该列进行查询。
因为MySQL对索引字段长度是有限制的,所以TEXT类型只能使用前缀索引,并且TEXT列上是不能有默认值的 3. 避免使用ENUM类型禁止使用数值作为ENUM的枚举值 4. 尽可能把所有列定义为NOT NULL进行比较和计算时要对NULL值做特别的处理 5. 使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间
6. 同财务相关的金额类数据必须使用decimal类型
索引设计规范1. 限制每张表上的索引数量,建议单张表索引不超过5个因为mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,同样会降低查询性能。 2. 禁止给表中的每一列都建立单独的索引5.6版本之前,一个sql只能使用到一个表中的一个索引,5.6以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好。 3. 每个Innodb表必须有个主键
常见索引列建议
如何选择索引列的顺序
避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)
对于频繁的查询优先考虑使用覆盖索引
Innodb是以聚集索引的顺序来存储的,对于Innodb来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。 而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了IO操作,提升了查询效率。
索引SET规范尽量避免使用外键约束
数据库SQL开发规范1. 建议使用预编译语句进行数据库操作相同语句可以一次解析,多次使用,提高处理效率。 2. 避免数据类型的隐式转换
3. 充分利用表上已经存在的索引避免使用双%号的查询条件。如: 一个SQL只能利用到复合索引中的一列进行范围查询。如:有 a,b,c列的联合索引,在查询条件中有a列的范围查询,则在b,c列上的索引将不会被用到。 在定义联合索引时,如果a列要用到范围查找的话,就要把a列放到联合索引的右侧,使用left join 或 not exists 来优化not in 操作,因为not in 也通常会使用索引失效。 4. 数据库设计时,应该要对以后扩展进行考虑5. 程序连接不同的数据库使用不同的账号,进制跨库查询
6. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询
7. 禁止使用不含字段列表的INSERT语句
8. 避免使用子查询,可以把子查询优化为join操作由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。 9. 避免使用JOIN关联太多的表同时对于关联操作来说,会产生临时表操作,影响查询效率,Mysql最多允许关联61个表,建议不超过5个。 10. 减少同数据库的交互次数数据库更适合处理批量操作,合并多个相同的操作到一起,可以提高处理效率。 11. 对应同一列进行or判断时,使用in代替orin 的值不要超过500个,in 操作可以更有效的利用索引,or大多数情况下很少能利用到索引。 12. 禁止使用order by rand() 进行随机排序推荐在程序中获取一个随机值,然后从数据库中获取数据的方式。 13. WHERE从句中禁止对列进行函数转换和计算
14. 在明显不会有重复值时使用UNION ALL 而不是UNION
15. 拆分复杂的大SQL为多个小SQL
数据库操作行为规范超100万行的批量写(UPDATE、DELETE、INSERT)操作,要分批多次进行操作主从环境中,大批量操作可能会造成严重的主从延迟,大批量的写操作一般都需要执行一定长的时间 而只有当主库上执行完成后,才会在其他从库上执行,所以会造成主库与从库长时间的延迟情况 大批量写操作会产生大量日志,特别是对于row格式二进制数据而言,由于在row格式中会记录每一行数据的修改,我们一次修改的数据越多,产生的日志量也就会越多,日志的传输和恢复所需要的时间也就越长,这也是造成主从延迟的一个原因 大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞,阻塞会对MySQL的性能产生非常大的影响。 特别是长时间的阻塞会占满所有数据库的可用连接,这会使生产环境中的其他应用无法连接到数据库,因此一定要注意大批量写操作要进行分批 对于大表使用pt-online-schema-change修改表结构
对大表数据结构的修改一定要谨慎,会造成严重的锁表操作,尤其是生产环境,是不能容忍的。 pt-online-schema-change它会首先建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器。 把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原来的表删除掉。把原来一个DDL操作,分解成多个小的批次进行。 禁止为程序使用的账号赋予super权限
对于程序连接数据库账号,遵循权限最小原则
|
|