MySQL推荐使用规范

非原创,我只是一个搬运工!!!
在讲到SQL语句优化前,有必要知道业界的优良做法是什么。下面的规范,应该是来自阿里和58到家的开发规范。

规范

基础规范

  1. 表存储引擎必须使用InnoDB
    支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。
  2. 表字符集默认使用utf8,必要时使用utf8mb4
    通用,无需转码,无乱码风险, 支持emoji表情以及部分不常见汉字;汉字3字节,英文1字节;utf8mb4是utf8的超集,有存储4字节例如表情符号时,使用它。
  3. 表、字段必须加注释
  4. 不在数据库做计算
  5. 禁止存储文件
    文件存储在文件系统,可在数据库里存URI;
  6. 控制单表数据量
    单表记录控制在千万级
  7. 禁止使用存储过程,视图,触发器,Event
    • 对数据库性能影响较大,互联网业务,能让站点层和服务层干的事情,不要交到数据库层;
    • 调试,排错,迁移都比较困难,扩展性较差;
    • 在并发量大的情况下,这些功能很可能将数据库拖跨,业务逻辑放到服务层具备更好的扩展性,能够轻易实现增机器就加性能。
  8. 禁止在线上环境做数据库压力测试
  9. 测试,开发,线上数据库各个环境必须隔离

命名规范

  1. 库名、表名、字段名
    小写,下划线风格,做到见名知义,长度不要超过32字符;
    唯一索引使用uniq_[字段名]来命名,非唯一索引使用idx_[字段名]来命名;
    库备份必须以bak为前缀,以日期为后缀;
    从库必须以-s为后缀;
    备库必须以-ss为后缀;

字段设计规范

  1. 把字段定义为NOT NULL并提供默认值
    • null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化;
    • null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段时,数据库的处理性能会降低很多;
    • null值需要更多的存储空间,无论是表还是索引中每行中的null的列都需要额外的空间来标识;
    • 对null处理时,只能采用is null或is not null,而不能采用=、in、<、<>、!=、not in这些操作符号。如:where name != 'johnny',如果存在name为null值的记录,查询结果就不会包含name为null值的记录
  2. 不要使用TEXT、BLOB类型
    会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能,如果必须要使用则独立出来一张表,用主键来对应,避免影响其它字段索引效率
  3. 不要使用小数存储货币
    建议使用整数,小数容易导致钱对不上
  4. 使用varchar(20)存储手机号,不要使用整数
    • 牵扯到国家代号,可能出现+/-/()等字符,如+86
    • 手机号不会用来做数学运算
    • varchar可以模糊查询,如like ‘138%’
  5. 为提高效率可以牺牲范式设计,冗余数据,条件必须满足:
    • 不是频繁修改的字段
    • 不是varchar超长字段,更不能是text字段
  6. 单实例表个数必须控制在2000个以内,单表分表个数必须控制在1024个以内;表必须有主键,推荐使用UNSIGNED整数为主键。删除无主键的表,如果是row模式的主从架构,从库会挂住
  7. 禁止使用外键,如果要保证完整性,应由应用程式实现
    外键使得表之间相互耦合,影响update/delete等SQL性能,有可能造成死锁,高并发情况下容易成为数据库瓶颈;
    建议将大字段,访问频度低的字段拆分到单独的表中存储,分离冷热数据。
  8. 表必须有主键,如自增主键
    • 主键递增,数据行写入可以提高插入性能;
    • 主键要选择较短的数据类型,Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率;
    • 保证实体的完整性,唯一性;
  9. 不要使用外键,如果有外键约束,用应用程序控制
    外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。高并发情况下容易造成数据库性能下降,大数据高并发业务场景数据库使用以性能优先
  10. 根据业务区分使用tinyint/int/bigint,分别会占用1/4/8字节;根据业务区分使用char/varchar。
    1)字段长度固定,或长度近似的业务场景,适合使用char,能够减少碎片,查询性能高;
    2)字段长度相差较大,或者更新较少的业务场景,适合使用varchar,能够减少空间;
  11. 根据业务区分使用datetime/timestamp
    解读:前者占用5个字节,后者占用4个字节,存储年使用YEAR,存储日期使用DATE,存储时间使用datetime
  12. 使用INT UNSIGNED存储IPv4,不要用char(15)
  13. 使用TINYINT来代替ENUM
    ENUM增加新值要进行DDL操作

索引设计规范

  1. 禁止在更新十分频繁、区分度不高的属性上建立索引
    a)更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能
    b)区分度不大的属性,建立索引是没有什么意义的,如性别
  2. 建立组合索引,必须把区分度高的字段放在最左边,且组合索引字段数不建议超过5个
    如果where a=? and b=?,a列几乎接近于唯一值,那么只需要单建 idx_a 索引即可;如果5个字段还不能极大缩小row范围,八成是设计有问题。
  3. 页面搜索严禁左模糊或全模糊
    索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引,如果需要请走搜索引擎来解决
  4. 单张表索引数量建议控制在5个以内
    互联网高并发业务,太多索引会影响写性能;
    生成执行计划时,如果索引太多,会降低性能,并可能导致MySQL选择不到最优索引;
    异常复杂的查询需求,可以选择ES等更为适合的方式存储。
  5. 非必要不要进行JOIN查询,如果要进行JOIN查询,被JOIN的字段必须类型相同,并建立索引
    因为JOIN字段类型不一致,会导致全表扫描;
  6. 理解组合索引最左前缀原则,避免重复建设索引,如果建立(a,b,c),相当于建立(a), (a,b), (a,b,c)

SQL使用规范

  1. 禁止使用SELECT *,只获取必要的字段,需要显式说明列属性
    原因:
    消耗cpu,io,内存,带宽
    指定字段能有效利用索引覆盖
    使用SELECT *容易在增加或者删除字段后出现程序BUG,不具有扩展性
  2. 使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性
    容易在增加或删除字段后出现程序BUG
  3. 务必请使用同类型进行比较(隐式类型转换会使索引失效),否则可能导致全表扫描:SELECT name FROM user WHERE phone=1333333333会导致全表扫描
  4. 禁止在WHERE条件的上使用函数或表达式
    SELECT name FROM tuser WHERE date(createdatatime)='2017-12-15'会导致全表扫描
    推荐写法:SELECT name FROM tuser WHERE createdatatime>= '2017-02-15 ' and create_datatime < '2017-02-16 '
  5. 禁止负向查询,以及%开头的模糊查询
    负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描;%开头的模糊查询,会导致全表扫描
  6. 大表不要使用JOIN查询,禁止大表使用子查询
    会产生临时表,消耗较多内存与CPU,极大影响数据库性能
  7. 同一个字段上的OR必须改写问in或union,in的值必须少于50个,因为or关键字不会走索引
  8. 事务要尽可能简单
    事务就像程序中的锁一样粒度尽可能要小
  9. 不要一次更新大量数据
    数据更新会对行或者表加锁,应该分为多次更新
  10. 应用程序必须捕获SQL异常
    方便定位线上问题
  11. insert必须指定字段,禁止使用insert into T values()
    指定字段插入,在表结构变更时,能保证对应用程序无影响

参考

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

johnny233

晚饭能不能加鸡腿就靠你了

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值