mysql群集优化_高并发网站集群场景下MySQL数据库优化

1、硬件层面优化

1.1数据库物理机

a.CPU:64位，服务器2-16个CPU,2-4颗，L1,L2越大越好；

b.mem(内存)：48G-96G-128G-256G(48g 2-3个实例；96g 3-4个实例 )；

c.disk(磁盘IO)数据库是IO密集应用

机械盘：SAS(不要选择SATA)，300G*12块，磁盘数量越多IO越高，SAS 15K转的硬盘。

SSD:固态硬盘

测试对比：SAS单盘随机IO,300IOPS,SSD单盘随机IO达到上万。

d.raid阵列：选硬件RAID(0>10>5>1)，选10

e．网卡至少千兆(bond),万兆交换机

f.数据库服务器尽量不用虚拟化

g.SLAVE服务器配置最好是大于等于Master

案例：

百度：IBM服务器，内存96-128G，CPU48核，3-4个实例

SINA: DELL R510,内存48G，磁盘300*12块，raid10

多实例：就是一台服务器多个数据库，比喻一个房子多个卧室。

1.2硬件调整：

Bios调整提高CPU性能

1)打开DAPC模式，发挥CPU性能。

2)启动Node Interleaving,避免NUMA问题(NUMA=0)。

3)关键C1E和State等选项。

陈列卡：

1)配置CACHE和BBU模块(机械盘)

2)写策略调为(wb)

3)不用wt策略,关闭陈列预读策略

2、软件层面优化

2.1操作系统层面优化

1)选择x86_64位系统

2)将系统盘和数据盘分开

3)尽量避免是用swap

4)避免使用操作系统软raid

5)避免使用LVM

6)专库专用(不要跑LNMP，TOMCAT)

7)调整Cache mode

启动wce=1(Write Cache Enable)  RCD=0(Read Cache Disable)

8)调度算法默认cfq,noop,deadline。针对deadline可以调参(内核参数)

9)Centos6.8默认ext4可以作为数据为的文件系统，访问量大的话，XFS就更好。Centos7默认也选了XFS，调整XFS日志，缓冲参数。

10)mount参数很重要，async,noatime,nodirname,nobarrier等。

2.2文件系统层优化

2.3内核层面优化

1)vm.swappiness设置0，或者0-5,让数据库尽量不使用swap.

2)Vm.dirty_background_ration设置5-10,vm.dirty_ration设置前面的2倍。持续将系统脏数据刷到磁盘。

3)net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_reuse=1, net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600减少time_wait

4)内核优化，参考老男孩博客；

3、MySQL层面优化

3.1 my.conf参数的优化

1)如果采用myisam引擎，key_buffer_szie加大。尽量采用innnodb.

2)推荐使用innodb,5.5.5以后默认就是innodb引擎

3)innodb_buffer_pool_szie,调整为内存的50%，单实例。

4)innodb_flush_log_at_trx_commit,sync_binlog,设置为1,数据可以丢，设置0，从库设置为0。

5)使用独立表空间。innodb_file_per_table=1

6)innodb_log_file_size=256M

7)log_query_time=1 ,超过1秒的语句记录LOG。

8)一些session参数不要设置过大，一个连接就会占用参数设置的大小。Sort_buffer_size,join_buffer_size这类参数都是session级别参数。

9)查询缓存参数要设置小一些：query_cache_size=64M,要想缓存，前端加mc，redis。

3.2库和表的设计优化

1)字符集UTF-8

2)固定字符串的内容，可以选择char

3)数据库都要给一个自增的主键，没有什么业务用途

4)字段长度，在满足需求前提下，用最短的。Varchar(16)

5)省份，性别，这类内容字段可以设置ENUM类型，mysql系统表(char ,ENUM)

6)尽可能不用text/blob,如果使用的话，放到子表里。

7)针对字段索引，尽量采用字段的前N个字符索引，不要整个字段索引。

8)多用联合索引，前缀特性，少用独立索引，性别列不要建立独立索引了。

3.3 SQL语句的优化

A、索引优化

1)抓出来慢查询

百度：白名单的方法，设计程序时候参与设计，程序上线连接数据库，有个控制查库的东西，请示放我库里，才能查询，数据库没有或者减少慢查询。

给常给开发做培训，DB水平更高。

现在网站慢了，show full processlist;抓慢查询，连续执行两下。间隔1-2秒，如果还有，怀疑他是慢查询。

日常：把慢查询语句记录到log。

My.cnf

Long_query_time=2

Log_queryies_not_using_indexes

Log-slow-queries=/data/3306/slow.log

每天生成slow.log,按天切割slow.log,切割以后用分析软件分析(mysqlsla,-pt-query-digest)

Mysqldumpslow,myprofi。优化的语句不一定是单条占用时间长的，频率高单条不长，但是总时间很长的，这些可能也是优化的重点。

运维来讲，慢查询SQL发给开发。

Explain测试语句是否走索引。Set profile深度查看语句执行情况。

检查删除重复的索引，pt-duplicate-key-checker,效率很低的索引检查删除，pt-index-usage

2)尽量不用子查询，用join替代

数据库是存放数据的地方，不是计算数据的地方，计算放在前面Web。搜索功能，like “%daf%”,不用数据库搜索。

3)语句中尽量去掉in or <>

4、网站集群架构上优化数据库

1)服务器跑多实例2-4个

2)主从复制最多9个，1主5从，采用mixed,不要跨机房复制(远程写，本地读)

3)业务拆分：搜索功能，like “%daf%”,不用数据库搜索。

搜索软件：Sphinx,Xapian,Solr

4)粉丝关注，好关系，统计这类应用比较简单，不用数据，放到redis。

5)数据库前端加缓存

6)动态内转静态化(数据库的数据，转成html文件，放到存储上)使用CDN。

7)数据库采用读写分离，MyCat,atlas,cobar,amoeba,MySQL-proxy

8)单表超过800万，拆库拆表。自动扩容，自动收缩。

9)选择从库备份，锁表，备份时间很长，影响数据访问。

10)备份时采用分表分库。

5、流程、制度、安全:50%的故障都是人为造成的。

1)操作流程：开发-核心开发—运维或DBA

2)测试流程：办公室测试-IDC测试-IDC正式