更多相关内容可查看
SQL性能监控
全局监控:通过全局监控,可以了解整个数据库集群或服务器的性能状况。这有助于确定是否存在性能瓶颈或问题区域。
定点监控:在确定了全局性能问题后,可以进行定点监控,专注于具体的查询或事务。这有助于识别造成性能下降的具体原因,如慢查询或磁盘问题
SQL执行频率
MySQL 客户端连接成功后,通过 show [session|global] status
命令可以查看服务器状态信息
可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT
的访问频次
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以查询为主,那么就要考虑对数据库的索引进行优化了。
那么通过查询SQL的执行频次,我们就能够知道当前数据库到底是增删改为主,还是查询为主。 那假如说是以查询为主,我们又该如何定位针对于那些查询语句进行优化呢?
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time
,单位:秒,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log。
如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
systemctl restart mysqld
然后,再次查看开关情况,慢查询日志就已经打开了。
windows下临时配置慢查询 :
show variables like '%slow_query_log%';
set global slow_query_log = 1 ;
set global slow_query_log_file='D:\\Users\\Desktop\\log\\mysql-slow.log';
set long_query_time = 1 ;
show variables like '%long_query_time%';
select count(*) from item ;
测试:
- 执行如下SQL语句 :
select * from item; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from item ; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时 13.35sec
- 检查慢查询日志 :
最终我们发现,在慢查询日志中,只会记录执行时间超多我们预设时间(2s)的SQL,执行较快的SQL是不会记录的。
那这样,通过慢查询日志,就可以定位出执行效率比较低的SQL,从而有针对性的进行优化
explain
找到了哪些SQL语句执行速度比较慢, 那么我们该如何进行优化呢 ? 首先我们需要知道这条SQL为什么慢 , 找到慢的原因
TIPS : 任何一个问题的解决思路, 都是先找到问题, 再针对性解决问题
可以通过SQL语句执行计划EXPLAIN
关键字或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
字段 | 含义 |
---|---|
id | select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。 |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、 |
UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等 | |
type | 表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
possible_key | 显示可能应用在这张表上的索引,一个或多个。 |
key | 实际使用的索引,如果为NULL,则没有使用索引。 |
key_len | 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。 |
rows | MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的。 |
filtered | 表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。 |
id
每个 SELECT语句都会⾃动分配的⼀个唯⼀标识符. 表示查询中操作表的顺序,有三种情况:
- id相同:执⾏顺序由上到下
- id不同:如果是⼦查询,id号会⾃增,id越⼤,优先级越⾼。
- id相同和不同同时存在
id列为null的就表示这是⼀个结果集,不需要使⽤它来进⾏查询。
例如 :
explain select (select name from tb_user) from tb_user ;
select_type
查询类型,主要⽤于区别普通查询、联合查询(union、union all)、⼦查询等复杂查询。
explain select * from tb_user
explain select (select name from tb_user) from tb_user ;
explain select * from tb_user where id = (select max(id) from tb_user);
explain select * from tb_user where sex='man' union select * from tb_user where sex='2';
explain select * from (select * from tb_user where sex='man') b;
table
显示的查询表名
- 如果查询使⽤了别名,那么这⾥显示的是别名
- 如果不涉及对数据表的操作,那么这显示为null
- 如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执⾏计划中的id,表示结果来⾃于 这个查询产⽣
- 如果是尖括号括起来的,与<unionM,N> 类似,也是⼀个临时表,表示这个结果来⾃于union查询 的id为M,N的结果集。
type
索引的使用类型 , 性能由好到差依次为 :
system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL
除了all之外,其他的type都可以使⽤到索引,除了index_merge
之外,其他的type只可以⽤到⼀个索引
TIPS : 如果想让一条SQL的效率高, 那么最起码要使用到range级别
explain select * from (select * from tb_user where id=1) a;
explain select * from tb_user where name = 'Bob';
explain select a.id from tb_user a left join tb_dep b on a.deptId=b.id;
explain select * from tb_user where name = 'Bob';
explain select * from tb_user where name like 'B%';
explain select name from tb_user;
explain select * from tb_user;
possible_keys
此次查询中可能选⽤的索引,⼀个或多个
key
查询真正使⽤到的索引,select_type为index_merge时,这⾥可能出现两个以上的索引,其他的 select_type这⾥只会出现⼀个。
key_len
⽤于处理查询的索引⻓度,如果是单列索引,那就整个索引⻓度算进去,如果是多列索引,那么查询不⼀定都能使⽤到所有的列
key_len只计算where条件⽤到的索引⻓度,⽽排序和分组就算⽤到了索引,也不会计算到 key_len中
ref
如果是使⽤的常数等值查询,这⾥会显示const 如果是连接查询,被驱动表的执⾏计划这⾥会显示驱动表的关联字段
如果是条件使⽤了表达式或者函数,或者条件列发⽣了内部隐式转换,这⾥可能显示为func
rows
这⾥是执⾏计划中估算的扫描⾏数,不是精确值
Extra
这个列包含不适合在其他列中显示单⼗分重要的额外的信息,这个列可以显示的信息⾮常多,有⼏⼗ 种,常⽤的有
- using temporary : 表示使⽤了临时表存储中间结果
- using filesort : 排序时⽆法使⽤到索引时,就会出现这个。常⻅于order by和group by语句中
explain select * from tb_addr order by address;
- using index : 查询时不需要回表查询,直接通过索引就可以获取查询的数据
explain select name,age,sex from tb_user where name like '%B';
- 表示相应的SELECT查询中使⽤到了覆盖索引(Covering Index),避免访问表的数据⾏,效率不错!
- 如果同时出现Using Where ,说明索引被⽤来执⾏查找索引键值
- 如果没有同时出现Using Where ,表明索引⽤来读取数据⽽⾮执⾏查找动作
- using where : 表示存储引擎返回的记录并不是所有的都满⾜查询条件,需要在server层进⾏过滤
注:Oracle的EXPLAIN PLAN其实本质上跟Mysql的explain差距不大
本篇小结
有小伙伴对其他数据库内容感兴趣,可以通过以下链接进行查看
数据库-索引(基础篇)
数据库-索引结构(B-Tree,B+Tree,Hash,二叉树)
数据库-索引语法(增删查)
数据库-索引分类(主键索引、唯一索引、普通索引、全文索引)
数据库-索引使用(验证索引效率、单列索引与联合索引、最左前缀法则)
索引失效情况
数据库-Mysql锁详解(全局锁、表级锁、行级锁)
数据库-事物原理刨析(高级详解)