表字符集与存储过程字符集不一致导致的存储过程执行缓慢

昨天遇到个线上问题，某个存储过程执行异常缓慢，在数据量

不到1K条的情况下竟然需要4min才能全部完成。

查看了存储过程的具体操作，全部是删除逻辑，涉及大概十几张表

删除操作。

大致如下（项目代码不便透露，这里意思一下即可）

CREATE  PROCEDURE deleteinfo(IN `aaax` varchar(50),IN `bbbx` varchar(50))
BEGIN
  DECLARE aaa varchar(255); 
  DECLARE bbb   varchar(255); 
    
  SET aaa = aaax;
  SET bbb   = bbbx;

 Delete from table1
   where aaa = aaax
     and bbb = bbbx;

  Delete from table2
   where aaa = aaax
     and bbb = bbbx;

  Delete from Ptable3
   where aaa = aaax
     and bbb = bbbx;

END

 

经过确认，这几张表内的全部数据不过1K左右，但是执行时间却异常缓慢，

于是我决定手动执行验证下时间，发现不过10s内就全部完毕，那么我猜测

存储过程中一定发生了什么。

经过进一步的排查，我们将目光放到了索引上，这几张表的索引都是没问题的，

会不会存储过程中没有利用到索引呢？

抱着这个疑问我查找了下索引失效的可能，最终发现，与存储过程和表的字符集有关。

我们执行存储过程时，定义的参数没有指定字符集，则默认取character_set_server这个全局变量的值

  DECLARE aaa varchar(255); 
  DECLARE bbb   varchar(255); 

我们可以通过下面语句来查看

show variables like "%char%";

 

 

 

接下来我们再来看下表的字符集，通过information_schema.columns可以查出

SELECT DISTINCT
            TABLE_SCHEMA,TABLE_NAME,CHARACTER_SET_NAME
        FROM
            information_schema.`COLUMNS`
        WHERE
            TABLE_SCHEMA = (select databases())
        AND CHARACTER_SET_NAME != 'utf8mb4'

 

 

 

 我们发现很多表的字符集是utf-8，而存储过程内部参数确是utf-8mb4的，在这种情况下，

mysql会将表转换为utf-8mb4（mylsq字符集转换遵循由小到大的规则，因为utf8mb4是utf8的超集，所以把

utf8转换为utf8mb4），转换后，由于原来表上的索引依然是utf8的，这就导致了这些索引的失效。

 

于是，我们终于找出了这个问题的罪魁祸首，接下来，我们看下怎么处理这个问题，这里有2个方案

（1）修改character_set_server=utf8，然后重建存储过程（存储过程的字符集是在新建时读取的character_set_server因此不重建则无效）

（2）将表的字符集改为utf8mb4

经过考量后决定修改表的字符集（character_set_server修改需重启数据库服务，线上项目改动风险过大），可以通过以下语句实现

alter table table_name convert to charset utf8mb4;

但项目上表较多的时候一张张表修改显然是不现实的，因此这里提供下批量生成语句，

可以直接将非utf8mb4的表查询出来并生成修改语句（修改前建议先备份）

SELECT
    CONCAT(
        "ALTER TABLE `",
        tb.TABLE_SCHEMA,
        "`.`",
        tb.TABLE_NAME,
        "` CONVERT TO CHARACTER SET utf8mb4;"
    )
FROM
    (
        SELECT DISTINCT
            TABLE_SCHEMA,TABLE_NAME
        FROM
            information_schema.`COLUMNS`
        WHERE
            TABLE_SCHEMA = (select database())
        AND CHARACTER_SET_NAME != 'utf8mb4'
and TABLE_NAME 
not in (select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA = (select database()))
    ) as tb;

 

最后，这个问题时由于存储过程字符集utf8mb4,表字符集utf8引起的，但是反过来则不会有这个问题，因为utf8mb4是兼容utf8的。