MySQL数据库优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

第1章 硬件层面优化

1.1 数据库物理机采购(*****

  • CPU(运算):64CPU,一台机器2-16CPU,至少2-4颗,L2越大越好。

  • 内存:96G-256G(百度),3-4个实例。32-64G,跑1-2个实例(新浪)。

  • disk(磁盘IO):机械盘:选SAS,数量越多越好。

 

性能:SSD(高并发) > SAS(普通业务线上) >SATA(线下)

SSD:使用SSD或者PCIe SSD设备,可提升上千倍的IOPS效率。

随机IOSAS单盘能力300IOPS SSD随机IO:单盘能力可达35000IOPS Flashcache HBA

 

raid磁盘阵列: 4快盘:RAID0>RAID1(推荐)>RAID5(少用)>RAID1

主库选择raid10,从库可选raid5/raid0/raid10,从库配置等于或大于主库

网卡:使用多块网卡bond,以及buffertcp优化

千兆网卡及千兆、万兆交换机

提示:

数据库属于IO密集型服务,硬件尽量不要使用虚拟化。

Slave硬件要等于或大于Master的性能

 

 

1.2 企业案例:

百度:某部门IBM服务器为48CPU,内存96GB,一台服务器跑3~4个实例:

sina:服务器是DELL R510居多,CPUE521048GB内存,硬盘12*300G SAS,做RAID10

 

1.3 服务器硬件配置调整

(1)服务器BIOS调整 buffer和缓存调整大点。)

  

提升CPU效率参考设置:

a.打开Perfirmance Per Watt OptimeizedDAPC)模式,发挥CPU最大性能,数据库通常需要高运算量

b.打开CIEC States等选项,目的也是为了提升CPU效率

c. MemoryFrequency(内存频率)选择MaximumPerformance(最佳性能)

d.内存设置菜单中,启动Node Interleaving,避免NUMA问题

(2)阵列卡调整:

a.购置阵列卡同时配备CACHEBBU模块(机械盘)

b.设置阵列写策略为WEB,甚至OFRCE WB (对数据安全要求高)(wbraid卡的写策略:会写(write back))

c.严禁使用WT策略,并且关闭阵列预读策略

 

第2章 操作系统层面优化

2.1 操作系统及MySQL实例选择

1.一定要选择x86_64系统,推荐使用CentOS6.8 linux,关闭NUMA特性

2.将操作系统和数据分开,不仅仅是逻辑上,还包括物理上

3.避免使用Swap交换分区

4.避免使用软件磁盘阵列

5.避免使用LVM逻辑卷

6.删除服务器上未使用的安装包和守护进程

 

2.2 文件系统层优化(***

2.2.1 调整磁盘Cache mode

启用WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式

命令:sdparm -s WCE=1,RCD=0 -S /dev/sdb

2.2.2 采用Linux I/O scheduler算法deadline

deadline调度参数

对于Centos Linux建议 read_expire = 1/2 write_expire

echo 500 >/sys/block/sdb/queue/iosched/read_expire

echo 1000 >/sys/block/sdb/queue/iosched/write_expire

Linux I/O调度方法 Linux deadline io 调度算法。

 

2.2.3 采用xfs文件系统

  业务量不是很大也可采用ext4,业务量很大,推荐使用xfs:调整XFS文件系统日志和缓冲变量

XFS高性能设置。

2.2.4 mount挂载文件系统

  增加:async,noatime,nodiratime,nobarrier

noatime

访问文件时不更新inode的时间戳,高并发环境下,推荐显示应用该选项,可以提高系统I/O性能

async

写入时数据会先写到内存缓冲区,只到硬盘有空档才会写入磁盘,这样可以提升写入效率!风险为若服务器宕机或不正常,会损失缓冲区中未写入磁盘的数据 

解决办法:服务器主板电池或加UPS不间断电源

nodiratime

不更新系统上的directory inode时间戳,高并发环境,推荐显示该应用,可以提高系统I/O性能

nobarrier

不使用raid卡上电池

2.2.5 Linux 内核参数优化

1.vmswappiness设置为0-10

2.vmdirty_background_ratio设置为5-10,将vmdirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待


2.3 优化TCP协议栈

 

#减少TIME_WAIT,提高TCP效率

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

#减少处于FIN-WAIT-2连接状态的时间,使系统可以处理更多的连接

net.ipv4.tcp_fin_timeout=2

#减少TCP KeepAlived连接侦测的时间,使系统可以处理更多的连接。

net.ipv4.tcp_keepalived_time=600

#提高系统支持的最大SYN半连接数(默认1024

net.ipv4.tcp_max_syn_backlog = 16384

#减少系统SYN连接重试次数(默认5)

net,ipv4.tcp_synack_retries = 1

net.ipv4.tcp_sync_retries = 1

#在内核放弃建立的连接之前发送SYN包的数量

net.ipv4.ip_local_prot_range = 450065535

#允许系统打开的端口范围


2.4 网络优化

#优化系统套接字缓冲区

 

#Increase TCP max buffer size

net.core.rmem_max=16777216 #最大socketbuffer

net.core.wmem_max=16777216 #最大socketbuffer

net.core.wmem_default = 8388608 #该文件指定了接收套接字缓冲区大小的缺省值(以字节为单位)

net.core.rmem_default = 8388608

#优化TCP接收/发送缓冲区

 

# Increase Linux autotuning TCPbuffer limits

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000927000000

#优化网络设备接收队列

 

net.core.netdev_max_backlog=3000

2.5 其他优化

 

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000


 


第3章 MySQL数据库层面优化(*****

3.1 my.cnf参数优化

此优化主要针对innodb引擎

如果采用MyISAM引擎,需要key_buffer_size加大。

       key_buffer_size = 256M  

       #指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器来        说,该参数可设置为256MB或384MB。


强烈推荐采用innodb引擎,default-storage-engine=Innodb

调整innodb_buffer_pool_size大小,考虑设置为物理内存的50%-60%左右。

        innodb_buffer_pool_size = 64M  

         #InnoDB使用一个缓冲池来保存索引和原始数据,设置越大,在存取表里面数据时所需要的磁盘I/O越少。强烈建议不要武断地将InnoDB的Buffer Pool值配置为物理内存的50%~80%,应根据具体环境而定。


根据实际需要设置inno_flush_log_at_trx_commitsync_binlog的值。如果要需要数据不能丢失,那么两个都设为1.如果允许丢失大一点数据,则可分别设为20,在slave上可设为0

                           wKiom1lwkK3weO9pAADvpneHMYM028.jpg

设置innodb_file_per_table = 1,使用独立表空间

设置innodb_data_file_path = ibdata1:1G:autoextend,不要使用默认的10%

设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可满足90%以上的场景;

不要将innodb_log_file_size参数设置太大,这样可以更快同时又更多的磁盘空间,丢掉多的日志通常是好的,在数据库崩溃后可以降低恢复数据库的事件

设置long_query_time = 1记录那些执行较慢的SQL,用于后续的分析排查;

根据业务实际需要,适当调整max_connection(最大连接数)max_connection_error(最大错误数,建议设置为10万以上,而open_files_limitinnodb_open_filestable_open_cachetable_definition_cache这几个参数则可设为约10倍于max_connection的大小;)不要设置太大,会将数据库撑爆


建议关闭query cache功能或降低设置不要超过512M


query_cache_size = 64M 

#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用得非常频繁。另外如果改值较小反而会影响效率,那么可以考虑不用查询缓冲。对于Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。

tmp_table_size = 64M  

#设置内存临时表最大值。如果超过该值,则会将临时表写入磁盘,其范围1KB到4GB。

max_heap_table_size = 64M 

 #独立的内存表所允许的最大容量。

table_cache = 614 

#给经常访问的表分配的内存,物理内存越大,设置就越大。调大这个值,一般情况下可以降低磁盘IO,但相应的会占用更多的内存,这里设置为614。



3.2 关于库表的设计规范

1.推荐utf-8字符集,虽然有人说谈没有latin1

2.固定字符串的列尽可能多用定长char,少用varchar

   存储可变长度的字符串使用VARCHAR而不是CAHR---节省空间,因为固定长度的CHAR,而VARCHAR长度不固定(UTF8不愁此影响)

3.所有的InnoDB表都设计一个无业务的用途的自增列做主键

4.字段长度满足需求前提下,尽可能选择长度小的

5.字段属性尽量都加NOT NULL约束(空的字段不能走索引,查询速度慢)

   对于某些文本字段,例如省份或者性别我们可以将他们定义为ENUM类型

6.尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT*的时候读性能太差。

7.读取数据时,只选取所需要的列,不要每次都SELECT * 避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT*的时候读性能太差

8.对一个VARCHARN)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引。

9.多用符合索引,少用多个独立索引,尤其是一些基础(Cardinality)太小(如果说:该列的唯一值总数少于255)的列就不要创建独立索引了。


3.3 SQL语句的优化

3.3.1 索引优化

1)白名单机制一百度,项目开发,DBA参与,减少上线后的慢SQL数据

抓出慢SQL,配置my.cnf

long_query_time = 2

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

log_queries_not_using_indexs

按天轮询:slow-log.log

2)慢查询的日志分析工具——mysqlslapt-query-digest(推荐)

 

pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter

3)每天晚上0点定时分析慢查询,发到核心开发,DBA分析,及高级运维,CTO的邮箱里

DBA分析给出优化建议-->核心开发确认更新-->DBA线上操作处理


4)定期使用pt-duplicate-key-checker检查并删除重复的索引

定期使用pt-index-usage工具检查并删除使用频率很低的索引


5)使用pt-online-schema-change来完成大表的ONLINE DDL需求

6)有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX

7)使用explainset profile优化SQL语句

 

网站打开慢之慢查询

3.3.2 大的复杂的SQL语句拆分成多个小的SQL语句

子查询,JOIN连表查询,某个表4000万条记录

3.3.3 数据库是存储数据的地方,但不是计算数据的地方

对数据计算,应用类处理,都要拿到前端应用解决。禁止在数据库上处理

3.3.4 搜索功能,like %oldboy% 一般不要用MySQL数据库

使用连接(JOIN)来代替子查询(Sub_Queries

避免在整个表上使用cout(*),它可能锁住整张表

多表联接查询时,关联字段类型尽量一致,并且都要有索引。

WHERE子句中使用UNION代替子查询

多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一样是全表数据量小的)作为驱动表

 

  • 爬虫获取数据的过程



wKioL1lwkUazxQsvAAB845m7ck4086.jpg

wKiom1lwkUbjQ8ESAAC0cehvAUQ790.jpg

wKioL1lwkUeyIdqdAADAiA54w3I589.jpg

 

第4章 网站集成架构优化(*****

网站集群架构上的优化

1.服务器上跑多实例,2-4个(具体需要看服务器的硬件信息)

2.主从复制一主五从,采用mixed模式(混合或行模式),尽量不要跨机房同步(进程远程读本地写),(数据要一致,拉光纤,没有网络延迟)

3.定期使用pt-table-checksumpt-table-sync来检查并修复mysql主从复制的数据差异(重构)

4.业务拆分:搜索功能,like '%oldboy% ' 一般不要用MySQL数据库

5.业务拆分:某些业务应用使用nosql持久化存储,例如:memcachedredisttserver

 例如粉丝关注,好友关系等

6.数据库前端必须要加cache,例如:memcached,用户登录,商品查询

7.动态的数据库静态化,整个文件静态化,页面片段静态化

8.数据库集群与读写分离。一主多从,通过程序或dbproxy进行集群读写分离

9.单表超过800万,拆库拆表。人工拆表拆库(登录、商品、订单)

10.百度、阿里国内前三公司,会选择从库进行备份,对数据库进行分库分表

 

 

第5章 MySQL数据库管理流程(*****

任何一次人为数据库记录的更新,都要走一个流程:

a.人的流程:开发-->核心开发-->运维或DBA

b.测试流程:内网测试-->IDC测试-->线上执行

c.客户端管理,phpmyadmin

 

第6章 MySQL数据库安全优化(*****

6.1 MySQL基础安全

 

1.启动程序700,属主和用户组为MySQL。

2.MySQL超级用户root设置密码。

3.如果要求严格可以删除root用户,创建其他管理用户,例如admin。

4.登录时尽量不要在命令行暴露密码,备份脚本中如果有密码,给设置700,属主和密码组为mysqlroot。

5.删除默认存在的test库。

6.初始删除无用的用户,只保留。

| root | 127.0.0.1 |

| root | localhost |

7.不要一个用户管理所有的库,尽量专库专用户(少量库)

8.清理mysql操作日志文件~/.mysql_history(权限600,可以不删)

9.禁止开发获得到web连接的密码,禁止开发连接操作生产对外的库

10.phpmyadmin安全

11.服务器禁止设置外网IP

12.SQL注入(WEBphp.iniweb开发插件监控,waf控制


本文转自写个博客骗钱博客51CTO博客,原文链接http://blog.51cto.com/dadonggg/1949500如需转载请自行联系原作者

菜鸟东哥
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
27天前
|
消息中间件 缓存 监控
优化微服务架构中的数据库访问:策略与最佳实践
在微服务架构中,数据库访问的效率直接影响到系统的性能和可扩展性。本文探讨了优化微服务架构中数据库访问的策略与最佳实践,包括数据分片、缓存策略、异步处理和服务间通信优化。通过具体的技术方案和实例分析,提供了一系列实用的建议,以帮助开发团队提升微服务系统的响应速度和稳定性。
|
1月前
|
SQL 缓存 关系型数据库
MySQL高级篇——关联查询和子查询优化
左外连接:优先右表创建索引,连接字段类型要一致、内连接:驱动表由数据量和索引决定、 join语句原理、子查询优化:拆开查询或优化成连接查询
MySQL高级篇——关联查询和子查询优化
|
1月前
|
算法 关系型数据库 MySQL
MySQL高级篇——排序、分组、分页优化
排序优化建议、案例验证、范围查询时索引字段选择、filesort调优、双路排序和单路排序、分组优化、带排序的深分页优化
MySQL高级篇——排序、分组、分页优化
|
14天前
|
存储 关系型数据库 MySQL
MySQL索引失效及避免策略:优化查询性能的关键
MySQL索引失效及避免策略:优化查询性能的关键
96 3
|
14天前
|
缓存 关系型数据库 MySQL
MySQL数据库优化:提升性能和扩展性的关键技巧
MySQL数据库优化:提升性能和扩展性的关键技巧
41 2
|
2天前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
2天前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化策略
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生不利影响
|
27天前
|
消息中间件 缓存 监控
优化微服务架构中的数据库访问:策略与实践
随着微服务架构的普及,如何高效管理和优化数据库访问成为了关键挑战。本文探讨了在微服务环境中优化数据库访问的策略,包括数据库分片、缓存机制、异步处理等技术手段。通过深入分析实际案例和最佳实践,本文旨在为开发者提供实际可行的解决方案,以提升系统性能和可扩展性。
|
25天前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
27 2
|
1月前
|
关系型数据库 MySQL 测试技术
《性能测试》读书笔记_数据库优化
《性能测试》读书笔记_数据库优化
25 7