mysql、oracle like查询不走索引的解决方案

1.情景展示

我们知道：无论是mysql还是oracle，只要使用like查询，就可能会面临索引失效（不走索引）的问题；

下面，我们将一起来看看什么情况下，索引会失效，以及如何解决不走索引的问题。

已知，base_org_info表有两个索引

我们需要使用ORGNAME进行模糊查询，据此进行案例展示。

2.具体分析

要想解决走不走索引的问题，首先，我们需要确定的是：索引在哪的问题？

通常情况下，我们为了提高查询效率，往往会建一些索引；

我们要使用索引，需要满足两个条件：

第一，创建索引；

第二，将要查询条件(where)包含索引列。

举个例子：

如果没有where限制条件，查询的将是所有数据，这一点大家都知道。

只要查询条件包含索引列，就会走索引；

即使，存在其他限制条件，也会走索引；

但是，like查询可能会让索引失效，具体见下文。

另外的话，主键字段本身索引，而且是唯一索引。

情形一：like '字符串%'和like '字符串_'；

这种情况和'='一样，都会走索引。

情形二：like '%字符串'和like '_字符串'；

不走索引，索引IDX_BASE_ORG_INFO_ORGNAME失效；

情形三：like '%字符串%'和like '_字符串_'；

不走索引，索引IDX_BASE_ORG_INFO_ORGNAME失效；

说明：_和%的索引失效情况，一模一样，不再截图展示。

3.解决方案

先来说说这种情况：

虽然，限制条件orgseq是索引列，但是，这个索引不起作用，因为like查询走的是全表扫描！！！ 

解决方案一：创建复合索引；

为where后面的所有限制条件，联合创建一个索引。

一起来看下效果：

此时，咱们新建的联合索引生效了，是不是到此为止了？

别急，一个神奇的事情将会发生：

我们可以看到：索引，又失效了！

所以说，这种方式仅限于查询对应的索引列，一旦查询的字段不是组合索引当中的索引列的话，索引将会失效；

另外，这种方式也仅限于：少量的查询限制条件，如果字段太多就失去了使用索引的意义。

解决方案二：使用内连接。（推荐使用）

SELECT
	t.ORGSEQ,
	t2.ORGNAME 
FROM
	base_org_info_copy1 t,
	( SELECT t.ORGID, t.ORGNAME FROM base_org_info_copy1 t WHERE t.ORGNAME LIKE '%阳_' ) t2 
WHERE
	t2.ORGID = t.ORGID

第一步：为要使用like '%字符串%'或者'%字符串'的列创建单独索引；

第二步：查询该字段与主键列，并将查询结果作为一张表；

第三步：与原来的表，使用主键列建立内连接；

第四步：其它查询限制条件使用原来的表进行限制。

我们可以从上图当中看到，走了索引列：IDX_BASE_ORG_INFO_ORGNAME；

用了唯一索引：主键列ORGID，因为两表关联用的它。

4.扩展

Mysql innodb引擎默认的索引数据结构是b+树，组合索引会形成多字段顺序排序，比如下图，会先按照姓名进行排序，姓名相等就再按照年龄排序，所以会有组合索引的最左前缀原理（这就是使用组合索引，并且只查询索引列的时候，like'%字符串'可以走组合索引的原因）；
而假如只like查询姓名，例如like "张%"，则也可以使用最左前缀原理，先索引到张六，然后遍历查询，直到姓名不以张开头。

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

posted @ 2022-02-06 21:00  Marydon  阅读(4111)  评论(0)  编辑  收藏  举报