MySQL存储过程优化

1. 现象

某天,研发同事找到我说一个存储过程执行比较慢,拿到对应的参数执行需要400ms,查看了存储过程的包体是几个判断加查询的 SQL.单独使用包体中的 SQL 查询都不慢.

2. 问题复现

找了一个相同版本的 MySQL 服务端,创建了字符集是 UTF8MB4 的库.

mysql> show create database d1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------+

创建表并在 mail 列创建索引,字符集是 GBK 并插入100万随机数据.

CREATE TABLE `t_email` (
  `mail` varchar(255) DEFAULT NULL,
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  KEY `idx_mail` (`mail`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

创建一个只查询语句的存储过程.

mysql> DELIMITER //
mysql> CREATE PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in  _recipient varchar(96))
    -> begin
    -> set _result = 0; 
    -> if _result = 0 then 
    -> select count(*) into _result from t_email where mail = _recipient limit 1; end if; 
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

执行存储,发现确实不快,需要270ms.

mysql> call mail_attrifilter_lxy(@_result,'315761111184573@qq.com');
Query OK, 1 row affected (0.27 sec)

3.原因剖析

单独拎出来 SQL 查询,强制忽略索引,查询260ms,正常执行 SQL,执行时间很快,由此可以猜测存储过程的执行中没有使用到索引,而是使用了全表扫描.

mysql> select count(*) from t_email IGNORE INDEX(idx_mail)  where mail = '315761111184573@qq.com' limit 1;

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.26 sec)
mysql> select count(*) from t_email where mail = '315761111184573@qq.com' limit 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

检查存储过程ddl过程时看到排序规则是 UTF8MB4_GENERAL_CI,而表的字符集是 GBK. GBK 的默认排序方式是 GBK_CHINESE_CI, 而存储过程的排序方式 UTF8MB4_GENERAL_CI,自然无法使用索引过滤,只能全表扫描.

Create Procedure: CREATE DEFINER=`app`@`%` PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in  _recipient varchar(96))
begin
set _result = 0; 
if _result = 0 then 
select count(*) into _result from t_email USE INDEX (idx_mail) where mail = _recipient limit 1; end if; 
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_general_ci

使用 SHOW CHARACTER SET; 查看支持的字符集及对应的排序规则.也验证了 GBK 无法使用 UTF8MB4_GENERAL_CI 排序.

mysql> SHOW COLLATION WHERE Charset in('utf8mb4','gbk');
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| gbk_chinese_ci         | gbk     |  28 | Yes     | Yes      |       1 |
| gbk_bin                | gbk     |  87 |         | Yes      |       1 |
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
+------------------------+---------+-----+---------+----------+---------+

4. 解决方法

既然是排序方式的问题,下一步要确认为什么使用了不正确的排序方式及如何修正.

If CHARACTER SET and COLLATE 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 an explicit CHARACTER SET and a COLLATE attribute for character data parameters.
If you alter the database default character set or collation, stored routines that are to use the new database defaults must be dropped and recreated.

官方描述:如果创建存储过程时没有显式声明字符集和排序规则,就使用数据库级别的.如果修改了数据库的规则,存储过程不会自动变更,要删除并重建存储过程才能使用数据库默认的规则.
根据官方文档描述,解决方法有两个:一个是修改数据库后重建存储过程,另一个是在显式定义输入参数的字符集及排序规则.

4.1 解决方法一

首先将数据库的字符集修改为 GBK, 排序规则会被隐式修改.

mysql> alter database d1 charset gbk;
Query OK, 1 row affected (0.01 sec)

删除存储过程并重建.

mysql> drop PROCEDURE mail_attrifilter_lxy;
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in _recipient varchar(96))
    -> begin
    -> set _result = 0; 
    -> 
    -> if _result = 0 then 
    -> select count(*) into _result from t_email where mail = _recipient limit 1; end if; 
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

查看存储过程的定义,数据库的排序规则是 GBK_CHINESE_CI, 存储过程继承数据库级别的排序规则, 存储过程的排序规则也是 GBK_CHINESE_CI.

mysql> show create PROCEDURE `mail_attrifilter_lxy`\G
*************************** 1. row ***************************
           Procedure: mail_attrifilter_lxy
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`app`@`%` PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in  _recipient varchar(96))
begin
set _result = 0; 
if _result = 0 then 
select count(*) into _result from t_email USE INDEX (idx_mail) where mail = _recipient limit 1; end if; 
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: gbk_chinese_ci

使用相同的参数调用存储过程,时间变成10毫秒,与业务需求相匹配.

mysql> call mail_attrifilter_lxy(@_result,'315761111184573@qq.com');
Query OK, 1 row affected (0.01 sec)

4.2 解决方法二

显式指定输入形参的字符集和排序规则,可以屏蔽数据库级别的参数,不会受到其他因素影响,从根本解决问题.
将变量 _RECIPIENT 的字符集定义为 GBK, 将排序规则定义为 GBK_CHINESE_CI.

mysql> DELIMITER //
mysql> CREATE PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in  _recipient varchar(96) CHARACTER SET gbk COLLATE gbk_chinese_ci)
    -> begin
    -> set _result = 0; 
    -> if _result = 0 then 
    -> select count(*) into _result from t_email where mail = _recipient limit 1; end if; 
    -> END //
mysql> DELIMITER ;

数据库级别的排序规则没有变化,参数列表位置的输入参数已经被限定.

mysql> show CREATE PROCEDURE `mail_attrifilter_lxy`\G
*************************** 1. row ***************************
           Procedure: mail_attrifilter_lxy
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`app`@`%` PROCEDURE `mail_attrifilter_lxy`(out _result tinyint, in  _recipient varchar(96) CHARACTER SET gbk COLLATE gbk_chinese_ci)
begin
set _result = 0; 
if _result = 0 then 
select count(*) into _result from t_email where mail = _recipient limit 1; end if; 
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_general_ci

执行存储过程优化效果明显,同样可以符合业务预期.

mysql> call mail_attrifilter_lxy(@_result,'315761111184573@qq.com');
Query OK, 1 row affected (0.00 sec)

5. 总结

如果数据库中表的字符集比较比较统一,将数据库的字符集设置为表相同的字符集.如果没有办法设置,使用存储过程时,将参数列表固定字符集和排序规则.