一 简介:今天咱们来聊聊如何针对开发进行规范和培训
二 目的:为了能够更好的在源头进行规范研发人员对于mysql的应用,从开始就进行规范,能够减少以后一系列的问题
三 阶段:
1 创建表进行规范(请参考创建表规范)
2 查询语句和索引(请参考索引失效场景和查询建议)
3 DML语句规范(请参考DML语句规范)
4 业务场景并发和增长预算(请参考 业务场景估算)
四 创建表规范(create)
1 表级别
1 主要采用innodb引擎+utf8/uft8-mb4以上字符集,不要为单列指定字符集,不要采用memory引擎
2 主键要采用自增ID,对于大表,建议使用bigint,业务需求可以采用唯一索引进行约束
3 不允许使用外键,不能使用集合,枚举.位图类型
4 不要使用事件.存储过程,触发器等消耗mysql性能的功能
5 建表语句不要包含除引号外的其他特殊符合,尤其是反引号
6 表名和字段名一律强制用小写,防止有大小写识别的问题
7 表和字段名不要触及到任何mysql的关键字,创建表最好以业务命名,不允许不同库同表命名相同
8 针对表的数据量一定要提前进行规划好,定期拆分,避免出现问题
2 列级别
1 列必须需要有comment说明,默认值最好不要default not
2 最好不使用blob/text类型,对于范围要求较高的数值,采用UNSIGNED(无符号型)
3 对于时间类型的选择 建议采用 datetime,开销较小,时间范围大,不可以设置系统默认值
4 对于varchar长度设计要根据业务需求,不能设置太大,否则有可能不能建立和适用索引全部
5 对于精度字段的处理，建议选择decimal,也可以使用bigint进行程序二次计算
6 对于特殊场景应采用通用解决办法,比如存储IP采用int类型,然后用函数进行转化
3 索引级别
1 每张表最好不多于5个索引,因为维护索引也会加重负担
2 不要在区分度较低并且更新频率高的字段建立索引,高并发下可能出现死锁问题
3 建立联合索引要符合最左原则,区分度从高到低
4 对于不在应用的索引要提示进行删除维护
5 建立索引要统一以idx_开头命名,实现统一规范
6 针对类似业务的多个sql，选取相同的部分条件建立索引,以达到一索引多用的效果
五 查询场景(select)
1 索引相关
1 索引失效场景
1 在索引列进行数学运算和函数运算
2 使用%前导的查询
3 使用反向查询，如not in / not like
4 使用不等值查询,如=! <>
5 日期DATE_FORMAT使用sysdate()函数
6 字符串查询没有带引号,发生隐式转换
7 多表join,表的字符集不同(比如utf8和utf8_mb4) 会使连接字段的索引失效,因为会发生函数字符集转化
8 不符合联合索引最左原则
9 mysql优化器认为利用索引扫描的成本高于全表扫描,不会利用索引
10 没有任何利用索引的条件查询
11 查询的数据量到达表数据量的至少1/3
12 表本身元数据不准确的情况下会导致explain判断失误
2 语句建议
1 禁止使用order by rand()
2 禁止使用select * from
3 统计表总数要使用select count(*)/select count(1)
4 禁止where无条件,条件无索引
5 优化分页查询,对于数据量大的分页查询,根据业务判断是否能优化
6 多表联查的连接字段,必须要加索引
7 尽量减少多表联查的连接表数量,将大sql拆分为多个小sql
8 对于不再使用的task查询语句,要尽早下线或者降低查询频率
9 尽量将计算交与程序,而不是利用mysql内部函数,消耗数据库的资源
10 尽量提前做好数据库的读写分离,将查询转移到从库,减少主库压力
11 对于慢查询语句必须尽快优化,防止慢查询堆积
六 事务控制(DML语句)
1 事务建议
1 对于事务操作,尽量都根据索引进行操作,最好是主键
2 针对大事务操作,要将大事务拆分为小事务
1 最好按照主键作为拆分条件,针对delete/update操作,
2 values进行合并,针对insert操作
3 对于特殊事务处理,要在业务低峰期做
1 insert into a select b b是要加锁的,并非快照读
2 update a where id in (select b) b同样是要对符合条件的加锁
2 架构设计
1 在程序上线时期就要做好读写分离,防止因为业务增长导致的主库压力
2 redis 作为缓存架构,采用 redis 缓存数据库减少直接操作mysql的次数,减轻主库的压力
3 ES提供mysql类似查询功能, 减少直接操作mysql的次数,减轻主库的压力
4 在业务增长导致的热数据增长时做好库表常规拆分,减少单台DB的压力,防止影响业务,方式就是做好业务增长量估算
5 对于分库分表跨机器操作,建议采用类似sharding-jdbc的程序中间件进行规划拆分库表
6 程序一定要有重连机制,否则不能保证mysql故障切换后程序业务的可用性
七 锁相关
1 将多个同表的DDL语句合并成一条语句进行执行
2 避免死锁的措施
1 对于insert的单事务语句并发,1 减少唯一性列重复值的插入2 去掉唯一性的检测
2 对于update的单事务语句并发,1 尽量减少并发线程数量 2 尽量改成按照主键更新,只锁定对应行 3 改造业务去掉更新
3 对于混合事务语句并发 1 调整加锁顺序 2 减少并发线程数量
3 避免锁等待的措施
1 尽量按照主键/唯一键更新,只扫描需要的主键行 防止由 并发变成串行
小建议: 利用inception在测试环境,inception本身自带很多审核规范,很有用.