构建高性能MySQL体系

时间:2021-09-08 00:30:32

构建高性能MySQL体系

  1. 数据库架构
    • 单实例无法解决空间和性能需求时考虑拆分
    • 垂直拆分
    • 水平拆分
    • 引入缓存系统
  1. IO相关参数
    • innodb_flush_method=O_DIRECT
    • innodb_read_io_threads=16
    • innodb_write_io_threads=16
    • innodb_io_capacity=3000 PCIE卡建议更高
    • innodb_flush_neighbors=0 innoDB存储引擎在刷新一个脏页时,会检测该页所在去的所有页,如果是脏页,那么一起刷新.这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作.对于传统机械硬盘建议使用,而对固态可以关闭.
    • innodb_flush_log_at_trx_commit redo刷盘策略
    • sync_binlog binlog的刷盘策略
    • innodb_log_buffer_size 建议8-16M,有高TPS的可以提高到32M
    • 推荐文章猛击这里
  2. 系统资源
    • open_file_limit=65535
    • table_open_cache
    • back_log 大于max_connections
    • thread_stack=192
  3. 并发控制
    • 使用thread_pool
    • thread_cache_size
  4. 索引优化
    • 利用最小的索引成本找到最需要的行记录
    • 原则
      • 最左前缀原则(mysql会一致直向右匹配直到遇到范围查询(>,<,between, like)就停止匹配,比如: a=1 and b=2 and c<3 and d=4如果建立(a,b,c,d)顺序索引,d是用不到索引的.如果建立(a,b,d,c)的索引则都可以用到.abd的顺序可以调整.)
      • 避免单列索引,尽量使用复合索引,精确确定where条件对应的行.
      • 避免重复索引(id_abc多列索引,相当于创建了(a)单列索引,(a,b)组合索引以及(a,b,c)组合索引).
      • 不在索引列使用函数.如: max(id) >10, id+1>3等
      • 尽量选择区分度高的列作为前缀索引.区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录越少.
    • 推荐文章
      • MySQl索引原理及慢查询优化
      • MySQL索引实践
      • 由浅入深探究MySQL索引结构原理性能分析与优化.
  5. SQL开发优化
    • 不是用存储过程,触发器,自定义函数.
    • 不是用全文索引
    • 不是用分区表
    • 不是用多表查询,禁用JOIN
    • 不使用*,SELECT使用具体列名
    • IN的元素个数300-500
    • 避免使用大事物,使用短小的事物.减少锁等待和竞争.
    • 禁止使用%前缀模糊查询where like '%XXX'
    • 禁止使用子查询,遇到使用子查询的情况,尽量使用join代替.
    • 遇到分页查询,使用延迟关联解决,分页如果有大offset,可以先取id,然后用主键id关联表会提高效率.
    • 禁止并发执行count(*), 并发导致CPU飙高
    • 禁止使用order by rand()
    • 不使用负向查询,如not in/like,使用in反向代替
    • 不要一次更新大量数据(大于30000条), 批量更新和删除
    • sql中使用到OR的改写为用IN()(or的效率没有in的效率高)