分库:将海量数据分成多个库保存,比如:2017年的订单库——Order2017,2018年的订单库——Order2018。。。
分表:水平分表(Order拆成Order1.....12)、垂直分表(Order,OrderDetail)
用Sql和ORM(EF)来实现
using EFAdvancedUsage.Models; using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace EFAdvancedUsage.Service { public class OrderService { /// <summary> /// 获取订单详情 /// </summary> /// <param name="orderSerialId">格式如:2018100300001,前8位是订单时间,后5位是当天订单id(最大99999,即一天最多可产生10W-1条订单)</param> /// <returns></returns> public OrderContract GetOrderInfoBySerialId(string orderSerialId) { if (!VerifyOrderSerialIdFormat(orderSerialId)) return new OrderContract(); string orderCreatedMonth = orderSerialId.Substring(4,2); //01~12 using (var context = new EFAdvancedUsageContext()) { /*①最好不用*,而是指明全部列; *②按业务需求列返回OrderBussinessContract对象; *③可对KeyId、OrderSerialId、CustomerId-Name-Mobile等常用条件查询增加索引 */ string sql = "SELECT TOP(1) * FROM Order" + orderCreatedMonth + " Where OrderSerialId=" + orderSerialId; return context.Database.SqlQuery<OrderContract>(sql) .FirstOrDefault(); } } private bool VerifyOrderSerialIdFormat(string orderSerialId) { //用正则或其他方式验证格式 throw new NotImplementedException(); } private string GenerateOrderSerial() { throw new NotImplementedException(); } } }
核心代码同上
读写分离,暂时未整理好、下次再写。