文章目录
一. 通过 show status 命令了解各SQL的执行频率
show [session|global] status like “Com_%”;
session:当前连接执行的统计结果
global:上一次数据库启动至今的统计结果
常见的执行参数:
Com_select:执行查询的次数
Com_insert:执行插入的次数
Com_update:执行更新的次数
Com_delete:执行删除的次数
Com_rows_read:执行查询的返回行数(举一反三:inserted、updated、deleted)
Connection:试图连接Mysql 服务器的次数
Uptime:服务器工作时间
Slow_queries:慢查询次数
mysql> show global status like "Slow_queries";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set
二. 查找定位低效 SQL 语句
可以通过两个办法定位效率较低的 SQL 语句:
1、通过慢查询日志 slow.log 定位执行效率低的SQL,但是只能查到执行完的 sql ,
比如:
# Time: 2020-06-17T07:21:27.095287Z
# User@Host: test[test] @ [1.1.1.1] Id: 1053670
# Query_time: 4884.455628 Lock_time: 0.000117 Rows_sent: 57 Rows_examined: 516215
SET timestamp=1592378487;
select `x`, `xxx`, `xxxx` ...
2、使用 show processlist
命令查看当前 Mysql 正在进行的线程,包括线程状态、是否锁表
mysql> show processlist ;
| Id | User | Host | db | Command | Time | State | Info |
| 1058129 | test | 1.1.1.1:57617 | mytest | Query | 3775 | Sending data | select `app ...
三. 通过 explain 分析低效 SQL 语句的执行计划
查询到效率低的 sql 语句后,可以通过 explain 分析低效 SQL 的执行计划。
explain 加在 select 前面,可以获取到查询执行计划。
在做 SQL 调优时,explain 是王道,
可以让我们检查到:在什么位置加索引最好,优化器是否以最佳顺序做表连接。
比如:
mysql> explain select * from testtb ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | testtb | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set
select_type: 表示select 的类型,常见取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY、UNION、SUBQUERY等。
table: 输出结果集的表
type: 表示Mysql 的访问方式(从上到下依次变快)
type=all,全表扫描,Mysql 遍历全表来找到匹配的行
type=index,Mysql 遍历整个索引来找到匹配的行
type=range,索引范围扫面
type=ref,使用非唯一索引扫描或唯一索引的前缀扫描
type=eq_ref,使用唯一索引
type-const/system,单表中最多只有一个匹配行
type=NUll,MySQL 不用访问表或者索引就能直接得到结果
possible_key: 表示查询时可能用到的索引
key: 表示实际用到的索引
key_len: 使用到索引字段的长度
rows: 扫描行的数量
Extra: 执行情况的说明和描述。
四. 通过 show profile 分析低效 SQL 语句
默认不开启 profile,使用时先开启 profile
mysql> set profiling=1;
Query OK, 0 rows affected
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set
测试获取表行数
mysql> select count(*) from testtb;
+----------+
| count(*) |
+----------+
| 1192 |
+----------+
1 row in set
查询执行时间
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.0109785 | select @@profiling |
| 2 | 0.05502275 | select count(*) from testtb |
+----------+------------+------------------------------+
2 rows in set
查询每一步的详细执行时间
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 8.2E-5 |
| checking permissions | 5E-6 |
| Opening tables | 3E-5 |
| init | 1.2E-5 |
| System lock | 5E-6 |
| optimizing | 0.056635 |
| statistics | 5.2E-5 |
| preparing | 1.2E-5 |
| executing | 3E-6 |
| Sending data | 5E-5 |
| end | 4E-6 |
| query end | 1.2E-5 |
| closing tables | 8E-6 |
| freeing items | 3.2E-5 |
| cleaning up | 1.1E-5 |
+----------------------+----------+
15 rows in set