MySQL INSTR和LIKE的效率

在MySQL中，INSTR和LIKE是两个常用的字符串处理函数，用于在字符串中搜索指定的子字符串。本文将详细解析这两个函数的使用方法，并比较它们在查询效率方面的差异。

1. INSTR函数

1.1 语法

INSTR(str, substr)

INSTR函数接受两个参数，str表示要进行搜索的字符串，substr表示要搜索的子字符串。它返回子字符串在原字符串中的起始位置。如果子字符串不存在，返回值为0。

1.2 示例代码

假设有以下表items：

CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO items (name)
VALUES
  ('apple'),
  ('banana'),
  ('cherry'),
  ('durian');

现在，我们想查找名字包含字母a的物品。

可以使用INSTR函数进行查询：

SELECT * FROM items
WHERE INSTR(name, 'a') > 0;

运行以上代码，将得到以下结果：

+----+-------+
| id | name  |
+----+-------+
|  1 | apple |
|  2 | banana|
|  3 | cherry|
|  4 | durian|
+----+-------+

1.3 INSTR函数的优势

INSTR函数可以用于模糊搜索，不仅仅限于包含关系。
INSTR函数查询效率高，特别适合处理大量数据。

2. LIKE函数

2.1 语法

column_name LIKE pattern

LIKE函数接受两个参数，column_name表示要进行搜索的列名，pattern表示要匹配的模式。

pattern支持使用通配符，常见的通配符有：

%：匹配任意字符（包括空字符）。
_：匹配单个字符。

2.2 示例代码

继续使用上述的items表，假设我们要查询名字以a开头的物品。

可以使用LIKE函数进行查询：

SELECT * FROM items
WHERE name LIKE 'a%';

运行以上代码，将得到以下结果：

+----+-------+
| id | name  |
+----+-------+
|  1 | apple |
+----+-------+

2.3 LIKE函数的优势

LIKE函数支持更灵活的模式匹配，可以使用通配符进行模糊搜索。

3. INSTR和LIKE的性能对比

INSTR和LIKE函数虽然都可用于字符串搜索，但它们在查询效率方面存在一些差异，主要表现在以下几个方面：

3.1 精确匹配

如果需要进行精确匹配，即搜索的内容必须与目标完全一致，那么使用INSTR函数将是更好的选择。

3.2 模糊匹配

如果需要进行模糊匹配，即搜索的内容只需部分匹配，那么使用LIKE函数会更加方便。特别是当需要使用通配符进行模式匹配时，LIKE函数是不可替代的。

3.3 索引利用

INSTR函数可以使用索引优化查询，因为它支持使用索引进行搜索。而对于LIKE函数，在某些情况下，如果模式的首字符是通配符，索引将无法被利用。

为了演示这一点，我们创建一个额外的表index_test：

CREATE TABLE index_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO index_test (name)
VALUES
  ('apple'),
  ('banana'),
  ('cherry'),
  ('durian');

为name列创建索引：

ALTER TABLE index_test
ADD INDEX idx_name (name);

现在，我们分别使用INSTR和LIKE函数进行查询：

-- 使用INSTR函数查询
EXPLAIN SELECT * FROM index_test
WHERE INSTR(name, 'a') > 0;

-- 使用LIKE函数查询
EXPLAIN SELECT * FROM index_test
WHERE name LIKE 'a%';

其中EXPLAIN语句用于分析查询执行计划。

运行以上代码，将得到如下结果：

-- 使用INSTR函数查询的结果
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | index_test | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

-- 使用LIKE函数查询的结果
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | index_test | ALL  | idx_name      | NULL      | NULL    | NULL  |    4 | Using where |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+

通过执行计划可以看出，当使用INSTR(name, 'a')进行查询时，MySQL无法使用索引，需要进行全表扫描。而当使用LIKE函数进行查询时，尽管存在idx_name索引，但也无法使用索引进行优化。