不懂索引,简历上都不敢写自己熟悉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

相关文章

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》:本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一:桌面图标进入方法二:运行窗口进入方法三:查找文件路径方法四:检查 SQL Server 安