2022年12月8日 (四) 06:09 Evan

2022-12-08T06:09:06Z

新页面

=pre see also=
[[Mysql常见配置参数]]
=服务器优化=

== 优化服务器硬件==
===配置较大的内存===
足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。
===磁盘I/O相关===
配置高速磁盘，比如SSD。

合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。

配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。
==系统层相关优化==
===文件系统层优化===
在文件系统层，下面几个措施可明显提升IOPS性能：

1、使用deadline/noop这两种I/O调度器，千万别用cfq（它不适合跑DB类服务）；

2、使用xfs文件系统，千万别用ext3；ext4勉强可用，但业务量很大的话，则一定要用xfs；

3、文件系统mount参数中增加：noatime, nodiratime, nobarrier几个选项（nobarrier是xfs文件系统特有的）；

===其他内核参数优化===

针对关键内核参数设定合适的值，目的是为了减少swap的倾向，并且让内存和磁盘I/O不会出现大幅波动，导致瞬间波峰负载：

1、将vm.swappiness设置为5-10左右即可，建议为1 少用,甚至设置为0（RHEL 7以上则慎重设置为0，除非你允许OOM kill发生），以降低使用SWAP的机会；
swappiness的值的大小对如何使用swap分区是有着很大的联系的。swappiness=0的时候表示最大限度使用物理内存，然后才是 swap空间，swappiness＝100的时候表示积极的使用swap分区，并且把内存上的数据及时的搬运到swap空间里面。linux的基本默认设置为60，具体如下：

一般默认值都是60 我的kali也是
[root@tr ~]# cat /proc/sys/vm/swappiness
60

也就是说，你的内存在使用到100-60=40%的时候，就开始出现有交换分区的使用。大家知道，内存的速度会比磁盘快很多，这样子会加大系统io，同时造的成大量页的换进换出，严重影响系统的性能，所以我们在操作系统层面，要尽可能使用内存，对该参数进行调整

2、将vm.dirty_background_ratio设置为5-10，将vm.dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待（和MySQL中的innodb_max_dirty_pages_pct类似）；

3、将net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1，减少TIME_WAIT，提高TCP效率；

4、至于网传的read_ahead_kb、nr_requests这两个参数，我经过测试后，发现对读写混合为主的OLTP环境影响并不大（应该是对读敏感的场景更有效果），不过没准是我测试方法有问题，可自行斟酌是否调整；

=4.优化MySQL server=
==4.1MySQL内存管理和优化==
==4.2InnoDB log机制及优化==
==4.3调整跟并发相关的MySQL参数==
（1）max_connections
（2）back_log
（3）table_open_cache
（4）thread_cache_size
（5）innodb_lock_wait_timeout

==MySQL调优之innodb_buffer_pool_size大小设置==
<pre>
修改配置文件的调整方法，修改my.cnf配置：

innodb_buffer_pool_size = 2147483648 #设置2G

innodb_buffer_pool_size = 2G #设置2G

innodb_buffer_pool_size = 500M #设置500M

MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL，但是低版本，静态的，修改后，需要重启MySQL。
</pre>
[https://blog.csdn.net/sunny05296/article/details/78916775 MySQL调优之innodb_buffer_pool_size大小设置]

[https://www.cnblogs.com/innocenter/p/14948857.html Mysql优化之innodb_buffer_pool_size篇]

== 优化MySQL的参数==
<pre>

默认是这样的，安装目录有配置模版可以看一下 my-*.cnf my-huge.cnf ：用于高端产品服务器，包括1到2GB RAM,主要运行mysql
my-innodb-heavy-4G.ini ：用于只有innodb的安装，最多有4GB RAM，支持大的查询和低流量
my-large.cnf ：用于中等规模的产品服务器，包括大约512M RAM
my-medium.cnf ：用于低端产品服务器，包括很少内存（少于128M）
my-small.cnf ：用于最低设备的服务器，只有一点内存（少于512M）

设置为内存的50% 差不多了只跑一个mysql的机器
innodb_buffer_pool_size = 2048M
# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了

MariaDB [(none)]> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |

innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1，将在每次提交事务后将日志写入磁盘。为提供性能，可以设置为0或2，但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件，而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志，但日志文件每次刷新到磁盘一次。

#innodb_max_dirty_pages_pct = 90 使用默认75观察
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒每秒的输入输出量(或读写次数)。

#innodb_data_file_path = ibdata1:1024M:autoextend 设置过大导致报错，一般为1G左右
#表空间文件重要数据

long_query_time = 1 可以设置为0.1~0.5s

binlog_format = row #复制过程不会出现丢数据的情况

interactive_timeout = 28800 wait_timeout = 28800
交互等待时间和非交互等待时间两个要一致建议为300~500s 不要默认的8小时

max_connections = 1000 不在太大小心OOM

#innodb_log_file_size = 128M 使用默认48M redo log
#此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

gerenal log 全局日志建议关闭浪费磁盘空间降低mysql性能
#innodb_file_io_threads = 4 不明确，使用默认值
#文件IO的线程数，一般为 4，但是在 Windows 下，可以设置得较大。

</pre>

[[File:my1.jpg|frameless|50px]]

[[File:my2.jpg|frameless|50px]]

===常见参数参考 ===
<pre>
bulk_insert_buffer_size
专用于myisam引擎，用一个特别的类似的树形结构体缓存，用于提高 insertselect insert…values（…）(….)以及load data写数据到非空表的情景

MySQL relay_log_purge=0 时的风险

</pre>

[https://blog.csdn.net/jiao_fuyou/article/details/51073945 MySQL relay_log_purge=0 时的风险]

[https://blog.csdn.net/leshami/article/details/45688503 MHA 清理relay log（purge_relay_logs）]

[https://www.jianshu.com/p/6dda4a141ebe MySQL的my.cnf文件（解决5.7.18^下没有my-default.cnf）]

[https://blog.51cto.com/dbaspace/1884341 MySQL5.7配置参数]

[https://wenku.baidu.com/view/9643c1badc3383c4bb4cf7ec4afe04a1b071b064.html 高性能MySQL5.7配置信息]

[https://blog.csdn.net/weiliu1463/article/details/26512817 调整刷新脏页处理innodb_io_capacity]

[https://blog.csdn.net/twj13162380953/article/details/70142206 mysql参数之innodb_buffer_pool_size大小设置]

[https://www.cnblogs.com/luyucheng/p/6265594.html MySQL慢查询（一） - 开启慢查询]

[https://blog.imdst.com/mysql-5-6-pei-zhi-you-hua/ MySQL 5.6 my.cnf配置优化]

=2.优化数据库对象=
2.1选择合适的存储引擎
2.2字段选择合适的数据类型
procedure analyse() 可以对当前应用的表进行分析，对数据表中列的数据类型提出优化建议。

2.3三范式和反三范式
2.4对表进行水平或者垂直拆分
=3.针对存储引擎的优化=
优化特定参数

=SQL和索引优化=
① 为查询缓存优化查询
② EXPLAIN 我们的SELECT查询(可以查看执行的行数)
③ 当只要一行数据时使用LIMIT 1
④ 为搜索字段建立索引
⑤ 在Join表的时候使用相当类型的列，并将其索引
⑥ 千万不要 ORDER BY RAND ()
⑦ 避免SELECT *
⑧ 永远为每张表设置一个ID
⑨ 可以使用ENUM 而不要VARCHAR
⑩ 尽可能的使用NOT NULL
⑪ 固定长度的表会更快
⑫ 垂直分割
⑬ 拆分打的DELETE或INSERT语句
⑭ 越小的列会越快
⑮ 选择正确的存储引擎
⑯ 小心 "永久链接"
---------------------

原文：https://blog.csdn.net/u013087513/article/details/77899412

=5.磁盘I/O优化=
==5.1使用磁盘阵列（RAID）==
==5.2使用Linux虚拟文件卷模拟RAID==
==5.3符号连接（Symbolic Links）分布I/O==
利用操作系统的符号连接（Symbolic Links）将不同的数据库、表或索引指向不同的物理磁盘，从而达到分布磁盘I/O的目的。

==5.4禁止操作系统更新文件的atime属性==
==5.5用裸设备（Raw Device）存放InnoDB的共享表空间==
==5.6调整I/O调度算法==
==5.7RAID卡电池的充放电引起的性能波动==
==5.8NUMA架构优化==
非一致存储访问结构（Non-Uniform Memory Access, NUMA）

=6.应用优化=
==6.1使用连接池==
==6.2减少对MySQL的访问==
①理清应用逻辑，能一次取出的数据不用两次；
②使用查询缓存
MySQL的查询缓存（MySQL query cache）是4.1版本之后新增的功能，作用是存储select的查询文本和相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询。
查询缓存适用于更新不频繁的表，当表更改（包括表结构和数据）后，查询缓存会被清空。
③在应用端增加cache层

④负载均衡

=源码安装性能优化=
==去掉不需要的模块==
==只选择要使用的字符集==
==使用静态编译以提高性能==

=程序哥要的优化=
[https://blog.csdn.net/ieayoio/article/details/79543899 mysql5.7+ 关闭ONLY_FULL_GROUP_BY]
=see also=

[https://blog.csdn.net/sinat_23080035/article/details/52802569 MySQL性能优化方案总结good]
[https://coolshell.cn/articles/1846.html MYSQL性能优化的最佳20+条经验]

[https://my.oschina.net/bigdataer/blog/894147 Mysql数据库性能优化]

[http://blog.jobbole.com/87450/ 运维角度浅谈MySQL数据库优化]

[http://dbaplus.cn/news-11-186-1.html 美图秀秀DBA谈MySQL运维及优化]

[https://blog.csdn.net/Jacabe/article/details/82812787# mysql性能调优笔记(二)--查询优化和索引]

[https://blog.csdn.net/sinat_23080035/article/details/52802569 MySQL性能优化方案总结]

[https://zhuanlan.zhihu.com/p/55975068 MySQL性能优化]

[https://blog.csdn.net/weixin_41580948/article/details/79573932 MySQL性能优化]

[https://blog.csdn.net/qq_39827935/article/details/81214376 性能优化之MySQL优化]

[https://www.cnblogs.com/bingabcd/p/7492670.html MySQL20个经典面试题]

[https://blog.csdn.net/Jacabe/article/details/82810658 MySql性能调优笔记(一)]

[http://www.cnblogs.com/jiekzou/p/5371085.html Mysql性能优化一]

[https://www.cnblogs.com/jiekzou/p/5380073.html Mysql性能优化二]

[https://blog.csdn.net/u011277123/article/details/61914773 Mysql千万级别数据批量插入只需简单三步]

[http://www.dongcoder.com/detail-15912.html MySQL 优化之 Linux系统层面调优]

[https://www.jianshu.com/p/684c67f9b016 mysql优化]

[https://blog.csdn.net/weixin_41580948/article/details/79573932 MySQL性能优化]

[https://blog.csdn.net/lufeisan/article/details/53339991 Linux系统swappiness参数在内存与交换分区之间优化作用]

[[category:mysql]] [[category:ops]]

Mysql性能优化 - 版本历史

2022年12月8日 (四) 06:09 Evan