MySQL连接与优化-优快云博客

mysql连接

- mysql的连接
- 通信类型
1 同步通信 : 同步通信依赖于被调用方,程序在连接数据库时,会一直等待数据库的返回和响应,
2 异步通信: 可以避免应用阻塞等待,遇到高并发一条sql建立一个连接,
- 连接方式
1 短连接: 创建连接-数据传输-关闭连接   注意每次进行连接都会重新创建连接
2 长链接： 创建连接-数据传输-保持连接-数据传输-保持连接-关闭连接
- 连接协议
1 tcp/ip: 套接字连接方式是任何平台连接mysql的方式,在网络中最常用的方式,
2 Unix Socket: 是连接mysql需要一个物理文件,文件的存在位置在配置文件中有定义,所以mysql和客户端在同一台服务器上的情况下使用,它是所有协议中最高效的一种。
-查看mysql信息
1 select version() 版本号
2 show global status 查看mysql数据状态信息
3 show global status like 'Thread%' 查看连接数
具体信息详细解释
1 Thread_cached: 缓存中的线程数量
2 Thread_connected: 打开使用的连接数量
3 Thread_created: 总创建的线程数量
4 Thread_running: 未休眠的线程数量
-优化Thread_created 
Thread_create表示数据库创建的线程数量,数量越大代表线程越多,占用资源越多,会影响服务器运行
-解决方案
1 配置thread_cache_size 
thread_cache_size是当客户端断开之后,服务器会将客户端连接的线程缓存起来,以被下一个连接使用,
查看thread_cache_size的数量
show variables like 'thread_cache_size'
设置规则
1 如果是短连接的话,要设置大一些,
2 如果是长链接,可以设置小一些 50 - 100 
注意长链接是使用了数据库连接池了
在my.cnf配置文件中设置 thread_cache=64
-windos 下的mysql配置文件C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
设置最大连接数量 max_connections= 100

mysql架构

客户端 —— 连接器 作用是权限认证与管理连接 —— 分析器 语法分析和词法分析 —— 优化器 生成执行计划索引选择 —— 执行器 操作引擎返回结果集
- 查看mysql缓存配置信息
show variables like '%query_cache%';
1 have_query_cache 表示是否支持查询缓存  yes是支持
2 query_cache_type 查询缓存类型 off关闭 on 开启 
- 开启mysql缓存
1 查看当前数据库是否支持数据缓存
show variables like 'have_query_cache'
2 查看当前mysql是否开启缓存
show variable like 'query_cache_type' 
3 在配置文件中配置开启:
query_cache_type=0  关闭
query_cache_type=1  缓存所有结果
query_cache_type = 2 指定缓存结果 select sql_cache * from user 
配置mysql配置文件
在windows中名字是my.ini
在liunx中名字是my.cnf
4 配置缓存大小 
query_cache_size = 10M

注意重启服务才可以生效
- 什么情况下缓存失效
1 大小写不一致问题
2 方法函数是动态的
3 伪表数据

sql语句优化

-mysql日志分类
1 错误日志 log_error 
-- mysql的错误日志是记录mysql运行过程中的警告和报错信息还有每次启动和关闭的详细信息,名字通常为hostname.err
-- hostname是服务器主机名
-- 查看错误日志相关信息
show variables like '%log_error%'
## binlog_error_action 含义是错误处理方式 1 ab出现问题关闭服务 in 忽视错误
## log_error 含义是错误日志的文件以及路径
## log_error_verbosity记录级别 
## 修改错误日志路径在mysql配置文件中 my.ini 或是my.cnf 中
## log_error = 路径/日志.err
2 二进制日志
-- 这个日志包含了所有更新数据操作有新增,删除,修改,改表等 sql信息记录 mysql主从配置依赖这个日志
-- 查看二进制日志信息
show variables like '%log_bin%'
-- 默认是不开启动需要在配置文件中设置开启 全局配置文件 my.ini
log_bin=mysqllogbin 
重启mysql服务之后发现log_bin 参数值为on
通过命令查看二进制文件 show binlog events in 'mylogbin'
3 通用查询语句
这个日志是记录建立客户端连接和执行语句
- 查看日志信息
show variables like ‘%general%’
- 查看日志输出位置
show variables like '%log_output%'
- 查看系统日志地区格式
show variables like '%log_timestamps%';
- 设置日志时间为系统时间
log_timestamps = SYSTEM;
4 --查看慢查询日志
show variables like '%quer%'

- 执行计划
可以使用explain关键字来模拟优化器执行sql查询语句,来分析慢查询语句,
可以从慢查询日志中找到定位慢查询sql,在用执行计划分析慢原因
- 语法结构
explain select 列 from 表 where 条件
- 解释
1 select_type 查询类型 普通查询 子查询 联合查询
2 table 数据来源于那张表
3 type sql语句的级别 重要指标 system最好 all 最差
4 key 实际上使用的索引 
5 rows 需要扫描的行数
- 索引使用失效的场景
1 like 匹配第一个字符是通配符% 这样会失效
2 索引列参加了运算计算 
3 索引列使用了函数计算 
4 or关键字左右列都要是索引列才会有效
可以是union 来替换掉or 
- limit分页优化
使用子查询优化分页查询
select * from users where username = 'oldlu' limit 10000,20
select * from users where username = 'oldlu' and userid >= 
(select userid from users where username = 'oldlu' limit 10000,1) limit 20
假设id是递增的情况
select * from users where username = 'oldlu' and (userid between 10000 and 10020) limit 20;
-mysql索引
索引的作用：提高查询效率,快速定位到数据
常用的索引位置：创建唯一索引保证数据每一行的唯一性, 加快检索效率,加快表与表的连接,分组和排序时使用索引列提高效率,
索引的缺点:1 创建索引和维护索引要是消耗时间,2 索引也是要占用物理空间, 3 对表中进行增加,删除和修改都是要动态维护索引,
-索引需要加到那些列
1 经常搜索的列 
2 主键列 
3 外键列 用来建立表连接的列
4 排序和分组的列上
5 where之后的列加索引
- 索引分类 
1 单列索引 普通索引  唯一索引 可以是null 主键索引 不可以重复 不为null
2 组合索引
3 全文索引 
- 索引的使用方式
1排序使用索引时 如果where之后是索引列,那么order by 之后就不要使用索引了,因为mysql查询中只会使用一个索引
2 类型转化导致索引列无效 文本类型字段转化成数值类型
3 索引遇到 != <> 会失效  or

2022-mysql优化