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

1、前言

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

2、理念落地（实现）

2.1、使用LIKE进行查询

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

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

执行效果是这样的：

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

2.2、不使用LIKE进行查询

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

EXPLAIN SELECT * FROM product WHERE product_id = 12;

结果如下：

可以很明显的看到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后就需要收费了，言归正传：
右击需要添加索引的表 -> 设计表 -> 索引，就会打开下图：

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);

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 子句中对字段进行表达式、函数操作，这将导致引擎放弃使用索引而进行全表扫描。

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