数据库（MySQL）—— 索引

数据库（MySQL）—— 索引

  • 什么是索引
  • 创建索引
      • 使用 `CREATE INDEX` 语句
      • 使用 `ALTER TABLE` 语句
      • 在创建表时定义索引
      • 特殊类型索引
      • 注意事项
  • 举个例子
  • 无索引的情况
  • 有索引的情况
  • 为什么索引快
  • 索引的结构

今天我们来看看MySQL中的索引：

什么是索引

MySQL中的索引是一种数据结构，主要用于提高数据库查询效率。它类似于书籍的目录，让你能够快速找到所需内容而无需逐页浏览整本书。在数据库中，索引使得MySQL能够快速定位到表中特定数据行的位置，从而加速数据检索过程。

以下是关于MySQL索引的一些关键点：

  1. 数据结构：最常见的索引类型是B-Tree索引，它利用了平衡树的数据结构，保持数据排序，便于执行范围查询和排序操作。此外，还有哈希索引、全文索引等其他类型，分别适用于不同的查询需求。
  2. 存储位置：索引存储在数据库表的一个独立的结构中，不与实际数据混在一起。对于InnoDB存储引擎，聚簇索引（Clustered Index）会直接存储数据在叶子节点，而非聚簇索引（Secondary Index）则存储指向聚簇索引的指针。
  3. 提高查询速度：当执行查询时，数据库系统首先查看索引，直接定位到数据行，而不是遍历整个表，显著减少了查询时间，特别是在处理大数据量时。
  4. 索引选择：不是所有列都适合创建索引，一般在以下情况考虑创建索引：频繁作为查询条件的列、经常需要排序或分组的列、用于连接操作的列。但同时要注意，索引也会占用存储空间，并可能降低写入（插入、更新、删除）操作的性能，因为每次数据变更都需要同步更新索引。
  5. 复合索引：当一个索引包含多个列时，称为复合索引或多列索引。在复合索引中，最左侧原则是一个重要概念，即查询条件从索引的最左列开始进行匹配。但在MySQL 5.6及以上版本引入了索引下推功能，可以在一定程度上放宽这一限制。
  6. 管理索引：可以通过CREATE INDEX语句创建索引，使用ALTER TABLE语句添加或删除索引，以及使用DROP INDEX语句来删除索引。

综上所述，MySQL索引是优化数据库性能的关键工具，合理设计和使用索引对于提升应用的响应速度至关重要。

创建索引

在MySQL中，创建索引可以通过几种方式来实现，具体取决于你的需求和所使用的SQL语句。下面是几种常见的创建索引的方法：

使用 CREATE INDEX 语句

这是创建索引的最基本方式，适用于已存在的表。基本语法如下：

CREATE INDEX index_name ON table_name(column_name(length));
  • index_name 是你给索引指定的名字。
  • table_name 是你要在其上创建索引的表的名称。
  • column_name 是你想要索引的列名，可选的 (length) 指定索引的长度，仅对字符串类型列有效，可以减少索引大小但可能影响前缀匹配查询。

示例：

CREATE INDEX idx_lastname ON employees(lastname);

使用 ALTER TABLE 语句

如果你在创建表后想添加索引，也可以使用 ALTER TABLE 语句：

ALTER TABLE table_name ADD INDEX index_name(column_name);

或者，如果想创建唯一索引（不允许重复值）：

ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name);

示例：

ALTER TABLE students ADD INDEX idx_email ON(email);

在创建表时定义索引

你也可以在创建表的同时定义索引：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    INDEX index_name(column_name),
    UNIQUE INDEX unique_index_name(column_name)
);

特殊类型索引

  • 全文索引：适用于全文本搜索。
CREATE FULLTEXT INDEX ft_index ON articles(content);
  • 空间索引：用于地理空间数据类型。
CREATE SPATIAL INDEX sp_index ON places(location);

注意事项

  • 在创建索引之前，应评估索引对查询性能的提升以及对数据插入、更新和删除操作的影响。
  • 避免对经常更新的列创建过多索引，因为这会增加维护索引的成本。
  • 使用 EXPLAIN 语句分析查询计划，以帮助决定最佳的索引策略。
  • 根据实际情况选择合适的索引类型，比如是否需要唯一索引、全文索引等。

通过上述方法，你可以根据数据库的具体需求灵活地创建不同类型的索引。

举个例子

我们创建一张表：

-- 学生表
CREATE TABLE stu(
    id int COMMENT '学生id',
    name varchar(10) COMMENT '姓名',
    tele_number varchar(11) COMMENT '电话号码'
)COMMENT '学生表';

我们插入一些数据：

-- 插入第一条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (1, '张三', '13800138000');

-- 插入第二条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (2, '李四', '13912345678');

-- 插入第三条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (3, '王五', '13777777777');

-- 插入第四条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (4, '赵六', '13600000000');

-- 插入第五条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (5, '陈七', '13555555555');

-- 插入第六条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (6, '周八', '13411111111');

-- 插入第七条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (7, '吴九', '13322222222');

-- 插入第八条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (8, '郑十', '13233333333');

-- 插入第九条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (9, '钱十一', '13144444444');

-- 插入第十条学生记录
INSERT INTO stu (id, name, tele_number)
VALUES (10, '孙十二', '13055555555');

在这里插入图片描述

无索引的情况

我们执行下面的语句：

SET profiling = 1; -- 开启查询性能分析（MySQL特有）
SELECT SQL_NO_CACHE * FROM stu WHERE tele_number = '13800138000';
SHOW PROFILES; -- 显示最近的查询性能分析结果

找到下面的这条：
在这里插入图片描述
前面的时间就是执行查询语句的时间，我们看到时间是0.00022875秒。

当然，如果我们重复执行，每一次的时间都不一样，这个很正常。
在这里插入图片描述这次是0.00023775。

有索引的情况

我们对stu的tele_num创建索引：

CREATE INDEX idx_tele_number ON stu(tele_number);

然后，再执行上面的三句话：

SET profiling = 1; -- 开启查询性能分析（MySQL特有）
SELECT SQL_NO_CACHE * FROM stu WHERE tele_number = '13800138000';
SHOW PROFILES; -- 显示最近的查询性能分析结果

在这里插入图片描述
时间是0.000222，唉，时间好像没有节省很多哎。这里我们的数据比较少，看不出什么区别，如果数据量一旦很大，就可以看出索引的厉害了。

其实，索引的本质就是空间换时间，索引本身会占空间，但是通过索引我们可以提高检索速度。

为什么索引快

索引之所以快，是因为索引创建之后，数据的排序结构发生了变化（变成了索引结构）。

如果没有索引，MySQL会进行全表查找：

在这里插入图片描述在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

但是如果我们创建了索引，数据的排序结构就发生变化，比如变成二叉树：
在这里插入图片描述
此时我们在进行查询时，极大的提高的查询的效率。

索引的结构

索引的底层结构会有这几种：

索引类型结构描述
B+Tree索引最常见的索引类型，适用于大多数数据库引擎，如InnoDB。它通过层级的树状结构存储数据，每个节点可以包含多个键值对以及指向子节点的指针。叶子节点包含了实际的数据记录或数据记录的指针，并且叶子节点间通过指针相连，形成了一个有序链表，这有助于范围查询和排序操作。
Hash索引底层基于哈希表实现，适用于等值查询，特别是键值唯一或高度重复的情况。哈希索引通过计算索引列的哈希值并直接定位到对应的值或行，查询速度快。但由于哈希冲突的存在，它不支持范围查询，也无法用于排序或最左前缀匹配。
R-tree索引（空间索引）一种特殊类型的索引，主要由MyISAM引擎支持，用于高效地存储和查询多维空间数据，如地理坐标。R-tree通过将多维空间划分为重叠的区域来组织数据，使得空间查询（如“附近的所有地点”）变得高效。
Full-text索引（全文索引）专为文本内容设计的索引，能够支持复杂的文本搜索，包括词根搜索、同义词匹配等。它通过构建倒排索引来实现，即索引项是单词，而值是一系列包含该单词的文档位置。这种索引适用于大文本字段的模糊查询和全文搜索，常见于博客、文档数据库等应用场景。

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

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

相关文章

unity基础(一)

unity基础(一)

内容概要： 生命周期函数vector3 位置 方向 缩放旋转等信息Vector3欧拉角和Quaternion四元素unity脚本执行顺序设置 一 生命周期函数 方法说明Awake最早调用,所以一般可以再此实现单例模式OnEnable组件激活后调用,在Awake后会调用一次Start在Update之前调用一次&a…
阅读更多...
硬件知识积累 音频插座的了解，看音频插座的原理图来了解音频插座的引脚。

硬件知识积累 音频插座的了解，看音频插座的原理图来了解音频插座的引脚。

1. 音频接口 音频插座是一种用于连接音频信号线路的电子元件，常见于音频设备（如音响、耳机、话筒等）中。它的主要作用是将电子信号转化为声音信号，以满足人们对于音乐、电影、游戏等方面的需求。 根据插头形状的不同，音…
阅读更多...
和comate一起，用JavaScript实现一个简易版五子棋小游戏

和comate一起，用JavaScript实现一个简易版五子棋小游戏

前言 五子棋起源于中国，是全国智力运动会竞技项目之一，是一种两人对弈的纯策略型棋类游戏。双方分别使用黑白两色的棋子，下在棋盘直线与横线的交叉点上，先形成五子连珠者获胜。 这次和Baidu Comate智能代码助手共同完成这个小游戏…
阅读更多...
[华为OD] C卷 田忌赛马 DFS 200

[华为OD] C卷 田忌赛马 DFS 200

题目： 给定两个只包含数字的数组a, b,调整数组a里面数字的顺序，使得尽可能多的a[i] >b[i]。 数组a和b中的数字各不相同。 输出所有可以达到最优结果的a数组的数量 输入描述 输入的第一行是数组a中的数字，其中只包含数字，每…
阅读更多...
LVS DR模式部署

LVS DR模式部署

一、LVS 简介 LVS的三种工作模式 NAT 地址转换 调度器会作为所有节点服务器的默认网关，也是客户端的访问入口和节点服务器返回响应消息的出口，所以调度器会承载双向流量的负载压力，可能会为整个群集的性能瓶颈。由于节点服务器都会处于内网…
阅读更多...
AcWing 4993 FEB

AcWing 4993 FEB

4993. FEB - AcWing题库 大佬亲笔 将原串分成三段&#xff1a; FFF|E.....B|FFF 先合并中间段&#xff0c;再合并两边的段 #include <iostream> #include <cstring> #include <algorithm> #include <string> #include <queue&g…
阅读更多...
Eclipse下载安装教程（包含JDK安装）【保姆级教学】【2023.10月最新版】

Eclipse下载安装教程（包含JDK安装）【保姆级教学】【2023.10月最新版】

目录 文章最后附下载链接 第一步&#xff1a;下载Eclipse&#xff0c;并安装 第二步&#xff1a;下载JDK&#xff0c;并安装 第三步&#xff1a;Java运行环境配置 安装Eclipse必须同时安装JDK &#xff01;&#xff01;&#xff01; 文章最后附下载链接 第一步&#xf…
阅读更多...
ES:聚合查询语法

ES:聚合查询语法

基础查询结构&#xff1a; GET http://ip:prot/textbook/_search { "query" : { ...query子句... }, "aggs" : { "agg_name":{ "agg_type": { "agg_arg": agg_arg_value } } }, "sort" : { ..sor…
阅读更多...
快速学习Python：新手入门指南

快速学习Python：新手入门指南

一、确定学习目标 首先&#xff0c;你需要明确自己学习Python的目标。是希望成为一名Python开发人员&#xff0c;还是仅仅想在数据分析、数据可视化等领域使用Python。不同的目标需要不同的学习路径和资源。 二、选择合适的教材和课程 Python的学习资源非常丰富&#xff0c;…
阅读更多...
vscode 使用正则搜索

vscode 使用正则搜索

ctrl c 复制&#xff0c;内容如下&#xff1a; Vue3简介创建Vue3工程Vue3核心语法路由pinia组件通信其它 APIVue3新组件
阅读更多...
HDLbits 刷题 -- Exams/m2014 q3

HDLbits 刷题 -- Exams/m2014 q3

Consider the function f shown in the Karnaugh map below. Implement this function. d is dont-care, which means you may choose to output whatever value is convenient. 译&#xff1a;考虑下面卡诺图中显示的函数f。 实现这个函数。D是dont-care&#xff0c;这意味着…
阅读更多...
别再观望！2024年必做的项目：视频号无货源

别再观望！2024年必做的项目：视频号无货源

大家好&#xff0c;我是电商花花。 现在做项目&#xff0c;更喜欢的是一个能稳定出单&#xff0c;稳定发展的一个创业项目&#xff0c;一个好的项目就是能长期稳定的发展&#xff0c;如果只追求短平快收益的项目&#xff0c;这样的项目也并不适合我们。 对于越来越火爆的视频…
阅读更多...
MoviePy（Python音视频开发）

MoviePy（Python音视频开发）

音视频基础帧率、码率、分辨率视频格式H.264和H.265视频压缩算法 Moviepy常见剪辑类VideoFlieClipImageFlieClipColorClipTextClipCompositeVideoClipAudioFlieClipCompositeAudioClip 常见操作音视频的读入与导出截取音视频 音视频基础 帧率、码率、分辨率 体积&#xff08;V…
阅读更多...
TL-WN826N无线网卡连接电脑蓝屏，提示rtl8188gu.sys

TL-WN826N无线网卡连接电脑蓝屏，提示rtl8188gu.sys

TL-WN826N无线网卡插电脑就蓝屏&#xff0c;提示rtl8188gu.sys 处理方法&#xff1a; 设备管理器中卸载其他的2.0无线网卡程序和功能中卸载网卡驱动TPlink官网下载 TL-WN826N V1.0_1.0.0&#xff08;https://www.tp-link.com.cn/product_572.html?vdownload&#xff09;&…
阅读更多...
Redis简介和数据结构

Redis简介和数据结构

目录 简介 进入之后身份认证才能使用 优点 用途&#xff1a; 数据结构 string string自动扩容 Redis中的简单动态字符串&#xff08;SDS&#xff09;具有以下优点&#xff1a; SDS数据的编码格式 比较&#xff1a; string 常用操作 分布式锁 使用情况&#xff0c;…
阅读更多...
每日Attention学习2——Multi-Scale Convolutional Attention

每日Attention学习2——Multi-Scale Convolutional Attention

模块出处 [link] [code] [NIPS 22] SegNeXt: Rethinking Convolutional Attention Design for Semantic Segmentation 模块名称 Multi-Scale Convolutional Attention (MSCA) 模块作用 多尺度特征提取&#xff0c;更大感受野 模块结构 模块代码 import torch import torch.…
阅读更多...
【启明智显技术分享】“ESP-IDF环境搭建全攻略：告别基于乐鑫方案彩屏开发中的搭建难题”

【启明智显技术分享】“ESP-IDF环境搭建全攻略：告别基于乐鑫方案彩屏开发中的搭建难题”

前言&#xff1a; 【启明智显】专注于HMI&#xff08;人机交互&#xff09;及AIoT&#xff08;人工智能物联网&#xff09;产品和解决方案的提供商&#xff0c;我们深知彩屏显示方案在现代物联网应用中的重要性。为此&#xff0c;我们一直致力于为客户提供彩屏显示方案相关的技…
阅读更多...
深度解析：数据结构二叉树（1）

深度解析：数据结构二叉树（1）

✅作者简介&#xff1a;大家好&#xff0c;我是再无B&#xff5e;U&#xff5e;G&#xff0c;一个想要与大家共同进步的男人&#x1f609;&#x1f609; &#x1f34e;个人主页&#xff1a; 再无B&#xff5e;U&#xff5e;G-CSDN博客 目标 1. 掌握树的基本概念 2. 掌握二叉…
阅读更多...
分享10个高质量宝藏网站~

分享10个高质量宝藏网站~

分享一波高质量宝藏网站~ 这10个宝藏网站&#xff0c;个个都好用到爆&#xff0c;娱乐、办公、学习都能在这里找到&#xff01; 1、Z-Library https://zh.zlibrary-be.se/ 世界最大的免费电子书下载网站&#xff01;电子书资源超千万&#xff0c;不过这个网站不太稳定&#…
阅读更多...
网络原理

网络原理

UDP 特点&#xff1a;无连接 不可靠传输 面向数据报 全双工 报文格式&#xff1a; UDP数据报UDP报头UDP载荷&#xff08;应用层数据报&#xff09; | 源端口 目的端口 报文长度 校验和 TCP 特点&#xff1a;有连接 可靠传输 面向字节流 全双工 作为传输层…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站