MySQL模糊查询你只知道LIKE就OUT了

悠悠 2024-04-28 08:06 79阅读 0赞

## 1、前言 ##

在许多的许多的项目中对于查询的方式，模糊查询可以说是必不可少的一部分功能，在我们日常开发中用得最多的方式就是使用`LIKE`，这种方式也不是说不行，但是，LIKE有一个很大的缺点：使用了LIKE进行查询的时候，索引会失效。  
我勒个去，索引失效(⊙\_⊙)？  
是的，我们后端开发人员都应该知道当数据库数据量大的时候，索引是数据库优化的一个方案，那么LIKE会让索引失效毫无疑问就会导致查询效率低下。

## 2、理念落地（实现） ##

### 2.1、使用LIKE进行查询 ###

在MySQL数据库中可以使用`EXPLAIN`来查看SQL语句的执行情况。

EXPLAIN SELECT * FROM product WHERE product_id LIKE "%1%";

执行效果是这样的：  
![在这里插入图片描述][07de73ea32234eaa83b16c1030b5ab01.png]

这里可以很清楚的看到`key`这一列是Null的，这就代表着要么是数据库没有设置索引，要么就是LIKE会让索引失效。

### 2.2、不使用LIKE进行查询 ###

**因为我在product这个表中将索引放在了主键ID上，所以我就对主键ID进行查询了。**

EXPLAIN SELECT * FROM product WHERE product_id = 12;

结果如下：  
![在这里插入图片描述][092717b40a1a4a7798c9754a8c57b70c.png]  
可以很明显的看到Key这一列不是为NULL的，这就表示索引生效了。

### 2.3、如何添加索引 ###

#### 2.3.1、MySQL为我们提供了四种索引类型 ####

1.  index：普通索引， 基本的索引类型，值可以为空，没有唯一性的限制。
2.  unique：唯一索引，索引列的所有值都只能出现一次，即必须唯一，值可以为空。
3.  FullText：全文索引， 全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。
4.  组合索引：可以包含多列的索引。

#### 2.3.2、Navicat可视化 ####

目前Navicat在中国时常还是比较流行的，并且功能做得很完善，但是，版本 > 12后就需要收费了，言归正传：  
`右击需要添加索引的表 -> 设计表 -> 索引`，就会打开下图：  
![在这里插入图片描述][8777ad137afd4137a5b17c3c296c941c.png]

1.  第一列：索引名称
2.  第二列：需要加索引的表字段
3.  第三列：索引的类型
4.  第四列：索引的底层原理（Hash表、二叉树）

设置完成后保存就行了，可视化还是挺简单的?。

#### 2.3.3、使用SQL指令 ####

##### 2.3.3.1、添加普通索引 #####

ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

##### 2.3.3.2、添加唯一索引 #####

ALTER TABLE `table_name` ADD UNIQUE (`column`);

##### 2.3.3.3、添加全文索引 #####

ALTER TABLE `table_name` ADD FULLTEXT ( `column` );

##### 2.3.3.4、添加组合索引 #####

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

## 3、除了LIKE还有三种模糊查询方式 ##

以下三种方式也都会让索引失效，所以这里只是做一个了解，防止碰到的时候认为效率会比LIKE好，其实都是一样的。

### 3.1、LOCALE ###

SELECT * FROM product WHERE LOCATE("1",product_id);

![在这里插入图片描述][e1e55c929f434e6d888a88309b49a921.png]

### 3.2、POSITION ###

SELECT * FROM product WHERE POSITION("1" IN product_id);

### 3.3、INSTR ###

SELECT * FROM product WHERE INSTR(product_id,"1");

以上的SQL执行结果都是一样的，所以后面两条执行结果就不展示了，和第一条是一样的。

## 4、优化方案 ##

1.  应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2.  对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
3.  尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
4.  LIKE “%abc%会导致全表扫描且屏蔽索引，如果可以，就使用LIKE ”abc%“，这样就不会全表扫描且不会屏蔽索引。
5.  应尽量避免在 where 子句中对字段进行表达式、函数操作，这将导致引擎放弃使用索引而进行全表扫描。

本人资历有限，没有接触过太多数据库优化的方案，所以上述如有错误，还望指出。

[07de73ea32234eaa83b16c1030b5ab01.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/28/a29d7b65e8b5495a98c40e91469a94a3.png
[092717b40a1a4a7798c9754a8c57b70c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/28/adecb9819f6349e58235860deed3fb84.png
[8777ad137afd4137a5b17c3c296c941c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/28/509fa4236ff147fe82a565f48039ad57.png
[e1e55c929f434e6d888a88309b49a921.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/28/f0f150423a5c413483187872cad45a21.png