業(yè)務(wù)反饋線上一個存儲過程執(zhí)行很慢，導(dǎo)致業(yè)務(wù)超時較多，而存儲過程比較簡單，就是一個簡單的判斷邏輯然后delete一條記錄，而且delete語句是有索引的。
表結(jié)構(gòu)如下：
CREATE TABLE `table1` (
? `BaseName` varchar(255) NOT NULL COMMENT '相對路徑名(相對于上層目錄)',
? `Bucket` mediumtext NOT NULL COMMENT '目錄所屬Bucket(業(yè)務(wù)名)',
? `DirUuid` varchar(64) NOT NULL COMMENT '父目錄Uuid',
? `IsDir` tinyint(1) NOT NULL COMMENT '是否是文件夾',
? ?xxx
??PRIMARY KEY (`DirUuid`,`BaseName`),
? KEY `origin` (`DirUuid`,`OriginBaseName`),
? KEY `dir` (`DirUuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
存儲過程如下：
?CREATE DEFINER=`xxx`@`%` PROCEDURE `PDelete`( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? IN `base_name` varchar(255), ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? IN `dir_uuid` varchar(36), ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? IN `IsDir` tinyint(1))
BEGIN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? DECLARE t_error INTEGER DEFAULT 0; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; ? ? ? ? ? ? ? ? ? ?
? ? START TRANSACTION; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? IF IsDir = 1 THEN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? delete from table1 where `DirUuid` = dir_uuid and `BaseName` = base_name;
? ? ? ? ? ? delete from table2 where `DirUuid` = dir_uuid and `Dir` = base_name;
? ? ? ? ELSE ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? delete from table2 where (`DirUuid` = dir_uuid and `BaseName` = base_name) or (`DirUuid` = dir_uuid and `OriginBaseName` = base_name);
? ? ? ? END IF; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? IF t_error = 1 THEN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ROLLBACK; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ELSE ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? COMMIT; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? END IF; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? select t_error; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
END
然后單獨調(diào)用存儲過程：
mysql> call PDelete('5', 'd3b18237-07ee-43bd-be15-aa7b7399f94b', 0);
+---------+
| t_error |
+---------+
| ? ? ? 0 |
+---------+
1 row in set (3.47 sec)
竟然需要3.47s，從存儲過程的定義以及表結(jié)構(gòu)來看，怎么也不用3.47s.
當(dāng)單獨拿出delete語句來執(zhí)行看看：
mysql> delete from table1 where (`DirUuid` = 'd3b18237-07ee-43bd-be15-aa7b7399f94b' and `BaseName` = '5') or (`DirUuid` = 'd3b18237-07ee-43bd-be15-aa7b7399f94b' and `OriginBaseName` = '5');
Query OK, 0 rows affected (0.00 sec)
執(zhí)行很快 ，和存儲過程調(diào)用差別了很大。而且存儲過程中也沒有其他特別的邏輯，沒有循序，沒有等待，不應(yīng)該這么慢才對，只有可能耗時的地方就是delete語句，
存儲過程中delete和外部單獨執(zhí)行的delete語句，看上去完全一致，但是我們還需要判斷，where條件的類型是否一致，會不會是類型不一致導(dǎo)致的執(zhí)行慢？
然后對比存儲存儲過程和表結(jié)構(gòu)，where條件給定的類型也是一致的。那還會有什么原因?qū)е聢?zhí)行很慢呢，還有一種情況沒考慮到：字符集，如何表的字符集和where條件中值字符集不一致，
也是有可能導(dǎo)致索引失效的，進行導(dǎo)致執(zhí)行變慢。這里就需要理解一下調(diào)用存儲過程是如何使用字符集的，在官方介紹中有如下說明：
For character data types, if there is a CHARACTER SET attribute in the declaration, the specified
character set and its default collation is used. If the COLLATE attribute is also present, that collation is
used rather than the default collation.
If CHARACTER SET and COLLATE attributes 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 explicit CHARACTER SET and COLLATE attributes for character data parameters.
If you change the database default character set or collation, stored routines that use the database
defaults must be dropped and recreated so that they use the new defaults.
The database character set and collation are given by the value of the character_set_database
and collation_database system variables. For more information, see Section 10.1.3.2, “Database
Character Set and Collation”.
如果存儲過程中定義參數(shù)時，沒有指定字符集，會默認(rèn)讀取創(chuàng)建存儲過程時的全局變量character_set_server，如果后續(xù)變更了字符集，存儲過程不會自動變更字符集，
需要刪除重新創(chuàng)建存儲過程，才能使得新的字符集生效。
由于創(chuàng)建存儲過程時，沒有指定字符集，因此采用的character_set_server指定的字符集 utf8mb4，那就意味著存儲過程傳參都是采用utf8mb4，那么存儲過程中delete語句中value的字符集
就是utf8mb4,那再看看表的字符集，) ENGINE=InnoDB DEFAULT CHARSET=utf8; 是utf8的,是不兼容utfbmb4的，字符集不同從而導(dǎo)致索引失效，進而導(dǎo)致delete很慢，影響整個存儲過程的執(zhí)行效率。
解決辦法很簡單,兩種方案均可：
1.調(diào)整character_set_server 為 utf8，然后重建存儲過程即可，不重建的話，不會起作用。存儲過程的字符集是以存儲過程創(chuàng)建時character_set_server 為準(zhǔn)。
2.將表的字符集更改為utf8mb4也可以。
兩種方案選擇代價較小的進行即可，我們這里由于是線上環(huán)境，更改字符集需要重啟服務(wù)，因此選擇轉(zhuǎn)化表的字符集。
反過來，如果存儲過程的字符集是utf8,而表的字符集是utf8mb4，那么是不會出現(xiàn)這個問題的，utf8mb4是兼容utf8的，這里需要注意一下。
