mysql入门到熟练

一，数据库规范

1，命名规范

• 全小写，数据库对象名称使用小写，下划线分割-----原因：大小写敏感，方便管理
• 避开保留字，对象名称不使用mysql保留关键字，比如sql语句from，filter等
• 语意化，且最好不超过32字符
• 临时表，以tmp为前缀，日期为后缀，方便清理
• 备份，备份库和表，要以bak为前缀，以日期为后缀
• 一致性，一个字段在不同表中，列名和类型要保持一致；不一致会发生隐式类型转换和索引失效，降低效率和出错

2，设计规范

• 使用Innodb存储引擎，而不是Myisam(5.5以前版本)，innodb支持事务，行级锁，更好的恢复性，更好的高并发性能
• 统一使用字符集，utf8兼容性好，其他看场景，ascii码更小的空间
• 表和字段添加注释，comment语句
• 控制单表大小，最好在500万行以内，历史归档（日志），分库分表（业务）
• 冷热数据分离，减小列数，单表最多4096列，单行最大65535字节；列数越多，加载时占用内存和IO压力越大
• 禁止预留字段，
• 禁止储存图片等二进制数据，比较大，影响性能；内存和IO；替换为存储图片路径
• 禁止在线上做数据库压测
• 开发环境，测试环境连接生产数据库

3，索引设计规范

索引会增加查询效率，可能降低插入，更新，查询的效率

• 少索引，单张表最好不超过5个索引
• 主键不宜频繁修改，不使用更新频繁的列作为主键，innodb的索引顺序和物理逻辑顺序一样，主键其实是索引，频繁更新，IO压力大
• 主键类别，不使用UUID，MD5，HASH，字符串作为主键，不能保证顺序增长和不重复。否则会引起数据库调整主键顺序，的IO压力

面试题：

• 常见索引列建议：5-7
• 如何选择索引列的顺序

4，字段设计规范

• 优先选择最小数据类型：INET_ATON, INET_NTOA，字符串与数字类型相互转换储存；非负整数优先使用无符号整型；varchar(n),n代表字符数，而不是字节数，varchar(255)=765字节（utf8,3倍）;varchar是根据实际数据长度储存至数据库，但是读取进内存时是按照定义的长度分配内存（提高效率）
• 避免使用TEXT，BLOB，ENUM类型
• 都设置为not null：null占用更多空间；比较和计算时，null需要特殊处理
• 使用TIMESTAMP和DATETIME储存时间：比字符串占用空间小
• 财务类使用decimal精准类型，计算不会丢精度，float，double会丢失

5，sql开发规范

• 使用预编译语句操作数据库，提高效率（一次解析多次使用）和避免sql注入
• 避免隐式转换，会导致索引失效；
• 合理利用索引，而不是增加；避免使用双百分号模糊查询；
• left join或not exists代替not in，not in 有几率使索引失效
• 分库分表，，，，，
• 禁止使用select 🌟，多余信息增加了CPU和IO压力和网络资源；无法利用覆盖索引；减少表结构变更带来的影响
• 禁止使用不含字段列表的insert语句，提高开放性，减少表结构变更对程序的影响
• 避免使用子查询，自查询可以是可读性提高；性能低下，结果集存在临时表中，无法使用索引；消耗CPU，IO，产生慢查询；有时可以优化为join操作
• 避免join过多的表，mysql最多61，建议不超过5个
• 减少同库交互次数，比如分页
• 禁止使用order by rand()，进行随机排序。性能差。原理，全部加载内存中，在随机选出；替换方法：程序生成随机值，然后依据随机值查询数据库
• 禁止在where从句中对列进行函数转换和计算，导致无法使用相关索引
• 拆分大sql为多个小sql：一个sql只能用一个CPU计算
  WX20200409-124744@2x.png

6，sql操作规范

• 操作超过百万行数据库，写操作分批进行，避免引起主从延时；避免大批量写日志的压力；避免产生大事务操作，锁定数据
• 程序的账号禁止赋予super权限，权限最小原则

二，常用数据库设计