13518219792
本篇文章给大家主要讲的是关于高并发网站集群场景下如何优化MySQL数据库的内容，感兴趣的话就一起来看看这篇文章吧，相信看完高并发网站集群场景下如何优化MySQL数据库对大家多少有点参考价值吧。
目前累计服务客户近1000家，积累了丰富的产品开发及服务经验。以网站设计水平和技术实力，树立企业形象，为客户提供网站设计制作、成都网站建设、网站策划、网页设计、网络营销、VI设计、网站改版、漏洞修补等服务。成都创新互联始终以务实、诚信为根本，不断创新和提高建站品质，通过对领先技术的掌握、对创意设计的研究、对客户形象的视觉传递、对应用系统的结合，为客户提供更好的一站式互联网解决方案，携手广大客户，共同发展进步。
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数据库详细内容，对大家有帮助吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
