构建高性能MySQL体系

数据库架构
- 单实例无法解决空间和性能需求时考虑拆分
- 垂直拆分
- 水平拆分
- 引入缓存系统

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
- 推荐文章猛击这里
系统资源
- open_file_limit=65535
- table_open_cache
- back_log 大于max_connections
- thread_stack=192
并发控制
- 使用thread_pool
- thread_cache_size
索引优化
- 利用最小的索引成本找到最需要的行记录
- 原则
  - 最左前缀原则(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索引结构原理性能分析与优化.
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的效率高)

秒客网

构建高性能MySQL体系

构建高性能MySQL体系

相关文章