mysql 存储过程执行慢_MySQL存储过程因为字符集不同导致执行慢

最新推荐文章于 2023-03-08 14:40:45 发布

Eunice Benson

最新推荐文章于 2023-03-08 14:40:45 发布

阅读量799

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 存储过程执行慢

本文链接：https://blog.csdn.net/weixin_42300167/article/details/113220366

本文分析了一个线上存储过程执行缓慢的问题，通过对比独立执行的DELETE语句，发现字符集不匹配导致索引失效是根本原因，并给出了两种解决方案。

业务反馈线上一个存储过程执行很慢，导致业务超时较多，而存储过程比较简单，就是一个简单的判断逻辑然后delete一条记录，而且delete语句是有索引的。

表结构如下：

CREATE TABLE `table1` (

`BaseName` varchar(255) NOT NULL COMMENT '相对路径名(相对于上层目录)',

`Bucket` mediumtext NOT NULL COMMENT '目录所属Bucket(业务名)',

`DirUuid` varchar(64) NOT NULL COMMENT '父目录Uuid',

`IsDir` tinyint(1) NOT NULL COMMENT '是否是文件夹',

xxx

PRIMARY KEY (`DirUuid`,`BaseName`),

KEY `origin` (`DirUuid`,`OriginBaseName`),

KEY `dir` (`DirUuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

存储过程如下：

CREATE DEFINER=`xxx`@`%` PROCEDURE `PDelete`(

IN `base_name` varchar(255),

IN `dir_uuid` varchar(36),

IN `IsDir` tinyint(1))

BEGIN

DECLARE t_error INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

START TRANSACTION;

IF IsDir = 1 THEN

delete from table1 where `DirUuid` = dir_uuid and `BaseName` = base_name;

delete from table2 where `DirUuid` = dir_uuid and `Dir` = base_name;

ELSE

delete from table2 where (`DirUuid` = dir_uuid and `BaseName` = base_name) or (`DirUuid` = dir_uuid and `OriginBaseName` = base_name);

END IF;

IF t_error = 1 THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

select t_error;

END

然后单独调用存储过程：

mysql> call PDelete('5', 'd3b18237-07ee-43bd-be15-aa7b7399f94b', 0);

+---------+

| t_error |

+---------+

| 0 |

+---------+

1 row in set (3.47 sec)

竟然需要3.47s，从存储过程的定义以及表结构来看，怎么也不用3.47s.

当单独拿出delete语句来执行看看：

mysql> delete from table1 where (`DirUuid` = 'd3b18237-07ee-43bd-be15-aa7b7399f94b' and `BaseName` = '5') or (`DirUuid` = 'd3b18237-07ee-43bd-be15-aa7b7399f94b' and `OriginBaseName` = '5');

Query OK, 0 rows affected (0.00 sec)

执行很快，和存储过程调用差别了很大。而且存储过程中也没有其他特别的逻辑，没有循序，没有等待，不应该这么慢才对，只有可能耗时的地方就是delete语句，

存储过程中delete和外部单独执行的delete语句，看上去完全一致，但是我们还需要判断，where条件的类型是否一致，会不会是类型不一致导致的执行慢？

然后对比存储存储过程和表结构，where条件给定的类型也是一致的。那还会有什么原因导致执行很慢呢，还有一种情况没考虑到：字符集，如何表的字符集和where条件中值字符集不一致，

也是有可能导致索引失效的，进行导致执行变慢。这里就需要理解一下调用存储过程是如何使用字符集的，在官方介绍中有如下说明：

For character data types, if there is a CHARACTER SET attribute in the declaration, the specified

character set and its default collation is used. If the COLLATE attribute is also present, that collation is

used rather than the default collation.

If CHARACTER SET and COLLATE attributes are not present, the database character set and collation in

effect at routine creation time are used. To avoid having the server use the database character set and

collation, provide explicit CHARACTER SET and COLLATE attributes for character data parameters.

If you change the database default character set or collation, stored routines that use the database

defaults must be dropped and recreated so that they use the new defaults.

The database character set and collation are given by the value of the character_set_database

and collation_database system variables. For more information, see Section 10.1.3.2, “Database

Character Set and Collation”.

如果存储过程中定义参数时，没有指定字符集，会默认读取创建存储过程时的全局变量character_set_server，如果后续变更了字符集，存储过程不会自动变更字符集，

需要删除重新创建存储过程，才能使得新的字符集生效。

由于创建存储过程时，没有指定字符集，因此采用的character_set_server指定的字符集 utf8mb4，那就意味着存储过程传参都是采用utf8mb4，那么存储过程中delete语句中value的字符集

就是utf8mb4,那再看看表的字符集，) ENGINE=InnoDB DEFAULT CHARSET=utf8; 是utf8的,是不兼容utfbmb4的，字符集不同从而导致索引失效，进而导致delete很慢，影响整个存储过程的执行效率。

解决办法很简单,两种方案均可：

1.调整character_set_server 为 utf8，然后重建存储过程即可，不重建的话，不会起作用。存储过程的字符集是以存储过程创建时character_set_server 为准。

2.将表的字符集更改为utf8mb4也可以。

两种方案选择代价较小的进行即可，我们这里由于是线上环境，更改字符集需要重启服务，因此选择转化表的字符集。

反过来，如果存储过程的字符集是utf8,而表的字符集是utf8mb4，那么是不会出现这个问题的，utf8mb4是兼容utf8的，这里需要注意一下。