MySQL：mysqldump 100M的數(shù)據(jù)導(dǎo)入需要幾個(gè)小時(shí)？
這個(gè)問(wèn)題相對(duì)簡(jiǎn)單，但是第一次遇到這種問(wèn)題，僅此記錄。問(wèn)題主要是一個(gè)mysqldump導(dǎo)出也就100來(lái)M的文件，導(dǎo)入居然要幾個(gè)小時(shí)，更換多個(gè)實(shí)例后都很慢，文件大小如下：
當(dāng)然這種可以重現(xiàn)的問(wèn)題就再次導(dǎo)入看看為什么就可以了。
一、問(wèn)題重現(xiàn)和分析
導(dǎo)入期間的信息如下：
OS狀態(tài)如下：
可以看到導(dǎo)入session的線程的CPU非常高。
查看show processlist狀態(tài)：
查看CPU調(diào)用火焰圖：
耗用CPU最多的上層調(diào)用為mysql_alter_db。問(wèn)題很明顯了，就是dump文件里面有大量的alter database 語(yǔ)句。這種語(yǔ)句耗用了大量的CPU，導(dǎo)致導(dǎo)入時(shí)間很長(zhǎng)。
隨后查看文件中的alter database語(yǔ)句大概有5600個(gè)，每個(gè)就算1秒，也要5000多秒了，因此整個(gè)導(dǎo)入自然就慢了。
二、為什么有這么多的ALTER DATABASE語(yǔ)句
實(shí)際上在進(jìn)行mysqldump的時(shí)候，如果發(fā)現(xiàn)存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器等的字符集和庫(kù)的字符集不一致的時(shí)候就會(huì)調(diào)用switch_db_collation和restore_db_collation 函數(shù)，將庫(kù)的字符集切換后再建立存儲(chǔ)過(guò)程等對(duì)象，然后再將庫(kù)的字符集切換回去，實(shí)際上就是多了如下的輸出，
if （strcmp(current_db_cl_name, required_db_cl_name) != 0）
{...
fprintf(sql_file, "ALTER DATABASE %s CHARACTER SET %s COLLATE %s %s\n",
quoted_db_name, db_cl->csname, db_cl->m_coll_name, delimiter);
...
}這樣這些對(duì)象的字符集就是導(dǎo)出庫(kù)一致的。
庫(kù)的字符集很明顯，而存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器等獲取的是 Database Collation：
mysql> show create procedure get_order_total_amount2 \G
*************************** 1. row ***************************
Procedure: get_order_total_amount2
...
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci ---這里比如：
- 當(dāng)前庫(kù)：utf8mb3
- 存儲(chǔ)過(guò)程是：utf8mb4_0900_ai_ci
那么導(dǎo)出的語(yǔ)句就是：
alter database charset utf8mb4 ...;
create procedure...
alter database charset utf8mb3...;下面是測(cè)試的片段：
ALTER DATABASE `chr` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_order_total_amount2`(IN order_id INT, OUT total_amount DECIMAL(10, 2))
BEGIN
SELECT SUM(total_amount) INTO total_amount FROM orders WHERE order_id = order_id;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
ALTER DATABASE `chr` CHARACTER SET utf8mb3 COLLATE utf8_general_ci ;可以看到在存儲(chǔ)過(guò)程建立的前后有alter database 語(yǔ)句。如果有幾千個(gè)這樣的存儲(chǔ)過(guò)程，雖然數(shù)據(jù)不大，但是導(dǎo)入?yún)s很很慢，因?yàn)楹挠昧颂郈PU在alter database上，這些CPU耗用和導(dǎo)入的數(shù)據(jù)無(wú)關(guān)。
三、總結(jié)
這種問(wèn)題出現(xiàn)，最可能的原因就是當(dāng)庫(kù)初始化完成后，某天用 alter database修改了庫(kù)的字符集，導(dǎo)致導(dǎo)出的時(shí)候比對(duì)存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器等的字符集和庫(kù)的字符集不一致出現(xiàn)了alter database語(yǔ)句，如果剛好存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器等很多那么就可能很慢很慢。
