文章目录

  • 一、索引失效与优化
  • 二、优化终极奥义
  • 三、数据库其他优化原则


一、索引失效与优化
  • 复合索引尽量全匹配
  • 最佳左前缀法则（带头索引不能死，中间索引不能断）
  • 不要在索引上做任何操作（计算、函数、自动、手动类型转换），不然会导致索引失效而转向全表扫描
  • mysql存储引擎不能继续使用索引中范围条件（between、<、>、in等）右边的列
  • 尽量使用覆盖索引【只查询索引的列（索引列和查询列一致）】，减少select *
  • 索引字段上使用（!= 或者 <>）判断时，会导致索引失效而转向全表扫描
  • 索引字段上使用is null / is not null判断时，会导致索引失效而转向全表扫描。在8版本，并未失效，在5.6版本中失效
  • 索引字段使用like以通配符开头（’%字符串’）时，会导致索引失效而转向全表扫描
  • 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
  • 索引字段使用or时，会导致索引失效而转向全表扫描
二、优化终极奥义
  • 针对百万数量级，放弃在mysql中的join操作，推荐分别根据索引单表取数据，然后再程序里面做join，merge数据。
  • 尽量使用nosql，例如redis、memcached等来缓存热点数据，从而缓解mysql压力。【一般qbs达到3500~4500已经是mysql主机极限了】
三、数据库其他优化原则
  • 总体的优化原则
  • 不要在数据库做运算，运算务必移至业务层
  • 库命令简洁明确（长度不能超过30个字符）
  • 控制列数量（字段少而精，字段数建议在20以内）
  • 平衡范式与冗余（效率优先；往往牺牲范式）
  • 拒绝3B：
  • 拒绝大sql【big sql】
  • 拒绝大事务【big transaction】
  • 拒绝大批量【big batch】
  • 用好数值类型（用合适的字段类型节约空间）
  • 字符转化为数字（能转化的最好转化，同样节约空间，提高查询性能）
  • 字段类优化原则
  • 避免使用null字段（null字段很难查询优化、null字段的索引需要额外空间、null字段的复合索引无效）
  • 少用text类型（尽量使用varchar代替text字段）
  • 索引类优化原则
  • 合理使用索引（改善查询，减慢更新，索引一定不是越多越好）
  • 字符字段建前缀索引（例如：abckk,dfgkk,fdskk…只要前面3个）
  • 不在索引做列运算（例如：select * from t1 where id+1=10）
  • innodb主键推荐使用自增列（主键建立聚簇索引，主键不应该被修改，字符串不应该做主键）（理解Innodb的索引保存结构就知道了）；不用外键（由程序保证约束）
  • sql类优化原则
  • sql语句尽可能简单（一条sql只能在一个cpu运算，大语句拆小语句，减少锁时间，一条大sql可以堵死整个库）
  • 简单的事务（最好是不要有事务）
  • 避免使用trig/func(不用触发器，函数。客户端程序取而代之)
  • 不用select * (消耗cpu，io，内存，带宽，这种程序不具有扩展性)
  • or改写为in(在字段没有索引的情况下性能差别较大)
  • or改写为union（索引无效变有效）
  • 使用union all代替union
  • union有去重开销，例如分表操作，如果没有重复记录，则使用union all
explain select name from t3 where idc <=2 union select name from t3 where idc = 3
改成
explain select name from t3 where idc <=2 union all select name from t3 where idc = 3
  • 少用连接join
  • 超过3个join，一般移到业务代码里执行
  • 分页limit优化
  • 偏移量越大，执行越慢
select * from t1 limit 90000,20
改成
select * from t1 where id in(select id from t1 where id > 90000) limit 0,20;
  • 结构类优化原则
  • 表范式化原则
  • 范式化是指数据库设计的规范，目前范式化一般是指设计到弟三范式。也就是要求数据库中不存在非关键字段对任意候选关键字段的传递函数依赖，则符合第三范式。
  • 表范式化原则（表拆分，一般遵循第三范式）
  • 反范式化原则
  • 反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余，以达到优化查询效率的目的，反范式是一种以空间来换取时间的操作。
  • 适当的字段冗余，减少了表之间的join（三表join->单表查询）
  • 垂直拆分原则
  • 不常用的字段单独存放到一个表中
  • 大字段独立存放到一个表中
  • 经常一起使用的字段放到一起
  • 水平拆分
  • 表的水平拆分是为了解决单表数据量过大的问题
  • 尽管加了完美的索引，查询效率低，写入的效率也相应的降低
  • 通常对id进行hash运算，如果要拆分为5个表则使用mod（id,5）取出0-4个值