扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL索引命中与失效代码实现
  • 112

0
分享到

MySQL索引命中与失效代码实现

MySQL索引命中MySQL索引失效MySQL命中规则 2023-01-16 12:01:13 112人浏览 独家记忆
摘要

目录创建表Mysql执行优化器第一种情况：针对联合索引，是否遵循最左匹配原则；第二种情况：去掉大哥，看看索引是否命中；第三种情况：在索引列上做了函数操作，会导致索引失效而导致全表扫描第四种情况：模糊查询前缀是以%开头的，

创建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `passWord` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户密码',
  `create_essay` int(5) NOT NULL COMMENT '原创文章',
  `user_visited` int(10) NOT NULL COMMENT '被访问量',
  `user_rank` int(5) NOT NULL COMMENT '用户排名',
  `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

// 创建组合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

这里有一个组合索引的最左匹配原则：MySQL最左匹配原则

查看MySQL中索引是否命中可以使用explainh执行优化器来查看

MySQL执行优化器

执行优化器，顾名思义，优化语句的，准确来说是优化查询语句。其实就是在我们写的select语句前加一个Explain关键字。

索引的命中与失效情况

第一种情况：针对联合索引，是否遵循最左匹配原则；

建立一个组合索引

idx_username_password_user_rank(`username`,`password`,`user_rank`)
// 命中索引跟顺序无关
explain SELECT * from `user` where username =  "liuxianGCheng" and password = "515239" and user_rank = 1
explain SELECT * from `user` where user_rank = 1 and username =  "liuxiangcheng" and password = "515239"  
explain SELECT * from `user` where user_rank = 1 and password = "515239" and username =  "liuxiangcheng"

结果：

MySQL索引命中与失效代码实现

第二种情况：去掉大哥，看看索引是否命中；

// 去掉大哥
explain SELECT * from `user` where password = "515239" and user_rank = 1

去掉大哥之后，索引失效，全表扫描。

MySQL索引命中与失效代码实现

第三种情况：在索引列上做了函数操作，会导致索引失效而导致全表扫描

我们先把那个联合索引删除掉，然后在username这一列上建立一个唯一索引：

删除组合索引

drop index idx_username_password_user_rank on `user`

创建唯一索引

alter table `user` ADD UNIQUE key (`username`)

查看索引

MySQL索引命中与失效代码实现

explain SELECT * from `user` where username= 'user110819'

MySQL索引命中与失效代码实现

explain SELECT * from `user` where concat(username,'')= 'user110819'

MySQL索引命中与失效代码实现

第四种情况：模糊查询前缀是以%开头的，索引失效

explain SELECT * from `user` where username like '%user11081'

MySQL索引命中与失效代码实现

第五种情况：模糊查询中后缀是以%，可以命中索引

explain SELECT * from `user` where username like 'user11081%'

MySQL索引命中与失效代码实现

第六种情况：使用is not null 会导致索引失效

explain SELECT * from `user` where username is not null

MySQL索引命中与失效代码实现

第六种情况：使用and时，其中有一个条件查询带有索引而另一个不带索引，不会导致索引失效。而使用or时，如果条件查询中其中一个不带索引，导致索引失效，必须全部带有索引。

and情况：

explain SELECT * from `user` where username = "liuxiangcheng" and password = "515239"

MySQL索引命中与失效代码实现

or情况：

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

MySQL索引命中与失效代码实现

我们给user_rank加上索引

alter table `user` ADD index (`user_rank`)

MySQL索引命中与失效代码实现

再次查询：

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

MySQL索引命中与失效代码实现

第七种情况：使用不等于(!= 或者<>)的时候，会导致索引失效

explain SELECT * from `user` where   user_rank != 1

or 

explain SELECT * from `user` where   user_rank <> 1

MySQL索引命中与失效代码实现

 第八种情况：使用范围查询之后索引失效

explain SELECT * from `user` where user_rank >(<,>=,<=) 1

MySQL索引命中与失效代码实现

 第九种情况：隐式转换可能会导致我们的索引失效

varchar类型，如果用int类型来查询，索引失效

数据库user表中我们的password是varchar类型

如果我们在条件查询中使用整形来替代，那么这个时候索引就会失效，where varchar = int 索引失效

// password is varchar type

explain SELECT * from `user` where password = 515239

explain SELECT * from `user` where password = '515239'

explain SELECT * from `user` where password = "515239"

MySQL索引命中与失效代码实现

如果是int类型，我们使用varchar来替代，索引命中

// user_rank is int type

explain SELECT * from `user` where user_rank = "1"

explain SELECT * from `user` where user_rank = '1'

explain SELECT * from `user` where user_rank = 1

MySQL索引命中与失效代码实现

总结隐式转换：

  1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
  2. 当 where 查询操作符左边为数值类型时发生了隐式转换，但是索引会命中，对查询效率影响不大，但还是不推荐这么做。
  3. 当 where 查询操作符左边为字符类型时发生了隐式转换，这样会导致索引失效，造成全表扫描。
  4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

总结

MySQL中索引失效的情况

1、组合索引中不遵循最左匹配原则，带头大哥不在，导致索引失效，全表扫描。
2、在索引列上做了函数操作，导致索引失效，全表扫描。
3、模糊查询前缀是以%开头的，导致索引失效，全表扫描。
4、使用is not null 会导致索引失效。
5、使用or时，如果条件查询中其中一个不带索引，导致索引失效，全表扫描。
6、使用不等于(!= 或者<>)的时候，会导致索引失效。
7、使用范围查询（>、<、>=、<=）之后索引失效。
8、隐式转换可能会导致我们的索引失效。

 查看MySQL中索引是否命中可以使用explainh执行优化器来查看。

到此这篇关于MySQL索引命中与失效代码实现的文章就介绍到这了,更多相关MySQL索引命中与失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

--结束END--

本文标题: MySQL索引命中与失效代码实现

本文链接: https://lsjlt.com/news/177842.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL索引命中与失效代码实现
    目录创建表mysql执行优化器第一种情况：针对联合索引，是否遵循最左匹配原则；第二种情况：去掉大哥，看看索引是否命中；第三种情况：在索引列上做了函数操作，会导致索引失效而导致全表扫描第四种情况：模糊查询前缀是以%开头的，...
    99+
    2023-01-16
    MySQL索引命中 MySQL索引失效 MySQL命中规则
  • mysql索引失效的现象有哪些
    本篇内容介绍了“mysql索引失效的现象有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读...
    99+
    2024-04-02
  • mysql中出现索引失效的情况有哪些
    本篇文章给大家分享的是有关mysql中出现索引失效的情况有哪些，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从...
    99+
    2023-06-15
  • mysql中出现索引失效的原因是什么
    这篇文章将为大家详细讲解有关mysql中出现索引失效的原因是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前...
    99+
    2023-06-08
  • mysql中怎么实现重复索引与冗余索引
    这篇文章将为大家详细讲解有关mysql中怎么实现重复索引与冗余索引，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。重复索引：表示一个列或者顺序相同的几个列上建...
    99+
    2024-04-02
  • MySQL中索引失效的常见场景与规避方法
    前言 之前有看过许多类似的文章内容，提到过一些sql语句的使用不当会导致MySQL的索引失效。还有一些MySQL“军规”或者规范写明了某些sql不能这么写，否则索引失效。 绝大部分的内容笔者是认可的，不...
    99+
    2024-04-02
  • MySQL索引失效十种场景与优化方案
    目录1 数据准备1.1 新建数据表1.2 新增100万条数据2 基础知识2.1 explain type2.2 explain Extra3 索引失效场景3.1 查询类型错误3.1....
    99+
    2023-05-19
    MySQL索引失效场景 MySQL索引优化 MySQL索引失效与优化
  • oracle分区索引的失效和重建代码示例
    上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容，接下来的这篇文章，我们将探讨oracle分区索引的失效和重建问题，提供了相关代码示例供大家参考，具体如下。 --创建测试表 SQL&...
    99+
    2024-04-02
  • mysql中什么情况会导致索引失效
    这篇文章主要为大家展示了“mysql中什么情况会导致索引失效”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql中什么情况会导致索引失效”这篇文章吧。为了讲解以下索引内容，我们先建立一个临时...
    99+
    2023-06-25
  • mysql中like索引失效问题怎么解决
    在MySQL中，LIKE索引失效的问题通常是由以下几个原因造成的： 通配符在开头：如果LIKE操作符的通配符（%）在搜索模式的开...
    99+
    2024-02-29
    mysql
  • mysql中联合索引生效的条件及索引失效的条件是什么
    这篇文章主要介绍mysql中联合索引生效的条件及索引失效的条件是什么，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！1.联合索引失效的条件联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引：Mys...
    99+
    2023-06-25
  • Android通用索引栏实现代码
    偶尔看到之前写过的代码,感觉好多东西几乎在很多项目中都要用到,虽然每个项目的需求和设计都不同,不过实现的效果都是一样的,可能只是数据格式和一些颜色等的细微差距.但是有的时候因为...
    99+
    2022-06-06
    索引 Android
  • Mysql实现简易版搜索引擎的示例代码
    目录前言简介ngram 全文解析器创建全文索引检索方式1、自然语言检索（NATURAL LANGUAGE MODE）2、布尔检索（BOOLEAN MODE）与 Like 对比总结前言...
    99+
    2024-04-02
  • android 左右滑动+索引图标实现方法与代码
    使用Gallery和ImageView实现android左右滑动+索引图标效果。 首先自定义Gallery实现一次只能滑动一个页面 代码如下: public class M...
    99+
    2022-06-06
    方法 索引 Android
  • mysql字段为NULL索引是否会失效实例详解
    项目场景： 很多博客说mysql在字段中创建普通索引，如果该索引中的数据存在null值是不走索引这个结论是错误的，不过尽量还是设置默认值。（版本8.0低于这个版本可能结果不一致） 1...
    99+
    2024-04-02
  • MySQL细数发生索引失效的情况实例分析
    这篇文章主要介绍了MySQL细数发生索引失效的情况实例分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇MySQL细数发生索引失效的情况实例分析文章都会有所收获，下面我们一起来看看吧。索引的存储结构首先了解一下...
    99+
    2023-07-02
  • PHP与MySQL索引的失效情况及如何避免和解决
    引言：在开发Web应用程序时，PHP和MySQL往往是常用的编程语言和数据库。而在处理大量数据时，索引是提高查询性能的重要因素之一。然而，索引的失效情况可能会导致查询变慢，从而影响应用程序的性能。本文旨在介绍PHP与MySQL索引的失效情况...
    99+
    2023-10-21
    MySQL PHP 索引 避免 失效
  • 如何利用PHP与Spring实现高效实时索引？
    随着信息量的不断增加，搜索引擎的效率也变得越来越重要。实时索引是一种能够让搜索引擎更快速地响应用户查询的方法。在本文中，我们将介绍如何利用PHP与Spring实现高效实时索引。 一、什么是实时索引？ 传统的搜索引擎是将数据存储在磁盘中，然后...
    99+
    2023-09-08
    索引 实时 spring
  • mysql索引内部实现与算法分析
    本篇内容主要讲解“mysql索引内部实现与算法分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql索引内部实现与算法分析”吧! ...
    99+
    2024-04-02
  • Mysql使用全文索引(FullText index)的实例代码
    目录什么是全文索引注意创建全文索引使用全文索引自然语言搜索BOOLEAN MODE总结什么是全文索引 全文索引,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题....
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作