一文搞懂 MySQL 索引

article/2025/8/17 15:00:33

一文搞懂 MySQL 索引



1、MySQL 索引 简介

1.1、MySQL 索引 是什么？

 索引是一个单独的、存储在 磁盘 上的 数据库结构 ，包含着对数据表里 所有记录的 引用指针。


1.2、 MySQL 索引 的存储类型有哪些？

 MySQL中索引的存储类型有两种，即 BTree 和 Hash。


1.3、MySQL 索引 在哪里实现的？

 索引是在存储引擎中实现的。（MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap）

  • InnoDB / MyISAM 只支持 BTree 索引
  • Memory / Heap 都支持 BTree 和 Hash 索引

1.4、存储引擎 是什么？

 存储引擎就是指 表的类型 以及 表在计算机上的存储方式。


1.5、索引 的优缺点有哪些？

优点：

  • 提高数据的查询的效率（类似于书的目录）
  • 可以保证数据库表中每一行数据的唯一性（唯一索引）
  • 减少分组和排序的时间（使用分组和排序子句进行数据查询）
    • 被索引的列会自动进行分组和排序

缺点：

  • 占用磁盘空间
  • 降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）


2、MYSQL 索引 的分类

1、普通索引 和 唯一索引

  • 普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值

  • 唯一索引：要求索引列的值必须 唯一，但允许 有空值

    • 如果是组合索引，则列值的组合必须 唯一
    • 主键索引是一种特殊的唯一索引，不允许 有空值

2、单列索引 和 组合索引

  • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引
  • 组合索引：在表的 多个字段 组合上 创建的 索引
    • 只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）

3、全文索引

  • 全文索引 的类型为 fulltext
  • 在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
  • 全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

  • 空间索引 是对 空间数据类型 的字段 建立的索引

  • MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon

  • MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引

  • 创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

5、前缀索引

  • 在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度


3、MySQL 索引 的数据结构

 MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。


Hash：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。

 Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。


BTree：属于多叉树，又名多路平衡查找树。

性质：

  • BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
  • BTree 节点的键值按 非降序 排列
  • BTree 所有叶子节点都位于同一层（具有相同的深度）

在这里插入图片描述

查询过程，例如：Select * from table where id = 6;

在这里插入图片描述

BTree 的不足：

  • 不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历）
  • 节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低

B+Tree：在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储 键值 - 数据，非叶子节点只存储 键值 和 子节点 的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。

在这里插入图片描述

等值查询，例如：Select * from table where id = 8;

在这里插入图片描述

范围查询，例如：Select * from table where id between 8 and 22;

在这里插入图片描述

B+Tree 的优点：

  • 保证了等值查询和范围查询的快速查找
  • 单一节点存储更多的元素，减少了查询的 IO 次数


4、MySQL 索引 的实现

4.1、MyISAM 索引

  • MyISAM 的 数据文件（.myd） 和 索引文件（.myi） 是分开存储的
  • MyISAM（B+Tree）叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
  • MyISAM 的 主键索引（Primary key）和 辅助索引（Secondary key）在结构上没有任何区别，只是 主键索引 要求 键值唯一，而 辅助索引 键值 可以重复

在这里插入图片描述


4.2、InnoDB 索引

  • 数据和索引都存储在一个文件中（.ibd）

  • 一般情况下，聚簇索引等同于主键索引；除 聚簇索引 外的所有索引 均称为 辅助索引

  • InnoDB（B+Tree）叶子节点中存储的键值为索引列的值

    • 如果是聚簇索引，数据为整行记录（除了主键值）
    • 如果是辅助索引，数据为该行的主键值

  • 每一张表都有一个聚簇索引

    • 如果表中有定义主键，主键索引用作聚簇索引
    • 如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
    • 如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引

  • 根据在 辅助索引树 中获取的 主键id，再到 主键索引树 查询数据的过程 称为 回表 查询

  • 组合索引

    • 遵循 最左匹配（最左前缀）原则：
      • 使用 组合索引 查询时，MySQL 会一直向右匹配直至遇到范围查询（>、<、between、like）就停止匹配。
    • 只有第一列是有序的，其它列都是无序的（最左匹配原则的原因）

主键索引（聚簇索引）：
在这里插入图片描述

辅助索引：

在这里插入图片描述

组合索引：

在这里插入图片描述

在这里插入图片描述

覆盖索引：

  • 覆盖索引不是一种索引结构，而是一种优化手段
  • 我们只需要查询 组合索引 中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为 覆盖索引
create index idx on user(name, age, gender);

-- 使用覆盖索引
explain select name, age, gender from user where name ='万叶' and age = 18 and gender = '0';

在这里插入图片描述

-- 未使用覆盖索引
explain select * from user where name ='万叶' and age = 18 and gender = '0';

在这里插入图片描述



5、MySQL 索引 的使用

5.1、MySQL 索引 的基本语法

  • 定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引

创建表时：

create table table_name([col_name data_type] [unique | fulltext | spatial...],[unique...] [index | key] [index_name] (col_name [length], ...)
);create table user (id INT NOT NULL, name CHAR(30) NOT NULL, unique index uniqueIdx(id) 
);

表已存在时：

-- 第一种 
alter table table_name add [unique...] [index | key] [index_name] (col_name [length], ...);alter table user add unique index uniqueIdx(id);-- 第二种 
create [unique...] index index_name 
on table_name (col_name [length], ...);create unique index uniqueIdx on user(id);-- 删除索引drop index index_name on table_name;

5.2、怎么判断要不要加索引？

加索引：

  • 数据本身具有某种的性质，如：唯一性、非空性…
  • 频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引

不加索引：

  • 经常更新的列
  • 列 的值类型 很少，如 性别
  • where 条件中用不到的列
  • 参与计算的列
  • 数据量小的表

5.3、只要创建了索引，就一定会生效吗？

 不一定。当使用 组合索引 时，如果没有遵循 最左匹配 原则，索引不生效。

例如，创建 id、name、age 组合索引

  • id、（id、name）、（id、name、age）查询，索引生效
  • age、（age、name）查询，索引不生效

5.4、怎样判断索引是否生效？

 使用 explain 关键字。

  • possible_keys：MySQL 在搜索数据记录时可选用的各个索引
  • key：MySQL 实际选用的索引

例如：

explain select * from user where id = 1;

在这里插入图片描述


5.5、怎么避免索引失效?

  • 使用组合索引时，遵循 最左匹配 原则
  • 不在索引列上进行任何操作，如：计算、函数、类型转换
  • 尽量使用覆盖索引
  • 索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
  • 字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）

http://chatgpt.dhexx.cn/article/6XEGKDPB.shtml

相关文章

一文搞懂MySQL索引所有知识点（建议收藏）

一文搞懂MySQL索引所有知识点（建议收藏）

Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说&#xff0c;数据库索引好比是一本书前面的目录&#xff0c;能加快数据库的查询速度。 一般来说索引本身也很大&#xff0c;不可能全部存储在内存中&#xff0c;因此索引往往是存储…
阅读更多...
python中的%用法

python中的%用法

python中%&#xff1a; 1. 求模运算&#xff0c;相当于mod&#xff0c;也就是计算除法的余数&#xff0c;比如5%2就得到1。 2. %还用在python的格式化输出&#xff0c;比如&#xff1a; 说明如下&#xff1a; %[(name)][flags][width].[precision]typecode (name) 为命名 fl…
阅读更多...
python中的消息弹窗

python中的消息弹窗

在写python代码中，经常要弹窗提示一下消息情况，因为有时候我同时用了多个ui框架，比如tkinter，pyqt等，经常找不到合适的弹窗模块。因此梳理了一下几种弹窗方案。 一、采用windows自带的api（需要导入win32api） 特别强调采用这种方案，这种方案的优势就是弹窗模态，并不需…
阅读更多...
Python 中的\r 字符

Python 中的\r 字符

今天遇到了\r&#xff0c;然后就比较懵了&#xff0c;这里简单记录一下\r字符在Python中的应用。 \r&#xff1a;将光标回退到开始位置 先来看一个示例代码&#xff1a; import timetext "Hello\rWorld!" for i in text:time.sleep(0.5)print(i, end"")…
阅读更多...
python中flag的用法_python中flag什么意思

python中flag的用法_python中flag什么意思

python中flag一般就是标记、标识的意思 比如&#xff1a;&#xff08;推荐学习&#xff1a;Python视频教程&#xff09;#!/usr/bin/python # -*- coding: UTF-8 -*- x 7 i 1 flag 0 while i < 100: if (x%2 1) and (x%3 2) and (x%5 4) and (x%65): flag 1 e…
阅读更多...
python中result的用法_python中result的用法

python中result的用法_python中result的用法

Python中%(number,result)是什么意思 浮点型(Float) Python的浮点数就是数学中的小数&#xff0c;类似C语言中的double。 在运算中&#xff0c;整数与浮点数运算的结果是浮点数. 浮点数也就是小数&#xff0c;之所以称为浮点数&#xff0c;是因为按照科学记数法表示时&#xf…
阅读更多...
解决python中文乱码问题

解决python中文乱码问题

python输出中文乱码的问题相信大家都遇到过 那么应该如何解决呢&#xff1f; 一、修改系统变量 依次打开 设置->系统->关于->高级系统设置->环境变量->新建系统变量&#xff0c;新变量的变量名是&#xff1a;PYTHONIOENCODING&#xff0c;变量值是&#xff1…
阅读更多...
python中value的含义_python中value的意思

python中value的含义_python中value的意思

python语句s = str(value)是什么意思呢 把value转成字符串，然后赋值给变量s 比如说，s=str(100)以后。 Python 比如有一串数据的话里面 value[:-1]是什么value[:-1] ： value 应该是一个列表/元组, value[:-1]表示其最后一个元素 python 如何将字典中的value值CSS布局HTML小编…
阅读更多...
python中注释

python中注释

python中注释 在python中的注释一般分为单行注释、多行注释以及文档注释。 注释描述 在实际开发过程中&#xff0c;有效的代码注释不仅可以提升个人的工作效率&#xff0c;快速了解自己的程序情况&#xff0c;在团队协作开发过程中可以更加方便地让同事学习和调用你的代码。单…
阅读更多...
python中的各种符号

python中的各种符号

运算符描述实例算术运算符%取模 - 返回除法的余数b % a 输出结果 0**幂 - 返回x的y次幂a**b 为10的20次方&#xff0c; 输出结果 100000000000000000000//取整除 - 返回商的整数部分&#xff08;向下取整&#xff09; >>> 9//2 4 >>> -9//2 -5 赋值运算…
阅读更多...
python中的''''''字符串真的那么简单么？

python中的''''''字符串真的那么简单么？

文章目录 多行字符串&#xff0c;且保留代码格式&#xff01;文档&#xff01;&#xff01;&#xff01;注释功能 开门见山地说&#xff0c;如果你是一个接触Python一段时间的读者。那么你一定知道’和""可以灵活使用&#xff0c;例如以下的场景&#xff1a; s &quo…
阅读更多...
Python基础篇

Python基础篇

python是众多编程语言里面较为高级但也较慢的语言。所以&#xff0c;灵活地使用python内置的类和方法既可以减少代码量&#xff0c;也可以提高代码的运行速度。本文我们主要归纳python的一些基础知识&#xff0c;包括基本的类&#xff0c;内置方法&#xff0c;以及文件操作的一…
阅读更多...
python中的函数（全）

python中的函数（全）

函数的定义 概述&#xff1a;将一段经常使用的函数封装起来&#xff0c;减少重复代码&#xff0c;一个较大的程序&#xff0c;一般分为若干个程序块&#xff0c;每个模块实现特定的功能 于python中&#xff0c;定义函数时要用到def 语法结构&#xff1a; def 函数名称&…
阅读更多...
linux 图形设计软件,Ubuntu下使用Blender 3D图形专业设计工具

linux 图形设计软件,Ubuntu下使用Blender 3D图形专业设计工具

如果你正在为你的Ubuntu Linux操作系统寻找免费而又够酷够炫的图形设计软件&#xff0c;你就来对地方了&#xff01;这一次&#xff0c;我们将为你介绍应用广泛而且免费的3D图形设计软件Blender 。 Blender是一个自由、开源的3D模型创作软件&#xff0c;不仅适用于Linux平台&am…
阅读更多...
软件工程技术--第五章 详细设计

软件工程技术--第五章 详细设计

第五章 详细设计 5.1详细设计的任务与原则 详细设计&#xff08;又称为过程设计或模块设计&#xff09;&#xff0c;是编码的前导。 详细设计的根本目标是&#xff1a; 确定应该怎样具体地实现所要求的系统。经过这个阶段的设计工作&#xff0c;应该得出对目标系统的精确描…
阅读更多...
1 画图软件设计概述

1 画图软件设计概述

本人这段时间完成了一个小的绘图软件的设计&#xff1b;主要的功能包括&#xff1a; 1、文件操作&#xff08;打开、关闭、保存、打印、等&#xff09; 2、编辑功能 自由画笔&#xff0c;画点、直线、矩形、等 橡皮擦 撤销、恢复 3、查看功能&#xff0c;图片的放大、缩小…
阅读更多...
基于Java实现的绘图软件工具设计

基于Java实现的绘图软件工具设计

基于Java实现的绘图软件工具设计 演示视频 基于Java设计的绘图工具 01. 题目要求 做一个简单的绘图工具&#xff0c;以CAD的方式操作&#xff0c;能放置直线、矩形、圆和文字&#xff0c;能选中图形&#xff0c;修改参数&#xff0c;如颜色等&#xff0c;能拖动图形和调整大小…
阅读更多...
linux画板软件下载,Drawing画图软件

linux画板软件下载,Drawing画图软件

Drawing Linux(简单画图软件)让Linux用户有像win画图工具一样的体验&#xff0c;Linux下一款简单的绘图程序&#xff0c;类似Windows自带的画图&#xff0c;支持PNG&#xff0c;JPEG和bmp文件&#xff01; 软件介绍 该应用程序是一个基本的图像编辑器&#xff0c;类似于Microso…
阅读更多...
【软考-软件设计师】(下午题).

【软考-软件设计师】(下午题).

【软考-软件设计师】(下午题). 一&#xff0c;数据流图 二&#xff0c;数据库 三&#xff0c;UNL 四&#xff0c;算法题 &#xff0c;c语言 五&#xff0c;c / java &#xff0c;程序填空 数据流图 数据流图基本概念 数据流图&#xff1a;没有联系&#xff08;&#xff09; …
阅读更多...
基于JavaSwing开发画图软件 课程设计 小作业 大作业源码

基于JavaSwing开发画图软件 课程设计 小作业 大作业源码

基于JavaSwing开发画图软件&#xff08;大作业/课程设计&#xff09; 开发环境: Windows操作系统 开发工具&#xff1a;MyEclipse/eclipse/ideaJdk 运行效果图&#xff1a; 基于JavaSwing开发画图软件&#xff08;大作业/课程设计&#xff09; 开发环境: Windows操作系统 开…
阅读更多...
推荐文章

Copyright @ 2022~2023