首页 > 数据库 >mysql索引（索引失效，遵循最左前缀，使用1.全值匹配 2.覆盖索引，失效：索引加函数，范围查询右边的列，!=, is null, like左模糊，传参类型不匹配，or/in）

mysql索引（索引失效，遵循最左前缀，使用1.全值匹配 2.覆盖索引，失效：索引加函数，范围查询右边的列，!=, is null, like左模糊，传参类型不匹配，or/in）

时间：2024-03-18 18:34:00浏览次数：23  
标签：匹配 name age 索引 EXPLAIN 失效 employees SELECT

1.遵循 联合索引最左列原则

当表中创建了一个 联合索引 idx_name_age_position

案例演示

1.当我们在执行sql 语句：以name 为where 条件时，我们可以用到索引

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

在这里插入图片描述
2.当我们在执行sql 语句：以age为where 条件时，索引就会失效

EXPLAIN SELECT * FROM employees WHERE age = 30;

在这里插入图片描述
3.那我们怎么判断用了联合索引的哪几个索引呢？

  • 执行以name 为查询条件的
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

在这里插入图片描述

  • 执行以 name和age为查询条件的
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

在这里插入图片描述

  • 执行以 name ,age ,position为查询条件的
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

key_len 分析

分析 ： key_len 代表的是 索引使用的字节长度
具体字节长度 参考文章 Explain执行计划 https://blog.csdn.net/king_zzzzz/article/details/136801003
创建表的时候，name 为 varchar (24) age int(11) position (20) 且索引字段都不为null

  • 若使用 name 索引 ，那么key_len = 24*3+2 =74

  • 若使用 name +age 索引, 那么ley_len = key_len(name) + 4 =78

  • 若使用 name +age +position 索引, 那么ley_len = key_len(name) + key_len(age ) + 20*3+2 =140
    在这里插入图片描述

  • 创建表的时候，所有的索引字段都不为null
    在这里插入图片描述
    那么每个ley_len = key_len(name) + key_len(age) + key_len(position)
    在这里插入图片描述

  • 若是索引字段允许为null
    在这里插入图片描述
    那么每个ley_len = (key_len(name)+1) + (key_len(age) +1) +( key_len(position) +1)
    原因是：如果字段允许为 NULL，需要1字节记录是否为 NULL
    在这里插入图片描述

2.进行 全值匹配

若是联合索引，那么查询字段越多越好，这样 效率更高（可以判断key_len 是否和联合索引 字节长度判断）

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

3.不建议 在索引列上做任何操作，否则索引会失效

以下sql 的查询条件 在索引树中是 无法定位的，且是无序的

-- 查询name的最左边的两个字符为Li的行
EXPLAIN SELECT * FROM employees WHERE LEFT(name,2) = 'Li';

在这里插入图片描述

特例（范围查询）

给时间字段添加个索引

ALTER TABLE employees ADD INDEX idx_hire_time(hire_time)USING BTREE;
  • 当我们执行下面sql时，索引会失效，因为我们在索引字段上加了函数
EXPLAIN select * from employees where date(hire_time)='2018-09-30';

在这里插入图片描述

  • 转化为日期范围查询，就可能会走索引，但具体会不会走，根据表中的数据量来定，若全表扫描的效率> 走索引的效率，就会全部扫描
EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-3023:59:59';

在这里插入图片描述

4.不能使用联合索引中范围条件右边的列

查询的条件中的联合索引字段 使用了范围查询，那么联合索引中，该范围查询的字段列 右边的不走索引

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

key_len =keylen(name) +key_len(age)
原因： 索引树是 有序的 范围查找时 ，该字段是有序的，但是 右边的字段就不是有序的了
在这里插入图片描述

5.尽量使用覆盖索引 不需要再回表查询了 减少 select *

  • 未使用覆盖索引，进行回表
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

  • 使用了覆盖索引，在索引树就可以查找到
EXPLAIN SELECT id,name,age,position FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

6.使用 != 或 <> 不等于查询时，会导致索引失效。

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

在这里插入图片描述

7.is null ，is not null一般情况下也无法使用索引

mysql 会将null 的所有值放在一起存储

EXPLAIN SELECT * FROM employees WHERE name is   null;

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name is not  null;

在这里插入图片描述

8.like查询建议使用xxx%方式匹配，%xxx或者%xxx%索引失效

  • 右模糊
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

在这里插入图片描述

  • 左模糊
EXPLAIN SELECT * FROM employees WHERE name like '%Lei';

在这里插入图片描述

  • 左右模糊
EXPLAIN SELECT * FROM employees WHERE name like '%Lei%';

在这里插入图片描述
分析： 索引树中 字符串的排序是从左到右 来进行排序的

优化：对于左右模糊的sql 语句，建议一般采用 覆盖索引来进行优化，

EXPLAIN SELECT  id,name,age,position FROM employees WHERE name like '%Lei%';

虽然 是index ,但已经 大于 all 了
在这里插入图片描述

9.字符串查询 不加引号 索引也会失效

分析：若是类型不匹配，就可能会使用函数 将 该字段 转换

explain SELECT * from employees where name = 1324;

在这里插入图片描述

10.少用 or 或 in ，mysql 不一定会走索引

内部优化器会根据索引树，表大小等来进行评估是否需要走索引

EXPLAIN SELECT *  FROM employees WHERE name ='LiLei'or name = 'HanMeimei';

在这里插入图片描述

范围查找优化（缩小范围）

先给 age 加一个单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

当查找 age 范围 为 1-200 时的数据时

-- 查询age 在1到1000分为内的数据
explain SELECT * from employees where age >1 and age < 1000 ;

在这里插入图片描述
分析：
我们要找到1-1000的数据，那么在这棵树书上怎么定位？

  • 首先会定位一个age=2在树上的位置 在定位一个age=1999在树上的位置，然后从age=2的节点开始取右边的节点，一直取下去 直到age=1999为止。
  • 但是我们表总只有2条数据，mysql觉得这样操作还没有全表扫描快，毕竟一共才几条数据全表扫描反而更快些，所以mysql就去全表扫描了。
  • 优化：将范围缩小，就可能用到索引

在这里插入图片描述

总结

idx_a_b_c（a,b,c）
在这里插入图片描述

建表sql

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='员工记录表';



INSERT INTO employees(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei',23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());

标签：匹配,name,age,索引,EXPLAIN,失效,employees,SELECT
From： https://blog.csdn.net/king_zzzzz/article/details/136813110

相关文章

  • oracle数据库执行报错：ORA-01861: 文字与格式字符串不匹配
    报错sql：selectto_date(sysdate,'yyyy-mm-dd')afromuser原因是:to_date()函数第一个参数，要求的是一个字符串格式，当这个值是一个日期格式的时候就会报错，解决方案：方案一：使用to_char(日期,'yyyy-mm-dd')将日期转为字符，再使用to_date()，方案二:修改数据库配置，让数据库隐式......
  • 【PostgreSQL PGCE-091题目解析14】PostgreSQL中使用CONCURRENTLY选项创建索引时，允许
    本文为云贝教育刘峰（微信：yunbee_DBA）原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。PostgreSQL中使用CONCURRENTLY选项创建索引时，允许增删改数据表。A.正确B.错误参考答案：A解析：我们知道，PG是有行级琐的，在创建索引的时候，会在行上加琐......
  • 初窥-openGauss-之索引推荐Index-advisor
    初窥openGauss之索引推荐Index-advisorTPC-H是一个面向分析型业务(AP)的基准测试，它由一系列热点查询组成，这些热点查询都是高度复杂的，因此执行时间往往都比较长。在本次实验测试中，将手动向数据库加载TPC-H数据，并保存在名为tpch的数据库中。默认TPC-H数据库的表缺少索引......
  • openGauss的索引组织表
    openGauss的索引组织表概述今天有位小伙伴问我，Oracle数据库可以通过索引组织表(IOT)将数据按照主键排序存储，有序的数据存储可以有效提高数据库缓冲区的命中率，减少SQL查询的IO消耗，提升查询效率。而openGauss的建表语句中并没有看到索引组织表的相关语法。openGauss目前......
  • @Transactional底层实现和失效场景
    本文介绍下@Transactional底层实现和哪些场景会导致其失效当使用@Transactional注解标注一个方法时，springboot会在运行时生成一个代理对象，该代理对象拦截被注解的方法调用，并在方法调用前后进行事务管理。事务管理包括开启事务、提交事务或者回滚事务等操作。@Transactional实现......
  • 整块代码自动生成、智能括号匹配……CodeGeeX编程提效，功能再升级！
    CodeGeeX插件功能持续打磨，希望成为开发者更高效的智能编程工具，提高开发速度和代码质量。今天介绍VSCode中最新的v2.4.0版本插件新功能，让你在编写代码时更加得心应手。一、新增block代码块生成的设置CodeGeeX插件中，以往针对代码生成的行数，只有Automatic和Linebyline两种模式。......
  • SQL 查询优化之 WHERE 和 LIMIT 使用索引详解
    奇怪的慢sql我们先来看2条sql第一条：第二条：表的索引及数据总情况： 索引：acct_id，create_time分别是单列索引，数据库总数据为500w。通过acct_id过滤出来的结果集在1w条左右。 查询结果：第一条要5.018s，第二条0.016s为什么会是这样的结果呢？第一，acct_id和create_time都有索引，不......
  • Mybatis-plus构建wrapper条件时出现索引越界异常Caused by: org.apache.ibatis.except
    项目场景：学习springboot整合mybatis-plus时通过构建器执行相关操作@AutowiredBookMappermapper;@Testvoidtest(){QueryWrapper<Book>wrapper=newQueryWrapper<>();wrapper.select("id","name","press")//只查询前三......
  • 字符串匹配/查找字符串中子串存在次数/出现位置下标 问题----- {1.[find] 2.[substr]
    下文将介绍三种方法，求解问题类型：1.子串在主串中出现次数2.子串在主串中每次出现的下标位置以此题为例：题目链接：https://www.luogu.com.cn/problem/P8195解法一：kmp#include<iostream>#include<string>usingnamespacestd;constintN=1e6+10;intne[N];......
  • 图的匹配与网络流技巧总结
    1.拆点1.1入点和出点P2764最小路径覆盖问题考虑建图，将一个点\(i\)的出点拆成\(u_i\)，如入点拆成\(v_i\)，一条边\((x,y)\)等价于连一条\(u_x\)到\(v_y\)的边。显然这是个二分图，不难发现这个二分图的最大匹配与一种路径覆盖一一映射，所以答案就是总点数减去最大匹配。......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99