複雜業務下向Mysql匯入30萬條資料程式碼優化的踩坑記錄

haifeiWu發表於2018-04-23
MySql優化

從畢業到現在第一次接觸到超過30萬條資料匯入MySQL的場景（有點low），就是在順豐公司接入我司EMM產品時需要將AD中的員工資料匯入MySQL中，因此樓主負責的模組connector就派上了用場。在樓主的努力下，線上資料同步程式碼經歷了從最初的將近16個小時（並且還出現其他問題這些問題，等後面慢慢細說），到最終25分鐘的效能優化。

打個廣告，樓主自己造的輪子，感興趣的請點github.com/haifeiWu/li…

程式碼直接Jenkins打包上線

樓主負責的connector模組之前經歷過的最大的資料量也僅僅是幾千條，當然面對幾千條資料程式碼也是跑的及其的快，沒有啥影響，然而當第一次在順豐的正式環境上線時，由於資料量比較大，樓主的程式碼又是序列執行的，事務保持的時間就相當長，也就因此出現了下面的錯誤資訊：

Lock wait timeout exceeded; try restarting transaction
複製程式碼

這裡來說一下報這個錯的解決方案，檢視MySQL是否有鎖

show OPEN TABLES where In_use > 0;
複製程式碼

另外，在information_schema下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS（解決問題方法），通過這三張表，可以更簡單地監控當前的事務並分析可能存在的問題。

比較常用的列：

  • trx_id: InnoDB儲存引擎內部唯一的事物ID
  • trx_status: 當前事務的狀態
  • trx_status: 事務的開始時間
  • trx_requested_lock_id： 等待事務的鎖ID
  • trx_wait_started： 事務等待的開始時間
  • trx_weight： 事務的權重，反應一個事務修改和鎖定的行數，當發現死鎖需要回滾時，權重越小的值被回滾
  • trx_mysql_thread_id： MySQL中的程式ID，與show processlist中的ID值相對應
  • trx_query： 事務執行的SQL語句

kill 程式ID，發生上面錯誤的根本原因在業務邏輯程式碼對資料庫的操作無視了大資料量的情況，比如當資料量比較大時就會出現剛剛修改完這條記錄，接著再次修改就會出現上述出現的問題。

程式碼優化過程

使用執行緒池，併發執行，提高效率

由於資料量比較大，首先想到的方法是拿到資料後將資料分拆成n份，由多個執行緒併發執行數匯入的操作。由此引出多執行緒的問題，在處理多執行緒問題時共享的資料結構像Map，List應該採用jdk提供的current包下的資料結構，另外在涉及到運算元據庫的地方應該加鎖，樓主用的是jdk提供的ReentrantLock。使用jdk自帶的 jvisualvm，進行程式碼的監控進而找到最佳執行緒數，下面是監控的資料，

jvisualvm-monitor
jvisualvm-thread

下面是執行緒池的建立程式碼

ThreadFactory namedThreadFactory = new ThreadFactoryBuilder()
                .setNameFormat("demo-pool-%d").build();
ExecutorService singleThreadPool = new ThreadPoolExecutor(1, 1,0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<Runnable>(1024), namedThreadFactory, new ThreadPoolExecutor.AbortPolicy());

singleThreadPool.execute(()-> System.out.println(Thread.currentThread().getName()));
singleThreadPool.shutdown();
複製程式碼

果不其然，在使用多執行緒之後，資料插入效率由原來的十幾個小時降到了三個小時，然後並沒有達到我們的預期效果，我們繼續。

使用druid監控發現問題的SQL

  • 配置druid監控，在應用的web.xml新增配置，並放開/druid的攔截
<!-- druid 資料庫監控 -->
	<filter>
		<filter-name>DruidWebStatFilter</filter-name>
		<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
		<init-param>
			<param-name>exclusions</param-name>
			<param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,*.jsp,/druid/*,/download/*</param-value>
		</init-param>
		<init-param>
			<param-name>sessionStatMaxCount</param-name>
			<param-value>2000</param-value>
		</init-param>
		<init-param>
			<param-name>sessionStatEnable</param-name>
			<param-value>true</param-value>
		</init-param>
		<init-param>
			<param-name>principalSessionName</param-name>
			<param-value>session_user_key</param-value>
		</init-param>
		<init-param>
			<param-name>profileEnable</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>DruidWebStatFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

	<servlet>
		<servlet-name>DruidStatView</servlet-name>
		<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
		<!--<init-param>
          <param-name>allow</param-name>
          <param-value>*.*.*.*</param-value>
        </init-param>-->
		<init-param>
			<!-- 允許清空統計資料 -->
			<param-name>resetEnable</param-name>
			<param-value>true</param-value>
		</init-param>
		<init-param>
			<!-- 使用者名稱 -->
			<param-name>loginUsername</param-name>
			<param-value>druid</param-value>
		</init-param>
		<init-param>
			<!-- 密碼 -->
			<param-name>loginPassword</param-name>
			<param-value>druid</param-value>
		</init-param>
	</servlet>
	<servlet-mapping>
		<servlet-name>DruidStatView</servlet-name>
		<url-pattern>/druid/*</url-pattern>
	</servlet-mapping>
複製程式碼
  • 配置資料庫連線池
<!-- 資料來源配置, 使用 BoneCP 資料庫連線池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
		<!-- 資料來源驅動類可不寫，Druid預設會自動根據URL識別DriverClass -->
		<property name="driverClassName" value="${jdbc.driver}" />

		<!-- 基本屬性 url、user、password -->
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />

		<!-- 配置初始化大小、最小、最大 -->
		<property name="initialSize" value="${jdbc.pool.init}" />
		<property name="minIdle" value="${jdbc.pool.minIdle}" />
		<property name="maxActive" value="${jdbc.pool.maxActive}" />

		<!-- 配置獲取連線等待超時的時間 -->
		<property name="maxWait" value="60000" />

		<!-- 配置間隔多久才進行一次檢測，檢測需要關閉的空閒連線，單位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="60000" />

		<!-- 配置一個連線在池中最小生存的時間，單位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="300000" />

		<property name="validationQuery" value="${jdbc.testSql}" />
		<property name="testWhileIdle" value="true" />
		<property name="testOnBorrow" value="true" />
		<property name="testOnReturn" value="false" />

		<!-- 配置監控統計攔截的filters -->
		<property name="filters" value="stat" />
	</bean>
複製程式碼
  • 訪問http://ip/xxx/druid，輸入使用者名稱，密碼登入就會看到下面的圖

druid

  • 使用druid的SQL監控發現問題SQL語句，優化SQL

通過命令檢視程式的gc情況

通過命令jstat -gc pid 來檢視程式的gc情況，下面是樓主程式資料同步完成之後的gc情況

gc
簡單說明：

這裡寫程式碼片S0C、S1C、S0U、S1U：Survivor 0/1區容量（Capacity）和使用量（Used）
EC、EU：Eden區容量和使用量
OC、OU：年老代容量和使用量
PC、PU：永久代容量和使用量
YGC、YGT：年輕代GC次數和GC耗時
FGC、FGCT：Full GC次數和Full GC耗時
複製程式碼

小結

通過上面的優化過程，樓主的connector的資料同居效率也由小時級下降到了分鐘級，同步30+萬的時間可以在25分鐘內完成。經此一役，樓主算是收穫滿滿。抱著虔誠的心態學習，不浮不躁，快樂成長。

相關文章