一次由于字符集不统一导致的存储过程不使用索引的cook
1、业务反馈登陆失败，初步查询数据库登陆不进去，赶紧进去数据库查看，发现果然线程已经卡住了不少了，并且是同一条sql
show processlist;
state出现的状态为 searching rows for update
2、查看慢日志，出现大量同一访问存储过程卡住的情况
执行时间到40s左右
3、利用information_schema下面和锁有关的三个表查询：
SELECT r.trx_state wating_trx_state,r.trx_id waiting_trx_id,r.trx_mysql_thread_Id waiting_thread,r.trx_query waiting_query,b.trx_state blocking_trx_state,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
出现200多组锁等待，而且都是同类型的等待
4、表的大小为80w行数据左右，检查表的索引情况，存在检索字段的索引，如果走索引了，不应该存在这种情况
explain select * from test1 where column_a = 'a' and column_b = 'b'; 结果走索引了已经；这就纳闷了
由于之前遇到过存储过程里面和外面执行语句效率不一样，怀疑是否是存储过程里面的原因
提取出存储语句，进行稍微的更改，利用存储过程返回的结果，查看sql执行效率
CREATE DEFINER=`root`@`localhost` PROCEDURE `ptest1`( i_usermobile VARCHAR (11), OUT o_return INT) label_pro : BEGIN DECLARE p_curym INT ; declare p_curymd int; DECLARE p_count INT; DECLARE CONTINUE HANDLER FOR 1062 SET o_return = -2 ; SET p_curym = DATE_FORMAT(NOW(), '%Y%m') ; SET p_curymd = DATE_FORMAT(NOW(), '%Y%m%d') ; SET p_count = 0; SET o_return = 0; explain select * from test1 where mobile = i_usermobile AND curmonth = p_curym; commit; end
查看到返回的结果
mysql> CALL ptest2('13654390105',@com_mysql_jdbc_outparam_o_return); +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 99847 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec)
根本没有走索引，所以
###问题的根本还是单条sql引起的性能问题，需要从存储过程下手，开始没能想到字符集的问题，后面没办法向老大求救
5、查看建表语句，字符集为gbk，
*** ENGINE=InnoDB AUTO_INCREMENT=118297 DEFAULT CHARSET=gbk COMMENT='抽奖机会记录表'
查看存储过程字符集
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
然后尝试使用，将索引使用的行改成utf8字符集
ALTER TABLE test1 mobile VARCHAR(13) CHARACTER SET utf8;
再次进行flag存储过程的调用
mysql> CALL ptest1('13654390105',@com_mysql_jdbc_outparam_o_return); +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | test1 | NULL | const | PK_Y_CN201612A1_DRAWCE | PK_Y_CN201612A1_DRAWCE | 46 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
如上图所示，开始使用索引了
6、在现网进行更改行的属性之后，主机负载下降到正常水平。由于处理故障的时候没想到比较特殊的一次经历，也没有保留好现场，靠着回忆和重现完成该笔记了。
7、扩展：
猜想1、如果表字符集和存储过程的字符集统一，是否能够正常使用索引
mysql> show create procedure ptest2\G *************************** 1. row *************************** Procedure: ptest2 sql_mode: 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=`root`@`localhost` PROCEDURE `ptest2`( i_usermobile VARCHAR (11), OUT o_return INT) label_pro : BEGIN DECLARE p_curym INT ; declare p_curymd int; DECLARE p_count INT; DECLARE CONTINUE HANDLER FOR 1062 SET o_return = -2 ; SET p_curym = DATE_FORMAT(NOW(), '%Y%m') ; SET p_curymd = DATE_FORMAT(NOW(), '%Y%m%d') ; SET p_count = 0; SET o_return = 0; explain select * from test2 where mobile = i_usermobile AND curmonth = p_curym; commit; end character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) mysql> show create table test2\G *************************** 1. row *************************** Table: test2 Create Table: CREATE TABLE `test2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mobile` varchar(13) DEFAULT NULL, `total` int(1) NOT NULL DEFAULT '0' COMMENT '抽奖机会总数', `used` int(1) NOT NULL DEFAULT '0' COMMENT '使用的抽奖机会', `curmonth` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `PK_Y_CN201612A1_DRAWCE` (`mobile`,`curmonth`) ) ENGINE=InnoDB AUTO_INCREMENT=118297 DEFAULT CHARSET=gbk COMMENT='抽奖机会记录表' 1 row in set (0.00 sec)
除却所在库的字符集为utf8之外，其余皆是gbk，进行实验
mysql> CALL ptest2('13654390105',@com_mysql_jdbc_outparam_o_return); -> $$ +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 99847 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
结果让人失望，存储过程并没有使用索引
猜想2、进行更加规范的统一，将存储过程所在库的字符集也改为gbk
mysql> CALL ptest2('13654390105',@com_mysql_jdbc_outparam_o_return); -> $$ +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | test2 | NULL | const | PK_Y_CN201612A1_DRAWCE | PK_Y_CN201612A1_DRAWCE | 46 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
成功了，看来存储过程内部使用索引还和数据库的字符集有一定关联
猜想3、在猜想2的基础上将索引字段的字符集改为utf8
mysql> show full columns from test2; -> $$ +----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | mobile | varchar(13) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | | | total | int(1) | NULL | NO | | 0 | | select,insert,update,references | 抽奖机会总数 | | used | int(1) | NULL | NO | | 0 | | select,insert,update,references | 使用的抽奖机会 | | curmonth | int(11) | NULL | NO | | NULL | | select,insert,update,references | | +----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+ 5 rows in set (0.01 sec) mysql> CALL ptest2('13654390105',@com_mysql_jdbc_outparam_o_return);$$ +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | test2 | NULL | const | PK_Y_CN201612A1_DRAWCE | PK_Y_CN201612A1_DRAWCE | 46 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+------------------------+------------------------+---------+-------------+------+----------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
gbk能够兼容utf8的字符集，反过来utf8不能兼容gbk字符集，所以建议建表的ddl还是放在utf8字符集下面处理。
