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