MySQL中如何查询及优化慢SQL语句

MySQL中如何查询及优化慢SQL语句

引用

CSDN

1.

https://blog.csdn.net/2201_75656709/article/details/145793220

在优化SQL语句前，需要查看哪条SQL语句执行时间较长，性能较差，可以通过以下方式查看。

一、查询及分析SQL语句

1. 开启profiling功能

在MySQL中，profiling功能可以帮助你分析SQL查询的性能。默认情况下，profiling是关闭的，你需要通过以下命令将其开启：

1. SET profiling = 1；
2. 执行要查看时间的sql语句，例如：

   select * from user;
   

3. 如何调用方法：

   SHOW PROFILES;
   

   查看执行时间。

其中Duration为SQL执行时间，Query即为执行的SQL语句。

2. 开启MySQL的日志记录

1. 通过set global slow_query_log = 'ON'开启日志记录。
2. 通过set global slow_query_time = 1即可设置慢SQL阈值，单位为秒，当超过设置时间时，就会被记录到日志中。
3. 通过show variables like 'slow_query_log'查询日志是否开启，以及存储位置。

3. 使用EXPLAIN分析查询

使用EXPLAIN或EXPLAIN ANALYZE来查看SQL的执行计划。这可以帮助你理解查询是如何执行的，例如是否使用了正确的索引，是否进行了全表扫描等。

以上各字段的意思：

1. id：查询的标识符。每个SELECT查询都有一个唯一的标识符。如果查询中有子查询，这个ID可以显示子查询的顺序。
2. select_type：查询的类型。常见的值包括：

• SIMPLE：简单的SELECT，不包含子查询或UNION。
• PRIMARY：查询中最外层的SELECT。
• UNION、DEPENDENT UNION：内层查询依赖于外层查询的结果集。
• UNION RESULT：UNION的结果集。
• SUBQUERY：子查询中的第一个SELECT。

3. table：输出结果集的表。如果查询涉及多个表，此列表示MySQL决定按哪个表的顺序来连接它们。
4. partitions：当前操作所涉及的分区。
5. type：连接类型，显示MySQL决定如何连接表。常见的值包括：

• system：表只有一行（等于const）。
• const：表最多有一个匹配行。
• eq_ref：所有部分都使用了唯一索引或主键。
• ref：非唯一索引或唯一索引的非唯一前缀。
• fulltext：使用全文索引。
• ref_or_null：与ref类似，但增加了对NULL值的搜索。
• index_merge：表示使用了索引合并优化方法。
• unique_subquery：用于IN子查询，确保子查询返回唯一值。
• index_subquery：与unique_subquery类似，但可能返回非唯一值。
• range：对索引进行范围查找。
• index：全索引扫描，比全表扫描稍快。
• ALL：全表扫描。

6. possible_keys：显示可能应用在这张表中的索引。
7. key：实际使用的索引。如果为NULL，则没有使用索引。
8. key_len：使用的索引的长度。
9. ref：显示哪些列或常量被用于查找。
10. rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
11. filtered：表示此操作过滤中保留的的行数占扫描行数的百分比。值越小，说明该步骤筛选掉的数据越多。
12. Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary等。

二、优化方式

1. 优化索引：

• 确保对查询中经常使用的列建立索引。
• 考虑使用联合索引，特别是当查询条件中包含多个列时。
• 避免过度索引，过多的索引会增加写操作的开销。
• 使用覆盖索引（Covering Index），即索引中包含查询需要的所有列，避免回表。

2. 重构查询：

• 简化查询逻辑，避免使用复杂的子查询和连接（JOINs）。
• 使用WITH语句（Common Table Expressions, CTEs）来重构复杂的查询。
• 尽量减少数据处理的行数，例如通过增加WHERE条件来过滤掉不需要的行。

3. 调整查询语句：

• 确保使用正确的数据类型和格式，这可以减少转换开销。
• 使用合适的聚合函数和分组（GROUP BY）策略。
• 避免在SELECT列表中使用不必要的计算或函数调用。

4. 使用合适的表连接方式：

• 根据实际情况选择合适的连接类型（INNER JOIN, LEFT JOIN等）。
• 确保连接条件能够有效利用索引。

5. 分页优化：

• 如果使用LIMIT和OFFSET进行分页，确保在WHERE子句中添加过滤条件，这样可以减少需要排序的数据量。

在Spring项目中的优化方式

1. 缓存策略：

• 对于重复查询相同数据的情况，考虑使用应用层缓存（如Redis）或数据库缓存（如MySQL的Query Cache）。

2. 批量操作：

• 对于批量插入、更新或删除操作，可以使用MyBatis的批量执行功能来减少数据库的访问次数。

3. 使用缓存

• MyBatis 默认开启了一级缓存（即 SqlSession 级缓存），它会缓存当前 SqlSession 执行的查询结果。当你在同一个 SqlSession 内执行相同的查询时，会直接返回缓存的数据，而无需再次查询数据库。一级缓存作用范围仅限于同一个 SqlSession。
• 二级缓存：二级缓存是跨 SqlSession 共享的缓存，它能够提高应用程序的查询效率。二级缓存可以通过配置开启，并且支持使用外部缓存框架，如 Ehcache、Redis 等。
• 利用MyBatis的一级缓存和二级缓存来减少数据库访问次数。
• 可以考虑使用外部缓存框架（如Ehcache、Redis等）来进一步提升性能。