MYSQL极限插入

用过MYSQL数据库的人都知道,MYSQL的一般普通机器上用Insert语句操作,插入的速度2k条左右,如果随着数据的膨胀或者表数量的增多,速度会急剧下降。公司有个老项目,是记录仪器产生的数据,该仪器每秒产生一条数据,由于是很多台仪器,所以数据量很大,通过Insert语句根本达不到要求。

现在公司准备改进这个项目,计划在一台普通的pc机做为服务器的话(普通的机器就是主流配置一般机器:主频3G的CPU,4G的内存等等,差不多这样子),要求每秒要记录2000台设备产生的数据,就是每秒插入速度要达到2000+,每天要记录1亿七千多万条数据。我刚接手这个项目,感觉老板疯了,就是把MYSQL数据库累翻了也不可能完成的任务。试想,如此大的数据量,必定要对数据进行拆分,必须动态建立表,随着表数量的增多,数据库的膨胀,速度肯定变慢,更变态的是,这个仪器产生的数据中,每一条还要存储一个512字节二进制数据,算了一下,每天的数据大小是120G多,加上索引乱七八糟的150G都不止,并且更更变态是,要求这台服务器还要可以运行其他软件,当时一听心里骂老板真抠门,多弄几台服务器能死啊。对于一台普通的机器,对于一个小型数据库,如果能有此能力,真是不敢想象啊。

老板就是老板,执意为之。做为喽啰只有领命了。开始建议老板做集群,读写分离,老板一听要多买机器,增加成本,马上否定了,说多一台机器,要多加2000台仪器,nnd,那加服务器有什么用!没办法就慢慢摸索。

闲言少叙,在我的实践过程中,实验了10几种方法,历时半年有余,最终成功了,故分享给大家。以下数据都是在普通pc机上测试所得。

首先说明一下这个仪器。这是个做网络信号测试的仪器,每台仪器都会有一个IGM号和一个Port号,这两个号加起来就是该仪器的唯一标识。

由于数据量很大,需要拆分数据。通过无数次实验可得,MYSQL数据库每张表的记录达到千万级别的时候,速度会降低,数据库中的表的数量达到500张以上时速度也会下降,当达到几万张表的时候,速度相当于蜗牛,每秒仅可以Insert100条数据左右。这样粗略算了一下,只能根据时间纵向拆分,每小时一张表,这样每张表就只有720万条数据,2个月的表也不到1500张表,何况普通机器根本记录不了2个月的数据,最多一个月就了不得了,一个月的数据可是好几个T啊。

数据拆分方案定下来后,就是找插入数据的方案了,普通Insert肯定是不行了。LOAD DATA INFILE的速度是insert的数倍,所以采用LOAD做为方案是可行的。关于LOAD DATA INFILE的用法手册里说的很清除,不多说了。LOAD速度虽然快,但是也有问题,每条数据中的这512个字节的二进制数据不知道是什么东西,也许是图片,也许是个txt文件,怎么样保证把这个二进制数据写到文件中,再LOAD到数据库中是正确的呢。直接把这个二进制数据写下来肯定是错误的。我在实验中存了一张照片,再用

SELECT * INTO OUTFILE 'data.txt'  FIELDS TERMINATED BY ',' FROM table;

查看编码,一头雾水,不知所以,没办法只有啃源码了,还好MYSQL数据库是开源的能下到代码。通过编译调试,终于找到了SELECT * INTO OUTFILE的编码方式,如下:

#region ReadOnly Field

        private readonly byte ESCAPE_CHAR = 0x5C;     //'\'
        private readonly byte NULL_CHAR = 0x4E;     //'N'
        private readonly byte FIELD_SEP_CHAR = 0x22;  //ENCLOSED BY '"' 
        private readonly byte FIELD_TERM_CHAR = 0x2C; //FIELDS TERMINATED BY ','
        private readonly byte LINE_SEP_CHAR1 = 0x0D;   //LINES TERMINATED BY '\r'
        private readonly byte LINE_SEP_CHAR2 = 0x0A;   //LINES TERMINATED BY '\n'
        private readonly byte ZERO_CHAR = 0x30;     //'0'

        private readonly string LOAD_DATA_DIRECTORY = "LoadData";
        private readonly string FILE_NAME_JOINT_STRING = "___";
        #endregion
        private bool NEED_ESCAPING(byte x)
        {
            return (x == ESCAPE_CHAR ||
                x == FIELD_SEP_CHAR ||
                x == LINE_SEP_CHAR1 ||
                x == 0);
        }

        private void RecodeData(byte[] data)
        {
            if (data != null)
            {
                this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;
                this._buffer_cursor++;
                for (int i = 0; i < data.Length; i++)
                {
                    byte x = data[i];
                    byte y = (i + 1) < data.Length ? data[i + 1] : data[i];
                    if (NEED_ESCAPING(x))
                    {
                        this._bufferPool[this._buffer_cursor] = ESCAPE_CHAR;
                        this._buffer_cursor++;
                        this._bufferPool[this._buffer_cursor] = (x > 0 ? x : ZERO_CHAR);
                        this._buffer_cursor++;
                    }
                    else
                    {
                        this._bufferPool[this._buffer_cursor] = x;//x > 127 ? unchecked((byte)((int)x - 256)) : 
                        this._buffer_cursor++;
                    }
                }
                this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;
                this._buffer_cursor++;
            }
            else
            {
                this._bufferPool[this._buffer_cursor] = ESCAPE_CHAR;
                this._buffer_cursor++;
                this._bufferPool[this._buffer_cursor] = NULL_CHAR;
                this._buffer_cursor++;
            }
        }

this._bufferPool只是个容器,这里是个很大的byte[]数组,this._buffer_cursor是个表示位置的数字,以上代码就是源码中重新对二进制数据重新编码存储到本地的方式。因为我在调试源代码的时候,固定了数据的格式,即每项数据以双引号(")包括,以逗号分割(,),以换行(\r\n)结束,形式像:"每列值","每列值",..."每列值"\r\n(换行不显示)。知道了如何编码,以下的事情就好办了,直接把数据存到一个大容器里,然后设定一个最大阀值,每当到达阀值时就Load,代码如下:

        private void GetByteArrayFromIngressMonitorResultHeaderWithData(IngressMonitorResultHeader rlt, byte[] data)
        {
            int minuteSecond = rlt.TestTime.Minute * 60 + rlt.TestTime.Second;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;
            this._buffer_cursor++;
            byte[] arrayTemp = System.Text.Encoding.Default.GetBytes(minuteSecond.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.PortStatus.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.StartFreq.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.StopFreq.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.DwellTime.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.RBW.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this.RecodeData(data);
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.SampleIndex.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.IGMUnitNum.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_TERM_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            arrayTemp = System.Text.Encoding.Default.GetBytes(rlt.PortNum.ToString());
            AppendByteArray(arrayTemp);
            this._bufferPool[this._buffer_cursor] = FIELD_SEP_CHAR;;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = LINE_SEP_CHAR1;
            this._buffer_cursor++;
            this._bufferPool[this._buffer_cursor] = LINE_SEP_CHAR2;
            this._buffer_cursor++;
        }


        private void WriteDataByteArray(string fileName)
        {
            FileStream fs = new FileStream(fileName, FileMode.Create);
            try
            {
                fs.Write(this._bufferPool, 0, this._buffer_cursor);
            }
            catch
            {
            }
            finally
            {
                fs.Close();
                this._buffer_cursor = 0;
            }
        }


        private void LoadTable()
        {
            while (this._qFiles.Count > 0)
            {
                string file_table;
                lock (this)
                {
                    file_table = this._qFiles.Dequeue();
                }
                string[] fileName_tbName = file_table.Split(new string[] { FILE_NAME_JOINT_STRING }, StringSplitOptions.None);
                string fileName =
                    this.Load_File_Path.EndsWith("\\") ? (this.Load_File_Path + file_table) : (this.Load_File_Path + Path.DirectorySeparatorChar + file_table);//fileName_tbName[0];
                string tbName = fileName_tbName[1];
                if (File.Exists(fileName))
                {
                    string fileNameTemp = fileName.Replace(Path.DirectorySeparatorChar.ToString(),
                         Path.DirectorySeparatorChar.ToString() + Path.DirectorySeparatorChar.ToString());
                AGAIN:
                    string sql = @"LOAD DATA INFILE '" + fileNameTemp + "' INTO TABLE `" + tbName + "` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n';";
                    try
                    {
                        Vetronics.Data.MySqlHelper.ExecuteNonQuery(ref this._connection.Connection, CommandType.Text, sql, null);
                    }
                    catch (Exception ex)
                    {
                        if (this._connection.Connection.State != ConnectionState.Open)
                        {
                            this._connection.ReConnect();
                            goto AGAIN;
                        }
                    }
                    File.Delete(fileName);
                }
            }
        }

上面第一个方法是把自己定义的类,也就是要存入数据库中的数据转成byte[],第二个方法是到达上限阀值的时候把数组里面的内容存储到本地,这里一定要注意,不可以把内存里的内容重新用utf8之类的格式编码,一定要把内存里的内容原封不动的存到一个文档里,这里可以是记事本之类的记录容器;第三个方法就是Load数据了,上面那个_qFiles是个队列,刚开始打算用多线程,缓存数据与Load互不干扰,把每一次记录到本地的文件名插入队列,这样Load时依次出队Load数据,这样可以大大提高效率,但是由于机器性能问题,最后采用了单线程,保留了这个队列,以后改回来方便。
经测试,多线程的情况下开始可以达到1w条每秒,但是这只是缓存到了本地,至于Load的速度要看你机器的性能了;单线程的情况下,3000+每秒绝对没问题。这样问题解决了,感谢老板的抠门与执着,否则真的不敢想象,小小mysql能干如此大事。
如果你遇到了和我同样的问题,看到了我的这篇文章,如果对你有一些帮助,那么我会感到非常荣幸。也许文章写的比较槽糕,不够明白,如果有什么更好方案,可以提出来大家一起讨论,共同提高。
期待你的参与。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值