我們認為的 SQL like 查詢和優(yōu)化技巧，設(shè)計的初衷和真正的實現(xiàn)原理是什么。

在 h2database SQL like 查詢實現(xiàn)類中（CompareLike），可以看到 SQL 語言到具體執(zhí)行的實現(xiàn)、也可以看到數(shù)據(jù)庫嘗試優(yōu)化語句的過程，以及查詢優(yōu)化的原理。可以做為條件語句的經(jīng)典案例去分析。

我們熟知的索引前綴匹配，實現(xiàn)過程和局限可以通過源碼體現(xiàn)。

文章中的查詢不只局限在 Select 語句，包括 Update、Delete。

SQL like 語句實現(xiàn)

根據(jù)之前的文章《Insight H2 database 數(shù)據(jù)查詢核心原理》。 condition 執(zhí)行的結(jié)果返回布爾類型，true or false。

任何表達式都可以是 condition， 根據(jù)轉(zhuǎn)換規(guī)則，都可以轉(zhuǎn)為布爾類型。

核心方法： org.h2.expression.CompareLike#getValue

/*** 執(zhí)行 SQL like 表達式，返回 true or false.* 假設(shè) SQL like 語句為 NAME like 'bj%' * @see org.h2.expression.CompareLike#getValue*/
public Value getValue(Session session) {// 從當前行（session）獲取對應列（left）的值。 left 對應為：NAME (ExpressionColumn)Value l = left.getValue(session);if (!isInit) {// 獲取 like 表達式，正常情況下。 right 對應為：bj% (ValueExpression)Value r = right.getValue(session);String p = r.getString();// 解析 like 表達式，方便后續(xù)識別和比對。主要是識別和定位通配符。initPattern(p, getEscapeChar(e));}String value = l.getString();boolean result;if (regexp) {// 正則模式匹配result = patternRegexp.matcher(value).find();} else {// SQL Like 模式匹配。 字符循環(huán)比對。result = compareAt(value, 0, 0, value.length(), patternChars, patternTypes);}return ValueBoolean.get(result);
}

SQL like 查詢優(yōu)化

like 查詢比較損耗性能，針對特定的情況下，會進行查詢優(yōu)化。

prepare 階段，重寫查詢語句，會徹底替換 Condition 對象。

之后，嘗試增加索引查詢條件，縮小數(shù)據(jù)遍歷的范圍。

①查詢語句重寫

核心方法： org.h2.expression.CompareLike#optimize

在查詢準備階段（org.h2.command.dml.Select#prepare），如果檢測到如下的情況，會進行查詢語句重寫。

if ("%".equals(p)) {// optimization for X LIKE '%': convert to X IS NOT NULLreturn new Comparison(session, Comparison.IS_NOT_NULL, left, null).optimize(session);
}if (isFullMatch()) {// 沒有通配符的情況下，約等于等值匹配// optimization for X LIKE 'Hello': convert to X = 'Hello'Value value = ValueString.get(patternString);Expression expr = ValueExpression.get(value);return new Comparison(session, Comparison.EQUAL, left, expr).optimize(session);
}

②增加索引查詢條件

嘗試限定查找范圍 (start or end)，而非全表掃描。

比如：select * from city where name like ‘朝陽%’;

等同于：select * from city where name >= ‘朝陽’ and name < ‘朝陰’;

核心方法：org.h2.expression.CompareLike#createIndexConditions

在查詢準備階段（org.h2.command.dml.Select#prepare），如果支持索引前綴匹配，那么就嘗試計算匹配范圍，增加索引查詢條件，達到減少遍歷的目的。

/*** like 前綴查詢的本質(zhì)是什么？* 拆解匹配查詢字符串，把 like 查詢，轉(zhuǎn)為對應規(guī)律的字符串范圍查詢。* 例如：NAME like 'bj%i' --> NAME >= 'bj' && NAME < 'bk'*/
public void createIndexConditions(Session session, TableFilter filter) {// 使用正則模式查詢，索引不生效。 NAME REGEXP '^bj.*'if (regexp) {return;}// 非當前表的關(guān)聯(lián)查詢語句，索引不生效ExpressionColumn l = (ExpressionColumn) left;if (filter != l.getTableFilter()) {return;}String p = right.getValue(session).getString();initPattern(p, getEscapeChar(e));// 非前綴匹配，索引不生效// private static final int MATCH(char) = 0, ONE(_) = 1, ANY(*) = 2;if (patternLength <= 0 || patternTypes[0] != MATCH) {// can't use an indexreturn;}int dataType = l.getColumn().getType();if (dataType != Value.STRING && dataType != Value.STRING_IGNORECASE && dataType != Value.STRING_FIXED) {// column is not a varchar - can't use the indexreturn;}// 假設(shè)查詢語句為： NAME like 'bj%i'// 從 patternChars(bj%i) 中提取最佳匹配的前綴字符串 bj。String end;if (begin.length() > 0) {// 增加索引查詢查詢條件 NAME >= 'bj'。以此作為 like 前綴匹配的起始filter.addIndexCondition(IndexCondition.get(Comparison.BIGGER_EQUAL, l, ValueExpression.get(ValueString.get(begin))));char next = begin.charAt(begin.length() - 1);// search the 'next' unicode character (or at least a character that is higher)// 根據(jù)字符串順序，嘗試找到大于前綴的字符串。以此作為 like 前綴匹配的終止for (int i = 1; i < 2000; i++) {end = begin.substring(0, begin.length() - 1) + (char) (next + i);if (compareMode.compareString(begin, end, ignoreCase) == -1) {// 增加索引查詢查詢條件 NAME < 'bk'。 j 的下一個字符即 k。filter.addIndexCondition(IndexCondition.get(Comparison.SMALLER, l, ValueExpression.get(ValueString.get(end))));break;}}}
}

其他

上述描述的過程，其中有一些細節(jié)，需要單獨說明。

①通配符模式前綴查找

int maxMatch = 0;
// 存儲通配符模式前綴字符串， that is "begin"
StringBuilder buff = new StringBuilder();
// 找到非通配符的前綴字符串。遍歷 patternChars ， 遇到非精確字符串， 終止。
while (maxMatch < patternLength && patternTypes[maxMatch] == MATCH) {buff.append(patternChars[maxMatch++]);
}

②索引條件校驗

createIndexConditions 方式是把所有可以轉(zhuǎn)為范圍查找的列都加入了索引條件中（org.h2.table.TableFilter#indexConditions）。

有些列可能并沒有索引，所以，需要在準備階段（org.h2.table.TableFilter#prepare），剔除無效的索引條件。

/*** Prepare reading rows. This method will remove all index conditions that* can not be used, and optimize the conditions.* @see org.h2.table.TableFilter#prepare*/
public void prepare() {// forget all unused index conditions// the indexConditions list may be modified herefor (int i = 0; i < indexConditions.size(); i++) {IndexCondition condition = indexConditions.get(i);if (!condition.isAlwaysFalse()) {Column col = condition.getColumn();if (col.getColumnId() >= 0) {if (index.getColumnIndex(col) < 0) {indexConditions.remove(i);i--;}}}}
} 

Code Insight 環(huán)境

java -jar h2-1.4.184.jar org.h2.tools.Shell -url "jdbc:h2:~/test;MV_STORE=false" -user sa -password ""

select * from city where name like 'bj%i';SELECT *
FROM INFORMATION_SCHEMA.INDEXES
WHERE TABLE_NAME = 'CITY';

總結(jié)

• SQL like 模式匹配支持正則表達式和通配符兩種。

• 常用的通配符模式采用約定的字符串匹配規(guī)則確定每一行數(shù)據(jù)是否符合要求。

• 正則模式匹配不支持優(yōu)化，需要遍歷目標表的每一行，性能損耗大。

• 使用前綴匹配的通配符模式匹配，嘗試增加索引列的區(qū)間范圍條件，優(yōu)化掃描區(qū)間。

• 熟悉條件篩選的底層原理，趨利避害，達到數(shù)據(jù)查詢的最佳性能。