背景
sqlserver 单表数据过多，数据量已经过亿，查询缓慢，之前是采取手动分表的处理，我是半路接手，现在想对其进行改造，但是项目比较老，无法使用现成的分表插件，我们分表是根据项目来，这里记录下操作过程以及具体代码，希望能给小伙伴提供一些思路

 

 //这里在配置文件中设置分表的阈值
<add key="SubmeterThreshold" value="1000000"/>

 /// <summary>/// 根据原表名创建新表/// </summary>/// <param name="sourceTableName"></param>/// <returns></returns>public static string CreateSubmeterTable(string sourceTableName){string newTableName = "";try{ string DateStr = DateTime.Now.ToString("yyyyMMddHHmmss");string connectionString =Settings.DBHerperSqlConnection; // 替换为你的数据库连接字符串 newTableName = sourceTableName+"_" + DateStr; // 替换为你的新表名称using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open();// 复制表结构string createTableQuery = "SELECT TOP 0 * INTO "+newTableName+" FROM "+sourceTableName+" ";using (SqlCommand command = new SqlCommand(createTableQuery, connection)){command.ExecuteNonQuery();}// 复制主键，这里通过语句只能获取到主键名称，和主键字段不一致，所以这里自己写死,且主键名是数据库唯一，所以自行定义string pkkey = "PK_" + newTableName;string pkvalue = "主键字段id"string alterTableQuery = "ALTER TABLE " + newTableName + " ADD CONSTRAINT " + pkkey + " PRIMARY KEY CLUSTERED (" + pkvalue + ")";using (SqlCommand alterTableCommand = new SqlCommand(alterTableQuery, connection)){alterTableCommand.ExecuteNonQuery();}// 复制索引string indexQuery = "SELECT name, type_desc, is_unique, is_primary_key,index_id FROM sys.indexes WHERE OBJECT_NAME(object_id) = '" + sourceTableName + "'";List<ModelIndex> modelindex = new List<ModelIndex>();using (SqlCommand command = new SqlCommand(indexQuery, connection)){using (SqlDataReader reader = command.ExecuteReader()){while (reader.Read()) {bool isPrimaryKey = reader.GetBoolean(3); if (!isPrimaryKey){ModelIndex entity = new ModelIndex(); entity.indexName = reader.GetString(0) + "_" + DateStr;entity.indexType = reader.GetString(1);entity.isUnique = reader.GetBoolean(2);entity.index_id = reader.GetInt32(4);modelindex.Add(entity);}}}}//复制索引字段foreach (var item in modelindex){ string createIndexQuery = "CREATE " + (item.isUnique ? "UNIQUE " : "") + item.indexType + " INDEX " + item.indexName + " ON " + newTableName + " (";// 获取索引关联的字段string indexColumnsQuery = "SELECT name FROM sys.index_columns ic JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id WHERE ic.object_id = OBJECT_ID('" + sourceTableName + "') AND ic.index_id = " + item.index_id;using (SqlCommand indexColumnsCommand = new SqlCommand(indexColumnsQuery, connection)){using (SqlDataReader indexColumnsReader = indexColumnsCommand.ExecuteReader()){bool firstColumn = true;while (indexColumnsReader.Read()){if (!firstColumn){createIndexQuery += ", ";}createIndexQuery += indexColumnsReader.GetString(0);firstColumn = false;}}}createIndexQuery += ")";using (SqlCommand createIndexCommand = new SqlCommand(createIndexQuery, connection)){createIndexCommand.ExecuteNonQuery();}}} }catch (Exception ex){LogHelper.WriteLog("分表失败：" + ex.Message);}return newTableName;}

思路提供：
每次创建项目，都获取下最新分表的数据总量，如果记录数超过设置的阈值，则进行分表处理，且将分表的表名，项目id进行记录
目的是下次创建项目的时候，根据项目id以及存储的分表表名，查询新表数据是否超出阈值

手动分表的缺点就是需要重写插入、查询方法，因为没有引用orm，这里 插入数据需要进行改造
//部分代码如下
 

 db.CommandTimeout = 600000;// 打开数据库连接db.Connection.Open();//开启事务var transaction = db.Connection.BeginTransaction();db.Transaction = transaction;try{ //获取字段属性var tabledata = SubmeterHerper.GetEntityData(存储数据的实体, true, 1);string answerSql = string.Format(@"INSERT INTO {0} ({1}) OUTPUT INSERTED.answerid VALUES ({2})", "分表表名", tabledata.Item1, tabledata.Item2); var result = db.ExecuteQuery<int>(answerSql).FirstOrDefault();db.Transaction.Commit();}catch (Exception ex){ // 发生异常时回滚事务db.Transaction.Rollback();throw;}finally{// 关闭数据库连接db.Connection.Close();}/// <summary>/// 获取字段属性/// </summary>/// <typeparam name="T"></typeparam>/// <param name="entity">值</param>/// <param name="association">是否存在关联表</param>/// <param name="takenum">如果存在关联，末尾跳过的数量</param>/// <returns></returns>public static Tuple<string, string> GetEntityData<T>(T entity, bool association = false, int takenum = 1){// 用于拼接字段名StringBuilder fieldNameBuilder = new StringBuilder();// 用于拼接字段值StringBuilder fieldValueBuilder = new StringBuilder();// 获取entity的类型Type entityType = entity.GetType();// 获取entity的所有属性PropertyInfo[] properties = entityType.GetProperties();//判断是否存在关联表，如果存在需要跳过关联字段if (association){properties = properties.Skip(1).Take(properties.Length - (1 + takenum)).ToArray();}else{properties = properties.Skip(1).ToArray();}// 遍历属性foreach (PropertyInfo property in properties){//主键自增无法通过反射获取，这里去除第一个字段（--默认第一个字段为主键）// 获取属性名string fieldName = property.Name;// 获取属性值object fieldValue = property.GetValue(entity, null);// 将属性名拼接到字段名的字符串中fieldNameBuilder.Append("[").Append(fieldName).Append("],");// 将属性值拼接到字段值的字符串中fieldValueBuilder.Append("'").Append(fieldValue).Append("',");}// 去除最后一个逗号fieldNameBuilder.Length--;fieldValueBuilder.Length--;// 返回拼接的结果string fieldNameString = fieldNameBuilder.ToString();string fieldValueString = fieldValueBuilder.ToString();return new Tuple<string, string>(fieldNameString, fieldValueString);}/// <summary>/// 获取字段属性--被关联专用/// </summary>/// <typeparam name="T"></typeparam>/// <param name="entity">值</param>/// <param name="colname">被关联字段</param>/// <param name="value">需赋的值</param>/// <returns></returns>public static Tuple<string, string> GetEntityDataGL<T>(T entity,string colname,int value){// 用于拼接字段名StringBuilder fieldNameBuilder = new StringBuilder();// 用于拼接字段值StringBuilder fieldValueBuilder = new StringBuilder();// 获取entity的类型Type entityType = entity.GetType();// 获取entity的所有属性PropertyInfo[] properties = entityType.GetProperties();//这因为是关联表，所以去掉首尾，如果存在多个关联，则下面应该是properties.Length -(1+n)properties = properties.Skip(1).Take(properties.Length - (2)).ToArray();// 遍历属性foreach (PropertyInfo property in properties){//主键自增无法通过反射获取，这里去除第一个字段（--默认第一个字段为主键）// 获取属性名string fieldName = property.Name;// 获取属性值object fieldValue = property.GetValue(entity, null);if (fieldName==colname){fieldValue = value;}// 将属性名拼接到字段名的字符串中fieldNameBuilder.Append("[").Append(fieldName).Append("],");// 将属性值拼接到字段值的字符串中fieldValueBuilder.Append("'").Append(fieldValue).Append("',");}// 去除最后一个逗号fieldNameBuilder.Length--;fieldValueBuilder.Length--;// 返回拼接的结果string fieldNameString = fieldNameBuilder.ToString();string fieldValueString = fieldValueBuilder.ToString();return new Tuple<string, string>(fieldNameString, fieldValueString);} }