Mycat Source code Analysis of database Middleware (c): Mycat SQL parsing and routing

Mycat the parsing of SQL is divided into two parts, one is ordinary SQL and the other is preparedstatment.

The following is an example of parsing ordinary SQL (the other way is the same), SQL sent from the client after the server is received will call Frontendcommandhandler handle method, This method invokes the Frontendconnection query method, and the query method calls the Serverqueryhandler query method, then calls the Execute method of the serverconnection. As shown in the following:

public void execute (String sql, int type) {//connection state Check if (this.isclosed ()) {Logger.warn ("Ignore Execute, Server connection is Closed "+ this); return;} Transaction status Check if (txinterrupted) {writeerrmessage (Errorcode.er_yes, "Transaction error, need to rollback." + txinterrputmsg); return;} Check the currently used dbstring db = This.schema;boolean IsDefault = true;if (db = = null) {db = Schemautil.detectdefaultdb (sql, type); I F (db = = null) {Writeerrmessage (errorcode.err_bad_logicdb, "No mycat Database selected"); return;} IsDefault = false;} Compatible with Phpadmin ' s, supports simulation of MySQL metadata return////TODO:2016/5/20 supports more information_schema features if (Serverparse.select = = Type && Db.equalsignorecase ("Information_schema")) {mysqlinformationschemahandler.handle (SQL, this); return;} if (Serverparse.select = = Type && sql.contains ("MySQL") && sql.contains ("proc")) { Schemautil.schemainfo schemainfo = Schemautil.parseschema (sql); if (schemainfo! = null && "MySQL". Equalsignorecase (Schemainfo.schema) && "proc". EqualsignoRecase (schemainfo.table)) {//compatible with mysqlworkbenchmysqlprochandler.handle (SQL, this); return;}} Schemaconfig schema = Mycatserver.getinstance (). GetConfig (). GetSchemas (). get (db); if (schema = = null) {Writeerrmessage (ERRORCODE.ERR_BAD_LOGICDB, "Unknown mycat Database '" + db + "'"); return;}  Fix navicat SELECT state as ' state ', ROUND (sum (DURATION), 7) as ' DURATION ', CONCAT (ROUND (SUM (DURATION)/*100,3), '% ') as ' Percentage ' from INFORMATION_SCHEMA. PROFILING WHERE query_id= GROUP by State ORDER by seqif (serverparse.select = = Type &&sql.contains ("Information_sc HEMA. PROFILING ") &&sql.contains (" CONCAT (ROUND (SUM (DURATION)/*100,3) ") {Informationschemaprofiling.response ( this); return;} /* When the default schema is already set, you can execute * related SQL by specifying a different schema in SQL, which has been verified in the MySQL client. * So add support for specifying schema in SQL here. */if (IsDefault && schema.ischecksqlschema () && isnormalsql (type)) {Schemautil.schemainfo SchemaInfo = Schemautil.parseschema (SQL); if (schemainfo! = NULL && Schemainfo.schema! = null &&!schemainfo.schema.equals (db)) {Schemaconfig schemaconfig = Mycatserver.getinstance (). GetConfig (). GetSchemas (). get (Schemainfo.schema); if (schemaconfig! = null) schema = Schemaconfig;}} Routeendexecutesql (SQL, type, schema);}

Finally there is a Routeendexecutesql method, it will first call the Routeservice route method Advanced, and then call the Hintsqlhandler route method, This method calls Routestrategy's route method, which uses a policy pattern that contains the following types of SQL, different types using different strategies.

Public final class Serverparse {public static final int. other = -1;public static final int BEGIN = 1;public static final I NT COMMIT = 2;public static final int DELETE = 3;public static final int INSERT = 4;public static final int REPLACE = 5;pu Blic static final int ROLLBACK = 6;public static final int SELECT = 7;public static final int SET = 8;public static final int SHOW = 9;public static final int START = 10;public static final int UPDATE = 11;public static final int KILL = 12;publ IC static final int savepoint = 13;public static final int use = 14;public static final int EXPLAIN = 15;public static fin Al int EXPLAIN2 = 151;public static final int kill_query = 16;public static final int help = 17;public static final int MY Sql_cmd_comment = 18;public static final int mysql_comment = 19;public static final int call = 20;public static final int    DESCRIBE = 21;    public static final int load_data_infile_sql = 99; public static final int DDL = 100;

The use of different routing methods is determined in Routenormalsqlwithast,

Public Routeresultset routenormalsqlwithast (schemaconfig schema,string stmt, Routeresultset RRs, String CharSet, Layercachepool Cachepool) throws sqlnontransientexception {/** * only MySQL is supported MySQL syntax */sqlstatementparser parser = Null;i F (Schema.isneedsupportmultidbtype ()) {parser = new Mycatstatementparser (stmt);} else {parser = new Mysqlstatementparser (stmt); }mycatschemastatvisitor visitor = null;            SQLStatement statement;/** * Resolution problem Unified Throw SQL syntax error */try {statement = Parser.parsestatement (); Visitor = new Mycatschemastatvisitor ();} catch (Exception t) {logger.error ("Druidmycatroutestrategyerror", t); throw new Sqlsyntaxerrorexception (t);} /** * Test Unsupported Statement */checkunsupportedstatement (statement);D Ruidparser druidparser = Druidparserfactory.create (schema, statement, visitor);d Ruidparser.parser (schema, RRS, statement, Stmt,cachepool, visitor);/** * Druidparser the direct return of the route was completed during parsing (Rrs.isfinishedroute ()) {return RRS;} /** * No from SELECT statement or other */druidshardingParseinfo ctx= druidparser.getctx (); if (ctx.gettables () = = NULL | | ctx.gettables (). Size () = = 0) && (ctx.gettablealiasmap () ==null| | Ctx.gettablealiasmap (). IsEmpty ()) {return Routerutil.routetosinglenode (RRs, Schema.getrandomdatanode (), DRUIDP Arser.getctx (). GetSQL ());} if (Druidparser.getctx (). Getroutecalculateunits (). Size () = = 0) {Routecalculateunit routecalculateunit = new Routecalculateunit ();d ruidparser.getctx (). Addroutecalculateunit (Routecalculateunit);} sortedset<routeresultsetnode> nodeset = new treeset<routeresultsetnode> (); for (RouteCalculateUnit unit: Druidparser.getctx (). Getroutecalculateunits ()) {Routeresultset rrstmp = routerutil.tryroutefortables (schema, Druidparser.getctx (), unit, RRS, Isselect (statement), Cachepool), if (rrstmp! = null) {for (Routeresultsetnode node: Rrstmp.getnodes ()) {Nodeset.add (node);}}} routeresultsetnode[] nodes = new Routeresultsetnode[nodeset.size ()];int i = 0;for (iterator<routeresultsetnode> iterator = NodeseT.iterator (); Iterator.hasnext ();) {Nodes[i] = Iterator.next (); i++;} Rrs.setnodes (nodes);//Sub-table/** * subtables= "t_order$1-2,t_order3" * Current table 1.6 Start support shipowner Hideaway DataNode only one can be configured under sub-table conditions, join is not supported under table conditions. */if (Rrs.isdisttable ()) {return this.routedistable (STATEMENT,RRS);} return RRS;}

It uses Druid to do database connection pool, support the Sub-Library sub-table, the following we have a number of tables of the sub-database table routing strategy as an example of analysis.

public static void Findroutewithcconditionsfortables (Schemaconfig schema, Routeresultset rrs,map<string, Map< String, set<columnroutepair>>> tablesandconditions,map<string, set<string>> tablesRouteMap , String sql, Layercachepool Cachepool, Boolean isselect) throws Sqlnontransientexception {//For the Sub-Library table for routing for (map.entry< String, map<string, set<columnroutepair>>> Entry:tablesAndConditions.entrySet ()) {String tableName = Entry.getkey (). toUpperCase ();  Tableconfig tableconfig = Schema.gettables (). Get (TableName), if (tableconfig = = null) {String msg = "can ' t find table define In schema "+ TableName +" Schema: "+ schema.getname (); Logger.warn (msg); throw new Sqlnontransientexception (msg);} if (Tableconfig.getdisttables ()!=null && tableconfig.getdisttables (). Size () >0) {Routetodisttablenode ( Tablename,schema,rrs,sql, Tablesandconditions, cachepool,isselect);} A global table or a table without a library is skipped (calculated after the global table) if (tableconfig.isglobaltable () | | | schema.gettables (). Get (tableName). GETdatanodes (). Size () = = 1) {continue;} else {//Non-Global table: Sub-Library table, childtable, other map<string, set<columnroutepair>> Columnsmap = Entry.getvalue (); String Joinkey = Tableconfig.getjoinkey (); String Partioncol = Tableconfig.getpartitioncolumn (); String PrimaryKey = Tableconfig.getprimarykey (); Boolean isfoundpartitionvalue = Partioncol! = NULL &&                Entry.getvalue (). Get (partioncol)! = NULL;                Boolean isloaddata=false; if (logger.isdebugenabled () && sql.startswith (loaddata.loaddatahint) | | Rrs.isloaddata ()) {//due to the fact that the load data is accounting for a lot of routing, the output of this log can greatly degrade the performance of load data Isloadda                Ta=true;                }if (Entry.getvalue (). Get (PrimaryKey)! = null && entry.getvalue (). Size () = = 1&&!isloaddata) {//primary key lookup//try by primary key if found in cacheset<columnroutepair> primarykeypairs = Entry.getvalue (). Get (Primaryke                        y); if (primarykeypairs! = null) {if (logger.isdebugenabled ()) {         Logger.debug ("Try to find caches by primary key");} String Tablekey = schema.getname () + ' _ ' + tablename;boolean Allfound = true;for (Columnroutepair pair:primarykeypairs) {//possible ID in (CacheKey) multi-primary key string = Pair.colvalue; String dataNode = (string) cachepool.get (Tablekey, CacheKey), if (DataNode = = null) {allfound = False;continue;} else {if (t Ablesroutemap.get (tableName) = = null) {Tablesroutemap.put (TableName, New hashset<string> ());} Tablesroutemap.get (TableName). Add (DataNode); continue;}} if (!allfound) {//Need cache primary key->datanode relationif (Isselect && tableconfig.getprimarykey ()! = nul L) {Rrs.setprimarykey (Tablekey + '. ' + Tableconfig.getprimarykey ())}} else {//primary key cache found in the next round of execution loop continue;}}} if (isfoundpartitionvalue) {//Sub-Library table set<columnroutepair> Partitionvalue = Columnsmap.get (PartionCol); Partitionvalue = = NULL | | Partitionvalue.size () = = 0) {if (Tablesroutemap.get (tableName) = = null) {Tablesroutemap.put (tableName, New hashset< String>());} Tablesroutemap.get (TableName). AddAll (Tableconfig.getdatanodes ());} else {for (Columnroutepair pair:partitionvalue) {if (pair.colvalue! = null) {Integer Nodeindex = tableconfig.getrule (). GE Trulealgorithm (). Calculate (Pair.colvalue); if (Nodeindex = = null) {String msg = "Can ' t find any valid Datanode:" + Tablecon Fig.getname () + "--" + tableconfig.getpartitioncolumn () + "--" + pair.colvalue; Logger.warn (msg); throw new Sqlnontransientexception (msg);} arraylist<string> datanodes = Tableconfig.getdatanodes ();  String node;if (nodeindex >=0 && Nodeindex < Datanodes.size ()) {node = Datanodes.get (Nodeindex);} else {node = NULL; String msg = "Can ' t find a valid data node for specified node index:" + tableconfig.getname () + "+" + tableconfig.ge Tpartitioncolumn () + "+" + pair.colvalue + "+" + "Index:" + nodeindex; Logger.warn (msg); throw new Sqlnontransientexception (msg);} if (node! = null) {if (Tablesroutemap.get (tableName) = = null) {Tablesroutemap.put (tabLename, New hashset<string> ());} Tablesroutemap.get (TableName). Add (node);}} if (pair.rangevalue! = null) {integer[] Nodeindexs = Tableconfig.getrule (). Getrulealgorithm (). Calculaterange ( Pair.rangeValue.beginValue.toString (), pair.rangeValue.endValue.toString ()); arraylist<string> datanodes = Tableconfig.getdatanodes (); String node;for (Integer idx:nodeindexs) {if (idx >= 0 && idx < datanodes.size ()) {node = datanodes.get (idx );}  else {String msg = "Can ' t find valid data node (s) for some of specified node indexes:" + tableconfig.getname () + "-" + Tableconfig.getpartitioncolumn (); Logger.warn (msg); throw new Sqlnontransientexception (msg);} if (node! = null) {if (Tablesroutemap.get (tableName) = = null) {Tablesroutemap.put (TableName, New hashset<string> () );} Tablesroutemap.get (TableName). Add (node);}}}} else if (joinkey! = null && columnsmap.get (joinkey)! = null && columnsmap.get (joinkey). Size ()! = 0) {//chil Dtable (If the parent-child join of the SELECT statement) is found before root tAble, childtable is removed, leaving only root tableset<columnroutepair> Joinkeyvalue = Columnsmap.get (Joinkey); set<string> Datanodeset = rulebyjoinvaluecalculate (RRs, Tableconfig, Joinkeyvalue); if (DataNodeSet.isEmpty ()) { throw new Sqlnontransientexception ("Parent key can ' t find any valid datanode");} if (logger.isdebugenabled ()) {Logger.debug ("Found partion nodes (using parent partion rule directly) Date "+ arrays.tostring (Datanodeset.toarray ()) +" sql: "+ SQL); if (Datanodeset.size () > 1) {routetomultinode (rrs.iscacheable (), RRS, Datanodeset, SQL); Rrs.setfinishedroute (True) ; return;} else {rrs.setcacheable (true); Routetosinglenode (RRs, Datanodeset.iterator (). Next (), SQL); return;}} else {//The Split field is not found, all nodes of the table are routed if (Tablesroutemap.get (tableName) = = null) {Tablesroutemap.put (tableName, New hashset< String> ());} Tablesroutemap.get (TableName). AddAll (Tableconfig.getdatanodes ());}}}

Mycat will first Brahma the key (support multi-primary key), according to the primary key to find different node nodes, and then execute the SQL separately in different node, so it gets the SQL routing table, so-called routing table is the lookup table in which nodes. This is done primarily by parsing the existing table name and key values of the primary key in the schema configuration through the algorithm (RULEALGORITHM), if there is no primary key range, in the case of a primary key partition table (with multiple shard types as shown). Once the node is found, it is only specific to execute SQL.

Partitionbydatepartitionbyfilemappartitionbyhashmodpartitionbyhotdatepartitionbyjumpconsistenthashpartitionbylongpartitio Nbymodpartitionbymonthpartitionbymurmurhashpartitionbypatternpartitionbyprefixpatternpartitionbyrangedatehashpartitionbyr Angemodpartitionbystringpartitiondirectbysubstring

After finding the routing node in the Routeendexecutesql method mentioned above , it calls the Execute method of Nonblockingsession, which is divided into single-node mode and multi-node mode, the following is a multi-node mode example, in which case it calls Multinodequeryhandler's Execute method.

public void execute () throws Exception {final Reentrantlock lock = This.lock;lock.lock (); try {This.reset (Rrs.getnodes (). length); this.fieldsreturned = False;this.affectedrows = 0l;this.insertid = 0L;} finally {Lock.unlock ();} Mycatconfig conf = mycatserver.getinstance (). GetConfig (); startTime = System.currenttimemillis (); Logger.debug ("Rrs.getrunonslave ()-" + Rrs.getrunonslave ()); for (final Routeresultsetnode node:rrs.getNodes ()) { Backendconnection conn = session.gettarget (node), if (Session.tryexistscon (conn, node)) {Logger.debug (" Node.getrunonslave ()-"+ Node.getrunonslave ()"); Node.setrunonslave (Rrs.getrunonslave ());//Implement master/ Slave annotation Logger.debug ("Node.getrunonslave ()-" + Node.getrunonslave ()); _execute (conn, node);} else {//Create new Connectionlogger.debug ("Node.getrunonslave ()" + Node.getrunonslave ()); Node.setrunonslave ( Rrs.getrunonslave ());//Implement Master/slave annotation Logger.debug ("Node.getrunonslave ()" + Node.getrunonslave ()); Physicaldbnode DN = conf.getdatanodes (). Get (Node.getname ());Dn.getconnection (Dn.getdatabase (), autocommit, node, this, node);//Note that this method is not just getting the connection, getting a new connection succeeds, it passes the layer callback, and finally callbacks to this class connectionacquired//This is done through the layer pass of the This parameter of the method above. Connectionacquired execution://Session.bindconnection (node, conn);//_execute (conn, node);}}}

This optimized SQL is sent to the individual nodes in the routing results for execution.

Mycat Source code Analysis of database Middleware (c): Mycat SQL parsing and routing