面试官:讲讲MySql索引失效的几种情况

这篇具有很好参考价值的文章主要介绍了面试官:讲讲MySql索引失效的几种情况。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

索引失效

准备数据:

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

1、计算、函数导致索引失效

-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

2、LIKE以%开头索引失效

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

拓展:Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

3、不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

注意:当数据库中的数据的索引列的NULL值达到比较高的比例的时候,即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引,此时type的值是range(范围查询)

-- 将 id>20000 的数据的 name 值改为 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 执行查询分析,可以发现 IS NOT NULL 使用了索引
-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效,由查询优化器的算法决定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

测试完将name的值改回来

UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

5、类型转换导致索引失效

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

6、全值匹配我最爱

准备:

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题:为以下查询语句创建哪种索引效率最高

-- 查询分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 执行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看执行时间
SHOW PROFILES;

创建索引并重新执行以上测试:

-- 创建索引:分别创建以下三种索引的一种,并分别进行以上查询分析
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_age_deptid ON emp(age,deptid);
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

结论:可以发现最高效的查询应用了联合索引 idx_age_deptid_name

7、最佳左前缀法则

准备:

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

问题:以下这些SQL语句能否命中 idx_age_deptid_name 索引,可以匹配多少个索引字段

测试:

  • 如果索引了多列,要遵守最左前缀法则。即查询从索引的最左前列开始并且不跳过索引中的列。
  • 过滤条件要使用索引,必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
-- EXPLAIN结果:
-- key_len:5 只使用了age索引
-- 索引查找的顺序为 age、deptid、name,查询条件中不包含deptid,无法使用deptid和name索引

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果:
-- type: ALL, 执行了全表扫描
-- key_len: NULL, 索引失效
-- 索引查找的顺序为 age、deptid、name,查询条件中不包含age,无法使用整个索引

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果:
-- 索引查找的顺序为 age、deptid、name,匹配所有索引字段

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
-- EXPLAIN结果:
-- 索引查找的顺序为 age、deptid、name,匹配所有索引字段

8、索引中范围条件右边的列失效

准备:

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题:为以下查询语句创建哪种索引效率最高

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc'; 

测试1:

-- 创建索引并执行以上SQL语句的EXPLAIN
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
-- key_len:10, 只是用了 age 和 deptid索引,name失效

注意:当我们修改deptId的范围条件的时候,例如deptId>100,那么整个索引失效,MySQL的优化器基于成本计算后认为没必要使用索引了,所以就进行了全表扫描。(注意:因为表中的数据是随机生成的,因此实际测试中根据具体数据的不同测试的结果也会不一样,最终是否使用索引由优化器决定)

测试2:

-- 创建索引并执行以上SQL语句的EXPLAIN(将deptid索引的放在最后)
CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
-- 使用了完整的索引

补充:以上两个索引都存在的时候,MySQL优化器会自动选择最好的方案文章来源地址https://www.toymoban.com/news/detail-508057.html

到了这里,关于面试官:讲讲MySql索引失效的几种情况的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Mysql进阶优化篇02——索引失效的10种情况及原理

    前 言 🍉 作者简介:半旧518,长跑型选手,立志坚持写10年博客,专注于java后端 🍌 专栏简介:mysql基础、进阶,主要讲解mysql数据库sql刷题、进阶知识,包括索引、数据库调优、分库分表等 🌰 文章简介:本文将介绍索引失效的10种情况及原理,绝对不需要死记硬背,建议收

    2024年02月02日
    浏览(42)
  • 索引失效的 12 种情况

    目录 一、未使用索引字段进行查询 二、索引列使用了函数或表达式 三、使用了不等于(!= 或 )操作符 四、LIKE 操作符的模糊查询 五、对索引列进行了数据类型转换 六、使用 OR 连接多个条件 七、表中数据量较少 八、索引列上存在大量重复值 九、数据分布不均匀 十、索引

    2024年02月19日
    浏览(36)
  • SQL笔记 -- 索引失效情况

    对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。 例如以下情况,索引从左到右的顺序为age,classId,name,但是在查询时跳过了age,因此该查询无法使用索引。 查询条件中使用了函数会导致索引失

    2024年01月20日
    浏览(38)
  • 索引失效的七种情况

    以上这些情况都可能导致数据库查询时无法有效地使用索引,从而影响查询性能。为了避免索引失效,需要优化查询语句,合理设计索引,尽量避免上述情况的出现。

    2024年02月10日
    浏览(34)
  • 关于load过高的几种情况

    Linux 中 load啥意思 \\\"Load\\\" 在 Linux 系统中通常是指系统的负载情况,也称为系统负荷。它指的是系统正在运行的进程数量以及这些进程对系统资源的使用情况,例如 CPU、内存、磁盘 I/O 等。Linux 系统的负载通常由三个数字表示,分别对应于过去 1 分钟、5 分钟和 15 分钟内的平均

    2024年02月10日
    浏览(42)
  • 避坑:.NET内存泄露的几种情况

    内存“泄露”是开发中常见的问题之一,它会导致应用程序占用越来越多的内存资源,最终可能导致系统性能下降甚至崩溃。软件开发者需要了解在程序中出现内存泄露的情况,以避免软件出现该的问题。 什么是内存“泄露”? 内存泄露是申请了内存空间的变量一直在占用

    2024年02月11日
    浏览(44)
  • JS中内存泄漏的几种情况

    JavaScript 中的内存泄漏是指程序中使用的内存不再被需要却没有被释放,最终导致浏览器或者 Node.js 进程使用的内存越来越大,直到程序崩溃或者系统运行缓慢。 在 JavaScript 中,内存泄漏通常是由于变量、对象、闭包、事件监听器等长期存在而没有被释放引起的。这些长期存

    2024年02月03日
    浏览(50)
  • 时间状语前不用介词的几种情况

    1. today,yesterday, tomorrow,tonight, yesterday/tomorrow morning(evening, afternoon),today week (下周的今天) there is a football game tomorrow morning. 2. 表示时间的短语里面有next, last, one, this,these,those,every,each,some,any,all等单词,介词应该省略。 i will see you next week. you can come any days you want. 你哪天来都可以

    2023年04月09日
    浏览(105)
  • Linux 查看内存使用情况的几种方法

    *以下内容为本人的学习笔记,如需要转载,请声明原文链接 微信公众号「ENG八戒」https://mp.weixin.qq.com/s/27UaVm5_FMhCnxB88pc0QA 在运行 Linux 系统的过程中为了让电脑或者服务器以最佳水平运行,常常需要监控内存统计信息。 那么今天我们就来看看有哪些方法可以访问所有相关信息

    2023年04月20日
    浏览(42)
  • 分享Linux 查看内存使用情况的几种方法

    Linux 查看内存使用情况的几种方法包括使用 free 命令、top 命令、htop 命令、vmstat 命令和/proc/meminfo 文件。这些方法可以帮助用户了解系统内存的使用情况,包括总内存、已用内存、空闲内存、缓存和交换分区等信息。 在运行 Linux 系统的过程中为了让电脑或者服务器以最

    2024年02月04日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包