如何分析SQL语句性能

最新推荐文章于 2024-12-05 16:16:01 发布

PgSheep

最新推荐文章于 2024-12-05 16:16:01 发布

阅读量1.7k

点赞数 36

文章标签： sql 数据库

本文链接：https://blog.csdn.net/2301_79437276/article/details/139280955

版权

总结：最常用的就是explain

SQL性能分析

【主要优化的查询语句，索引优化占主导地位】

查看执行频次

【全局或会话】

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：【7个下划线】 SHOW GLOBAL STATUS LIKE 'Com_______'; 或者 SHOW SESSION STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

开启慢查询日志开关

slow_query_log=1

设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

long_query_time=2

更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log

查看慢查询日志开关状态：

show variables like 'slow_query_log';

配置完毕之后，通过以下指令重新启动MySQL服务器进程测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

查询：SHOW VARIABLES LIKE 'slow_query_log_file';

【我的服务器在/usr/local/mysql/data/iZf8z74wba1lkt3cpe42siZ-slow.log】

tail -f 文件名表示跟踪文件的信息

profile配置

show profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数，

查看是否开启：select @@profiling; 0未开启

能看到当前 MySQL 是否支持 profile 操作： SELECT @@have_profiling;

profiling 默认关闭，可以通过set语句在session/global级别开启 profiling： SET profiling = 1;

查看所有语句的耗时： show profiles;

查看指定query_id的SQL语句各个阶段的耗时： show profile for query query_id; 【query_id，由show profiles;查出】

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

explain

【用时间耗时判定性能只是粗略的判定】【explain可用来查看索引有没有失效，以及Key_Len判断哪个索引字段失效】

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。（是否用到索引）

语法：

直接在select语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

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（全表扫描）【id查也才const，Null不太可能（当不访问任何表的时候）】
possible_key：可能应用在这张表上的索引，一个或多个
Key：实际使用的索引，因为可能索引失效，如果为 NULL，则没有使用索引
Key_len：表示索引中使用的字节数，该值为索引字段（参加索引的变量名）最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
Extra：额外信息，前面没显示的在这里显示出来。【在覆盖索引&回表查询章节】

索引常见面试题 | 小林coding

【type 表示数据扫描类型，我们需要重点看这个。】

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）；

index（全索引扫描）；

range（索引范围扫描）；

ref（非唯一索引扫描）；

eq_ref（唯一索引扫描）；【通常用于多表联查】

const（结果只有一条的主键或唯一索引扫描）。

尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

explain 中 extra 字段含义： using index condition：查找使用了索引，但是需要回表查询数据【索引下推】 using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。

Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。

Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

null代表查询列未被索引覆盖，用了where后面的前导索引，没有全部使用【联合索引，但只用一个字段的where条件】