Mycat原理解析:SQL语句的处理

Mycat原理的内容有很多，这篇主要来说说Mycat对SQL语句的处理。

Mycat接收到客户端的sql语句时，会统一使用ServerQueryHandler.query(String sql)方法来处理，ServerQueryHandler主要做了两件事情。

1,.确定sql的类型。比如：SELECT、UPDATE、INSERT、SHOW等

2.将不同类型的sql交给不同的处理器进行处理

@Override
public void query(String sql) {
    ServerConnection c = this.source;
    //确定sql类型
    int rs = ServerParse.parse(sql);
    int sqlType = rs & 0xff;

    //将不同类型的sql交给不同的处理器进行处理
    switch (sqlType) {
    //explain sql
    case ServerParse.EXPLAIN:
        ExplainHandler.handle(sql, c, rs >>> 8);
        break;
    //explain2 datanode=? sql=?
    case ServerParse.EXPLAIN2:
        Explain2Handler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SET:
        SetHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SHOW:
        ShowHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SELECT:
        SelectHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.START:
        StartHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.BEGIN:
        BeginHandler.handle(sql, c);
        break;
    //不支持oracle的savepoint事务回退点
    case ServerParse.SAVEPOINT:
        SavepointHandler.handle(sql, c);
        break;
    case ServerParse.KILL:
        KillHandler.handle(sql, rs >>> 8, c);
        break;
    //不支持KILL_Query
    case ServerParse.KILL_QUERY:
        LOGGER.warn(new StringBuilder().append("Unsupported command:").append(sql).toString());
        c.writeErrMessage(ErrorCode.ER_UNKNOWN_COM_ERROR,"Unsupported command");
        break;
    case ServerParse.USE:
        UseHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.COMMIT:
        c.commit();
        break;
    case ServerParse.ROLLBACK:
        c.rollback();
        break;
    case ServerParse.HELP:
        LOGGER.warn(new StringBuilder().append("Unsupported command:").append(sql).toString());
        c.writeErrMessage(ErrorCode.ER_SYNTAX_ERROR, "Unsupported command");
        break;
    case ServerParse.MYSQL_CMD_COMMENT:
        c.write(c.writeToBuffer(OkPacket.OK, c.allocate()));
        break;
    case ServerParse.MYSQL_COMMENT:
        c.write(c.writeToBuffer(OkPacket.OK, c.allocate()));
        break;
    case ServerParse.LOAD_DATA_INFILE_SQL:
           c.loadDataInfileStart(sql);
           break;
    case ServerParse.MIGRATE:
        MigrateHandler.handle(sql,c);
        break;
    case ServerParse.LOCK:
        c.lockTable(sql);
        break;
    case ServerParse.UNLOCK:
        c.unLockTable(sql);
        break;
    default:
        if(readOnly){
            LOGGER.warn(new StringBuilder().append("User readonly:").append(sql).toString());
            c.writeErrMessage(ErrorCode.ER_USER_READ_ONLY, "User readonly");
            break;
        }
        c.execute(sql, rs & 0xff);
    }
}

确定sql的类型

确定sql类型的工作是交给ServerParse.parse(sql);来完成的。ServerParse的算法很简单，就是一个字符串的匹配过程，下面拿insert和show两种类型的sql来分析。

insert语句的匹配过程：

static int insertCheck(String stmt, int offset) {
    //因为insert的长度最小是6，所以首先判断sql语句的长度是不是大于6
    if (stmt.length() > offset + 6) {
        char c1 = stmt.charAt(++offset);
        char c2 = stmt.charAt(++offset);
        char c3 = stmt.charAt(++offset);
        char c4 = stmt.charAt(++offset);
        char c5 = stmt.charAt(++offset);
        char c6 = stmt.charAt(++offset);
        //一个字符一个字符进行匹配
        if ((c1 == 'N' || c1 == 'n') && (c2 == 'S' || c2 == 's')
                && (c3 == 'E' || c3 == 'e') && (c4 == 'R' || c4 == 'r')
                && (c5 == 'T' || c5 == 't')
                && (c6 == ' ' || c6 == '\t' || c6 == '\r' || c6 == '\n')) {
            //【注意】这里是直接返回INSERT
            return INSERT;
        }
    }
    return OTHER;
}

show语句的匹配过程：

static int sCheck(String stmt, int offset) {
    //判断字符s的下一个字符
    if (stmt.length() > ++offset) {
        switch (stmt.charAt(offset)) {
        case 'A':
        case 'a':
            return savepointCheck(stmt, offset);
        case 'E':
        case 'e':
            return seCheck(stmt, offset);
        case 'H':
        case 'h':
            //下一个字符是s，继续判断是不是show
            return showCheck(stmt, offset);
        case 'T':
        case 't':
            return startCheck(stmt, offset);
        default:
            return OTHER;
        }
    }
    return OTHER;
}
// SHOW' '
static int showCheck(String stmt, int offset) {
    //因为show的长度最小是4，并且offset是从字符h开始的，所以首先判断sql语句的长度是不是大于offset+3
    if (stmt.length() > offset + 3) {
        char c1 = stmt.charAt(++offset);
        char c2 = stmt.charAt(++offset);
        char c3 = stmt.charAt(++offset);
        //逐个字符进行判断
        if ((c1 == 'O' || c1 == 'o') && (c2 == 'W' || c2 == 'w')
                && (c3 == ' ' || c3 == '\t' || c3 == '\r' || c3 == '\n')) {
            //【注意】这里是offset左移8位再和SHOW进行或操作的结果。
            //这个设计的巧妙之处就在于通过一个返回值，返回了两个信息：offset和sql类型。
            //唯一需要注意的就是sql类型的常量值不能超过8位，也就是0~255
            return (offset << 8) | SHOW;
        }
    }
    return OTHER;
}

//下面是Mycat目前支持的sql类型对应的常量值。
public static final int OTHER = -1;
public static final int BEGIN = 1;
public static final int COMMIT = 2;
public static final int DELETE = 3;
public static final int INSERT = 4;
public static final int REPLACE = 5;
public 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;
public static final int SAVEPOINT = 13;
public static final int USE = 14;
public static final int EXPLAIN = 15;
public static final int EXPLAIN2 = 151;
public static final int KILL_QUERY = 16;
public static final int HELP = 17;
public static final int MYSQL_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 LOCK = 22;
public static final int UNLOCK = 23;
public static final int LOAD_DATA_INFILE_SQL = 99;
public static final int DDL = 100;
public static final int MIGRATE  = 203;

经过上面的分析，我们可以看出，ServerParse.parse(sql)的返回值有下面两种情况：

1.直接返回sql类型。例如INSERT、DDL

2.返回sql类型+offset。例如SHOW、SELECT

所以，对于ServerParse.parse(sql)的返回值，后续的处理方式是下面这样的。

int rs = ServerParse.parse(sql);
//获取sql类型
int sqlType = rs & 0xff;
//获取offset
int offset = rs >>> 8;

将不同类型的sql交给不同的处理器进行处理

SQL的处理方式，大致可以分为下面几种：

1.对于大部分的sql，都有专门的处理器就行处理。

EXPLAIN——ExplainHandler
EXPLAIN2——Explain2Handler
SET——SetHandler
SHOW——ShowHandler
SELECT——SelectHandler
START——StartHandler
BEGIN——BeginHandler
SAVEPOINT——SavepointHandler
KILL——KillHandler
USE——UseHandler
MIGRATE——MigrateHandler

2.对于不支持的sql，直接返回错误信息

KILL_QUERY、HELP

3.Mycat能直接处理掉的，直接处理

COMMIT、ROLLBACK、LOCK、UNLOCK、MYSQL_CMD_COMMENT、MYSQL_COMMENT

4.需要通过MySQL来处理的，执行execute

UPDATE、DELETE、DDL

在极悦的Mycat教程当中还有更多的内容在等着大家去学习，当然也有相关的配套视频教程能够免费下载，课程内容详细，通俗易懂，适合初学者，希望对大家的学习能够有所帮助哦。