如何构建高性能MySQL系统

1、 如何构建高性能MySQL系统DBAplus社群 微信号 dbaplus功能介绍 围绕数据库、大数据、PaaS云，顶级大咖、技术干货，运营几个月受众过十万！成为运维圈最专注围绕“数据”的学习交流和专业社群！欢迎投稿，加入探讨。思维导图构建高性能MySQL系统涵盖从单机、硬件、OS、文件系统、内存到MySQL 本身的配置，以及schema 设计、索引设计 ，再到数据库架构上的水平和垂直拓展。三、内容展示硬件（1）CPUCPU亲和性：确保每个io都被其发起的CPU处理echo 2 /sys/block/queue/rq_affinity选择最大性能模式，避免节能模式导致性能不足关闭NUMA，降低s

2、wap概率numactl -interleave=all（2）RAID卡选择FORCE WB读写策略选择合适的充放电策略高IO，推荐RAID10空间需求大则RAID5操作系统（1）IO调度策略SSD/PCIE SSD推荐noop，其它推荐deadlineecho noop /sys/block/queue/scheduler（2）禁用块设备轮转模式echo 0 /sys/block/queue/rotational（3）内存vm.swappiness=0内存最大性能模式文件系统确保4K对，如果使用全盘一个分区，例如mkfs.ext4 /dev/dfa也可以使用xfs 构建文件系统。禁止atim

3、e、diratimemount -o noatime -o nodiratime开启trimmount -o discard关闭barriermount -o barrier=0/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0MySQL（1）配置优化IO相关参数innodb_flush_method = O_DIRECTinnodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 3000（PCIE卡建议更高）innodb_flu

4、sh_neighbors=0InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭innodb_flush_log_at_trx_commitredo 的刷盘策略sync_binlogbinlog 的刷盘策略innodb_log_buffer_size建议8-16M，有高TPS（比如大于6k）的可以提高到32M，系统tps越高设置可以设置的越大推荐文章 /conanwang/p/5849437.html内存分配策略：jemalloc

5、是BSD的提供的内存分配管理tcmalloc是google的内存分配管理模块ptmalloc是glibc的内存分配管理malloc-lib= /usr/lib64/libjemalloc.so.1系统资源：malloc-lib= /usr/lib64/libjemalloc.so.1back_log：大于max_connectionsthread_stack=192并发控制：使用thread_poolthread_cache_size（2）schema优化索引优化目标：利用最小的索引成本找到最需要的行记录。原则：最左前缀原则：MySQL会一直向右匹配直到遇到范围查询(、3 and d=4 如果

6、建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整避免重复索引：idx_abc多列索引,相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引。不在索引列使用函数 如 max(id) 10 ,id+13 等尽量选择区分度高的列作为前缀索引：区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少推荐文章：MySQL索引原理及慢查询优化/mysql-index.htmlMySQL索引实践/mysql/2015/08/26/MySQL%E7

7、%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/由浅入深探究 MySQL索引结构原理、性能分析与优化/87107/SQL开发优化不使用存储过程、触发器，自定义函数不使用全文索引不使用分区表针对OTLP业务尽量避免使用多表join和子查询不使用*,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的效率高）