不懂索引,简历上都不敢写自己熟悉SQL优化

2024-06-20 17:12

本文主要是介绍不懂索引,简历上都不敢写自己熟悉SQL优化,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

大家好,我是考哥。

今天给大家带来MySQL索引相关核心知识。对MySQL索引的理解甚至比你掌握SQL优化还重要,索引是优化SQL的前提和基础,我们一步步来先打好地基。

当MySQL表数据量不大时,缺少索引对查询性能的影响不会太大,可能都是0.0几秒;但当表数据量逐日递增时,建立一个合适且优雅的索引就至关重要了。

文章目录

    • 1. 索引类型
      • 1.1 B-Tree索引
      • 1.2 B-Tree值的存储
      • 1.3 哈希索引
      • 1.4 聚簇索引
    • 2. 索引效率
      • 2.1 Explain关键字
      • 2.2 索引失效

1. 索引类型

面试官:索引有什么用?

大家可以把你最近最爱的一本书类比成一个MySQL数据库,你要快速翻到你昨天看到的精彩部分,是不是要先看下书的目录索引,要翻到第几章、第几页。

数据库最主要的就是数据存储,其次就是提供复杂查询服务,而索引就是MySQL作为快速找到记录的一种数据结构。索引类型有多种,像常见的B树索引、哈希索引,这些都需要我们去掌握。

不要和我说你看书都用书签,或者靠手感就能翻出来昨天看到的地方。

我们对比下不采用索引和采用索引的差异。

目前我本机数据库的article表有10w条数据,表结构如下。

CREATE TABLE `article`  (`id` int(10) NOT NULL AUTO_INCREMENT,`author_id` int(10) NULL DEFAULT NULL,`category_id` int(10) NOT NULL DEFAULT 0,`views` int(10) NULL DEFAULT NULL,`comments` int(10) NULL DEFAULT NULL,`title` varbinary(255) NULL DEFAULT NULL,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,PRIMARY KEY (`id`, `category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

没建立索引前,使用explain关键字分析查询SQL。type显示ALL,也就是该SQL执行时对MySQL进行的是全表扫描。

explain select id from article where category_id = 1 order by views desc;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL | 102279 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+

建立索引后。

create index idx_ca_vi on article(category_id,views);

type显示为ref,同时Extra列显示Using where; Using indexUsing index代表该SQL执行时使用了索引,而Using index代表了在MySQL服务端再进行了一次views字段的排序。

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_ca_vi     | idx_ca_vi | 4       | const |    51139 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

1.1 B-Tree索引

面试官:B树索引说一下?

在杂乱无章的一堆数字里,我要你快速找到唯一的一个数字66,大家要怎么做?

两种选择,你在一堆数字里一个个地找,就如MySQL全表扫描。或者把所有数都按大小顺序进行排列,找到第66个位置的数字。

我们假设建立的是主键索引,MySQL索引会根据主键id建立起一棵B-Tree。B-Tree类似于二叉搜索树,同样具有快速查找特定值的功能。

(1)但在结构方面,B-Tree又不同于二叉搜索树,它是多子树的。即每一个节点可以有两棵以上的子树。

(2)在值的存储方面,B-Tree所有的值都存储在叶子节点。并且每一个叶子节点可以存储多个元素,这一点也与二叉搜索树不同。两个人想要去湖里打水,一个人拿着手大的碗,一个人拿着一个水桶,拿水桶的不会比拿碗的装的少。每个叶子节点存储的元素多,每次磁盘访问就可以获得更多的数据,从而减少查询的I/O操作。

面试官经常会问你这个问题,叶子节点是什么数据结构?。实际上叶子节点之间用指针链接形成了一串双向链表。这个留到下文解释。

(3)另外大家很容易漏掉一个重要的知识点。如果是二级索引建立的B-Tree,每个叶子节点的值保存的是对应行数据的主键。那一级索引叶子节点保存什么呢?一级索引也就是主键索引,下文我会告诉大家。

在这里插入图片描述

1.2 B-Tree值的存储

面试官:你说值都存储在叶子节点,那有什么好处?

数据库数据都存储在叶子节点,会使得非叶子节点层数更少。从外表来看,很明显整棵B-Tree的层数变少,B-Tree高度变得矮胖

B-Tree变得矮胖有什么作用?举个爬楼梯的例子,B-Tee的每一层级就像一层楼。相信大家租房都不想租高楼,每次回去都要爬那么多层楼梯,膝盖怎么受得了呢。

B-Tree每一层的搜索可能就代表了一次磁盘I/O操作,B-Tree的层数变少意味着I/O读取的次数就变少,查询的效率也会因此提高。

另外企业业务在查询上更多的是范围查询,你对网页的每一次翻页操作都是对MySQL数据的一次范围查询。B-Tree的元素都存储叶子节点,同时形成双向链表结构,很适合范围查询这种复杂查询操作。

1.3 哈希索引

面试官:知道为什么主流数据库引擎不采用哈希索引吗?

上文其实已经有涉及到,业务上一般都是范围查询,而哈希索引由于其底层数据结构,不能够支持任何范围查询。这也难怪主流数据库引擎不青睐它。

但其实哈希索引也有它的闪光灯,哈希索引会为所有的索引列计算一个哈希码。同时在哈希表中保存哈希码和指向每个数据行的指针,这种结构对精确匹配查询的效率极高。

MEMORY数据库引擎底层采用的就是哈希索引。

1.4 聚簇索引

面试官:聚簇索引和二级索引有什么关联?

读到这里,我回答下上文还没回答大家的问题。

首先,聚簇索引和主键索引是等同的,也有一个一般都不提的名称:一级索引。

而B-Tree的二级索引指的是非主键索引,它的叶子节点保存的只是行的主键值,所以需要另外通过主键来找到行数据。

聚簇索引通过主键来建树,它的叶子节点包含了行的全部数据

这就把两者相关联起来了,通过二级索引查找行,需要先在二级索引建立的B-Tree上找到主键的值,接着再从聚簇索引建立的B-Tree找到行数据。

2. 索引效率

2.1 Explain关键字

面试官:那我一条SQL,我怎么知道它有没使用到索引?

面试官看你简历写了掌握MySQL,那这道问题就是必考题。

检查是否使用索引可以利用Explain关键字来分析,它会模拟执行sql语句,查询出sql语句执行的相关信息,如哪些索引可以被命中、哪些索引实际被命中。

我说下Explain查询结果的几个关键字段。

  • type

    • cost:通过索引一次查询
    • ref:使用到索引
    • range: 使用到索引
    • all:全表扫描
  • Extra

    • using filesort:使用外部文件排序,发生在无法使用索引的情况下

    • using index:where查询的列索引覆盖,直接通过索引就可以查询到数据

    • using where:where查询的列,没有全部被索引覆盖

    • using join buffer:使用了连接缓存

  • possible_key

    表示可以使用的索引

  • key

    表示实际使用的索引

如果简历你写了精通MySQL,那问的可就没这么简单。我可以问你在工作中紧急处理了哪些数据库重大事故,优化了哪些业务慢SQL、是怎么优化的、为什么这么做。

2.2 索引失效

面试官:有没索引失效的情况呢?

索引失效一般是这个SQL查询破坏了使用B-Tree查询的条件。也有一种可能出现,如果表数据膨胀得太快,即使建立索引你查询起来也会有索引失效的错觉,这个问题就要另外讨论了。

  1. 如果在where子句中使用not in、!=和<>操作,会使索引失效而导致进行全表扫描。

  2. 对索引列进行数学函数处理的话,索引会失效。

  3. 索引是字符串类型,查询值没有添加单引号’'那索引会失效。因为值类型与索引列类型。不一致,MySQL不会使用索引,而是把索引列数据进行类型转换后进行查询。

  4. 对索引列进行模糊查询,%要放在最右侧,否则索引会失效。SELECT * FROM user WHERE name LIKE n%

  5. 在组合索引中,如果前一个索引使用范围查询,后面的索引也会失效。

大家在实际工作切忌乱加索引,此切忌切记。每加一次索引,MySQL都要多去维护一棵新的B-Tree。增加太多索引,数据查询效率会变得低下。

本文收录在我开源的《Java学习面试指南》中,目前已经更新有近200道面试官常考的面试题,涵盖了Java系列、Redis系列、MySQL系列、多线程系列、Kafka系列、JVM系列、ZooKeeper系列等等。GitHub地址:https://github.com/hdgaadd/JavaGetOffer,相信你看了一定会有所收获。

创作不易,不妨点赞、收藏、关注支持一下,各位的支持就是我创作的最大动力❤️

这篇关于不懂索引,简历上都不敢写自己熟悉SQL优化的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1078753

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误