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

本篇文章给大家主要讲的是关于高并发网站集群场景下如何优化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正式

以上关于高并发网站集群场景下如何优化MySQL数据库详细内容，对大家有帮助吗？如果想要了解更多相关，可以继续关注我们的数据库板块。