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

叁歲伎倆 2023-09-25 23:54 114阅读 0赞

**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在设置索引时，合理地设定索引类型和索引长度 ####

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

发表评论取消回复

表情：

评论列表（有 0 条评论，114人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化：如何避免那些常见的索引误区

MySQL索引是提高查询效率的关键工具。然而，一些常见的索引误区可能会阻碍你的优化进程。以下是一些需要避免的错误： 1. **过度索引**：不要在所有列上创建索引，这

悠悠/ 2025年03月23日 16:54/ 0 赞/ 59 阅读

相关如何避免MySQL索引失效带来的问题？

MySQL索引失效可能带来数据查询效率降低、性能下降甚至某些场景下可能导致业务错误。以下是几个避免索引失效的建议： 1. **合理设计表结构**：避免冗余和无意义的数据，这样

清疚/ 2025年02月24日 03:39/ 0 赞/ 78 阅读

相关 MySQL索引优化：如何避免常见的误区?

在MySQL索引优化过程中，确实存在一些常见的误解。以下是一些避免误区的建议： 1. **所有列都创建索引**：不是所有的列都需要索引。通常，对查询频率高或者用于连接

女爷i/ 2025年02月18日 20:39/ 0 赞/ 79 阅读

相关 MySQL索引失效：如何避免因数据更新导致的索引失效问题？

在MySQL中，索引是数据库对表进行优化的重要手段。然而，当表中的数据发生变化（如插入、删除或修改操作）时，如果对应的列上有索引，就需要考虑索引失效的问题。以下是避免索引失

淩亂°似流年/ 2025年01月17日 03:24/ 0 赞/ 94 阅读

相关 MySQL索引优化：如何避免常见的索引失效问题？

在MySQL中，索引是提高查询效率的重要工具。但是，如果不正确地使用或管理索引，可能会导致索引失效的问题。以下是一些避免常见索引失效问题的方法： 1. **合理设计表结构**

电玩女神/ 2025年01月11日 17:54/ 0 赞/ 146 阅读

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

Mysql索引算是非常常用了，用得好提高效率，用的不好适得其反如何避免常见的索引失效 1.模糊查询使用 LIKE 查询时，如果搜索表达式以通配符开头，如 \`

叁歲伎倆/ 2023年09月25日 23:54/ 0 赞/ 115 阅读

相关 MySql 索引优化和避免索引失效

建表和索引 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nL

古城微笑少年丶/ 2023年02月15日 12:44/ 0 赞/ 158 阅读

相关 MySQL避免索引失效

create table staffs( id int primary key auto_increment, name varchar(24) not null de

墨蓝/ 2022年05月30日 00:52/ 0 赞/ 363 阅读

相关索引失效的原因和如何避免

一.索引介绍 mysql索引: 是一种帮助mysql高效的获取数据的数据结构，这些数据结构以某种方式引用数据，这种结构就是索引。可简单理解为排好序的快速查找数据结构。如

╰半橙微兮°/ 2022年03月18日 08:36/ 0 赞/ 720 阅读

相关 MySQL避免索引失效

create table staffs( id int primary key auto\_increment, name varchar(24) not null d

一时失言乱红尘/ 2022年01月21日 08:09/ 0 赞/ 503 阅读