启用慢查询日志

慢查询日志记录了所有执行时间超过指定参数(lon…定位慢sql

工具排查慢sql

通过以上工具可以看到哪个接口比较慢并且可以分析SQL具体的执行时间定位到哪个sql出了问题。

启用慢查询日志

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

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

开启MySQL慢日志查询开关

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

long_query_time2配置完成后重启MySQL服务保证配置生效。

Time:2024-08-01T12:00:00.123456Z

UserHost:

Query_time查询时间查询执行的总时间单位为秒。

是关键的指标用于判断查询的性能。

Lock_time锁定时间表被锁定的时间单位为秒。

可以帮助判断是否存在锁等待问题。

Rows_examined检查的行数查询过程中检查的行数用于判断查询的效率。

分析慢sql

提供的一种用于查看查询语句执行的详细步骤和资源消耗的工具。

使用

SHOW

在执行查询时采用的具体执行计划包括访问数据表的方式、使用的索引、连接表的顺序等信息。

这些信息对于优化查询性能至关重要。

基本概念

查询语句要获取一条sql语句的执行计划只需要在语句前加上explain关键字即可。

explain

----------------------------------------------------------------------------------------------------------------------

select_type

----------------------------------------------------------------------------------------------------------------------

PRIMARY

----------------------------------------------------------------------------------------------------------------------返回结果中各字段的含义解释如下

列名含义idSELECT

关键字对应的查询类型table用到的表名partitions匹配的分区对于未分区的表值为

NULLtype表的访问方法possible_keys可能用到的索引key实际用到的索引key_len所选索引的长度ref当使用索引等值查询时与索引作比较的列或常量rows预计要读取的行数filtered按表条件过滤后留存的记录数的百分比Extra附加信息

字段释意

查询的序列标识符用于表示查询的执行顺序。

值越大优先级越低执行顺序越靠后。

select_type

查询的类型主要用于区分普通查询、联合查询、子查询等复杂的查询常见的值有

SIMPLE:

查询执行的类型描述了查询是如何执行的。

常见的类型如下这些类型的性能从最优到最差排序为system

const

system如果表使用的引擎对于表行数统计是精确的如MyISAM且表中只有一行记录的情况下访问方法是

system

的一种特例。

const表中最多只有一行匹配的记录一次查询就可以找到常用于使用主键或唯一索引的所有字段作为查询条件。

eq_ref当连表查询时前一张表的行在当前这张表中只有一行与之对应。

是除了

system

方式常用于使用主键或唯一索引的所有字段作为连表条件。

ref使用普通索引作为查询条件查询结果可能找到多个符合条件的行。

range对索引列进行范围查询执行计划中的

key

类似只不过扫描的是索引而索引一般在内存中速度更快。

ALL全表扫描。

possible_keys

index表明查询使用了覆盖索引不用回表查询效率非常高。

Using

index

condition表示查询优化器选择使用了索引下推这个特性。

Using

WHERE

子句进行条件过滤。

一般在没有使用到索引的时候会出现。

Using

filesort在排序时使用了文件排序而不是索引排序通常是因为无法使用索引进行排序。

Using

temporaryMySQL

Loop)连表查询的方式表示当被驱动表的没有使用索引的时候MySQL

会先将驱动表读出来放到

根据explain执行计划的返回结果我们可以根据以下字段进行sql优化

通过key和key_len检査是否命中了索引索引本身存在是否有失效的情况通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全表扫描通过extra字段判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复

深分页优化查询

1000;这种方法对于小数据集或页数较小时效果较好但在数据量非常大的情况下OFFSET

深分页

这种方法通过子查询使用覆盖索引快速定位到分页的起始位置外部查询从该位置获取实际数据避免大量数据扫描和回表操作。

如本例中通过子查询定位到了第100001页的起始位置向后获取100行数据。

SELECT

将结果缓存起来随后从缓存中进行分页这种方法适用于需要多次访问相同分页结果的场景。