本文采用mycat的values批量插入方式进行测试，连写的数据量达到8000左右事务提交可以达到每秒75000左右数据量，也证实了Mycat的效率是小于或等于Mysql的性能。在实际生产中，因为访问量和并发问题使得效率下降，这也是数据库底层IO无法避免的困境，所以实际生产中多采用主备-读写分离的方式进行分片处理，可以多设置几个Mycat的主备节点。本文采用的是一主一备，单个Mycat节点的读写分离之Mysql InnoDB的测试。
理想测试
何为理想测试，只是理想状态的下的测试数据，可能不是很准确。
Mycat数据分片
schema.xml
<table name="userinfo" primaryKey="id" type="global" dataNode="dn1,dn2" /><table name="processtask" primaryKey="id" type="global" dataNode="dn1,dn2" />
dbBatch.sql
DROP TABLE IF EXISTS `userinfo`;CREATE TABLE `userinfo` ( `id` int(20) NOT NULL, `name` varchar(50) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `processtask`;CREATE TABLE `processtask` ( `id` int(12) NOT NULL AUTO_INCREMENT, `pmethod` varchar(50) DEFAULT NULL, `plimit` int(20) DEFAULT NULL, `ptime` int(20) DEFAULT NULL, `systime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Java测试类
BatchInsert
package demo.test;import java.sql.DriverManager;import java.sql.SQLException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Random;import org.junit.Before;import org.junit.Test;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;/** * 批量插入JDBC操作类 * * @author pengjunlin * */public class BatchInsert {private String driver = "com.mysql.jdbc.Driver";private String url = "jdbc:mysql://192.168.178.128:8066/TESTDB";private String batch_url = "jdbc:mysql://192.168.178.128:8066/TESTDB?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true";//要5.1.13以上版本的驱动包private String user = "root";private String password = "123456";private int limit=10;private String method="batchInsertWithTransaction";public String getMethod() {return method;}public void setMethod(String method) {this.method = method;}public int getLimit() {return limit;}public void setLimit(int limit) {this.limit = limit;}@Beforepublic void deleteAll(){Connection conn = null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(url, user, password);String sql = "DELETE FROM userinfo ;";conn.prepareStatement(sql).execute();} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}/** * 记录执行的时间 * * @MethodName: insertResult * @Description: * @param methodName * @param limit * @param timeStr * @throws */public void insertResult(String methodName,String limit,String timeStr) {Connection conn = null;PreparedStatement pstm = null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(url, user, password);SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String sql = "/*#mycat:db_type=master*/INSERT INTO processtask (pmethod,plimit,ptime,systime) VALUES('"+methodName+"','"+limit+"','"+timeStr+"','"+sdf.format(new Date())+"')";System.out.println(sql);pstm = (PreparedStatement) conn.prepareStatement(sql);pstm.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {if (pstm != null) {try {pstm.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}@Testpublic void batchInsertWithTransaction() {Connection conn = null;PreparedStatement pstm = null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(batch_url, user, password);StringBuffer sql = new StringBuffer("/*#mycat:db_type=master*/INSERT INTO userinfo(id,name,phone,address) VALUES");conn.setAutoCommit(false);// 即手动提交Random rand = new Random();int a, b, c, d;int index=1;for (int i = 1; i <= limit; i++) {a = rand.nextInt(10);b = rand.nextInt(10);c = rand.nextInt(10);d = rand.nextInt(10);if(index==limit){sql.append("("+i+",'boonya',"+"'188" + a + "88" + b + c + "66" + d+"','"+"xxxxxxxxxx_" + "188" + a + "88" + b + c+ "66" + d+"');");}else{sql.append("("+i+",'boonya',"+"'188" + a + "88" + b + c + "66" + d+"','"+"xxxxxxxxxx_" + "188" + a + "88" + b + c+ "66" + d+"'),");}index++;}System.out.println(sql.toString()); pstm = (PreparedStatement) conn.prepareStatement(sql.toString());Long startTime = System.currentTimeMillis();pstm.execute();conn.commit();// 手动提交Long endTime = System.currentTimeMillis();String timeStr=(endTime - startTime)+""; System.out.println("OK,用时：" + timeStr);insertResult("batchInsertWithTransaction", limit+"", timeStr);} catch (Exception e) {e.printStackTrace();} finally {if (pstm != null) {try {pstm.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);}}}}}BatchInsertThread
package demo.test;import java.util.concurrent.Executor;import java.util.concurrent.Executors;/** * 批量插入线程类 * * @author pengjunlin * */public class BatchInsertThread implements Runnable{BatchInsert batchInsert;static int loop=10;public BatchInsertThread(BatchInsert batchInsert){this.batchInsert=batchInsert;}public static void main(String[] args) {Executor executor=Executors.newSingleThreadExecutor();int limit=15630;for (int i = 0; i <= 50000; i++) {limit+=10;BatchInsert bi=new BatchInsert();bi.setLimit(limit);executor.execute(new BatchInsertThread(bi));}}public void run() { synchronized (batchInsert) {try {for (int i = 0; i < loop; i++) {System.out.println("第--"+i+"---次---------------------开始");batchInsert.deleteAll();batchInsert.batchInsertWithTransaction();System.out.println("第--"+i+"---次---------------------结束");}} catch (Exception e) {e.printStackTrace();}finally{}}}}BatchInsertDataParsor
package demo.test;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.mysql.jdbc.Connection;import com.mysql.jdbc.ResultSetMetaData;/** * 测试数据分析类 * * @author pengjunlin * */public class BatchInsertDataParsor {private String driver = "com.mysql.jdbc.Driver";private String url = "jdbc:mysql://192.168.178.128:8066/TESTDB?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true";//要5.1.13以上版本的驱动包private String user = "root";private String password = "123456";@Testpublic void queryData(){Connection conn = null;ResultSet rs=null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(url, user, password);String sql = "/*#mycat:db_type=slave*/SELECT id,pmethod,plimit,ptime,systime FROM processtask ;";long startTime=System.currentTimeMillis();rs=conn.prepareStatement(sql).executeQuery(sql);if(rs==null){throw new RuntimeException("ResultSet is null。。。。");}long endTime=System.currentTimeMillis();long cost=endTime-startTime;System.out.println("Totoal rows:"+rs.getRow()+" cost:"+cost+"ms");} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}try {if(rs!=null&&!rs.isClosed()){rs.close();}} catch (SQLException e) {e.printStackTrace();}}}@Testpublic void parseTimeTest(){Connection conn = null;ResultSet rs=null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(url, user, password);String sql = "/*#mycat:db_type=slave*/SELECT avg(ptime) avg,max(ptime) max,min(ptime) min FROM processtask;";rs=conn.prepareStatement(sql).executeQuery(sql);if(rs==null){throw new RuntimeException("ResultSet is null。。。。");}ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();//获取键名int columnCount = md.getColumnCount();//获取行的数量while (rs.next()) { for (int i = 1; i <= columnCount; i++) { System.out.println(md.getColumnName(i)+": "+rs.getString(i));//获取键名及值 }}} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}try {if(rs!=null&&!rs.isClosed()){rs.close();}} catch (SQLException e) {e.printStackTrace();}}}@Testpublic void parseLimitAndTimeTest(){Connection conn = null;ResultSet rs=null;try {Class.forName(driver);conn = (Connection) DriverManager.getConnection(url, user, password);String sql = "/*#mycat:db_type=slave*/SELECT plimit,avg(ptime) avg FROM processtask group by plimit;";rs=conn.prepareStatement(sql).executeQuery(sql);if(rs==null){throw new RuntimeException("ResultSet is null。。。。");}ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();//获取键名int columnCount = md.getColumnCount();//获取字段的数量while (rs.next()) { float limit=0,avg=0; for (int i = 1; i <= columnCount; i++) { float result=rs.getFloat(i); //System.out.println(md.getColumnName(i)+": "+result+"");//获取键名及值 if(i==1){ limit=result; }else{ avg=result; } } System.out.println("limit="+limit+"\t\t估算1s大概的批量插入量:"+(1000*limit)/avg); }} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}try {if(rs!=null&&!rs.isClosed()){rs.close();}} catch (SQLException e) {e.printStackTrace();}}}}测试数据
样本(limit)-每组10个实例样本批量插入平均耗时(avg)ms估算一秒内批量插入可插入的数量SELECT plimit as '样本(limit)-每组10个实例样本',avg(ptime) as '批量插入平均耗时(avg)ms' ,(1000*plimit/avg(ptime)) as '估算一秒内批量插入可插入的数量' FROM processtask group by plimit;1011.6862.069768097.878527.60747790100.677435.3877973012677222.22228050105.676231.06061042013776058.3942728095.875991.64939740128.775679.87577700101.875638.50699520125.975615.567910430139.174982.02739410126.174623.31488180109.774567.0009575077.574193.5484659089.473713.646510600143.873713.4919130123.973688.4584579078.873477.15748620117.573361.7021664090.773208.3793672091.873202.61449810134.173154.36247820106.973152.479616084.572899.40838850121.972600.49229140125.972597.2994711098.272403.25879470130.872400.6116996013872173.9139440130.972116.11921262017572114.28571563021772027.6498623086.572023.121410630147.672018.97028980124.871955.128210360144.171894.517710050139.971837.02641056014771836.7347696096.971826.6254649090.471792.0354954013371729.3233666093.171535.98288110113.471516.75498400117.571489.36179710135.971449.59531250017571428.5714473066.371342.3831683095.871294.36338060113.471075.83777450104.971020.01917440104.870992.36647520106.170876.5316467065.970864.94697570106.970813.84477650108.170767.807610260145.270661.15712200172.770642.73317490106.170593.77951058015070533.3333692098.270468.43188680123.370397.40477360104.670363.2887594084.670212.7669290132.570113.20759970142.270112.51767760110.770099.367711180159.570094.0439953013670073.52949770139.669985.6734436062.369983.94867380105.669886.3636675096.669875.77649160131.369763.8995498071.469747.899210120145.169745.003414870213.369714.01788370120.169691.9234625089.869599.109166809669583.33339500136.669546.120145206569538.461510080145.269421.48769050130.469401.84055550806937510270148.269298.245613320192.569194.805210220147.969100.74371050015269078.94747420107.569023.25587530109.169019.248415000217.468997.24019080131.668996.960515420223.568993.288610140147.268885.869610700155.468854.56899240134.268852.45911900173.268706.69758140118.568691.98317090103.368635.043614620213.168606.28817260105.968555.240815610227.768555.116412520182.768527.640910110147.668495.93510010146.268467.8523601087.868451.02511054015468441.558412300179.868409.3437493072.168377.253810410152.368351.93710900159.768252.9743455066.768215.892110720157.568063.492155808268048.78057750113.968042.14226840100.667992.047712330181.467971.3341693010267941.17657500110.567873.3032657096.867871.90088760129.267801.85768630127.367792.61596900101.867779.96077400109.267765.5678724010767663.551413370197.667661.943311690172.867650.46315460228.867569.930113120194.267559.2173597088.467533.936712680187.967482.70367980118.367455.62139720144.167453.1575439065.167434.71589620142.867366.9468578085.867365.96749760144.967356.7978577085.767327.8887780115.667301.03818820131.167276.8879975014567241.379314920222.167176.94737340109.467093.23589490141.567067.13788950133.567041.1985494073.767028.493914810221.366922.72937540112.766903.28318900133.166867.01739640144.266851.595362054.266789.6679613091.866775.5991655098.166768.6035437065.566717.557311280169.266666.66677690115.466637.7816407061.166612.11137740116.266609.294313260199.166599.69867020105.566540.2844497074.866443.850310590159.466436.637410170153.266383.8129910149.366376.4233430064.866358.0247132702006635011680176.166325.951211410172.266260.162610520158.866246.851412660191.366178.776811600175.466134.54969670146.366097.06089860149.266085.79096910104.666061.18559800148.466037.7358917013965971.223451068.465935.672514430218.965920.51169660146.665893.588458069.665804.597710200155.165764.023210100153.665755.20838410127.965754.495713300202.365743.9446619094.265711.25271228018765668.44928430128.765501.165512320188.365427.509335305465370.37048030122.965337.67298480129.865331.2789921014165319.1489395060.565289.256215100231.365283.182576088.365232.16319510145.865226.33746940106.465225.56398290127.265172.9567310112.265151.5152628096.465145.228213680210.165111.851512180187.464994.663812490192.264984.391312060185.764943.457212100186.564879.3566567087.464874.1419387059.764824.120615290235.964815.599815560240.364752.392814860229.564749.45538200126.764719.81061488023064695.652211370175.864675.767914280220.964644.6356382059.164636.20986740104.364621.284811460177.464599.774515210235.564585.987312370191.764527.9082553085.764527.42128230127.664498.432611330175.764484.917514010217.564413.7931392060.964367.816110960170.364357.01712050187.364335.29156608864318.181810470162.864312.039314070218.864305.3016448069.764275.466361709664270.83331478023064260.86969230143.764231.03696730104.864217.557314680228.664216.972912240190.764184.58319920154.664165.58867230112.764152.617615050234.864097.1039614095.864091.85810180158.964065.458220128.563968.871615440241.463960.23214690229.863925.15231048016463902.43912550196.463900.203712410194.363870.3037475074.463844.0861098017263837.20937580118.863804.713813430210.563800.475113480211.363795.55135610886375013360209.663740.4589790153.663736.97928700136.563736.263711960187.763718.700112160191.163631.606514520228.263628.39616770106.463627.819511260177.163579.898411010173.363531.448413570213.663529.962513110206.563486.682814550229.463426.329661509763402.061910390163.963392.31247720121.963330.5989559088.363306.90836880108.763293.468310760170.163256.907711800186.663236.8703942014963221.476513940220.563219.954610650168.563204.747814830234.963133.24827860124.563132.530113950221.363036.60199310147.862990.5277434068.962989.84038010127.262971.698111380180.862942.477913730218.362895.098512360196.862804.8781016016262716.049411430182.362698.848110090161.162631.905614730235.262627.551378060.462582.781511070176.962577.72758670138.662554.112614960239.362515.67071031016562484.848514890238.462458.053714560233.662328.767113530217.162321.510815550249.662299.6795602096.862190.082613880223.362158.531113030209.862106.768415250245.662092.83391260020362068.9655478077.161997.4069030145.761976.66441295020961961.722511980193.461944.1572485078.361941.251612770206.361900.14546620107.161811.39129840159.261809.045212790207.161757.60511390184.661700.9751359058.261683.84887270117.961662.42589900160.661643.835610070163.661552.56721045017061470.5882513083.561437.1257428069.761406.025812630205.861370.262454608961348.314614450235.661332.767413140214.361315.912311140181.761309.85149000146.961266.167513550221.361229.100815150247.561212.121212890210.661206.077911570189.161184.558411630190.161178.327237906261129.032313580222.261116.111615010245.861065.90726890112.961027.457915260250.161015.59388450138.561010.83038150133.661002.99414120231.660967.184856009260869.565243207160845.070412020197.760799.190711530189.760780.179211610191.160753.532214050231.460717.37257880129.860708.782710750177.160700.169414610240.760697.96437150117.860696.0951593097.760696.008212040198.660624.370610400171.760570.76314260235.560552.01711920196.960538.344312260202.660513.3268519085.860489.510512170201.660367.06358960148.560336.70031285021360328.638512700210.660303.893612080200.560249.376610570175.560227.920215130251.360206.92415490257.460178.710211030183.360174.57726240103.760173.57768690144.660096.818812750212.260084.8256468077.960077.021814300238.260033.58527630127.160031.4713389064.860030.864212940215.660018.552915040250.660015.96174620776000015570259.56000011500191.759989.56713510225.359964.491814190236.759949.30298170136.459897.3607445074.359892.328412780213.459887.535115120252.559881.1881982016459878.048810380173.459861.59179190153.659830.729211540192.959823.7429366061.259803.921612090202.259792.284915450258.759721.685315580260.959716.366411580194.159659.969112860215.759619.84249980167.559582.089613780231.459550.5618474079.659547.738710000168.159488.399812480210.259372.02668540143.959346.76869700163.559327.217113070220.459301.27046090102.759298.928910880183.659259.259311290190.659233.997915270257.859231.962812440210.159209.913890235.159081.2427320123.959079.90318710147.559050.8475968016459024.390211620196.959014.728311550195.858988.76412350209.458978.032512450211.158976.788310250173.858975.834313310225.758972.086814390244.158951.249511560196.158949.515613090222.358884.3905484082.258880.778613800234.558848.614110460177.958797.0771264021558790.69771310022358744.394612510213.158704.833412220208.258693.5639564096.158688.865812670215.958684.57626290107.258675.37319340159.358631.512913600232.158595.433446076.258530.1837416071.158509.1421530090.658498.8962813013958489.208610040171.758474.08279430161.358462.492314650250.658459.696714600249.858446.757412740218.158413.571812130207.758401.54076450110.558371.040711090190.158337.717488083.758303.46481148019758274.111712290210.958274.06357610130.658269.52538380143.958234.885312540215.558190.255211880204.258178.2566453077.958151.476313380230.158148.6311459025158127.4911220193.158104.60915620268.958088.508710850186.958052.434513810237.958049.600711520198.558035.2645376064.858024.69146600113.857996.485140006957971.0145369063.757927.786514080243.257894.736812470215.557865.429211470198.357841.654114130244.357838.722911200193.857791.5377417072.357676.348514480251.157666.268414740255.757645.67857920137.457641.921415140263.157544.65987410128.857531.0559367063.857523.511476082.857487.92278260143.857440.890111240195.757434.849313400233.457412.16811780205.257407.4074143502505740010130176.557393.76776210108.257393.7153431075.257313.82989870172.457250.585810101.557241.379313340233.157228.65721379024157219.9179550166.957219.8922565098.857186.234813450235.257185.374112730222.757162.10156510113.957155.399514150247.657148.62681554027257132.352915520271.757121.825510790188.957120.16949590167.957117.33176340111.157065.7066510089.457046.97991077018956984.1277990140.356949.394211230197.256947.261713060229.556906.31811341023656822.033912560221.156806.8747512090.256762.7494554097.656762.295115600274.956747.908312710224.156715.751915500273.356714.23341349023856680.672313010229.656663.763110030177.156634.669714950264.156607.3457425075.156591.211710020177.156578.204413870245.356543.0086718012756535.433113500238.856532.66339120161.456505.57626560116.156503.01461214021556465.116314760261.656422.01831193021256273.58498930158.756269.691211860210.856261.859611060196.856199.18712930230.156192.959614250253.856146.57211033018456141.3043500089.156116.722813230235.856106.87029580170.856088.993410073.156087.551312270218.856078.61065920105.656060.606113930248.756011.258514460258.256003.09848520152.255978.97515510277.155972.57318810157.455972.04578590153.555960.91218340149.155935.613713720245.355931.512415190271.655927.835113190235.955913.52271135020355911.33495088.655869.074514000250.755843.637813170235.955828.74111970214.755752.212411050198.355723.6518000143.655710.306413240237.755700.4628349062.755661.88215200273.155657.268414180254.855651.491413920250.355613.264113690246.455560.06497370132.755538.809312980233.855517.536413860249.855484.38758910160.955376.009915160273.955348.66741450026255343.51159060163.855311.35538460153.155258.001315240275.955237.40491491027055222.222215110273.755206.430414750267.755098.9914433078.655089.058511110201.755081.80479380170.654982.4151484027054962.9638360152.354891.661214270260.354821.3614040256.354779.555210510192.254682.622314440264.254655.5649460173.254618.93768280151.754581.4107884016254567.90127480137.154558.716310740196.954545.45451194021954520.5479714013154503.8168459084.354448.39869260170.154438.5655375068.954426.70549690178.254377.104411450210.754342.6673989018254340.659312910237.754312.158211300208.154300.81699100167.654295.942713000239.554279.749510290189.654272.1519406074.954205.60755700105.254182.509515230281.154180.00716700123.754163.2983639011854152.542414370265.454144.687312810236.654142.01189220170.354139.75347620140.854119.31828500157.154105.66521438026654060.15041346024954056.2249374069.254046.24288890164.554042.553253509954040.4047060130.754016.8324956017754011.299413040241.553995.859213290246.253980.503710240189.753979.96845480101.653937.0079272050.553861.386113420249.453809.14198650160.853793.53239940184.853787.87887050131.153775.7437463086.153774.680614720273.853761.8712690236.153748.4117531098.853744.93935490102.253718.19965830108.653683.241310830202.153587.3338610160.753578.095811100207.553493.97596980130.553486.5912000224.553452.115811950223.753419.758612400232.253402.239411130208.553381.2956430120.553360.995913830259.753253.754315030282.453222.37965390101.453155.818513960262.853120.243513620256.753058.044414410271.952997.425511440215.952987.4942507095.752978.05645820109.952957.233814640276.652928.41656530123.452917.342429081.152897.657246008752873.56327850148.552861.952910490198.652819.738213540256.552787.52445340101.252766.798413180249.952741.09649830186.452736.0515383072.752682.255813250251.752642.034212920245.552627.2912413078.652544.529314100268.752474.87914660279.652432.045814170270.452403.846211890226.952401.939213610259.852386.451114670280.252355.46047660146.452322.404412900246.752290.23111120212.852255.63918920170.752255.418914770282.852227.72289270177.552225.35211081020752222.22229320178.652183.650614700281.752183.1736491094.152178.533514630280.552156.8627419080.452114.427911740225.352108.37300140.751883.439914220274.351841.04995720110.451811.594213280256.551773.879115060291.451681.5374386074.751673.360113210255.751662.10414930289.451589.495511870230.151586.266815590302.651520.158612430241.351512.639914360278.851506.456214060273.151482.973313820268.751432.82475440105.851417.76946380124.251368.76019570186.351368.76011402027351355.31147940154.751325.14545710111.351302.785314570284.251266.71369880193.251138.7164456089.251121.076214940292.351111.871410870212.851080.827115380301.351045.469613520264.951038.12767390144.851035.91166100119.651003.3445466091.450984.682710680209.650954.19851416027850935.251814140277.750918.25711532030150897.01461090.650883.002214980294.950796.880315090297.150790.97958100159.550783.699113740270.650776.053210840213.550772.83377900155.750738.59996610130.450690.18414540286.950679.679311650230.150630.160814510286.750610.394115020296.850606.46911210221.650586.64266040119.450586.26477040139.250574.712612030238.250503.778313330264.250454.201414790293.250443.383414320284.350369.328215310304.250328.7311254050.550297.029710660212.250235.62688240164.450121.65459650192.650103.842212110241.750103.4346780135.450073.855214580291.350051.49336710134.25000010610212.549929.411812610252.949861.60547190144.249861.30377000140.449857.549915430309.549854.604210060201.949826.646914330287.749808.82868080162.349784.3510710215.249767.65813020262.349637.8193340068.549635.036514820298.649631.614212880259.749595.68736630133.749588.63138090163.349540.7226911018449510.86967010141.649505.6497648013149465.6489223045.149445.676310280208.349351.896312970262.949334.34777550153.149314.173713710278.449245.689714210288.849203.601114030285.249193.5484348070.849152.542415300311.449132.948356072.549103.44837250147.749085.985114290291.249072.8022226046.149023.861210210208.349015.8425268054.748994.5155384078.548917.1975480098.248879.8371233047.748846.960211360232.748818.220914470296.548802.69811547031748801.261812760261.548795.411113640279.648783.97719010184.848755.411310550216.548729.792110530216.248704.9029182037.448663.10167870161.848640.296710890223.948637.784715170312.348575.08818320171.348569.76071272026248549.6183454093.648504.27357930163.548501.529111160230.248479.5839850203.348450.5657358073.948443.8435900121.848440.065712340254.948411.1416441091.148408.342514800305.848397.645513200273.548263.254114340297.648185.483914530301.648176.3926368076.448167.53936330131.548136.882114850308.648120.5444260054.148059.1497438091.248026.315815530323.947946.897211420238.347922.78647210150.547906.97671365028547894.73689930207.447878.495713980292.647778.537313390280.447753.20977810163.847680.09771363028647657.34276970146.547576.79187590159.647556.39114200298.647555.257911770247.647536.34899610202.347503.70745030105.947497.639312990273.947426.0679904019147329.842912580265.947311.019212230258.647293.1168165034.947277.9377960168.447268.40861095023247198.2759409086.747174.163812190258.747120.216511910253.546982.248511660248.246978.243413080279.546797.85339090194.346783.32488440180.546759.002813910297.546756.302515370328.946731.529313050279.346723.9527784016846666.6667527011346637.16818350179.346569.99446000128.946547.71144990107.346505.125812800275.446477.8504263056.646466.43118790189.346434.231411720252.646397.4663225048.546391.75268740188.446390.658211670251.846346.306610920235.746330.080614970323.446289.424911640251.546282.306215480334.846236.55915240113.446208.112911190242.346182.418514400312.246124.27938390181.946124.244112070261.746121.5132266057.746100.51995690123.546072.874510990238.746041.0557385083.745997.610512120263.645978.755714310311.345968.519113350290.545955.2496119025.945945.94598600187.345915.64347460162.645879.4588294064.145865.834610370226.145864.6617194042.345862.88421315028745818.815315350335.345779.898610350226.145776.2052360078.745743.329115180331.945736.66776410140.345687.8118237051.945664.739914230311.945623.597311400250.145581.767312380271.645581.7378405088.945556.80548730191.745539.906110730235.745523.97118250181.345504.68846810149.845460.614215360337.945457.235912590277.145434.8611164036.145429.36298420185.445415.31828510187.445410.88585570122.745395.2734650102.545365.85379400207.345344.910812530276.445332.85091019022545288.8889245054.145286.506537108245243.9024550012245081.96728940198.545037.783413470299.245020.05358530189.545013.1926344076.544967.3203930020744927.53626800151.544884.48847830174.644845.360811710261.244831.546711700261.544741.873811040246.844732.5778020179.444704.570813670305.844702.4199710015944654.0881346077.544645.1613373083.844510.73995010112.644493.78335410121.644490.131610940245.944489.629912650284.644448.348612870289.744425.26755520124.344408.688715280345.344251.37564490101.544236.45325840132.144208.9326831018844202.127711310256.144162.4365195044.244117.6471341077.444056.84758870201.444041.7086300143.243994.4134265060.343946.9325320121.143930.63584690106.843913.85776860156.443861.89269250210.943859.64914440101.343830.20735180118.243824.02718830201.643799.603214990342.343791.99538780200.843725.09965080116.243717.728120104643695.6522247056.643639.5765620128.843633.54045380123.343633.41447170164.543586.62616580151.343489.7555687015843481.012712460286.743460.062811590266.843440.7796122028.143416.370111810272.143403.16064470103.243313.953510800249.443303.92946790156.943275.97225105843275.86215420125.343256.185213130303.743233.45415330123.343227.89945260121.743221.0353241055.843189.96425230121.243151.8152381088.343148.35799070210.643067.42644720109.743026.43576690155.742967.244712210284.442932.489510690249.342880.064210970256.142834.8301420098.142813.45578660202.642744.323811490268.942729.6393816019142722.513113990327.642704.5177361084.642671.394813850325.242589.17596050142.242545.710313750323.242543.31688210193.142516.830711340267.242440.1198286067.442433.23449780230.642411.101513560319.842401.500912310290.642360.6332370087.442334.09611082025642265.625183043.342263.279411270266.942225.5526414098.242158.859514900353.842114.188810780256.142092.93249280220.842028.985513970332.542015.037615220362.641974.627711990286.341879.1477281067.141877.79434920117.641836.734710300246.541784.989910860260.141753.171915080361.341738.167714420345.641724.53710230245.541670.06117950190.941644.8402307073.841598.9167290175.441562.14374820116.141515.9345198047.741509.43411750283.241490.11311760283.841437.63217890190.541417.32287350177.541408.45079180221.941369.986511840286.341355.2218521012641349.206314710355.841343.4514258062.741148.32549020219.441112.12410910265.741061.34745630137.241034.98548860216.340961.627495023.240948.2759197048.240871.3693351085.940861.4668393096.240852.3909243059.540840.336110150248.740812.223613760338.440661.9385394096.940660.4747622015340653.5948626015440649.3506106026.140613.02689370231.240527.68176670164.640522.478713160324.940504.77078560211.440491.9584204050.440476.19056180152.740471.51289302340434.7826399098.840384.61541201029840302.01345450135.540221.402212250305.240137.61474810119.940116.76413700341.940070.196131032.740061.1621292073.339836.289212570316.339740.752513900349.939725.63594030101.639665.3543391098.639655.172411102839642.8571352088.839639.63965280133.239639.63965360135.439586.410617804539555.55565860148.339514.49761409035739467.7871277070.239458.68954240107.639405.2045239060.839309.210512960331.839059.674511820303.238984.168923005938983.050812390318.938852.3048188048.438842.97527730199.138824.7112191049.238821.1382180046.438793.10349600247.538787.8788319082.338760.6318298076.938751.625510640274.638747.2688222057.338743.4555252065.138709.67749990258.138705.927912420321.138679.5391297076.838671.875262067.838643.067814490375.138629.6987167043.338568.129311150289.238554.63354430115.238454.861115070392.138434.0729176045.838427.9476116030.238410.596287074.838368.98411000287.238300.835710670278.638298.63610340270.138282.11775370140.538220.64068300217.438178.4729301078.938149.55646350166.538138.138113840363.438084.75515880154.738009.049811510303.137974.26591032027237941.176514110372.837848.7124343090.837775.330413590360.137739.51684020106.637711.0694135035.937604.4568253067.437537.0928970239.737421.7772144038.537402.5974120032.137383.17767330196.237359.83698570229.637325.78411020295.437305.3487347093.137271.75084150111.737153.08866400172.337144.5154186050.137125.74857130192.237096.774212840346.237088.38826420173.237066.97467430200.936983.5739136036.836956.5217123033.336936.9369130035.236931.81826320171.336894.3374323087.636872.1461310084.136860.8799169045.936819.1721185050.436706.34927120194.336644.364411170305.236598.9515849023236594.82768640236.336563.690211830324.136501.079994025.836434.10855150141.436421.4993242066.636336.3363256070.736209.33523640100.636182.902612830354.936151.02859480262.336141.82238720242.335988.4441244067.835988.20064010111.835867.6208288080.335865.504413220368.635865.4368124034.635838.15035730159.935834.89681125031435828.02556760188.935786.1302156043.635779.81653770105.435768.500913440375.935754.189914240398.735716.077313770385.635710.58098580240.435690.5158279078.235677.749412150340.835651.40854890137.635537.790715330431.735510.7714200056.435460.99299450266.635446.36164260120.535352.6971289082.135200.97445290150.535149.5017224063.835109.71795040144.234951.45638070231.434874.6759574016534787.878888025.334782.6087284081.734761.32196470186.334728.93186440185.934642.28085680164.534528.8754228066.134493.19216120177.634459.45958040234.334314.980811080323.334271.57446500190.234174.55316070177.734158.69444120120.734134.217116004734042.5532145042.634037.5587229067.433976.261119005633928.5714875025833914.72878990265.733835.15249360276.933802.816999029.333788.39597070209.733714.8307159047.233686.4407995029633614.86496460192.233610.82213980118.833501.683511850354.333446.23215410460.833441.84034900146.733401.49978470253.833372.734411320339.533343.151715390462.533275.67577670230.833232.235710930329.133211.789712820386.433178.05387910238.833123.9531127038.433072.9167216065.433027.5229299090.633002.20756650201.632986.11118550259.632935.285131609632916.6667139042.332860.5201133040.532839.5062486014832837.837811790359.832768.20469630294.332721.7125172052.632699.6198118036.132686.98065160157.932678.91079200282.332589.44397800239.932513.54735050155.732434.1683259079.932415.51947770240.832267.441977023.932217.5732157048.832172.13115400167.932162.0012320099.732096.28894830150.532093.0233143044.632062.7803248077.432041.3437246076.832031.258770275.831798.4046179056.431737.5887278087.631735.15984700148.431671.159173054.831569.34314790151.931533.90395170164.231485.99276310200.831424.302815400490.431402.9364269085.931315.48317970254.931267.16367030225.531175.1663291093.531122.994798031.531111.11113900125.531075.697213660439.931052.5119283091.530928.96179150296.230891.289729309530842.10537220234.430802.0478107034.830747.12646080198.730598.892872023.630508.4746153050.230478.08766950229.430296.42557600251.230254.77716990231.330220.49295890195.830081.7169390312.930009.58776540218.629917.657885028.529824.5614274092.129750.27143220108.729622.815110620358.629615.1701276093.229613.7339147049.829518.0723177060295003000101.729498.52518120275.329495.09635870199.929364.6823264090.429203.5398203069.629166.66675110175.429133.409413404629130.43485560191.729003.651511303928974.359270093.328938.90683040105.128924.83353500121.228877.88783110107.828849.7217109037.828835.97885980207.928763.8288162056.528672.5664207072.528551.7241126044.228506.78737470262.228489.7025720025328458.4986270220.828396.73915990211.128375.1776236083.228365.38462900102.328347.9961193068.428216.37434870172.828182.870496034.228070.1754218077.927984.595682029.427891.1565137049.227845.5285189067.927835.0515261094.127736.4506170061.627597.40266360230.727568.2705163059.327487.35245800211.527423.16783020110.227404.7187108039.627272.727315340562.827256.5743148054.327255.9853354013027230.76927080261.127116.0475161059.427104.3771267098.926996.96662820105.526729.8578217081.226724.137973027.426642.3358175065.726636.225311730440.726616.74618800330.826602.17654710177.326565.1438155058.826360.5442181068.926269.9565210080.226184.5387199076.126149.80293390130.425996.93252208.525882.3529257099.525829.14577640295.925819.533619607625789.4737202078.525732.48417160278.325727.6321128049.825702.81123080120.625538.97186060237.425526.53754230165.825512.665997038.225392.6702141055.825268.8172102040.525185.18525470217.425160.9936321012825078.12586034.325072.8863221088.724915.44535910237.524884.2105421017124619.8839330379.724572.03066520265.624548.1928112045.724507.6586682028024357.14292550104.724355.30096370263.124211.3265100041.424154.5894132054.824087.59124220177.123828.34565850245.723809.5238457019223802.08332380100.123776.22386200262.523619.04765060214.723567.7692960125.923510.72287510319.923476.08635220222.523460.6742168071.823398.32875950254.523379.17496110261.423374.13936030259.223263.88895960256.423244.92982500107.723212.6277158068.123201.17475140221.623194.94582109.123076.9231363015822974.68358190359.322794.32234110180.622757.475176033.622619.047623010.222549.01964420196.222528.032624010.722429.906578035.122222.22225430245.222145.1876372016822142.85718330380.421898.0021184084.121878.715846021.221698.11327710356.721614.80242270105.821455.57664500209.921438.7804240011221428.57144960232.421342.51292710127.821205.00782190104.320997.12374770228.420884.4133212010220784.313770033.720771.51348270403.820480.43592730133.420464.76765510269.720430.1075174085.520350.87722140105.920207.74324270211.720170.05290044.720134.22822800139.120129.40332340116.820034.24662320115.920017.256331015.5200002110105.719962.1572080104.819847.328280040.519753.08643650184.819751.082347023.819747.8992187094.819725.73845090260.519539.34743050156.919439.13328904619347.8261129067.119225.03733180167.119030.520642022.119004.524930015.918867.9245104055.218840.5797114060.718780.88963880208.318626.98032090112.618561.2789440023818487.395888048118461.5385103056.118360.071348026.218320.61073570195.418270.2149152083.218269.230836019.818181.81821709.418085.1064138076.717992.17733550201.717600.39663340189.917588.2043115066.317345.3997146084.417298.57823970229.917268.3776101058.717206.1329166096.617184.265151089.716833.89073030180.616777.4086105062.816719.745245027.316483.51656850420.616286.257784051.816216.21621509.316129.032379049.415991.90283420215.515870.06965250330.915865.82051901215833.33332150135.915820.45625020317.915791.12933060194.315748.842231014815608.10813090198.815543.25964040260.415514.59297560498.415168.5393121080.115106.1174142094.115090.329411007315068.49323150209.315050.1672313020815048.076920013.414925.373134022.914847.16162950201.214662.02783120217.314358.030491063.514330.708728019.614285.71434180296.314107.323751036.713896.45781500108.913774.10471208.813636.36362050152.113477.9757105313396.22644640352.713155.65642850216.713151.82283280249.713135.762910440799.113064.69782200169.712964.054218013.912949.64033290256.112846.54431108.612790.6977335026312737.64264350343.312671.13311400111.512556.05381920156.312284.06913800312.312167.787487071.912100.139174061.811974.1116013.511851.85195200440.911794.05763360287.111703.239341036.211325.96692350210.211179.828713011.811016.94923960367.410778.443175069.710760.40173140295.310633.25431250117.710620.220943040.610591.133807.710389.610433032.410185.18522130211.610066.16261170124.19427.880744048.39109.7308390439069.76743260362.19003.037840804558967.03329032.68895.70552750309.38891.044381094.88544.303868079.98510.638335042.18313.5392830100.58258.706534504527632.743449065.47492.354714902067233.009793501305.37163.10433250456.67117.827432045.37064.01773320473.87007.1762490357.76961.14061540230.46684.0278609.26521.73911710265.96430.989127042.16413.30173300519.56352.261837058.76303.23689201506133.33333330546.86089.97813380560.96026.029650084.25938.242326043.85936.073114023.65932.2034406.85882.35297011.95882.3529690119.75764.4113170563.55625.55462060369.35578.120852095.45450.733840074.45376.3441509.55263.157925047.85230.1255530101.85206.28683270657.44974.1406570114.94960.83553310701.14721.1525610152.34005.2528630164.13839.1225560147.93786.34213083750600167.13590.6643620174.43555.04596701983383.8384550164.23349.5737380119.13190.5961540181.32978.4887640218.52929.0618660248.92651.6673590236.12498.94119041.22184.466580289.32004.839333701797.11875.243432401745.71855.98910062.41602.5641650543.31196.39242027.4729.927客观测试
客观测试是考虑多种情况下的测试，更接近实际情况。
GitHub测试工具
https://github.com/boonyachengdu/SQLTools
Mysql与Mycat插入性能测试
单线程读写性能测试
1W测试数据
批次数量
数据样本
MySQL(s)
Mycat(s)
1
10000
0.835
2
5000
0.954
1.344
3
3333
1.067
1.619
4
2500
0.096
0.736
5
2000
0.829
0.863
6
1666
0.842
0.771
7
1428
0.816
0.963
8
1250
0.84
0.785
9
1111
0.854
0.988
10
1000
0.816
0.748
15
666
0.823
1.043
20
500
0.878
0.745
25
400
0.834
0.753
30
333
0.814
1.467
40
250
0.897
0.772
50
200
0.827
0.787
100
100
0.985
0.859
10W测试数据
批次数量
数据样本
MySQL(s)
Mycat(s)
1
100000
7.856
-
2
50000
7.969
7.484
3
33333
7.953
15.285
4
25000
8.856
20.125
5
20000
7.808
8.353
6
16666
7.508
8.977
7
14280
7.712
7.129
8
12500
7.275
7.214
9
11110
7.328
7.202
10
10000
7.166
6.993
15
6666
7.14
7.133
20
5000
6.984
7.132
25
4000
7.228
7.101
30
3333
7.017
7.381
40
2500
6.556
7.583
50
2000
7.242
7.579
100
1000
6.823
8.451
多线程读写性能测试
1W测试数据
线程数量
数据样本
MySQL(s)
Mycat(s)
1
10000
1.517
-
2
5000
1.637
1.343
3
3333
1.569
1.797
4
2500
1.609
1.457
5
2000
1.551
1.002
6
1666
1.554
0.859
7
1428
1.593
0.868
8
1250
1.563
0.847
9
1111
1.556
0.851
10
1000
1.6
0.882
15
666
1.633
0.657
20
500
1.677
1.11
25
400
1.622
0.877
30
333
1.62
0.92
40
250
1.705
0.915
50
200
1.699
0.947
100
100
2.109
1.094
10W测试数据
线程数量
数据样本
MySQL(s)
Mycat(s)
1
100000
8.949
-
2
50000
8.969
-
3
33333
9.101
-
4
25000
8.951
-
5
20000
9.094
-
6
16666
8.353
-
7
14280
8.471
-
8
12500
8.8
-
9
11111
8.798
-
10
10000
8.784
-
15
6666
8.828
7.86
20
5000
8.636
7.807
25
4000
8.928
14.183
30
3333
8.558
15.309
40
2500
8.375
7.493
50
2000
8.995
8.991
100
1000
9.243
7.376
测试对比和结论
测试数据对比
测试属性
MySQL-InnoDB(v)
MYCAT(v)
多线程插入1w
6000/s左右
10000/s左右
多线程读写10w
11000/s左右
13000/s左右
单线程读取1w
10000/s左右
10000/s左右
单线程读写10w
15000/s左右
13000/s左右
测试结论
1、 Mysql单线程写入和Mycat性能相当。Mycat批量插入数据的时候不能一次超过9500条，需分批次插入。
2、 多线程读写性能Mycat优于MySQL。
3、 Mycat批量处理大于10000可能产生插入异常。
4、总体来看Mycat性能与Mysql性能相差无几。
