对比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了。
