如何在sql存储过程中打log_SQL语句优化

最新推荐文章于 2024-07-28 03:16:43 发布

weixin_39561431

最新推荐文章于 2024-07-28 03:16:43 发布

阅读量621

点赞数

文章标签：如何在sql存储过程中打log 怎么让sql查询的字段可以不出现在group分组里

如何定位并优化慢查询 SQL？大致思路如下：

根据慢查询日志定位慢查询 SQL；
使用 explain 等工具分析 SQL；
修改 SQL 或者尽量让 SQL 走索引。

获取有性能问题的 SQL 的两种方法：

通过慢查日志获取存在性能问题的 SQL；
实时获取存在性能问题的 SQL；

慢SQL定位

1.根据慢查询日志定位慢查询 SQL

MySQL 慢查询日志是一种性能开销比较低的解决方案，主要性能开销在磁盘 IO 和存储日志所需要的磁盘空间。对于磁盘 IO 来说，由于写日志是顺序存储，开销基本上忽略不计，所以主要需要关注的还是磁盘空间。

首选，需要确认是否已经开启了慢查询日志：

show

MySQL 提供了以下参数用于控制慢查询日志：

slow_query_log

开启慢查询日志有两种方式：

通过配置 /etc/my.cnf 文件开启，是永久性的
通过设置全局变量开启，MySQL 重启后会失效。

设置全局变量的 SQL 如下：

set

和二进制日志不同，慢查询日志会记录所有符合条件的 SQL，包括查询语句、数据修改语句、已经回滚的 SQL。

慢查询日志中记录的内容：

# Query_time: 0.000220             //执行时间，可以精确到毫秒，220毫秒

通常情况下，在一个繁忙的系统中，短时间内就可以产生几个 G 的慢查询日志，人工检查几乎是不可能的，为了快速分析慢查询日志，必须借助相关的工具。

常用的慢查询日志工具：

mysqldumpslow：一个常用的，MySQL 官方提供的慢查询日志分析工具，随着 MySQL 服务器的安装而被安装。可以汇总除查询条件外其他完全相同的 SQL，并将分析结果按照参数中所指定的顺序输出。
pt-query-digest：用于分析 MySQL 慢查询的一个工具。

2.实时获取性能问题SQL

为了更加及时的发现当前的性能问题，我们还可以通过实时的方法来获取有性能问题的 SQL。最方便的一种方法就是利用 MySQL information_schema 数据库下的 PROCESSLIST 表来实现实时的发现性能问题 SQL。例如下面这条 SQL 表示查询出当前服务器中执行时间超过 1 秒的 SQL：

SELECT

然后我们可以通过脚本周期性的来执行这条 SQL，实时的发现哪些 SQL 执行的是比较慢的。

explain分析SQL

explain 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因。这个关键字一般放在 select 语句的前面，用于描述 MySQL 如何执行查询操作以及 MySQL 成功返回结果集需要执行的行数，执行会输出一些 explain 的字段。例如：

EXPLAIN

需要注意的是，执行 explain 并不会真正的执行 SQL，而是对 SQL 做了一些分析，速度非常快。

explain 关键字段：

id

type 字段：

type 字段的返回值，性能从最优到最差：

system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> all

index 和 all 表示本次查询走的是全表扫描。如果 type 值是这两个，表明 SQL 是需要优化的。

Extra字段

Extra 中出现了以下两种意味着 MySQL 根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化：

Extra 项	说明
Using filesort	表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为 “文件排序”。
Using temporary	表示 MySQL 在对查询结果排序时使用的临时表，常见于排序 order by 和分组查询 group by。

特定SQL的查询优化

大表/大数据量的更新和删除

对于大表的数据修改最好要分批处理，比如我们要在一个 1000 万行记录的表中删除/更新 100 万行记录，那么我们最好分多个批次进行删除/更新，一次只删除/更新 5000 行记录，避免长时间的阻塞，并且为了减少对主从复制带来的压力，每次删除/修改数据后需要暂停几秒。这里提供一个可以完成这样工作的 MySQL 存储过程的实例：

DELIMITER

2. 如何修改大表的表结构

对于 InnoDB 存储引擎来说，对表中的列的字段类型进行修改或者改变字段的宽度时还是会锁表，同时也无法解决主从数据库延迟的问题。

解决方案：

在主服务器上建立新表，新表的结构就是修改之后的结构，再把老表的数据导入到新表中，并且在老表上建立一系列的触发器，把老表数据的修改同步更新到新表中，当老表和新表的数据同步后，再对老表加一个排它锁，然后重新命名新表为老表的名字，最好删除重命名的老表，这样就完成了大表表结构修改的工作。这样处理的好处是可以尽量减少主从延迟，以及在重命名之前不需要加任何的锁，只需要在重命名的时候加一个短暂的锁，这对应用通常是无影响的，缺点就是操作比较复杂。好在有工具可以帮我们实行这个过程，这个工具同样是 percona 公司 MySQL 工具集中的一个，叫做 pt-online-schema-change：

pt-online-schema-change

这个命令就是把 db_name 数据库下的 table_name 表中 c 列的宽度改为 VARCHAR(150)。

3. 如何优化not in和<>查询

MySQL 查询优化器可以自动的把一些子查询优化为关联查询，但是对于存在not in和<>这样的子查询语句来说，就无法进行自动优化了，这就造成了会循环多次来查找子表来确认是否满足过滤条件，如果子查询恰好是一个很大的表的话，这样做的效率会非常低，所以我们在进行 SQL 开发时，最好把这类查询自行改写成关联查询。

改写前：