現在有一個需求：使用者表裡面有將近200萬條資料，查詢時需要按使用者名稱字的漢語拼音按從a-z排序。有兩種解決方案：1.查詢時使用資料庫自帶的CONVERT()函式進行轉化，按拼音首字母排序;2.新加一個拼音欄位（spell_name），使用者註冊時，將使用者名稱字的漢語拼音也一併插入資料庫。權衡之後我採用了第二種，因為使用者量還會持續增長，使用資料庫自帶的函式會拖慢查詢速度，索引也會失效，如果採用第二種加一個拼音欄位，就涉及到對原來老資料拼音欄位的一個初始化，資料量比較多，使用多執行緒，在這裡記錄一下。
1.使用jpinyin和emoji-java將漢字轉化成拼音
引入相關jar
<!--漢字轉拼音jar-->
<dependency>
<groupId>com.github.stuxuhai</groupId>
<artifactId>jpinyin</artifactId>
<version>1.0</version>
</dependency>
<!--java操作emoji的jar-->
<dependency>
<groupId>com.vdurmont</groupId>
<artifactId>emoji-java</artifactId>
<version>4.0.0</version>
</dependency>
複製程式碼emoji表情處理工具類
public class EmojiDealUtil extends EmojiParser {
/**
* 獲取非表情字串
* @param input
* @return
*/
public static String getNonEmojiString(String input) {
int prev = 0;
StringBuilder sb = new StringBuilder();
List<UnicodeCandidate> replacements = getUnicodeCandidates(input);
for (UnicodeCandidate candidate : replacements) {
sb.append(input.substring(prev, candidate.getEmojiStartIndex()));
prev = candidate.getFitzpatrickEndIndex();
}
return sb.append(input.substring(prev)).toString();
}
/**
* 獲取表情字串
* @param input
* @return
*/
public static String getEmojiUnicodeString(String input){
EmojiTransformer transformer = new EmojiTransformer() {
public String transform(UnicodeCandidate unicodeCandidate) {
return unicodeCandidate.getEmoji().getHtmlHexadecimal();
}
};
StringBuilder sb = new StringBuilder();
List<UnicodeCandidate> replacements = getUnicodeCandidates(input);
for (UnicodeCandidate candidate : replacements) {
sb.append(transformer.transform(candidate));
}
return parseToUnicode(sb.toString());
}
public static String getUnicode(String source){
String returnUniCode=null;
String uniCodeTemp=null;
for(int i=0;i<source.length();i++){
uniCodeTemp = "\\u"+Integer.toHexString((int)source.charAt(i));
returnUniCode=returnUniCode==null?uniCodeTemp:returnUniCode+uniCodeTemp;
}
return returnUniCode;
}
}
複製程式碼漢字轉換成拼音的工具類
public class ChineseToPinYinUtil {
/**
* 轉換為不帶音調的拼音字串
* @param pinYinStr 需轉換的漢字
* @return 拼音字串
*/
public static String changeToTonePinYin(String pinYinStr) {
String tempStr = null;
try {
tempStr = PinyinHelper.convertToPinyinString(pinYinStr, " ", PinyinFormat.WITHOUT_TONE);
} catch (Exception e) {
e.printStackTrace();
}
return tempStr;
}
}
複製程式碼拼音轉換不做為本文點，網上搜下有很多庫和程式碼解決，主要說下面的多執行緒處理。
2.使用多執行緒查詢並更新資料庫
建立初始容量為5的執行緒池，每次每個執行緒查詢500條記錄並處理，加個同步鎖，分配給每個執行緒它查詢的起始記錄，查詢出記錄之後呼叫上面的漢字轉換成拼音方法處理，完畢之後更新到資料庫。
2.1接收請求方法
//每個執行緒每次查詢的條數
private static final Integer LIMIT = 500;
//起的執行緒數
private static final Integer THREAD_NUM = 5;
ThreadPoolExecutor pool = new ThreadPoolExecutor(THREAD_NUM, THREAD_NUM*2,0,TimeUnit.SECONDS,new LinkedBlockingQueue<>(100));
@GetMapping("/chineseToSpellName")
public void execute(){
//計數器,一次轉換隻能一個請求調，不然會出錯
int count = 0;
logger.info("trans start");
//查詢總記錄數
int total = userService.getTotalCount2();
logger.info("total num:{}",total);
int num = total/(LIMIT*THREAD_NUM) + 1;
logger.info("要經過的輪數：{}",num);
for(int j=0;j<num;j++){
//起 THREAD_NUM 個執行緒並行查詢更新庫，加鎖
for(int i=0;i<THREAD_NUM;i++){
synchronized(ChineseToPinYinController.class){
int start = count*LIMIT;
count++;
pool.submit(new TransTask(start,LIMIT));
}
}
}
}
複製程式碼2.2多執行緒處理業務方法
class TransTask implements Runnable{
int start;
int limit;
public TransTask(int start, int limit) {
this.start = start;
this.limit = limit;
}
@Override
public void run() {
//查詢記錄並更新資料庫
List<User> userList = userService.getList2(start,limit);
logger.info("更新記錄起始位置：{}--{}",start,limit);
if(!CollectionUtils.isEmpty(userList)){
userList.stream().forEach(u -> {
u.setSpellName(ChineseToPinYinUtil.changeToTonePinYin(EmojiDealUtil.getNonEmojiString(u.getName())).trim());
userService.updateUser2(u);
}
);
}
}
}
複製程式碼3.不使用傳統的limit分頁查詢資料
userService.getList2(start,num)是根據起始位置和查詢條數查詢記錄，以前我們寫的分頁查詢一般是這樣寫的：select * from table limit start,num（如：select * from user limit 0,20）。這種查詢在資料量小時沒有問題，但是資料量大的時候查詢會非常慢，因為它走的不是索引，而是全表掃描，資料量越大，越到後面速度越慢。對於id是自增長的查詢可以採用另一種查詢方式，select * from table where id>start limit num(如：select * from user where id>1000 limit 20)，從指定id查詢num條記錄。這種查詢即使到百萬級資料量，查詢速度也不會明顯變慢，因為走的是主鍵索引，而不是全表掃描。
4.優化後記
程式碼寫完之後在實際使用中，資料初始化到70多萬條的時候，資料庫連線數開的太多，將資料庫裡面的全部佔滿了，考慮再次優化，採用分段的方式，傳入兩個引數，初始化記錄和初始化條數。比如第一次初始化0-10萬條記錄，第二次初始化10到20萬條記錄，依次類推，這樣的好處是可以人工干預，即使出錯，也可以只執行出錯的這部分割槽間資料，程式碼如下：
//每個執行緒每次查詢的條數
private static final Integer LIMIT = 500;
//起的執行緒數
private static final Integer THREAD_NUM = 5;
ThreadPoolExecutor pool = new ThreadPoolExecutor(THREAD_NUM,Integer.MAX_VALUE,0,TimeUnit.SECONDS,
new ArrayBlockingQueue<>(10));
@GetMapping("/chineseToSpellName")
public void execute(@RequestParam("startId") Integer startId,@RequestParam("total") Integer total){
logger.info("trans start");
int num = total/(LIMIT*THREAD_NUM) + 1;
logger.info("要經過的輪數：{}",num);
for(int j=0;j<num;j++){
//起 THREAD_NUM 個執行緒並行查詢更新庫，加鎖
for(int i=0;i<THREAD_NUM;i++){
synchronized(ChineseToPinYinController.class){
pool.submit(new TransTask(startId,LIMIT));
startId+=LIMIT;
}
}
}
}
class TransTask implements Runnable{
int start;
int limit;
public TransTask(int start, int limit) {
this.start = start;
this.limit = limit;
}
@Override
public void run() {
//查詢記錄並更新資料庫
List<User> userList = userService.getList2(start,limit);
logger.info("更新記錄起始位置：{}--{}",start,limit);
if(!CollectionUtils.isEmpty(userList)){
userList.stream().forEach(u -> {
u.setSpellName(ChineseToPinYinUtil.changeToTonePinYin(EmojiDealUtil.getNonEmojiString(u.getName())).trim());
userService.updateUser2(u);
}
);
}
}
}
複製程式碼