欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库优化方法详解

程序员文章站 2022-07-09 22:12:24
MySQL数据库优化方法详解 一、硬件的优化 1.1 cpu优化(64位cpu,建议多核绑定不同的服务) 1.2 内存(按照企业发展,合理调控内存) 1.3 硬盘 (数量越...

MySQL数据库优化方法详解

一、硬件的优化

1.1 cpu优化(64位cpu,建议多核绑定不同的服务)

1.2 内存(按照企业发展,合理调控内存)

1.3 硬盘 (数量越多越好,性能:ssd(核心高并发业务) > sas(线上普通业务)> sata(线下))

Raid 4 4块盘 性能: RAID0 > RAID10 > RAID5 > RAID1

1.4 网卡 (网卡的ffer,通过参数可以内核调优,多块网卡bond)

二、软件的优化

2.1 操作系统:x86_64位系统

2.2 建议编译安装,编译优化(一般安装有三种方式:yum安装,二进制安装,编译安装)

三、my.cnf参数的优化

my.cnf参数优化的幅度很小,大部分在架构和sql语句的调优

3.1 Innodb_buffer_pool_size = 2048M #数据库的数据会缓存在这个buffer_pool里面,一般设置值为物理内存的1/3,不超过50%

3.2 innodb_flush_log_at_trx_commit = 2 #修改参数提交不需要刷新binlog

3.3 sort_buffer_size = 2M #这些都是线程buffer,这里的值不能给大

3.4 join_buffer_size = 2M

3.5 open_file_limit = 10240 这里的值可以给大

3.6 query_cache_size = 64M

3.7 query_cache_limit = 4M 这里的值不能给大,建议前端做memcached缓存,不要在数据库里做查询缓存。

3.8 query_cache_min_res_unit = 2k

3.9 tmp_table_size = 256M #可以稍微给大点,但是不要给太大,会占用磁盘空间

3.10 tmp_heap_table_size= 256M

3.11 long_query_time = 2 超过两秒的查询记录下来

3.12 log_slow_queries = /data/mysql/slowlog.log

3.13 log_error = /data/mysql/error.log 记录错误日志

3.13 expire_logs_days = 7 自动清理binlog,不要手动清理

3.14 key_buffer_size = 2048M 索引的缓存,主要用于myisam引擎,索引的数据存储在这里,如果是myisam引擎,建议值尽量大些

3.15 skip-name-resolve 这个需要在配置文件里面添加,不加容易导致用户权限问题,show processlist会出现大量login等待

3.16 innodb_data_file_path = iddata1:1024M:autoextend

小结:

1、生产参数是一般情况下的参数,通过命令监控慢慢调整:show global status\G

2、mysql性能调优工具mysqlreport

Sql语句的优化

2.1 索引优化

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

抓出慢SQL,配置my.cnf

Long_query_time=2

Log_slow_queries=/data/mysql/slowlog.log

按天轮询:slowlog.log

2) 慢查询日志分析工具

Mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfilter比较

3)每天晚上0点定时分析慢查询

2.2 大量复杂的sql语句拆分成多个小的sql语句

2.3 数据库时存储数据的地方,不是计算数据的地方,对数据计算的应用类处理,都要拿到前端应用解决。

2.4 索引的功能,一般不要用mysql数据库,用单独的工具,像like %餐巾纸%,这样的语句没有办法做索引

架构的优化

3.1 业务拆分:搜索功能,像like %餐巾纸%,一般不要用mysql数据库

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

3.3 某些业务应用使用nosql持久化缓存,例如:redis,memcachedb,ttsserver,例如:粉丝,好友关系等

3.4 动态的数据静态化,整个文件静态化,页面片段静态化。比如:用户发布一个商品,商品生成html,推到前端(大网站必须要做的)

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

3.6 单表超过2000万,拆库拆表。人工拆表拆库(登录、商品、订单)

流程、制度、安全的优化

4.1 任何一次认为数据记录的更新都要走一个流程:开发--核心开发--运维或DBA

4.2 测试流程:内网测试--IDC测试--DBA测试--线上执行(比如删除一个表的记录)

4.3 客户端的管理,phpmyadmin