c# 自定义分表

最新推荐文章于 2024-07-22 20:44:53 发布

墨水直达

最新推荐文章于 2024-07-22 20:44:53 发布

阅读量581

点赞数

文章标签：数据库开发语言 c#

本文链接：https://blog.csdn.net/huxinyu0208/article/details/134076265

版权

背景
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);
       } 

    }