MySQL进阶-索引-使用规则-索引失效情况一（索引列运算，字符串不加引号，头部模糊匹配）

于 2024-06-27 11:18:55 发布
阅读量264 收藏 7
点赞数 5
分类专栏： MySQL高级篇 文章标签： mysql 数据库 索引列运算 字符串不加引号 头部模糊匹配 索引使用规则
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_65152767/article/details/140001568
版权

文章目录

1、索引列运算

不要在索引列上进行运算操作，索引将失效

1.1、查询表tb_user

mysql> select * from tb_user;
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
| id | name   | phone       | email                 | profession           | age  | gender | status | createtime          |
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
|  1 | 吕布   | 17799990000 | lvbu666@163.com       | 软件工程             |   23 | 1      | 6      | 2001-02-02 00:00:00 |
|  2 | 曹操   | 17799990001 | caocao666@qq.com      | 通讯工程             |   33 | 1      | 0      | 2001-03-05 00:00:00 |
|  3 | 赵云   | 17799990002 | 17799990@139.com      | 英语                 |   34 | 1      | 2      | 2002-03-02 00:00:00 |
|  4 | 孙悟空 | 17799990003 | 17799990@sina.com     | 工程造价             |   54 | 1      | 0      | 2001-07-02 00:00:00 |
|  5 | 花木兰 | 17799990004 | 19980729@sina.com     | 软件工程             |   23 | 2      | 1      | 2001-04-22 00:00:00 |
|  6 | 大乔   | 17799990005 | daqiao666@sina.com    | 舞蹈                 |   22 | 2      | 0      | 2001-02-07 00:00:00 |
|  7 | 露娜   | 17799990006 | luna_love@sina.com    | 应用数学             |   24 | 2      | 0      | 2001-02-08 00:00:00 |
|  8 | 程咬金 | 17799990007 | chengyaojin@163.com   | 化工                 |   38 | 1      | 5      | 2001-05-23 00:00:00 |
|  9 | 项羽   | 17799990008 | xiaoyu666@qq.com      | 金属材料             |   43 | 1      | 0      | 2001-09-18 00:00:00 |
| 10 | 白起   | 17799990009 | baiqi666@sina.com     | 机械工程及其自动
化 |   27 | 1      | 2      | 2001-08-16 00:00:00 |
| 11 | 韩信   | 17799990010 | hanxin520@163.com     | 无机非金属材料工
程 |   27 | 1      | 0      | 2001-06-12 00:00:00 |
| 12 | 荆轲   | 17799990011 | jingke123@163.com     | 会计                 |   29 | 1      | 0      | 2001-05-11 00:00:00 |
| 13 | 兰陵王 | 17799990012 | lanlinwang666@126.com | 工程造价             |   44 | 1      | 1      | 2001-04-09 00:00:00 |
| 14 | 狂铁   | 17799990013 | kuangtie@sina.com     | 应用数学             |   43 | 1      | 2      | 2001-04-10 00:00:00 |
| 15 | 貂蝉   | 17799990014 | 84958948374@qq.com    | 软件工程             |   40 | 2      | 3      | 2001-02-12 00:00:00 |
| 16 | 妲己   | 17799990015 | 2783238293@qq.com     | 软件工程             |   31 | 2      | 0      | 2001-01-30 00:00:00 |
| 17 | 芈月   | 17799990016 | xiaomin2001@sina.com  | 工业经济             |   35 | 2      | 0      | 2000-05-03 00:00:00 |
| 18 | 嬴政   | 17799990017 | 8839434342@qq.com     | 化工                 |   38 | 1      | 1      | 2001-08-08 00:00:00 |
| 19 | 狄仁杰 | 17799990018 | jujiamlm8166@163.com  | 国际贸易             |   30 | 1      | 0      | 2007-03-12 00:00:00 |
| 20 | 安琪拉 | 17799990019 | jdodm1h@126.com       | 城市规划             |   51 | 2      | 0      | 2001-08-15 00:00:00 |
| 21 | 典韦   | 17799990020 | ycaunanjian@163.com   | 城市规划             |   52 | 1      | 2      | 2000-04-12 00:00:00 |
| 22 | 廉颇   | 17799990021 | lianpo321@126.com     | 土木工程             |   19 | 1      | 3      | 2002-07-18 00:00:00 |
| 23 | 后羿   | 17799990022 | altycj2000@139.com    | 城市园林             |   20 | 1      | 0      | 2002-03-10 00:00:00 |
| 24 | 姜子牙 | 17799990023 | 37483844@qq.com       | 工程造价             |   29 | 1      | 4      | 2003-05-26 00:00:00 |
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
24 rows in set (0.00 sec)

1.2、查看tb_user的索引

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email            |            1 | email       | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)

mysql>

1.3、查询 phone=‘17799990015’

mysql> select * from tb_user where phone='17799990015';
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone       | email             | profession | age  | gender | status | createtime          |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 软件工程   |   31 | 2      | 0      | 2001-01-30 00:00:00 |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

1.4、执行计划 phone=‘17799990015’

mysql> explain select * from tb_user where phone='17799990015';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone | idx_user_phone | 46      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

1.5、查询 substring(phone,10,2) =‘15’

mysql> select * from tb_user where substring(phone,10,2) ='15';
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone       | email             | profession | age  | gender | status | createtime          |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 软件工程   |   31 | 2      | 0      | 2001-01-30 00:00:00 |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

1.6、执行计划 substring(phone,10,2) =‘15’

mysql> explain select * from tb_user where substring(phone,10,2) ='15';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

phone索引失效

2、字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

2.1、查询 phone=17799990015

mysql> select * from tb_user where phone=17799990015;
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone       | email             | profession | age  | gender | status | createtime          |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 软件工程   |   31 | 2      | 0      | 2001-01-30 00:00:00 |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

2.2、执行计划 phone=17799990015

mysql> explain select * from tb_user where phone=17799990015;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | idx_user_phone | NULL | NULL    | NULL |   24 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql>

字符串没有加单引号，所以phon索引失效

2.3、执行计划 profession=‘软件工程’ and age=31 and status=0

mysql> explain select * from tb_user where profession='软件工程' and age=31 and status=0;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 49      | const,const |    1 |    10.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql>

profession的key_len=47
age的key_len=2
由于status是char字符类型，所以需要加单引号，如果不加单引号，索引会失效
此时索引status失效

3、模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。
如果是头部模糊匹配，索引失效。

3.1、查询 profession like ‘软件%’

mysql> select * from tb_user where profession like '软件%';
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
| id | name   | phone       | email              | profession | age  | gender | status | createtime          |
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
|  5 | 花木兰 | 17799990004 | 19980729@sina.com  | 软件工程   |   23 | 2      | 1      | 2001-04-22 00:00:00 |
|  1 | 吕布   | 17799990000 | lvbu666@163.com    | 软件工程   |   23 | 1      | 6      | 2001-02-02 00:00:00 |
| 16 | 妲己   | 17799990015 | 2783238293@qq.com  | 软件工程   |   31 | 2      | 0      | 2001-01-30 00:00:00 |
| 15 | 貂蝉   | 17799990014 | 84958948374@qq.com | 软件工程   |   40 | 2      | 3      | 2001-02-12 00:00:00 |
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
4 rows in set (0.00 sec)

mysql>

3.2、执行计划 profession like ‘软件%’

mysql> explain select * from tb_user where profession like '软件%';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | NULL |    4 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

profession的key_len=47
profession命中索引

3.3、执行计划 profession like ‘%软件’

mysql> explain select * from tb_user where profession like '%软件';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

头部模糊匹配，profession索引失效。

3.4、执行计划 profession like ‘%软件%’

mysql> explain select * from tb_user where profession like '%软件%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

头部尾部都模糊查询，profession索引更失效。

在这里插入图片描述

小丁学Java
关注
  • 5
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
博客
固定Linux的ip地址，通过图形化界面操作，简单易上手
05-27 1084
固定Linux的ip地址，通过图形化界面操作，简单易上手。
博客
运行SpringBootTest报 UnsatisfiedDependencyException 和 NoSuchBeanDefinitionException 异常的解决思路分析
05-22 717
运行SpringBootTest报 UnsatisfiedDependencyException 和 NoSuchBeanDefinitionException 异常的解决思路分析。
博客
which 命令在Linux中是一个快速查找可执行文件位置的工具
07-16 407
本身并不直接支持这样的用法；这里提到的推荐用法更多是一种概念上的指导，实际上你可能需要结合其他工具（如。命令在 Linux 中用于查找并显示指定命令的绝对路径。可以显示别名和 shell 函数的完整路径。例如，你可以通过管道将。命令时遇到任何问题或发现任何 bug，可以向。命令的输出写入标准输入，这样。等）来实现类似的功能。
博客
汇总国内镜像提供了Redis的下载地址
07-16 141
要在Windows上安装Redis，您可以从官方网站下载Redis的Windows版本。然而，由于网络原因，直接从官方网站下载可能会比较慢。幸运的是，有一些国内镜像提供了Redis的下载，这可以加快下载速度。在使用这些镜像下载Redis时，请确保选择与您的Windows系统架构（32位或64位）相匹配的版本。下载完成后，通常是一个压缩文件，您需要解压并按照官方文档进行配置和启动Redis服务。如果上述链接不再有效，建议访问相应镜像站的主页搜索最新版本的Redis。
博客
Windows命令行（CMD）中，tasklist | findstr（搜索并显示包含特定字符串的进程信息）
07-16 150
但是，请注意，PID是数值，而进程名称是文本字符串。如果你正在搜索PID，确保不要包含任何前缀或空格，因为。你也可以结合使用进程名称和PID的一部分来搜索，但这样做可能会返回不期望的结果，因为。命令时，你可以搜索并显示包含特定字符串的进程信息。注意这里没有引号包围PID，因为PID是数值，且。如果你想要找到所有属于特定用户的进程，你可以使用。更直接且可能更高效，特别是当处理大量进程时。命令的输出中PID前通常会有空格，但。命令用于显示当前运行的进程列表，而。命令的输出中PID前面通常会有空格。
博客
nginx核心配置文件结构
07-15 374
从前面的内容学习中，我们知道Nginx的核心配置文件默认是放在/usr/local/nginx/conf/nginx.conf，这一节，我们就来学习下nginx.conf的内容和基本配置方法。读取Nginx自带的Nginx配置文件，我们将其中的注释部分【学习一个技术点就是在Nginx的配置文件中可以使用#来注释】删除掉后，就剩下下。
博客
nginx系统环境准备
07-15 253
现，在linux2.6内核以后的版本中，selinux已经成功内核中的一部分。准备一个内核为2.6及以上版本的操作系统，因为linux2.6及以上内核才。selinux，我们的系统会更安全，但是对于我们的学习Nginx的历程中，可以说selinux是linux史上最杰出的新安全子系统之一。这一项的要求仅针对于那些对linux系统的防火墙设置规则不太清楚的，建议大家把防火墙都关闭掉，因为我们此次课程主要的内容是对Nginx。的学习，把防火墙关闭掉，可以省掉后续Nginx学习过程中遇到的诸多。
博客
在Linux上部署Java项目过程
07-12 246
在Linux上部署Java项目过程。
博客
whereis命令是 Linux 和类 Unix 系统中的一个命令行工具，用于定位二进制程序、源代码和手册页（man pages）的位置
07-11 1037
然而，请注意，Nginx 的默认网站内容目录可能因安装和配置的不同而有所不同。命令是 Linux 和类 Unix 系统中的一个命令行工具，用于定位二进制程序、源代码和手册页（man pages）的位置。命令可能不会显示所有相关的文件和目录，特别是如果 Nginx 是通过非标准方式安装的（例如，从源代码编译并安装到非标准目录）。：这些是 Nginx 的手册页文件，分别属于不同的手册部分（man3 通常用于库函数，但在这里可能是个异常；（对于源代码，但通常这个目录不包含用户安装的软件的源代码），以及。
博客
在linux中查找 / 目录下的以.jar结尾的文件（find / -name *.jar）
07-11 271
在linux中查找 / 目录下的以.jar结尾的文件（find / -name *.jar）
博客
在linux中安装docker
07-10 219
https://download.docker.com/linux/centos/docker-ce.repo这个网站不一定可以访问，所以我们可以配置更换镜像源：如果直接从官方源添加失败，您可以尝试更换为国内的镜像源。搜索结果中提到了使用阿里云的镜像源来代替官方源3。您可以使用以下命令来添加阿里云的Docker CE仓库：
博客
LibreOffice的国内镜像安装地址和node.js国内快速下载网站
07-08 334
国内镜像包网址：https://mirrors.cloud.tencent.com/libreoffice/libreoffice/
博客
nginx安装演示（离线安装，直接安装在Linux中）
07-05 411
nginx安装演示（离线安装，直接安装在Linux中）
博客
Nginx（engine x）简介(作为Web服务器、正向代理、反向代理、负载均衡、动静分离)
07-04 781
Nginx (“engine x”) 是一个高性能的HTTP和反向代理服务器，特点是占有内存少并发能力强，事实上nginx的并发能力确实在同类型的网页服务器中表现较好，中国大陆使用nginx网站用户有：百度、京东、新浪、网易、腾讯、淘宝等。
博客
命令行运行git reflog（reference log）报错的解决办法
07-04 935
当你遇到这样的错误消息：“无法将‘git’项识别为 cmdlet、函数、脚本文件或可运行程序的名称”，这通常意味着 Git 没有被正确地安装在你的系统上，或者 Git 的可执行文件没有被添加到系统的环境变量 PATH 中。这包括了你所有的提交（commit）、分支切换（checkout）、标签操作（tag）、重置（reset）、回退（revert）等改变HEAD或分支引用位置的操作。下载最新版本的 Git，并按照安装向导进行安装。在安装过程中，确保选择了“在 PATH 中添加 Git”的选项（如果可用）。
博客
sql优化-单表优化
07-04 1389
被用来获取name字段值的最左边（也就是开头）的3个字符，然后这个结果被与字符串’abc’进行比较。只有当name字段的前3个字符恰好是’abc’时，相应的记录才会被选中并返回。：在查询条件中对索引列使用了LEFT函数，这可能导致索引无法被有效利用。优化方法是尽量避免在索引列上进行函数操作，或者考虑创建一个函数索引来覆盖这种查询。：这是一个字符串函数，用于从字符串的左边开始提取指定数量的字符。在这个例子中，它从name字段的每个值中提取前3个字符。
博客
订单服务-提交订单业务&立即购买业务
07-03 332
订单服务-提交订单业务&立即购买业务。
博客
确认下单：购物车页面点击 去结算 按钮发起两个请求trade（显示购物车的商品信息和计算商品的总金额）&findUserAddressList
07-03 1056
确认下单：购物车页面点击 去结算按钮发起两个请求trade（显示购物车的商品信息和计算商品的总金额）&findUserAddressList
博客
Redis-分布式锁（基本原理和不同实现方式对比）
07-01 553
多个线程都能看到相同的结果，注意：这个地方说的可见性并不是并发编程中指的内存可见性，只是说多个进程之间都能感知到变化的意思。，只要大家使用的是同一把锁，那么我们就能锁住线程，不让线程进行，让程序串行执行，这就是分布式锁的核心思路。那么分布式锁他应该满足一些什么样的条件呢？：程序不易崩溃，时时刻刻都保证较高的可用性。：互斥是分布式锁的最基本的条件，使得程序。：安全也是程序中必不可少的一环。常见的分布式锁有三种。
博客
MySQL-数据操作类型的角度理解 S锁 & X锁
07-01 240
MySQL-数据操作类型的角度理解 S锁 & X锁

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值