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

本文涉及的产品
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
简介: 大家好,我是考哥。今天给大家带来MySQL索引相关核心知识。对MySQL索引的理解甚至比你掌握还重要,索引是优化SQL的前提和基础,我们一步步来先打好地基。当MySQL表数据量不大时,缺少索引对查询性能的影响都不会太大,可能都是0.0几秒;但当表数据量逐日递增时,建立一个合适且优雅的索引就至关重要了。

大家好,我是考哥。

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

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

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,相信你看了一定会有所收获。

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
SQL 存储 数据库
性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
|
1天前
|
SQL 缓存 数据库
如何进行SQL优化?
【7月更文挑战第21天】如何进行SQL优化?
9 1
|
15天前
|
SQL 运维 监控
MSSQL性能调优实战技巧:索引优化、SQL查询优化与并发控制策略
在Microsoft SQL Server(MSSQL)的运维过程中,性能调优是确保数据库高效运行、满足业务需求的关键环节
|
1天前
|
SQL 索引
索引不合理导致的SQL执行效率低怎么优化
索引不合理导致的SQL执行效率低怎么优化
|
12天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
23 0
|
13天前
|
SQL 存储 关系型数据库
常见的一些SQL优化方式
SQL 优化是提高数据库性能和查询效率的重要手段。
13 0
|
SQL Go 数据库
SQLSERVER中如何忽略索引提示
原文:SQLSERVER中如何忽略索引提示 SQLSERVER中如何忽略索引提示 当我们想让某条查询语句利用某个索引的时候,我们一般会在查询语句里加索引提示,就像这样 SELECT id,name from TB with (index(IX_xttrace_bal)) where ba...
1018 0
|
2天前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
|
6天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
28 3
|
12天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。