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能干如此大事。
如果你遇到了和我同样的问题，看到了我的这篇文章，如果对你有一些帮助，那么我会感到非常荣幸。也许文章写的比较槽糕，不够明白，如果有什么更好方案，可以提出来大家一起讨论，共同提高。
期待你的参与。