构建高性能MySQL体系思维导图
树图思维导图提供 构建高性能MySQL体系 在线思维导图免费制作，点击“编辑”按钮，可对 构建高性能MySQL体系 进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：4b17c004c96d09d1767b91d937e81dc0
思维导图大纲
构 建 高 性 能 MySQL 体 系思维导图模板大纲
硬件
CPU
cpu 亲和性
确保每个io都被其发起的CPU处理
echo 2 > /sys/block/<block device>/queue/rq_affinity
选择最大性能模式，避免节能模式导致性能不足。
关闭NUMA，降低swap概率
numactl --interleave=all
RAID卡
选择FORCE WB，选择合适的充放电策略
高IO ，推荐RAID10 ;空间需求大则RAID5
NVME SSD
PCIE SSD
操作系统
io 调度策略
SSD/PCIE SSD 推荐 noop 其他推荐deadline
echo noop > /sys/block/<block device>/queue/scheduler
禁⽤用块设备轮转模式
echo 0 > /sys/block/<block device>/queue/rotational
内存
vm.swappiness=0
文件系统
确保4K对⻬,如果使用全盘一个分区, 例如 mkfs.ext4 /dev/dfa 也可以使用xfs 构建文件系统
禁⽌止atime, diratime
mount -o noatime -o nodiratime
开启trim
mount -o discard
关闭barrier
mount -o barrier=0
/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0
made by 杨一
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存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。
innodb_flush_log_at_trx_commit
redo 的刷盘策略
sync_binlog
binlog 的刷盘策略
innodb_log_buffer_size
建议 8-16M，有高TPS(比如大于6k)的可以提高到32M,系统tps越高设置可以设置的越大。
推荐文章 http://www.cnblogs.com/conanwang/p/5849437.html
内存分配
策略
jemalloc 是BSD的提供的内存分配管理
tcmalloc 是google的内存分配管理模块
ptmalloc 是glibc的内存分配管理
malloc-lib= /usr/lib64/libjemalloc.so.1
系统资源
open_files_limit = 65535
table_open_cache
back_log
大于max_connections
thread_stack=192
并发控制
使用thread_pool
thread_cache_size
schema 优化
索引优化
目标
利用最小的索引成本找到最需要的行记录
原则
最左前缀原则
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配， 比如a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d)顺序的索引， d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
避免单列索引，尽量使用复合索引，精确确定where条件对应的行
避免重复索引
idx_abc多列索引,相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引。
不在索引列使用函数 如 max(id)> 10 ,id+1>3 等
尽量选择区分度高的列作为前缀索引
区分度的公式是count(distinct col)/count(*)， 表示字段不重复的比例，比例越大我们扫描的记录数越少
推荐文章
MySQL索引原理及慢查询优化
MySQL索引实践
由浅入深探究 MySQL索引结构原理、性能分析与优化
SQL开发优化
不使用存储过程,触发器，自定义函数
不使用全文索引
不使用分区表
不使用多表查询, 禁用JOIN
针对OLTP 业务
不使用*,SELECT使用具体的列名
在发生列的增/删时, 发生列名修改时, 最大限度避免程序逻辑中没有修改导致的BUG
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的效率高)
数据库架构
单实例无法解决空间或性能需求时考虑拆分
垂直拆分
水平拆分
引入缓存系统
相关思维导图模板
树图思维导图提供 海洋之星产品体系 在线思维导图免费制作，点击“编辑”按钮，可对 海洋之星产品体系 进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：eb01bc969dc4effe6a3ed46704da4689
树图思维导图提供 金融机构合规文化建设 在线思维导图免费制作，点击“编辑”按钮，可对 金融机构合规文化建设 进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：cbcfa0c1cf1c8ef09c1ce76f21486b9f
沪公网安备 31011502019485号
上海工商