为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL数据库六大设计规范总结2

标签:
MySQL

数据库字段设计规范

    优先选择符合存储需要的最小的数据类型

        将字符串转换为数字类型存储

        例如:INET_ATON('255.255.255.255') = 4294967295;

                  INET_NTOA(4294967295) = '255.255.255.255';

        对于非负型的数据来说,要优先使用无符号整型来存储

        VARCHAR(N)中的N代表的是字符数,而不是字节数

        使用UTF8存储汉字Varchar(255) = 765字节

        过大的长度会消耗更多的内存

    避免使用TEXT、BLOB数据类型

        建议把BLOB/TEXT列分离到单独的扩展表中

        TEXT/BLOB类型只能使用前缀索引

    避免使用ENUM数据类型

        修改ENUM值需要使用ALTER语句

        ENUM类型的ORDER BY操作效率低,需要额外操作
        禁止使用数值作为ENUM的枚举值

    尽可能把所有列定义为NOT NULL

        索引NULL列需要额外的空间来保存,所以要占用更多的空间

        进行比较和计算时要对NULL值做特别的处理

    使用TIMESTAMP/DATETIME类型存储时间

        TIMESTAMP占用4字节和INT相同,但比INT可读性高

        超出TIMESTAMP取值范围的使用DATETIME类型

        字符串存储日期型的数据会导致无法用日期函数进行计算和比较,占用更多的空间

    同财务相关的金额类数据,必须使用decimal类型

        精准浮点数,在计算时不会丢失精度

        占用空间由定义的宽度决定

        可用于存储比bigint更大的整数数据


数据库SQL开发规范

    使用预编译语句进行数据库操作

        只传参数,比传递SQL语句更高效

        相同语句可以一次解析,多次使用,提高处理效率

    避免数据类型的隐式转换

        会导致索引失效

    充分利用表上已经存在的索引

        避免使用双%号的查询条件。如name like '%zhangsan%';

        一个SQL只能利用到复合索引中的一列进行范围查询

        使用LEFT JOIN/NOT EXISTS 来优化NOT IN 操作

    程序链接不同的数据库使用不同的账号,禁止跨库查询

        为数据库迁移和分库分表留出余地

        降低业务耦合度

        避免权限过大而产生的安全风险

    禁止使用SELECT * 必须使用SELECT <字段列表>查询

        消耗更多的CPU和IO以及网络带宽资源

        无法使用覆盖索引

        可减少表结构变化带来的影响

    禁止使用不含字段列表的INSERT语句

        insert into users values('zs', 19, 'male');

        insert into users('name', 'age', 'gender') valuse('zs', 19, 'male');

        可减少表结构变更带来的影响

    避免使用子查询,可以把子查询优化为join操作

        子查询的结果集无法使用索引
        子查询会产生临时表操作,如果子查询数据量大则严重影响效率

        消耗过多的CPU及IO资源

    避免使用JOIN关联太多的表

        每JOIN一个表会多占用一部分内存(join_buffer_size)

        会产生临时表操作,影响查询效率

    减少同数据库的交互

        数据库更适合处理批量操作

        合并多个相同的操作到一起,可以提高处理效率

        例如ALTER TABLE t1 add column c1 int, change column c2 c3 int...

    使用in代替or

        in操作可以有效的利用索引

    禁止使用ORDER BY rand()进行随机排序

        会把表中所有符合条件的数据装载到内存中进行排序

        会消耗大量的CPU和IO内存资源

        推荐在程序中获取一个随机值,然后从数据库中获取数据的方式

    WHERE从句中禁止对列进行函数转换和计算

        导致无法使用索引

    在明显不会有重复值时使用UNION ALL而不是UNION

        UNION会把所有数据放到临时表中后在进行去重操作

        UNION ALL不会再对结果集进行去重操作

    拆分复杂的大SQL为多个小SQL

        MySQL一个SQL只能使用一个CPU进行计算

        SQL拆分后可以通过并行执行来提高处理效率

   

数据操作行为规范

    超100万行的批量写操作,要分批多次进行操作

        大批量操作可能会造成严重的主从延迟

        binlog日志为row格式时会产生大量的日志

        避免产生大事务操作

    对于大表使用pt-online-schema-change修改表结构

        避免主从延迟

        避免锁表

    禁止为程序使用的账号赋予super权限

        达到最大连接数限制时,还允许1个有super权限的用户连接

        super权限只能留给DBA处理问题的账号使用

    对于程序连接数据库账号,遵循权限最小原则

        只能在一个DB下使用,不准跨库

        不准有DROP权限

点击查看更多内容
2人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
PHP开发工程师
手记
粉丝
11
获赞与收藏
116

关注作者,订阅最新文章

阅读免费教程

感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消