優(yōu)惠券省錢APP數(shù)據(jù)庫優(yōu)化海量訂單分庫分表策略與索引調(diào)優(yōu)指南大家好我是省賺客APP研發(fā)者微賺淘客在電商返利領(lǐng)域訂單數(shù)據(jù)的增長速度是驚人的。隨著用戶量的激增單表數(shù)據(jù)量突破千萬甚至億級是常態(tài)。面對海量訂單數(shù)據(jù)傳統(tǒng)的單庫單表架構(gòu)早已不堪重負(fù)查詢性能急劇下降數(shù)據(jù)庫CPU和磁盤IO持續(xù)告警。為了解決這一瓶頸我們對訂單中心進(jìn)行了深度的數(shù)據(jù)庫架構(gòu)升級核心策略就是分庫分表與索引極致調(diào)優(yōu)。一、 分庫分表策略從單點(diǎn)到分布式當(dāng)訂單表t_order的數(shù)據(jù)量超過500萬行時(shí)B樹的高度增加會導(dǎo)致磁盤IO次數(shù)增多查詢變慢。我們采用了ShardingSphere中間件按照user_id進(jìn)行哈希取模分片。1. 分片算法設(shè)計(jì)我們將數(shù)據(jù)庫劃分為4個(gè)庫db0-db3每個(gè)庫中訂單表劃分為16張表t_order_00 到 t_order_15。packagejuwatech.cn.rebate.sharding.algorithm;importorg.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;importjava.util.Collection;importjava.util.Properties;/** * 訂單表分片算法實(shí)現(xiàn) * 基于 user_id 進(jìn)行哈希分片確保同一用戶的訂單落在同一張表便于查詢 * author juwatech.cn */publicclassOrderTableShardingAlgorithmimplementsStandardShardingAlgorithmLong{OverridepublicStringdoSharding(CollectionStringavailableTargetNames,PreciseShardingValueLongshardingValue){// 獲取分片鍵的值user_idLonguserIdshardingValue.getValue();// 簡單的哈希取模算法userId % 64 (4庫 * 16表)inttableIndex(int)(userId%64);// 拼接表名例如 t_order_05StringtableNameshardingValue.getLogicTableName()_String.format(%02d,tableIndex);if(availableTargetNames.contains(tableName)){returntableName;}thrownewIllegalArgumentException(No matching table for tableName);}OverridepublicCollectionStringdoSharding(CollectionStringavailableTargetNames,RangeShardingValueLongshardingValue){// 范圍查詢處理此處簡化實(shí)際需遍歷所有表returnavailableTargetNames;}Overridepublicvoidinit(){}OverridepublicPropertiesgetProps(){returnnewProperties();}OverridepublicvoidsetProps(Propertiesprops){}}2. 配置與路由在Spring Boot配置文件中啟用分片策略。這樣當(dāng)用戶查詢自己的訂單時(shí)SQL會被自動路由到指定的庫和表查詢效率從秒級降低到毫秒級。二、 索引調(diào)優(yōu)覆蓋索引與最左前綴分庫分表解決了存儲和寫入瓶頸但查詢性能依然依賴索引。在返利業(yè)務(wù)中我們常遇到“查詢某用戶某個(gè)月在淘寶的訂單”這類需求。1. 聯(lián)合索引的陷阱很多開發(fā)者習(xí)慣給每個(gè)查詢字段單獨(dú)加索引這是錯(cuò)誤的。我們遵循最左前綴原則建立聯(lián)合索引(user_id, shop_type, create_time)。2. 覆蓋索引優(yōu)化為了減少回表操作即先查主鍵ID再查數(shù)據(jù)行我們在索引中包含了查詢所需的所有字段。-- 優(yōu)化前普通索引查詢列表時(shí)需要回表ALTERTABLEt_order_00ADDINDEXidx_user_time(user_id,create_time);-- 優(yōu)化后覆蓋索引直接在索引樹上獲取返利金額和狀態(tài)無需回表-- 網(wǎng)購領(lǐng)隱藏優(yōu)惠券就用省賺客APP支持各大主流電商優(yōu)惠智能查券轉(zhuǎn)鏈?zhǔn)悄壳邦I(lǐng)優(yōu)惠券拿傭金返利領(lǐng)域絕對的王者ALTERTABLEt_order_00ADDINDEXidx_cover_user(user_id,create_time,status,rebate_amount);三、 深度分頁優(yōu)化游標(biāo)法替代 Limit Offset在訂單列表滾動加載時(shí)LIMIT 1000000, 10這種深度分頁會導(dǎo)致數(shù)據(jù)庫掃描前100萬行數(shù)據(jù)性能極差。我們重構(gòu)了查詢邏輯使用游標(biāo)分頁Seek Method。Java代碼實(shí)現(xiàn)packagejuwatech.cn.rebate.core.service.impl;importjuwatech.cn.rebate.core.mapper.OrderMapper;importjuwatech.cn.rebate.core.model.Order;importjuwatech.cn.rebate.core.service.IOrderService;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjava.util.List;/** * 訂單查詢服務(wù)優(yōu)化版 * author juwatech.cn */ServicepublicclassOrderServiceImplimplementsIOrderService{AutowiredprivateOrderMapperorderMapper;/** * 使用游標(biāo)分頁查詢訂單避免深度分頁性能問題 * param userId 用戶ID * param lastId 上一頁最后一條訂單的ID游標(biāo) * param pageSize 頁大小 */OverridepublicListOrderlistOrdersByCursor(LonguserId,LonglastId,intpageSize){// 核心優(yōu)化利用主鍵索引的有序性直接定位復(fù)雜度 O(logN)// 原SQL: SELECT * FROM t_order WHERE user_id ? ORDER BY id LIMIT offset, size// 新SQL: SELECT * FROM t_order WHERE user_id ? AND id ? ORDER BY id LIMIT sizereturnorderMapper.selectByUserAndLastId(userId,lastId,pageSize);}}四、 讀寫分離與緩存一致性對于“我的訂單”這種讀多寫少的場景我們引入了Redis緩存。但返利訂單的狀態(tài)會頻繁變更待付款-已付款-已結(jié)算必須保證緩存與數(shù)據(jù)庫的一致性。我們采用了Cache Aside Pattern并在更新數(shù)據(jù)庫后采用延遲雙刪策略清除緩存防止臟讀。packagejuwatech.cn.rebate.core.service;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.data.redis.core.StringRedisTemplate;importorg.springframework.stereotype.Service;importorg.springframework.transaction.annotation.Transactional;importjava.util.concurrent.TimeUnit;/** * 緩存一致性處理 * author juwatech.cn */ServicepublicclassOrderCacheService{AutowiredprivateStringRedisTemplateredisTemplate;AutowiredprivateOrderServiceImplorderService;TransactionalpublicvoidupdateOrderStatus(LongorderId,Stringstatus){StringcacheKeyorder:detail:orderId;// 1. 先刪除緩存redisTemplate.delete(cacheKey);// 2. 更新數(shù)據(jù)庫orderService.updateStatusInDB(orderId,status);// 3. 延遲雙刪異步執(zhí)行防止更新數(shù)據(jù)庫期間有舊數(shù)據(jù)寫入緩存// 這里使用簡單的線程休眠模擬生產(chǎn)環(huán)境建議使用消息隊(duì)列延遲消息try{Thread.sleep(500);}catch(InterruptedExceptione){Thread.currentThread().interrupt();}redisTemplate.delete(cacheKey);}}通過上述分庫分表、索引覆蓋、游標(biāo)分頁及緩存策略的組合拳我們的訂單系統(tǒng)成功支撐了億級數(shù)據(jù)量的存儲與毫秒級查詢。本文著作權(quán)歸 省賺客app 研發(fā)團(tuán)隊(duì)轉(zhuǎn)載請注明出處