MySQL开发规范
基本规范:
使用INNODB存储引擎
* 5.5以后的默认引擘,支持事务,行级锁,更好的恢复性,高并发下性能更好,对多核,大内存,ssd等硬件支持更好
* 表字符集使用UTF8
* 使用utf8字符集,如果是汉字,占3个字节,但ASCII码字符还是1个字节
* 统一,不会有转换产生乱码风险
* 单表数据量建议控制在5000万以内
字段设计规范:
> 尽可能不要使用TEXT、BLOB类型
* 删除这种值会在数据表中留下很大的"空洞"
* 可以考虑把BLOB或TEXT列分离到单独的表中
> 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
* 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围
浮点数的缺点是会引起精度问题
> 将字符转化为数字
> 使用TINYINT来代替ENUM类型
> 字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量
* the best strategy is to allocate only as much space as you really need
* VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N
* VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存
> 如果可能的话所有字段均定义为not null
> 使用UNSIGNED存储非负整数
* 同样的字节数,存储的数值范围更大。如tinyint 有符号为 -128-127,无符号为0-255
> INT类型固定占用4个字节存储
> 使用TIMESTAMP存储时间
* 因为TIMESTAMP使用4字节,DATETIME使用8个字节, 同时TIMESTAMP具有自动赋值以及自动更新的特性
> 使用INT UNSIGNED 存储IPV4
> 使用VARBINARY存储大小写敏感的变长字符串
> 禁止在数据库中存储明文密码
SQL规范:
> 使用预编译语句prepared statement
* 只传参数,比传递SQL语句更高效
* 一次解析,多次使用
* 降低SQL注入概率
> 尽量避免相同语句由于书写格式的不同,而导致多次语法分析
> 避免隐式转换
* 会导致索引失效,如 select userid from table where userid=’1234’
> 充分利用前缀索引
* 必须是最左前缀
* 不可能同时用到两个范围条件
> 避免使用存储过程、触发器、EVENTS等
* 让数据库做最擅长的事
* 降低业务耦合度,为Scale Out、Sharding 留点余地
* 避开BUG
> 避免使用大表的join
* MySQL最擅长的是单表的主键/二级索引查询
* Join消耗较多的内存,产生临时表
> 避免在数据库中进行数学运算
* 容易将业务逻辑和DB耦合在一起
* MySQL不擅长数学运算和逻辑判断
* 无法使用索引
> 减少与数据库的交互次数
* Insert … on duplicate key update
* Replace into 、 insert ignore、insert into values(),(),()…
* Update … where id in (1,2,3,4)
* Alter table tbl_name add column col1, add column col2
> 拒绝大SQL,拆分成小SQL
* 充分利用query cache
* 充分利用多核CPU
> 使用in代替or,in的值不超过1000个
> 禁止使用order by rand()
* 因为ORDER BY rand()会将数据从磁盘中读取,进行排序,会消耗大量的IO和CPU,可以在程序中获取一个rand值,然后通过在从数据库中获取对应的值
> 使用union all 而不是union
> 程序应有捕获SQL异常的处理机制
> 禁止单条SQL语句同时更新多个表
> 不使用select *
* 消耗cpu和IO、消耗网络带宽
* 无法使用覆盖索引
* 减少表结构变更带来的影响
索引规范:
索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
> 单张表中索引数量不超过5个
> 单个索引中的字段数不超过5个
> 对字符串使用前缀索引,前缀索引长度不超过10个字符
* 如果有一个CHAR(200) 列,如果在前10个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个字符进行索引能够节省大量索引空间,也可能会使查询更快
> 表必须有主键
> 不使用更新频繁地列作为主键
> 尽量不选择字符串列作为主键
> 不使用UUID、MD5、HASH 作为主键
> 默认使用非空的唯一键
> 主键建议选择自增或发号器
> 重要的SQL必须被索引
* SELECT、UPDATE、DELETE语句的WHERE条件列
* ORDER BY、GROUP BY、DISTINCT的字段
* 多表JOIN的字段
> 区分度最大的字段放在索引前面
> 核心SQL优先考虑覆盖索引
* select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
> 避免冗余或重复索引
* 合理创建联合索引(避免冗余),index(a,b,c) 相当于 index(a) 、index(a,b) 、index(a,b,c)
> 索引不是越多越好,按实际需要进行创建
* 每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能
> 不在低基数列上建立索引,例如‘性别’
> 不在索引列进行数学运算和函数运算
> 尽量不要使用外键
* 外键用来保护参照完整性,可在业务端实现
* 对父表和子表的操作会相互影响,降低可用性
* INNODB本身对Online DDL的限制
> 不使用%前导的查询,如like “%xxx”
* 无法使用索引
> 不使用反向查询,如 not in / not like
* 无法使用索引,导致全表扫描
* 全表扫描导致buffer pool利用降低