Mysql如何避免常见的索引失效

Mysql索引算是非常常用了，用得好提高效率，用的不好适得其反

如何避免常见的索引失效

1.模糊查询

使用 LIKE 查询时，如果搜索表达式以通配符开头，如 `'%value'`，MySQL 就无法使用索引来加速查询，因为它无法倒序匹配索引

为了避免使用 LIKE 查询导致索引失效一般有两种解决方案：

1.1使用全文搜索 (Full-Text Search)

MySQL全文搜索是一种高级的文本搜索技术，它可以在文本数据中快速搜索特定文本并返回相关文档，与使用LIKE，REGEXP或其他搜索方法相比，全文搜索提供更准确，更可靠且速度更快的搜索体验

它支持全局搜索或部分匹配、排除不相关内容比如文件停用词（“a”、“an”、“the”）并且不仅仅是简单的字符串匹配，它可以根据搜索结果的相关性对搜索结果进行有效排序，常用于处理大量文本数据，比如新闻文章、博客、论文、电子邮件等，在搜索大量文本内容时，使用全文搜索索引通常比传统的“like”和“match against”语句更快 

MySQL 内置支持全文搜索，你只需要在表上创建全文索引即可：

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

在查询时，可以通过 MATCH AGAINST 语法来执行全文搜索

SELECT * FROM table_name 
WHERE MATCH (column_name) AGAINST ('search_expression' IN BOOLEAN MODE);

1.2避免使用前置通配符

在 WHERE 子句中使用列或列组合的前缀

比如使用 `'column_name like 'prefix%'` 来匹配列中以某个前缀开头的值

而不是使用 `'column_name like '%suffix'`，或添加前缀或后缀通配符将模式字符串包裹起来

2.使用Dateformat等函数操作

使用日期格式化等函数操作时，索引会失效

我们其实可以通过创建虚拟列的方式来解决这种问题

2.1创建虚拟列

MySQL 中的虚拟字段是一种基于表中其它字段逻辑计算得到的字段，不需要实际存储到表中，但可以像实际字段一样使用

虚拟字段可以用于简化查询、计算、筛选或组合数据等操作

创建虚拟字段需要使用 `AS` 关键字和表达式，表达式可以包含表中其它字段、数学和日期函数等，如下所示：

ALTER TABLE tablename ADD virtual_column_name AS (expression);

其中，`virtual_column_name` 是虚拟字段的名称，`expression` 是计算虚拟字段的表达式

ps：添加 `AS` 子句之后要加上 `STORED` 关键字，告诉 MySQL 要将这个虚拟字段存储到磁盘上，如果没有使用 `STORED` 关键字，则虚拟字段将在查询时动态计算，而不会存储到表中

示例：

我们查询一个统计的月报时，可以将日期字段的年份和月份分别存储在两个额外的字段中

例如，可以使用 `YEAR` 和 `MONTH` 函数创建两个虚拟字段 `year_column` 和 `month_column` 来存储日期字段的年份和月份：

ALTER TABLE tablename 
ADD year_column INTEGER AS (YEAR(date_column_name)) STORED, 
ADD month_column INTEGER AS (MONTH(date_column_name)) STORED, 
ADD INDEX idx_date_year_month (year_column, month_column);

这样就可以在查询时使用 `year_column` 和 `month_column` 字段，而不需要使用日期函数，从而避免索引失效的问题

同时，通过为 `year_column` 和 `month_column` 字段创建索引，可以提高查询性能

3.其他情况

3.1在查询中避免使用 OR 连接条件

通常使用 UNION 或者 UNION ALL 可以达到相同的目的，而更容易使用索引

3.2在查询中尽量避免类型转换

例如将数字列强制转换为字符串，这可能会导致索引失效

3.3在设置索引时，合理地设定索引类型和索引长度

不要在索引中包含不必要的列，避免使用过多的复合索引