SOURCE Analysis mycat----Optimization support ER multipart multiple-statement insertion (based on 1.6)

1, er shard support multi-sentence Insert optimization 1.1 problem throws a classic usage scene, Order table (T_order) with order Details (T_order_item), Order schedule er shard with order table. The code hierarchy generally has the following implementation (Orderdao) void Createorder (order order);  void Createorderitem (Interger orderId, orderitem item); @1 void Createorderitem (Integer orderid,list<orderitem> items); @2 mapping file level (MyBatis implementation) <insert id = "Createorderitem": > INSERT INTO T_order_item values (field values) </insert> in Java code, call the Createorderitem method multiple times to complete the creation of multiple OrderItem.
The second implementation method <insert id = "Createorderitem": > <foreach * * * > INSERT INTO T_order_item (field list) VALUES (field values) </foreach> &LT;/INSERT&G T In the second form, there is a special word description (multi-statment) in the MySQL protocol. In Mycat Branch 1.6, the current ER shard only supports the first method, and does not support multi-multi-statement support.

SOURCE Analysis:

public static Boolean processerchildtable (final Schemaconfig schema, final String Origsql, Final Serverconnection SC) throws sqlnontransientexception {String tableName = Stringutil.gettablename (orig
		SQL). toUpperCase ();
		Final Tableconfig TC = Schema.gettables (). get (TableName); Determines whether the child table, if not, will only return FALSE if (null! = TC && tc.ischildtable ()) {final Routeresultset RRs = new Routeresultset
			(Origsql, Serverparse.insert);
			String Joinkey = Tc.getjoinkey (); Because it is an INSERT statement, use Mysqlinsertstatement for parse mysqlinsertstatement insertstmt = (mysqlinsertstatement) (new    Mysqlstatementparser (Origsql)). Parseinsert ();
			@1//Judge condition integrity, obtain the Joinkey column in the parsed statement column, index int joinkeyindex = Getjoinkeyindex (Insertstmt.getcolumns (), Joinkey);
				if (Joinkeyindex = =-1) {String inf = "Joinkey not provided:" + tc.getjoinkey () + "," + insertstmt;
				Logger.warn (INF);
			throw new Sqlnontransientexception (INF); }//child table does not support BULK INSERT if (ISMULtiinsert (insertstmt)) {String msg = "ChildTable Multi insert not provided";
				Logger.warn (msg);
			throw new Sqlnontransientexception (msg);
			}//Gets the value of joinkey String joinkeyval = Insertstmt.getvalues (). GetValues (). Get (Joinkeyindex). toString ();
			Fix bug #938, when the value of the associated field is a char type, remove the front and back "'" String realval = joinkeyval; if (Joinkeyval.startswith ("") && Joinkeyval.endswith ("'") && joinkeyval.length () > 2) {realval =
			Joinkeyval.substring (1, Joinkeyval.length ()-1);

			} String sql = Insertstmt.tostring (); Try to route by ER parent partion key//If it is a Level two child table (the parent table no longer has a parent table), and the Shard field is exactly the Joinkey field, call Routebyerparentkey Routeresultset t
			Herrs = Routerutil.routebyerparentkey (SC, schema, Serverparse.insert, SQL, RRS, TC, realval);
				if (Therrs! = null) {Boolean processedinsert=false; Determine if global serial number if required (Sc!=null && tc.isautoincrement ()) {String PrimaryKey = tc.
                    GetPrimaryKey (); ProcesSedinsert=processinsert (Sc,schema,serverparse.insert,sql,tc.getname (), PrimaryKey);
                    } if (Processedinsert==false) {Rrs.setfinishedroute (true);
                Sc.getsession2 (). Execute (RRs, Serverparse.insert);
			} return true; }//route by SQL query root parent ' s datanode//If it is not a level two child table or the Shard field is not the Joinkey field result is empty, then start an asynchronous thread to go back to the background shard query out Datanode//As long as the query out the previous
			The corresponding value of the Parentkey field of the Level table in which Shard can be final String findroottbsql = Tc.getlocatertablekeysql (). toLowerCase () + joinkeyval;
			if (logger.isdebugenabled ()) {Logger.debug ("Find root parent ' s node sql" + Findroottbsql);
					} listenablefuture<string> listenablefuture = Mycatserver.getinstance (). Getlisteningexecutorservice (). Submit (New callable<string> () {@Override public String call () throws Exceptio n {fetchstorenodeofchildtablehandler Fetchhandler = new Fetchstorenodeofchildtablehandler ();//Return FetchHand Ler.execute (Schema.getname (), FIndroottbsql, Tc.getrootparent (). Getdatanodes ());
				Return Fetchhandler.execute (Schema.getname (), Findroottbsql, Tc.getrootparent (). Getdatanodes (), SC);


			}
			}); Futures.addcallback (Listenablefuture, New futurecallback<string> () {@Override public void onsuccess (String Result) {//The result is empty, proving that the record does not exist in the previous table, failed if (Strings.isnullorempty (Result)) {StringBuilder s = new Stringbuilde
						R (); Logger.warn (S.append (Sc.getsession2 ()). Append (Origsql). toString () + "ERR:" + "can ' t find (root) parent sharding n
						Ode for sql: "+ origsql); if (!sc.isautocommit ()) {//is under a transaction failure, you must roll back sc.settxinterrupt ("Can ' t find (root) parent sharding node for sql:" + ori
						GSQL);
						} sc.writeerrmessage (Errorcode.er_parse_error, "can ' t find (root) parent sharding node for sql:" + origsql);
					Return  } if (logger.isdebugenabled ()) {Logger.debug ("found partion node for child table to insert" + result + "sql
					: "+ origsql);
			}		Find the Shard and insert it (as well as the other, you need to determine whether global self-increment ID is required) boolean processedinsert=false; if (Sc!=null && tc.isautoincrement ()) {try {String Primaryk
							ey = Tc.getprimarykey ();
						Processedinsert=processinsert (Sc,schema,serverparse.insert,origsql,tc.getname (), PrimaryKey);
		                    } catch (Sqlnontransientexception e) {logger.warn ("sequence Processinsert error,", E);
						Sc.writeerrmessage (errorcode.er_parse_error, "sequence Processinsert ERROR," + e.getmessage ()); }} if (Processedinsert==false) {Routeresultset Executerrs = R
    					Outerutil.routetosinglenode (RRs, result, origsql);
                    Sc.getsession2 (). Execute (Executerrs, Serverparse.insert);
					}} @Override public void OnFailure (Throwable t) {StringBuilder s = new StringBuilder (); Logger.warn (S.append (Sc.getsession2 ()). Append (origsql). TostriNg () + "ERR:" + t.getmessage ());
				Sc.writeerrmessage (Errorcode.er_parse_error, t.getmessage () + "" + s.tostring ());
					}}, Mycatserver.getinstance ().
			Getlisteningexecutorservice ());
		return true;
	} return false; }

The problem arises here, where the incoming SQL pinning is assumed to have only one insert INTO statement, resulting in a cluster of INSERT into statements that only the first statement takes effect. Next, fix the code and analyze the method. The fix code given is as follows:

/** * This method, returns whether the ER Word table * @param schema * @param origsql * @param sc * @return * @throws sqlnontransientexception * * Note: * edit by DING.W at 2017.4.28, the main processing client_multi_statements (insert into; INSERT INTO) case * currently only Support for MySQL, and com_query all INSERT statements in the request package are either all ER tables, or all are not * */public static Boolean processerchildtable (final S Chemaconfig schema, Final String origsql, final serverconnection SC) throws sqlnontransientexception {MYSQL
		Statementparser parser = new Mysqlstatementparser (origsql);      list<sqlstatement> statements = parser.parsestatementlist (); @1 if (statements = = NULL | | statements.isempty ()) {throw new Sqlnontransientexception (String.Format ("Invalid SQL statement:%
		S ", Origsql)); } Boolean erflag = false;          Is ER table for (sqlstatement stmt:statements) {//@2 mysqlinsertstatement insertstmt = (mysqlinsertstatement) stmt; @3 String tableName = Insertstmt.gettablename (). Getsimplename (). TouppercasE ();
			Final Tableconfig TC = Schema.gettables (). get (TableName);
				if (null! = TC && tc.ischildtable ()) {//@4 Erflag = true;
				String sql = insertstmt.tostring ();
				Final Routeresultset RRs = new Routeresultset (sql, Serverparse.insert);
				String Joinkey = Tc.getjoinkey (); Because it is an INSERT statement, use Mysqlinsertstatement for parse//mysqlinsertstatement insertstmt = (mysqlinsertstatement) (new
				Mysqlstatementparser (Origsql)). Parseinsert ();   Judge the condition integrity, obtain the Joinkey column in the parsed statement column, index int joinkeyindex = Getjoinkeyindex (Insertstmt.getcolumns (), Joinkey);
					@5 if (Joinkeyindex = =-1) {String inf = "Joinkey not provided:" + tc.getjoinkey () + "," + insertstmt;
					Logger.warn (INF);
				throw new Sqlnontransientexception (INF);
					}//child table does not support BULK INSERT if (Ismultiinsert (insertstmt)) {String msg = "ChildTable Multi insert not provided";
					Logger.warn (msg);
				throw new Sqlnontransientexception (msg); }//Gets the value of joinkey String joinkeyval = InserTstmt.getvalues (). GetValues (). Get (Joinkeyindex). toString ();
				Fix bug #938, when the value of the associated field is a char type, remove the front and back "'" String realval = joinkeyval;  if (Joinkeyval.startswith ("") && Joinkeyval.endswith ("'") && joinkeyval.length () > 2) {realval =
				Joinkeyval.substring (1, Joinkeyval.length ()-1); }//try to route by ER parent partion key//If it is a Level two child table (the parent table no longer has a parent table), and the Shard field is exactly the Joinkey field, call Routebyerparentkey RouteR
				Esultset Therrs = Routerutil.routebyerparentkey (SC, schema, Serverparse.insert, SQL, RRS, TC, realval);
					if (Therrs! = null) {Boolean processedinsert=false; Determine if global sequence numbers are required if (Sc!=null && tc.isautoincrement ()) {String PrimaryKey = t
	                    C.getprimarykey ();
	                Processedinsert=processinsert (Sc,schema,serverparse.insert,sql,tc.getname (), PrimaryKey);
	                    } if (Processedinsert==false) {Rrs.setfinishedroute (true); Sc.getsessIon2 (). Execute (RRs, Serverparse.insert);
	                @6}//return true;
				Continue to deal with the next continue; }//route by SQL query root parent ' s datanode//If it is not a level two child table or the Shard field is not joinkey field result is empty, start an asynchronous thread to go back to the background shard query out Datanode//As long as the query The corresponding value of the Parentkey field in the first table is in which Shard, the parentkey is used to query asynchronously, then the Shard final String findroottbsql = Tc.getlocatertablekeysql (). Tolowe
				Rcase () + joinkeyval;
				if (logger.isdebugenabled ()) {Logger.debug ("Find root parent ' s node sql" + Findroottbsql);
						} listenablefuture<string> listenablefuture = Mycatserver.getinstance (). Getlisteningexecutorservice (). Submit (New callable<string> () {@Override public String call () throws Except Ion {Fetchstorenodeofchildtablehandler Fetchhandler = new Fetchstorenodeofchildtablehandler ();//return Fetch
						Handler.execute (Schema.getname (), Findroottbsql, Tc.getrootparent (). Getdatanodes ()); Return Fetchhandler.execute (Schema.getname (), FINDROOTTBSQL, Tc.getrootparent (). Getdatanodes (), SC);


				}
				}); Futures.addcallback (Listenablefuture, New futurecallback<string> () {@Override public void onsuccess (Strin G result) {//The result is empty, proving that the record does not exist in the previous table, failure if (strings.isnullorempty (Result)) {StringBuilder s = new STRINGB
							Uilder (); Logger.warn (S.append (Sc.getsession2 ()). Append (Origsql). toString () + "ERR:" + "can ' t find (root) parent sharding
							node for sql: "+ origsql); if (!sc.isautocommit ()) {//is under a transaction failure, you must roll back sc.settxinterrupt ("Can ' t find (root) parent sharding node for sql:" + or
							IGSQL);
							} sc.writeerrmessage (Errorcode.er_parse_error, "can ' t find (root) parent sharding node for sql:" + origsql);
						Return } if (logger.isdebugenabled ()) {Logger.debug ("found partion node for child table to insert" + result + "s
						QL: "+ origsql);
	                }//Find the Shard and insert it (as well as other, need to determine if global auto-increment ID is required) boolean processedinsert=false;    if (Sc!=null && tc.isautoincrement ()) {try {String PR
								Imarykey = Tc.getprimarykey ();
							Processedinsert=processinsert (Sc,schema,serverparse.insert,origsql,tc.getname (), PrimaryKey);
			                    } catch (Sqlnontransientexception e) {logger.warn ("sequence Processinsert error,", E);
							Sc.writeerrmessage (errorcode.er_parse_error, "sequence Processinsert ERROR," + e.getmessage ()); }} if (Processedinsert==false) {Routeresultset Executerrs
	    					= Routerutil.routetosinglenode (RRs, result, origsql);
	                    Sc.getsession2 (). Execute (Executerrs, Serverparse.insert);
						}} @Override public void OnFailure (Throwable t) {StringBuilder s = new StringBuilder ();
						Logger.warn (S.append (Sc.getsession2 ()). Append (Origsql). toString () + "ERR:" + t.getmessage ()); Sc.writeerrmessage (ErrorCode.er_parse_error, T.getmessage () + "" + s.tostring ());
						}}, Mycatserver.getinstance ().
			Getlisteningexecutorservice ());
			} else if (Erflag) {throw new Sqlnontransientexception (String.Format ("%s" contains tables that are not er shards, origsql));
	}} return Erflag;
 }

Code @1, using the Druid engine to parse SQL, supports multi-multi execution. Here is the key to solving the problem. Code @2, a statement of the execution of a statement. Code @3, can enter this method, the description is definitely an INSERT statement. The code @4 to determine if it is an ER Word table. Code @5, mainly using Joinkey for routing calculations. Code @6, executes the INSERT statement sequentially on the Shard. After the fix, how to verify is correct, first theory, and then test again. The execution flow of the entire statement is as follows: (see blog: http://blog.csdn.net/prestigeding/article/details/71247101) set autocommit=0; insert into ;insert into  statement commit; The  inisert into  statement of the intermediate step here is one executed, distributed to the node, The commit statement is also issued to the same node to execute. Here is the key to the problem. Mycat is the way to deal with this problem, each front end connection (serverconnection) will have a query hander (Queryhandler), each queryhanlder inside will hold a nonblockingsession , during a complete command processing, Nonblockingsession saves the correspondence of each routing node (the executed Shard node) to the backconnection of the back-end connection (concurrenthashmap< routeresultsetnode, backendconnection> target;) The data structure is/**      * @ Return previous bound connection      */    public  backendconnection bindconnection (Routeresultsetnode key,                                               backendconnection  conn)  {        // system.out.println ("bind  connection  "+conn+         // "  to key  "+ Key.getname () + " on sesion " +this);         return target.put (Key, conn);     }  method when adding data, Trace to the source (Physicaldatasource.getconnection method, the key is internally called Createnewconnection, its callback method connectionacquired), the front-end connection using the backend connection, the method will be called to complete the dimension of the relationship Ensure that the commit command arrives and is sent to the correct back-end connection to be executed. Therefore, it is not a problem to modify this method theoretically. Parse to test the correctness of the method itself: remarks, Mycat Learning test environment, please refer to   http://blog.csdn.net/prestigeding/article/details/71123373 Code submitted in Mycat git Library address: https://github.com/MyCATApache/Mycat-Server/pull/1491 Test code: http://git.oschina.net/ Zhcsoft/studydemo