关闭

Mysql按日期分表

标签: mysqljava分表
338人阅读 评论(0) 收藏 举报
分类:

一、表不存在时则创建

  之前做项目实在是太赶了,很多东西都没记录。是时候补回来了

  MySQL做一个大表,由于要存历史记录,所以数据量很大,查询很慢。恰好查询的时候,又不需要时间太久的冷数据。现在将其实现原理提取成一个控制台小程序。

  首先,创建一个简单的数据库访问类。

复制代码
    public static class CommonDao
    {
        private static MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["DB"]);        //创建连接

        /// <summary>
        /// Insert Update Delete语句的执行
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNonQuerySQL(string SQL)
        {
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = SQL;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            int Count = cmd.ExecuteNonQuery();
            conn.Close();
            return Count;
        }
    }
复制代码

  由于是按分钟分表,所以写个定时器,每一分钟往里面插入5条数据:

复制代码
        static void Main(string[] args)
        {
            //每分钟执行一次,插入5条数据
            Timer t = new Timer(60000);
            t.Enabled = true;       //到达时间就执行一次,或者是持续执行
            t.AutoReset = true;     //一直执行

            t.Elapsed += (sender, eea) =>
            {
                for (int i = 0; i < 5; i++)
                {
                    DateTime dt = DateTime.Now;
                    string TableName = "person" + dt.ToString("yyyyMMddHHmm");
                    string sql = "INSERT INTO " + TableName + " VALUES(null,'superman','" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                    try
                    {
                        CommonDao.ExecuteNonQuerySQL(sql);
                    }
                    catch (Exception e)
                    {
                        MySqlException ex = e as MySqlException;
                        //1146的代号就是表不存在的错误
                        if (ex.Number == 1146)
                        {
                            //如果表不存在,则先创建这个表
                            string SQLCreateTable = "CREATE TABLE " + TableName + " LIKE person";  //从模板表来创建新表,这样的好处是索引、自增什么的可以一次性建好
                            CommonDao.ExecuteNonQuerySQL(SQLCreateTable);
                        }
                    }
                }

            };

            Console.ReadKey();
        }
复制代码

  第一张Person模板表的格式如下:

CREATE TABLE `person` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `OperateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  

  于是就每分钟都会创建表并添加数据。

  

二、分表后跨表查询的问题

  分表后有很多问题要处理,简单做两个,maybe会有其他问题。

  1、数据在哪个表里;

  2、数据跨表了,那么就需要表连接;

  通常对这种大表,都肯定要加时间的了,所以要根据时间来判断查哪张表和如何表连接。

  增加一个查询的方法:

复制代码
        /// <summary>
        /// 根据SQL语句查询DataTable
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetDataTableBySQL(string SQL)
        {
            DataSet ds = new DataSet();
            MySqlDataAdapter Msda = new MySqlDataAdapter(SQL, conn);
            Msda.Fill(ds);
            DataTable dt = new DataTable();
            if (ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }
复制代码

  数据定位的方法:

复制代码
        public static string GetTableName(DateTime dtStart, DateTime dtEnd)
        {
            if(dtStart.ToString("yyyyMMddHHmm") == dtEnd.ToString("yyyyMMddHHmm"))
            {
                return "person" + dtStart.ToString("yyyyMMddHHmm");
            }
            else
            {
                string TableName = "(SELECT * FROM person" + dtStart.ToString("yyyyMMddHHmm");
                DateTime dt = dtStart.AddMinutes(1);
                while(dt.ToString("yyyyMMddHHmm") != dtEnd.ToString("yyyyMMddHHmm"))
                {
                    TableName += " UNION ALL SELECT * FROM person" + dt.ToString("yyyyMMddHHmm");
                    dt = dt.AddMinutes(1);
                }
                TableName += " UNION ALL SELECT * FROM person" + dt.ToString("yyyyMMddHHmm") + ") AS T";
                return TableName;
            }
        }
复制代码

  测试查询:

            string sql = "SELECT * FROM " + GetTableName(Convert.ToDateTime("2016-04-21 17:54:00"),Convert.ToDateTime("2016-04-21 17:56:00"));
            DataTable dt = CommonDao.GetDataTableBySQL(sql);
            foreach(DataRow dr in dt.Rows)
            {
                Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2]);
            }

  结果如下:

  

  很明显上面的代码暴露出了一个问题,ID重复了。

  本处提供的可选解决方案是。

  1、ID使用GUID。

  2、自增数字的前面加上表名的日期前缀,如,如果你是按月分表,自增Id设置long类型,然后起止值设置为"201604000000000000000 + Id"。这样就不会重复啦。

  自定数字的方式只需多执行以下一条SQL语句:

ALTER TABLE person201604211757 AUTO_INCREMENT=2016042100;

  然后生成的ID列的值如下:

  简单示例就是如此


原文链接:http://www.cnblogs.com/kissdodog/p/5417912.html

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

MYSQL之表分区----按日期分区

错误的按日期分区例子 最直观的方法,就是直接用年月日这种日期格式来进行常规的分区: PLAIN TEXT CODE: mysql> create table rms (d date) -> ...
  • zhang168
  • zhang168
  • 2015-07-16 14:23
  • 8072

MySQL分表处理的实现方法(按月份分的表)

MySQL分表处理用于处理海量的数据库,下面就为您详细介绍MySQL分表处理的实现方法,希望对您能够有所启迪。 面对海量数据的表的时候,我们需要对它进行分表处理,我是按月份分的表,即每月都要建立一张...
  • rongwenbin
  • rongwenbin
  • 2014-10-21 11:10
  • 5235

mysql存储过程按月创建表分区 方式一

mysql存储过程创建表分区(一) 关于分区表,表分区 请看:http://www.cnblogs.com/freeton/p/4265228.html 关于定时任务 请看:http://www....
  • aofavx
  • aofavx
  • 2015-12-22 10:47
  • 6080

MySQL分表处理的实现方法(按月份分的表)

MySQL分表处理用于处理海量的数据库,下面就为您详细介绍MySQL分表处理的实现方法,希望对您能够有所启迪。 面对海量数据的表的时候,我们需要对它进行分表处理,我是按月份分的表,即每月都要建立一张...
  • rongwenbin
  • rongwenbin
  • 2014-10-21 11:10
  • 5235

mysql里分表技术与查询

一、mysql分表 比如,我们建立三个表,表结构相同,表名分别为:tab_1、tab_2、tab_3、tab_4、tab_5.。。。。。。 当然,我们在用分表技术的时候,都是用程序建立,不会用手动建立...
  • dreamboycx
  • dreamboycx
  • 2015-08-14 14:40
  • 934

mysql 数据库 分表后 怎么进行分页查询?Mysql分库分表方案?

1.如果只是为了分页,可以考虑这种分表,就是表的id是范围性的,且id是连续的,比如第一张表id是1到10万,第二张是10万到20万,这样分页应该没什么问题。 2.如果是其他的分表方式,建议用s...
  • ahjxhy2010
  • ahjxhy2010
  • 2016-01-19 09:47
  • 10831

mysql分表的3种方法

一,先说一下为什么要分表 当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。 根据个人经验,mysq...
  • qq43599939
  • qq43599939
  • 2015-12-03 10:01
  • 919

mysql建表时的日期类型选择

mysql(5.5)所支持的日期时间类型有:DATETIME、 TIMESTAMP、DATE、TIME、YEAR。 几种类型比较如下: 日期时间类型 占用空间 日期格式...
  • SR2017
  • SR2017
  • 2017-04-09 16:53
  • 749

mysql数据库分表及实现---MERGE分表法

之前看别人弄网盘资源搜索网站,心血来潮,也想弄个玩玩,于是买了个域名,www1717search.com,也随便弄了个小网站。一开始在本地就在数据库查的,数据库数据是在网上爬的,速度及其慢,于是就弄了...
  • java_bruce
  • java_bruce
  • 2017-05-02 10:17
  • 776

mysql-水平分表-垂直分表

Mysql的分表技术 水平分割技术:->以qq用户登录为例: 创建uuid表 create table uuid (id int unsigned primary key auto_increm...
  • qq_17392301
  • qq_17392301
  • 2015-05-05 13:50
  • 2922
    个人资料
    • 访问:33308次
    • 积分:600
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:143篇
    • 译文:0篇
    • 评论:3条
    最新评论