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

前言：在优化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:表示其他额外的信息，包括Usingindex、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等）来进一步提升性能。