线上数据库用户反映数据提交很慢,前端遮罩屏蔽处理一个在转,于是看了应用的log日志文件,出现几百kb的文件错误,仔细看应用在调用一个存储过程的抛出了
Deadlock found when trying to get lock
这个排除了应用的问题,于是着手到数据层面找原因。
1.检查数据db引擎的状态status
通过登录mysql服务器使用使用如下命令将db status重定向输出到指定的文件分析
mysql -e "show engine innodb status\G" -u[name] -p[password] [database] > /usr/local/status.txt
查看status.txt文件中的发现果然是存储过程的问题,文件sql语句让我很吃惊,什么鬼怎么后面会自己使用NAME_CONST然后还把编码转成utf8mb4。
ts.user_id = NAME_CONST('userId',_utf8mb4'5ba51b22' COLLATE 'utf8mb4_general_ci')
然后我把语句拷贝加上explain查看执行计划,发现走了全表扫描,没有走user_id索引,如果NAME_CONST中的内容去掉,直接赋值id值,发现执行计划预估值只有20几行。应该是很快的。
这是开始检查user_id字段子表中的编码,发现是utf8并不是utf8mb4,通过show variables查看数据库编码也是utf8。
//toggle
mysql> use xxx;
//character_set
mysql>show variables like 'character_set_database';
为什么会出现强制改编码呢。其实也没遇到过这样的问题。也没有经验,这个时候不知道咋办了,只好打开google输入mysql procedure utf8mb4一篇快速浏览没有发现线索,点了第二编也是快速阅览,大概是来自stackoverflow,一个show procedure status突然吸引到我,虽然提问者问题和我的问题并不一样。我很好奇
这个命令到底展现啥。然后看到内容大致如下:
我的character_set_client和collation_connection的编码都是utf8而Database collation的编码是utf8mb4
//来自mysql官网
mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: test
Name: sp1
Type: PROCEDURE
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
我突然想起了之前为了兼容表情把数据编码改成了utf8mb4,但是后来又把数据库改回了utf8,这时我的脑海里突然觉得存储过程的Database Collation的编码依赖于创建时数据库的编码和当前的编码无关,于是快速的重新创建一个存储过程覆盖原来的存储过程,再用show procedure status like 'xxx' \G查看,发现编码已经修改。
结论:在改变数据库编码后需要使用show xxx status命令检查之前创建的表、存储过程、触发器、函数的编码是否与当前一致,否则做相应的编码修改。对dead lock一般都是由于数据变更数据时没有走索引导致innodb引擎出现锁整个表记录而出现了资源争用