构建高性能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的效率高)
转载于:https://www.cnblogs.com/zane-zong/articles/7067510.html