前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL数据库开发规范知识点速查

MySQL数据库开发规范知识点速查

作者头像
linxinzhe
发布2018-04-16 10:56:19
1.5K0
发布2018-04-16 10:56:19
举报
文章被收录于专栏:林欣哲

数据库设计规范

  • 命名规范
  • 基本设计规范
  • 索引设计规范
  • 字段设计规范
  • SQL开发规范
  • 操作行为规范

命名规范

  • 对象名称使用小写字母并用下划线分割
  • 禁止使用MySQL保留关键字
  • 见名识义,最好不超过32个字符。例如:用户数据库,mcuserdb(公司+user+db),用户表,useraccount
  • 临时表,以tmp为前缀,以日期为后缀
  • 备份表,以bak为前缀,以日期为后缀
  • 存储相同数据的列名和列类型必须一致(关联列类型相同用索引高效)

基本设计规范

  • 所有表用Innodb存储引擎
    • 支持事务
    • 行级锁
    • 更好的恢复性
    • 高并发的性能更好
  • 数据库和表的字符集统一使用UTF-8,统一可避免乱码
  • 所有表和字段添加注释( COMMENT从句)
  • 尽量控制单表数据量,建议控制在500万以内。(不是MySQL的限制,是经验值)
    • 处理方式:历史数据归档、分库分表
  • 谨慎使用MySQL分区表
    • 分区表:在物理上表现为多个文件,在逻辑上表现为一个表
    • 问题:谨慎选择分区键,跨分区查询效率可能更低
    • 建议:采用物理分表的方式管理大数据
  • 尽量做到冷热数据分离,减小表的宽度
    • 限制:一个表最多4096列
    • 减少磁盘IO,保证热数据的内存缓存命中率
    • 利用更有效的缓存,避免读入无用的冷数据
    • 建议:经常一起用的列放在一个表中
  • 禁止在表中建预留字段
    • 无法见名识义
    • 无法确定数据类型
    • MySQL修改预留字段比增加还麻烦,涉及对表的锁定
  • 禁止在数据库中存储图片,文件等二进制数据
  • 禁止在线上做数据库压力测试
  • 禁止从开发或测试环境直接连接生产环境数据库

索引设计规范

  • 限制每张表的索引数量,建议单表索引不超过5个
    • 索引增加查询效率,但是降低插入和更新效率
    • 禁止给每一列都建立单独的索引
  • 每个Innodb表必须有一个主键
    • 不使用更新频繁的列作为主键,不使用多列主键,因为更新后就涉及对索引顺序的修改,频繁更新会导致频繁调整,导致降低性能
    • 不使用UUID,md5,hash字符串作为主键,因为这类哈希不保证插入时递增的特性
    • 建议:使用自增ID值
  • 在哪建立索引?
    • SELECT, UPDATE, DELETE语句中的WHERE从句中的常出现的列
    • ORDER BY, GROUP BY, DISTINCT中的字段
    • 多表JOIN的关联列
  • 建立索引的顺序?
    • 区分度差不多的情况下,尽量把字段长度小的列放在联合索引的最左边
    • 两者还差不多的情况下,使用最频繁的列放在联合索引的左侧
    • 区分度最高的列放在联合索引的最左侧。区分度计算:Selectivity = Distinct Values / Total Number Rows,区分度最大的就是主键(区分度为1)
  • 避免建立冗余索引和重复索引
    • 重复索引例子: primary key(id),index(id),unique index(id)这三个就重复建立id的索引了
    • 冗余索引例子: index(a,b,c),index(a,b),index(a)对于a来说就重复建立了。
  • 对于频繁的查询优先考虑使用覆盖索引。
    • 覆盖索引:包含了所有查询字段的索引
    • 避免Innodb表进行索引的二次查找
    • 可以把随机IO变为顺序IO加快查询效率
  • 尽量避免使用外键
    • 外键是用于保证数据的参照完整性,但建议在业务端实现。
    • MySQL外键会建立索引
    • 不建议使用外键约束
    • 表与表之间的关联键建立索引是必须的
    • 外键会影响父表和子表的写操作而降低性能(检查约束导致的)

字段设计规范

  • 优先选择符合存储需要的最小的数据类型
    • 将字符串转化为数字类型存储
    • 非负的用无符号整形
    • VARCHAR(N)的N代表是字符数,不是字节数,使用UTF-8存储汉字 VARCHAR(255)=765个字节
    • 过大的长度会消耗更多的内存
  • 避免使用 TEXTBLOB数据类型
  • 避免使用 ENUM数据类型
    • 修改 ENUM值会导致表结构的修改
    • ENUMORDER BY需要额外操作,效率低
    • 禁止使用数值作为ENUM的枚举值
  • 尽可能把所有列定义为 NOT NULL
    • 索引 NULL列需要额外空间,占用更多空间
  • 使用 TIMESTAMPDATETIME类型存储时间。不要用字符串存储日期类型(无法利用内置日期函数而且占用更多空间)
  • 涉及财务的金额,必须用 DECIMAL类型
    • 精确浮点,计算不会丢失精度
    • 占用空间由定义的宽度决定
    • 可用于存储比 BIGINT更大的整数数据

SQL开发规范

  • 建议使用预编译语句进行数据库操作
    • 只传参数,多次使用,执行更快
    • 可避免动态SQL注入问题
  • 避免数据类型的隐式转换
    • 会导致索引失效
  • 合理利用存在索引,而不是盲目增加索引
  • 充分利用表上已经存在的索引
    • 避免使用双%号的查询条件,如 a LIKE'%123%'
    • 一个SQL只能利用到复合索引中的一列进行范围查询
    • 使用 LEFT JOINNOT EXISTS 来优 NOT IN操作(可能导致索引失效)
  • 禁止跨库查询,程序连接不同的数据库使用不同的账号
    • 为数据库迁移和分库分表留出余地
    • 降低业务耦合度
    • 避免安全风险
  • 禁止使用 SELECT*,需要用 SELECT<字段列表>查询
    • *返回不需要的字段
    • 无法使用覆盖索引
  • 禁止使用不含字段列表的INSERT语句
    • 禁止这种 INSERT INTO t VALUES('a','b','c')应该带上 INSERT INTO t(c1,c2,c3)VALUES('a','b','c')
  • 避免使用子查询,可以把子查询优化为 JOIN操作
    • 子查询的结果集无法使用索引
    • 子查询会产生临时表操作,如果子查询数据量大则严重影响效率
  • 避免使用 JOIN关联太多的表
    • JOIN一个表会多占用一部分内存(joinbuffersize)
    • 会产生临时表,影响查询效率
    • MySQL最多允许关联61个表,建议不超过5个
  • 减少同数据库的交互次数
    • 数据库更适合处理批量操作
    • 合并多个操作,可以提高处理效率
  • 使用 IN代替 OR
    • IN的值不超过500个
    • IN的操作可以有效的利用索引
  • 禁止使用 ORDER BY rand()进行随机排序
    • 会加载到内存再排序,消耗大量CPU和IO和内存
    • 建议:在程序中生成随机值,再获取数据
  • 禁止WHERE从句中队列进行函数转换和计算
    • 导致无法使用索引
  • 明显不会有重复值的用 UNION ALL而不是 UNION
    • UNION会把所有数据放到临时表中后再进行去重操作
    • UNION ALL则不会做去重操作
  • 拆分复杂的大SQL为多个小SQL
    • 一个SQL只用一个CPU计算
    • 拆分后可以并行执行

操作行为规范

  • 超过100万行的批量写操作,要分批多次进行操作
    • 注意之前提到的尽量合并操作是针对查询
    • 大批量的可能会造成主从延迟
    • binlog日志为row格式时会产生大量的日志
    • 避免产生大事务操作,造成锁定和大堵塞。
  • 对大表数据结构的修改一定要谨慎,会造成严重的锁表操作。尤其是生产环境,是不能忍受的。
  • 对于大表使用pt-online-schema-change(PERCONA公司的工具)修改表结构
    • 复制出一个新表,再修改新表为原表名称
    • 避免主从延迟
    • 避免修改时的锁表
  • 禁止程序使用super权限的账号
    • super可以在达到最大连接限制连上用户,但是只能有一个super账号连接,应该交给DBA处理问题用,不应被程序占用
  • 数据库账号遵循权限最小原则
    • 只需要查询就别给其他操作权限
    • 数据库账号只能在同一个DB下使用,不允许跨库
    • 程序的账号原则上不允许有drop权限

参考

  1. 高性能可扩展MySQL数据库设计及架构优化,sqlercn,https://coding.imooc.com/class/79.html
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-04-14,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 林欣哲 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 数据库设计规范
    • 命名规范
      • 基本设计规范
        • 索引设计规范
          • 字段设计规范
            • SQL开发规范
              • 操作行为规范
                • 参考
                相关产品与服务
                云数据库 SQL Server
                腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
                领券
                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档