互联网服务MySQL
mysql的存储过程占用cpu较高？
性能测试过程中，4各用户并发，数据库cpu资源占用100%，通过定位这个存储过程执行时间较长，数据库配置：4C/16G
Time: 2018-09-26T02:47:16.400412Z
User@Host: root[root] @ [10.50.180.99] Id: 2308
Query_time: 18.235699 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3980838
SET timestamp=1537930036;
CALL save_cust_loan(8879,@com_mysql_jdbc_outparam_SUCESS_STATUS);
Time: 2018-09-26T02:47:22.194205Z
User@Host: root[root] @ [10.50.180.99] Id: 2387
Query_time: 17.662239 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3981838
SET timestamp=1537930042;
CALL save_cust_loan(8881,@com_mysql_jdbc_outparam_SUCESS_STATUS);
Time: 2018-09-26T02:47:26.087044Z
User@Host: root[root] @ [10.50.180.99] Id: 2312
Query_time: 16.979817 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3982838
SET timestamp=1537930046;
CALL save_cust_loan(8883,@com_mysql_jdbc_outparam_SUCESS_STATUS);
Time: 2018-09-26T02:47:30.901209Z
User@Host: root[root] @ [10.50.180.99] Id: 2307
Query_time: 17.160906 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3983838
SET timestamp=1537930050;
CALL save_cust_loan(8885,@com_mysql_jdbc_outparam_SUCESS_STATUS);
Time: 2018-09-26T02:47:32.411461Z
User@Host: root[root] @ [10.50.180.99] Id: 22
Query_time: 16.698509 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3984838
SET timestamp=1537930052;
CALL save_cust_loan(8887,@com_mysql_jdbc_outparam_SUCESS_STATUS);
Time: 2018-09-26T02:47:33.455415Z
User@Host: root[root] @ [10.50.180.99] Id: 2308
Query_time: 16.817289 Lock_time: 0.000000 Rows_sent: 500 Rows_examined: 3985838
SET timestamp=1537930053;
CALL save_cust_loan(8889,@com_mysql_jdbc_outparam_SUCESS_STATUS);
表结构
ul7zoo9abjo
0a8q37mvdecd
存储过程
DELIMITER $$
USE `tcas`$$
DROP PROCEDURE IF EXISTS `save_cust_loan`$$
CREATE DEFINER=`root`@`%` PROCEDURE `save_cust_loan`(IN BATCH_NO_IN INT(11),OUT SUCESS_STATUS VARCHAR(2))
BEGIN
DECLARE B_BATCH_NO VARCHAR(20);
DECLARE B_CUST_NAME VARCHAR(200);
DECLARE B_MOBILE VARCHAR(20);
DECLARE B_IDENTITY_NO VARCHAR(20);
DECLARE B_SEX VARCHAR(1);
DECLARE B_MONTH_INCOME VARCHAR(20);
DECLARE B_PROVINCE VARCHAR(50);
DECLARE B_CITY VARCHAR(50);
DECLARE B_ADDRESS VARCHAR(200);
DECLARE B_BIRTHDAY VARCHAR(10);
DECLARE C_CAR_BRAND VARCHAR(20);
DECLARE C_CAR_MODEL VARCHAR(50);
DECLARE C_CAR_SERIES VARCHAR(50);
DECLARE C_DRIVING_LICENSE VARCHAR(10);
DECLARE C_PLAN_CITY VARCHAR(50);
DECLARE L_APPLY_AMOUNT DECIMAL(18, 2);
DECLARE L_APPLY_PERIOD INT(11);
DECLARE L_WAGE_GRANT_TYPE VARCHAR(10);
DECLARE L_CAR_TYPE VARCHAR(20);
DECLARE L_HOUSE_TYPE VARCHAR(20);
DECLARE L_LIFE_INSURANCE VARCHAR(10);
DECLARE L_SOCIAL_SECURITY VARCHAR(20);
DECLARE L_ACCUMULATION_FUND VARCHAR(20);
DECLARE L_CREDIT_SITUATION VARCHAR(20);
DECLARE L_CREDIT_NUM VARCHAR(10);
DECLARE M_EXIST_PRODUCT VARCHAR(50);
DECLARE M_NEARLY_THREE_MONTHS_FLOW VARCHAR(50);
DECLARE M_MONTHLY_KNOT_DAY VARCHAR(2);
DECLARE W_INVEST_HISTORY VARCHAR(10);
DECLARE W_BANK_DEPOSIT DECIMAL(18, 2);
DECLARE W_FAMILY_MEMBER INT(11);
DECLARE B_CREATE_TIME TIMESTAMP(0);
DECLARE B_CREATE_USER_ID VARCHAR(20);
DECLARE B_BUSINESS_TYPE VARCHAR(2);
DECLARE B_DATA_SOURCE VARCHAR(50);
DECLARE B_STATUS VARCHAR(2);
DECLARE B_ERROR_MSG VARCHAR(2000);
DECLARE K_CUST_ID INT(11);
DECLARE K_COUNT INT(11);
DECLARE done INT DEFAULT 0;
DECLARE cust_data CURSOR FOR
SELECT BATCH_NO, CUST_NAME, MOBILE, IDENTITY_NO, SEX,
MONTH_INCOME, PROVINCE, CITY, ADDRESS, BIRTHDAY, CAR_BRAND,
CAR_MODEL, CAR_SERIES, DRIVING_LICENSE, PLAN_CITY, APPLY_AMOUNT,
APPLY_PERIOD, WAGE_GRANT_TYPE, CAR_TYPE, HOUSE_TYPE, LIFE_INSURANCE,
SOCIAL_SECURITY, ACCUMULATION_FUND, CREDIT_SITUATION, CREDIT_NUM, EXIST_PRODUCT,
NEARLY_THREE_MONTHS_FLOW, MONTHLY_KNOT_DAY,INVEST_HISTORY, BANK_DEPOSIT, FAMILY_MEMBER,
CREATE_TIME, CREATE_USER_ID, BUSINESS_TYPE, DATA_SOURCE, STATUS, ERROR_MSG
FROM tm_t_cust_tmp WHERE BATCH_NO=BATCH_NO_IN;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cust_data;
read_loop: LOOP
FETCH cust_data INTO B_BATCH_NO, B_CUST_NAME, B_MOBILE, B_IDENTITY_NO, B_SEX,
B_MONTH_INCOME, B_PROVINCE, B_CITY, B_ADDRESS, B_BIRTHDAY, C_CAR_BRAND,
C_CAR_MODEL, C_CAR_SERIES, C_DRIVING_LICENSE, C_PLAN_CITY, L_APPLY_AMOUNT,
L_APPLY_PERIOD, L_WAGE_GRANT_TYPE, L_CAR_TYPE, L_HOUSE_TYPE, L_LIFE_INSURANCE,
L_SOCIAL_SECURITY, L_ACCUMULATION_FUND, L_CREDIT_SITUATION, L_CREDIT_NUM, M_EXIST_PRODUCT,
M_NEARLY_THREE_MONTHS_FLOW, M_MONTHLY_KNOT_DAY,W_INVEST_HISTORY, W_BANK_DEPOSIT, W_FAMILY_MEMBER,
B_CREATE_TIME, B_CREATE_USER_ID, B_BUSINESS_TYPE, B_DATA_SOURCE, B_STATUS, B_ERROR_MSG;
IF done=1 THEN
LEAVE read_loop;
END IF;
/*校验手机号或身份证号重复*/
IF B_MOBILE IS NOT NULL
THEN SELECT COUNT(m.id) INTO K_COUNT FROM (SELECT DISTINCT t1.MOBILE AS id FROM tm_t_cust_base t1 WHERE t1.MOBILE = B_MOBILE AND t1.BUSINESS_TYPE =B_BUSINESS_TYPE AND t1.VALIDATE_STATE ='1' AND t1.STATUS='1'
UNION SELECT DISTINCT t1.IDENTITY_NO AS id FROM tm_t_cust_base t1 WHERE t1.IDENTITY_NO = B_IDENTITY_NO AND t1.BUSINESS_TYPE =B_BUSINESS_TYPE AND t1.VALIDATE_STATE ='1' AND t1.STATUS='1') m;
END IF;
IF K_COUNT>0
THEN SET B_ERROR_MSG=CONCAT_WS('',B_ERROR_MSG ,'手机号或身份证号重复；');
SET B_STATUS='3';
END IF;
/*插入客户基础数据*/
INSERT INTO tm_t_cust_base(BATCH_NO, CUST_NAME, MOBILE, IDENTITY_NO, SEX, MONTH_INCOME, PROVINCE, CITY, ADDRESS, BIRTHDAY , CREATE_TIME, CREATE_USER_ID, BUSINESS_TYPE, DATA_SOURCE, STATUS, ERROR_MSG, VALIDATE_STATE)
VALUES (B_BATCH_NO, B_CUST_NAME, B_MOBILE, B_IDENTITY_NO, B_SEX, B_MONTH_INCOME, B_PROVINCE, B_CITY, B_ADDRESS, B_BIRTHDAY , B_CREATE_TIME, B_CREATE_USER_ID, B_BUSINESS_TYPE, B_DATA_SOURCE, B_STATUS, B_ERROR_MSG, '1');
/*获取基础表主键ID*/
SET K_CUST_ID=LAST_INSERT_ID();
SELECT K_CUST_ID;
/*插入贷款数据*/
INSERT INTO tm_t_cust_loan(CUST_ID, APPLY_AMOUNT, APPLY_PERIOD, WAGE_GRANT_TYPE, CAR_TYPE, HOUSE_TYPE, LIFE_INSURANCE, SOCIAL_SECURITY, ACCUMULATION_FUND, CREDIT_SITUATION, CREDIT_NUM, CREATE_TIME, CREATE_USER_ID, DATA_SOURCE, VALIDATE_STATE)
VALUES (K_CUST_ID, L_APPLY_AMOUNT, L_APPLY_PERIOD, L_WAGE_GRANT_TYPE, L_CAR_TYPE, L_HOUSE_TYPE, L_LIFE_INSURANCE, L_SOCIAL_SECURITY, L_ACCUMULATION_FUND, L_CREDIT_SITUATION, L_CREDIT_NUM, B_CREATE_TIME, B_CREATE_USER_ID, B_DATA_SOURCE, '1');
SET done = 0;
END LOOP;
CLOSE cust_data;
SET SUCESS_STATUS='1';
END$$
DELIMITER ;1同行回答
看一下慢查询日志，具体慢在哪里，或者自己把语句拿出来执行
根据已知信息，看到以下几个问题
1、隐式转换索引失效
BATCH_NO是varchar字符串
BATCH_NO_IN是int数字
MySQL隐式转换导致索引失效，这里要把BATCH_NO_IN也改成varchar(20)
2、tm_t_cust_base索引信息不明（猜测）
看不到tm_t_cust_base的信息，确认tm_t_cust_base的
MOBILE和BUSINESS_TYPE是否有联合索引，数据类型是否一致
IDENTITY_NO和BUSINESS_TYPE是否有联合索引，数据类型是否一致
3、统计和去重的逻辑改写
count()和distinct非常消耗资源，尤其是后者且没有合适索引的时候
看到你的语句（先不看IDENTITY_NO，逻辑一样的），只要MOBILE、业务类型、有效性、状态匹配，会返回去重的MOBILE，然后统计数量，只要大于0就返回错误信息。
从逻辑上来看，因为大于0就返回错误信息（或者说K_COUNT=0才执行），所以可以不用去重，也不需要统计，也不需要扫描所有数据。简单来说，只要子查询m返回了任何数据，那么K_COUNT都大于0。只有m子查询不返回任何数据，才会跳过下个判断。
你可以进行这样的改写（去掉统计和去重，仅用1来代替返回）
THEN SELECT 1 INTO K_COUNT FROM (SELECT 1 FROM tm_t_cust_base t1 WHERE t1.MOBILE = B_MOBILE AND t1.BUSINESS_TYPE =B_BUSINESS_TYPE AND t1.VALIDATE_STATE ='1' AND t1.STATUS='1' limit 1
UNION SELECT 1 FROM tm_t_cust_base t1 WHERE t1.IDENTITY_NO = B_IDENTITY_NO AND t1.BUSINESS_TYPE =B_BUSINESS_TYPE AND t1.VALIDATE_STATE ='1' AND t1.STATUS='1' limit 1) m;