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

叁歲伎倆 2023-09-25 23:54 7阅读 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 条评论，7人围观）

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

相关阅读

相关【索引失效】MySQL索引失效场景

如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按as...

今天药忘吃喽~/ 2024年04月23日 04:45/ 0 赞/ 105 阅读

相关避免索引失效的一些原则

文章目录 1、复合索引 a.复合索引，不要跨列或无序使用（最佳左前缀） b.复合索引，尽量使用全索引匹配 2、不要在索引上进行任何操作（计算、函数、类型转换），

梦里梦外;/ 2023年10月06日 13:06/ 0 赞/ 8 阅读

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

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

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

相关 Mysql索引失效

避免索引失效的原则： 1. 复合索引：复合索引不要跨列使用，尽量不要无序使用；尽量使用全索引匹配 2. 不要在索引上进行任何操作（计算，函数，类型转换）

忘是亡心i/ 2023年06月10日 08:27/ 0 赞/ 193 阅读

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

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

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

相关 MySQL的索引失效

参考：[https://segmentfault.com/a/1190000021464570][https_segmentfault.com_a_1190000021464

超、凢脫俗/ 2022年12月15日 12:39/ 0 赞/ 384 阅读

相关 MySQL避免索引失效

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

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

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

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

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

相关常见mysql索引失效条件

java狒狒 2019-05-28 15:21:28 使用索引的一般语句： explain select \ FROM jf\_user ju WHERE ju.user\

冷不防/ 2022年01月26日 13:35/ 0 赞/ 325 阅读

相关 MySQL避免索引失效

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

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