13518219792
如果mysql 连接数据设置不合理可能会导致很小的流量mysql就提示MySQL: ERROR 1040: Too many connections错误了，那么要如何才算是合理设置mysql最大连接数呢，下面我来给大家介绍介绍。
站在用户的角度思考问题，与客户深入沟通，找到纳雍网站设计与纳雍网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站制作、做网站、企业官网、英文网站、手机端网站、网站推广、域名与空间、网站空间、企业邮箱。业务覆盖纳雍地区。
MySQL服务器的连接数并不是要达到最大的100%为好，还是要具体问题具体分析，下面就对MySQL服务器最大连接数的合理设置进行了详尽的分析，供您参考。
我们经常会遇见“MySQL: ERROR 1040: Too many connections”的情况，通常，mysql的最大连接数默认是100, 最大可以达到16384。
一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：
mysql show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：
mysql show global status like 'Max_used_connections';
MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是：
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接上线就设置得过高了
在Windows下常用的有两种方式修改最大连接数。
第一种：命令行修改。
mysql -uuser -ppassword(命令行登录MySQL)
mysqlshow variables like 'max_connections';(查可以看当前的最大连接数)
msyqlset global max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)
mysqlexit(推出)
这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。
第二种：修改配置文件。
这 种方式说来很简单，只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能，一个是在安装目录下（这是比较理想的情况），另一种是在数据文件的目录下，安装的时候如果没有人为改变目录的话，一般就在C:/ProgramData/MySQL往下的目录下。
与连接数相关的几个参数：
在修改最大连接数的时候会有这样一个疑问—这个值是不是越大越好，或者设置为多大才合适？这个参数的大小要综合很多因素来考虑，比如使用的平台所支持的线程库数量（windows只能支持到2048）、服务器的配置（特别是内存大小）、每个连接占用资源（内存和负载）的多少、系统需要的响应时间等。可以在global或session范围内修改这个参数。连接数的增加会带来很多连锁反应，需要在实际中避免由此引发的负面影响。
I 硬件配置优化
CPU选择：多核的CPU，主频高的CPU
内存：更大的内存
磁盘选择：更快的转速、RAID、阵列卡，
网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0.0.0.0多端口绑定监听
II 操作系统级优化
使用64位的操作系统，更好的使用大内存。
设置noatime,nodiratime
[zhangxy@dowload_server1 ~]$ cat /etc/fstab
LABEL=/ / ext3 defaults,noatime,nodiratime 1 1
/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2
优化内核参数
net.ipv4.tcp_keepalive_time=7200
net.ipv4.tcp_max_syn_backlog=1024
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.neigh.default.gc_thresh3 = 2048
net.ipv4.neigh.default.gc_thresh2 = 1024
net.ipv4.neigh.default.gc_thresh1 = 256
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.forwarding = 1
net.ipv4.conf.default.proxy_arp = 0
net.ipv4.tcp_syncookies = 1
net.core.netdev_max_backlog = 2048
net.core.dev_weight = 64
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_rfc1337 = 1
net.ipv4.tcp_sack = 0
net.ipv4.tcp_fin_timeout = 20
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_max_orphans = 32768
net.core.optmem_max = 20480
net.core.rmem_default = 16777216
net.core.rmem_max = 16777216
net.core.wmem_default = 16777216
net.core.wmem_max = 16777216
net.core.somaxconn = 500
net.ipv4.tcp_orphan_retries = 1
net.ipv4.tcp_max_tw_buckets = 18000
net.ipv4.ip_forward = 0
net.ipv4.conf.default.proxy_arp = 0
net.ipv4.conf.all.rp_filter = 1
kernel.sysrq = 1
net.ipv4.conf.default.send_redirects = 1
net.ipv4.conf.all.send_redirects = 0
net.ipv4.ip_local_port_range = 5000 65000
kernel.shmmax = 167108864
vm.swappiness=0
加大文件描述符限制
Vim /etc/security/limits.conf
加上
* soft nofile 65535
* hard nofile 65535
文件系统选择 xfs
/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2
III Mysql设计优化
III.1存储引擎的选择
Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库
Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。
III.2命名规则
多数开发语言命名规则：比如MyAdress
多数开源思想命名规则：my_address
避免随便命名
III.3字段类型选择
字段类型的选择的一般原则：
根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。
只分配满足需求的最小字符数，不要太慷慨。
原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。
III.3.1 整型：
见如下图:
类型
字节
最小值
最大值
(带符号的/无符号的)
(带符号的/无符号的)
TINYINT
1
-128
127
255
SMALLINT
2
-32768
32767
65535
MEDIUMINT
3
-8388608
8388607
16777215
INT
4
-2147483648
2147483647
4294967295
BIGINT
8
-9223372036854775808
9223372036854775807
18446744073709551615
根据满足需求的最小整数为选择原则，能用INT的就不要用BIGINT。
用无符号INT存储IP，而非CHAR(15)。
III.3.2 浮点型：
类型
字节
精度类型
使用场景
FLOAT(M,D)
4
单精度
精度要求不高，数值比较小
DOUBLE(M,D)（REAL）
8
双精度
精度要求不高，数值比较大
DECIMAL(M,D)(NUMERIC）
M+2
自定义精度
精度要求很高的场景
III.3.3 时间类型
类型
取值范围
存储空间
零值表示法
DATE
1000-01-01~9999-12-31
3字节
0000-00-00
TIME
-838:59:59~838:59:59
3字节
00:00:00
DATETIME
1000-01-01 00:00:00~9999-12-31 23:59:59
8字节
0000-00-00 00:00:00
TIMESTAMP
19700101000000~2037年的某个时刻
4字节
00000000000000
YEAR
YEAR(4):1901~2155 YEAR(2)：1970~2069
1字节
0000
III.3.4 字符类型
类型
最大长度
占用存储空间
CHAR[(M)]
M字节
M字节
VARCHAR[(M)]
M字节
M+1字节
TINYBLOD，TINYTEXT
2^8-1字节
L+1字节
BLOB，TEXT
2^16-1字节
L+2
MEDIUMBLOB，MEDIUMTEXT
2^24-1字节
L+3
LONGBLOB，LONGTEXT
2^32-1字节
L+4
ENUM('value1','value2',...)
65535个成员
1或2字节
SET('value1','value2',...)
64个成员
1,2,3,4或8字节
注：L表示可变长度的意思
对于varchar和char的选择要根据引擎和具体情况的不同来选择，主要依据如下原则:
1. 如果列数据项的大小一致或者相差不大，则使用char。
2. 如果列数据项的大小差异相当大，则使用varchar。
3. 对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间。
4. 对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空 间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。
5. 表中只要存在一个varchar类型的字段，那么所有的char字段都会自动变成varchar类型，因此建议定长和变长的数据分开。
III.4编码选择
单字节 latin1
多字节 utf8(汉字占3个字节，英文字母占用一个字节)
如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。
III.5主键选择原则
注：这里说的主键设计主要是针对INNODB引擎
1. 能唯一的表示行。
2. 显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。
3. MySQL主键应该是单列的，以便提高连接和筛选操作的效率。
4. 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
5. 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
6. MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
7. MySQL主键应当有计算机自动生成。
8. 主键字段放在数据表的第一顺序。
推荐采用数值类型做主键并采用auto_increment属性让其自动增长。
III.6其他需要注意的地方
NULL OR NOT NULL
尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：
1. 使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。
2. 进行比较的时候，程序会更复杂。
3. 含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。
索引
索引的缺点：极大地加速了查询，减少扫描和锁定的数据行数。
索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。
添加索引有如下原则：
1. 选择唯一性索引。
2. 为经常需要排序、分组和联合操作的字段建立索引。
3. 为常作为查询条件的字段建立索引。
4. 限制索引的数据，索引不是越多越好。
5. 尽量使用数据量少的索引，对于大字段可以考虑前缀索引。
6. 删除不再使用或者很少使用的索引。
7. 结合核心SQL优先考虑覆盖索引。
8. 忌用字符串做主键。
反范式设计
适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。
IV Mysql软件优化
开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。
使用推荐的GA版本，提升性能
利用分区新功能进行大数据的数据拆分
V Mysql配置优化
注意：全局参数一经设置，随服务器启动预占用资源。
key_buffer_size参数
mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断
innodb_buffer_pool_size参数
INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断
wait_time_out参数
线程连接的超时时间，尽量不要设置很大，推荐10s
max_connections参数
服务器允许的最大连接数，尽量不要设置太大，因为设置太大的话容易导致内存溢出，需要通过如下公式来确定：
SET @k_bytes = 1024;
SET @m_bytes = @k_bytes * 1024;
SET @g_bytes = @m_bytes * 1024;
SELECT
(
@@key_buffer_size + @@query_cache_size + @@tmp_table_size+
@@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size+
@@innodb_log_buffer_size+
@@max_connections *
( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+
@@join_buffer_size + @@binlog_cache_size + @@thread_stack
) )
/ @g_bytes AS MAX_MEMORY_USED_GB;
thread_concurrency参数
线程并发利用数量，(cpu+disk)*2,根据(os中显示的请求队列和tickets)判断
sort_buffer_size参数
获得更快的--ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT
read_rnd_buffer_size参数
当根据键进行分类操作时获得更快的--ORDER BY
join_buffer_size参数
join连接使用全表扫描连接的缓冲大小，根据select_full_join判断
read_buffer_size参数
全表扫描时为查询预留的缓冲大小，根据select_scan判断
tmp_table_size参数
临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断
innodb_log_file_size参数(默认5M)
记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。
Ø innodb_flush_method参数(默认fdatasync)
Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能
innodb_flush_log_at_trx_commit(默认1)
表示每秒进行一次log写入cache，并flush log到磁盘。
表示在每次事务提交后执行log写入cache，并flush log到磁盘。
表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。
VI Mysql语句级优化
1. 性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计.一般的对统计的数据不会要求太精准的情况下适用。
2. 尽量不要在数据库中做运算。
3. 避免负向查询和%前缀模糊查询。
4. 不在索引列做运算或者使用函数。
5. 不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。
6. 查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
7. where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。
8. 避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
9. 所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
10. 联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。
11. 开启慢查询，定期用explain优化慢查询中的SQL语句。
mysql只能设定连接的最大数,max connections,这还要取决于服务器性能,不是说连接数越多越好,要有足够的内存支持。
网站的负载能力取决于很多因素，运行平台的架构，代码性能，缓存的性能大小等等。
这里不能一一列举。
mysql的最大连接数默认是100,
这个数值对于并发连接很多的数据库应用是远远不够的，当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些。
调节方法为：
1.linux服务器中
：改my.cnf中的值就行了
2.Windows服务器中（我用的）:
在文件“my.ini”中找到段
[mysqld],在其中添加一行
max_connections=200###
200可以更改为想设置成的值.
然后重启"mysql"服务。
/mysqladmin所在路径/mysqladmin -uroot -p variables
输入root数据库账号的密码后可看到
| max_connections | 1000 |
其他需注意的：
在编程时，由于用mysql语句调用数据库时，在每次之执行语句前，会做一个临时的变量用来打开数据库，所以你在使用mysql语句的时候，记得在每次调用完mysql之后就关闭mysql临时变量。
另外对于访问量大的，可以考虑直接写到文本中，根据预测的访问量，先定义假若是100个文件文件名依次为1.
txt,2.
txt
100.
txt。
如何设置mysql数据库访问次数
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
