Mysql索引,聚簇索引,非聚簇索引,回表查询

什么是索引

        数据库索引是为了实现高效数据查询的一种有序的数据数据结构,类似于书的目录,通过目录可以快速的定位到想要的数据,因为一张表中的数据会有很多,如果直接去表中检索数据效率会很低,所以需要为表中的数据建立索引,这样就会提高效率。

索引优势

  1. 通过索引可以快速定位到数据,降低IO次数,提升效率
  2. 排序列添加索引,也可以提高提高排序的效率,因为索引是有序的。

索引劣势

  1. 索引保存也需要占用空间
  2. 增删改数据时,数据发生变化,索引也需要做出相应的改变,也是需要时间开销的

索引创建原则

  • 什么时候需要创建索引

  1. 主键自动建立唯一索引
  2. 作为查询条件的字段应该创建索引(where 后面的语句中包含的字段)
  3. 尽量使用联合索引,减少单列索引
  4. 针对数据量较大,且查询比较频繁的表建立索引
  5. 查询中排序的字段,分组中的字段,若通过索引去访问将大大提高排序速度
  • 什么时候不需要创建索引

  1. 表记录太少,例如类型表,员工职位表等
  2. 增删改频率高的表
  3. 查询条件中没有
  4. 唯一性差,例如性别,只有男和女两种值

索引分类

  • 主键索引

        创建表时,设置哪个列为 primary key ,主键列默认自动创建索引

# 创建主键索引方式1, 创建表时直接添加主键索引
create table 表名(
	id int primary key
);

# 创建主键索引方式2, 表创建完成后, 修改表添加主键索引
create table 表名(
	id int
);
alter table 表名 add primary key 表名(id) ;
# 删除主键索引
alter table 表名 drop primary key;
  • 唯一索引

        设置某个列数据唯一性,会创建唯一索引

# 创建唯一索引方式1, 创建表时直接添加唯一索引
create table 表名(
	id int primary key auto_increment, -- 创建表时直接设置主键
    account varchar(20) unique
);

# 创建唯一索引方式2, 表创建完成后, 修改表添加唯一索引
create table 表名(
	id int,
    account varchar(20)
);
create unique index index_unique_account on 表名(account);
# 删除索引, 非主键索引
drop index index_unique_account ON 表名;
  • 单值索引

        一个索引中,只包含一个列

# 创建索引
create index index_name on 表名(列名);

# 删除索引, 非主键索引
drop index index_name ON 表名;
  • 组合索引(复合索引)

        一个索引中包含多个列,节省了索引开支

create table t(
	a int,
    b int,
    c int
);

# 创建组合索引
create index index_t_a_b on t(a, b);

# 删除索引, 非主键索引
drop index index_t_a_b ON t;

在查询时,如果使用组合索中包含的字段引作为查询条件,必须要包含组合索引中的第一个列,如在上述索引 index_t_a_b ,如果在查询时不使用a作为查询条件会导致索引失效。

通过 explain 可以查看查询时是否是由索引

# 索引生效
explain select * from t where a='' and b='';
explain select * from t where b='' and a='';

# 索引生效
explain select * from t where a='' and c='';

# 索引失效
explain select * from t where b='' and c='';

  • 前缀索引

        有些列长度比较大,需要给前面置顶的长度的区间添加索引即可。

create index 索引名 on 表名(列名(长度));
  • 全文索引

        模糊查询时,即使有索引也可能出现索引失效的情况

CREATE TABLE t(
	id INT PRIMARY KEY  AUTO_INCREMENT,
	title VARCHAR(100)
);

INSERT INTO t(title)
VALUES
('小明没考上中学'),
('李华没考上大学'),
('我四级没过')

CREATE INDEX title_index_t ON t(title);

# 索引生效
EXPLAIN SELECT * FROM t WHERE title LIKE '小明%'

# 索引失效
EXPLAIN SELECT * FROM t WHERE title LIKE '%没考%'

# 创建全文索引
CREATE FULLTEXT INDEX title_index_t ON t(title) WITH PARSER ngram;

# 索引生效
EXPLAIN SELECT * FROM t WHERE MATCH(title) AGAINST('没考')

聚簇索引和非聚簇索引

区分方式:找到了索引是否就找对应的数据,找到是聚簇索引,没有找到事非聚簇索引

  • 聚簇索引

        找到索引就找到了对应的数据,即索引和数据的存储是在一起的

  • 非聚簇索引

        索引的存储和数据的存储是分离的,在myisam引擎中,由于索引和数据分别存储在两个不同的文件中,找到了索引,还需要重新查找一次才能找到数据。

        innodb引擎中,像普通的索引也称为二级索引,他们也是非聚簇索引,例如为名字(name)创建索引(主键索引为以及索引),通过名字查找到id后,需要到主键索引树中找到对应的数据也是非聚簇索引。

回表查询

回表查询指的是查询时查询的次数不止一次

例:现在有表t结构如下:

  1. 通过id查询学生的所有信息,这时只需要查询一次即可,因为主键索引是聚簇索引,查询到id就找到了对应行的数据
    SELECT * FROM t WHERE id = 1;
  2. 通过学号查询学生所有的信息,此时需要回表查询,因为根据学号找到后没有直接找到对应的其他数据(非聚簇索引)
    SELECT * FROM t WHERE stu_no = '1001';
  3. 通过学号查询学生的学号,此时不需要回表查询,因为我们所需要的数据已经在学号的索引树上找到了,此时也是聚簇索引,没有回表查询操作。
    SELECT stu_no FROM t WHERE stu_no = '1001';

    这样的查询方式用于查看数据库中是否包含这个学号。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/932077.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

以MP6924A为核心的LLC拓扑学习【一】

PFCLLC: 在PFC(功率因数校正)和LLC(谐振变换器)组成的电源系统中,各个电路有特定的作用,它们协同工作以实现高效率和高功率因数的电能转换。 1. PFC(功率因数校正)电路的作用 PFC电…

️ 在 Windows WSL 上部署 Ollama 和大语言模型的完整指南20241206

🛠️ 在 Windows WSL 上部署 Ollama 和大语言模型的完整指南 📝 引言 随着大语言模型(LLM)和人工智能的飞速发展,越来越多的开发者尝试在本地环境中部署大模型进行实验。然而,由于资源需求高、网络限制多…

1-1 ESP32开发环境配置

前言: 基于Arduio配置ESP32开发环境... 目录 前言: 1.0 安装Python 2.0 安装VSCode 3.0 VSCode实用插件 4.0 替换VSCode配置(可选) 后记 1.0 安装Python 在windows操作系统的搜索框中搜索Microsoft Store 点击获取 安装完成…

【k8s 深入学习之 event 聚合】event count累记聚合(采用 Patch),Message 聚合形成聚合 event(采用Create)

参考 15.深入k8s:Event事件处理及其源码分析 - luozhiyun - 博客园event 模块总览 EventRecorder:是事件生成者,k8s组件通过调用它的方法来生成事件;EventBroadcaster:事件广播器,负责消费EventRecorder产生的事件,然后分发给broadcasterWatcher;broadcasterWatcher:用…

AURIX TC3xx学习笔记2 GTM模块

文章目录 引言功能改进一些缩写 功能细节GTM Clock and Time Base Management (CTBM)Clock Management Unit (CMU)External Generation Unit (EGU)Configurable Clock Generation sub-unit (CFGU)Fixed Clock Generation (FXU) Time Base Unit (TBU) Cluster Configuration Mod…

在CentOS上无Parallel时并发上传.wav文件的Shell脚本解决方案

在CentOS上无Parallel时并发上传.wav文件的Shell脚本解决方案 背景概述解决方案脚本实现脚本说明使用指南注意事项在CentOS操作系统环境中,若需并发上传特定目录下的.wav文件至HTTP服务器,而系统未安装GNU parallel工具,我们可通过其他方法实现此需求。本文将介绍一种利用Sh…

QT通过在线安装器安装【详细】

在线安装器地址: 官方在线安装器:Index of /official_releases/online_installers (qt.io) 通过命令行启动安装页面 直接双击qt安装程序,在线安装会非常慢,甚至安装失败,所以通过命令行页面启动安装页面。点击wind…

保姆级教学 uniapp绘制二维码海报并保存至相册,真机正常展示图片二维码

一、获取二维码 uni.request({url: https://api.weixin.qq.com/wxa/getwxacode?access_token${getStorage("token")},responseType: "arraybuffer",method: "POST",data: {path: "/pages/index/index"},success(res) {// 转换为 Uint…

Unity类银河战士恶魔城学习总结(P166 Ailments FX 异常状态伤害粒子特效)

【Unity教程】从0编程制作类银河恶魔城游戏_哔哩哔哩_bilibili 教程源地址:https://www.udemy.com/course/2d-rpg-alexdev/ 本章节创建了三种粒子特效,火焰,寒冰,雷电 主场景创建/特效/粒子 初始的例子特效 火焰 寒冰 雷电 En…

Java基于SpringBoot的网上订餐系统,附源码

博主介绍:✌Java老徐、7年大厂程序员经历。全网粉丝12w、csdn博客专家、掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域和毕业项目实战✌ 🍅文末获取源码联系🍅 👇🏻 精彩专栏推荐订阅👇&…

[笔记] Windows 上 Git 安装详细教程:从零开始,附带每个选项解析

Git 是目前最流行的分布式版本控制系统之一,广泛应用于软件开发和项目管理中。对于 Windows 用户来说,正确安装和配置 Git 是开始使用 Git 的第一步。本文提供一份详细的指南,帮助你在 Windows 系统上顺利安装 Git,并解释每个安装…

JavaScript编写css自定义属性

一、自定义属性 是在 CSS 中定义的变量,以 --开头。它们可以存储颜色、尺寸、字体等任何 CSS 值,并且可以在整个文档中重复使用。 :root {--primary-color: #3498db;--font-size: 16px; }body {color: var(--primary-color);font-size: var(--font-siz…

项目开发之Jenkins

文章目录 思考基础概述JenkinsMavenGit集成开发部署GitLab服务安装 实战1 新建任务需要的配置pipeline最后 思考 jenkis怎么连接github仓库? jenkis的作用是什么?基础 概述 定义:Jenkins是一款开源的持续集成(Continuous Integration&…

core Webapi jwt 认证

core cookie 验证 Web API Jwt 》》》》用户信息 namespace WebAPI001.Coms {public class Account{public string UserName { get; set; }public string UserPassword { get; set; }public string UserRole { get; set; }} }》》》获取jwt类 using Microsoft.AspNetCore.Mvc…

TCP/IP协议详解(小白)

TCP/IP协议详解 TCP/IP协议包含了一系列的协议,也叫TCP/IP协议族(TCP/IP Protocol Suite,或TCP/IP Protocols),简称TCP/IP。TCP/IP协议族提供了点对点的连结机制,并且将传输数据帧的封装、寻址、传输、路由…

Java项目实战II基于微信小程序的旅游社交平台(开发文档+数据库+源码)

目录 一、前言 二、技术介绍 三、系统实现 四、核心代码 五、源码获取 全栈码农以及毕业设计实战开发,CSDN平台Java领域新星创作者,专注于大学生项目实战开发、讲解和毕业答疑辅导。 一、前言 随着移动互联网的迅猛发展,旅游已经成为人…

jmeter配置

单接口运行没问题,但是批量执行100个线程数发现总是提示请求不合法 最后发现 需要将配置改成 正好回归一下这个配置: Ramp-Up时间(秒)的定义: Ramp-Up时间是指在JMeter测试中,所有指定的线程&#xff08…

SpringBoot 项目如何集成 JWT

SpringBoot 项目如何集成 JWT JWT JSON Web Token (JWT) 是一个开放标准(RFC 7519),它定义了一种紧凑的、自包含的方式,用于作为 JSON 对象在各方之间安全地传输信息。 在 Oauth2 中,其实就是返回访问令牌 (access_token&#…

【innodb阅读笔记】之 Innodb行记录格式 (Compact 行格式)

一、Innodb行记录格式 innodb 存储引擎同大多数数据库一样,记录是以行的形式存储的。这意味着页中保存的一行行的数据。在 mysql 5.7 版本中,默认格式为 Dynamic,可以通过命令查看当前表的行格式,其中 row_format 表示当前表行记录…