MySQL EXECUTE statement log analysis tool

Since the project has been updated with a large version, it will not be long before the optimization task of SQL query queries, the Operation and Maintenance section to export a copy of the historical query SQL statement log to the development department to do optimization analysis work. Previously wrote a log analysis tool, can not be very good, so recently took a few days to improve the tool. Now, let me take a look at the development process:

First, the task analysis:

1. Tool Interface:

2. Specific requirements: classified statistics, ignoring parameters, debugging errors

3, the expected effect: parameter part use? Instead, the tool effect shows:

Ii. Development Code: (Read (Bgworke), write)

1. Variable settings:

Static stringResultfile;//log file AddressStaticdictionary<string,int> Seldic, Updic, Insertdic, Deletedic, Othdic, Keysdic;//DicStaticRegex patlike, Patequal, Patnumber, Patqo, Patbe, Patmanyspace, Patother, Patline, Patkeys;//RegularintType =5;//SQL Statement TypePatlike =NewRegex (@"(\s+like\s+) \ ' (. | \ n) +?\ '");p atequal=NewRegex (@"=\s*\ ' (. | \ n) +?\ '");p Atnumber=NewRegex (@"\d+");p Atqo=NewRegex (@ "\s+ (in) \s*\ ((\?\,) +");p atbe=NewRegex (@"\between\s+\ ' (.) +?\ ' \s+ (and) \s+\ ' (.) +?\ '");p Atmanyspace=NewRegex (@"\s{2,}");//SpacePatother =NewRegex (@"=\s* ("|null")");p Atline=NewRegex (@"\d+\s+ ((init| Connect| Quit| Statistics) | (Query\s+ (commit| begin| show| Set| Grant|flush )))", Regexoptions.ignorecase);p Atkeys=NewRegex (@"(\d+\s* (\d{2}\:*) +) \d+\s+ (query)");Keysdic=Newdictionary<string,int>();//Type Dictionary Keysdic.add ("s",1); Keysdic.add ("I",2); Keysdic.add ("u",3); Keysdic.add ("D",4);

2. Read the log:

 Private voidReadlog () {inti =0; StreamReader SR=NewStreamReader (Resultfile, Encoding.UTF8);            String Line; stringstrSQL =string.            Empty; intIsnumbercount =0;//            intLineNumber =0;//Bank Line number            stringNumber =string.            Empty;  while(line = Sr. ReadLine ())! =NULL)            {                if(Patline.ismatch (line)) {Continue; } Line=Line .                ToLower (); Line= line. Replace ("\ t",""); if(line. IndexOf ("Query") >-1) {Isnumbercount+=1; if(LineNumber = =1&& Isnumbercount = =2) {analyzesqlstring (strSQL); Isnumbercount=1; strSQL=""; }                    if(LineNumber! =1&& Isnumbercount = =1)                    {                        if(!string.                            IsNullOrEmpty (strSQL)) {analyzesqlstring (strSQL); strSQL=""; }} linenumber=1; strSQL= Patkeys.replace (line,"").                Trim (); }                Else{strSQL+=" "+Line .                    Trim (); LineNumber++; Isnumbercount=0; } I++; if(ckdebug.checked) {Thread.Sleep (1);                Bgworker.reportprogress (i, strSQL); }            }            if(strSQL! ="") {analyzesqlstring (strSQL); } Sr.            Close (); Sr.        Dispose (); }

3. Statement analysis

Private voidAnalyzesqlstring (stringstrSQL) {Keysdic.trygetvalue (convert.tostring (strsql[0]), outtype); if(ckignore.checked) {if(Type = =2)                {                    if(Strsql.indexof ("Values") >0) {strSQL= Strsql.substring (0, Strsql.indexof ("Values")); }                }                Else{strSQL= Patmanyspace.replace (strSQL," ");//Extra Space strSQL= Patother.replace (strSQL,"=?");//handling Name= ' situation strSQL= Patbe.replace (strSQL,"between?"); strSQL= Patnumber.replace (strSQL,"?"); strSQL= Patlike.replace (strSQL,"Like ?"); strSQL= Patequal.replace (strSQL,"=?"); strSQL= Patqo.replace (strSQL,"In ("); }            }}

3. Writing files

 Private voidWritelog (Dictionary<string,int> Data, Dictionary<string,int> Sort, StreamWriter SW,inti) {if(Data. Count >0) {Sort= data. OrderByDescending (o = o.value). ToDictionary (o = o.key, p =p.value); foreach(keyvaluepair<string,int> deinchsort) {SW. Write (DE. Value.tostring (). PadRight (7,' ') +"   "+ de. Key +"\ r \ n"); }            }        }

4. Final effect

Link: http://share.weiyun.com/b3b2a221231994207b8219cdc685970b (password: 8p1i)

Third, the final impression:

To tell the truth, this thing is not very useful, but when it does this, it deepens the learning of regular expressions.

MySQL EXECUTE statement log analysis tool