标签：教程 缓存 性能 连接数 设置 MySQL 数据库
将 MySQL 部署到普通的 X86 服务器上，在不经过任何优化情况下，MySQL 理论值正常可以处理 2000 左右 QPS。不过当访问量达到 1500 左右并发连接时，数据库处理性能就会变慢。
QPS（Queries Per Second）：每秒查询数，即一台数据库每秒能够处理的查询次数
而经过优化后，有可能会提升到 2500 左右 QPS。下面演示如何通过参数配置优化，让数据库最大化发挥性能。
一、选择合适的存储引擎
（1）MySQL 常用有两种存储引擎：
- 一个是 MyISAM，不支持事务处理，读性能处理快，表级别锁。
- 另一个是 InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。
- 表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。
- 行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。
（2）根据以上对比看来，使用 InnoDB 存储引擎是最好的选择，这也是 MySQL5.5 以后版本中默认存储引擎。除了一些公共参数外，每个存储引擎也有各自相关联的参数也会影响数据库性能，下面分别进行介绍。
二、公共参数配置
修改 mysql 数据库配置方法：编辑配置文件 /etc/my.cnf，在 [mysqld] 段中添加或修改对应的参数值即可。
1，修改数据库最大并行连接数
（1）如果实际连接数超过数据库最大连接数，会出现“MySQL: ERROR 1040: Too many connections”的异常情况。默认配置下数据库最大连接数是 151，我们可以通过如下命令查看：
|
1
|
show variables like '%max_connections%';
|
（2）我们可以根据情况，通过 max_connections 参数修改最大连接数，比如下改成 600：
（3）究竟最大连接数设置多少合适，我们可以执行如下命令查看 Max_used_connections 值来判断，它指的是本次 mysql 服务启动到现在，同一时刻并行连接数的最大值（即历史并发请求峰值）。
|
1
|
show global status like 'Max_used_connections';
|
三、MyISAM 参数配置
提示：下面设置仅针对以 MyISAM 引擎为主的 DB。如果以 InnoDB 引擎为主的 DB 则跳过此节。
1，设置索引缓存区大小
索引缓存区大小一般设置物理内存的 30% - 40%。
|
1
|
key_buffer_size = 2048M
|
2，设置读操作缓冲区大小
读操作缓冲区大小默认为 128K。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。一般设置为 2M 就足够了。
3，设置查询缓存功能
（1）为了加快查询语句的执行性能，从MySQL早期的版本开始就提供了一种名叫 Query Cache 的缓存技术。
Query Cache 的工作原理：当某一个客户端连接（session）进行 SQL 查询并得到返回信息时，MySQL 数据库除了将查询结果返回给客户端外，还在特定的内存区域缓存这条 SQL 查询语句的结果，以便包括这个客户端在内的所有客户的再次执行相同查询请求时，MySQL 能够直接从缓存区返回结果。
（2）下面配置将 query_cache_type 设置为 1 表示开启查询缓存功能，同时设置 Query Cache 的缓冲区大小为 32M。
|
1
2
|
query_cache_type = 1
query_cache_size = 32M
|
四、InnoDB 参数配置
提示：下面设置仅针对以 InnoDB 引擎为主的 DB。如果以 MyISAM 引擎为主的 DB 则跳过此节。
1，设置索引和数据缓冲区大小
该设置对 InnoDB 表来说非常重要，因为 InnoDB 相比 MyISAM 表对缓冲更为敏感，如果使用默认值十分影响性能。一般设置物理内存的 60% - 70%
|
1
|
innodb_buffer_pool_size = 6G
|
2，设置缓冲池实例个数
innodb_buffer_pool_instances 可以开启多个内存缓冲池，把需要缓冲的数据 hash 到不同的缓冲池中，这样可以并行的内存读写。该参数值默认为 1，推荐设置 4 个或 8 个。
|
1
|
innodb_buffer_pool_instances = 8
|
3，设置日志同步到磁盘方式
（1）MySQL 的事务日志中，为了性能和效率，可以配置每次事务提交后是否将日志缓冲刷到 log file 以进行数据持久化，这就是变量 innodb_flush_log_at_trx_commit 的作用。innodb_flush_log_at_trx_commit 有 3 种可选值：
- 0：代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失 1 秒左右事务数据。
- 1：为每执行一条 SQL 后写入到日志并同步到磁盘，I/O 开销大，执行完 SQL 要等待日志读写，这种方式即使系统崩溃也不会丢失任何数据，但效率低。
- 2：代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。
（2）对数据安全性要求不是很高的推荐设置 2，性能高，修改后效果明显。
|
1
|
innodb_flush_log_at_trx_commit = 2
|
4，设置独享表空间
默认是共享表空间（参数值为 0），共享表空间 idbdata 文件不断增大，影响一定的 I/O 性能。推荐开启独立表空间模式（参数值为 1），每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
- 共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。默认的文件名为 :ibdata1 初始化为 10M。
- 独占表空间：每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个 .frm 表描述文件，还有一个 .ibd 文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
|
1
|
innodb_file_per_table = 1
|
附：系统内核优化
大多数 MySQL 都部署在 linux 系统上，所以操作系统的一些参数也会影响到 MySQL 性能，比如：调小 TCP 协议的 time_wait 超时时间、调大服务器所支持的最大文件句柄数。具体方法参考我之前的文章：
原文出自：
www.hangge.com 转载请保留原文链接：
https://www.hangge.com/blog/cache/detail_3049.html
标签：教程,
缓存,
性能,
连接数,
设置,
MySQL,
数据库
From： https://www.cnblogs.com/xiaobingch/p/16817529.html