MySQL8.0.16存储过程比5.7.22性能大幅下降

MySQL8.0.16存储过程比5.7.22性能大幅下降

1、背景

从5.7.22迁移数据库到8.0.16，发现存储过程执行性能大幅下降。原来在5版本上执行只需要3-5秒，到8版本上居然要达到上万秒。
5版本：

call Calculation_Week()
OK
时间: 3.122s

8版本：

call Calc_Week_increment();
OK
时间: 13489.506s

存储过程就是按周统计数据，有select、insert、update ，没有特别复杂的查询。

时间大部分花在了Sending data

2、排查过程

（1）检查优化配置参数

5.7版本中有query_cache参数，但是在8版本中，这个参数取消了。
当某一个客户端连接（session）进行SQL查询并得到返回信息时，MySQL数据库除了将查询结果返回给客户端外，还在特定的内存区域缓存这条SQL查询语句的结果，以便包括这个客户端在内的所有客户的再次执行相同查询请求时，MySQL能够直接从缓存区返回结果。
于是把能考虑的所有和查询、写入的参数，都做了调优设置：

max_connections = 1000
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=2Ginnodb_log_file_size=256M
innodb_max_dirty_pages_pct=50
thread_cache_size=128Minnodb_flush_log_at_trx_commit = 2
sync_binlog = 100000#performance setttings
lock_wait_timeout = 3600
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32Mmax_allowed_packet = 512M
wait_timeout = 200000
interactive_timeout = 200000connect_timeout = 6000

结果：没有用！

（2）检查索引效率

MysqL数据库查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性,所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据” 。
A.先对比了5和8两个数据库的表上的索引，都完全一样，数据量在2000万。
B.把存储过程上使用的SQL语句都拿出来，在5和8上逐一做explain ，结果一样，没有差别。
C.修改存储过程，把存储过程上用到的每个SQL语句，都写入一个日志表，记录执行时间。

把对应的SQL在命令行中执行都很快，0.035秒左右，但是在存储过程中，居然5秒钟左右。

（3）考虑事务处理

检查事务处理：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

把自动提交处理关闭，然后在存储过程中，使用事务处理：

set autocommit = 0;

在程序中明确事务处理：

start transaction;
--存储过程程序
commit;

结果：没有任何作用！

（4）对比数据库结构

坚信8版本的性能应该比5版本的性能高，问题肯定出在环境上。

（a）8.0.16版本

数据库：

mysql> show create database pymysql;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                                                        |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| pymysql | CREATE DATABASE `pymysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表结构：show create table pytable;

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

存储过程：show create procedure Calc_Week_increment;

  
END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |

（b）5.7.22版本

数据库：

mysql> show create database pymysql;
+--------------+-----------------------------------------------------------------------+
| Database     | Create Database                                                       |
+--------------+-----------------------------------------------------------------------+
| pymysql | CREATE DATABASE `pymysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> 

表结构：show create table pytable;

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

存储过程：show create procedure Calc_Week_increment;

END | utf8mb4              | utf8mb4_general_ci   | utf8_general_ci    |

（c）对比结果

8版本数据库的字符集是utf8mb4，5版本数据库的字符集utf8 。
表结构和存储过程的字符集都一样。

8版本数据库字符集

mysql> show variables like '%char%';
+--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| character_set_client                 | utf8mb4                        |
| character_set_connection             | utf8mb4                        |
| character_set_database               | utf8mb4                        |
| character_set_filesystem             | binary                         |
| character_set_results                | utf8mb4                        |
| character_set_server                 | utf8mb4                        |
| character_set_system                 | utf8                           |
| character_sets_dir                   | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1                              |
+--------------------------------------+--------------------------------+
9 rows in set (0.01 sec)

5版本字符集：

mysql> show variables like '%char%'-> ;
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在 utf8mb4 是 utf8 的超集，除了将编码改为 utf8mb4 外不需要做其他转换。当然，为了节省空间，一般情况下使用 utf8 也就够了。

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8 字符，可能是因为 Mysql 刚开始开发那会，Unicode 还没有4字节的字符。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，应该是为了向后兼容性的考虑，还有就是4字节字符确实很少用到。

3、解决验证

考虑数据库字符集不同，造成的性能问题。在8版本下重新建数据库，指定字符集utf8：

CREATE DATABASE  `charsettest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

然后重新导入数据库。
在5下执行存储过程。

call Calculation_Week()
OK
时间: 3.122s

在8下执行存储过程，一切顺利丝滑。

call Calculation_Week()
OK
时间: 1.53s

性能提升一倍。
原因：数据库字符集和表的字符集不一致造成。但是奇怪的是在命令行执行很快，explain也没有异常，仅仅是在存储过程中执行，有问题。

此问题困扰了一周的时间，终于解决了！！！

根本原因是什么？

待查，找到了后续更新！