1 mysql 基本功能

1.1、mysql连接器的工作流程:

1.2、查看连接状态:

show processlist;
Note:客户端太长时间没动静 就自动断开 这个时间是由wait_timeout参数控制的,默认8h

长连接短链接

长连接是链接成功后,如果客户端持续有请求,则使用同一个链接[尽量使用长连接,因为每次建立链接过程是很复杂的]
短链接则是每次执行完很少的几次查询后就断开链接了,下次查询则重新建立一个链接

1.3、使用长连接的困惑以及解决方案

为什么长连接占用内存涨的特别快

这是因为mysql在执行过程中临时使用的内存是管理在链接对象里面的。
这些资源会在链接断开的时候才释放。所以长时间累积下来就导致内存占用太大被系统强制杀掉(OOM[Out Of Memory]),从现象来看就是mysql异常重启了。

怎么去解决使用长连接占用内存涨的特别快的问题

定期断开长连接。使用一段时间或者程序里面判断执行过一个占用内存大的查询后,断开链接.之后要查询再重新链接.

如果使用mysql5.7或更新的版本可以在每次执行一个较大的操作后通过执行 mysql_reset_connection来重新初始化链接资源。这个过程中不需要重新链接和重新做权限验证但是会将链接恢复到刚刚创建完时的状态.
mysql_reset_connection

1.4、查询缓存

mysql拿到一个查询请求后,会先到查询缓存里面看看,之前执行过该语句 就会以key value的形式存储 key = 查询的sql语句 value= 查询结果, 查询缓存 只要一个表有更新操作就会被清空 除非业务有静态表很长时间才更新一次 才适合使用查询缓存.

MySQL提供的按需使用的方式
query_cache_type参数 设置成 DEMAND

显式制定使用查询缓存的语句(mysql8.0版本直接删除了查询缓存功能模块)
mysql> select SQL_CACHE * from T where ID=10

1.5、分析器

步骤1:对sql语句做解析
“词法分析” 你输入的是由多个字符串和空格组成的一条sql语句,mysql需要识别出里面的字符串分别是什么,代表什么。
“语法分析” 判断你输入的sql语句是否满足mysql的语法

1.6、优化器

当表中有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联 join的时候,决定个个表的链接顺序.
举例说明:
mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
既可以先从表 t1 里面取出 c=10 的记录的 ID 值再根据 ID 值关联到表 t2再判断 t2 里面 d 的值是否等于20
也可以先从表 t2 里面取出 d=20 的记录的 ID 值再根据 ID 值关联到 t1再判断 t1 里面 c 的值是否等于10

这两种执行方法的逻辑结果时一样的但是执行的效率会有不同而优化器的作用就是决定选择哪一个方案

1.6、执行器

开始执行sql的时候
判断是否有对表t的执行权限
没有索引的执行流程

调用 InnoDB 引擎接口取这个表的第一行判断 ID 值是不是1如果不是则跳过如果是则将这行存在结果集中
调用引擎接口取"下一行"重复相同的判断逻辑直到取到这个表的最后一行

执行器将上述遍布过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

有索引的执行流程

第一调用的是"取满足条件的第一行"这个接口
之后循环取"满足条件的下一行"

2 优化连接池

连接池运行机制
MySQL连接器中的连接池用以提高数据库密集型应用程序的性能和可扩展性默认启用。MySQL连接器负责管理连接池中的多个连接自动创建、打开、关闭和破坏连接多个连接的创建可满足多客户端的频繁连接连接的重复使用获得最佳性能。
MySQL连接器 每三分钟运行一次后台作业并从池中删除闲置未使用超过三分钟的连接。池清理释放客户端和服务器端的资源。这是因为在客户端每个连接都使用一个Socket而在服务器端每个连接都使用一个Socket和一个线程。

max_connectionsMySQL最大并发连接数默认值是151最大连接数上限是16384

• 经验实际连接数是最大连接数的 85% 较为合适。
  设置 max_used_connections 方法

– 查询数据库目前设置的最大并发连接数是多少
SHOW VARIABLES LIKE ‘max_connections’;

– 查询数据库目前实际连接的并发数是多少
SHOW STATUS LIKE ‘max_used_connections’;

– 在MySQL配置文件 /etc/my.cnf 中设置 max_connections=3000表示修改最大连接数为3000。

注意需要重启 MySQL 才能生效。
– MySQL为每个连接创建缓冲区所以不应该盲目上调最大连接数。

如果最大连接数达到了上面设置的 3000会消耗大约 800M 内存。

其他连接池设置

开启连接池 Pooling=true默认开启
复用时重置连接状态 ConnectionReset=True
保持连接设置 CacheServerProperties=True
连接超时回收(秒) ConnectionLifeTime=300
支持的最大连接数量 Max Pool Size=100
保持最小的连接数量 Min Pool Size=10