MySQL之设计优化军规

核心军规

• 每个表都建立自增id，弊端此处略千百字……
• 尽量不在数据库做运算，运算字段无法使用索引
• 控制单表数据量，单表数据量控制在500W以内，当然未达到量但是体积太大另说
• 保持表身段苗条
• 平衡范式和冗余
• 拒绝大SQL，复杂事务，大批量任务

字段类军规

• 用好数值字段，尽量简化字段位数，别身份证号码的字段整个varchar(255)出来
• 把字符转化为数字，重复性字符串尽量使用数字代替，例如日志表设计
• 优先使用Enum或Set
• 避免使用Null字段，应设置不为空，默认空字符串
  1. NULL和空值的区别
  2. 占用空间区别，NULL列需要行中的额外空间来记录它们的值是否为NULL，空值长度为0
  3. 插入/查询方式区别，部分框架不支持
  4. COUNT 和 IFNULL函数，使用 COUNT(字段) 统计会过滤掉 NULL 值，但是不会过滤掉空值
  5. 索引字段说明，在有NULL值得字段上使用常用的索引，如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下，说明了 索引列必须为NOT NULL
• 少用并拆封Text/Blob
• 不在数据库中存图片，这一点不用多说

索引类军规

• 谨慎合理添加索引
• 字符字段必须建立前缀索引?
• 不在索引列做运算
• 自增列或全局ID做InnoDB主键
• 尽量不用外键，这里不推荐使用外键，使用逻辑关联

SQL类军规

• SQL尽可能简单
• 保持事务连接短小
• 尽量不用Select *，例如业务只需要索引列内容，只查索引列，可以避免回表查询
• 改写Or为IN()
• 改写Or为Union
• 避免负向查询和%前缀模糊查询（name like ‘%明远%’ 这样子索引是使不上的）
• Count不要使用在可Null的字段上面
• 减少Count(*)
• Limit高效分页，SELECT * FROM message WHERE id > 9527 (or sub select) limit 10
• 分解链接，保证高并发
• Group By 去除排序
• 同数据类型的列值比较
• Load Data导入数据，比Insert快20倍，大批量写操作（非实时）可以归集更新
• 打散大批量更新，尽量凌晨操作

约定类军规

• 隔离线上线下
• 禁止未经DBA认证的子查询
• 永远不在程序段显式加锁
• 统一字符集为UTF-8，统一命名规范，（表字段中，比如昵称可以使用utf8mb4格式，方便后期支持表情，遇到过前期昵称使用utf8存储，后期支持表情，取出之后需要再转义一次，其中滋味自行体会）

建索引规则

• 唯一索引使用uni_[字段名]来命名。
• 非唯一索引使用idx_[字段名]来命名。
• 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)。