数据库字符集不一样导致函数或存储过程中使用的索引失效

墨蓝 2021-09-25 20:46 273阅读 0赞

**一、问题情况说明：**

公司原来的MYSQL服务器版本为5.5，现在要升级为5.6。

由于数据量不大，我们决定直接导出数据到新建的5.6版本的数据库中。

原来5.5版数据库字符集是 utf8

迁移5.6版数据库字符集是 utf8mb4

数据迁移后，第二天发现有大量的查询SQL占用CUP资源，

排查这些SQL来源于开发人员创建的自定义函数。

这些函数内容比较简单（类似与根据 ID 获取一些信息），

理想中都应该走索引的，基本上不消耗多长时间就可查询出结果。

但目前执行这些函数非常耗时？？？?  
  
**二、先说下最终发现问题的根本原因：**

MYSQL针对函数或存储过程中传递进的参数,如果是varchar类型

时则默认会进行转换字符集校对规则与数据库保持一致。  
  
 例如以下函数：  
 CREATE FUNCTION func\_get\_msisdn(v\_user\_id VARCHAR(32))  
 RETURNS varchar(15)  
 BEGIN  
 DECLARE tmpMsisdn VARCHAR(15);  
 select msisdn INTO tmpMsisdn   
   from t\_app\_users a   
  where a.user\_id = v\_user\_id;  
 RETURN tmpMsisdn;  
 END;

执行的时候，使用 SHOW FULL PROCESSLIST;命令查询执行中的SQL如下：

select msisdn   
           from t\_app\_users a  
          where a.user\_id = NAME\_CONST('v\_user\_id',  
 \_utf8mb4'6e377e230e8c11e69e7e52541fc51092'  
 COLLATE 'utf8mb4\_general\_ci');

注意到上面的“utf8mb4\_general\_ci”字符集校对规则，

用 SHOW FULL COLUMNS FROM t\_app\_users 查看该表的user\_id字段的字符集校对规则。

结果发现是user\_id字段字符集校对规则是utf8\_general\_ci。

\---------------------------------------------------------------------------------------

**Field------Type-----------Collation  ....**

....\#\# 省略

USER\_ID----varchar(32)----- utf8\_general\_ci....

....\#\# 省略  
\---------------------------------------------------------------------------------------

当函数或存储过程中参数与查询关联的字段 字符集校对规则不一样时，将

导致该字段上的索引失效！！！

问题的原因在于当初从5.5版本DUMP数据时没有指定字符集！

导出数据时切记加上指定字符集参数：

mysqldump --default-character-set='utf8mb4' ....

**三、以下记录下问题排查的过程，不断学习**

1. 一开始根本没有怀疑索引失效，采用  show profiles; 命令查看执行的状态。

（1）SET profiling = 1;

（2）SELECT func\_get\_msisdn(a.USER\_ID) msisdn

FROM t\_user\_recharge a WHERE a.id = 178213;

（3）SHOW profiles; 展示的主要信息如下：

\------------------------------------------------------------------------------------------------------------------------------  
182------0.001491---------------SHOW STATUS  
183------0.00100775-----------SHOW STATUS  
184-----\-4.846844--------------select msisdn INTO tmpMsisdn   
                                          from t\_app\_users\_info a where a.user\_id = v\_user\_id    
            \#\# 该SQL为 函数 func\_get\_msisdn 中的语句 \#\#   
\------------------------------------------------------------------------------------------------------------------------------

（4）SHOW profile ALL FOR QUERY 184;

\#\# 或查询系统信息表 information\_schema.PROFILING 进行查询

发现状态“status” 为“Sending data”最耗时和性能！！！其他步骤都很好。

\------------------------------------------------------------------------------------------------------------------------------

**Status----------Duration-----------CPU\_user------CPU\_system....**

.... \#\# 省略

Sending data-----4.846844----------------3.834224 \-----------0.04....

.... \#\# 省略  
\------------------------------------------------------------------------------------------------------------------------------

（5）感到困惑的时，函数中的SQL 拿出来在查询分析器中执行 几乎秒出数据，

而且查询执行计划也是使用上索引了。问题出在哪？

（6）采用 SHOW STATUS 或借助mysql可视化开发工具即可查看以上SQL的执行状态。

最终发现参数 “Handler\_read\_rnd\_next” 几乎和表的总记录一样！！！

“Handler\_read\_rnd\_next” 官方文档解释：

Number of requests to read the next row in the data file.

This value is high if you are doing a lot of table scans.

在数据文件中读下一行的请求数。如果你正进行大量的表扫描，

该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

（7） 将表索引去除后查询看看，发现和函数执行的耗时一样。

总总迹象表明确实是没用上索引或是说索引几乎无效。

（8） 后来在 www.baidu.com 上找寻答案，总之找了很久无法解答这个问题。

再后来 有同事在国外网站上找到一个相关的论坛，里面描述的和我们

遇到的问题情况很相似。以下是论坛主要描述内容：

This could be a character set issue.   
  If the function is using a different character set than the table column,   
  it would lead to very slow performance despite the index.  
  Run show create table products\\G to determine the character set for the column.  
  Run show variables like 'character\_set%';   
  to see what the relevant default character sets are for your DB.

（9）最后发现 确实是 字符集校对规则不一样时，将导致该字段上的索引失效！！！

问题原因在上面第二点已经述说过了。 这里感谢同事们一起努力帮助解决问题。