一、前言

需求背景：mysql數(shù)據(jù)庫(kù)中有一個(gè)表的數(shù)據(jù)（600多萬(wàn)）有一個(gè)字段的內(nèi)容需要解密再通過(guò)另外一種加密方式進(jìn)行加密再回存。通過(guò)java程序計(jì)算完成更新。

二、方案一

一條條計(jì)算更新。這里是將手機(jī)號(hào)解密，在通過(guò)另外一種方式回存。

算法步驟：

1、查詢需要解密的數(shù)據(jù)總條數(shù)

2、循環(huán)查詢1條數(shù)據(jù)，進(jìn)行解密，加密，更新，commit。

	public void updateOne() {

		int n = 0;
		n = getCount();
		log.info("待密數(shù)據(jù)條數(shù)：" + n);

		if (n > 0) {
			for (int i = 0; i < n; i++) {
				TmpMUser tmpMUser = getOne();
				if (null != tmpMUser) {
					String yphone = tmpMUser.getCellphoneSha();
					log.info("原手機(jī)號(hào)：" + yphone);
					if (!StringUtils.isEmpty(yphone)) {
						yphone = yphone.replaceFirst("86-s4:", "");
						String phone = SM4Util.sm4Decode(yphone, keyString);
						String origPhone = SHAUtils.getSHA("86-" + phone);
						log.info("解密出手機(jī)號(hào)：" + phone);
						tmpMUser.setPhone(phone);
						tmpMUserMapper.updateTmpMUser(tmpMUser.getRowId(), phone, origPhone);
						tmpMUserMapper.commit();
					}

				}

				log.info("已解密數(shù)據(jù)條數(shù)：" + i);

			}

		} else {

			log.info("沒(méi)有需要解密的數(shù)據(jù)");
		}

	}

存在的問(wèn)題。

數(shù)據(jù)量太大，幾秒鐘才能操作完成一條。原因：每更新一條就commit，消耗數(shù)據(jù)庫(kù)性能，第二，更新的條件沒(méi)有索引，查找數(shù)據(jù)較慢。

三、方案二

改進(jìn)，建索引，每200條commit一次。

/**
	 * 更新200條
	 * 
	 */
	public void update200() {

		int n = 0;
		n = getCount();
		log.info("待密數(shù)據(jù)條數(shù)：" + n);

		if (n > 0) {
			for (int i = 0; i < n / 200 + 200; i++) {
				List<TmpMUser> tmpMUserList = get200();
				if (CollectionUtils.isNotEmpty(tmpMUserList)) {

					for (TmpMUser tmpMUser : tmpMUserList) {
						String yphone = tmpMUser.getCellphoneSha();
						log.info("原手機(jī)號(hào)：" + yphone);
						if (!StringUtils.isEmpty(yphone)) {
							yphone = yphone.replaceFirst("86-s4:", "");
							String phone = SM4Util.sm4Decode(yphone, keyString);
							String origPhone = SHAUtils.getSHA("86-" + phone);
							log.info("解密出手機(jī)號(hào)：" + phone);
							tmpMUser.setPhone(phone);
							tmpMUserMapper.updateTmpMUser(tmpMUser.getRowId(), phone, origPhone);
						}

					}
					tmpMUserMapper.commit();

				}

				log.info("已解密數(shù)據(jù)條數(shù)：" + 2 * i + "00");

			}

		} else {

			log.info("沒(méi)有需要解密的數(shù)據(jù)");
		}

	}

四、主要問(wèn)題

這里更新數(shù)據(jù)一定需要手動(dòng)commit，如果沒(méi)有commit更新是不生效的，所以優(yōu)化這一步是關(guān)鍵。

優(yōu)化完成，提升到每秒200條。

   /**
     * 提交事務(wù)
     */
    @Update("<script>" +
            "commit " +
            "</script>")
    public void commit();

五、mysql常規(guī)優(yōu)化

單表優(yōu)化

除非單表數(shù)據(jù)未來(lái)會(huì)一直不斷上漲，否則不要一開(kāi)始就考慮拆分，拆分會(huì)帶來(lái)邏輯、部署、運(yùn)維的各種復(fù)雜度。一般以整型值為主的表在千萬(wàn)級(jí)以下，字符串為主的表在五百萬(wàn)以下是沒(méi)有太大問(wèn)題的，而事實(shí)上很多時(shí)候MySQL單表的性能依然有不少優(yōu)化空間，甚至能正常支撐千萬(wàn)級(jí)以上的數(shù)據(jù)量。

1字段

盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類(lèi)型而非INT，如果非負(fù)則加上UNSIGNED；

VARCHAR的長(zhǎng)度只分配真正需要的空間；

使用枚舉或整數(shù)代替字符串類(lèi)型；

盡量使用TIMESTAMP而非DATETIME；

單表不要有太多字段，建議在20以內(nèi)；

避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間；

用整型來(lái)存IP。

2索引

索引并不是越多越好，要根據(jù)查詢有針對(duì)性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來(lái)查看是否用了索引還是全表掃描；

應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

值分布很稀少的字段不適合建索引，例如“性別”這種只有兩三個(gè)值的字段；

字符字段只建前綴索引；

字符字段最好不要做主鍵；

不用外鍵，由程序保證約束；

盡量不用UNIQUE，由程序保證約束；

使用多列索引時(shí)主意順序和查詢條件保持一致，同時(shí)刪除不必要的單列索引。

3查詢SQL

可通過(guò)開(kāi)啟慢查詢?nèi)罩緛?lái)找出較慢的SQL；

不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊；

sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫(kù)；

不用SELECT *；

OR改寫(xiě)成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，IN的個(gè)數(shù)建議控制在200以內(nèi)；

不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)；

避免%xxx式查詢；

少用JOIN；

使用同類(lèi)型進(jìn)行比較，比如用'123'和'123'比，123和123比；

盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；

對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；

列表數(shù)據(jù)不要拿全表，要使用LIMIT來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大。

4引擎

目前廣泛使用的是MyISAM和InnoDB兩種引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默認(rèn)引擎，它的特點(diǎn)是：

不支持行鎖，讀取時(shí)對(duì)需要讀到的所有表加鎖，寫(xiě)入時(shí)則對(duì)表加排它鎖；

不支持事務(wù)；

不支持外鍵；

不支持崩潰后的安全恢復(fù)；

在表有讀取查詢的同時(shí)，支持往表中插入新紀(jì)錄；

支持BLOB和TEXT的前500個(gè)字符索引，支持全文索引；

支持延遲更新索引，極大提升寫(xiě)入性能；

對(duì)于不會(huì)進(jìn)行修改的表，支持壓縮表，極大減少磁盤(pán)空間占用。

InnoDB

InnoDB在MySQL 5.5后成為默認(rèn)索引，它的特點(diǎn)是：

支持行鎖，采用MVCC來(lái)支持高并發(fā)；

支持事務(wù)；

支持外鍵；

支持崩潰后的安全恢復(fù)；

不支持全文索引。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-684053.html

六、mysql語(yǔ)句優(yōu)化

1. 盡量避免使用子查詢
2. 用IN來(lái)替換OR
3. 讀取適當(dāng)?shù)挠涗汱IMIT M,N，而不要讀多余的記錄
4. 禁止不必要的Order By排序
5. 總和查詢可以禁止排重用union all
6. 避免隨機(jī)取記錄
7. 將多次插入換成批量Insert插入
8. 只返回必要的列，用具體的字段列表代替 select * 語(yǔ)句
9. 區(qū)分in和exists
10. 優(yōu)化Group By語(yǔ)句
11. 盡量使用數(shù)字型字段
12. 優(yōu)化Join語(yǔ)句

到了這里，關(guān)于記一次批量更新mysql數(shù)據(jù)過(guò)程的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！