慢查询及其优化处理方法
慢查询定义
mysql慢查询日志是mysql提供的一种日志记录,它是用来记录在mysql中相应时间超过阈值的语句,就是指运行时间超过long_query_time值的sql,会被记录在慢查询日志中。long_query_time的默认值是10,意思是运行10S之上的语句
慢查询设置
-
查询慢sql时间设设置的值
show variables like 'long%'; -
修改慢查询时间阈值
set global long_query_time=4 -
展示慢sql的开启状态和日志文件的存放位置
show variables like 'slow%';
slow_launch_time 2
slow_query_log ON
slow_query_log_file /root/mysql/datas/mysql/slow_query.log -
未使用索引的查询被记录到慢查询日志中。如果调优的话,建议开启这个选项。如果开启了这个参数,full index scan的sql也会被记录到慢查询日志中。
show variables like 'log_queries_not_using_indexes';
开启开关
set global log_queries_not_using_indexes=1 -
查询有多少条慢查询记录
show global status like '%Slow_queries%';
通过explain来分析SQL性能(Mysql)
1. 什么是explain
explain是MySql中用来分析SQL语句执行效率的一条命令,通过explain命令我们可以知道以下信息:表的读取顺序,数据读取操作的类型,哪些索引可以使用,哪些索引实际使用了,表之间的引用,每张表有多少行被优化器查询等信息。
2. explain 参数解析
【id】
select查询的序列号包含一组数字,表示查询中执行select子句或者操作表的顺序
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先执行
id有相同的也有不同的,则相同的看为一组再继续按照上面两条规则执行
【select_type】
SIMPLE:简单的select查询,查询中不包含子查询或者UNION
PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为
SUBQUER: 在select或者where列表中包含了子查询
DERIVED:在from列表中包含的子查询被标记为DERIVED(衍生)MySql会递归执行这些子查询,把结果放在临时表
UNION:若第二个select出现在union之后,则被标记为UNION,若union包含在from子句的子查询中,外层select被标记为:DERIVED
UNION RESULT:从union表获取结果的select
【type】
- all:全表扫描
- index:全索引扫描
- range:范围扫描
- ref:不唯一的索引扫描,有多个符合条件的行
- eq_ref:唯一的索引扫描,只有一条记录与之匹配,常见于主键索引
- const、system:这两种的情况都比较好,前一种值的是条件为常量
- null:执行阶段不需要访问的表
索引type从优到差:System-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.
【table】
- 对应的表
【possible_keys】
- 哪些索引可能有助于查询。如果为空,说明没有可用的索引
【key】
- 实际从 possible_key 选择使用的索引,如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引
【key_len】
- 使用的索引的长度。在不损失精确性的情况 下,长度越短越好。
【ref】 - 这一列显示了哪些字段或者常量被用来和key配合从表中查询记录出来
【rows】 - 估计要读取的行数
【extra】 - Using index:使用覆盖索引,表示查询索引就可查到所需数据,不用扫描表数据文件,往往说明性能不错。
- Using Where:在存储引擎检索行后再进行过滤,使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。
- Using temporary:在查询结果排序时会使用一个临时表,一般出现于排序、分组和多表 join 的情况,查询效率不高,建议优化。
- Using filesort:对结果使用一个外部索引排序,而不是按索引次序从表里读取行,一般有出现该值,都建议优化去掉,因为这样的查询
CPU 资源消耗大。
优化方式
1. SQL调优
- 避免 SELECT *,只查询需要的字段。
- 小表驱动大表,即小的数据集驱动大的数据集: 当B表的数据集比A表小时,用in优化 exist两表执行顺序是先查B表再查A表查询语句:SELECT * FROM tb_dept WHERE id in (SELECT id
FROM tb_dept) ; 当A表的数据集比B表小时,用exist优化in ,两表执行顺序是先查A表,再查B表,查询语句:SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;- 尽量使用连接代替子查询,因为使用 join 时,MySQL 不会在内存中创建临时表。
2. 优化索引的使用
- 尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询。
- 不做列运算,把计算都放入各个业务系统实现
- 查询语句尽可能简单,大语句拆小语句,减少锁时间
- or 查询改写成 union 查询
- 不用函数和触发器
- 避免 %xx 查询,可以使用:select * from t where reverse(f) like reverse('%abc');
- 少用 join 查询
- 使用同类型比较,比如 '123' 和 '123'、123 和 123
- 尽量避免在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描
- 列表数据使用分页查询,每页数据量不要太大
- 避免在索引列上使用 is null 和 is not null
3. 表结构设计优化
- 使用可以存下数据最小的数据类型。
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
- 尽可能使用 not null 定义字段,因为 null 占用 4 字节空间。数字可以默认 0 ,字符串默认 “”
- 尽量少用 text 类型,非用不可时最好独立出一张表。
- 尽量使用 timestamp,而非 datetime。
- 单表不要有太多字段,建议在 20 个字段以内。
- Mysql常用数据类型存储大小及范围:https://blog.csdn.net/HXNLYW/article/details/100104768
4. show profiles 分析sql 性能
1. show profiles功能介绍
show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的,但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源
2. 查看并开启功能
为了统计报表,把profiling设为1
Profiling 功能由MySQL会话变量 : profiling控制,默认是OFF.关闭状态。select @@profiling; 或者show variables like '%profi%';
3. 使用方法
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)