-- mysql的常见性能优化配置-too many connections
【官网】：https://www.mysql.com/
应用场景
监控mysql日志或云数据库的警报，发现: 连接数限制, too many connections 导致mysql不可用.基础资源
mysql
使用须知
请根据监控mysql日志或云数据库的警报，确定是慢查询，索引，死锁，还是需要读写分离或分库分表等。 具体如何配置及涉及的默认配置值需要参考您自己的mysql版本，或云数据库厂商.
配置步骤
【常见原因及配置】
1.)保证代码中使用完连接，自动释放: close,dispose等.
2.)确保没有在for，while循环中构造并执行sql的情况.
3.)确保定时服务中的DB相关的扫描及操作间隔不要太密，工作线程数不要太多（线程需要检测状态自动释放）。
4.)可以利用数据库云厂商rds监控工具，优化索引，以便减少慢查询，慢查询占用时间长，类似不释放的效果。
5.)适当增大数据库的最大连接数。//阿里云rds.myql默认是300
注：如果最大连接数达到了上面设置的 3000，会消耗大约 800M 内存。考虑MySQL为每个连接创建缓冲区，所以不应该盲目上调最大连接数。
6.其它参数设置.wait_timeout=500 //阿里云rds.mysql默认：86400
loose_max_statement_time=15000 //单位：毫秒， 查询的最长耗时间，超过则放弃.. 阿里云rds.mysql默认是0（无限制）.
show processlist 得到进程，之后： kill {id}
开启连接池： Pooling=true，默认开启
复用时重置连接状态： ConnectionReset=True
保持连接设置： CacheServerProperties=True
连接超时回收(秒)： ConnectionLifeTime=300
支持的最大连接数量： Max Pool Size=200
保持最小的连接数量： Min Pool Size=50
数据库连接生存期：Connection Lifetime=14400 (单位：秒)
7.)优化大批量数据导入，防止偶尔用户的批量导入操作导致连接数耗尽.
使用优化SQL语句：将SQL语句进行拼接，使用 insert into table () values (),(),(),()然后再一次性插入，如果字符串太长，
则需要配置下MYSQL，在mysql 命令行中运行 ：set global max_allowed_packet = 2*1024*1024*10;消耗时间为：11:24:06 11:25:06;
【附：mysql数据库连接的工作和释放机制】
Pooling=true;Max Pool Size=200;Connection Lifetime=14400
（ConnectionLifetime中间也可以不要空格）
连接池端有一个lifetime控制(连接用完之后，检查lifetime生存期是否达到超时时间，达到则释放，
如果超过这个时间就会靠mysql自己的超时释放了.
两种情况下连接池里的连接会减少。
(1)每当一个连接使用完后释放回连接池，如果当前时间减去该连接建立的时间的值大于Connection Lifetime设定的值(秒)，该连接被销毁。Connection Lifetime是用于集群数据库环境下。例如一个应用系统的中间层访问一个由3台服务器组成的集群数据库，该系统运行一段时间后发现数据库的负荷太大而需要增加第4台数据库服务器。如果不设置Connection Lifetime，你会发现新增加的服务器很久都得不到连接而原来3台服务器的负荷一点都没减少。这是因为中间层的连接一直都不会销毁而建立新的连接的可能性很小(除非出现增加服务器之后数据库的并发访问量超过增加前的并发最大值)。
注意：Connection Lifetime很容易让人产生误解。不要认为Connection Lifetime决定了一个连接的生存时间。因为只有连接被释放回连接池的时刻(Close连接之后)才会检查Connection Lifetime值是否达到而决定是否销毁连接，而连接在空闲或者正在使用的时候并不会检查Connection Lifetime。这意味着绝大多数情况下连接从建立到销毁经过的时间比Connection Lifetime大。另外，如果Min Pool Size为N (N > 0),那么连接池里有N个连接不受Connection Lifetime影响。这N个连接会一直在池里直到连接池被销毁。
(2)当发现某个连接对应的“物理连接”断开(这种连接称为“死连接”)，例如数据库已经被shutdown、网络中断、SQL Server的连接进程被kill、Oracle的连接会话被kill，该连接被销毁。“死连接”出现后不是立刻被发现，直到该连接被占用来访问数据库的时候才会被发现。
常见问题
快速入门
A)需要确定web产品中DB操作的基本情况:以便快速从主要原因上入手，事半功倍.
1、Mysql服务的cpu占用率，内存占用率，带宽占用率，连接数占用率等. 进而可以使用排除法。
2、Mysql访问得最多的数据.
3、Mysql执行得最多的慢查询.
4、Mysql停留时间最长的状态.
5、Mysql用来执行查询的使用得最频繁的子系统.
6、Mysql查询过程中访问的数据种类.
7、Mysql执行了多少种不同类型的活动，比如索引扫描.
B)监控和排查工具.
b1)相关的性能排查命令.
SHOW PROCESSLIST;
SHOW STATUS LIKE ‘%lock%‘; #查询锁定的信息
SHOW VARIABLES LIKE ‘%timeout%‘;# 查询超时的信息
SHOW FULL PROCESSLIST;
SHOW STATUS LIKE ‘Table%‘;#查询锁表状态统计.
show global variables like ‘%slow%‘;#显示所有的慢查询.
show global variables like ‘%not_using%‘;#获取没用索引的查询,必须开启(log_queries_not_using_indexes)
b2)相关的辅助工具.
1) General log.
通过以上结果可以发现，log_output的值为FILE,证明是输出到日志文件，如果为TABLE则输出到默认‘mysql’数据库中的相应日志表
[注]开销较大，建议关闭.
2) Slow Log.
可以显示出没有使用慢查询和没有使用索引的功能.
c)常见优化思路.
c1)考虑是否需要分库分表（纵向，横向可以根据业务及架构情况来分析）
1)MyISAM表，MyISAM是表锁，在并发压力下，是会导致table_locks_waited急剧增加
2)Table_locks_immediate 指的是能够立即获得表级锁的次数，而Table_locks_waited指的是不能立即获取表级锁而需要等待的次数。如果 Table_locks_waited的值比较大的话，并且你遇到了性能问题，你可能希望将大表切分成小表；
c2)根据监控显示的慢查询，针对性的优化索引.
c3)频繁读而且频繁写的情况下，有条件时可以考虑读写分离。
c4)sql优化.
c4.1：<>可以改为:> union <. 另外对于唯一性比较好的进行索引查询.
c4.2: 用作索引的字段，尽量是稳定的，不是经常变的, 否则变动频繁..
c5)如果涉及非格式化数据，类似 like ‘%x%‘的则可以考虑使用全文检索，甚至基于api调用es或lucene架构的搜索结果.
附)关于如何获取慢查询sql.
<方案1>如果是云数据库等，一般官方有相关的性能监控及报表，会直接返回.
<方案2>如果是自行安装在服务器上的数据库，则通过mysql自带的慢查询获取方式.
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
查看是否开启： show variables like ‘%slow_query_log%‘;
开启慢查询日志：set global slow_query_log=1; (重启会失效)
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒
查看命令： show variables like ‘long_query_time%‘;
设置阀值命令： set global long_query_time=3 （修改为阀值到3秒钟的就是慢sql）
为什么设置后看不出变化：
需要重新连接或新开一个会话才能看到修改值。 show variables like ‘long_query_time%‘;
直接 show global variables like ‘long_query_time‘;
查看慢查询日志：
cat -n /data/mysql/mysql-slow.log
查看有多少条慢查询记录: show global status like ‘%Slow_queries%‘;
日志分析工具mysqldumpslow.
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow
s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式，大小写不敏感的
工作常用参考:
得到返回记录集最多的10个SQL: mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
得到访问次数最多的10个SQL: mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
得到按照时间排序的前10条里面含有左连接的SQL: mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log
建议: 为方便 可以结合 | 和 more 使用，否则可能出现爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
