索引-SQL 性能分析

SQL 执行频率的分析

Mysql 客户端连接成功后，通过 show [session|global] status命令可以提供服务器状态信息，通过如下指令可以查看当前数据库的insert、update、delete、select的访问频率

sql

show global status like 'Com_______';

Com 后跟上 7 个_表示 Com 开头的模糊匹配，后面还有 7 个字符的数据

查询结果如下：

bash

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 31    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

上述结果中，我们只用关注Com_insert、Com_delete、Com_update、Com_select几个指标，他们分别表示数据库增删改查的次数，可以看到查询的次数相对最多
可以根据这个数据决定是否要对数据库做索引优化
若是增删改的操作远高于查询，那么没有优化的必要

慢查询日志定位 SQL 性能

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认 10 秒)的所有 SQL 语句的日志。

查看慢查询日志开关的开启状态，OFF表示慢查询日志开关时关闭的

bash

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

配置慢查询开启状态以及默认时间

MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件(/etc/my.cnf)中配置如下信息:

bash

# 开启 MySQL 慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为 2 秒，SOL 语句执行时间超过 2 秒，就会视为慢查询，将sql记录到慢查询日志
long_query_time=2

配置完毕后，重启 mysql 服务器

bash

systemctl restart mysqld;

查看日志文件中记录的信息，日志文件的位置：/var/lib/mysql/localhost-slow.log

日志信息示例如下

text

# Time: 2021-10-28T15:45:39.688679z
# User@Host: root[root] @ localhost []  Id:  8
# Query_time:13.350650  Lock_time: 0.000358 Rows_sent: 1 Rows_examined: 0   use shop;
SET timestamp=1635435926;
select count(*) from tb_sku;

上述的慢查询日志记录了在什么时间，那个用户，查询花费了(Query_time) 13.350650 秒等详细信息，我们就可以根据蛇蝎信息来针对性的优化 sql

profile 详情-分析 sql

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiing 参数，能够看到当前 MySQL 是否支持： YES 表示支持，NO 表示不支持

bash

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

默认 profiling 是关闭的，可以通过 set 语句在 session/global 级别开启 profiling:

查看 profiling 的开启状态 0-关闭 1-开启

bash

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

开启 profiling

bash

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

开启 profiling 后，后续执行的一系列的业务 SQL 的操作各阶段的耗时情况都会被记录，可以通过如下指令查看指令的执行耗时:

sql

-- 查看每一条SQL的耗时基本情况（也是为了得到每一条sql语句的query_id）
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain 执行计划

explain 或者 desc 命令用于获取 mysql 如何执行 select语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序

语法

sql

-- 直接在 select 语句之前加上关键字 explain 或者 desc
explain select 字段列表 from 表名 where 条件;

示例：

bash

mysql> explain select * from emp,dept where emp.dept_id=dept.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL    | fk_emp_dept   | NULL    | NULL    | NULL             |    7 |   100.00 | Using where |
|  1 | SIMPLE      | dept  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.emp.dept_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

字段含义

id： select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序
- id 相同，执行顺序从上到下；
- id 不同，值越大，越先执行
select_type：表示 SELECT 的类型，常见的取值有
- SIMPLE（简单表，即不使用表连接或者子查询）
- PRIMARY（主查询，即外层的查询）
- UNION（UNION 中的第二个或者后面的查询语句）
- SUBQUERY（SELECT/WHERE 之后包含了子查询）等
type：<重点关注> 表示连接类型，优化 sql 的时候要向左边优化
性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
- NULL：只有在不访问表的查询才有可能会出现，例如： select 'A';
- const：当通过主键或者唯一索引访问表的时候会出现
- ref：当访问非唯一索引的时候会出现
- index：用了索引，但是也会对整个索引进行扫描遍历，扫描整个索引树
- all：全表扫描为 all
possible_key<重点关注>

显示可能应用在这张表上的索引，一个或多个。

Key<重点关注>

实际使用的索引，如果为 NULL,则没有使用索引。

Key_len<重点关注>

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows

MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

Extra

额外信息

若是包含 using index condition，表示查找使用了索引，但是需要回表查询数据

若是包含 using where;using index，表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

索引-SQL 性能分析 ​

SQL 执行频率的分析 ​

慢查询日志 定位 SQL 性能 ​

profile 详情-分析 sql ​

explain 执行计划 ​

索引-SQL 性能分析

SQL 执行频率的分析

慢查询日志定位 SQL 性能

profile 详情-分析 sql

explain 执行计划