SQL性能分析手段

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供整个服务器执行sql的状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

在这里插入图片描述

Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

通过查看这些次数就能知道当前数据库以增删改为主，还是查询为主。

如果是以增删改为主，我们可以考虑不对其进行索引的优化。
如果是以查询为主，那么就要考虑对数据库的索引进行优化了（借助慢查询日志）

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（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

查看日志

[root@localhost mysql]# tail -f localhost-slow.log

在这里插入图片描述

Time：执行时间
User@Host：执行用户
Query_time：查询耗时

profile详情

帮助我们了解执行SQL具体耗时在哪个步骤

-- 查看是否支持 yes：支持
SELECT @@have_profiling ;

-- 查看开启状态 0：关闭、1：开启
 select @@profiling;

-- 开启
SET profiling = 1;

-- 查看每一条SQL的耗时基本情况
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况（query_id根据以上命令得到）
show profile for query query_id;

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

在这里插入图片描述

explain

获取 MySQL 如何执行 SELECT 语句的信息，如：数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息

支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句

语法

-- 直接在select语句之前加上关键字 explain / desc
explain SELECT 字段列表 FROM 表名 WHERE 条件 ;

在这里插入图片描述

列名	含义
id	select查询的序列号，表示查询中执行select子句或者数据表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type	表示 SELECT 的类型，常见的取值： 1. `SIMPLE`（简单查询，即不使用表连接或者子查询） 2. `PRIMARY`（主查询，即最外层的 `SELECT` 或 `union`最左的select） 3. `UNION`（`UNION` 之后出现的 `SELECT`） 4. `SUBQUERY`（子查询中的第一个 `SELECT`） 5. `DERIVED`：在 `FROM` 中出现的子查询 6. `UNION RESULT`：`UNION` 查询的结果
table	用到的表名 `<unionM,N>` : 本行引用了 id 为 M 和 N 的行的 UNION 结果； `<derivedN>` : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。 `<subqueryN>` : 本行引用了 id 为 N 的表所产生的的物化子查询结果。
partitions	匹配的分区，对于未分区的表，值为 NULL
type	查询执行的类型，描述了查询是如何执行的，性能由好到差的连接类型为`system`、`const`、`eq_ref`、`ref`、`range`、 `index`、`all` 。 `system`：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。 `const`：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件，如 where id = 1这种。 `eq_ref`：主键索引或唯一索引与其他字段的关联查询，最多只返回一条记录，on xx.id = xxx.id ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。 `range`：对索引列进行范围查询，如where id>3这种，执行计划中的 key 列表示哪个索引被使用了。 `index`：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。 `ALL`：没有索引全表扫描。
possible_keys	执行查询时可能用到的索引
key	实际用到的索引
key_len	实际使用的索引的最大长度，在满足需求的前提下越短越好
ref	与索引比较的列或常量
rows	MySQL预估执行查询要读取的行数，不是结果集的行数，数值越小越好
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好
Extra	附加信息，更准确的理解 MySQL 到底是如何执行查询的 `Using filesort`：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序 `Using temporary`：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。 `Using index`：表明查询使用了覆盖索引，不用回表，查询效率非常高。 `Using index condition`：表示查询优化器选择使用了索引条件下推这个特性。 `Using where`：where后面没用到索引，此时可考虑添加索引。 `Using join buffer (Block Nested Loop)`：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。