对比Oracle和MySQL索引对于like的支持，MySQL一言难尽啊...

对比Oracle和MySQL索引对于like的支持，MySQL一言难尽啊...

MySQL版本：8.0.36

Oracle版本：11.2.0.4.0

MySQL:                                                                           |  Oracle:
(root@localhost 09:44:08) [zkm](673009)> select * from test;                     |  09:52:11 ZKM@test(1076)> select * from test;
+------+-----------+                                                             |  
| id   | name      |                                                             |          ID NAME
+------+-----------+                                                             |  ---------- -------------------------
|    1 | aaabbbccc |                                                             |           1 aaabbbccc
|    2 | dddeeefff |                                                             |           2 dddeeefff
|    3 | a  b   c  |                                                             |           3 a  b   c
+------+-----------+                                                             |  
3 rows in set (0.01 sec)                                                         |  Elapsed: 00:00:00.00
                                                                                 |  09:52:38 ZKM@test(1076)> create index idx_t_name on test(name);
(root@localhost 09:44:11) [zkm](673009)> create index idx_t_name on test(name);  |  
Query OK, 0 rows affected (0.26 sec)                                             |  Index created.
Records: 0  Duplicates: 0  Warnings: 0                                           |  
                                                                                 |  Elapsed: 00:00:00.02

现有SQL语句如下：

再Oracle和MySQL中正常无法自动走索引。

select * from test where name like '%ccc';

现在如果使用hint进行强制走索引操作，Oracle是支持的，但是MySQL就无法实现。。防。

https://www.cnblogs.com/PiscesCanon/p/18260833

MySQL强制走索引：
(root@localhost 16:28:50) [zkm](721306)> explain select * from test force index(idx_t_name) where name like '%ccc';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.12 sec)
PS：使用如下hint结果一样：



Oracle强制走索引：
10:00:52 ZKM@test(1076)> explain plan for select /*+ index(test(name)) */ * from test where name like '%ccc';

Explained.

Elapsed: 00:00:00.00
10:01:06 ZKM@test(1076)> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 3081316380

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | IDX_T_NAME |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%ccc')

对于where谓词条件中，reserve(name) like reserve('%ccc')的情况一样：

MySQL和Oracle都删除索引，并重新创建函数索引如下：

create index idx_t_name on test((reverse(name)));

MySQL依然无法使用索引，Oracle可以：

MySQL：
(root@localhost 16:34:17) [zkm](721306)> explain select * from test force index(idx_t_name) where reverse(name) like reverse('%ccc');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
PS:以下两种方式均不行
explain select * from test use index(idx_t_name) where reverse(name) like reverse('%ccc');
explain select /*+ index(test idx_t_name) */ * from test where reverse(name) like reverse('%ccc');

Oracle：
10:52:46 ZKM@test(1076)> explain plan for select * from test where reverse(name) like reverse('%ccc');

Explained.

Elapsed: 00:00:00.01
10:55:06 ZKM@test(1076)> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2114388309

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_NAME |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(REVERSE("NAME") LIKE 'ccc%')
       filter(REVERSE("NAME") LIKE 'ccc%')

哎，优化时候遇到的问题。

只能跟业务讨论然后改SQL了。